MySQLを使用する多くの人は、EXPLAINコマンドを使用してクエリを最適化し、使用されているインデックスと可能なインデックスに関する情報を取得することを知っています。ほとんどの開発者とDBMS管理者は、この情報に制限されています。EXPLAINコマンドをできるだけ詳しく調べることをお勧めします。
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 :
A.
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
id |
table |
possible_keys |
key |
key_len |
1 |
Orders |
Orders_Drivers_id_fk, Orders_client_id_driver_id |
Orders_client_id_driver_id |
8 |
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 .
-
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 , , .
High Performance MySQL (by Baron Schwartz, Peter Zaitsev, Vadim Tkachenko)
-
-
-
https://taogenjia.com/2020/06/08/mysql-explain/
https://www.eversql.com/mysql-explain-example-explaining-mysql-explain-using-stackoverflow-data/
https://dba.stackexchange.com/
-
https://andreyex.ru/bazy-dannyx/baza-dannyx-mysql/explain-analyze-v-mysql/
https://programming.vip/docs/explain-analyze-in-mysql-8.0.html
google.com