Szybkość działania dzisiejszych, ogromnych baz danych tylko pozornie zależy od użycia drogiego sprzętu i minimalizacji ilości zapytań. Dobrze zoptymalizowane kwerendy zapewnią Ci sprawnie działający program.
Część I
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.
Część II
Poprzednią część zakończyłem informacją jak budować zapytania aby stworzony indeks został wykorzystany. Przemilczałem jednak pewne pchające się na usta pytanie. Skąd mamy wiedzieć czy MySQL rzeczywiście wykorzystał indeks, oraz czy wykorzystał indeks właściwy. Ten artykuł chciałbym przeznaczyć właśnie na ten cel.
MySQL prawdę ci powie… Aby dowiedzieć się w jaki wykonywana jest robota najlepiej spytać tego kto pracuje a nie tego kto pracę zleca. MySQL daje nam taką możliwość. Polecenie EXPLAIN potrafi wyjaśnić jak zbudowana jest tabela, oraz jak wykona się dane zapytanie.
Zacznijmy zatem od prostszej wersji polecenia EXPLAIN:
EXPLAIN [nazwa tabeli]
Działa identycznie jak:
DESCRIBE [nazwa tabeli]
Przykładowy wynik dla "EXPLAIN items":
*************************** 1. row ***************************
Field: item_id
Type: int(11)
Null: NO
Key:
Default: 0
Extra:
*************************** 2. row ***************************
Field: name
Type: varchar(255)
Null: YES
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: color
Type: varchar(255)
Null: YES
Key:
Default: NULL
Extra:
Jak widać ta wersja EXPLAIN’a pozwala nam stwierdzić z jakich kolumn składa się tabela, jest to synonim poleceń “DESCRIBE” oraz “SHOW COLUMNS FROM”.
Tym razem interesuje nas bardziej polecenie: EXPLAIN SELECT… i nim się teraz zajmiemy.
Ta wersja zapytania informuje nas o tym jak zachowa się serwer przy wykonywaniu danego zapytania. Zacznijmy od prostego zapytania:
mysql> EXPLAIN SELECT * FROM items;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: items
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
Co nam mówi ten rezultat? Ano, wiele.
- id - identyfikator zapytania. Jest to numer sekwencyjny widać co w jakiej kolejności się wykonuje. Z działania wynika iż wyższy numer oznacza wcześniejsze wykonanie komendy.
- selelect_type - Informuje nas jakiego typu jest zapytanie.
- SIMPLE (proste zapytanie, brak unii i podzapytań),
- PRIMARY (główne zapytanie - w przypadku gdy mamy podzapytanie),
- UNION (gdy korzystamy z unii),
- DEPENDENT UNION (gdy podzapytanie jest unią zależną od głównego zapytania),
- UNION RESULT (wynik unii),
- SUBQUERY (podzapytanie),
- DEPENDENT SUBQUERY (podzapytanie zależne od głównego zapytania),
- DERIVED (pobieramy dane nie z tabeli ale z podzapytania)
Tutaj może się pojawić:
table - nazwa tabeli na jakiej działa zapytanie.type - informuje w jaki sposób wyszukiwane są wyniki.- system: tabela ma tylko jedna kolumnę. Specjalny przypadek typu ‘const’;
- const: Tabela ma co najwyżej jeden pasujący wynik (np., gdy szukamy po unikalnym kluczu głównym);
- eq_ref: Dla każego wiersza z tabeli pierwszej (t1), wybrana kolumna (k1) jest porównywana z każdym wierszem tabeli drugiej po wskazanej kolumnie (k2).
- ref: wyświetlane wtedy gdy jest działanie na zakresach wyników lub gdy możliwy jest wynik większy niż 1 wiersz (klucz po którym odbywa się wyszukiwanie nie jest unikalny).
- ref_or_null: podobnie jak wyżej ale dodatkowo null też wchodzi w zbiór wyszukiwań.
- index_mege: użyty został klucz wielokolumnowy.
- unique_subquery: powiązany z podzapytaniem w którym używany jest klucz główny (primary key). Oznacza iż podzapytanie zwraca wynik do operatora ‘IN’.
- index_subquery: podobnie jak wyżej z tą różnicą iż klucz po którym następuje wyszukiwanie nie jest unikalny.
- range: działanie na zakresach wyników.
- index: podobnie jak zjawisko opisane poniżej, z tą różnicą iż wszystkie dane w tym wypadku pobierane są z klucza.
- ALL: występuje wtedy gdy w celu wyszukania wyniku należy odwiedzić wszystkie wiersze tabeli. Oczywiście najmniej optymalne i zazwyczaj tego wyniku będziecie się chcieli wystrzegać.
Możliwe wartości i ich znaczenie:
possible_keys: indeksy które mogą zostać wykorzystane w celu znalezienia wyniku. key: klucz, który został wybrany do wyszukania wyniku. Pragnę tutaj nadmienić iż nie zawsze MySQL wybiera najlepszą możliwość. Ta informacja pozwala nam jednak dowiedzieć się z jakich innych indeksów możemy próbować korzystać.key_len: długość klucza który został wybrany do wykorzystania w drodze wyszukiwania. Pozwala wywnioskować z ilu części klucza złożonego MySQL korzysta podczas tego zapytania.8. ref: mówi nam które kolumny lub stałe posłużyły do wybrania wierszy z tabeli.rows: ważna informacja, mówi ile minimalnie wierzy musi zostać przeszukane aby znaleźć wynik.extra: dodatkowe informacje. Tutaj może się pojawić:- distinct: informacja iż mysql pominie podobne wyniki;
- not exists: mysql mógł przeprowadzić operację left join, ale nie był zdolny wyciągnąć informacji z drugiej tabeli.
- range checked for each record (index map: #): mysql nie znalazł odpowiednich indeksów, możliwe jest jednak skorzystanie z innych indeksów. MySQL sprawdza zatem przy każdym wierszu czy może skorzystać z typu range lub index_merge w celu zoptymalizowania wyszukiwania.
- using index: wyniki zostały wyszukane poprzez indeks, dane również zostały pobrane z indeksu. Nie było potrzeby wykonywać odczytu wszystkich informacji z wiersza.
- using temporary: w celu znalezienia wyniku utworzona została tabela tymczasowa.
- using where: informuje iż składnia WHERE została użyta to ograniczenia danych wysyłanych klientowi.
- Using sort_union(…) , Using union(…) , Using intersect(…): informuje w jaki sposób wykorzystane zostały indeksy wiązane.
- using index for group-by: informacja iż group by wykorzystał indeks. Niezwykle rzadki widok jednak niesamowicie piękny.
Cóż widać iż sam EXPLAIN może nam bardzo wiele powiedzieć. Prawdopodobnie wiele z rzeczy opisanych powyżej niewiele dla was znaczy, dlatego pozwalam sobie poniżej przedstawić kilka zapytań generujących specyficzne wartości w EXPLAIN’ie. Dla celów testowych pozwoliłem sobie stworzyć tabele, może nie mają dużego sensu ale pozwalają osiągnąć spodziewane wyniki. Bazę można pobrać stąd wykorzystać lokalnie w celu zabawy z mysql.
SIMPLE + ALL.
EXPLAIN SELECT * FROM items;
Co oznacza tego typu kod? Generalnie jest to proste zapytanie, brak joinow brak podzapytań. Niestety zażądaliśmy wszystkich rekordów więc serwer przejrzał każdy rekord (type: ALL). Jeżeli zdejmiecie klucz z tabeli i wyszukacie danych to podobnie jak tutaj zobaczycie ALL ponieważ konieczne będzie fizyczne odwiedzenie wierszy w celu znalezienia poprawnego wyniku.
PRIMARY + DEPENDENT SUBQUERY.
mysql> SELECT * FROM groups WHERE group_id < SOME (SELECT item_id FROM items) \G
*************************** 1. row ***************************
group_id: 1
group_name: Jabłka
gtype: food
*************************** 2. row ***************************
group_id: 2
group_name: Gruszki
gtype: food
*************************** 3. row ***************************
group_id: 3
group_name: Banany
gtype: food
*************************** 4. row ***************************
group_id: 1
group_name: telefony sznurowe
gtype: telco
*************************** 5. row ***************************
group_id: 2
group_name: Telefony komórkowe
gtype: telco
5 rows in set (0,02 sec)
Z wyniku odczytać można iż najpierw wykonywało się zapytanie pobierające dane z groups, ponieważ od jego wyniku zależą wyniki głównego zapytania (DEPENDENT QUERY). Jak widać MySQL nie skorzystał z kluczy (bo ich nie ma) i musiał przejrzeć wszystkie rekordy istniejące w obu tabelach (type: ALL, wartości w kolumnie rows są zgodne z ilością rekordów w obu tabelach).
Podrasujmy nieco tabele dodając im klucze. Zacznijmy od groups, dodam tam na razie klucz unikalny:
mysql> ALTER TABLE groups ADD UNIQUE `group` (group_id, gtype);
Query OK, 5 rows affected (0,03 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT * FROM items WHERE group_id IN (SELECT group_id FROM groups WHERE gtype = ‘food’)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: items
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: groups
type: unique_subquery
possible_keys: group
key: group
key_len: 5
ref: func,const
rows: 1
Extra: Using index; Using where
2 rows in set (0,00 sec)
Jak widać po dodaniu klucza MySQL przewiduje mniejszą ilość rekordów do odwiedzenia.
Jak pisałem w poprzednim rozdziale zależność DEPENDENT SUBQUERY bywa niebezpieczna. Wielokrotnie spotkałem się z sytuacją w której serwer MySQL zapętlał się przy takim zapytaniu. Jest to wada serwera, ma zostać ponoć rozwiązana w wersji 5.1, serwer znajduje nieistniejącą zależność pomiędzy zapytaniem wewnętrznym a zewnętrznym i wykonuje podzapytanie dla każdego wiersza zapytania głównego.
Przy adekwatnie zachowującym się zapytaniu typu LEFT JOIN problemów nie zaobserwowałem.
Zapytania wykonywały się w czasie ~0.02sec.
W każdym przypadku zauważenia iż MySQL traktuje zapytanie jako DEPENDEND SUBQUERY zalecam użycie komendy EXPLAIN EXTENDED:
mysql> EXPLAIN EXTENDED SELECT * FROM items WHERE group_id IN (SELECT group_id FROM groups WHERE gtype = ‘food’);
Wynik zapytania jest podobny, ale ważna jest inna rzecz która ludzie zazwyczaj pomijają:
2 rows in set, 1 warning (0,00 sec)
Ja widać MySQL zgłasza ostrzeżenie i warto spojrzeć co się w nim kryje:
mysql> SHOW warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `bochen`.`items`.`item_id` AS `item_id`,`bochen`.`items`.`group_id` AS `group_id`,`bochen`.`items`.`name` AS `name`,`bochen`.`items`.`price` AS `price` from `bochen`.`items` where <in_optimizer>(`bochen`.`items`.`group_id`,<exists>(((`bochen`.`items`.`group_id`) in groups on group where (`bochen`.`groups`.`gtype` = _latin2'food'))))
1 row in set (0,00 sec)
Jeżeli przedstawione zapytanie jest zgodne z Waszymi założeniami to można korzystać z podzapytania, jeżeli macie wątpliwości zastanówcie się nad zmianą zapytania na JOIN. Warto jednak zapamiętać: Przy DEPENDEND QUERY przygotuj się na problemy.
UNION + UNION RESULT.
Rozpatrzmy takie oto zapytanie:
mysql> explain select * from items where item_id = 3 OR price=15\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: items
type: ALL
possible_keys: item_id
key: NULL
key_len: NULL
ref: NULL
rows: 7
Extra: Using where
1 row in set (0,00 sec)
Jak widać jest możliwość skorzystania z klucza item_id, a jednak serwer nie korzysta z niego. Dlaczego? Bo i tak nic by mu to nie dało.
Aby sprawdzić które artykuły w bazie mają cenę 15 musi i tak przeprowadzić Full Row Scan. Jak go zatem zmusić do wykorzystania indeksu i przy okazji zoptymalizować zapytanie? Można skorzystać z UNII:
mysql> explain select * from items where item_id = 3 union select * from items where price=15\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: items
type: ref
possible_keys: item_id
key: item_id
key_len: 4
ref: const
rows: 2
Extra:
*************************** 2. row ***************************
id: 2
select_type: UNION
table: items
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7
Extra: Using where
*************************** 3. row ***************************
id: NULL
select_type: UNION RESULT
table:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
Jak widać podczas pierwszego zapytania mysql skorzystał z indeksu i odwiedził tylko dwa rekordy, niestety na potrzeby drugiego musiał odwiedzić wszystkie. Dodanie indeksu na pole ‘price’ tym razem załatwia sprawę:
mysql> explain select * from items where item_id = 3 union select * from items where price=15\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: items
type: ref
possible_keys: item_id
key: item_id
key_len: 4
ref: const
rows: 2
Extra:
*************************** 2. row ***************************
id: 2
select_type: UNION
table: items
type: ref
possible_keys: price
key: price
key_len: 4
ref: const
rows: 2
Extra: Using where
*************************** 3. row ***************************
id: NULL
select_type: UNION RESULT
table:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
3 rows in set (0,00 sec)
Dla dociekliwych. EXPLAIN EXTENDED wyświetla ostrzeżenie jednak do proponowanego zapytania nie mam zastrzeżeń.
Mimo wszystko widać że po wprowadzeniu indeksu oraz wykorzystaniu polecenia UNION mysql nie musi odwiedzać wszystkich rekordów.
DEPENDENT UNION.
Przedstawię zupełnie abstrakcyjny przykład:
mysql> explain extended SELECT groups.group_id FROM groups WHERE groups.group_id IN (select group_id from items where item_id = 3 union select group_id from items where price=15)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: groups
type: index
possible_keys: NULL
key: group
key_len: 5
ref: NULL
rows: 5
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: items
type: eq_ref
possible_keys: item_id
key: item_id
key_len: 8
ref: const,func
rows: 1
Extra: Using where; Using index
*************************** 3. row ***************************
id: 3
select_type: DEPENDENT UNION
table: items
type: ref
possible_keys: price
key: price
key_len: 4
ref: const
rows: 2
Extra: Using where
*************************** 4. row ***************************
id: NULL
select_type: UNION RESULT
table:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
4 rows in set, 1 warning (0,00 sec)
Cóż przy czymś takim uważałbym tak samo jak przy DEPENDEND SUBQUERY. Osobiście nie spotkałem się wśród rzeszy zapytań które przerabiałem z czymś takim ani z problemami przez nie wygenerowanymi, jednakże samo założenie ZALEŻNOŚCI pomiędzy zewnętrznym a wewnętrznym zapytaniem jest czynnikiem niepokojącym.
SUBQUERY.
mysql> explain select group_id from groups where group_id > ALL (SELECT items.group_id FROM items) AND gtype='food'\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: groups
type: index
possible_keys: NULL
key: group
key_len: 5
ref: NULL
rows: 5
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: SUBQUERY
table: items
type: index
possible_keys: NULL
key: item_id
key_len: 8
ref: NULL
rows: 7
Extra: Using index
2 rows in set (0,00 sec)
W sumie nie ma czego tłumaczyć, najpierw wykonuje się zapytanie wewnętrzne i pobiera identyfikatory group_id, potem zapytanie zewnętrzne sprawdza które identyfikatory w tabeli groups są większe aniżeli te istniejące w items. Explain extended nie czaruje i pokazuje wszystko zgodnie z założeniami.
DERIVED.
mysql> EXPLAIN EXTENDED SELECT item_id, group_id, items FROM (SELECT * FROM items WHERE item_id < 2) AS `a`\G SHOW WARNINGS\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra:
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: items
type: range
possible_keys: item_id
key: item_id
key_len: 4
ref: NULL
rows: 3
Extra: Using where
2 rows in set, 1 warning (0,01 sec)
Wyciągnięcie danych z ‘tabeli’ która jest wynikiem zapytania, czyli (przykładowo) obcięcie niepotrzebnych kolumn.
Tyle tematem rożnych typów zapytań i niebezpieczeństw z nimi związanych, kolejnym krokiem jest zapoznanie się z kosztem powiązanym z kolumna `type` otrzymana w wyniku polecenia EXPLAIN.