MySQL EXPLAIN

Příkaz EXPLAIN je nástroj pro analýzu dotazů. S jeho pomocí lze získat informace, jakým způsobem je konkrétní dotaz zpracován.

Spuštění je jednoduché ‐ stačí přidat klíčové slovo EXPLAIN před dotaz. Výsledkem je, že MySQL vrátí informace o zpracovaném dotazu, namísto aby jej skutečně provedl. 

EXPLAIN SELECT u.jmeno, o.nazev, uk.jmeno AS kamarad
FROM uzivatele AS u
LEFT JOIN uzivatele AS uk ON uk.id = u.nejlepsi_kamarad_id
JOIN obce AS o ON o.id = u.obec_id
WHERE u.pohlavi = 'muz'
ORDER BY u.datum_narozeni

SQL 1 - Ukázka příkazu EXPLAIN

Výstupem je tabulka s neměnným počtem sloupců a variabilním počtem řádků.

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE u ref pohlavi_datum_narozeni pohlavi_datum_narozeni 1 const 1683 Using index condition; Using where;
1 SIMPLE o eq_ref PRIMARY PRIMARY 3 obec_id 1
1 SIMPLE uk eq_ref PRIMARY PRIMARY 3 nejlepsi_kamarad_id 1 Using where;

Tabulka 1 - Ukázka výstupu příkazu EXPLAIN

Jak můžete vidět v tabulce 1, jedna tabulka v dotazu odpovídá jednomu řádku ve výstupu. Všiměte si, že se tabulka uzivatele objevila ve výstupu vícekrát, jelikož se k ní přistupuje dvakrát.

Později uvidíte, že jako samostatné tabulky zahrnuje MySQL do výstupu i subdotazy či dočasné tabulky.

Pořadí řádků je určeno posloupností v jakém MySQL zpracovává jednotlivé části dotazu. To nemusí vždy korespondovat s pořadím v samotném SQL dotazu, viz. pořadí tabulek v SQL 1 a pořadí řádků v tabulce 1.

Sloupce výstupu

id

Jedná se o číslo, které identifikuje jednotlivé SELECTy v dotazu. Číslování je posloupné podle pozice v dotazu, tzn. čím dříve se objeví SELECT v dotazu, tím menší má id.

V prvním příkladu byly všechny řádky označeny jako id 1, jelikož se v dotazu vyskytoval jediný SELECT. V dalším příkladě níže je již druhý SELECT v subdotazu označen jako id 2. 

EXPLAIN SELECT u.jmeno, o.nazev, ( SELECT 1 FROM staty LIMIT 1 ) AS const
FROM uzivatele AS u
JOIN obce AS o ON o.id = u.obec_id
WHERE u.pohlavi = 'muz'
ORDER BY u.datum_narozeni

SQL 2 - Druhý SELECT v dotazu

id select_type table ...
1 PRIMARY u ...
1 PRIMARY o ...
2 SUBQUERY staty ...

Tabulka 2 - Tabulky uzivatele a obce jsou označeny s id 1, jelikož se k nim přistupuje v rámci prvního SELECTu v dotazu. Subdotaz označený jako const se vyskytuje v dotazu až jako druhý, je proto označen jako id 2.

select_type

Jak už název naznačuje, tento sloupec označuje typ SELECTu. Může nabývat následujících hodnot:

SIMPLE

Jednoduchý SELECT. V dotazu se nevyskytuje žádný subdotaz či UNION.

PRIMARY

Označení pro první, nejkrajněší SELECT, v připadě, že se v dotazu vyskytuje subdotaz či UNION.

SUBQUERY

Subdotaz v klauzuli SELECT.

DERIVED

Subdotaz v klauzuli FROM. Tento typ subdotazu ukládá MySQL do dočacné tabulky.

UNION

Druhý a další posloupný SELECT v UNIONu.

UNION RESULT

Výsledek UNIONu. Jedná se o dočasnou anonymní tabulku s výsledky ze všech SELECTů v UNIONu. Jelikož se v dotazu nevyskytuje žádný SELECT do této tabulky, je id UNION RESULTu NULL.

DEPENDENT

Jako DEPENDENT mohou být označeny SUBQUERY a UNION. Jedná se o SELECTy, které závisejí na datech z vnějšího dotazu.

UNCACHEABLE

Také jako UNCACHEABLE mohou být označeny pouze SUBQUERY a UNION. UNCACHEABLE označuje dotaz jehož výsledek nemůže být cachován.

table

Hodnota tohoto sloupce je obvykle jednoznačná. Je to název či alias tabulky, ke které se přistupuje. Výjímku tvoří pouze přístup k anonymním dočasným tabulkám, které jsou výsledkem SELECTu typu DERIVED či UNION.

Přistupuje-li se k dočasné tabulce vzniklé ze SELECTu typu DERIVED, objeví se jako hodnota tohoto sloupce ve formě <derivedN>, kde N je id DERIVED SELECTu. 

EXPLAIN SELECT u.jmeno, o.nazev
FROM uzivatele AS u
JOIN ( SELECT * FROM obce WHERE stat_id > 10 LIMIT 5 ) AS o ON o.id = u.obec_id
WHERE u.pohlavi = 'muz'
ORDER BY u.datum_narozeni

SQL 3 - Dotaz se subdotazem v klauzily FROM

id select_type table ...
1 PRIMARY u ...
1 PRIMARY <derived2> ...
2 DERIVED obce ...

Tabulka 3 - JOINuje se tabulka vzniklá ze subdotazu, proto se na druhém řádku jako hodnota table objevuje <derived2>. 2 odkazuje k id SELECTU, jehož výsledkem tato anonymní dočasná tabulka vznikla.

V případě dočasné tabulky vzniklé jako výsledek UNIONu, označené jako UNION RESULT, se jako hodnota tohoto sloupce objeví <unionN,M>. Kde N a M jsou id jednotlivých SELECTů v UNIONu. 

EXPLAIN SELECT u.jmeno, u.prijmeni, u.email
FROM uzivatele AS u
WHERE u.pohlavi = 'muz'
AND u.datum_narozeni > '1990-01-01'
UNION SELECT u.jmeno, u.prijmeni, u.email
FROM uzivatele AS u
WHERE u.pohlavi = 'muz'
AND u.datum_narozeni < '1990-01-01'

SQL 4 - Dotaz s UNIONem

id select_type table ...
1 PRIMARY u ...
2 UNION u2 ...
NULL UNION RESULT <union1,2> ...

Tabulka 4 - V UNIONu jsou dva SELECTy, ve výstupu označené jako 1 a 2. Výsledky obou SELECTů MySQL uloží do dočasné tabulky, ta se objevuje jako třetí řádek ve výstupu. Jelikož se k ní nepřistupuje žádným SELECTem, je hodnota id NULL. <union1,2> jako hodnota table odkazuje na SELECTy ze kterých výsledek vznikl (SELECT id 1 a 2).

type

Tento sloupec může nabývat několika definovaných hodnot. Udávájí jakým způsobem MySQL přistupuje do tabulky.

ALL

Table scan. MySQL musí projít celou tabulku, od prvního řádku až k poslednímu.

index

Totéž co ALL, s tím rozdílem že MySQL přistupuje k řádkům tabulky v pořadí podle indexu. Jde tedy o získání všech řádků seřazených podle indexu.

range

MySQL přistupuje do tabulky podle indexu pouze v určitém rozsahu. Obvykle jde o filtrování indexovaného sloupce operátory >, <, BETWEEN, apod.

ref

Jde o porovnání neunikátního indexu s jednou hodnotou. Opět tedy musí MySQL přistoupit k více řádkům tabulky, nicméně oproti range se již index neporovnává vůči rozsahu hodnot, nýbrž vůči jedné hodnotě.

eq_ref

MySQL porovnává při JOINování unikátní index s jednou určitou hodnotou.

const

Nejlepší možný způsob přístupu do tabulky. Podobně jako u eq_ref je výstupem nanejvíš jeden řádek.

NULL

MySQL vůbec do tabulky nepřistupuje. Výsledek dotazu je známý již ve fázi optimalizace.

possible_keys

Jde o indexy, které MySQL vyhodnotilo jako užitečné pro zeefektivnění přístupu do tabulky.

key

Sloupec s názvem indexu, který byl pro dotaz využit.
Použitý index nemusí vždy korespondovat se seznamem v possible_keys. Prakticky to velmi často znamená, že je ve snaze co nejvíce zvýšit efektivitu dotazu využit tzv. covering index, který sice nemusí pomoci účelně nalézt požadované řádky, nicméně výrazně urychluje jejich získání.

key_len

Velikost použitého indexu v bajtech.

ref

Obsahem jsou názvy sloupců odkazující tabulky, které byly využity pro porovnání s použitým indexem. 

EXPLAIN SELECT u.jmeno, u.prijmeni, u.email
FROM uzivatele AS u
WHERE u.pohlavi = 'muz'
AND u.datum_narozeni = '1990-01-01'

SQL 5 - Ukázka filtrování v dotazu dvěma konstantami

... possible_keys key key_len ref ...
... pohlavi_datum_narozeni pohlavi_datum_narozeni 4 const,const ...

Tabulka 5 - V případě, že se index porovnává s konstantami (nikoliv s hodnotami z jiných tabulek), objeví se v ref hodnota const.

rows

Odhad počtu řádků tabulky, které bude nutno přečíst k nalezení všech požadovaných řádků. 

EXPLAIN SELECT *
FROM `uzivatele`
WHERE 1
LIMIT 10

SQL 6 - Dotaz s LIMITem

... rows ...
... 5006 ...

Tabulka 6 - rows je nejnepřesnější sloupec EXPLAINu. V tomto případě je hodnota rows 5006, ačkoliv MySQL přečte díky LIMITu pouze 10 řádku.

Extra

Sloupec Extra obsahuje některé dodatečné informace.
Seznam a popis všech možných hodnot naleznete v manuálu.

Using index
MySQL všechna potřebná data získá již z indexu (tzv. covering index). Nemusí tedy přistupovat do tabulky, což dotaz velmi zrychluje, často až o řády.
Using filesort
Po získání všech požadovaných řádku z tabulky se provádí ještě dodatečné řazení.
Using temporary
MySQL musí vytvořit dočasnou tabulku s výsledkem dotazu, na kterou poté aplikuje dodatečné operace, např. řazení apod.

Zdroje a odkazy