Explain详解与索引

简述: explain为mysql提供语句的执行计划信息。可以应用在select、delete、insert、update和place语句上。explain的执行计划，只是作为语句执行过程的一个参考，实际执行的过程不一定和计划完全一致，但是执行计划中透露出的讯息却可以帮助选择更好的索引和写出更优化的查询语句。 

官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

 

Explain的列说明

id列

id列的编号是select的序列号，一般有多少个select查询就有多少个id，且id是按照select出现的顺序增长的，id越大则select执行的优先级就越高。

id列也可以为null，当查询为union时，id为null。在这种情况下，table列会显示为形如<union M,N>，表示它是id为M和N的查询行的联合结果。

 

select_type列

顾名思义，此列表示的是查询的种类。

1. simple：简单的查询，不使用uinon或者子查询。如下示例

示例1-1

2. primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个。如示例1-1中 第一个出现的select，即id=1的
3. derived：派生表SELECT（FROM子句中的子查询）。如示例1-1中 FROM 后面的（select * from ...) emp

4. subquery：除了from子句中包含的子查询外，其他地方出现的子查询都可能是subquery。如示例1-1中 FROM前面出现的(select 1 ....)
5. union：union连接的select查询，除了第一个表外，第二个及以后的表select_type都是union。

6. union result：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null

table列

输出行所引用的表的名称。显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null。

<unionM，N>：该行引用ID值为M和N的行的并集。
<derivedN>：该行引用ID值为N的行的派生表结果。派生表可能来自例如FROM子句中的子查询。
<subqueryN>：该行引用ID值为N的行的实例化子查询的结果。

如示例1-1中id=1的table列为 <derived3>派生表(临时表),代表其使用的是id为3的结果集，即from后的子查询。

 

partitions列

版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

 

type列

描述如何联接表，即如何查找表中的行，以及数据范围等。

依次从好到差：system>const>eq_ref>ref>fulltext>ref_or_null

>index_merge>unique_subquery>index_subquery>range>index>ALL

除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

1.system:表中只有一行数据时，且其是const的一个特例。 EXPLAIN EXTENDED 结合show warnings时可以看出，mysql会将查询某些部分进行优化-将其转换为一个常量查询

EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM employee WHERE id = 1) emp;
SHOW WARNINGS;

2.const:如上面的示例，id=2时，使用唯一索引(unique)或者主键(primary)，返回记录一定是1行记录的等值where条件时，通常type是const，也可称之为唯一索引扫描。

3.eq_ref:当连接使用索引的所有部分并且索引是PRIMARY KEY或UNIQUE NOT NULL索引时，将使用它。

驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为不为空，当唯一索引和主键多列时，只有所有的列都用作比较时才会出现eq_ref。

EXPLAIN SELECT * FROM employee emp 
LEFT JOIN department dept ON emp.departmentId = dept.id;

EXPLAIN SELECT * FROM employee emp ,department dept 
WHERE emp.departmentId = dept.id;

4.ref:如果联接仅使用键的最左前缀，或者如果键不是PRIMARY KEY或UNIQUE索引（换句话说，如果联接无法基于键值选择单个行）则为ref。

KEY `idx_empId_deptId` (`departmentId`,`id`) #组合索引
KEY `departmentId` (`departmentId`) #普通索引

示例1:不是PRIMARY KEY或UNIQUE索引

EXPLAIN SELECT * FROM employee WHERE departmentId = 1;

示例2:索引的最左前缀

EXPLAIN SELECT * FROM department dept 
LEFT JOIN employee emp ON dept.id = emp.departmentId;

5.fulltext：全文索引检索，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。

6.ref_or_null：与ref方法类似，只是增加了null值的比较。

7.range：索引范围扫描，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。

EXPLAIN SELECT * FROM employee WHERE id > 1;

8.index:

• 如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树(不从根节点开始查找，直接对二级索引的叶子结点遍历和扫描)。在这种情况下，“extra”列显示“using index”。仅索引扫描通常比ALL更快，因为索引的大小通常小于表数据。

EXPLAIN SELECT departmentId FROM employee ;#departmentId为普通索引

• 在索引上进行全表扫描，没有Using index的提示

9.ALL:全表扫描

EXPLAIN SELECT * FROM employee

possibile_keys列

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

• 该列显示null时：说明当前查询情况，没有索引可以选择。
• 该列不显示null时：但key 显示可能为null，因为可能表中数据不多，mysql直接走了全表查询。

 

key列

显示实际使用的哪个索引来进行表查询的。

select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

 

key_len列

显示索引使用的字节数，通过这个值来计算使用了索引中的哪些列。

KEY `idx_empId_deptId` (`departmentId`,`id`) #组合索引

EXPLAIN SELECT * FROM employee WHERE departmentId = 1;

departmentId是int 4个字节(这里展示5是因为建表时默认给了null值，占了一个字节)，这里本身就有一个索引，没走组合索引。

 

key_len计算规则如下:

• 字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字 或字母占1个字节，一个汉字占3个字节

• char(n):如果存汉字长度就是 3n 字节
• varchar(n):如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为 varchar是变长字符串
  

• 数值类型

• tinyint:1字节
• smallint:2字节

• int:4字节
• bigint:8字节
  

• 时间类型

• date:3字节
• timestamp:4字节

• datetime:8字节如果字段允许为 NULL，需要1字节记录是否为 NULL

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

 

ref列

显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有:const(常量)，字段名(例:emp.id)

 

rows列

行计划中估算的扫描行数，不是精确值

 

filtered列

explain的extended 扩展能够在原本explain的基础上额外的提供一些查询优化的信息，这些信息可以通过mysql的show warnings命令得到。(5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数)

EXPLAIN [EXTENDED] SELECT select_options 

rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数(前一个表指 explain 中的id值比当前表id值小的 表)

当为100时代表的基本是进行了全表扫描，值越小越好。

Extra列

显示的是额外的信息。

官网有句话，大概翻译为：如果你想要优化你的查询，那就要注意extra辅助信息中的using filesort和using temporary，这两项非常消耗性能，需要注意。

常见信息：

1.using index：explain结果的key里有使用索引，如果此时select的字段都能通过这个索引(索引树里)来查找到，一般值得就是覆盖索引(一般针对的是辅助索引，不再需要通过辅助索引拿到主键)，即查询时不需要回表查询，直接通过索引就可以获取查询的数据。

2.using where:使用了where子句来查询，并且select查询的列没有被索引覆盖到。

3.Using index condition:查询的列不完全被索引覆盖，where条件中是一个前导列的范围。

KEY `idx_workAge_birthday` (`workAge`,`birthday`)
EXPLAIN SELECT * FROM employee WHERE workAge > 1;

4.using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。

5.using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中。数据较小时从内存排序，否则需要在磁盘完成排序。

a.排序的字段未建立索引

EXPLAIN SELECT * FROM employee ORDER BY address;

此处address字段未建立索引，进行全表扫描，保存address和对应的id，再排序address并检索记录。

b.排序的字段建立了索引

KEY `idx_address` (`address`)
EXPLAIN SELECT id,address FROM employee ORDER BY address;
#这里select的字段基本只能包含id和当前索引字段，不然依旧是using filesort

1） 如果select 只查询索引字段，order by 索引字段会用到索引，要不然就是全表排列；

2） 如果有where 条件，比如where xx=1 order by 索引字段 asc . 这样order by 也会用到索引！一般情况order by没有按照索引顺序排序

6.Select tables optimized away:使用某些聚合函数(比如 max、min)来访问存在索引的某个字段。table列为null