Mysql Explain工具

explain工具介绍

EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的SQL语句或是性能瓶颈。

在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。

注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中。

explain中的列

id列

id 列的编号是 select 的序列号，有几个 select 就有几个id，并且 id 的顺序是按 select 出现的顺序增长的。

id 列值越大，执行的优先级越高！如果相同，则从上到下依次执行。

select_type列

select_type 表示对应行是简单还是复杂的查询。

类型	含义
simple	简单查询，不包含子查询和union
primary	复杂查询中最外层的select
subquery	子查询（不在from子句中）
derived	包含在from子句中的子查询。Mysql会将结果放在一个临时表中，也称为派生表
union	在union中第二个和随后的select
其他	.........

mysql5.7默认会对派生表进行合并优化，通过derived_merge来控制，默认为on，表示开启。

开启：set session optimizer_switch='derived_merge=on';
关闭：set session optimizer_switch='derived_merge=off';

开启时，会自动对派生表进行合并优化：

关闭时，不会合并派生表： 

子查询 select * from film where id = 1 的 select_type = DERIVED

table列

表示 explain 的一行正在访问哪个表。

当 from 子句中有子查询，且 derived_merge=off 时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。

当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

partitions列

分区相关，不用管

type列(重要)

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL

一般来说，得保证查询达到 range 级别，最好达到 ref ！

NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。
例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。

const：mysql 能对查询的某部分进行优化并将其转化成一个常量。
用于 primary key 或 unique key 的所有列与常数比较时，最多有一个匹配行，读取1次，速度比较快。

system：system是const的特例，表里只有一条元组匹配时为 system。例如下面这个sql，子查询中肯定只会返回一条记录，外层的查询就是system类型。

eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

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

1. 简单 select 查询，name 是普通索引（非唯一索引）

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

range：范围扫描通常出现在 in、between、>、<、>=、<= 等操作中。使用一个索引来检索给定范围的行。

index：扫描全表索引，这通常比ALL快一些。 

这里表 film 的所有字段都有索引，所以才是 index 类型。

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

possible_keys列

这一列显示查询可能使用哪些索引来查找。 

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

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

key列

这一列显示 mysql 实际采用哪个索引来优化对该表的访问。

如果没有使用索引，则该列是 NULL。

如果想强制 mysql 使用或忽视 possible_keys 列中的索引，在查询中使用 force index、ignore index。

key_len列

这一列显示了mysql在索引里使用的字节数。通过这个值可以算出具体使用了索引中的哪些列。

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

key_len计算规则：

字符串类型
char(n)：n字节长度 
varchar(n)：2字节存储字符串长度，如果是 utf-8，则长度 3n + 2

数值类型
tinyint：1字节 
smallint：2字节 
int：4字节 
bigint：8字节 

时间类型
date：3字节 
timestamp：4字节 
datetime：8字节 

如果字段允许为 NULL，需要1字节记录是否为 NULL。

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref列

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

常量：

字段名：

rows列

这一列是 mysql 估计要读取并检测的行数，注意这个不是结果集里的行数。

filtered列

这一列表示返回结果的行数占需读取行数的百分比，也就是存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例。注意是百分比，不是具体记录数。所以，filtered 列的值越大越好。

Extra列

这一列展示的是额外信息。

1) Using index

使用了覆盖索引，查询的所有列数据均在一棵索引树上，不需要访问实际的行记录。

file_id 在索引里有，所以是 using index。

如果换成 select * ，结果就不一样了。

当然，如果一个表中所有字段都加进了同一个索引中，那么也是 using index。

2) Using where

表示存储引擎返回的记录并不都是符合条件的，需要在server层进行筛选过滤，性能比较低。

3) Using index condition

查询的列不完全被索引覆盖，而且where条件中是一个前导列的范围查找；

如下 sql 中，remark 不在索引中，需要回表：

如果不加 remark：

4) Using temporary

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

1. actor.name 没有索引，此时创建了张临时表来 distinct。

2. film.name 有索引，distinct 时就不需要创建临时表了，会在索引树结构里做去重。

5) Using filesort

用非索引字段排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

1. actor.name 没有索引

2. film.name 有索引

6) Select tables optimized away

使用某些聚合函数（比如 max、min）来访问存在索引的某个字段。mysql 内部会做优化，这种情况效率比较高。

当然，Extra列还有其他情况，就不一一列举了。