www.linux123.net

Linux123

当前位置: 首页 > 数据库 > mysql >

MySQL系列-优化之explain执行计划详解

1.id介绍

这个id不是主键的意思,他是用来标识select查询的序列号,包含一组数字,表示查询中执行select子句或者操作表的顺序。
 
会出现以下情况:
 
id相同:按从上到下顺序执行
 
id不同:id值越大,优先级越高,越先被执行
 
id相同不同的同时存在:优先执行id值大的,如果id值相同,则按从上到下的顺序执行
 
id为null表示是用来合并结果集的,在sql使用union关键字合并结果集就会出现他。
 

2.select_type介绍

顾名思义,表示查询的类型
 
mysql之explain
 

3.table介绍
 

对应行正在访问哪一个表,表名或者别名。也有可能是临时表等等,或者是union合并结果集。
 
表名是derivedN的形式,表示使用了id为N的查询产生的衍生表
当有union result的时候,表名是union n1,n2等的形式,n1,n2表示参与union的id
注意:MySQL对待这些表和普通表一样,但是这些“临时表”是没有任何索引的。
 

4.举例说明

 
说明:可以看到,先查询b2表再查询b1表,而且都是简单查询。
 
 
说明:b2表对应的id为2,所以先查询b2表,查询类型是依赖子查询,然后在查询b1表,查询类型是主查询。
 
 
 
 
说明:先查询b2表,在查询b3表,然后查询b1表,最后合并结果集。<union1,2>指的是将id为1和id为2查询的结果合并。
 
 
 
 
说明:先查询b1表得出一个临时表,然后在查询这个临时表,这个derived2表示使用了id为2的查询出来的结果集作为临时表,最后才是b2表。
 

5.type介绍

type意味着类型,这里的type官方全称是“join type”,意思是“连接类型”,这样很容易给人一种错觉觉得必须需要俩个表以上才有连接类型,称之为访问类型更加容易理解。访问类型表示我们是以何种方式去访问我们的数据的,当然很容易想的的是全表扫描了,直接暴力的遍历一张表取寻找需要的数据,效率非常之低下。访问类型的种类有很多,而且各个版本的MySQL可能会不一样,但是常见的就那么几种,按照效率最差到最好依次排列依次是:
 
all < index < range < index_subquery < unique_subquery < index_merge < ref_or_null < ref < eq_ref < const<system
 
all : 这个就是全表扫描了,一般这样的出现这样的SQL而且数据量比较大的话那么就需要进行优化了,要么是这条SQL没有用上索引,要么是没有建立合适的索引。
 
index : 全索引扫描,这个比all效率要好一点,主要有几种情况,一是当前的查询是覆盖索引的,即我们需要的数据在索引中就可以获取(Extra中有Using Index,Extra也是explain的一个字段)(关于覆盖索引:MySQL系列-优化之覆盖索引),或者是使用了索引进行排序,这样就避免数据的重排序(extra中无 Using Index)。如果Extra中Using Index与Using Where同时出现的话,则是利用索引查找键值的意思。
 
range : 这个是index了范围限制,例如 >100、<1000之类的查询条件,这样避免的index的全索引扫描,当然限制的范围越小 效率就越高。
 
index_subquery : 在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与 unique_subquery 类似,但是查询的是非唯一 性索引
 
unique_subquery : 在某些 IN 查询中使用此种类型,而不是常规的 ref
 
index_merge : 说明索引合并优化被使用了
 
ref_or_null : 如同 ref, 但是 MySQL 必须在初次查找的结果 里找出 null 条目,然后进行二次查找。
 
ref : 使用了非唯一性索引进行数据的查找,例如:我们对用户表的用户名这一列建立了非唯一索引,因为用户名可以重复,当我们查找用户的时候select * from user where username=“xxx”的时候就出现了ref,使用非唯一索引查找数据。
 
eq_ref : 这个就很好理解了,使用的唯一性索引进行数据查找,例如主键索引之类的。
 
const : 通常情况下,将一个主键放置到where后面作为条件查询,mysql优化器就能把这次查询优化转化为一个常量,如何转化以及何时转化,这个取决于优化器。这个比eq_ref效率高一点。
 
system : 表只有一行。不过这种情况下就没意义了。
 
NULL : MySQL不用访问表或者索引就直接能到结果。
 

6.举例说明

 
可以看到,有三个字段,其中id是主键索引,n1是非唯一索引,n2是唯一索引。
 
select * from tb1; 简单粗暴的全表扫描
 
 
查询数据n1的时候使用了覆盖索引。
 
 
使用n1列的索引进行排序,所以extra中没有出现using index,因为当前查询不是索引覆盖的。这里我强制使用了idx_n1,MySQL也遵循了我的建议(如果强制使用的索引是用不上的话,MySQL会忽略)。
 
 
很明显,id>3,只需要范围扫描。
 
 
n1是非唯一索引,所以type为ref。
 
 
n2这一列是唯一索引,MySQL直接把他优化到了const级别。
 
接下来的就不列举了,因为在开发中能优化到range就很不错了,而且有些情况因为业务的关系根本就不能优化。
 

7.possible_keys介绍

这个显示可能用到的索引,一个列上可能有多个复合索引,但最后只能选择其中一个使用。当然很可能一个都不会使用,也可能显示没有可能用上的索引,但最后却用上了某个索引,所以这个possible_keys没什么太大意义。
 

8.key

这个就真实的反应了MySQL使用了哪个索引。这个字段很关键,因为MySQL优化器的能力有限,有时候他使用的索引不一定是最优的,所以我们需要知道他到底使用了那个索引,以及强制MySQL使用某个DBA认为最优的索引。当然如果当前的查询是覆盖索引的话,这个索引也会出现在key中。
 

9.key_len介绍

他表示索引中使用的字节数,可通过该key_len计算查询中使用的索引长度,当然在不损失精度的情况下长度越短越好。但是这个key_len只是表示使用索引的长度最大可能是多少,并不是真实的长度。
 

10.举例说明

 
 
对n1和n2字段建立一个复合索引
 
 
可以看到,possible_key是null,实际使用了索引idx_n1n2,索引长度为1536,并且是覆盖索引。
 
那如果我们强制使用索引idx_n1呢?
 
 
可以看到,key_len变短了,同时也从覆盖索引变成了非覆盖索引。
 

11.ref介绍

显示索引的哪一列被使用了,如果可能的话,是一个常数。
 

12.rows介绍

根据表的统计信息及索引使用情况,大致估算出找出所需的记录需要读取的行数。这个rows非常重要,直接反应的SQL找了多少数据,从前面的举例说明中我们也可以看到,在完成目的的情况下当然是越少越好。这个更SQL使用的索引息息相关,可见索引的重要性。
 

13.extra介绍

这个是包含了一些十分重要的额外信息,也是非常重要的一个字段。
 
他可能包含以下一个或多个值:
 
1、using filesort,这个说明mysql无法利用索引进行排序,那他只能用排序算法重新进行排序了,这会额外消耗资源。出现这个的话那么说明这条SQL需要优化了,需要正确使用索引或者建立合适的索引。
 
下面是一个让MySQL使用正确索引的例子,同样的结果,不一样的过程。
 
但是具体那条SQL更快呢,也不一定,根据具体情况而定,比如数据规模之类的。还有就是开发当中一般是不允许用select * 的,要什么字段就拿什么字段,不然会造成性能浪费。
 
 
2、using temporary,建立了临时表来保存中间结果,查询完成之后又要把临时表删除。出现这样的情况说明这条SQL语句请一定要优化,如果这样的SQL一多的话非常影响系统的性能。
 
3、using index,前面也说过,这个表示当前的查询是覆盖索引的,直接从索引中读取数据,而不用访问数据表。如果同时出现using where表明索引被用来执行索引键值的查找,如果没有using where,表面索引被用来读取数据,而不是进行查找。
 
4、using where,使用了where进行条件过滤。
 
5、using join buffer、表示使用了连接缓存。
 
6、impossible where,where语句的值总是false。
 
7、Distinct,一旦MySQL找到了与行相联合匹配的行,就不再搜索了。
 

14.重点

type:访问类型,查看SQL到底是以何种类型访问数据的。
 
key:使用的索引,MySQL用了哪个索引,有时候MySQL用的索引不是最好的,需要force index()。
 
rows:最大扫描的列数。
 
extra:重要的额外信息,特别注意损耗性能的两个情况,using filesort和using temporary。
------分隔线----------------------------