MySql Explain是对SQL进行性能优化不可或缺的工具,通过他我们可以对SQL进行一定的分析和性能优化,降低线上业务因慢查询造成的性能损失。
执行计划依赖于表,列,索引等细节和where中的条件,mysql优化器利用多种技术来有效的执行一条sql中的查询语句,比如在大表中的一个查询可以不通过全表扫描来完成。
一个涉及到多表的join操作可以避免比较每种的组合情况,优化器选择最优的效果查询对一个集合进行操作,通过explain语句可以获取mysql如何执行语句的信息。
explain对select语句操作返回一行输出信息,表示的顺序是mysql处理语句时实际读取表的顺序。
mysql通过嵌套循环方式解决所有join操作。就是说mysql从第一个表中读取一行,在第二个表中找到匹配的一行,之后在找到第三个表,以此类推。 explain输出列包含如下信息:
id
执行编号,标识select所属的行。如果在语句中没子查询或关联查询,只有唯一的select,每行都将显示1。否则,内层的select语句一般会顺序编号,对应于其在原始语句中的位置
select_type
显示本行是简单或复杂select。如果查询有任何复杂的子查询,则最外层标记为PRIMARY(DERIVED、UNION、UNION RESUlT)
table
访问引用哪个表(引用某个查询,如“derived3”)
type
数据访问/读取操作类型(ALL、index、range、ref、eq_ref、const/system、NULL)
possible_keys
揭示哪一些索引可能有利于高效的查找
key
显示mysql决定采用哪个索引来优化查询
key_len
显示mysql在索引里使用的字节数
ref
显示了之前的表在key列记录的索引中查找值所用的列或常量
rows
为了找到所需的行而需要读取的行数,估算值,不精确。通过把所有rows列值相乘,可粗略估算整个查询会检查的行数
Extra
额外信息,如using index、filesort等
id是用来顺序标识整个查询中SELECT语句的,在嵌套查询中id越大语句优先执行。如果这一列用来说明的是其他行的联合结果,该值可能为null。
表示的是查询的类型,类型如下:
对应的行正在访问哪个表,表名或者别名。
type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说,得保证查询至少达到range级别,最好能达到ref。
显示查询使用了哪些索引,表示该索引可以进行高效地查找,但是列出来的索引对于后续优化过程可能是没有用的
key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。使用的索引的长度。在不损失精确性的情况下,长度越短越好 。
ref列显示使用哪个列或常数与key一起从表中选择行。
rows列显示MySQL认为它执行查询时必须检查的行数。注意这是一个预估值。
Extra是EXPLAIN输出中另外一个很重要的列,该列显示MySQL在查询过程中的一些详细信息,MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。
Using filesort
MySQL有两种方式可以生成有序的结果,通过排序操作或者使用索引,当Extra中出现了Using filesort 说明MySQL使用了后者,但注意虽然叫filesort但并不是说明就是用了文件来进行排序,只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快,所以一般这时也要考虑优化查询了。使用文件完成排序操作,这是可能是ordery by,group by语句的结果,这可能是一个CPU密集型的过程,可以通过选择合适的索引来改进性能,用索引来为查询结果排序。
Using temporary
用临时表保存中间结果,常用于GROUP BY 和 ORDER BY操作中,一般看到它说明查询需要优化了,就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。
Not exists
MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行, 就不再搜索了。
Using index
说明查询是覆盖了索引的,不需要读取数据文件,从索引树(索引文件)中即可获得信息。如果同时出现using where,表明索引被用来执行索引键值的查找,没有using where,表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的,但无需再回表查询记录。
Using index condition
这是MySQL 5.6出来的新特性,叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的,但是现在可以了,这样减少了不必要的IO操作,但是只能用在二级索引上。
Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意:Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。
Using join buffer
使用了连接缓存:Block Nested Loop,连接算法是块嵌套循环连接;Batched Key Access,连接算法是批量索引连接
impossible where
where子句的值总是false,不能用来获取任何元组
select tables optimized away
在没有GROUP BY子句的情况下,基于索引优化MIN/MAX操作,或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
distinct
优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作