EXPLAIN 使用分析

EXPLAIN简介

EXPLAIN 模拟优化器执行SQL语句，查看一个SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描。深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

通过EXPLAIN，可以分析出以下结果:

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

用法

explain+SQL语句

EXPLAIN SELECT * FROM sys_log

执行计划包含的信息

信息	描述
id	选择标识符
select_type	表示查询的类型
table	输出结果集的表
partitions	匹配的分区
type	表示表的连接类型
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
ref	列与索引的比较
rows	扫描出的行数(估算的行数)
filtered	按表条件过滤的行百分比
Extra	执行情况的描述和说明

字段含义

id

SELECT识别符，即查询序列号。可以理解为SQL从小到大的执行顺序。

id相同时，执行顺序由上至下；
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

select_type

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

SIMPLE 简单SELECT，不使用UNION或子查询等
PRIMARY 子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY
UNION UNION中的第二个或后面的SELECT语句
DEPENDENT UNION UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT UNION的结果，union语句中第二个select开始后面所有select
SUBQUERY 子查询中的第一个SELECT，结果不依赖于外部查询
DEPENDENT SUBQUERY 子查询中的第一个SELECT，依赖于外部查询
DERIVED 派生表的SELECT, FROM子句的子查询
UNCACHEABLE SUBQUERY 一个子查询的结果不能被缓存，必须重新评估外链接的第一行

table

输出的行所引用的表。有时不是真实的表名字，可能是别名。

type

显示联结类型，显示查询使用了何种类型，按照从最佳到最坏类型排序。

从左到右，性能从差到好：ALL、index、range、 ref、eq_ref、const、system、NULL

ALL：遍历全表以找到匹配的行。
index: 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。
range: 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描。
ref: 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体。
eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描。
const：表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
system: 表中仅有一行，这是const联结类型的一个特例。平时不会出现，这个也可以忽略不计。
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

一般保证查询至少达到range级别，最好能达到ref。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）。

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。

key

显示MySQL实际决定使用的键(索引)。

如果没有选择索引,键是NULL。查询中如果使用覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引和查询的select字段重叠，仅出现在key列表中。

要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。

如果键是NULL，则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra

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

Using filesort 说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”
Using temporary 使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。
Using index 表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。
Using where 表明使用where过滤
using join buffer 使用了连接缓存
impossible where where子句的值总是false，不能用来获取任何元组
select tables optimized away 在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

注：

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

EXPLAIN不考虑各种Cache

EXPLAIN不能显示MySQL在执行查询时所作的优化工作

部分统计信息是估算的，并非精确值

EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划

SQL执行顺序

书写顺序：select->distinct->from->join->on->where->group by->having->order by->limit

执行顺序: from->on->join->where->group by->sum、count、max、avg->having->select->distinct->order by->limit

 FROM
 WHERE
 GROUP BY
 HAVING
 SELECT
   OVER
   DISTINCT
   TOP
 ORDER By

运算符优先级

优先级
1	算术运算符
2	连接符
3	比较符
4	IS [NOT] NULL、LIKE、[NOT] IN
5	[NOT] BETWEEN
6	NOT
7	AND
8	OR

使用括号可以改变优先级顺序

欢迎关注

原文始发于微信公众号（全栈客）：EXPLAIN 使用分析

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/162661.html