mysql-explain

explain

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈

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

explain的两个变种

1）explain extended

会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。

 explain extended select * from table_name where id = 1;
 show warnings;

2）explain partitions

相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

explain中的列

1.id

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

2.select_type

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

        1）simple:简单查询。查询中不包含子查询和union

        2）primary:复杂查询中最外层的select

        3）subquery:包含在select中的子查询(不在from子句中)

        4）derived:包含在from子句中的子查询

set session optimizer_switch='derived_merge=off'; #关闭mysql5 #关闭mysql5.7新特性对衍生表的合并优化
explain select (select 1 from table_name1 where id = 1) from (select * from table_name2 where id = 1) dd;
set session optimizer_switch='derived_merge=on'; #还原默认配置

        5）union:在union中的第二个和随后的select

explain select 1 union all select 1;

3.table

explain的一行正在访问哪个表

4.partitions

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

5.type

表示关联类型或访问类型

从优到差分别为：system>const>eq_ref>ref>range>index>all

一般来说需要保证查询级别达到range级别

system：通过主键或唯一索引读取该表只有一行时

const：通过主键或唯一索引读取到多行时

eq_ref：通过主键或唯一索引进行关联读取到多行时

ref：使用非主键且非唯一索引进行查询时

range：范围扫描，例如in、>、<=等操作中

index:全索引扫描

all：全表扫描

6.possible_keys

可能使用那些索引来查找

7.key

实际使用了哪个索引

8.key_len

索引里使用的字节数

计算规则：

utf-8字符集编码下字符串：

        char(n):3n字节

        varchar(n):3n+2字节，varchar变长，所以用2个字节存储字符串长度

数值类型：

        tinyint：1字节

        smallint：2字节

        int：4字节

        bigint：8字节

时间类型：

        date：3字节

        timestamp：4字节

        datetime：8字节

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

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

9.ref

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

10.rows

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

11.filtered

该列是一个百分比的值

12.extral

展示额外信息

1）using index：使用覆盖索引

覆盖索引是指select后边的字段都已从这个索引树中取

2）using where：使用where语句来处理结果，并且查询列未被索引覆盖

3）using index condition：查寻的列不完全被索引覆盖，where条件中是一个前导列的范围

4）using temporary：需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的

        一般是创建索引进行优化

5）using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况一般也是要优化的。

        一般是创建索引进行优化

6）select tables optimized away：使用某些聚合函数来访问存在索引的某个字段