MySQL查询执行计划详解——explain

一、前言

本文来自官方文档

explain用于解释优化器在执行select、update、delete、insert、replace语句时的执行计划，即它解释了MySQL如何处理SQL语句，包括表如何连接、表的连接顺序、用了哪些索引等。（replace是MySQL对于标准SQL语句的扩展，其他数据库可能没有，replace的使用见这里）

本文使用的表结构和数据如下：

CREATE DATABASE test;

CREATE TABLE trb1 (
    id int auto_increment primary key,
    name varchar(50),
    purchased date
)
PARTITION BY RANGE (ID) (
    PARTITION p0 VALUES LESS THAN (3),
    PARTITION P1 VALUES LESS THAN (7),
    PARTITION P2 VALUES LESS THAN (9),
    PARTITION P3 VALUES LESS THAN (11)
);

INSERT INTO trb1 VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'CD player', '1993-11-05'),
(3, 'TV set', '1996-03-10'),
(4, 'bookcase', '1982-01-10'),
(5, 'exercise bike', '2004-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'popcorn maker', '2001-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '1984-09-16'),
(10, 'lava lamp', '1998-12-25');

create table trb2(
    id2 int auto_increment primary key,
    id int
);

insert into trb2(id) values(3), (3), (4);

create table trb3(
    id3 int,
    name varchar(50),
    purchased date,
    primary key(id3, name),
    index trb3_index1(name, purchased)
);

insert into trb3 values
(1, 'desk organiser', '2003-10-15'),
(2, 'CD player', '1993-11-05'),
(3, 'TV set', '1996-03-10'),
(4, 'bookcase', '1982-01-10'),
(5, 'exercise bike', '2004-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'popcorn maker', '2001-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '1984-09-16'),
(10, 'lava lamp', '1998-12-25');

文章目录：

目录

• MySQL查询执行计划详解——explain
  • 一、前言
  • 二、explain输出格式解释
    • 1、id
    • 2、select_type
    • 3、table
    • 4、partition
    • 5、type
    • 6、possible key
    • 7、key
    • 8、key_len
    • 9、ref
    • 10、rows
    • 11、filtered
    • 12、Extra
  • 三、explain的扩展输出格式
  • 四、explain的其他用法
    • explain for connection
    • explain table_name
  • 五、总结

二、explain输出格式解释

EXPLAIN为SELECT语句中使用的每个表返回一行信息，按照MySQL在处理语句时读取它们的顺序列示。explain的输出列如下。

Column	JSON Name	Meaning
id	select_id	查询的唯一标识，
select_type	None	select类型
table	table_name	表名，如设置了别名（alias）则展示别名
partitions	partitions	查询计划匹配到的分区
type	access_type	连接类型
possible_keys	possible_keys	可能使用的索引
key	key	实际使用的索引
key_len	key_length	实际使用的索引的字节长度
ref	ref	与索引比较的列
rows	rows	估计要检查的行数量
filtered	filtered	按表条件过滤的行百分比
Extra	None	额外信息

1、id

MySQL会给每一个查询分配一个id，归属同一个查询的行则该标识相同，不同的查询按序号顺序列示。注意并不是每有一个select就会有一个独立的id，如下：

mysql> explain select * from trb1 t1 where exists(select 1 from trb2 t2 where t2.id = t1.id); -- id相同
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------+
| id | select_type | table | partitions  | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra
                            |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL        | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Start temporary
                            |
|  1 | SIMPLE      | t1    | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where; End temporary; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

mysql> explain select t1.*, (select t2.id2 from trb2 t2 where t2.id = t1.id limit 1) as id2 from trb1 t1; -- id不同
+----+--------------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions  | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | t1    | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL        |
|  2 | DEPENDENT SUBQUERY | t2    | NULL        | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+--------------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

2、select_type

select_type Value	JSON Name	Meaning
SIMPLE	None	简单的select语句，没使用union或子查询
PRIMARY	None	最外层的select语句
UNION	None	union中的第二个或更后面的select语句
DEPENDENT UNION	dependent (true)	union中的第二个或更后面的select语句，依赖于外部查询（不理解）
UNION RESULT	union_result	union的结果，把union查询当作一个临时表的结果
SUBQUERY	None	子查询的第一个select语句
DEPENDENT SUBQUERY	dependent (true)	子查询的第一个select语句，依赖于外部查询
DERIVED	None	派生表，临时表
DEPENDENT DERIVED	dependent (true)	依赖于另外一个表的临时表
MATERIALIZED	materialized_from_subquery	物化子查询
UNCACHEABLE SUBQUERY	cacheable (false)	一个无法缓存的子查询，外部查询的每一行都要重新执行子查询
UNCACHEABLE UNION	cacheable (false)	属于一个uncacheable subquery的union查询的第二个或更后面的查询

看下面这个查询。

id为1的是外部主查询，表名是，即id为3的derived类型的表，即别名为b的表，explain中没把b展示出来。id为2的是一个临时表，其表名为table1，可能是因为使用了union，展示的union中的第一个表名。

id为4的是一个uncacheable union，即无法被缓存的子查询，且存在于一个union中，处于union的第二个或更后面的位置。因为order by rand()，每次都要重新执行这次查询才能获取结果，因此无法被缓存。

id为NULL的是一个union结果，表名为<union3,4>，即union了id为3和4两张表的结果。

id为2的是一个UNCACHEABLE SUBQUERY，解释如id为4的一样。

mysql> EXPLAIN select b.*, (SELECT table3.id as c from trb1 table3 order by rand() limit 1) AS c FROM ( (select table1.id as a from trb1 table1 order by rand() LIMIT 1) UNION (select table2.id as a from trb1 table2 order by rand() LIMIT 1) ) as b;
+----+----------------------+------------+-------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type          | table      | partitions  | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra
    |
+----+----------------------+------------+-------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | PRIMARY              | <derived3> | NULL        | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL
    |
|  3 | DERIVED              | table1     | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary; Using filesort |
|  4 | UNCACHEABLE UNION    | table2     | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary; Using filesort |
| NULL | UNION RESULT         | <union3,4> | NULL        | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary
      |
|  2 | UNCACHEABLE SUBQUERY | table3     | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary; Using filesort |
+----+----------------------+------------+-------------+------+---------------+------+---------+------+------+----------+---------------------------------+
5 rows in set, 1 warning (0.00 sec)

当子查询中依赖外部表来获取结果时，就会有一个dependent，如下。

mysql> explain select t1.*, (select t2.id2 from trb2 t2 where t2.id = t1.id limit 1) as id2 from trb1 t1;
+----+--------------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions  | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | t1    | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL        |
|  2 | DEPENDENT SUBQUERY | t2    | NULL        | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+--------------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

DEPENDENT SUBQUERY评估与UNCACHEABLE SUBQUERY评估不同：对于DEPENDENT SUBQUERY，子查询仅针对其外部上下文中变量的每组不同值重新评估一次。对于UNCACHEABLE SUBQUERY，将为外部上下文的每一行重新评估子查询。即DEPENDENT SUBQUERY是有一部分缓存的。

3、table

table指的是表名或别名，或其他形式名称。（<DERIVEDN>，<UNIONM,N>，<subqueryN>，加粗字母均为表对应的id。具体见上面的分析）

4、partition

partition指的是该查询所使用到的表分区。关于表分区的解释见官方文档。如上面那个trb1表使用了所有的分区，又如下面这个查询只用到了p0、p1分区。

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

5、type

type指的是join type，即表之间是如何连接的。下面从最优到最差的方式排序列出了各种连接方式。

• system：指表中只有一条记录，且符合const类型的查询。是一种特殊const类型。

• const：指通过primary key或unique查询出来的数据，最多只有一条记录匹配。

• eq_ref：指该表通过完整的primary key或uniqeu not null去和其他表相应字段连接时，则该表的join type为qe_ref，另外一张表的join type根据另外一张表的key去判断。这种情况下可以保证最多只能匹配出一条记录。如下，trb1表是eq_ref，而trb2表的join type是ALL，全表扫描。

mysql> explain select * from trb1, trb2 where trb1.id = trb2.id;
+----+-------------+-------+-------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions  | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra       |
+----+-------------+-------+-------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
|  1 | SIMPLE      | trb2  | NULL        | ALL    | NULL          | NULL    | NULL    | NULL         |    3 |   100.00 | Using where |
|  1 | SIMPLE      | trb1  | p0,P1,P2,P3 | eq_ref | PRIMARY       | PRIMARY | 4       | test.trb2.id |    1 |   100.00 | NULL        |
+----+-------------+-------+-------------+--------+---------------+---------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

• ref：指该表通过某个索引的最左前缀的部分或完整字段或多字段主键中满足最左前缀的部分字段去和其他表字段连接时，则该表的join type为ref。这种情况下无法保证匹配出最多一条记录。如下：

mysql> explain select * from trb3 where id3 = 1;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | trb3  | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select t3.*, t2.id2 from trb3 t3, trb2 t2 where t3.id3 = t2.id2;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL        |    3 |   100.00 | Using index |
|  1 | SIMPLE      | t3    | NULL       | ref   | PRIMARY       | PRIMARY | 4       | test.t2.id2 |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

• fulltext：使用全文索引。大多业务使用较少，除非那种需要检索大量文本的业务。全文索引仅排在ref后面，说明全文索引的效率很高。
  全文索引大概意思就是专门用于文本查询的一个索引，只能构建在char、varchar、text类型上，通过全文索引的查询有自己特殊的语法（match(index_column) again('xxxx')），全文索引的检索有最小搜索长度和最大搜索长度限制（当然可以通过修改my.ini修改配置），表的行数量条件要求，以及各种殷勤和版本限制等。关于全文索引的具体介绍见官方文档。

• ref_or_null：即在ref情况下，使用索引的后面一个或多个字段使用is null来匹配，如下。

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

• index_merge：索引合并。索引合并大概意思就是查询使用了多个索引并且可以合并这些索引以查询数据。具体见官方文档。如下就是一个索引合并。

mysql> explain select * from trb3 where id3 = 1 or name = 'CD player';
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra
                                    |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | trb3  | NULL       | index_merge | PRIMARY,trb3_index1 | trb3_index1,PRIMARY | 202,4   | NULL |    2 |   100.00 | Using sort_union(trb3_index1,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

• unique_subquery：官方文档说是这种是在IN子查询中使用索引覆盖，以提高效率，如下，但是本人实际测试中并未使用这种join type。

value IN (SELECT primary_key FROM single_table WHERE some_expr)

• index_subquery：和上一个相似，区别是不用primary_key，而是使用普通的索引。

value IN (SELECT key_column FROM single_table WHERE some_expr)

• range：索引被用作范围查询时可能使用range类型，有以下几点值得注意：
  • join type为range时，key_len为使用的索引的最大长度
  • join type为range时，ref字段为NULL
  • =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() 都可能会使用range
  • 索引用作范围查询时并不一定使用range，也可能使用其他，优化器会根据实际情况选择。如下第一个SQL即使用了index。
  • range也要满足最左前缀原则，不满足则可能使用其他类型，如下方代码块中的最后一个SQL。

mysql> explain select * from trb3 where name > 'aa';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | trb3  | NULL       | index | trb3_index1   | trb3_index1 | 206     | NULL |   10 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from trb3 where name > 'sofa';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | trb3  | NULL       | range | trb3_index1   | trb3_index1 | 202     | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from trb3 where name > 'sofa' and purchased < '2020-01-01';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | trb3  | NULL       | range | trb3_index1   | trb3_index1 | 202     | NULL |    2 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from trb3 where purchased < '2020-01-01';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | trb3  | NULL       | index | trb3_index1   | trb3_index1 | 206     | NULL |   10 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

• index：虽然使用了索引中的字段进行查询，但是不满足最左前缀原则，则MySQL会在索引树中全树扫描，这就是index，如上面代码块中的第一个SQL和最后一个SQL。最后一个SQL使用index好理解，第一个SQL使用index个人认为是优化器发现索引树中的最小值 > 查询条件'aa'，因此name > 'aa'就等于全树扫描，所以为index。如下当条件为name > 'ar'时，就使用了range，且任何条件大于索引树中的最小值的查询，都会使用range，而小于则全树扫描。
  index的效率比ALL高一点点，毕竟扫描全树的IO事件比扫描全表的IO事件更少。

mysql> explain select * from trb3 where name > 'ar';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | trb3  | NULL       | range | trb3_index1   | trb3_index1 | 202     | NULL |    9 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

• ALL：全表扫描，未走任何索引，效率最低。

6、possible key

可能使用的索引，没啥好讲的。

7、key

实际使用的索引，也没啥好讲。

index hint是指，让MySQL按照我们的给定的索引去查询数据，主要有force index、use index和ignore index，也可以加上for join | order by | group by来指定索引使用的范围，如下SQL。具体使用见官方文档。

force index和use index的区别是：force index会强制使用该索引，但use index是建议MySQL使用该索引，但是优化器还是会根据实际情况来选择是否要全表扫描。

mysql> explain select * from trb3 force index for order by (trb3_index1) where id3 > 3 order by name;
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys       | key     | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | trb3  | NULL       | range | PRIMARY,trb3_index1 | PRIMARY | 4       | NULL |    7 |   100.00 | Using where; Using filesort |
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

8、key_len

该字段指的是查询执行时实际使用的索引的总最大字节长度，当所有有多个字段时，可以通过这个来看一个查询具体使用了哪几个字段。int占用4个字节，varchar每一个字符占用4个字节和2个字符存储字符串长度（varchar中文实际上大部分占3个字节，少量才占用四个字节，这里按最大的算），因此varchar(50)占用202个字节。如下通过查看字节长度就可以发现使用了多字段索引中的哪几个字段。

mysql> explain select * from trb3 where id3 = 3;
+----+-------------+-------+------------+------+---------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys       | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | trb3  | NULL       | ref  | PRIMARY,trb3_index1 | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from trb3 where name > 'sdf';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | trb3  | NULL       | range | trb3_index1   | trb3_index1 | 202     | NULL |    5 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from trb3 where id3 = 3 and name > 'a';
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys       | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | trb3  | NULL       | range | PRIMARY,trb3_index1 | PRIMARY | 206     | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

9、ref

该字段显示了用哪些列或常量来和索引字段去匹配以查询数据。当索引和一个常量匹配时，ref字段为const，当使用索引行进范围查询时，ref字段为NULL。

mysql> explain select * from trb2 where id2 in (select id from trb1 where name = 'aquarium');
+----+-------------+-------+-------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions  | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+-------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | trb2  | NULL        | ALL    | PRIMARY       | NULL    | NULL    | NULL          |    3 |   100.00 | NULL        |
|  1 | SIMPLE      | trb1  | p0,P1,P2,P3 | eq_ref | PRIMARY       | PRIMARY | 4       | test.trb2.id2 |    1 |    10.00 | Using where |
+----+-------------+-------+-------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select * from trb3 where id3 = if(id3 > 3, 5, 2);
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | trb3  | NULL       | index | NULL          | trb3_index1 | 206     | NULL |   12 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

10、rows

rows列表示MySQL认为执行查询必须检查的行数。对于InnoDB表，此数字是估计值，可能并不总是准确的。

11、filtered

该列指的是按表条件过滤的表行的估计百分比。最大值为100，这表示未过滤行。值从100减小表示过滤量增加。rows × filtered的值表示与下表连接的行数。例如，如果行数为1000，过滤条件为50.00（50％），则与下表连接的行数为1000×50％= 500。

12、Extra

该列展示了SQL执行计划的额外信息，包括太多的内容，大部分是很少见的，以下主要解释几个重要的值得优化的内容：

• Using filesort：意为MySQL必须额外对检索出来的数据进行一次排序再输出这些数据。
  排序是通过根据连接类型遍历所有行并存储与WHERE子句匹配的所有行的排序键和指向该行的指针来完成的。即排序会using filesort会遍历所有行，存储通过where条件筛选出的行的排序字段和指向该行的指针，再对排序字段值和指针进行排序，再按照指针顺序输出数据。
  因此这种排序方式是特别慢的，排序优化见官方文档。

• Using index：仅使用索引树中的信息从表中检索列信息，而不必进行其他查找以读取实际行。

• Using index condition：通过访问索引集并首先对其进行测试以确定是否需要读取完整的表。除非有必要整表扫描，否则索引信息将用于延迟（“下推push down”）再读取整个表行。
  索引条件下推（Index Condition Pushdown）是针对MySQL使用using index从表中检索行的情况的一种优化。如果不使用ICP，则存储引擎将遍历索引以在基表中定位行，并将其返回给MySQL服务器，后者将评估这些行的WHERE条件。启用ICP后，如果仅可以使用索引中的列来评估WHERE条件的一部分，则MySQL服务器会将WHERE条件的这一部分下推到存储引擎。然后，存储引擎通过使用索引条目来评估推送的索引条件，并且只有在满足此条件的情况下，才从表中读取行。 ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。见官方文档。

• Using index for group-by：即有一个索引可以可用于检索GROUP BY或DISTINCT查询的所有列，类似于group by的索引覆盖。

• Using temporary：使用临时表。

• Using where：即存在where条件，且where字段不在任意一个索引中，不能使用索引树进行where匹配，而必须在检查所有行再把满足where条件的数据输出给客户端。

三、explain的扩展输出格式

explain输出列中的Extra列实际上并不是explain的，而是show warnings的结果，可以在使用explain后，可以紧跟着使用show warnings命令查看完整的extended information。

8.0.12版本之前，show warnings只适用于select，8.0.12版本之后，它适用于select、delete、update、replace、insert。

show warnings的message列显示了优化器如何限定select语句中的表名和列名，select语句在应用优化器的优化和重写之后的样子（会额外提供一些特殊标记，不一定是有效的SQL），以及其他与优化器处理有关的信息。如下。

mysql> explain select t1.id, t1.id in (select id from trb2) from trb1 t1;
+----+-------------+-------+-------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions  | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+-------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | t1    | p0,P1,P2,P3 | index | NULL          | PRIMARY | 4       | NULL |   10 |   100.00 | Using index |
|  2 | SUBQUERY    | trb2  | NULL        | ALL   | NULL          | NULL    | NULL    | NULL |    3 |   100.00 | NULL        |
+----+-------------+-------+-------------+-------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message

                                                                                                                   |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`t1`.`ID` AS `id`,<in_optimizer>(`test`.`t1`.`ID`,`test`.`t1`.`ID` in ( <materialize> (/* select#2 */ select `test`.`trb2`.`id` from `test`.`trb2` where true having true ), <primary_index_lookup>(`test`.`t1`.`ID` in <temporary table> on <auto_key> where ((`test`.`t1`.`ID` = `materialized-subquery`.`id`))))) AS `t1.id in (select id from trb2)` from `test`.`trb1` `t1` |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

show warnings中message列可能包含的特殊标记见官方文档，如下。

• <auto_key>
  为临时表自动生成的索引。

• <cache>(expr)
  expr表达式执行一次，将结果保存在内存中备用。对于有很多个缓存值，MySQL会创建一个临时表，并显示<temporary table>。

• <exists>(query fragment)
  子查询将转换为EXISTS语句。和子查询优化有关，子查询优化有物化成临时表、semi join和转换成EXISTS语句等优化方法，其中如果使用转换成EXISTS这种优化方式时，可能就会有<exists>标记（盲猜的）。见官方文档8.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions。

• <in_optimizer> (query fragment)
  指这是一个内部优化器对象，对用户没有任何意义。

• <index_lookup> (query fragment)
  使用索引查找来处理查询片段以查找合格的行。

• <if>(condition, expr1, expr2)
  if条件，condition条件为真则执行expr1，否则执行expr2。

• <is_not_null_test>(expr)
  用于验证表达式是否为null的测试。

• <materialize>(query fragment)
  物化子查询，见<exists>。

• `materialized-subquery`.col_name
  一个子查询结果被物化成内部临时表后，这个临时表对某一列的引用。

• <primary_index_lookup>(query fragment)
  使用主键查找来处理查询片段以查找合格的行。

• <ref_null_helper>(expr)
  这是一个内部优化器对象，对用户没有任何意义。

• /* select#N */ select_stmt
  指与explain中对应的某一个SELECT语句，N即为explain中的id。

• outer_tables semi join (inner_tables)
  半联接操作。inner_tables显示未拉出的表。见<exists>。

• <temporary table>(expr)
  为缓存结果创建的一个临时表。

四、explain的其他用法

explain for connection

可以通过show processlist查看连接线程列表，或通过select connection_id()查看当前连接线程的id。

explain for connection connection_id的用处是查看id为connection_id的线程当前正在执行的SQL语句的执行计划。如果那个线程当前没有执行SQL语句，则结果为空；如果那个线程当前执行的SQL语句不是select、update、replace、insert、delete中的任意一个，则会报错。如下：

mysql> show processlist;
+-----+-----------------+-----------------+-------+---------+--------+------------------------+------------------+
| Id  | User            | Host            | db    | Command | Time   | State                  | Info             |
+-----+-----------------+-----------------+-------+---------+--------+------------------------+------------------+
|   4 | event_scheduler | localhost       | NULL  | Daemon  | 733424 | Waiting on empty queue | NULL             |
| 508 | root            | localhost:2004  | test  | Query   |      0 | starting               | show processlist |
| 509 | root            | localhost:1748  | xxxx | Sleep   |    275 |                        | NULL             |
| 510 | root            | localhost:5639  | xxxx | Sleep   |    275 |                        | NULL             |
| 515 | root            | localhost:13576 | xxxx   | Sleep   |  45170 |                        | NULL             |
| 516 | root            | localhost:13578 | xxxx   | Sleep   |  45170 |                        | NULL             |
+-----+-----------------+-----------------+-------+---------+--------+------------------------+------------------+
6 rows in set (0.00 sec)

mysql> explain for connection 516;
Query OK, 0 rows affected (0.00 sec)

mysql> explain for connection 508;
ERROR 3012 (HY000): EXPLAIN FOR CONNECTION command is supported only for SELECT/UPDATE/INSERT/DELETE/REPLACE

explain table_name

explain table_name = show columns from table_name = describe table_name

mysql> explain trb1;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| ID        | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(50) | YES  |     | NULL    |                |
| purchased | date        | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

五、总结

explain的用处其实就是让MySQL告诉你某一个SQL查询执行时，优化器会怎么优化它，存储引擎会采用怎样的表连接方式，采用哪些索引，执行该SQL必须扫描的行数量（估计数），和其他可以用于SQL优化的信息。通过获取这些信息，我们就可以发现一个SQL语句执行慢的原因，并作出合理的优化。