MySQL 的 Explain 工具使用

摘要:mysql 的 explain 工具用来获取关于查询计划的信息,以及如何解释和输出,explain 命令是查询查询优化器如何决定执行查询的主要方法。

explain 工具使用方法

直接在 select 语句前加上 explain 或者 desc 都可以,然后就可以查看分析结果。mysql 会在查询上设置一个标记,在执行查询时,这个标记会使其返回关于在执行计划中的每一步的信息,而不是执行它。它会返回一行或多行信息,显示出执行计划中的每一部分和执行的次序。

认为增加 explain 时,mysql 不会执行他,这种想法是错误的,事实上,如果查询 from 字句中的包括子查询,那么 mysql 实际上会执行子查询,将结果放到一个临时表中,然后处理完外层查询优化。这意味着如果语句中包含着开销比较大的子查询时或使用临时表算法的视图,实际上也会给服务器带来很大的开销。


explain 分析结果返回的列分析

比较重要的字段就是 id、select_type、key、rows。一般用 explain 工具分析的就是当前 sql 是否使用索引、有哪些可以使用的索引、最终选择了什么索引、是否使用的我们期望的索引、如果是联合查询或者子查询,查询顺序是什么、是否符合我们的预期、查询扫描的行数、是否使用临时表等等。

+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | ask_title | index | NULL          | PRIMARY | 4       | NULL |    1 | NULL  |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------+


id

查询中执行 select 子句或操作表的顺序。

id 相同:执行顺序由上至下;

id 不同:如果是子查询,id 序号会递增,id 的值越大优先级越高,越先被执行;

id 相同又不同:id 相同的可以认为是一组,顺序由上向下执行。在所有组中,id 值越大,优先级越高,越先执行。


select_type

区分普通查询、联合查询、子查询等的复杂查询。

simple:简单的 select 查询,查询中不包含子查询或者 union;

primary:查询中若包含任何复杂的子查询部分,最外层查询则被标记为 primary;

derived:在 from 列表中包含的子查询被标记为 derived (衍生) mysql 会递归执行这些子查询,把结果放在临时表里;

subquery:在 select 或 where 列表中包含了子查询。dependent subquery:在 select 或 where 列表中包含了子查询,子查询基于外层;

uncacheable subqurey:无法被缓存的子查询;

union:若第二个 select 出现在 union 之后,则被标记为 union。若 union 包含在 from 子句的子查询中,外层 select 将被标记为:derived;

union result:从 union 表获取结果的 select。


table

显示这一行数据是关于那张表的。


type

查询类型,最好到最差依次是 system>const>eq_ref>ref>range>index>all。

system:表只有一行记录(等于系统表),这是 const 类型的特列,平时一般不会出现。

const:表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据,所以很快如将主键置于where 列表中,mysql 就能将该查询转换为一个常量。

eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。

range:只检索给定范围的行,使用一个索引来选择行,比如 where 条件中的 between、<、>、in 等的查询。

index:和全表扫描一样,只不过全表扫描时,是按照索引次序进行而不是行。它的主要优点是避免了排序,最大缺点是要承担按照索引次序读取整个表的开销。

all:全表扫描。


possible_keys

显示这张表中可能应用的索引。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。


key

多索引竞争,实际用到的索引。null 代表没有使用索引。如果该索引没有出现在 possible_keys 列中,那么 mysql 选用它是出于另外的原因。比如,查询中若使用了覆盖索引,则该索引和查询的 select 字段重叠,哪怕没有 where 子句。


key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精度的前提下,长度越短越好。key_len 字段能够帮你检查是否充分的利用上了索引。


ref

显示索引的哪一列被使用了。如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。


rows

rows 列显示 mysql 认为它执行查询时必须检查的行数,越少越好。


extra

包含不适合在其他列中显示但十分重要的额外信息。

using filesort:说明 mysql 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。mysql 中无法利用索引完成的排序操作称为“文件排序”。

using temporary:使了用临时表保存中间结果,mysql 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

using index:表示相应的 select 操作中使用了覆盖索引 (covering index),避免访问了表的数据行,效率不错!如果同时出现 using where,表明索引被用来执行索引键值的查找。如果没有同时出现 using where,表明索引只是用来读取数据而非利用索引执行查找。

using index condition:使用了索引下推。

using where:表明使用了 where 过滤。using join buffer:使用了连接缓存。

impossible where:where 子句的值总是 false,不能用来获取任何元组。

select tables optimized away:在没有 group by 子句的情况下,基于索引优化 min/max 操作或者对于 myisam 存储引擎优化count(*) 操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。


参考资料:

《高性能MySQL · 第三版》

MySQL 性能优化神器 Explain 使用分析

结束语:感谢您对本网站文章的浏览,欢迎您的分享和转载,但转载请说明文章出处。
Top