FAQ系列 | EXPLAIN执行计划中要重点关注哪些要素

导读

EXPLAIN的结果中,有哪些关键信息值得注意呢?

MySQL的EXPLAIN当然和ORACLE的没法比,不过我们从它输出的结果中,也可以得到很多有用的信息。

总的来说,我们只需要关注结果中的几列:

列名 备注
type 本次查询表联接类型,从这里可以看到本次查询大概的效率
key 最终选择的索引,如果没有索引的话,本次查询效率通常很差
key_len 本次查询用于结果过滤的索引实际长度,参见另一篇分享(FAQ系列-解读EXPLAIN执行计划中的key_len
rows 预计需要扫描的记录数,预计需要扫描的记录数越小越好
Extra 额外附加信息,主要确认是否出现 Using filesort、Using temporary 这两种情况

首先看下 type 有几种结果,分别表示什么意思:

类型 备注
ALL 执行full table scan,这事最差的一种方式
index 执行full index scan,并且可以通过索引完成结果扫描并且直接从索引中取的想要的结果数据,也就是可以避免回表,比ALL略好,因为索引文件通常比全部数据要来的小
range 利用索引进行范围查询,比index略好
index_subquery 子查询中可以用到索引
unique_subquery 子查询中可以用到唯一索引,效率比 index_subquery 更高些
index_merge 可以利用index merge特性用到多个索引,提高查询效率
ref_or_null 表连接类型是ref,但进行扫描的索引列中可能包含NULL值
fulltext 全文检索
ref 基于索引的等值查询,或者表间等值连接
eq_ref 表连接时基于主键或非NULL的唯一索引完成扫描,比ref略好
const 基于主键或唯一索引唯一值查询,最多返回一条结果,比eq_ref略好
system 查询对象表只有一行数据,这是最好的情况

上面几种情况,从上到下一次是最差到最好

再来看下Extra列中需要注意出现的几种情况:

关键字 备注
Using filesort 将用外部排序而不是按照索引顺序排列结果,数据较少时从内存排序,否则需要在磁盘完成排序,代价非常高,需要添加合适的索引
Using temporary 需要创建一个临时表来存储结果,这通常发生在对没有索引的列进行GROUP BY时,或者ORDER BY里的列不都在索引里,需要添加合适的索引
Using index 表示MySQL使用覆盖索引避免全表扫描,不需要再到表中进行二次查找数据,这是比较好的结果之一。注意不要和type中的index类型混淆
Using where 通常是进行了全表/全索引扫描后再用WHERE子句完成结果过滤,需要添加合适的索引
Impossible WHERE 对Where子句判断的结果总是false而不能选择任何数据,例如where 1=0,无需过多关注
Select tables optimized away 使用某些聚合函数来访问存在索引的某个字段时,优化器会通过索引直接一次定位到所需要的数据行完成整个查询,例如MIN()\MAX(),这种也是比较好的结果之一

再说下,5.6开始支持optimizer trace功能,看样子在执行计划方面是要逐渐和ORACLE看齐 :)

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yuhongli.com 获得专属优惠

 

FAQ系列 | 解读EXPLAIN执行计划中的key_len

导读

EXPLAIN中的key_len一列表示什么意思,该如何解读?

EXPLAIN执行计划中有一列 key_len 用于表示本次查询中,所选择的索引长度有多少字节,通常我们可借此判断联合索引有多少列被选择了。

在这里 key_len 大小的计算规则是:

  • 一般地,key_len 等于索引列类型字节长度,例如int类型为4-bytes,bigint为8-bytes;
  • 如果是字符串类型,还需要同时考虑字符集因素,例如:CHAR(30) UTF8则key_len至少是90-bytes;
  • 若该列类型定义时允许NULL,其key_len还需要再加 1-bytes;
  • 若该列类型为变长类型,例如 VARCHAR(TEXT\BLOB不允许整列创建索引,如果创建部分索引,也被视为动态列类型),其key_len还需要再加 2-bytes;

综上,看下面几个例子:

列类型 key_len 备注
id int key_len = 4+1 = 5 允许NULL,加1-byte
id int not null key_len = 4 不允许NULL
user char(30) utf8 key_len = 30*3+1 允许NULL
user varchar(30) not null utf8 key_len = 30*3+2 动态列类型,加2-bytes
user varchar(30) utf8 key_len = 30*3+2+1 动态列类型,加2-bytes;允许NULL,再加1-byte
detail text(10) utf8 key_len = 30*3+2+1 TEXT列截取部分,被视为动态列类型,加2-bytes;且允许NULL

备注,key_len 只指示了WHERE中用于条件过滤时被选中的索引列,是不包含 ORDER BY/GROUP BY 这部分被选中的索引列。
例如,有个联合索引 idx1(c1, c2, c3),3个列均是INT NOT NULL,那么下面的这个SQL执行计划中,key_len的值是8而不是12:

SELECT…WHERE c1=? AND c2=? ORDER BY c1;

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yuhongli.com 获得专属优惠

[MySQL FAQ]系列 — EXPLAIN结果中哪些信息要引起关注

MySQL FAQ
插图来自网络并作简单加工,如果觉得不当还请及时告知 :)

我们使用EXPLAIN解析SQL执行计划时,如果有下面几种情况,就需要特别关注下了:

首先看下 type 这列的结果,如果有类型是 ALL 时,表示预计会进行全表扫描(full table scan)。通常全表扫描的代价是比较大的,建议创建适当的索引,通过索引检索避免全表扫描。此外,全索引扫描(full index scan)的代价有时候是比全表扫描还要高的,除非是基于InnoDB表的主键索引扫描。

再来看下 Extra 列的结果,如果有出现 Using temporary 或者 Using filesort 则要多加关注:

Using temporary,表示需要创建临时表以满足需求,通常是因为GROUP BY的列没有索引,或者GROUP BY和ORDER BY的列不一样,也需要创建临时表,建议添加适当的索引。

Using filesort,表示无法利用索引完成排序,也有可能是因为多表连接时,排序字段不是驱动表中的字段,因此也没办法利用索引完成排序,建议添加适当的索引。

Using where,通常是因为全表扫描或全索引扫描时(type 列显示为 ALLindex),又加上了WHERE条件,建议添加适当的索引。

暂时想到上面几个,如果有遗漏,以后再补充。

其他状态例如:Using index、Using index condition、Using index for group-by 则都还好,不用紧张。

更多详情请看官方文档:8.8.2 EXPLAIN Output Format