MySQL Explain查看执行计划详解
前言
什么是执行计划?简而言之,就是 SQL 在数据库中执行时的表现情况,通常用于 SQL 性能分析、优化和加锁分析等场景,执行过程会在 MySQL 查询过程中由解析器,预处理器和查询优化器共同生成。在MySQL中使用 explain 关键字来查看。
执行计划能告诉我们什么?她可以用来分析SQL语句和表结构的性能瓶颈。通过分析explain的输出结果集,可以了解到诸如关联查询的执行顺序、查询操作的操作类型、哪些索引可以被命中、哪些索引实际被命中、每张表有多少记录参与查询等信息。接下来,我们将介绍在explain结果集中每一列的意义。
EXPLAIN 中的列
mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | servers | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)
expain执行后输出的结果集包含10列,分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows和Extra,下面对这些字段进行解释。
列名 | 说明 |
---|---|
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_type
每个被独立执行的操作的标识,表示各个SQL片段被操作的顺序;在嵌套查询中id越大的语句越先执行,如果相同,那么按照从上到下的顺序执行。该值可能为NULL,如果这一行用来说明的是其他行的联合结果。
-- 查看在研发部并且名字以Jef开头的员工,经典查询
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = '研发部';
select_type表示查询的类型,其枚举值和说明见下表:
类型 | 说明 |
---|---|
simple | 简单SELECT,不包含子查询和union |
primary | 包含union或者子查询,最外层的部分标记为primary |
subquery | 一般子查询中的子查询被标记为subquery,也就是位于select列表中的查询 |
derived | 派生表——该临时表是从子查询派生出来的,位于from中的子查询 |
union | 位于union中第二个或者其以后的子查询被标记为union,第一个就被标记为primary。若union位于from中,则标记为derived。取决于外面的查询 |
union result | 用来从匿名临时表里检索结果的select子句被标记为union result |
dependent union | 顾名思义,首先需要满足UNION的条件,及UNION中第二个以及后面的SELECT语句,同时该语句依赖外部的查询 |
subquery | 子查询中第一个SELECT语句 |
dependent subquery | 子查询中的第一个 select,取决于外侧的查询,一般是 in 中的子查询。 |
该示例中,in子查询的select_type被标记为dependent subquery ,主查询被标记为primary。
此图展示了 union 和 union result 的 SQL 案例。
table
对应行正在访问哪一个表,可以是表名或者别名
- 关联优化器会为查询选择关联顺序,左侧深度优先
- 当from中有子查询的时候,表名是derivedN的形式,N指向子查询,也就是explain结果中的下一列
- 当有union result的时候,表名是union 1,2等的形式,1,2表示参与union的query id
注意:MySQL对待这些表和普通表一样,但是这些“临时表”是没有任何索引的。
type
type显示的是各个表之间的关联类型,表示MySQL决定如何查找表中的记录。是最重要的一个指标,其性能从高到低依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说,得保证查询至少达到range级别,最好能达到ref;否则,就可能会出现性能问题。
类型 | 说明 |
---|---|
All | 最坏的情况,全表扫描 |
index | 和全表扫描一样,只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index,说明正在使用覆盖索引,只扫描索引的数据,它比按索引次序全表扫描的开销要小很多 |
range | 根据索引检查给定范围的行。key 列显示使用了哪个索引,当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 等操作符,用常量比较关键字列时,可以使用 range |
ref | 非唯一性索引扫描,返回匹配某个值的所有行。这个类型跟eq_ref不同的是,它用在关联操作只使用了索引的最左前缀,或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。 |
eq_ref | 主键或者唯一性索引扫描,最多只返回一条符合条件的记录(高效)。它是除了 const 类型外最好的连接类型 |
const | 当通过索引检索最多只会有一行匹配的时候,MySQL优化器会在查询前读取它而且只读取一次,因此非常快。当主键放入where子句时,mysql把这个查询转为一个常量(高效) |
system | 这是const连接类型的一种特例,表中仅有一行数据,相当于系统表。 |
Null | 意味说mysql能在优化阶段分解查询语句,在执行阶段甚至用不到访问表或索引(高效) |
子查询通过id返回一条数据,故其type为const。而子查询结果集只有一条数据(rows=1),故主查询的type为system。
possible_keys
显示查询可以使用哪些索引,基于查询访问的列和使用的比较操作符来判断。表示他们可以进行高效地查找,但是他们对于后续优化过程可能是没有用的
key 和 key_len
key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len列显示MySQL在索引里使用的字节数。如果键是NULL,则长度为NULL。在语义清晰时,长度越短越好 。
ref 和 rows
ref列显示使用哪个列或常数从表中选择行。如下图所示,从 t2 读取数据时,要判断 t2.id = t1.id,所以 ref 就是 mysql.t1.id。
rows列显示MySQL认为它执行查询时必须检查的行数。注意,这是一个预估值。
Extra
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操作,在找到第一匹配的元组后即停止搜索更多的行 |
No tables used | select 语句中使用from dual 或不含任何from子句 |
如此图所示,展示了使用连接缓存和临时表时的Extra信息。
No tables used 示例,执行 explain select now()
也会得到相同的结果。
小结
- EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
- EXPLAIN不考虑各种Cache
- EXPLAIN不能显示MySQL在执行查询时所作的优化工作
- 部分统计信息是估算的,并非精确值
- EXPALIN只能解释SELECT操作,其他操作要重写为SELECT后查看执行计划。
以上就是这篇文章的全部内容了,希望本文对大家的学习或者工作具有一定的参考和学习价值;如果有疑问,大家可以在评论区留言交流,也希望大家多多点赞关注。谢谢大家对楼兰的胡杨的支持!
Reference
![](https://common.cnblogs.com/images/wechat.png)