MySQL – EXPLAIN – jak używać i jak czytać

W dzisiejszym wpisie będę omawiał użycie polecenia EXPLAIN w MySQL. Dowiemy się jak używać tego polecenia. W jakich sytuacjach może być przydatne, a także jak interpretować otrzymywane odpowiedzi. Dyrektywa EXPLAIN powoduje, że zamiast wykonywać zadane zapytanie zostaną wyświetlone informacje na temat planu wykonania danego zapytania. Do wersji MySQL 5.6.3 możliwe było użycie EXPLAIN tylko do zapytań SELECT. Najnowsze wersje MySQL wspierają dyrektywę EXPLAIN również z zapytaniami UPDATE, INSERT, REPLACE.

W treści będę używał tabel z aplikacji Redmine, bo używamy tego narzędzia w firmie.

Jak użyć polecenia <code>EXPLAIN</code>?

Użycie polecenia EXPLAIN jest bardzo proste i polega na poprzedzeniu zapytania dyrektywą EXPLAIN. Najprostszy przykład użycia EXPLAIN to: EXPLAIN SELECT 1\G, dzięki któremu uzyskujemy następujący wynik:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used

Jak czytać wyniki?

Przyjrzyjmy się poszczególnym elementom otrzymanego wyniku.

Kolumna ID

Kolumna id zawiera numer zapytania, którego dotyczy. W naszym pierwszym przykładzie było oczywiście pojedyncze zapytanie dlatego mamy tylko jeden wiersz w odpowiedzi o id = 1. W przypadku zapytań z podzapytaniami, podzapytania w dyrektywie FROM oraz zapytań z dyrektywą UNION podzapytania numerowane są (zazwyczaj) w kolejności ich występowania w zapytaniu. Przykładowo dla podzapytania:  EXPLAIN SELECT (SELECT 1 FROM issues) FROM journals\G otrzymujemy

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: journals
         type: index
possible_keys: NULL
          key: index_journals_on_user_id
      key_len: 4
          ref: NULL
         rows: 33843
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: issues
         type: index
possible_keys: NULL
          key: issues_project_id
      key_len: 4
          ref: NULL
         rows: 6704
        Extra: Using index

Dla podzapytania pochodnego EXPLAIN SELECT id FROM (SELECT * FROM issues) AS DERIVED\G otrzymujemy:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6704
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: issues
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6704
        Extra: NULL

Dla zapytania z dyrektywą UNION EXPLAIN SELECT 1 UNION SELECT 1\G otrzymujemy

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used
*************************** 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: Using temporary

Przykład z UNION jest najciekawszym jak do tej pory. Możemy zobaczyć, że MySQL zawsze kopiuje wyniki zapytania UNION do tabeli tymczasowej, a następnie z tabeli tymczasowej zwraca nam otrzymany rezultat. Stąd mamy 3, a nie 2 wiersze w ostatnim wyniku.

Kolumna SELECT_TYPE

W kolumnie select_type otrzymujemy informacje o tym jakiego typu jest dane zapytanie. Możliwe wartości to: SIMPLE, SUBQUERY, DERIVED, UNION, UNION RESULT oraz PRIMARY:

• PRIMARY zostaje oznaczona najbardziej zewnętrzna składowa w badanym zapytaniu;
• SIMPLE otrzymujemy jeżeli nie mamy żadnych podzapytań w danym zapytaniu;
• SUBQUERY to podzapytania, które występują w klauzuli;
• SELECT zapytania pierwotnego;
• DERIVED to podzapytania w klauzuli FROM zapytania pierwotnego;
• Typ UNION otrzymują drugie i kolejne części zapytania UNION. UNION RESULT (jak już wcześniej wspomniałem) jest efektem pracy MySQL – zwraca rezultat z tymczasowej tabeli w przypadku zapytań z UNION.

Kolumna TABLE

Kolumna table mówi o tym jakiej tabeli dotyczy dane zapytanie. Odczytując wartości z tej kolumny możemy zobaczyć jaką kolejność optymalizator zapytań MySQL zdecydował się zastosować do danego zapytania. Nie musi to być kolejność zgodna z kolejnością w zapytaniu.

Przykładowo: EXPLAIN SELECT i.id FROM journals j INNER JOIN issues i ON i.id = j.journalized_id INNER JOIN projects p ON i.project_id = p.id\G – otrzymujemy

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: index
possible_keys: PRIMARY
          key: index_projects_on_lft
      key_len: 5
          ref: NULL
         rows: 116
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: ref
possible_keys: PRIMARY,issues_project_id
          key: issues_project_id
      key_len: 4
          ref: xcoding_redmine.p.id
         rows: 47
        Extra: Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: j
         type: ref
possible_keys: journals_journalized_id,index_journals_on_journalized_id
          key: index_journals_on_journalized_id
      key_len: 4
          ref: xcoding_redmine.i.id
         rows: 3
        Extra: Using index

Możemy zauważyć, że w kolumnie table otrzymaliśmy aliasy zastosowane w zapytaniu (nie mamy nazwy tabeli projects, ale alias p). Kolejność zastosowana przez MySQL jest inna niż w zapytaniu i odpowiada drzewu wykonywania zapytania. W przypadku gdy zapytanie dotyczy tabel tymczasowych możemy zobaczyć np. table: <union1,2> tak jak w przykładzie z UNION. Oznacza to, że jest to tabela tymczasowa z podzapytań o ID 1 i ID 2 z planu wykonywania zapytania. Możemy też zobaczyć np. table: . Będzie to oznaczało, że to zapytanie korzysta z wyniku zapytania w podzapytaniu o ID 2.

Kolumna TYPE

Kolumna type pokazuje w jaki sposób MySQL musi przetworzyć wiersze w tabeli.

• ALL – oznacza, że należy przeskanować wszystkie wiersze w tabeli,
• INDEX – MySQL musi przeskanować wszystkie wiersze w tabeli, ale może wykonać to w porządku w jakim jest przechowywane w indeksie, co może zaoszczędzić czas wykonywania zapytania gdyż nie trzeba go już sortować (ale dalej musimy uzyskać dostęp do pełnej tabeli na dysku),
• RANGE – MySQL może użyć indeksu do przeszukania tylko potrzebnych w danym zapytaniu wartości,
• REF – MySQL może przeszukać jedynie indeks do znalezienia potrzebnych wartości. Może występować przypadek REF_OR_NULL gdzie potrzebny jest jeszcze dodatkowy dostęp do pobrania wartości odpowiadających NULL,
• EQ_REF – MySQL zadecydował do użycia indeksu głównego lub unikalnego do zwrócenia wartości. Jest to bardzo szybki dostęp, bo przy natrafieniu na wartość MySQL może ją zwrócić bez szukania dalszych, także dopasowanych wartości,
• CONST – zazwyczaj występuje w przypadku użycia w klauzuli WHERE wartości z indeksu głównego tabeli.

Przykładowo: EXPLAIN SELECT id FROM issues WHERE id = 7555\G – otrzymujemy:

************************* 1. row *************************
           id: 1
  select_type: SIMPLE
        table: issues
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL

• NULL – oznacza, że wartość może być określona już na etapie optymalizacji zapytania. Najczęstszym przykładem jest wartość maksymalna z pola, które jest zaindeksowane w danej tabeli.

Kolumna POSSIBLE_KEYS

Ta kolumna mówi o tym, które indeksy mogą być użyte przy obliczaniu wyniku zapytania. Jest tworzona na początku procesu optymalizacji dlatego lista może być dość długa i nie być pomocna przy analizie całości zapytania (jak w przykładzie wyjaśniającym kolumnę TABLE).

Kolumna KEY

Kolumna key wskazuje, który indeks będzie użyty przez MySQL podczas wykonywania zapytania. Możliwa jest sytuacja, w której mamy pustą kolumnę possible_keys, ale i tak zostanie użyty indeks.

Przykładowo: EXPLAIN SELECT id FROM issues\G – otrzymujemy:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: issues
         type: index
possible_keys: NULL
          key: issues_project_id
      key_len: 4
          ref: NULL
         rows: 6704
        Extra: Using index

Kolumna KEY_LEN

Kolumna key_len podaje informacje ile bajtów indeksu zostanie użytych przez MySQL podczas wykonywania zapytania. Szczególnie przydatna może być w przypadku wielokolumnowych indeksów. Informacja o ilości użytych bajtów może powiedzieć jaka część indeksu została użyta (do wersji MySQL 5.5 może zostać użyta tylko lewa część indeksu lub jego całość). W przypadku gdy indeks obejmuje kolumny tekstowe należy też brać pod uwagę charset jaki jest w nich użyty. Podawana tutaj informacja zawsze oznacza maksymalną wielkość indeksu (lub jego części), bez względu na to, czy obecnie wypełnione w tabeli wartości wymagają aż tak dużej ilości pamięci do ich przechowania.

Kolumna REF

Ta kolumna informuje o tym które kolumny z indeksów podany w kolumnie key zostaną użyte podczas wykonywania zapytania (patrz przykład z kolumny table).

Kolumna ROWS

Kolumna rows podaje informacje ile średnio rekordów w bazie danych będzie należało przejrzeć żeby wydobyć dane niezbędne w zapytaniu. Jeżeli mamy do czynienia ze złożonym zapytaniem to informacja dotyczy pojedynczego wykonania pętli dla JOIN. Informacje te mogą być mocno nieprecyzyjne.

Przykładowo zapytanie: SELECT * FROM issues LIMIT 1\G zwróci wyniki już po znalezieniu pierwszego rekordu, a wynik jest następujący (w tabeli jest 6704 rekordów):

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: issues
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6704
        Extra: NULL

Kolumna EXTRA

Najważniejsze wartości jakie mogą pojawić się w tej kolumnie:

• Using index – podczas wykonywania zapytania użyty będzie indeks zamiast odczytu bezpośrednio z tabeli
• Using where – MySQL będzie musiał przefiltrować wyniki otrzymane z silnika bazy danych. Często jest to informacja, którą warto przemyśleć pod kątem stworzenia nowego (lub modyfikacji istniejących) indeksów.
• Using temporary – MySQL użyje tabeli tymczasowej do sortowania wyników
• Using filesort – wymagane będzie użycie sortowania po wyciągnięciu danych z bazy zamiast wyciągnięcia ich w wymaganym porządku

Podsumowanie

Dyrektywa EXPLAIN jest pierwszym narzędziem po jakie powinniśmy sięgnąć w przypadku problemów z wydajnością zapytań SQL lub po prostu w procesie optymalizacji zapytań. Pozwala ona zrozumieć jak MySQL będzie przetwarzał zapytanie, dostarcza informacji o użytych indeksach (lub ich braku) , kolejności przetwarzania podzapytań czy łączenia elementów z wielu tabel.

Mam nadzieję, że wyjaśnione w artykule składowe polecenia EXPLAIN zachęcą do zapoznania się bardziej dogłębnie z możliwościami tej dyrektywy i przyczynią się do zwiększenia szybkości działania Waszych baz danych.