MySQL explain参数详解

ninetySeven
2,105 阅读6分钟

前言

在使用MySQL过程中，校验查询语句以及优化查询是程序员的必修课，因此explain工具是我们必须要掌握和了解的一环。

Explain

什么是explain？

EXPLAIN

查看查询优化器如何决定执行查询的主要方法,MYSQL会在查询上设置一个标记,当执行查询时,这个标记会使其返回关于在执行计划中每一步的信息,而不是执行它,它会返回一行或多行信息,显示出执行计划中的每一部分和执行的次序,从而可以从分析结果中找到查询语句或是表结构的性能瓶颈。

explain能做些什么？

  • 分析出表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

优化中Explain的重要参数

1、Type

system最好，到ref，一般就认为达标了。

ALL和index最差，必须要进行优化。

2、possible_keys, key

possible_keys: 表示可能用到的索引

key: 表示实际用到的索引

3、Extra

一般而言，出现Using filesort和Using temporary就需要进行优化了。

4、rows

显示此查询一共扫描了多少行. 这个是一个估计值.

怎么使用explain

Explain + SQL语句即可,如下:

explain select id from users;

执行结果如下：

image.png

Explain 参数含义

如上图，explain执行sql语句后，得到参数信息，其中参数含如下:

id

id代表执行select 子句或操作表达数顺序。会有三种不同的执行结果，分别是

  • id相同，执行顺序由上至下
EXPLAIN SELECT
	om.order_no 
FROM
	order_master_0 om
	LEFT JOIN order_extension_0 oe ON om.order_no = oe.target_no 
WHERE
	om.id > 60000;

image.png

  • id不同，如果是子查询，id的序号会递增，id越大优先级越高，越先被执行
EXPLAIN SELECT
	* 
FROM
	order_master_0 
WHERE
	order_no = ( SELECT order_no FROM order_master_0 WHERE id = 60877 );

image.png

  • id相同和不同，同时存在，遵从优先级高的优先执行，优先级相同的从上往下顺序执行
EXPLAIN SELECT
	o.order_no,
	o.order_time 
FROM
( SELECT order_no, order_time FROM order_master_0 WHERE id > 60000 ) o
LEFT JOIN order_extension_0 oe ON o.order_no = oe.target_no;

image.png

select_type

查询的类型，主要用于区分普通查询、联合查询、子查询等复杂查询

  • SIMPLE：简单的select查询，查询中不包含子查询或者union查询
  • PRIMARY：查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY
  • SUBQUERY：select或者where列表中包含子查询
  • DERIVED：from列表中包含的子查询被标记为DERIVED，mysql会递归这些子查询，将结果放在临时表中
  • UNION：做第二个select出现在union之后,则被标记为union,若union包含在from子句的子查询中,外层select将被标记为derived
  • UNION RESULT：从union表获取结果的select
table

　　显示查询的数据是关于哪张表的

type

查询的类型，从最好的到最差的依次为：system > const > eq_ref > ref > range > index > ALL，一般情况下，至少要保证达到 range 级别，最好可以达到 ref 级别。

  • system：表只有一行记录，这是const类型的特例，平时不会出现
  • const：通过索引一次就查找到了，const即常数，用于比较primary和unique索引，因为只匹配一行数据，所以效率很快
explain select * from order_master_0 where id = 60876;

image.png

  • eq_ref：唯一性索引扫描，对于索引字段的值，表中只有一条记录与之匹配，常见于主键和唯一性索引扫描
  • ref：非唯一性索引扫描，可能返回多条数据
  • range：只检索给定范围的行，使用一个索引来选择行，比如：where语句中的between、<>、in等查询，这种范围性查询比全表扫描要好，因为只需要匹配索引的开始点和结束点就行（B+树的叶子节点是有序的），不用扫描全部索引。
  • index：只遍历索引树，这通常要比All快，因为索引文件通常比数据文件小。index是从索引中查询，ALL是从磁盘。
  • ALL：全表扫描，最差的一种查询类型。
possible_keys

显示查询可能使用到的表中的索引，最终并不一定使用

image.png

key

查询时实际使用的索引，如果是NULL，则没有使用索引，因此可能出现，possible_keys列有可能被使用的索引，但是key为null的情况。

key_len

表示索引中使用的字节数，而通过该列计算查询中使用的 索引长度，在不损失精确性的情况下，长度越短越好，key_len显示的值为索引字段的最大可能长度，并非实际使用长度,即，key_len是根据表定义计算而得么不是通过表内检索出的

ref

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

Extra
  • Using filesort：mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取
EXPLAIN SELECT
	* 
FROM
	order_2b_0 
WHERE
	user_id = 'd1db4cb3028e93d0d47c2b5997550907' 
ORDER BY
	order_time DESC;

image.png

  •  Using temporary：使用了临时表保存中间结果，mysql对查询结果排序时使用临时表，常见于order by和group by
EXPLAIN SELECT
	* 
FROM
	( SELECT * FROM order_2b_0 WHERE user_id = 'd1db4cb3028e93d0d47c2b5997550907' ORDER BY id DESC ) o 
GROUP BY
	o.id;

image.png

  •  Using index：表示相应的select 操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现 Using where，表示索引被用来执行索引键值的查询；如果没有同时出现 Using where，表示索引用来读取数据而非执行查找。
EXPLAIN SELECT
	user_id,
	order_time 
FROM
	order_2b_0 
WHERE
	user_id = 'd1db4cb3028e93d0d47c2b5997550907' 
ORDER BY
	order_time DESC;

image.png

  • Using where：表示使用了 where 过滤
  • Using join buffer：表示使用了连接缓存，如在查询的时候有多次 join，则可能会产生临时表
  • impossible where：表示where 子句的值总是false，不能用来获取任何数据
  • select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
  • distinct：优化distinct 操作，在查找第一匹配的数据后停止找同样值的动作。
avatar
文章
阅读
粉丝