深入理解 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...
https://blog.csdn.net/UncleMo...

最后，如果喜欢本篇文章，欢迎点赞和收藏。
更多精彩内容请关注我的微信公众号：Python数据科学