前言
通过Explain关键字可以更透彻的来解析一条查询的SQL,以便进行后续的优化,使用方法是在一条select 查询前面加上 Explain,然后执行。
当执行一条查询语句的时候,使用explain会展现出 12 列数据,下面来逐一分析每一列数据包含的含义。
id列
这一列总是会包含一个编号,编号越大表示该行数据越先执行,如果编号一样,则会从上往下依次执行。
下面是id相同的情况,会从上往下依次执行,会优先执行 t_course表,因为t_course表的数量比t_test表要少,mysql会优先执行数据量少的表。
下面是id不相同的情况,会先执行id大的表,也就是会先执行子查询里面的内容。
select_type列
这一列是查询的类型,它分为以下几种情况:
SIMPLE(简单查询)
simple就是简单的一个查询,不会包含任何子查询和UNION。
PRIMARY(主查询)、SUBQUERY(子查询)
如果出现了子查询,则主查询为PRIMARY,子查询为SUBQUERY,但是只限制是select 或者where后面出现的子查询。
DERIVED(派生查询)
DERIVED表示子查询出现在from语句的后面。在mysql8.0很难出现DERIVED,下面是mysql 5.6.47版本测试的结果。
UNION(联合查询)、UNION RESULT(联合结果的查询)
UNION表示的是第二个select查询所在的表。UNION RESULT表示对 union查询产生的这个临时表做查询操作。
DEPENDENT SUBQUERY(依赖性子查询)
只要出现了DEPENDENT 关键字,就表示是依赖性查询,依赖指的是依赖于主查询。该字段出现表示是出现了相关子查询,意思是子查询里面的表要依赖于主查询中的数据。
UNCACHEABLE UNION(未被缓存的查询)
该字段出现表示查询出来的结果不能放到缓存中。出现于子查询里面又有一个union查询,但是union后面的那张表(t_course)和主查询中的表(t_test)没有联系的情况。
DEPENDENT UNION(依赖性联合查询)
表示子查询里面出现了union查询,但是union后面的那张表和主查询有联系。
table列
这一列显示了对应行正在访问哪个表,如果该表起了别名,显示的就是表的别名。
如果出现了<union 数字1,数字2>,则表示查询出的结果是通过union 联合前两张表(第一张,第二张)查出来的。
如果出现了 <derived 数字>,表示是用到了衍生表,数字是该表出现的顺序
type列
这一列和最后一列Extra是查询SQL性能的核心,它显示了该select SQL使用了何种方式进行查询,粗略的来说,一共有8种,性能从好到差依次是: system、const、eq_ref、ref、range、index、all。
在我们日常开发中,前三个基本是达不到的,最好可以达到ref级别。前提是需要有索引。
system
表中只有一条数据,这种情况基本达不到,只是理想的情况。它是const类型的一个特例。
const
从表中通过常量只查询出一条数据,并且这条数据是通过主键索引或者唯一索引来查询出来的。
eq_ref
唯一性索引,对于要查询的字段,只返回匹配唯一的一行数据,有且只能有一个(不能多个,也不能为0),比如姓名这一列不能出现重名的,所以还是经常出现在唯一索引和主键索引上。(该字段在mysql 8.0版本测试很不稳定,没有特别准确的例子,下图是msyql 5.7版本)
ref
非唯一性索引,对于索引的查询,可以返回0个或多个
t_student表中有两个名字为‘zs’的值
range
执行索引查询的范围,使用between、>、<、in。需要注意的是,如果in后面的数据太多,使用in的话索引会失效,然后变成全表扫描,并且使用数字查询的时候一定要确保索引列的类型为int。
index
使用index表示从当前索引树(B+树)来进行查询,比全表扫描要好一些。
All
表示全表扫描,将表中的每一列都查了一遍,没有使用到索引,效率最低。
Possible_keys 列
这一列显示理论上应该可能用到的索引有哪些,这个数量最好和select 后面查询的字段数量是一致的。如果使用了索引,但是没有出现在该列中,则说明该SQL使用的是覆盖索引。如果为null,表示没有用到索引。
key列
该SQL实际用到的索引。
key_len列
表示索引的长度,也可以说是字节数。出现该列的前提是查询的字段一定要设为索引。
下面是一张表的设计:
上图中的name字段的字符集是utf8,长度是16,utf8的字符集表示一个字符占3个字节,varchar等可变类型占2个字节,设置可以为null占1个字节,所以,给name列设好索引之后,应该是 16 *3+2+1=51。
所以在使用索引的前提下,该数值越小越好,数值越小,意味着设计表时,该字段的长度比较短,节省空间。
ref列
表示用到了哪个表的哪个字段。
下图中的第二行表示t_one这张表用到了t_student表中的id字段。
如果该列值为const,表示使用了常量:
rows列
通过索引查到的数据个数,表中可能有很多条,但是这里显示的只是根据索引查询出来的数据个数。
extre列
包含不适合在其他列中显示但十分重要的额外信息。
using filesort
单个索引查询如果出现了using filesort表示需要额外的一次排序,性能不好,举例:
#不会出现 using filesort,不需要重排序,因为已经查出了name,然后根据name排序,和合理。存在先后关系
select name from table1 order by name
#会出现using filesort,需要重排序,因为已经查出了name,然后根据age排序,所以需要查询出age,然后再重新排序。
select name from table1 order by age
复合索引查询也不能跨列,要遵循最左前缀法则。
using temporary
出现了using temporary,表示性能损耗更大,开辟了新的临时表,一般出现在group by语句中。
#不会出现temporary
select max(id) from table1 where name in("zs","ls") group by name
#会出现temporary, 查询出来了name,然后需要给age放在一个新的临时表里面进行分组,所以需要一张新的表
select max(id) from table1 where name in("zs","ls") group by age
using index
出现了 using index 表示使用到了覆盖索引,不用进行回表查询,性能较好。如下id和name都是索引:
需要注意的是,如果出现了 Using index 会对possible keys 列造成影响。当有where条件的时候,possible _keys 会有值,见上图,如果没有where条件,possible _keys不会有值,见下图:
using where
表示需要回表查询;如下图所示,tname是索引,但是tcid并不是索引,所以在查询tcid的时候还是需要进行回表操作。