SQL - EXPLAIN a ANAYLYZE (efektivní SQL dotazy, optimalizace)

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

 

Komentáře

Vaše reakce na SQL - EXPLAIN a ANAYLYZE (efektivní SQL dotazy, optimalizace)
Martin
#1891

12.03.2024 [1]
Ahoj, můžu nějak vypnout striktní mód u MySQL / MariaDB serveru?

OL3G
#1892

12.03.2024 [2]
Poslední komentář
Ahoj,

stačí spustit tento SQL dotaz:

set global sql_mode='';

Tím se vypne strict mode.

Související obsah

databaze

sql

mysql

serial-sql

tool

Reference

Podívejte se na naše reference

Prohlédnout

Aplikace

Podívejte se na naše aplikace

Prohlédnout

Co umíme?

Podívejte se co umíme

Prohlédnout

Co umíme?

Vytváříme sofistikované aplikace pro náročné

Od webových aplikací přes android až po převodové můstky či složité informační systémy.

Podívejte se k nám

Máte ještě čas? Podívejte se na další rubriky

Tento web používá soubory cookie. Dalším procházením tohoto webu vyjadřujete souhlas s jejich používáním.. Více informací zde.