Explain详解与索引最佳实践

Explain可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈。在select之前加上explain关键字，Mysql会在查询上设置
一个标记，执行查询时就不是执行这条SQL而是会返回执行计划的信息。

如果from中包含子查询，仍然会执行子查询，并将结果放入临时表中。在查询中，每个表会输出一行，如果有两个表通过join连接查询，则会输出两行。紧随Explain的语句后，通过show warnings命令可以得到优化后的查询语句，从而看出优化器优化了什么。

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

字段详解

Explain字段

id

属于select的编号，有几个select就会有几个id,与select出现的顺序一致。

id值越大，执行优先级越高；相同则从上往下执行；为null最后执行。

select_type

标识对应行使简单查询还是复杂查询。

1. simple:简单查询，查询中不含有子查询和union
2. primary:复杂查询中最外层select
3. subquery:包含在select中的子查询（不在from子句中）
4. derived:包含在from子句中的子查询。Mysql会讲结果存在临时表中。

table

表示行正在访问的表。

当from子句中有子查询，table展示<derivenN>,表示当前查询依赖id = N的查询

当有union时，table展示<union1,2>,1和2表示参与union的行id

type

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

从优到劣依次为：system > const > eq_ref > ref > range > index > All

• null：能够在优化阶段分解查询语句，在执行阶段不需要再访问表或索引
• system：是const的特例，表里只有一条元组匹配时为system
• const：mysql能对查询的某部分进行优化并转化为一个常量，用于主键索引或唯一索引
  的所有列与常数比较时，所以表最多有一个匹配行
• eq_ref：主键索引或唯一索引的所有部分都被连接使用，最多只会返回一条符合的记录。
• ref：与eq_ref类型，使用的是普通索引或唯一索引部分前缀，索引要和某个值比较，可能会找到多个符合条件的行
• range：通常出现在 IN、between、>、<等操作中。使用一个索引来检索给定范围
• index：扫描全索引就能拿到结果，一般扫描某个二级索引，这种扫描是直接扫叶子节点，速度较慢，称为覆盖索引
• all：全表扫描，扫描聚集索引所有叶子节点，数据大，比index更慢

一般得保证查询达到range,最好能达到ref

partitions

如果查询是基于分区表的话，会显示查询将访问的分区

此字段与在5.7以前还需要再加上 partitions 关键字才会展示

possible_keys

显示本次查询可能会使用哪些索引来完成。

key

显示实际采用的索引

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:1字节

• int:4字节
  • bigint:8字节
• 时间类型
  • data:3字节

• timestamp:4字节

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

ref

显示在key列记录的索引中，表查找所用到的列或常量

rows

显示本行查询估计要读取并检测的行数，不是结果集里的行数

filtered

filtered是一个半分比的值.rows*filtered/100可以估算出将要和explain中前一个表进行连接的行数（id值比当前表id值小的表）

此字段与在5.7以前还需要再加上 extended 关键字才会展示

Extra

展示额外信息，重要值如下：

1. Using index：使用了覆盖索引
2. Using where：使用where语句来处理结果，并且查询的列未被索引覆盖
3. Using index condition：查询的列不完全被覆盖，where条件中是一个前导列的范围
4. Using temporary：mysql建了一张临时表来处理查询
5. Using filesort：使用外部排序而不是索引排序，数据较小时从内存排，否则需要在磁盘完成
6. Select tables optimized way：使用了某些聚合函数（min()、max()等）来访问存在索引的某个字段

索引最佳实践

• 全值匹配
• 最左前缀原则
• 在索引列上做操作（计算、函数、自动或手动类型转换）会导致索引失效
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引，减少select * 的语句
• 使用不等于（!=或<>）、not in、not exists会导致索引失效
• 大于、小于、大于等于、小于等于会根据检索比例、表大小等因素整体评估是否使用索引
• is null 、 is not null 一般情况下也会导致索引失效
• like 以通配符开头（'%xa'）也会导致索引失效