Mysql: Explain 使用详解

Explain关键字是我们在做sql性能调休的时候用到的，语法很简单就是在Select前面加上Explain。explain的返回结果有这些列：id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。

image.png

Explain 的第一列 Id

Id代笔着sql的执行顺序，Id不同，sql的执行顺序是，先执行Id大的Sql，后执行Id小的sql;
Id相同，Sql的执行顺序是，同上到下依此执行。

EXPLAIN 
SELECT a.cxy_name from tb_cxy01 a where a.cxy_sex > (
    SELECT cxy_sex from tb_cxy01 where tb_cxy01.cxy_name = 'ayueyue'
) a

image.png

这个查询就先执行id=2的子查询,查tb_cxy01表得到结果表a,然后再从表a中执行查询。

Explain 的第二列 Select_type

表示查询中每个子句的类型， 常见的有下面5种类型
1：Simple : 简单的SELECT查询,如下面的Case

  EXPLAIN SELECT * from tb_cxy01

image.png

2：Primary: 子查询的最外层查询
3：SUBQUERY: 子查询，子查询中的第一个SELECT，结果不依赖于外部查询)

  EXPLAIN
  SELECT * from tb_cxy01 a where a.cxy_sex > (
       SELECT tb_cxy01.cxy_sex from tb_cxy01 where tb_cxy01.cxy_sex > 13
   )

image.png

查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY,如：最外层的tb_cxy01别名为a,查询解析时被设置为primary;而内层的查询被设置成subquery,如我们括号里面的select查询。
4：UNION

EXPLAIN
SELECT a.cxy_name,a.cxy_sex from tb_cxy01 a where a.cxy_sex > 90
UNION
SELECT a.cxy_name,a.cxy_sex from tb_cxy01 a where a.cxy_id > 10

image.png

5：DERIVED
我们外层查询的from后面是用的表是来自内层查询返回的临时表时，那这个临时表就被
称为derived表。

EXPLAIN
SELECT * from (
       SELECT tb_cxy01.cxy_sex from tb_cxy01 where tb_cxy01.cxy_name = 'ayueyue'
 ) a

image.png
（注：mysql 5.7以后会自动优化derived查询，并将derived的临时表合并到外层的，Primay表中。所以我们需要在mysql中设optimizer_switch='derived_merge=OFF' Linux下的mysql配置文件：mysqld.conf Windows下的mysql配置文件： my.ini）
然后我们把配置文件中derived优化器打开(optimizer_switch='derived_merge=ON')再次执行我们上面的Sql,发现mysql帮我们把子查询合并到了外层查询，变成了一个简单的Simple查询。
image.png

Explain 的第二列 type

查询时使用的索引类型，既然是索引类型那就必须得创建索引，如果表上没有索引就不存在索引优化这种说法。Type共有7种，性能由好到差分别是System, const, eq_ref,ref, range, index, all。
1：System: 查询的是系统表，并且系统表中只有一条数据的时候。或者derived表只有一条数据的主查询，所以System级别一般是达不到的。
2：Const: 查询匹配的数据有且只有一条，并且查询的where条件只能是primary key或者unique key。所以基本上也是达不到的。如：下面的Sql,通过primay key来查询数据

EXPLAIN
    SELECT tb_cxy01.cxy_sex from tb_cxy01 where tb_cxy01.cxy_id = 13

image.png

然后我们给表tb_cxy01的cxy_name创建一个普通索引，再来查看查询时使用的索引类型

create index iNormal_cxy_name on tb_cxy01(cxy_name)
EXPLAIN
     SELECT tb_cxy01.cxy_sex from tb_cxy01 where tb_cxy01.cxy_name = 'ayueyue'

image.png
这时候我们发现同样的表，同样的数据，使用普通索引来查询时，索引类型就变成了ref级别。
3：Eq_ref，查询匹配的数据有且只有一条，但常见于where条件是primary key获取unique key的时候，对于普通索引当索引键不重复可能会达到。一般ef_ref级别也是比较理想的状态，基本上达不到。
（注： const和Eq_ref都是查询结果有且只有一条数据的时候，但还是有区别： 1：const级别的查询只能使用primary key 和 unique key索引,而Eq_ref级别的查询常见于primary key 和 unique key索引 2：const一般是单表查询，而eq_ref是联表查询）

  EXPLAIN 
  SELECT a.cxy_name FROM tb_cxy01 a where a.cxy_name = 'abc'

image.png

我们发现这时候索引类型并不是eq_ref,而是ref.然后查了一下mysql的官方文档：

image.png

文档中说的是这张表和前面的一张表组合后生成的一条数据。示例中也是2表联查。所以猜想应该是eq_ref适用的场景是2表联查。然后修改Sql如下：

  EXPLAIN 
SELECT a.cxy_name,b.house_master FROM tb_cxy01 a INNER JOIN tb_house01 b ON a.cxy_id = b.house_master

image.png

这时我可以看出先执行了表tb_house,因为我没有对house_master创建索引，所以是全表扫描。对于表tb_cxy01执行的是eq_ref(2表联合查询，另外一张表使用的是primary key或unique key时可以达到eq_ref级别)
4：ref，通过索引查询返回的结果可以是多个，0个或1个。这个索引级别就很好达到。也是我们在做Sql优化时尽量去达到。
给tb_cxy01表的name字段添加普通索引，tb_house表的master字段添加普通索引，然后初始化数据如下：

image.png

编写sql 如下：

EXPLAIN 
SELECT a.cxy_name,b.house_master FROM tb_cxy01 a, tb_house01 b where a.cxy_name = b.house_master

image.png

EXPLAIN 
SELECT a.cxy_name FROM tb_cxy01 a where a.cxy_name = 'lisi'

image.png

（所以，ref可以适用于单表也可以适用于联表查询。即可以适用于唯一索引也可以适用于普通索引。返回的数据可以是0条也可以是多条。）
5：range,检索指定范围的行，where条件一般是between, in , < , >

EXPLAIN 
SELECT a.cxy_name FROM tb_cxy01 a where a.cxy_id BETWEEN 11 and 13

image.png

EXPLAIN 
SELECT a.cxy_name FROM tb_cxy01 a where a.cxy_id > 11

image.png

EXPLAIN 
SELECT a.cxy_name FROM tb_cxy01 a where a.cxy_id in ( 11,12,13,14)

image.png
通过上面的3个Case我们可以看出，在where后面对索引进行between, <, >查询时，mysql得执行索引类型是range,但是当我们用in时type变成了index,有时候还会变成all,
（注：在范围查询的时候，in 关键字经常会使索引降级或失效）
6：index, 对某个索引进行了全部遍历

EXPLAIN 
SELECT a.cxy_name FROM tb_cxy01 a where a.cxy_id > 90

image.png

意思就是把cxy_id这一列的所有数据遍历了一遍。
7：all, 没有使用到索引，把整个表中的每一列的数据都遍历了一遍,如我们表中cxy_name字段没有添加索引，然后我们来执行下面的sql

EXPLAIN 
SELECT a.cxy_name FROM tb_cxy01 a where a.cxy_name > 'asdfasdf'

image.png

Explain 的Possible_keys,Key 字段

Possible_keys: 可能会用到的索引，预测此次查询可能会使用的索引
Key:当前查询实际用到的索引

Explain 的Key_len字段

索引的长度（查询时候使用到的索引的长度），用于判断复合索引是否被完全使用。
1:不为null的char类型字段创建索引
下面来创建一张表tb_key_len,创建字段col2, 定长类型，20个字符，不能为null；然后执行如下sql

create index index_col2 on tb_key_len(col2)
EXPLAIN 
SELECT * from tb_key_len WHERE col2= 'abc'

image.png

从执行的解释来看key_len的长度是60。 因为col2是char(20),在mysql utf8中每个字符占用3个字节，所以key_len = 20 * 3 = 60;
2:可以为null的char类型字段创建索引
创建字段col3, 定长类型，20个字符，可以为null；然后执行如下sql

  create index index_col3 on tb_key_len(col3)
EXPLAIN 
SELECT * from tb_key_len WHERE col3= 'cde'

image.png

这次执行，key_len变成了61，是因为col3是可以允许为null的，所以mysql会额外使用一个字节来标识这个索引的字段是可以为null的，61 = 20 * 3 + 1(可以为null);
3:不为null的varchar类型字段创建索引
创建字段col4, varchar类型，20个字符，不可以为null；然后执行如下sql

create index index_col4 on tb_key_len(col4)
EXPLAIN 
SELECT * from tb_key_len WHERE col4= 'wsx'

image.png

这次解释执行后，key_len变成了62，因为mysql在创建索引时候，如果遇到了变长类型的字段，会再增加2个字节来标识这个索引使用的字段是可变长度的：62 = 20 * 3 + 2(可变长度)
3:可以为null的varchar类型字段创建索引
创建字段col5, varchar类型，20个字符，可以为null；然后执行如下sql

create index index_col5 on tb_key_len(col5)
EXPLAIN 
SELECT * from tb_key_len WHERE col5= 'wsx'

image.png
这次解释执行后，key_len变成了63，因为mysql在创建索引时候，如果遇到了变长类型的字段，会再增加2个字节来标识这个索引使用的字段是可变长度的，如果是可以为null,又会再增加1个字段来标识是可以允许为null 的，所以63 = 20 * 3 + 2(可变长度) + 1(可以为null)
（注：utf-8字符集下mysql中一个char是3个字节; Gbk一个字符集2个字节。 如果字段可以为null,对该字段创建索引后，mysql会使用1个字节来标识 如果字段是varchar类型,对该字段创建索引后，mysql会增加2个字节来标识这个索引。）

到这里后，大家可能会有疑问，刚才测试的都是varchar, char,但是int呐？ 复合索引那？
下面我们增加2个字段col6 int(11) null, col7 int(11) not null,并对col6和col7创建复合索引，执行下面sql:

create index index_col86_col7 on tb_key_len(col6,col7)
EXPLAIN 
 SELECT * from tb_key_len WHERE col6= 1

image.png

我们发现key_len是5 ，因为col6时可以为null的mysql会花一个字节来标识，所以，col6字段的索引应该是4,同时因为我们的Sql中只使用了一个索引，所以是4+1 = 5;
然后我们再执行一个sql来验证：

EXPLAIN 
 SELECT * from tb_key_len WHERE col6= 1 and col7 =0

image.png

这次key_len变成了9，因为我们使用了复合索引，col6,col7,长度就是 4 + 4 +1;所以int类型的字段索引长度固定是4个字节。

Explain 的ref字段

查询时使用的索引参照的是哪个字段，如果没有走索引显示为null, 如果索引条件是常量显示的是const,如果索引使用的是某个字段则显示的是db.tbl.field
1:单表常量查询

EXPLAIN 
SELECT * from tb_cxy01 tc where tc.cxy_name = 'jkl'

image.png

我们在字段cxy_name上创建索引，然后再查询的时候使用常量’jkl’来查询，ref字段显示的是const。
2:不使用索引查询
字段cxy_sex上不创建索引，然后执行如下语句：

EXPLAIN 
SELECT * from tb_cxy01 tc where tc.cxy_sex = 90

image.png

从explain的解析上可以看出来，当查询语句没有使用索引时，执行的是全表扫描，同时ref字段显示的是null
2:二张表联合查询
我们在执行一个2表联合查询的Sql语句,字段cxy_name,house_master都创建了索引。

EXPLAIN 
SELECT * from tb_cxy01 tc INNER JOIN tb_house01 th on tc.cxy_name = th.house_master  
where tc.cxy_sex = 90

image.png

从执行结果来看，mysql先执行了tb_cxy01的查询，因为cxy_sex没有创建索引所以是全表扫描,同样ref的结果就是null.然后又对tb_house执行查询，使用的house_master字段，因为该字段创建了索引，所以key是house_naster索引，同样ref显示就是db.tbl.field。因为house_master的取值是依赖与tb_cxy表的cxy_name, 所以ref显示的是：cdb_cxy.tc.cxy_name

Explain 的Row字段

显示执行计划中查询了多少行

Explain 的Extra字段

执行计划的附加信息，下面就解释一下Using filesort，这个是我们平常写sql时常出现的一个属性，也是sql执行的性能杀手
Using filesort的出现一般是因为出现了2次排序，下面执行2个 Case方便理解
1:where的字段和order by的字段是同一个

EXPLAIN 
SELECT * from tb_cxy01 tc where tc.cxy_sex = 90 ORDER BY tc.cxy_sex DESC

image.png

上面的Sql查询的条件是cxy_sex, 我们排序的字段也是cxy_sex,mysql就可以先做查询然后对查询出来的结果再做排序处理。所以Extra字段显示的是using where
2:where的字段和order by的字段不是同一个

EXPLAIN 
SELECT * from tb_cxy01 tc where tc.cxy_sex = 90 ORDER BY tc.cxy_name DESC

image.png
这次我查询的条件是cxy_sex,但排序的字段变成了cxy_name了，所以Extra变成了filesort,因为我们这次查询的查询条件和排序的字段不一样了，所以mysql在执行完查询后，再执行排序发现这个排序的自己没有做查询，那mysql又会再去查询一遍cxy_name然后再 进行排序。这样的效率当然要慢的多因为多做了一次查询。
(注：在单索引条件下，如果查询的字段和排序的字段不是一个，则会出现using filesort.所以我们在写Sql的时候尽量where什么，order by 什么）
2:复合索引下如何会出现Using filesort

EXPLAIN
SELECT * from tb_new tn where tn.pcode = '' ORDER BY tn.ppwd

image.png

发现当我们where pcode, order by ppwd时，出现了using filesort
然后我们再执行下面这个sql

EXPLAIN
SELECT * from tb_new tn where tn.pcode = '' ORDER BY tn.premark

image.png
我们只是修改了order by的字段，Extra就没有using filesort了。其实上面2个Sql的唯一区别就是order by 的条件不一样，我们创建复合索引时这3个字段的顺序是：（pcode,premark,ppwd）第一个Sql我们的where条件和order by条件所使用的字段中间间隔了一个premark,。而第2个Sql我们的where条件和order by条件所使用的字段是挨着的。
（注：在复合索引下，我们的where 条件和order by条件所使用的字段是不垮列(和创建索引时的顺序是一致的)则不会做2次查找排序，即Extra不会出现using filesort}）