🔇 🤞🏼 👪 Reading EXPLAIN at maximum speed 🆙 👔 🦖

Many people who work with MySQL know that the EXPLAIN command is used to optimize queries, to get information about used and possible indexes. Most developers and DBMS administrators limit themselves to this information. I suggest studying the EXPLAIN command in as much detail as possible.

MySQL logical architecture

, EXPLAIN, MySQL.

. MySQL. , , MySQL.
MySQL. :

A. . , /. , MySQL, , . , , , , , , . , .

B. MySQL. «» MySQL. , , SQL, , (, / ).
. MySQL.

, « MySQL», . , , « », EXPLAIN-.

EXPLAIN

EXPLAIN , MySQL . SELECT, UPDATE, INSERT, DELETE REPLACE.

5.6

5.6 EXPLAIN SELECT, , , SELECT.

, , EXPLAIN , MySQL . , . , FROM, .

EXPLAIN :

id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra

5.6

filtered partitions. , EXPLAIN, EXTENDED PARTITIONS, .

5.6

5.6 partitions -, filtered EXTENDED.

, , , , .

EXPLAIN SELECT 1

id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used

ID

SELECT- . SELECT , ( ) . SELECT :

EXPLAIN SELECT (SELECT 1 from Orders) from Drivers

id	select_type	table
1	PRIMARY	Drivers
2	SUBQUERY	Orders

B. , FROM

EXPLAIN SELECT * FROM (SELECT 1, 2) AS tmp (a, b)

id	select_type	table
1	PRIMARY	<derived2>
2	SUBQUERY	null

, MySQL tmp. ref. , table , <derivedN>, N — ID .

C. UNION

EXPLAIN SELECT id FROM Cars UNION SELECT id FROM Drivers

id	select_type	table
1	PRIMARY	Cars
2	UNION	Drivers
null	UNION RESULT	<union1,2>

c FROM-. -, MySQL , , , . SQL-, id null. -, , , , .

table <unionN,M>, N — ID , M — .

select_type

SELECT EXPLAIN. , , SIMPLE. , PRIMARY, :

SUBQUERY. SELECT, , SELECT (.. FROM).
DERIVED. , SELECT FROM. , “derived table”.

: FROM , , FROM.
UNION. UNION, PRIMARY (. ). UNION FROM, SELECT DERIVED. UNION.
UNION RESULT. SELECT, MySQL , UNION.

, SUBQUERY, UNION DERIVED DEPENDENT, SELECT , SELECT.

5.7

DEPENDENT DERIVED 8 MySQL.

SUBQUERY UNION UNCACHABLE. , SELECT . , - RAND().

MySQL 5.7 select_type MATERIALIZED, , .

table

, . — ( ) SQL- .

table .

EXPLAIN SELECT Clients.id
        FROM Clients
        JOIN Orders ON Orders.client_id = Clients.id
        JOIN Drivers ON Orders.driver_id = Drivers.id

id	select_type	table
1	SIMPLE	Clients
1	SIMPLE	Orders
1	SIMPLE	Drivers

, . , , .

FROM UNION, table , MySQL , .

table FROM . derived.N , N — ID . UNION RESULT (union N,M) , , UNION.

, , table :

EXPLAIN SELECT id, (SELECT 1 FROM Orders WHERE client_id = t1.id LIMIT 1)
       FROM (SELECT id FROM Drivers LIMIT 5) AS t1
       UNION
       SELECT driver_id, (SELECT @var1 FROM Cars LIMIT 1)
       FROM (
           SELECT driver_id, (SELECT 1 FROM Clients)
           FROM Orders LIMIT 5
       ) AS t2

id	select_type	table
1	PRIMARY	<derived3>
3	DERIVED	Drivers
2	DEPENDENT SUBQUERY	Orders
4	UNION	<derived6>
6	DERIVED	Orders
7	SUBQUERY	Clients
5	UNCACHEABLE SUBQUERY	Cars
null	UNION RESULT	<union1,4>

, , , .

t1, <derived3>.
3, SELECT. select_type DERIVED, FROM.
ID = 2 ID, , , t1.id, ID = 3. DEPENDENT SUBQUERY , .
, UNION. <derived6> , FROM UNION.
— FROM, t2.
SELECT. 7, , ID = 5.
, ID, ? , DERIVED , . , ID , DERIVED ( 5 < 6). , SELECT, <derived6>. UNCACHEABLE select_type - @var1.
UNION RESULT UNION.

EXPLAIN . , , , UNION.

partitions

, . , null.

type

, MySQL . MySQL “The join type”, . type , :

ALL. , MySQL , LIMIT extra Distinct/not exists, .
index. MySQL , , . , . , extra using index, , , MySQL . ,
range. . , . range , =, <>, >, >=, <, <=, IS_NULL, <=>, BETWEEN, LIKE IN.
index_subquery. , IN , MySQL .
unique_subquery. index_subquery, , , Primary key Unique index.
index_merge. range- , . , extra : Using intersect — , Using union — , Using sort_union — ( )
ref_or_null. ref, , MySQL , NULL- .
fulltext. FULLTEXT-.
ref. , , . , , Primary key Unique index , . ref = <=>.
eq_ref. . =. “=” .
const. . MySQL , type const. , - , JOIN.
system. . const.
NULL. , . , , .

possible_keys

, . , EXPLAIN, . NULL, .

keys

(), MySQL . possible_keys. , possible_keys , , , - . , , .

key_len

() . , primary key id int, , , key_len 4, int 4 . key_len . key NULL, key_len NULL.

EXPLAIN SELECT * FROM Orders
WHERE client_id = 1

id	table	possible_keys	key	key_len
1	Orders	Orders_Clients_id_fk	Orders_Clients_id_fk	4

EXPLAIN SELECT * FROM Orders
WHERE client_id = 1 AND driver_id = 2

table

possible_keys

key

key_len

Orders

Orders_Drivers_id_fk,

Orders_client_id_driver_id

ref

, key . NULL, const . func, . , , EXPLAIN SHOW WARNINGS.

EXPLAIN SELECT * FROM Drivers

id	table	ref
1	Drivers	null

EXPLAIN SELECT * FROM Drivers
WHERE id = 1

id	table	ref
1	Drivers	const

EXPLAIN SELECT * FROM Drivers
JOIN Orders ON Drivers.id = Orders.driver_id

id	table	ref
1	Orders	null
1	Drivers	Orders.driver_id

rows

, , MySQL, . . . , , rows , . ( ), .

filtered

, MySQL. 100, 100 % . rows, , MySQL . , rows 100 , filtered 50,00 (50 %), 100 x 50 % = 50.

Extra

. . (!!!). , , .

const row not found. , SELECT … FROM table, table .
Deleting all rows. MySQL, MyISAM, . , Deleting all rows Extra.
Distinct. DISTINCT, MySQL , .
FirstMatch (table_name). optimizer_switch firstmatch=on, MySQL FirstMatch, , . , id = 10 , , :

EXPLAIN

SELECT id FROM Drivers

WHERE Drivers.id IN (SELECT driver_id FROM Orders WHERE client_id = 10)

MySQL FirstMatch, .

id	table	extra
1	Orders	Using index;
2	Drivers	Using index; FirstMatch(Orders)

Full scan on NULL key. Using where , .
Impossible HAVING. HAVING .
Impossible WHERE. WHERE .
Impossible WHERE noticed after reading const tables. MySQL const ( system) , WHERE .
LooseScan(m..n). GROUP BY. .
No matching min/max row. , MIN/MAX.
No matching rows after partition pruning. Impossible WHERE SELECT, DELETE UPDATE.
No tables used. FROM FROM DUAL.
Not exists. MySQL . , , . NOT EXISTS(), , .
Plan isn’t ready yet. EXPLAIN FOR CONNECTION, .
Range check for each record (!!!). , , , . range index_merge.
Recursive. (WITH) extra.
Scanned N databases. INFORMATION_SCHEMA . N 0, 1 all.
Select tables optimized away (!!!). , ( GROUP BY). , , , . extra, .
Skip_open_table, Open_frm_only, Open_full_table. , , MySQL .frm, . MyISAM .MYD .MYI . INFORMATION_SCHEMA Skip_open_table , , (data dictionary). Open_frm_only .frm. Open_full_table .frm, .MYD .MYI.
Start temporary, End temporary. , DuplicateWeedout. , Start temporary. , extra End temporary. .
unique row not found (!!!). SELECT … FROM table PRIMARY UNIQUE KEY.
Using filesort (!!!). MySQL , , . , , EXPLAIN .
Using index (!!!). MySQL , .
Using index condition (!!!). , , . WHERE , Using index condition Using index.
Using index for group-by (!!!). Using index, GROUP BY DISTINCT. , .
Using join buffer (Block nested loop | Batched Key Access | hash join). , (JOIN), (Join Buffer). . (Block nested loop | Batched Key Access | hash join) extra.
Using sort_union, Using union, Using intersect. , index_merge type.
Using temporary (!!!). .
Using where (!!!). , . Using where extra, , .
Zero limit. LIMIT 0.

SHOW WARNINGS

EXPLAIN , SHOW WARNINGS. .

MySQL 5.6

SHOW WARNINGS EXPLAIN EXTENDED.

EXPLAIN SELECT
              Drivers.id,
              Drivers.id IN (SELECT Orders.driver_id FROM Orders)
FROM Drivers;

SHOW WARNINGS;

/* select#1 */ select `explain`.`Drivers`.`id` AS `id`,
<in_optimizer>(`explain`.`Drivers`.`id`,
<exists>(<index_lookup>(<cache>(`explain`.`Drivers`.`id`) in Orders on Orders_Drivers_id_fk))) AS `Drivers.id 
IN (SELECT Orders.driver_id FROM Orders)` from `explain`.`Drivers`

, SQL- . , . , , ( ).

SHOW WARNINGS , SQL -. :

<auto_key>. .
<cache> (expr). expr , . , <cache> <temporary table>.
<exists> (query fragment). EXISTS -, , EXISTS.
<in_optimizer> (query fragment). , .
<index_lookup> (query fragment). .
<if> (condition, expr1, expr2). , expr1, expr2.
<is_not_null_test> (expr). , expr null.
<materialize> (query fragment). .
‘materialized-subquery’.col_name. col_name .
<primary_index_lookup> (query fragment). .
<ref_null_helper> (expr). , .
/* select # N */. SELECT id = N EXPLAIN.
<temporary table>. , .

EXPLAIN

, - EXPLAIN?

. type. — ( , LIMIT). . type NULL (. type), .

rows filtered. rows filtered,- . , rows filtered 100 % - .

, key , possible_keys. ( ), , , possible_keys.

, Extra. , (!!!), , , . , , . SHOW WARNINGS.

, rows , .

, :

EXPLAIN ( ), .
EXPLAIN .
EXPLAIN , MySQL .
— , .
EXPLAIN , . , filesort , , , Using temporary.
MySQL EXPLAIN , , .

EXPLAIN TREE FORMAT EXPLAIN ANALYZE

MySQL, , SHOW WARNINGS.

8.0.16 , FORMAT=TREE:

EXPLAIN FORMAT = TREE select * from Drivers
   join Orders on Drivers.id = Orders.driver_id
   join Clients on Orders.client_id = Clients.id

-> Nested loop inner join  (cost=1.05 rows=1)
   -> Nested loop inner join  (cost=0.70 rows=1)
       -> Index scan on Drivers using Drivers_car_id_index  (cost=0.35 rows=1)
       -> Index lookup on Orders using Orders_Drivers_id_fk (driver_id=Drivers.id)  (cost=0.35 rows=1)
   -> Single-row index lookup on Clients using PRIMARY (id=Orders.client_id)  (cost=0.35 rows=1)

, . . . — , «» MySQL , .

, FORMAT = TREE ANALYZE, MySQL 8.0.18.

EXPLAIN ANALYZE select * from Drivers
   join Orders on Drivers.id = Orders.driver_id
   join Clients on Orders.client_id = Clients.id

-> Nested loop inner join  (cost=1.05 rows=1) (actual time=0.152..0.152 rows=0 loops=1)
   -> Nested loop inner join  (cost=0.70 rows=1) (actual time=0.123..0.123 rows=0 loops=1)
       -> Index scan on Drivers using Drivers_car_id_index  (cost=0.35 rows=1) (actual time=0.094..0.094 rows=0 loops=1)
       -> Index lookup on Orders using Orders_Drivers_id_fk (driver_id=Drivers.id)  (cost=0.35 rows=1) (never executed)
   -> Single-row index lookup on Clients using PRIMARY (id=Orders.client_id)  (cost=0.35 rows=1) (never executed)

, actual time={ }..{ }. rows, . loops — , ( ). , (never executed).

, MySQL , .

EXPLAIN . , , , . , .

EXPLAIN, , , . .

, , , FROM, UNION JOIN , , .

Reading EXPLAIN at maximum speed

MySQL logical architecture

EXPLAIN

ID

select_type

table

partitions

type

possible_keys

keys

key_len

ref

rows

filtered

Extra

SHOW WARNINGS

EXPLAIN

EXPLAIN TREE FORMAT EXPLAIN ANALYZE

More articles: