SQL - EXPLAIN a ANAYLYZE (efektivní SQL dotazy, optimalizace)
SQL příkaz EXPLAIN je nástroj, který nám umožní porozumět efektivitě našich SQL dotazů. Na základě výstupu z explainu pak můžeme neefektivní SQL dotazy optimalizovat - úprava indexů atd. Jinak řečeno, používá se k diagnostice, když chceme zjistit, proč nějaký SQL dotaz trvá moc dlouho - jak se dotaz zpracuje (plán) a jak dlouho to trvá. Příkaz ANALYZE je podobný jako EXPLAIN - spustí optimalizátor, provede příkaz, vypíše výstup EXPLAIN a umí zobrazit více podrobností.
EXPLAIN
Příkaz EXPLAIN se používá velice jednoduše, stačí ho jen napsat před daný SQL dotaz (tento je neoptimalizovaný bez indexu):
EXPLAIN SELECT projects.* FROM `projects_features` LEFT JOIN projects ON pf_proj_id = proj_id WHERE proj_name LIKE "%a%";
Výstup:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | projects_features | ALL | NULL | NULL | NULL | NULL | 307 | ||
| 1 | SIMPLE | projects | eq_ref | PRIMARY,proj_name | PRIMARY | 4 | up4_cz.projects_features.pf_proj_id | 1 | Using where |
Co znamenají sloupce z EXPLAIN výstupu:
type = join typ
possible_key = možné indexy k použití
key = jaký se použil index
key_len = jaká je délka klíče
ref = sloupce ke srovnání s indexem
rows = kolik bylo prohledáno řádků
extra = extra informace - použití where atd.
filtered = řádky filtrované dle podmínky (v procentech)
ANALYZE
Stejné použití platí i pro příkaz ANALYZE:
ANALYZE SELECT projects.* FROM `projects_features` LEFT JOIN projects ON pf_proj_id = proj_id WHERE proj_name LIKE "a%";
Výstup:
Výstup obsahuje oproti EXPLAIN navíc r_rows, filtered a r_filtered
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | projects_features | ALL | NULL | NULL | NULL | NULL | 307 | 307.00 | 100.00 | 100.00 | ||
| 1 | SIMPLE | projects | eq_ref | PRIMARY | PRIMARY | 4 | up4_cz.projects_features.pf_proj_id | 1 | 1.00 | 100.00 | 71.01 | Using where |
Pomocí FORMAT=JSON SELECT pro ANALYZE získáme podrobnější výstup:
ANALYZE FORMAT=JSON SELECT projects.* FROM `projects_features` LEFT JOIN projects ON pf_proj_id = proj_id WHERE proj_name LIKE "a%";
Výstup:
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.4816476,
"table": {
"table_name": "projects_features",
"access_type": "ALL",
"r_loops": 1,
"rows": 307,
"r_rows": 307,
"r_table_time_ms": 0.092003074,
"r_other_time_ms": 0.063371009,
"filtered": 100,
"r_filtered": 100
},
"table": {
"table_name": "projects",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY", "proj_name"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["proj_id"],
"ref": ["up4_cz.projects_features.pf_proj_id"],
"r_loops": 307,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.300798484,
"r_other_time_ms": 0.021251846,
"filtered": 8.029196739,
"r_filtered": 8.794788274,
"attached_condition": "projects.proj_name like 'a%'"
}
}
}
Odkazy
Doporučuji pročíst např. https://medium.com/datadenys/using-explain-in-mysql-to-analyze-and-improve-query-performance-f58357deb2aa
