Mysql中explain命令简析

前言

之前总结了Mysql慢查询日志的开启与配置方法，通过分析慢查询日志可以锁定执行效率差的SQL，但是这仅仅是发现了需要优化的部分，还要分析执行缓慢的原因，这时候就可以使用EXPLAIN命令去分析，所执行的操作究竟慢在哪里，是不是可以通过加索引或者改变查询方法来解决。

通过查询资料发现除了EXPLAIN命令，还有一个DESCRIBE命令，看起来很陌生是不是，但是如果写出简写desc应该很多人的就熟悉了，这不是查询表结构的时候常用的命令吗？实际上以上三个命令在mysql中是等价的，不过在使用时有些习惯性的偏向，通常使用 EXPLAIN 来分析SQL语句的执行缓慢的问题，而使用 DESCRIBE 或者 desc 来查看表的结构，就类似于惯用法，知道就好。

测试环境

Windows 10
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

EXPLAIN 的使用

关于 EXPLAIN 使用其实很简单，就是在正常的执行语句之前加一个explain 就可以了，不过这里也存在一个疑问，就是发现很多篇文章，提到下面这种说法：

explain 只能解释select查询，并不会对存储过程、insert、update、delete或其他语句做解释

但是我查阅了官方文档发现，EXPLAIN 后面可以跟 SELECT、 DELETE、 INSERT、 REPLACE、和 UPDATE语句，另外之前使用的 EXPLAIN EXTENDED 选项现在也默认开启，EXTENDED 关键字后续会在 Mysql 8.0 版本删除，应该是版本问题导致了explain语句使用的差异，所以请记住在使用新版本的Mysql时，需要分析语句执行情况的，只需要在语句前面添加一个 explain关键字即可。

不过在分析 select 语句时，explain命令会给出额外的提示信息，帮助我们优化查询语句，这也是我们需要学习的重点，先来简单看一下使用方法：

1. 普通的查询语句

   mysql> select * from a;
   +----+-----+
   | id | num |
   +----+-----+
   |  1 | 100 |
   |  2 | 200 |
   |  3 | 300 |
   +----+-----+
   3 rows in set (0.05 sec)

2. 使用 EXPLAIN 来分析普通的查询语句

   mysql> explain select * from a;
   +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
   | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
   +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
   |  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
   +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
   1 row in set (0.04 sec)

通过上面的例子可以很清楚的知道 EXPLAIN 命令的使用方法，使用了 EXPLAIN 关键之后会生成一个分析结果的表格，该表格有id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra一共12列，而这12列中的内容代表的含义是我们学习的重点，也是我们进行优化的依据，这个结果集可能包含多行，其中每一行都是关于一个表的查询信息，可以针对于具体的表查询优化。

本来想每种情况后面紧跟一个例子的，但是发现这样会造成重点内容分散，不利于整体把握，所以还是先把各列可能的取值说清楚，然后在文末针对于上文的取值给出例子，如果看描述就能明白就可以省略例子的内容，否则可以对照着例子的写法理解一下, [eg：<id-1>]表示参考后面的例子<id-1>，想对照的话搜索即可。

EXPLAIN 各列的可能取值

id

id 列的取值通常是一组数字，表示select查询的序号，也有可能是一个NULL：

取值	含义	例子编号
id数字相同	优先级相同，从上往下执行	[eg：<id-1>]
id数字不同	数字越大优先级越高，越先执行，比如包含子查询的语句，内部查询优先执行	[eg：<id-2>]
id值为NULL	通常是使用了union，表示该行是一个结果集，不需要使用它来进行查询	[eg：<id-2>]

select_type

select_type 列表示查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的情况：

取值	含义	例子编号
SIMPLE	简单的查询语句，查询中不包括UNION操作和子查询	[eg：<id-1>]
PRIMARY	在复杂查询中处于最外层的查询	[eg：<id-2>]
UNION	查询语句中处于 UNION 关键字之后的查询	[eg：<id-2>]
DEPENDENT UNION	查询语句中处于 UNION 关键字之后的查询，需要依赖于外部查询	[eg：`<id->`]*
UNION RESULT	表示 UNION 操作之后的结果，本身并不需要参与查询，通常该记录id字段为 NULL	[eg：<id-2>]
SUBQUERY	在子查询中的第一个查询	[eg：`<id->`]*
DEPENDENT SUBQUERY	在子查询中的第一个查询，需要依赖于外部查询	[eg：`<id->`]*
DERIVED	在 FROM 列表中包含的子查询	[eg：<id-4>]
MATERIALIZED	物化子查询	[eg：<id-5>]
UNCACHEABLE SUBQUERY	一个结果不能被缓存并且对于外部查询的每一行都需要重新评估自身的子查询	[eg：`<id->`]*
UNCACHEABLE UNION	查询语句中处于 UNION 关键字之后的子查询，并且其结果属于UNCACHEABLE SUBQUERY类型	[eg：`<id->`]*

table

table 列表示查询所引用到的表名，如果查询中使用了别名，那么会显示别名，此外还有一些其他类型的引用：

取值	含义	例子编号
表名、别名	查询时引用了这个表	[eg：<id-1>]
<unionM,N>	查询时引用了由id为M和N的两个查询的结果集构成的临时结果集	[eg：<id-2>]
<derivedN>	查询时引用了id为N的查询形成的结果集	[eg：<id-4>]
<subqueryN>	查询时引用了id为N的物化子查询形成的结果集	[eg：<id-5>]

partitions

partitions 列表示查询结果集所涉及到的分区，值为 NULL 时表示该表并未分区：

取值	含义	例子编号
分区名	查询结果集引用到了这个分区	[eg：<id-6>]
NULL	该表格并未分区，或者结果集中数据不再分区中	[eg：<id-1>]

type

type 列表示访问类型，也就是找到所需数据所能使用的最好方式，取值类型很多，在下表中从上到下效果越来越差：

取值	含义	例子编号
NULL	查询经过优化执行时不用访问表数据，可能通过索引就搞定了，或者根本没有数据	[eg：<id-8>]
system	在MyISAM类型的表中只有一行数据时出现，如果在 Innodb 类型表中只有一行数据通常显示 ALL	[eg：<id-9>]
const	表格使用了唯一索引或者主键，并且将其作为判定相等的筛选条件，得到一条记录	[eg：<id-10>]
eq_ref	连接查询时，驱动表的每一条记录的条件列，与后面连接表的主键或唯一索引判定相等，后表采用的连接方式	[eg：<id-11>]
ref	连接查询时，驱动表的每一条记录的条件列，与后面连接表的索引列判定相等，后表采用的连接方式，索引列数据不要求唯一，不连接表时就是查索引列等于一个具体的值	[eg：<id-7>]
ref_or_null	与 ref 基本一致，另外包含查询索引列为 NULL 的记录	[eg：<id-12>]
fulltext	包含全文索引的表的查询方式，全文索引的优先级要高于普通索引	[eg：`<id->`]*
index_merge	至少用到了两个索引，并且用到了索引合并优化	[eg：`<id->`]*
unique_subquery	where条件in形式的子查询子查询返回唯一结果时，等价于将类型为 eq_ref 的查询作为子查询	[eg：`<id->`]*
index_subquery	where条件in形式的子查询引用了非唯一索引，等价于将类型为 ref 的查询作为子查询	[eg：`<id->`]*
range	对于表的索引列使用范围判定的查询	[eg：<id-13>]
index	除了查找索引树之外，与 ALL 选项基本一致，通常由于索引较小查询会快一点	[eg：<id-14>]
ALL	完整浏览整个表格，查找符合条件的结果，属于最差的访问方式	[eg：<id-1>]

possible_keys

possible_keys 列表示查询所需数据过程可能用到的索引名，具体是否使用还要依赖于查询过程中表的连接顺序，该值为 NULL 时表示无索引可用，此时需要考虑对表进行优化来改善查询结果情况了。

取值	含义	例子编号
索引名	查询时可能用到的索引名，是否使用取决于查询连接顺序	[eg：<id-7>]
NULL	该查询没有可用索引，需要考虑优化	[eg：<id-1>]

key

key 列表示查询所需数据过程确实用到的索引名，该值为 NULL 时表示无索引可用，此时也需要考虑对表进行优化。

取值	含义	例子编号
索引名	查询时确实用到的索引名	[eg：<id-7>]
NULL	查询时没有可用索引，需考虑优化	[eg：<id-1>]

key_len

key_len 列表示查询所需数据过程用到的索引长度，该值为 NULL 时表示没有使用索引，由于存储格式的不同，对于可以为 NULL 的列储存索引所需空间要比不能为 NULL 列的大一个字节。

取值	含义	例子编号
索引长度	查询时确实用到的索引长度	[eg：<id-7>]
NULL	没有使用到索引	[eg：<id-1>]

ref

key_len 列表示查询时使用常数或者某一列来和索引列比较，有时会显示 func，表示使用了一些函数的结果与索引比较，值为 NULL 时表示没用到索引比较

取值	含义	例子编号
引用列名	查询时与索引比较的列名，形式可能为<subquery2>.id，表示引用了子查询结果中的id列	[eg：<id-5>]
const	查询时与索引比较的为常数	[eg：<id-10>]
func	查询时与索引比较的一些函数结果	[eg：`<id->`]*
NULL	不是上述几种情况，可能没有使用索引比较	[eg：<id-1>]

rows

rows 列表示查询符合条件的结果时所要检查的数据行数，在 InnoDB 类型的表中，这个值是一个估计值，可用来参考并不精确，值为 NULL 时表示表中无数据，或者无法找到匹配行，比如查找一条主键中不包含的数据。

取值	含义	例子编号
数字	查询时所要检查的数据行数	[eg：<id-3>]
NULL	没有数据或者不需要检测	[eg：<id-1>]

filtered

filtered 列表示通过筛选条件的记录数占可能参与检查的记录数，是一个估计值，该值与 rows 的乘积大概就是结果集中的记录数：

取值	含义	例子编号
数字	通过筛选条件的记录数占可能参与检查的记录数，最大为 100.00	[eg：<id-3>]

Extra

Extra 列表示Mysql处理查询所使用的额外信息，类型很多，其中一些情况是需要进行优化的信号，对于SQL分析很有帮助：

取值	含义	例子编号
Child of 'tbl_name' pushed join@1	当表被当做另一个表’tbl_name’的子表能被存放在 NDB 内核的时候，该值只出现在存储选项被开启的 NDB 集群上	-
const row not found	当一个系统表没有数据可查的时候	-
Deleting all rows	对于 DELETE 操作， MyISAM 引擎支持一个可以简单快速删除表数据的方法，如果使用了整个优化则显示此选项	-
Distinct	查询时使用了 distinct 关键字，当查找到一个第一个匹配值后，相同匹配就不再搜索了	-
const	当一个系统表没有数据可查的时候	-
FirstMatch(tbl_name)	当 semi-join FirstMatch 访问简化策略被使用时候， 通常出现在 Where的 in 子句中，找到一个值后，后面相同值不再匹配出现	-
Full scan on NULL key	当优化器不能使用索引查找访问方法时，将会显示该值，表示将子查询作为一种后备策略	-
Impossible HAVING	当 HAVING 子句的条件总是不成立，无法匹配出任何数据	[eg：<id-23>]
Impossible WHERE	当 WHERE 子句的条件总是不成立，无法匹配出任何数据	[eg：<id-22>]
Impossible WHERE noticed after reading const tables	在读取const表和system表时，WHERE 子句的条件总是不成立	-
LooseScan(m..n)	当 semi-join LooseScan 策略被使用的时候. m 和 n 是索引的编号	-
No matching min/max row	在查询中包括系统函数，但是通过条件查询无法匹配出数据的时候， 比如SELECT MIN(...) FROM ... WHERE CONDITION	-
no matching row in const table	当连表查询时有一个空表或者没有匹配唯一索引的数据时，会给出此提示	[eg：<id-18>]
No tables used	当查询中没有 FROM 子句或者只有 FROM DUAL子句时	[eg：<id-20>]
Not exists	发生在左外连接的优化，当要求右侧表字段为空时，如果查找到一条不为空匹配，则停止查找匹配这项记录， 比如 SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL	-
Plan isn't ready yet	执行命令 EXPLAIN FOR CONNECTION 时，优化器在命名连接中还没有完成为语句执行创建执行计划	-
Range checked for each record(index map: N)	当没有好的默认索引可使用时，但当我们可以将以前表中的所有列都视为常量时，可能会使用某些索引就是这种情况	-
Scanned N databases	表示在执行对INFORMATION_SCHEMA表的查询时，服务器执行了多少次目录扫描，数字可以是0,1或者任何整数	-
Select tables optimized away	优化器发现只有一行，并且通过索引直接皆可以获得想要的数据，而不需要真正访问表数据，比如在索引列使用聚合函数	[eg：<id-16>]
Skip_open_table	对于 INFORMATION_SCHEMA 表的查询，不需要打开表，只需要浏览目录就可以完成查询	-
Open_frm_only	对于 INFORMATION_SCHEMA 表的查询，需要打开 .frm 文件完成查询	-
Open_full_table	对于 INFORMATION_SCHEMA 表的查询，需要打开 .frm, .MYD, .MYI 文件完成查询	-
Start temporary,End temporary	表示使用临时表用于 semi-join Duplicate Weedout 策略	[eg：<id-15>]
unique row not found	当一个拥有 UNIQUE 索引或者 PRIMARY 索引的表没有查到满足条件数据时	-
Using filesort	无法仅通过引用索引就完成排序，需要一个额外的阶段来进行外部排序，并且按排序结果取回记录	[eg：<id-17>]
Using index	只通过索引排序就可以取得排序后的数据，无需做额外的搜索真实记录数据的工作	[eg：<id-7>]
Using index condition	首先通过访问索引元组的方式来读取表格，除非必要时会通过索引索引信息延迟读取整个表格数据	-
Using index for group-by	索引用于处理包含 GROUP BY 和 DISTINCT 的查询，由于重复项会被快速跳过。所以非常高效	-
Using join buffer (Block Nested Loop)	连接访问之前表格数据被部分读入连接缓存区，然后使用缓存中的行与当前表进行连接，括号内容为使用算法	[eg：<id-1>]
Using join buffer (Batched Key Access)	连接访问之前表格数据被部分读入连接缓存区，然后使用缓存中的行与当前表进行连接，括号内容为使用算法	-
Using MRR	表格数据会通过 Multi-Range Read 优化策略来读取	-
Using sort_union(...), Using union(...), Using intersect(...)	针对于 index_merge 选项，表明索引浏览被合并的特定算法	-
Using temporary	需要创建一个临时表来存储结果，通常出现在包含了作用在不同列的上的 GROUP BY 子句和 ORDER BY子句	[eg：<id-2>]
Using where	当查询使用了 WHERE 子句来过滤结果发送给客户端的时候	[eg：<id-3>]
Using where with pushed condition	仅适用于 NDB 类型表，它意味着NDB集群正在使用 “条件存储”优化选项来提高接近于非索引列和常量之间直接比较的效率。	-
Zero limit	当查询语句包含 LIMIT 0子句并不能查到任何记录的时候	[eg：<id-19>]

EXPLAIN 各列的可能取值对应的例子

建表操作(1

为了展示id取值的不同先创建 a、b两个表，然后插入测试数据，由于是测试的开始，我们分别查看表结构和数据，之后为了减少篇幅，只给出命令，不再查询表结构和数据，接着我们开始测试：

mysql> create table a(id int, num int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into a values(1, 100),(2,200),(3,300);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| num   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> select * from a;
+----+-----+
| id | num |
+----+-----+
|  1 | 100 |
|  2 | 200 |
|  3 | 300 |
+----+-----+
3 rows in set (0.02 sec)

mysql> create table b(id int, num int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into b values(1, 100),(2,200),(4,400);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc b;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| num   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> select * from b;
+----+-----+
| id | num |
+----+-----+
|  1 | 100 |
|  2 | 200 |
|  4 | 400 |
+----+-----+
3 rows in set (0.03 sec)

<id-1>

mysql> explain select * from a, b;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                                  |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set (0.05 sec)

<id-2>

mysql> explain select * from a union select * from b;
+------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id   | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|    1 | PRIMARY      | a          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL            |
|    2 | UNION        | b          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL     | Using temporary |
+------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set (0.05 sec)

<id-3>

mysql> explain select id from a where id = (select id from b where num = 100);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
|  2 | SUBQUERY    | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set (0.06 sec)

<id-4>

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.6.33    |
+-----------+
1 row in set (0.05 sec)

mysql> explain select num from (select * from a) t where t.id > 1;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
|  2 | DERIVED     | a          | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL        |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.06 sec)

-- 很神奇的是我在5.7版本操作了半天也没出现，内部进行了优化，相同的语句操作如下：
mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.21-log |
+------------+
1 row in set (0.05 sec)

mysql> explain select num from (select * from a) t where t.id > 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.05 sec)

建表操作(2

新建两个带索引的表格 c 和 d，其中表格c带有普通索引，表格d带有主键，这两个表格会参与后面用作展示的例子：

mysql> create table c(id int, num int, key idindex(id));
Query OK, 0 rows affected (0.21 sec)

mysql> insert into c values(1,103),(2,203),(6,603);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table d(id int, num int, primary key(id));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into d values(1,104),(2,204),(6,504);
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

<id-5>

mysql> explain select id from d where id in (select id from a);
+----+--------------+-------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key     | key_len | ref            | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           | NULL |   100.00 | Using where |
|  1 | SIMPLE       | d           | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | <subquery2>.id |    1 |   100.00 | Using index |
|  2 | MATERIALIZED | a           | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           |    3 |   100.00 | NULL        |
+----+--------------+-------------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+
3 rows in set (0.05 sec)

建表操作(3

为了测试 partitions 字段的取值，创建表格 p，其实就是创建了一个带有分区的表，这个表格会参与后面用作展示的例子：

mysql> create table p(id int, num int) partition by range(id)(partition p0 values less than(3), partition p1 values less than(6));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into p values(1, 111),(2,222),(4,444),(5,555);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

<id-6>

mysql> explain select * from p where id < 5;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | p     | p0,p1      | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.05 sec)

<id-7>

mysql> explain select id from c where id = 1;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | c     | NULL       | ref  | idindex       | idindex | 5       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.06 sec)

建表操作(4

为了测试 type 字段的取值，有时需要特定的数据引擎才可以，所以创建了以 MyISAM引擎类型的表格 m，然后进行一些测试：

mysql> create table m(id int, num int)engine=myisam;
Query OK, 0 rows affected (0.02 sec)

<id-8>

mysql> explain select * from m;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set (0.03 sec)

<id-9>

mysql> insert into m values(1,1001);
Query OK, 1 row affected (0.00 sec)

mysql> explain select * from m;
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | m     | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.04 sec)

<id-10>

mysql> explain select id from d where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | d     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set (0.04 sec)

<id-11>

mysql> explain select a.id from a, d where a.id = d.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |    3 |   100.00 | Using where |
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | sqltest2.a.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
2 rows in set (0.05 sec)

<id-12>

mysql> explain select id from c where id = 1 or id is null;
+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type        | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | c     | NULL       | ref_or_null | idindex       | idindex | 5       | const |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+--------------------------+
1 row in set (0.04 sec)

<id-13>

mysql> explain select id from c where id > 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | c     | NULL       | range | idindex       | idindex | 5       | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set (0.04 sec)

<id-14>

mysql> explain select id from c;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | c     | NULL       | index | NULL          | idindex | 5       | NULL |    3 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.03 sec)

<id-15>

mysql> explain select id from c where id in (select a.id from a, d where a.id = d.id);
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra                        |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |    3 |   100.00 | Using where; Start temporary |
|  1 | SIMPLE      | c     | NULL       | ref    | idindex       | idindex | 5       | sqltest2.a.id |    1 |   100.00 | Using index                  |
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | sqltest2.a.id |    1 |   100.00 | Using index; End temporary   |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+------------------------------+
3 rows in set (0.04 sec)

<id-16>

mysql> explain select min(id) from c;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set (0.05 sec)

<id-17>

mysql> explain select id from c order by num;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set (0.04 sec)

<id-18>

mysql> explain select id from d where id = 100;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set (0.04 sec)

<id-19>

mysql> explain select id from d limit 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Zero limit |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
1 row in set (0.04 sec)

<id-20>

mysql> explain select 1 from dual;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set (0.04 sec)

<id-22>

mysql> explain select * from a where 1 = 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set (0.03 sec)

<id-23>

mysql> explain select sum(num) from a group by id having 1 =2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra             |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Impossible HAVING |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------+
1 row in set (0.04 sec)

总结

• 关于 EXPLAIN 命令的所有可能取值后面，还有部分例子是空的，完全是由于个人水平有限，等找到所说的取值情况再补充，也欢迎大家提供例子
• 另外 EXPLAIN 提供的信息中没有关于触发器、存储过程的信息或者评估用户自定义函数对查询的影响情况
• 所有的可能取值中 possible_keys、rows、filtered中的统计信息基本是估算的，并非精确值，只能用来做优化参考
• Extra 列的信息对于尝试优化起到了至关重要的作用，当出现 Using filesort、Using temporary、Using join buffer的时候一般就要考虑采取优化方案了
• 首先了解这些可能出现的情况，之后我们留这里利用这些说明来进行查询优化了