MatheW’s blog

lipca 06

MySQL : Indeksy i klucze obce

Tag: MySQL — MatheW @ 18:14. . Post odwiedzany 18437 razy. Trackback

W ciągu codziennej pracy webdeveloperskiej potrzebowałem ostatnio odświeżyć sobie wiadomości na temat procedur i widoków w MySQL. Mimo, że MySQL 5 istnieje już sporo czasu to niewiele możemy o nim znaleźć na polskich weblogach, dlatego też postanowiłem zapełnić tę niszę kilkoma fachowymi, miejmy nadzieję, artykułami o najważniejszych rzeczach, które MySQL 5 wniósł, a więc widokach, procedurach czy wyzwalaczach. Są to niezwykle istotne elementy projektowania baz danych, czyli jednego z najważniejszych etapów programowania aplikacji bazodanowych. Uznałem, że na początku, w ramach wstępu przydatnym będzie opisanie i omówienie indeksów oraz kluczy obcych, które co prawda istniały wcześniej ale dalej są rzadko używane przez początkujących.

Wiele informacji jest tu przedstawionych w sposób niezależny od silnika bazy danych, także artykuł przyda się także użytkownikom innych silników baz danych.

Indeksy

W aplikacjach bazodanowych w większości przypadków korzystamy głównie z zapytań typu SELECT, rzadziej modyfikujemy nasze dane poprzez INSERT, UPDATE, czy DELETE. Dodatkowo działania modyfikujące są zwykle wykonywane w stałym czasie, który bywa zazwyczaj małą cząstką trwania wszystkich zapytań. Natomiast czas wykonania kwerendy SELECT bywa bardzo różny – wszystko zależy od jej skomplikowania, dlatego na tym polu możemy święcić duże sukcesy w procesie optymalizacji. Indeksy to jeden z najłatwiejszych sposobów na przyspieszanie zapytań do bazy danych.

Zapytanie wykonywane bez indeksów zmusza silnik bazy danych do przeszukiwania sekwencyjnie wszystkich wierszy tabeli. O ile w wypadkach gdy tabela jest mała nie powinniśmy napotkać problemów z wydajnością, jednak wraz z jej rozbudowaniem czas wykonywania zapytań niebezpiecznie wzrasta. Jedynym ratunkiem bardzo często są indeksy.

Indeksy to specjalne dane przechowywane w oddzielnym miejscu przez silnik bazy danych. Możemy je porównać do znanych nam z tradycyjnych książek indeksów – dużo szybciej znajdziemy pewną interesującą nas frazę w ułożonym alfabetycznie indeksie, niż mielibyśmy przeszukiwać całą książkę w poszukiwaniu interesującego nas tematu. Tak też działają indeksy w bazie danych – silnik bazy danych podczas wyszukań wybiera odpowiednie dane dzięki indeksowi, który stanowi wskaźnik na odpowiednie dane w tabeli.

W tym artykule będziemy pracować na takiej oto tabeli:

CREATE TABLE `players` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 30 ) NOT NULL ,
`surname` VARCHAR( 30 ) NOT NULL ,
`age` TINYINT NOT NULL ,
`nationality` VARCHAR( 30 ) NOT NULL
) ENGINE = InnoDB;

Indeksy możemy zakładać na pojedynczych polach w tabeli, bądź też na wielu. Automatycznie indeksy są tworzone dla kluczy głównych tabeli – czyli w większości przypadków automatycznie inkrementowanego pola id. Dzięki temu takie zapytanie:

SELECT name, surname FROM players WHERE id=4

nie powoduje przeszukiwania każdego wiersza tabeli i sprawdzania czy akurat ten rekord ma pole id równe 4, zamiast tego silnik bazy danych korzysta z odpowiednio poukładanego indeksu, który zajmuje mniej miejsca, w związku z czym działa szybciej.

Zakładanie indeksów

Oczywiście możemy sami założyć indeks dzięki poleceniu

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX nazwa_indeksu ON tabela (pole [ASC | DESC] [, kolejne_pole [ASC | DESC] ])

Widzimy tu 3 typy indeksu (można także nie wybierać typu indeksu): UNIQUE zapewnia nas, że to pole (lub zestaw pól) będzie unikalny dla każdego rekordu, FULLTEXT zakładamy dla pól, gdzie chcemy wyszukiwać dane za pomocą składni MATCH (tylko w MyISAM – opis), SPATIAL można wykorzystywać dla danych geometrycznych. Zwykle będziemy korzystać z unikalnego bądź domyślnego indeksu. Opcjonalne modyfikatory ASC, DESC określają w jaki sposób indeks ma zostać posortowany.

Możemy założyć indeks na wielu kolumnach – bardzo istotna jest tu kolejność. Powiedzmy, że założyliśmy taki indeks:

CREATE  INDEX name_surname ON players (name, surname)

Indeks wykorzystany będzie, gdy będziemy przeszukiwać dane po polu name, bądź jednocześnie po polu name jak i surname – nie będzie tak, gdy wyszukujemy dane tylko przez sprawdzenie nazwiska – to zapytanie nie wykorzysta indeksu:

SELECT * FROM players WHERE surname='Adams'

Testy

W naszej przykładowej tabeli dodałem 10 tysięcy rekordów i wykonałem takie zapytanie:

SELECT * FROM players WHERE nationality = 'Poland' AND age BETWEEN 25
AND 30

Zwróciło ono 89 rekordów i bez indeksów zajęło to 0.0074 s, natomiast po poindeksowaniu wieku i narodowości na wykonanie zapytania wystarczyło 0.0018 s, a więc czas krótszy ponad 4 razy!

Kiedy zakładać indeksy?

Gdy często w zapytaniach w klauzuli WHERE korzystamy z danego pola lub zestawu pól
Gdy pola często wykorzystujemy do złączania tabel poprzez JOIN-y
Gdy pola są często wykorzystywane do sortowania wyników tabeli
Gdy pola są często wykorzystywane w funkcjach MIN() i MAX()
Gdy rekordy w tabeli w danym polu mają bardzo różne wartości – wyszukiwanie przy pomocy indeksu jest tym skuteczniejsze, im mniej rekordów ma szukaną przez nas wartość (a więc gdy np. 5 spośród miliona rekordów mają szukaną przez nas wartość to indeks będzie korzystny, podczas gdy tę wartość będzie miała np. połowa to indeks tylko zajmuje niepotrzebnie miejsce, zapytania nam nie przyspieszy)

Kiedy zakładanie indeksów się nie opłaca?

Często programiści, którzy dopiero co poznali indeksy wariują na ich punkcie i zakładają ich tysiące, co jest często głupim i nieprzemyślanym działaniem, nie przynoszącym żadnych korzyści. Co więcej indeksy zajmują przestrzeń na dysku twardym i może się zdarzyć, że nieprzemyślane rozwiązania spowodują zapchanie naszego dysku.

Dlatego należy poważnie przemyśleć kwestię niezbędności indeksów, nie są one potrzebne m.in. w takich sytuacjach:

Gdy tabela jest bardzo rzadko odczytywana, a w głównej mierze dodajemy do niej rekordy (jeżeli tabela ma wiele indeksów to czas dodawania bądź zmieniania w niej danych przedłuża się w związku z koniecznością modyfikacji indeksu)
Gdy pole zawiera dużo jednakowych wartości
Gdy zapytania są mało selektywne
Gdy pole w zapytaniach jest wykorzystywane poprzez funkcję np. WHERE substr(name, 3, 1) = ‘r’ – silnik nie wykorzysta wtedy indeksu

Nie powinno się ponadto zakładać indeksu wielokrotnie na tę samą kolumnę, na przykład założenie indeksu na (name, surname) oraz na name nic nam nie da, poza zwiększoną zajętością miejsca na dysku. Oczywiście istnieją przypadki, w których indeksowanie będzie potrzebne – np. unikatowy indeks (name, surname) zapewniający nas o unikatowości pól i indeks na surname, pomagający nam wyszukiwać po nazwisku.

Klucze obce

Oprócz kluczy głównych indeksowane są również klucze obce. Ich głównym zadaniem jest tworzenie relacji między tabelami. Przyjmijmy, że do naszej tabeli z graczami dokładamy tabelę z zespołami:

CREATE TABLE `teams` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 30 ) NOT NULL
) ENGINE = InnoDB;

Aby powiązać zawodników z zespołami tworzymy tabelę:

CREATE TABLE `players_to_teams` (
  `team_id` INT(11) UNSIGNED NOT NULL,
  `player_id` INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY(`team_id`, `player_id`)
) ENGINE = InnoDB;

Klucz główny złożony z 2 pól zapewnia nas, że nie powstaną 2 identyczne powiązania zawodnika i zespołu, możemy dodać jeszcze unikatowy indeks na player_id, który zapewni nas, że żaden zawodnik nie będzie graczem dwóch zespołów.

Na dobrą sprawę już możemy z takiej tabeli korzystać, tworzyć wiązania. Jednak co będzie gdy w jakiś sposób usuniemy zespół, nie usuwając jednocześnie powiązań jego zawodników? Dane w bazie danych nie będą spójne.

MySQL jak większość innych silników baz danych ma mechanizmy, które zabezpieczą nas przed takimi sytuacjami. Są nimi właśnie klucze obce, dostępne jedynie w tabelach typu InnoDB (zwykle domyślnym mechanizmem jest MyISAM).

Zanim dodamy odpowiednie referencje będziemy zmuszeni dodać indeks na polu player_id – klucze muszą być indeksowane i o ile silnik bazy danych może korzystać z indeksu głównego dla team_id, to nie zrobi tego (o czym pisałem w poprzedniej części artykułu) dla player_id, tak więc dodajemy:

ALTER TABLE `players_to_teams` ADD INDEX ( `player_id` )

Następnie dodajemy referencje do tabeli players_to_teams:

ALTER TABLE `players_to_teams`
  ADD CONSTRAINT FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Przydatne będzie objaśnienie – player_id to nazwa pola będącego kluczem głównym, dalej dodajemy referencje do pola id tabeli players. Klauzule ON DELETE i UPDATE określają jak zachowywać się mają rekordy, gdy zostają usunięte bądź zedytowane obiekty do których odnoszą się odpowiednie klucze obce. Jest kilka możliwości:

CASCADE – jeżeli obiekt, do którego klucz obcy się odnosi zostanie usunięty/zedytowany to samo stanie się z rekordem referującym (czyli – usunięty zostaje zespół, automatycznie usunięte zostają wszystkie powiązania do niego zawodników)
RESTRICT lub NO ACTION – jeżeli obiekt, do którego klucz obcy się odnosi zostanie usunięty/zedytowany, a dalej posiada on rekordy, które się do niego odnoszą to akcja zostanie anulowana (a więc nie będzie można usunąć zespołu, jeżeli ma on przypisanych zawodników)
SET NULL - jeżeli obiekt, do którego klucz obcy się odnosi zostanie usunięty/zedytowany, a dalej posiada on rekordy, które się do niego odnoszą to kluczom obcym zostaną przypisane wartości puste (NULL)
SET DEFAULT - jeżeli obiekt, do którego klucz obcy się odnosi zostanie usunięty/zedytowany, a dalej posiada on rekordy, które się do niego odnoszą to kluczom obcym zostaną przypisane wartości domyślne dla tego pola

Istnieją pewne ograniczenia przy tworzeniu kluczy obcych:

Tabela, do której odnosi się klucz obcy musi istnieć
Pole, do którego odnosi się klucz obcy musi być kluczem głównym
Zarówno tabela z kluczem obcym, jak i tabela do, której się on odnosi muszą być w InnoDB
Pola muszą mieć identyczną strukturę (tj. jeżeli klucz główny to INT(11) UNSIGNED to i klucz obcy musi być taki sam)
Pole klucza obcego musi mieć założony indeks

Klucze obce stanowią znakomite ułatwienie dla programisty, zapewniają poprawność danych przechowywanych w bazie danych. Nie pozwalają na wykonywanie błędów, tworzenie błędnych relacji. Przenoszą te kompetencje z samego programu bezpośrednio do bazy danych, dzięki czemu nie będzie potrzeby projektować oddzielnie tych mechanizmów, co przydaje się zwłaszcza, gdy z jednej bazy danych korzysta wielu użytkowników.

Podsumowanie

Dobry projekt bazy danych to podstawa poprawnie stworzonej aplikacji i nie należy go lekceważyć, gdyż późniejsze jego modyfikacje są niezwykle ciężkie. O ile indeksy możemy założyć zawsze i mogą one przynieść korzyści nawet w późnym cyklu tworzenia oprogramowania, to dodanie odpowiednich relacji może być już niemożliwe, podobnie jak zmodyfikowanie niewydajnych rozwiązań.

Tworzenie struktury bazy danych na sucho nie jest zbyt efektywne, dużo wygodniej korzysta się z projektanta relacji typu DBDesigner, dzięki niemu w bardzo łatwy sposób utworzymy szkielet naszej bazy, a on za nas wygeneruje kod SQL, który tą bazę powoła do życia.

Mam nadzieję, że ten artykuł jak i kolejne pomogą w projektowaniu baz danych, nauczą bądź przypomną pewne rozwiązania, z których powinien korzystać każdy programista aplikacji bazodanowych.