🌙 🌝 🖖🏻 Leyendo EXPLAIN a máxima velocidad 🐭 ⛈️ 🎅🏼

Mucha gente que trabaja con MySQL sabe que el comando EXPLAIN se usa para optimizar consultas, para obtener información sobre índices usados y posibles. La mayoría de los desarrolladores y administradores de DBMS se limitan a esta información. Le sugiero que estudie el comando EXPLAIN con el mayor detalle posible.

Arquitectura lógica de MySQL

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

Leyendo EXPLAIN a máxima velocidad

Arquitectura lógica de MySQL

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: