SQL优化——使用Explain查看执行计划

夜深,最近在写SQL优化的一些文章,看到私聊中,有很多人在问如何判断是否需要优化或者是如何查看MySQL执行计划,本文简要介绍一下 MySQL 之 EXPLAIN命令。

EXPLAIN命令是查看优化器如何决定执行查询的主要方法。通常,我们查看执行计划获取以下信息:

  • SQL 如何使用索引
  • 联接查询的执行顺序
  • 查询扫描的数据行数
  • 哪些索引可以使用
  • 哪些索引被实际使用

MySQL Explain语法

file

如下

file


执行计划包含的信息

file

ID 说明

表示执行 SELECT 语句的顺序,ID 相同时,执行顺序由上至下。如果是子查询,ID 的序号会递增,ID 越大优先级越高,越优先被执行


SELECT_TYPE说明

  • SIMPLE:简单查询,即不包含子查询或是 UNION 操作的查询。

  • PRIMARY:最外层查询,即查询中包含任何子查询,则最外层的查询则被标记为 PRIMARY

  • SUBQUERY:映射为子查询,即在 SELECTWHERE 列表中包含了子查询 。

  • DEPENDENT SUBQUERY:依赖外部结果的子查询。

  • DERIVED:子查询,即出现在 FROM 子句中的子查询。

  • UNION:联合,若第二个 SELECT 出现在UNION之后,则被标记为UNION。若UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVED

  • UNION RESULT:使用联合的结果,即UNION产生的结果集。


TABLE 说明

指的就是当前执行的表,即输出数据行所在的表的名称。由 ID 为 M, N 查询 union 产生的结果集,或者是由 ID 为 N 的查询产生的结果。


TYPE 说明

  • ALL:全数据表扫描 ,遍历全表以找到匹配的行,效率最差。
  • INDEX:全索引表扫描,INDEX 与 ALL 区别为 index 类型只遍历索引树。
  • RANGE:对索引列进行范围查找,只检索给定范围的行,使用一个索引来选择行,常见于 BETWEEN、>、<这样的查询条件,当 MySQL 使用索引去查找一系列值时,例如 IN 和 OR 时,也会显示 RANGE。
  • INDEX_MERGE:合并索引,使用多个单列索引搜索。
  • REF:使用非唯一索引扫描或者唯一索引的前缀扫描,返回匹配某个单独值的数据行,然而他可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体 。
  • EQ_REF:唯一性索引扫描,对于每个索引键,表中只有一条数据与之匹配。常见于主键 或 唯一索引扫描。
  • CONST:表中有且只有一个匹配的行时使用。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,如对主键或是唯一索引的查询,效率最高的联接方式。
  • SYSTEM:SYSTEM 是 CONST 类型的特例,即当查询的表只有一行的情况下(等于系统表)。
  • NULL: MySQL 在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

性能排序如下

file

一般来说,得保证查询至少达到 RANGE 级别,最好能达到REF。


POSSIBLE_KEYS 说明

可能使用的索引,指出 MySQL 能使用哪些索引来优化查询,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用。


KEY 说明

KEY 列显示 MySQL 实际决定使用的索引,如果没有可用的索引,则显示为 NULL。如查询使用了覆盖索引,则该索引仅出现在 Key 列中。要想强制 MySQL 使用或忽视 POSSIBLE_KEYS 列中的索引,在查询中使用 FORCE INDEXUSE INDEX或者 IGNORE INDEX


KEY_LEN 说明

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,KEY_LEN 显示的值为索引字段的最大可能长度,理论上长度越短越好,但并非实际使用长度,即 KEY_LEN 是根据表定义计算而得,而不是通过表内检索出的。


REF 说明

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


ROWS 说明

表示 MySQL 根据表统计信息及索引选用情况,估算的找到所需的数据所需要读取的行数,ROWS 值的大小是个统计抽样结果,并不完全准确。


Extra 说明

不适合在其他列中显示,但又很重要的额外信息,有以下几种情况:

  • using index:使用了覆盖索引(Covering Index)进行查询,以避免访问表。

  • using where:MySQL 将在存储引擎检索行后再进行过滤。许多 WHERE 条件里涉及索引中的列,当它读取索引时,就能被存储引擎检验,因此不是所有带 WHERE 子句的查询都会显示 "using where"。

  • using temporary:MySQL 需要使用临时表来存储结果集,常见于排序、子查询和分组查询。

  • using filesort:MySQL 中无法利用索引完成的排序操作称为文件排序。MySQ 有两种文件排序方式,都可以在内存或者磁盘上完成, 通常会出现在 ORDER BY 或 GROUP BY 查询中。

  • not exists:使用 Not Exists 来优化查询。

  • select tables optimized away:直接通过索引来获取数据,不用访问表。

  • Using join buffer:表示在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,应该注意,根据查询的具体情况可能需要添加索引来改进性能。


引发索引失效,导致全表扫描的原因有:

  • 索引列进行计算、函数、类型转换等操作。
  • 索引列使用不等于,如 !=<>
  • 索引列使用 IS NULLIS NOT NULL
  • 模糊查询 LIKE 以通配符开头如,%ab
  • 索引列使用使用 OR 来连接条件。
  • 索引列使用INNOT IN
  • 隐式转换,类型错误,如字段NUM类型为 varchar,WHERE 条件用 number,NUM = 1。
  • WHERE 子句和 ORDER BY 使用相同的索引,并且 ORDER BY 的顺序和索引顺序相同,并且 ORDER BY 的字段都是升序或者降序,否则不会使用索引。
  • 复合索引不符合最佳左前缀原则或存在断点。
  • 如果 MYSQL 评估使用索引比全表扫描更慢,则不使用索引。

file

讨论数量: 0

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!