深入理解 MySql 的 Explain

为了了解自己写的SQL是好是坏，MySql提供了Explain执行计划功能。它对优化SQL语句尤为的重要，通过它可以看清执行过程的细节，分析查询语句或是结构的性能瓶颈，找到问题所在。

如何使用Explain？

explain的使用很简单，就是在select 语句之前增加 explain关键字就ok了。MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL。比如这样：

# explain + sql
explain select * from table where a = 1;

Explain执行计划能做什么？

• 确定表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

可以看出执行计划给我们提供的信息是非常有帮助的。只有读懂了这些内容，才能定位问题点在哪，进而去解决。下面东哥给大家介绍一下explain执行计划的内容。

因为有些字段光看很难理解，因此建立三个表作为例子来说明，感兴趣的朋友也可以自己跑下试试。

DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
 `id` int(11) NOT NULL,
 `name` varchar(45) DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18');
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (2,'b','2017-12-22 15:27:18');
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (3,'c','2017-12-22 15:27:18');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0');
INSERT INTO `film` (`id`, `name`) VALUES (1,'film1');
INSERT INTO `film` (`id`, `name`) VALUES (2,'film2');

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
 `id` int(11) NOT NULL,
 `film_id` int(11) NOT NULL,
 `actor_id` int(11) NOT NULL,
 `remark` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1);
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (2,1,2);
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (3,2,1);

注意：上面三张表中，actor主键为id；film主键为id，以name字段为索引；film_actor表中id为主键，以film_id和actor_id为联合索引。

执行计划的内容介绍

我们在Navicat里随便执行一个查询语句，看看都会返回哪些内容。

explain select (select id from actor limit 1) from film;

执行后的结果不是查询的数据而是执行计划的解释，一共有


id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra这些字段，每个都代表不同的含义，下面详细介绍。

id

id 决定了每个表的加载和读取顺序。比如你写了个复杂的嵌套逻辑，有很多子查询，那每个select执行的顺序就可通过id序列号观察出来。

原则是：id值越大越先被执行。id值相同的按从上到下的顺序执行。id为NULL的最后执行。

1、id相同

explain select * from film, actor, film_actor where film.id=actor.id and film.id=film_actor.id;

2、id不同

explain select (select id from actor limit 1) from film;

select_type

select查询的类型主要有三大类：

1、简单类型

SIMPLE：最简单的select查询，就是查询中不包含子查询或者union，表里如一。

explain select * from film where id=1;

2、嵌套类型

PRIMARY、SUBQUERY、DERIVED 这三个是用在有嵌套逻辑的语句中的。

PRIMARY：嵌套查询最外层的部分被标记为PRIMARY。

SUBQUERY：出现在select或者where后面中的子查询被标记为SUBQUERY。

DERIVED：这个其实我理解是SUBQUERY的一种特例，只不过出现的位置比较特殊，是在from后面的子查询，MySQL会将子查询结果存放在一个临时表中，称为派生表，因为这是我们派生出来的，而非原始表。

通过一个例子说明。

explain select (select id from actor where id = 1) from (select * from film) t;

3、组合类型

组合类型包括UNION和UNION RESULT两个。

UNION：UNION前后如果有两个select ，那么把出现在union之后的第二个select标记为UNION；如果UNION包含在from 子句的子查询中，外层select将被标记为DERIVED。

UNION RESULT：从 UNION表获取结果的select。

通过一个例子说明。

explain select id from actor union all select id from actor;

table

表示正在访问哪个表,以表的名称出现。

但是有两个特殊的情况：

1）当 from 子句中有子查询（派生表）时，那table就会以 < derivedN > 格式出现。因为此时查询所依赖的表是一个我们派生出来的表，即依赖一个 id 为 N 的子查询的。比如：

explain select (select id from actor where id = 1) from (select * from film) t;

2）当使用 union 时，UNION RESULT 的 table 值为 <union1,2>，1和2表示参与 union 的 select 行id。比如：

explain select id from actor union all select id from actor;

type

访问类型，表示MySQL是如何访问数据的，是全表扫描还是通过索引等？这是考量sql查询优化中一个很重要的指标，共分有很多种类型，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，好的sql查询至少达到range级别，最好能达到ref。下面挑几个常见且比较重要的说一下。

1. system

表里只有一行记录，这个属于const类型的特例，一行数据平时很少出现，可以忽略不计。

2. const

表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const。

system和const有啥区别呢？看解释不太好理解，举一个例子。

explain select * from (select * from film where id = 1) tmp;

这里子查询就是const，而最外层查询则为system，为什么呢？

因为子查询将主键id置于where中选择，我们知道主键是有唯一性的，所以这个子查询就只返回一行记录，即匹配了一行数据。而外层查询没得选，因为子查询派生表就给了它一行数据，也就是说它要查询的表里就一行数据。因此，system是表里只有一行数据，const是从表里选出唯一一条数据，表里可能很多数据。

3. eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。

explain select * from film_actor left join film on film_actor.film_id = film.id;

4. ref

相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。举例如下：

普通索引的简单查询

explain select * from film where name = "film1";

关联表查询，idx_film_actor_id是film_id和actor_id的联合索引。这里使用到了film_actor的左边前缀film_id部分。

explain select film_id from film left join film_actor on film.id = film_actor.film_id;

5. range

只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引

explain select * from actor where id > 1;

6. index

Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）

explain select * from film;

这里用了查找所有*，但也返回了index，这是因为这个表里的两个字段都是索引，id是主键，name也被定位为索引。

7. all

全表扫描，意味MySQL需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了。

explain select * from film_actor;

possible_keys

这一列显示查询可能使用哪些索引来查找。explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

key

这一列显示MySQL实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。如果想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len

表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的

举例说明：film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

explain select * from film_actor where film_id = 2;

ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名。举例如下：

ref为常量

explain select * from film_actor where film_id = 2;

ref为字段

explain select film_id from film left join film_actor on film.id = film_actor.film_id;

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra

最后一列展示额外的信息。有以下几种重要的值，Using filesort，Using temporary，Using index，Using where Using index，``

1、Using filesort

MySQL对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作成为“文件排序” 。这种情况下一般也是要考虑使用索引来优化的。

explain select * from actor order by name;

2、Using temporary

mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。常见于order by 和 group by。

举例如下：actor.name没有索引，此时创建了张临时表。

explain select distinct name from actor;

3、Using index

表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高 如果同时出现Using where，表明索引被用来执行索引键值的查找 如果没用同时出现Using where，表明索引用来读取数据而非执行查找动作。

explain select film_id from film_actor where film_id = 1;

索引非常重要，关于索引会专门写一篇文章介绍。

参考：

https://blog.csdn.net/belalds/article/details/80728354https://blog.csdn.net/UncleMoveBrick/article/details/84477527