Pracując z bazami danych, na pewno każde z nas spotkało się z problemem długo działającego zapytania. Wtedy zastanawiamy się, co spowodowało, że one tak długo trwa i co zrobić aby działało lepiej. Pomóc nam w tym może polecenie EXPLAIN. Poniżej opisze, jak czytać informację, którą zwraca nam ta instrukcja. Jeśli jednak informacje tutaj są niewystarczająco, odsyłam do dokumentacji, która znajdziecie tutaj.
Jakie informacje możemy wyciągnąć?
Przykładowy wynik działania:
Co oznaczają poszczególne kolumny?
Typy złączeń (kolumna type)
Kolumna type instrukcji EXPLAIN informuje nas w jaki sposób łączymy tabele. Jest to bardzo ważna informacja dlatego przyjrzymy się im uważnie.
Instrukcja EXPLAIN ma jeszcze dwa rozszerzenia, ale które może omówimy innym razem:
Mam nadzieję, że ta informacje pomoże wam w optymalizacji zapytań. Na pewno jest to pierszy krok aby zrozumiec jak działa optymalizator MySQL-a.
Powodzenia
Jakie informacje możemy wyciągnąć?
- Plan wykonania zapytania przez optymalizator
- które tabele są wykorzystane
- jakie index-y są wykorzystane
- ile wierszy zostanie przeszukanych
- W jaki sposób tabele są ze sobą łączone i w jakiej kolejności
Przykładowy wynik działania:
mysql (root@test)> explain select distinct c2, c3 from test \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using temporary 1 row in set (0.00 sec)
Co oznaczają poszczególne kolumny?
- id: Jest identyfikatorem polecenia SELECT. Może być wartością NULL jeśli odpowiada wynikowi działania polecenia UNION.
mysql (root@test)> explain select * from test UNION select * from test2 \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: test type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: *************************** 2. row *************************** id: 2 select_type: UNION table: test2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: *************************** 3. row *************************** id: NULL select_type: UNION RESULT table: <union1,2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: 3 rows in set (0.00 sec)
- select_type: typ select-u z jakim mamy do czynienia
- SIMPLE - zapytanie SELECT, które nie jest UNION-em lub podzapytaniem
- PRIMARY: najbardziej zewnętrzne zapytanie
- UNION: drugie albo ostatnie polecenie SELECT jest UNION-em
- DEPENDENT UNION: drugie albo ostatnie polecenie SELECT jest UNION i zależy od zewnętrznego zapytania
- UNION RESULT: wynik polecenia UNION
- SUBQUERY: pierwsze zapytanie jest podzapytaniem
- DEPENDENT SUBQUERY: pierwsze zapytanie jest podzapytaniem i zależy od zapytania zewnętrznego.
mysql (root@test)> explain select * from test where c1 IN (select c1 from test2) \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: test type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: test2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using where 2 rows in set (0.00 sec)
- DERIVED: pochodzi z zapytania SELECT czyli po FROM mamy podzapytanie
- UNCACHEABLE SUBQUERY: zapytanie dla którego wynik nie może być zkeszowany i za każdym razem jest wykonywane dla każdego wiersza z zewnętrznego zapytania. W porównaniu do DEPENDENT SUBQUERY, zapytanie jest wykonywane nawet dla tych samych wartości z zapytania zewnętrznego.
- UNCACHEABLE UNION: drugi albo ostatni select w poleceniu UNION, dla którego istnieje UNCACHEABLE SUBQUERY.
- table: Nazwa tabeli albo alias tabeli do której odnosi się opis. Może to być także <unionM,N> (odnosi się wierszy odpowiadającym UNION z id: M i N), <derivedN> (odnosi się do wierszy z id: N)
- type: typ łączenia(Poniżesz omówię każdy z nich)
- possible_keys: mówi, które index-y mogą być użyte do znalezienia wierszy w tabeli. Jeśli kolumna zawiera wartość NULL, nie ma index-ów, które mogły by być użyte. Ważne jest to w jakiej kolejności są łączone tabele ze sobą dlatego w praktyce niektóre z index-ów nigdy nie mogą nie być brane pod uwagę.
- key: identyfikuje, który z index-ów został użyty. Istnieje możliwość, że klucz (index) został wybrany mimo iż w kolumnie possible_keys go nie było. W tej sytuacji nie istnieje index przy pomocy, które można by wybrać wiersze ale wszystkie kolumny wybierane przez zapytanie są w kolumnach innego index-u:
CREATE TABLE `test` ( `c1` int(11) NOT NULL, `c2` varchar(255) DEFAULT NULL, `c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`c1`), KEY `idx_c2` (`c2`), KEY `idx_c2_c3` (`c2`,`c3`), KEY `idx_c3_c2` (`c3`,`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `test2` ( `c1` int(11) NOT NULL, `c2` varchar(255) DEFAULT NULL, `c3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', KEY `idx_c2` (`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql (root@test)> explain select * from test where c1 IN (select c1 from test2 where c2 = 'test') \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: test type: index possible_keys: NULL key: idx_c2_c3 key_len: 772 ref: NULL rows: 4 Extra: Using where; Using index *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: test2 type: ref possible_keys: idx_c2 key: idx_c2 key_len: 768 ref: const rows: 3 Extra: Using where 2 rows in set (0.00 sec)Lub
mysql (root@test)> explain select * from test where c1 like 1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: index possible_keys: PRIMARY key: idx_c2_c3 key_len: 772 ref: NULL rows: 4 Extra: Using where; Using index 1 row in set (0.00 sec)
- key_len: identyfikuje długość klucza
- ref: mówi, które kolumny lub stałe są porównywane do index-u w kolumnie key aby zwrócić wynik zapytania
- rows: przypuszczalna liczba wierszy, które MySQL musi sprawdzić do wykonania zapytania. Dla InnoDB wartość ta estymowana i może być niedokładna
- Extra: dodatkowa informacja jak MySQL zwraca wynik jak użycie tabeli tymczasowej (Using temporary), użycie warunku WHERE (Using where), użycie indexu dla grupowania (Using index for group-by). Więcej informacji znajdziecie w dokumentacji tutaj.
Typy złączeń (kolumna type)
Kolumna type instrukcji EXPLAIN informuje nas w jaki sposób łączymy tabele. Jest to bardzo ważna informacja dlatego przyjrzymy się im uważnie.
- system: Tabela posiada tylko jeden wiersz
- const: Tabela posiada co najwyżej jeden pasujący wiersz. Zazwyczaj dotyczy szukania po kluczu głównym lub innych wartości unikalnych:
mysql (root@test)> explain select * from test where c1 = 1 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: 1 row in set (0.00 sec)
- eq_ref: Dokładnie jeden wiersz jest czytany z tabeli przy kombinacji wierszy z poprzedniej tabeli. Jest to najlepszy (najbardziej optymalny) tym złączenia.
mysql (root@test)> explain select * from test a, test b where a.c1 = b.c1 AND a.c2 = 'test' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a type: ref possible_keys: PRIMARY,idx_c2,idx_c2_c3 key: idx_c2_c3 key_len: 768 ref: const rows: 3 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: b type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: test.a.c1 rows: 1 Extra: 2 rows in set (0.00 sec)
- ref: Wszystkie wiersze z wartości index-u są czytane z tej tabeli dla wszystkich wierszy z poprzedniej tabeli. Jest to dobry typ łączenia jeśli klucz, który został użyty, dopasowuje tylko kilka wierszy.
mysql (root@test)> explain select * from test a, test2 b where a.c1 = b.c1 AND a.c2 = 'test' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a type: ref possible_keys: PRIMARY,idx_c2,idx_c2_c3 key: idx_c2_c3 key_len: 768 ref: const rows: 3 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: b type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using where; Using join buffer 2 rows in set (0.00 sec)
- fulltext: Łączenie odbywa się przy użyciu index-u FULLTEXT.
- ref_or_null: Ten typ łączenia jest bardzo podobny do ref z tą różnicą, że MySQL dodatkowo przeszukuje wiersze, które zawierają wartości NULL.
mysql (root@test)> explain select * from test where c2 = 'test' or c2 IS NULL \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: ref_or_null possible_keys: idx_c2,idx_c2_c3 key: idx_c2_c3 key_len: 768 ref: const rows: 4 Extra: Using where; Using index 1 row in set (0.00 sec)
- index_merge: Został użyty 'Index Merge'. Jest to metoda do otrzymania wierszy z kilku skanowań typu 'range' i połączenia ich wyniku do jednego. Więcej możecie przeczytać tutaj.
- unique_subquery: Zastępuje ref typ łączenia przy warunkach z użyciem IN podzapytań.
mysql (root@test)> explain select * from test where c1 IN (select c1 from test2 where c2 = 'test') \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: test type: index possible_keys: NULL key: idx_c2_c3 key_len: 772 ref: NULL rows: 4 Extra: Using where; Using index *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: test2 type: unique_subquery possible_keys: PRIMARY,idx_c2 key: PRIMARY key_len: 4 ref: func rows: 1 Extra: Using where 2 rows in set (0.01 sec)
- index_subquery: Jest bardzo podobne do typu powyżej ale działa dla indexów, które nie są unikalne (nie klucze główne i nie z klauzurą unikalności).
- range: Szuka wierszy, gdzie wartości są w danych przedziałach.
mysql (root@test)> explain select * from test where c2 like 'test%' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test type: range possible_keys: idx_c2,idx_c2_c3 key: idx_c2_c3 key_len: 768 ref: NULL rows: 3 Extra: Using where; Using index 1 row in set (0.00 sec)
- index: Jest bardzo podobny do typu ALL z tym wyjątkiem, że to drzewo index-u jest przeszukiwane, a nie dane, aby zwrócić wynik.
- ALL: Pełne skanowanie tabeli dla każdej kombinacji wierszy z poprzedniej tabeli. Najgorszy scenariusz przeszukiwania danych.
Instrukcja EXPLAIN ma jeszcze dwa rozszerzenia, ale które może omówimy innym razem:
- EXPLAIN EXTENDED
- EXPLAIN PARTITIONS
Mam nadzieję, że ta informacje pomoże wam w optymalizacji zapytań. Na pewno jest to pierszy krok aby zrozumiec jak działa optymalizator MySQL-a.
Powodzenia
Brak komentarzy:
Prześlij komentarz