Jak działa MySQL. MyISAM – Optymalizacje II

Artykuł ten jest kontynuacją artykułu “Jak działa MySQL. MyISAM – Optymalizacje I”. Osobom które nie zapoznały się z wcześniejszym materiałem sugeruję przejrzenie go, w innym wypadku część informacji może się wydawać niespójna.

Poprzednio artykuł 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.

1. 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.
2. 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ć:

3. table – nazwa tabeli na jakiej działa zapytanie.
4. 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:

5. possible_keys: indeksy które mogą zostać wykorzystane w celu znalezienia wyniku.
6. 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ć.
7. 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. 8. ref: mówi nam które kolumny lub stałe posłużyły do wybrania wierszy z tabeli.
9. rows: ważna informacja, mówi ile minimalnie wierzy musi zostać przeszukane aby znaleźć wynik.
10. 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.