MySQL EXPLAINN

EXPLAIN 用来查看MySQL执行一个SQL语句的执行计划。


语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}
explain_type: {
EXTENDED
| PARTITIONS
| FORMAT = format_name
}
format_name: {
TRADITIONAL
| JSON
}
explainable_stmt: {
SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}

1.
简单说一下这个语法怎么看,有些初学者可能会看不懂。
语法里,{}表示一个语句块;| 表示或的意思,就是说可以用EXPLAIN 或 DESCRIBE 或 DESC的意思;[]这个表示可选项,就是可有可无的意思。
其中,explain_type,explainable_stmt格式要怎么写,可以写什么内容,分别参考下面给出的explainable_stmt:{}等对应的包裹的格式。
比如:EXPLAIN table_name_1;
再比如:EXPLAIN EXTENDED SELECT FROM table_name_2;
再或者:EXPLAIN FORMAT = JSON SELECT
FROM table_name_3;

2.
EXPLAIN和DESCRIBE 、DESC 是同义词。执行结果是一样的。但是习惯上,会使用DESCRIBE 、DESC 来查询表结构信息;用EXPLAIN来查看MySQL执行计划。(In practice, the DESCRIBE keyword is more often used to obtain information about table structure, whereas EXPLAIN is used to obtain a query execution plan)。

获取表结构信息:

1
2
3
4
5
6
7
8
9
10
mysql> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| Country | char(3) | NO | UNI | | |
| District | char(20) | YES | MUL | | |
| Population | int(11) | NO | | 0 | |
+------------+----------+------+-----+---------+----------------+

查看MySQL执行计划:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> EXPLAIN SELECT * FROM city ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN EXTENDED SELECT * FROM city ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

3.
EXPLAIN 加上 EXTENDED 关键字的意思的在执行计划结果字段里显示:filtered 字段(什么意思后面会继续说)。但是细看2中的例子,会发现,没有加EXTENDED关键字也显示了filtered字段。这是因为在5.7.3以后,EXPLAIN的默认处理就是按存在EXTENDED关键字来处理的。所以不用加EXTENDED也会显示filtered字段。
在细心的看一下2中查看MySQL执行计划的两次结果提示,含有EXTENDED的查询有两个warnings,而不含EXTENDED的只有一个warnings。
我们用SHOW WARNINGS; 查看一下提示信息:

1
2
3
4
5
6
7
8
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release. |
| Note | 1003 | /* select#1 */ select `world`.`city`.`ID` AS `ID`,`world`.`city`.`Name` AS `Name`,`world`.`city`.`CountryCode` AS `CountryCode`,`world`.`city`.`District` AS `District`,`world`.`city`.`Population` AS `Population` from `world`.`city` |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

‘EXTENDED’ 在未来版本将被弃用。so…

另外,2中的两次查询都有 1003这个warning。

这里再试试:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> SELECT * FROM city WHERE ID=1;
+----+-------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM city WHERE ID=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | city | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select '1' AS `ID`,'Kabul' AS `Name`,'AFG' AS `CountryCode`,'Kabol' AS `District`,'1780000' AS `Population` from `world`.`city` where 1 |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

同样可以看见1003这个warning,再看Message字段的内容,sql语句条件变成了where 1,并且select字段也变成了“结果值 AS 字段名”的样子。
这个警告信息,只是起提示作用的,告诉你一些信息,比如说:执行计划使用“const” 访问类型,将列值转换为常量来处理了。
Message的语句并不等同于原始SQL语句,也不等同于被优化器执行的语句,并且不一定是能被执行的语句。
(原文:Because the statement displayed by SHOW WARNINGS may contain special markers to provide information about query rewriting or optimizer actions, the statement is not necessarily valid SQL and is not intended to be executed. The output may also include rows with Message values that provide additional non-SQL explanatory notes about actions taken by the optimizer.)

扩展阅读:https://www.percona.com/blog/2006/07/24/extended-explain/


EXPLAIN 输出的列

列名 JSON 名称
id select_id
select_type None
table table_name
partitions partitions
type access_type
possible_keys possible_keys
key key
key_len key_length
ref ref
rows rows
filtered filtered
Extra None

//TODO