登录 注册

MySQL EXPLAINN 执行计划

本贴最后更新于 3018 天前，其中的信息可能已经时移俗易

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

语法

{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

}

简单说一下这个语法怎么看，有些初学者可能会看不懂。 语法里，{}表示一个语句块；| 表示或的意思，就是说可以用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;

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）。

使用

获取表结构信息：

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执行计划：

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)

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

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。

这里再试试：

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

 

id

Optimizer 执行计划中查询的序列号。表示查询中执行 select 子句或操作表的顺序，id 值越大优先级越高，越先被执行。id 相同，执行顺序由上至下。

如果行数据通过union关联了其他的行数据，则该值为NULL。这个时候table 列的值会设置成 <unionM,N>形式，表示id为M和N的结果进行了union。

select_type

通过这个类型来标识一条语句中不同位置的SQL语句。

SIMPLE--简单的SELECT查询，没有使用UNION或子查询。

PRIMARY--嵌套查询时，最外层的SELECT语句；在UNION查询时，最前面的SELECT语句。

UNION--UNION中第二个以及后面的SELECT语句。

UNION RESULT--一个UNION查询的结果。

SUBQUERY--子查询中第一个SELECT语句。

DERIVED--FROM子查询中的SELECT语句。

DEPENDENT SUBQUERY--子查询中的第一个SELECT语句，并且是依赖外部查询的。

DEPENDENT UNION--UNION中的第二个以及之后的SELECT语句，并且是依赖外部查询的。

DEPENDENT的这两个类型有必要写个sql来看看，先看一下下面这个sql的执行计划：

mysql> explain select a.articleTitle from b3_solo_article a where a.oid in
>(select commentOnId from b3_solo_comment where oid='1471598068326' union select commentOnId from b3_solo_comment where oid='1471622574286');   
+----+--------------------+-----------------+-------+---------------+---------+---------+-------+------+-----------------+
| id | select_type        | table           | type  | possible_keys | key     | key_len | ref   | rows | Extra           |
+----+--------------------+-----------------+-------+---------------+---------+---------+-------+------+-----------------+
|  1 | PRIMARY            | a               | ALL   | NULL          | NULL    | NULL    | NULL  |    4 | Using where     |
|  2 | DEPENDENT SUBQUERY | b3_solo_comment | const | PRIMARY       | PRIMARY | 767     | const |    1 | NULL            |
|  3 | DEPENDENT UNION    | b3_solo_comment | const | PRIMARY       | PRIMARY | 767     | const |    1 | NULL            |
| NULL | UNION RESULT       | <union2,3>      | ALL   | NULL          | NULL    | NULL    | NULL  | NULL | Using temporary |
+----+--------------------+-----------------+-------+---------------+---------+---------+-------+------+-----------------+
4 rows in set (0.00 sec)

可以看到，

id为3的select_type是DEPENDENT UNION，所以3对应的就是select commentOnId from b3_solo_comment where oid='1471622574286'；

id为2的select_type是DEPENDENT SUBQUERY ，所以2对于的就是select commentOnId from b3_solo_comment where oid='1471598068326' ；

table <union2,3> ，就表示id为2和3的结果进行了union。

你肯能注意到了，上面DEPENDENT类型的描述“并且是依赖外部查询的”，但是2和3的查询看起来并不依赖于外部。

这是因为Optimizer对实际执行的sql语句做优化，把in中的非关联的子查询转成了关联子查询（Correlated Subqueries）。

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

转成了

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

所以转换以后，就变成子查询依赖外部查询了。

table

标识查询的表信息，值可能是：

表名

指定的别名

<unionM,N> 上面已经说到过

<derivedN> 使用id为N的表作为此衍生表

<subqueryN> 使用id为N的子查询作为次衍生表

partitions

使用EXPLAIN PARTITIONS 关键字会显示出partitions列，显示表的分区信息。参考：http://dev.mysql.com/doc/refman/5.6/en/partitioning-info.html

type

system-- 系统表，一行匹配，const的特例

const-- 当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。const只会用在将常量和主键或唯一索引进行比较时，而且是比较所有的索引字段。

eq_ref

ref

fulltext

ref_or_null

index_merge

unique_subquery

index_subquery

range

index

ALL

possible_keys

 

key

 

key_len

 

ref

 

rows

 

filtered

 

Extra

 

更详细的信息参考：http://dev.mysql.com/doc/refman/5.7/en/explain-output.html

  • MySQL

    MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    691 引用 • 535 回帖
  • 优化

    不成熟的优化是万恶之源。

    过度优化实则是劣化。

    32 引用 • 173 回帖
  • 执行计划
    2 引用 • 3 回帖
北京 位置
3 回帖
2
1.6k 6 505 316 83 134 69 471

相关帖子

3 回帖
MySQL EXPLAINN 执行计划

欢迎来到这里！

我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。

注册 关于
请输入回帖内容 ...
Hassan
VPS推荐，5$ 👉 北京
回帖
545
 帖子
112
 积分
45774