Jak działa MySQL. MyISAM – Optymalizacje I

Kolejnego posta czas zacząć. Tym razem 0day padł na MySQL i działanie MyISAM.
Nie będę się tutaj rozpisywał o podstawach MySQL mam zamiar jedynie opisać ja działa MyISAM (i to dosyć ogólnikowo) oraz jak optymalizować zapytania i strukturę bazy tak aby wszystko dobrze działało.

Oczywiście mógłbym pisać tylko o optymalizacji i nie wspominać o budowie, jednak w takim wypadku jest to podanie ryby zamiast wędki.
Nie da się tutaj wszystkich możliwych ścieżek opisać, rozumiejąc jednak zasady działania można w prosty sposób optymalizować wszelkie inne zapytania. Nie są to tylko regułki w stylu: będzie szybciej jeżeli użyjesz tego czy tamtego…

Na początek chciałbym przestrzec że nie widzę siebie jako megaeksperta od wszystkiego. Lubię dłubać i szukać. Optymalizacja i uczenie się jak co działa, to moje małe hobby.

No dobrze to może od początku. MySQL ma wiele silników zarządzających danymi. Na dzień dzisiejszy najczęściej używanymi są MyISAM, InnoDB, Cluster. W kolejnych artykułach postaram się umieścić opisy dla kolejnych silników, łącznie z silnikami które są jeszcze w fazie wytwarzania. Samo rozbicie na kilka osobnych artykułów podyktowane jest ich objętością. Nie sposób w kilku sensownych słowach zawrzeć szereg przydatnych informacji dotyczących tak rozbudowanych mechanizmów.

Wracając do MyISAM’a jest to stosunkowo najprostszy i szybki silnik (jeżeli chodzi o pobieranie danych) z omawianej bazy danych.
Kolejne rekordy doklejane są zawsze na końcu pliku, można powiedzieć że struktura wewnętrzna bazy mocno przypomina listę wiązaną.
Powodem jest tego podobieństwa jest to, iż aby dostać się do następnego rekordu należy odczytać informację gdzie takowy się znajduje. Poniżej przedstawiam rysunek przedstawiający strukturę MyISAM w baaardzo dużym uproszczeniu.

Struktura danych w MyISAM

Długość rekordów w bazie może się zmieniać, dlatego w nagłówku każdego z rekordu znajdują się informacje, które umożliwiają wyliczenie położenia kolejnego rekordu. Skutkiem takiej budowy dodawanie danych jest niezwykle szybkie (uogólniając). Nie jest konieczne przechodzenie przez szereg rekordów aby znaleźć miejsce dla nowego rekordu. Jest jeszcze jedna cecha tego silnika. Zmodyfikowane obiekty mogą wymagać przeniesienia ich w nowe miejsce (oczywiście na koniec pliku) tym samym w środku pliku pozostawiana jest informacja iż rekord jest usunięty. Tym samym każdy usunięty rekord cały czas fizycznie istnieje w strukturze tabeli.

Proponuję wykonać test. Stwórzmy sobie najpierw nic nie znaczącą tabelę items w której występują pola item_id, name oraz color.

CREATE TABLE items (item_id INTEGER, name VARCHAR(255), color VARCHAR(255));

Dodajmy rekordy testowe:

INSERT INTO items SET item_id=1, name='aaaaaa', color='red';
INSERT INTO items SET item_id=2, name='bbbbbb', color='red';
INSERT INTO items SET item_id=3, name='cccccc', color='red';
INSERT INTO items SET item_id=4, name='dddddd', color='red';
INSERT INTO items SET item_id=5, name='eeeeee', color='red';


Sprawdzając zajętość tabeli otrzymamy: 100b, czyli po 20b na rekord. Zajętość tabeli można sprawdzić dwojako:
1) wykonując komendę: SHOW TABLE STATUS;
2) Sprawdzając wielkość pliku items.MYD w odpowiednim folderze powiązanym z danymi serwera MySQL.

Wykonajmy zatem kolejne całkiem zwyczajne zapytanie:

DELETE FROM items WHERE item_id=2;

Po sprawdzeniu zajętości okazuje się iż wielkość tabeli nie zmieniła się, Oczywiście SHOW table status wskazuje że w tabeli znajduje się teraz wolna przestrzeń (20B). Cóż oznacza to tylko tyle że rekordy pozostają w tabeli nawet pomimo usunięcia. Cóż można przypuszczać świetna optymalizacja nic się nie dzieje, szybka operacja. Co jednak z zajętym miejscem?
Nasuwa się kolejne pytanie, co dzieje się z rekordem kiedy modyfikacja wykracza poza przestrzeń przeznaczona dla niego. Cóż przekonajmy się. Niedowiarkom proponuję zajrzeć jakimkolwiek edytorem to pliku items.MYD, okaże się iż ciąg ‘aaaaaa’ jest na początku pliku zgodnie z przewidywaniami. Zmodyfikujmy ten własnie rekord rekord:

UPDATE items SET name='111111222222333333444444555555666666777777888888999999' WHERE item_id = 1;

Po ponownym obejrzeniu pliku okazuje się że rekord został przeniesiony na koniec pliku. Wielkość pliku to 164 bajty, wskazywana ilość wolnego miejsca w tabeli to dalej 20B. Błąd? Nie MyISAM aby nie kopiować niepotrzebnie rekordów umożliwia utworzenie wskazania na dane fragmentując rekord (teraz już wiadomo czemu rysunek przedstawiony wyżej był bardzo uproszczony ).

Na rysunku widać skasowany rekord (zaznaczony wartościami 0xFF) oraz rekord, dla którego dane przeniesione zostały na koniec pliku.

Reasumując powyższy fragment opowieści: wstawianie, modyfikacja oraz usuwanie danych z tabel MyISAM owych może być niezwykle szybkie.

Chciałbym w tym miejscu dodać jeszcze jedną uwagę. Skutki budowy tabeli oraz algorytmów modyfikacji rekordów powodują, że w środowisku wielowątkowym/wieloprosesowym, dostęp do zapisu powinien mieć tylko jeden wątek/proces. Co oznacza iż trzeba zablokować całą tabelę na czas modyfikacji. W przeciwnym wypadku dwa osobne procesy mogą pomieszać swoje dane i powstanie niespójność.

Co zatem z wyszukiwaniem? Cóż znając budowę list od razu możemy stwierdzić, że aby dostać się do ostatniego rekordu musimy odwiedzić wszystkie poprzedniki. Jeżeli często będziemy się odwoływać do losowych rekordów a baza będzie duża, cóż nie będziemy zadowoleni.

Odejdźmy na chwilę od głównego tematu i zastanówmy się czym jest optymalizacja. W ogólnym słowa znaczeniu jest to skracanie ścieżek poszukiwań. Nie jest to wszystko ale na razie się na tym zatrzymamy.

Pierwsza rzeczą jaka się ciśnie na palce po tym co zrobiliśmy wcześniej jest posprzątanie nieużytków, czyli ominięcie rekordów usuniętych a tym samym zmniejszenie ilości kroków które trzeba przebyć aby dostać się do wyniku.
W tym celu można wywołać komendę:
OPTIMIZE TABLE;

Po wykonaniu jej zaobserwujemy ze zwolniło się niemalże 40b. To by się mniej-więcej zgadzało. Oczywiście aby teraz dobrać się do ostatniego rekordu potrzebujemy jeden skok mniej.

Pomimo takiego zabiegu każdy programista powinien stwierdzić że lista nie bardzo przydaje się do wyszukiwania obiektów. I ma rację.
Kolejnym krokiem przy skracaniu ścieżek przeszukiwań jest zmiana struktury. Przykładowo jeżeli często będziemy wyszukiwać po kolumnie item_id przydało by się ułożyć rekordy w taki sposób aby nie musieć odwiedzać wszystkich by znaleźć ten właściwy. Cóż sama zmiana rekordów również nie byłaby na rękę, gdyby tak się stało to co należałoby zrobić gdyby konieczne byłoby skrócenie ścieżek również dla innej kolumny?
Rozwiązaniem tutaj są indeksy. Dla struktury MyISAM dostępny jest tylko jeden typ indeksu (i dobrze, będzie mniej pisania). Indeks ma strukturę B+drzewa. Nie będę się tutaj rozpisywał jak to dokładnie wygląda zainteresowanych odsyłam do książek o algorytmach i strukturach danych lub do wikipedii. Powiem w skrócie iż B+drzewo jest drzewem n-arnym co oznacza, że na jednym liściu znajduje się wiele kluczy i każdy węzeł może mieć n-potomków. Każdy klucz w indeksie wskazuje na właściwy powiązany rekord w pliku *.MYD. Indeksy przechowywane są w pliku *.MYI.
Po utworzeniu indeksu wyszukiwanie elementów może się skrócić o rzędy wielkości, np z 1000000 porównań można zejść nawet do 3.
Ładne porównanie prawda?

Cóż jednak nie wszystko złoto co się świeci. Nie ma rozwiązań doskonałych i wszystko ma swoje ograniczenia.
O ile wyszukiwanie danych w takiej strukturze jest niezwykle szybkie, o tyle modyfikacja struktury oraz budowa drzewa jest dosyć skomplikowana i często wiąże się z wielokrotnym kopiowaniem danych w pamięci. Jeżeli więc częściej będą wykonywane modyfikacje klucza aniżeli wyszukiwania optymalizacja zda się na nic. Nawet przyniesie szkody w postaci większych opóźnień czasowych.

Czy zatem jesteśmy straceni, skazani na żółwie tempo? Oczywiście że nie. Przede wszystkim trzeba wiedzieć do czego potrzebna nam struktura danych. Zazwyczaj znacznie częściej wyszukujemy dane aniżeli je modyfikujemy i właśnie w takim przypadku należy używać struktury MyISAM + indeksy. Jeżeli natomiast częściej wykonywane są modyfikacje rekordów bądź jest to stosunek 1:1 to radziłbym używać innych struktur ale o tym w kolejnych artykułach.

Wracając do tematu. Sam indeks to nie wszystko. Jeżeli nieodpowiednio będzie się tworzyć zapytania to może się okazać że baza wcale ich nie wykorzysta i będą bardziej jak kula u nogi. Zatem jak pisać zapytania?
Główna odpowiedz to: pisz prosto i czytelnie. Jeżeli ty drogi czytelniku będziesz miał problem z zawiłym zapytaniem, to zapewniam Ciebie ze MySQL również.

Kilka prostych zasad na początek:
0) Dobry benchmark nie jest zły. Testuj to co masz zamiar zrobić zanim wypuścisz do użytkowników.
Same zasady nic nie dają, wszystko zależy od wielu czynników o których można napisać obszerną książkę a nie artykuł. Trzeba starać się zrozumieć co się dzieje, jak pracuje MySQL. Tylko w takim wypadku można prawidlowo optymalizować.

1) Unikaj w zapytaniach alternatyw: (SELECT * FROM t1 WHERE c1 = 1 OR c2 = 2);
W wielu przypadkach MySQL nie potrafi poprawnie wykorzystać indeksów. Jeżeli jest konieczne wykorzystanie alternatywy upewnij się że obie kolumny są zawarte w jednym indeksie. Jeżeli to nie przyniosło rezultatu sprawdź czy wykonanie osobno zapytań jest szybsze. Jeżeli tak to można próbować połączyć zapytanie za pomocą unii:

SELECT * FROM t1 WHERE c1 = 1 UNION SELECT * FROM t1 WHERE c2 = 2 ORDER BY c_order;

W takim wypadku robione są osobno dwa zapytania, potem łączone. Wspólny wynik podlega dyrektywom ORDER, GROUP BY, HAVING…

Jeżeli OR dotyczy tej samej kolumny proponuję użyć skladni IN.

2) Z jakiegoś powodu użycie ABS w składni WHERE powoduje 10 krotny spadek wydajności (w mysql >= 4.1).
Zamieniaj zatem ABS na:

SELECT * FROM t1 WHERE c1 IN (-1, 1);

3) Pamiętaj, że jeżeli zapytanie wymaga obliczania go dla każdego wiersza to mysql przeprowadzi full rowscan, czyli nie skorzysta z indeksu. Bardzo prostym przykładem takiego zapytania może być:

SELECT * FROM items WHERE 1 < item_id < 3;

Dla wielu programistów którzy mają możliwość stosowania takiej pisowni w swoich językach (np python), może to być normalny zapis.
Niestety dla MySQL nie jest tym samym czym mogło by się wydawać. Zapytanie to zostanie rozważone w taki sposób:

DLA Każdego wiersza:

X = 1 < item_id; // Tutaj może być prawda lub fałsz (1, 0)
wynik = X < 3;

Oczywiście zapytanie wykona się niby prawidłowo (o ile ostatnim rekordem będzie item_id == 3), jest to jednak przypadek który pozostawiam do rozpatrzenia czytelnikom .
Faktem jest, że MySQL odwiedzi wszystkie rekordy nawet jeżeli item_id będzie unikalnym kluczem głównym (Primary Key).

Jeżeli rozpiszemy to zapytanie prawidłowo:
SELECT * FROM items WHERE 1 < item_id AND item_id < 3;

To w procesie wyszukiwania wykorzystany zostanie indeks prawidłowo.

4) Ostrożnie z podzapytaniami.
Źle zaprojektowane podzapytania mogą podobnie jak w powyższym przypadku być wykonywane dla każdego przeszukiwanego rekordu. Mogą również tworzyć olbrzymie produkty kartezjańskie. Zapewne nie jest to coś co chcielibyśmy osiągnąć.
W dokumentacji MySQL doszukać się można kilku optymalizacji dt. podzapytań:

- ograniczaj maksymalnie ilość wyników otrzymywanych z podzapytania w składni IN(SELECT …);
- nie wykonuj podzapytania dla każdego wiersza:
to będzie wykonane raz:

SELECT (SELECT column1 + 5 FROM t1) FROM t2;

to n razy:

SELECT (SELECT column1 FROM t1) + 5 FROM t2;

- jeżeli podzapytanie zwraca zawsze jeden wiersz używaj znaku równości a nie zakresu:

SELECT * FROM t1 WHERE t1.col_name = (SELECT a FROM t2 WHERE b = some_const);
zamiast:
SELECT * FROM t1 WHERE t1.col_name IN (SELECT a FROM t2 WHERE b = some_const);

5) Sprawdzaj czy adekwatny join nie działa szybciej. Zapytania wiązane joinami często lepiej są optymalizowane. Przykładowo:

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);
można zamienić na:
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.id=t2.id;

Cóż ogólnie podstawy optymalizacji zostały tutaj wyłuszczone. Zapewniam jeszcze że to nie wszystko.
Reszta w kolejnym artykule.

6) Unikaj tworzenia iloczynów kartezjańskich. Polecenie:

SELECT * FROM t1, t2 WHERE t1.col1 = t2.col1 AND t2.col2 > 10

powoduje że:

• Stworzony zostaje produkt kartezjański t1 x t2. Oznacza to iż w pamięci powstaje tabela która przechowuje rekordy w ilości: count(t1) * count(t2).

• Wynik jest wybierany z tabeli powstałej w wyniku stworzenia produktu kartezjańskiego.

Jeżeli tabele t1 i t2 posiadają po 10 000 wierszy, to powstanie tabela o wielkości 100000000 wierszy. Warto jest używać w takich przypadkach instrukcji LEFT JOIN, który jeżeli jest odpowiednio skonstruowany może zmniejszyć ilość wyszukiwanych rekordów nawet 10 000 krotnie.