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ů.

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

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:

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

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

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.

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

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

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 řádků.

Extra

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

Zdroje a odkazy

• Baron Schwartz, Peter Zaitsev, Vadim Tkachenko (2012). High Performance MySQL, 3rd Edition, O'Reilly Media
• MySQL manuál