mysql的执行计划再熟悉不过了,sql优化经常使用,但是里面很多字段都是知道个大概,没有很系统的学习过。今天就抽空整理一下这块的知识点。
如何查看执行计划
在要分析的sql之前加上 explain 关键字。
举个例子
explain
select * from table
字段说明
id
普通的id字段,一般不用
select_type
查询类型
值 | 说明 |
---|---|
SIMPLE | 单表查询,不包含任何子部分 |
PRIMARY | 如果包含复杂的子部分,最外层部分标记为PRIMARY |
DERIVED | 在from后面的子查询,mysql会为子查询创建一个临时表,也叫派生表 |
SUBQUERY | 包含在select列表中的子查询 |
UNION | 在union语句中,第二个开始的表都会被标记为UNION |
UNION RESULT | 用来从UNION的匿名临时表检索结果的select被标记为UNION RESULT |
一般比较常用的是SIMPLE,PRIMARY,DERIVED。记住这三个,其他用的时候查资料就行。
table
当前sql访问的表名
当sql包含子查询时,table字段会标记为<derivedN> 这里的N是子查询对应id
举个例子
select count(1) from (select * from table) t
对应的执行计划应该是
id | select_type | table |
---|---|---|
1 | PRIMARY | <derived2> |
2 | DERIVED | table |
可以在从一列中从上往下观察 关联优化器 为查询选择的关联顺序。
举个例子
explain
select film.file_id
from film
inner join film_actor using(film_id)
inner join actor using(actor_id)
对应的执行计划是
id | select_type | table |
---|---|---|
1 | SIMPLE | actor |
2 | SIMPLE | film_actor |
3 | SIMPLE | film |
mysql的执行计划总是左侧深度树优先,我们可以根据这个原则从执行计划中解读出关联顺序。关联顺序与执行计划的关系如下图所示。 | ||
partitions
查询使用到表分区的分区名
type
访问类型,这个字段很重要。下面从差到优枚举一下。
值 | 说明 |
---|---|
ALL | 全表扫描,mysql需要扫描整张表才能取到想要的数据 |
index | 按索引的顺序读取整个表的数据。 相比ALL,index的优点是避免排序。 出现index通常意味着若是按随机次序访问行,开销会非常大。 如果在EXtra列中看到"Using index",说明Mysql正在使用覆盖索引, 它只扫描索引的数据,不是按索引次序扫描的每一行(不回表)。 |
rang | 范围扫描是一个有限制的索引扫描。 它开始于索引的某一点,返回匹配的行。 rang比index好一些,因为它不需要全索引扫描 |
ref | 索引访问,返回所有匹配某个单个值的行。 它可能返回多行,所以它是查找和扫描的混合体。 出现在使用非唯一索引,或者唯一索引的非唯一前缀时。 |
eq_ref | 索引查找 和ref一样返回所有匹配某个单个值的行。区别是它只可能返回一行。 eq_ref可以在使用主键或者唯一键执行sql的时候看到。 |
const | 当Mysql能将某部分查询语句转化成一个常量时,它就会使用const访问类型。 简单来说就是当查询最多只有一个匹配行的时候,出现在主键和唯一键查询。 const和eq_ref的区别: const一般出现在当前查询最多只有一个匹配行的情况下 eq_ref一般是多表关联,关联字段是唯一性索引,但是当前sql可能返回多行 |
system | system是const类型的特例,当查询的表只有一行的情况下,使用system。 |
NULL | 意味着Mysql能在优化阶段分解查询语句,在执行阶段甚至不用访问表或者索引 例如从索引列里选取最小值可以通过单独查询索引来完成。 |
possible_keys
这一列表示本次查询可以使用哪些索引。这是基于查询访问的列和使用的比较操作符来判断的。
key
这一列显示了Mysql决定采用哪个索引。优化器会选择查询成本最低的索引。
key_len
该列显示了Mysql在索引里使用的字节数。可以使用这个值来计算出具体使用了索引中的哪几个字段。这个字节数对应的是字段类型的字节数,和存储值无关。
注意点:
- 对于字符串类型,需要把字符列的字符集也考虑进去,比如utf8下一个字符最大占用3个字节,所以使用3字节来计算。
- 当索引字段为定长数据类型,比如char,int,datetime,需要有是否为空的标记,这个标记需要占用1个字节,当字段定义为非空的时候,是否为空的标记将不占用字节。
- 对于变长数据类型,比如:varchar,除了是否为空的标记外,还需要有长度信息,需要占用2个字节。
ref
这一列显示了当前的表在key列记录的索引中查询值所用的列或者常量。
举个例子:
explain
select * from
a inner join b
on a.bid = b.id
table | type | key | ref |
---|---|---|---|
a | ALL | ||
b | eq_ref | PRIMARY | a.bid |
这个例子里,b表走了主键索引,而走索引查询所使用的列就是a表的bid字段 |
rows
这是Mysql估算的,为了找到所需的行而要读取的行数,大体就是扫描行数的意思。这只是个估算,不一定准确,可以作为参考。
filtered
这一列显示的是针对扫描行里符合条件的记录数的百分比。rows * filtered/100 可以估算出将要和执行计划里前一个表关联的行数。
这个百分比应该是越大越好,越大代表扫描行数和最终结果集越接近。
extra
对于当前查询的额外信息。常见的见下表。
值 | 说明 |
---|---|
Using index | 表示Mysql将使用覆盖索引,以避免访问表。 不要把覆盖索引和index访问类型搞混了。 覆盖索引指的是,索引中包含了需要返回的所有列(select的字段)。 |
Using where | 表示Mysql将在存储引擎检索行之后再进行过滤。 许多where条件里涉及索引中的列,当它读取索引时,就能被存储引擎校验。 因此不是所有带where子句的查询都会现实“Using where”。 有时“Using where”就是一个暗示:查询可受益于不同的索引(换个索引吧) |
Using index Condition | Index Condition Pushdown(ICP)是Mysql 5.6开始支持的一种优化方式。 使用ICP时,Mysql会在取出索引的同时把where的部分过滤操作放在存储引擎层进行(当存在只通过索引就可以进行where过滤时发生)。 在某些查询下可以大大减少SQL层对存储引擎层的查询。 |
Using temporary | 表示Mysql在对查询结果排序时会使用一个临时表 |
Using filesort | 表示Mysql会对结果使用一个外部索引排序, 而不是按索引次序从表里读取行。 |
Range checked for each record (index map: N) | 表示没有好用的索引,新的索引将在联接的每一行上重新估算。 N是显示在possible_keys列中索引的位图,并且是冗余的。 |
前5种在工作中会比较常见,掌握好可以解决80%的问题。 |
参考资料
《高性能Mysql》
《MySQL技术内幕-InnoDB存储引擎》