Wstęp

MySQL jest chyba najpowszechniej używaną bazą danych w różnego typu aplikacjach internetowych: od portali, przez fora, blogi, gry i inne rodzaje stron. Dlaczego? Bo jest szybka, prosta, darmowa i się przyjęła. Oprócz ewidentnych zalet ma też serię wad: standardowy engine MyISAM nie jest transakcyjny, nie jest bezpieczny, przy zapisie blokuje całą tabelę, a nie pojedynczy rekord. Jakby tego jeszcze było mało, to w 95% procentach przypadków, właśnie baza MySQL jest wąskim gardłem całej aplikacji i to jej wydajność (szybkość przetwarzania zapytań) decyduje o wydajności całego rozwiązania. Pracując nad pewną aplikacją (Pulsar-Online), przez ostatni tydzień starałem się skupić właśnie nad sprawą optymalizacji MySQL na której oparty jest Pulsar i podzielić się swoimi spostrzeżeniami na ten temat.

Po pierwsze, specyfika aplikacji internetowych, a przynajmniej ich większość, więcej odczytuje z bazy danych, niż do niej zapisuje. UPDATE i INSERT stanowią mniejszość w porównaniu z liczbą wykonywanych SELECTów. Wydajność pobierania rekordów z bazy ma więc dość duże znaczenie.

Ogólnie

Zastanówmy się, jak MySQL pobiera dane z tabeli, jeśli wybrana tabela nie posiada indeksów na polach które uwzględnimy w zapytaniu (zarówno w JOIN jak i WHERE lub ORDER BY). Dla każdego zapytania skanuje od początku wszystkie rekordy w tabeli, aż warunek nie zostanie znaleziony. Jeśli nie uwzględnimy LIMIT, MySQL będzie skanował dalej, aż do końca tabeli. Co to dla nas oznacza? Wyjaśnię na przykładzie.

Załóżmy, że mamy tabelę item składającą się z 3 pól: X, Y, Z. W tabeli znajduje się w sumie milion rekodów. Każde wydanie polecenia

SELECT * FROM item WHERE X='{wartość}' AND Y='{wartość}' AND Z='{wartość}'

spowoduje przeszukanie wszystkich rekordów (1 000 000 odczytów) nawet w sytuacji, gdy tylko jeden wiersz zawiera interesującą nas wartość. Jeśli wiem, ile rekordów może spełniać warunek, samo dodanie polecenia LIMIT może przyspieszyć zapytanie:

SELECT * FROM item WHERE X='{wartość}' AND Y='{wartość}' AND Z='{wartość}' LIMIT 1

spowoduje znalezienie pierwszego rekordu spełniającego warunek, bez konieczności szukania do końca tabeli. Jeśli poszukiwany rekord jest na początku tabeli, przyrost wydajności może być zauważalny.

Niestety, LIMIT to za mało aby szybko pobierać dane. Potrzebny jest inny mechanizm i nosi on nazwę indeksów. W szczegóły nie warto wnikać, ważne, że indeksy pozwalają na szybki dostęp do rekordów bez konieczności skanowania całej tabeli. Indeksy można zakładać na pojedyncze pola, jak i kilka pól na raz.

Zanim zaczniemy przykłady, warto napisać, jak MySQL wykorzystuje indeksy do wyszukiwania rekordów. W jednym zapytaniu, dla jednej tabeli użyty może być tylko jeden indeks. Jeśli baza może wybrać spośród kilku indeksów, postara się użyć ten o większej mocy, czyli taki, w wyniku którego użycia znajdziemy mniej rekordów.

W przypadku indeksów złożonych z kilku pól, indeks może być użyty tylko, jeśli kolejność warunków w zapytaniu zgadza się z kolejnością pól w indeksie.

Czyli, jeśli na polach X, Y i Z założonych jest indeks złożony, ten indeks będzie mógł być użyty w zapytaniach (między innymi):

SELECT * FROM item WHERE X='{wartość}' AND Y='{wartość}' AND Z='{wartość}'

SELECT * FROM item WHERE X='{wartość}' AND Y='{wartość}'

SELECT * FROM item WHERE X='{wartość}' ,

ale w tych już nie:

SELECT * FROM item WHERE Y='{wartość}'

SELECT * FROM item WHERE Z='{wartość}'

SELECT * FROM item WHERE Y='{wartość}' AND Z='{wartość}'

Generalnie, tworząc indeksy należy uwzględnić kolejność i sposób składania zapytań do danej tabeli i nie zawsze jest to oczywiste.

Na szczęście, MySQL dostarcza narzędzie pozwalające sprawdzić jakie indeksy baza użyje (lub spróbuje użyć) w zapytaniu. Jest to polecenie EXPLAIN. Przykładowo:

EXPLAIN SELECT * FROM item WHERE X='{wartość}' AND Y='{wartość}' AND Z='{wartość}',

Szczegóły opiszę w innym wpisie (może).

Testy

Wreszcie przechodzimy do meritum wpisu, czyli o wpływie jakie indeksy mają na wydajność odczytu z tabel MySQL MyISAM. W tym celu przygotowałem serię testów, które wraz z wynikami przedstawię poniżej.

Na potrzeby testu przygotowałem 'stanowisko' testowe składające się z tabeli zawierającej 4 pola:

  • ID - int(10), klucz główny tabeli
  • X - tinyint(3) zawierający wartości 1-100
  • Y - tinyint(3) zawierający wartości 1-100
  • Z - tinyint(3) zawierający wartości 1-100

Tabela została wypełniona 1 000 000 rekordów w ten sposób, że dla każdej trójki wartości z przedziału 1-100 istnieje dokładnie jeden rekord.

Sama procedura testowa polegała na wykonaniu 1 000 razy zapytania SELECT * FROM item WHERE X='{wartość}' AND Y='{wartość}' AND Z='{wartość}' i zliczeniu łącznego czasu. W kolejnych próbach zmieniała się wyłącznie organizacja indeksów na tabeli, bez zmiany samej procedury testowej.

Wyniki znajdują się w poniższej tabeli:

Konfiguracja indeksów Czas [s]
Brak indeksów 201,5498
Indeks na polu X 36,2083
Indeks na polu Y 36,1640
Indeks na polu Z 37,5764
Indeksy na polach X i Y 9,6394
Indeksy na polach X i Z 9,8326
Indeksy na polach Y i Z 9,3918
Indeksy na polach X, Y i Z 9,4500
Indeks złożony X,Y 0,6300
Indeks złożony X,Z 0,6408
Indeks złożony Y,Z 0,6755
Indeks złożony X,Y i Z 0,292

Wyniki testu powinny być jednoznaczne. W tym konkretnym przykładzie, dobrze założony indeks spowodował wzrost wydajności o 690 raz (z lekkim kawałkiem). A 1 000 000 rekordów w tabeli to nie jest aż tak dużo. Ciekawe są przypadki w których występuje więcej niż jeden indeks prosty. Co prawda MySQL używa tylko jednego, ale dzięki możliwości wyboru pomiędzy nimi przez silnik, wydajność także wzrasta.

Podsumowanie

Podsumowanie może być tylko jedno: pracując na MySQL (a właściwie dowolnej bazie danych) nauczmy się właściwie zakładać indeksy. Może to w znaczący sposób zwiększyć wydajność (choć nie zawsze, ale o tym innym razem).