explain 语法解析

要使用explain 语句来查看执行计划，我们需要先知道explain 语句的语法，如下：

AAA@localhost Sat Mar 18 12:24:31 2017 12:24:31 [(none)]>help explain ;
Name: 'EXPLAIN'
Description:
Syntax:
{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    explainable_stmt

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
- 另外，三个关键字后面可以跟上某表、某表某字段，表示查看表、字段的定义信息，通常来讲查看表定义属性都是使用desc、describe，或者使用show create 语句来查看完整的建表语句
explain_type有三个类型，EXTENDED、PARTITIONS、FORMAT = format_name，三个只能同时使用一个，否则报语法错误，分别表示含义如下（注意：5.7开始默认启用EXTENDED和PARTITIONS，即只需要选择用与不用 FORMAT =json即可，默认FORMAT =TRADITIONAL。如果要显式使用EXTENDED和PARTITIONS关键字，那么与5.6及其之前的版本一样，仍然只能选其一）
- EXTENDED：表示查看扩展的执行计划信息，会把执行计划内部改写的SQL语句打印出来，放到warnings信息中，另外，EXTENDED类型还会多输出一个列filtered（注意：5.7版本开始默认启用了扩展的格式，不需要再显式使用这个关键字了）
- PARTITIONS：表示查看分区表的执行计划信息，执行计划中可以看到mysql扫描了哪些分区，会多出一个partitions输出列（注意：5.7版本开始默认使启用了PARTITIONS关键字的功能，不需要再显式使用这个关键字了）
- FORMAT = format_name：表示使用指定的格式来输出执行计划信息，format_name有TRADITIONAL和JSON两种输出格式，分别表示的含义如下：
  - TRADITIONAL：表示使用传统的输出格式，像查询一行数据横排那样输出，当然你也可以使用\G来竖排输出
  - JSON：表示使用json输出格式，json格式只会打印值不为null的列，json格式中打印了更多的执行计划内部信息，5.7中更是打印了详细的cost信息、行扫描信息以及使用到的列字段信息等
explainable_stmt有5种类型：select、delete、insert、replace、update（注意：虽然语法支持insert、replace语句，但是实际上执行计划是不支持的，输出结果在5.6版本中除了id,select_type,Extra里诶之外，其他的列全部都是Null，因为并没有where条件可指定，在5.7版本中table、possible_keys和type可以显示内容了，但是Extra列反而变null了），5.6之前的版本只支持查看select的执行计划，从5.6开始支持查看DML的执行计划
下面是几个使用explain语句查看执行计划的使用示例（以下以5.7.17版本做演示）

# 查看select执行计划
root@localhost Sat Mar 18 13:10:38 2017 13:10:38 [xiaoboluo]>explain select * from test where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

# 查看update执行计划
root@localhost Sat Mar 18 13:14:49 2017 13:14:49 [xiaoboluo]>explain update test set id=2 where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | test  | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)

# 查看delete执行计划
root@localhost Sat Mar 18 13:14:55 2017 13:14:55 [xiaoboluo]>explain delete from test where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | DELETE      | test  | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)

# 查看replace执行计划
root@localhost Sat Mar 18 13:15:00 2017 13:15:00 [xiaoboluo]>explain replace into test(test1) values(1);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | REPLACE     | test  | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | NULL |     NULL | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.00 sec)

# 查看insert执行计划
root@localhost Sat Mar 18 13:15:06 2017 13:15:06 [xiaoboluo]>explain insert into test(test1) values(1);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | INSERT      | test  | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | NULL |     NULL | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.00 sec)

# 查看分区表执行计划(5.7中使用partitions 关键字时，会出现两个warnings信息，其中一个就是告诉你这个关键字已经弃用了，另外一个是告诉你执行计划内部改写的语句是怎样的)
root@localhost Sat Mar 18 13:15:11 2017 13:15:11 [xiaoboluo]>explain partitions select * from test where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

root@localhost Sat Mar 18 13:30:07 2017 13:30:07 [xiaoboluo]>show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                         |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'PARTITIONS' is deprecated and will be removed in a future release.                                                                             |
| Note    | 1003 | /* select#1 */ select '1' AS `id`,'1' AS `test1`,'0' AS `test2`,NULL AS `test3`,NULL AS `test4`,NULL AS `test5` from `xiaoboluo`.`test` where 1 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

# 查看扩展的执行计划(5.7中使用extended关键字时，会出现两个warnings信息，其中一个就是告诉你这个关键字已经弃用了，另外一个是告诉你执行计划内部改写的语句是怎样的)
root@localhost Sat Mar 18 13:15:16 2017 13:15:16 [xiaoboluo]>explain extended select * from test where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

root@localhost Sat Mar 18 13:19:32 2017 13:19:32 [xiaoboluo]>show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                         |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                                                               |
| Note    | 1003 | /* select#1 */ select '1' AS `id`,'1' AS `test1`,'0' AS `test2`,NULL AS `test3`,NULL AS `test4`,NULL AS `test5` from `xiaoboluo`.`test` where 1 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

# 以json格式输出执行计划
root@localhost Sat Mar 18 13:15:20 2017 13:15:20 [xiaoboluo]>explain format=json select * from test where id=1;
| EXPLAIN  |
...
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.00"
    },
    "table": {
      "table_name": "test",
      "access_type": "const",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "id"
      ],
      "key_length": "4",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.00",
        "eval_cost": "0.20",
        "prefix_cost": "0.00",
        "data_read_per_join": "1K"
      },
      "used_columns": [
        "id",
        "test1",
        "test2",
        "test3",
        "test4",
        "test5"
      ]
    }
  }
} |
...
1 row in set, 1 warning (0.00 sec)

PS：以上查看执行计划步骤中也可以把explain关键字换做desc和describe，执行结果相同，大家自行尝试，这里不再赘述

下面是使用EXPLAIN | DESCRIBE | DESC几个关键字查看表定义信息的示例

# 使用explain关键字查看表定义信息
root@localhost Sat Mar 18 13:15:29 2017 13:15:29 [xiaoboluo]>explain test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| test1 | varchar(100)     | NO   |     | 0       |                |
| test2 | varchar(100)     | NO   |     | 0       |                |
| test3 | varchar(100)     | YES  | MUL | NULL    |                |
| test4 | varchar(100)     | YES  | MUL | NULL    |                |
| test5 | varchar(100)     | YES  | MUL | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

# 使用desc关键字查看表定义信息
root@localhost Sat Mar 18 13:15:35 2017 13:15:35 [xiaoboluo]>desc test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| test1 | varchar(100)     | NO   |     | 0       |                |
| test2 | varchar(100)     | NO   |     | 0       |                |
| test3 | varchar(100)     | YES  | MUL | NULL    |                |
| test4 | varchar(100)     | YES  | MUL | NULL    |                |
| test5 | varchar(100)     | YES  | MUL | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

# 使用describe语句查看表定义信息
root@localhost Sat Mar 18 13:15:39 2017 13:15:39 [xiaoboluo]>describe test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| test1 | varchar(100)     | NO   |     | 0       |                |
| test2 | varchar(100)     | NO   |     | 0       |                |
| test3 | varchar(100)     | YES  | MUL | NULL    |                |
| test4 | varchar(100)     | YES  | MUL | NULL    |                |
| test5 | varchar(100)     | YES  | MUL | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

上一篇：附录D explain 执行计划详解目录 | 下一篇：执行计划输出格式、结果详解

本 WIKI 包含了《千金良方--MySQL 性能优化金字塔法则》一书的代码段加粗命令行命令和SQL语句文本、以及4个附录内容，其中：
- 代码段和高清图单独整理为一个系列文档，如下：
  - 书中代码段以及高清大图目录
- 每个附录都各自整理成了一个小系列文档，如下：
《千金良方--MySQL 性能优化金字塔法则》一书的作者信息如下：
- 李春、罗小波、董红禹
联系人QQ：309969177
提示：
- 附录内容主要的验证、测试、整理工作由本人罗小波完成。鉴于本人精力和能力有限，难免出现一些纰漏，欢迎大家踊跃指正！
- 如有购书需求，请自行前往京东、当当等平台购买，快捷入口如下：
  - 京东自营购买入口
  - 当当自营购买入口
- 注：本书主要基于MySQL 5.7撰写，同时，涉及到了少量的MySQL 5.6和MySQL 8.0内容作为参照对比
郑重声明：本WIKI仓库中的资料为电子工业出版社与本书的三位作者共同授权开源，为了在方便大家的同时，避免不必要的纠葛，任何商业与非商业的引用、转载，麻烦大家注明出处，谢谢配合！

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

explain 语法解析

Clone this wiki locally