一文学会快速定位和解决 MySQL 负载高、执行慢的问题！

 

MySQL的 explain 命令语句提供了如何执行 SQL 语句的信息，解析 SQL 语句的执行计划并展示，explain 支持 select、delete、insert、replace 和 update 等语句，也支持对分区表的解析。

通常 explain 用来获取 select 语句的执行计划，通过 explain 展示的信息我们可以了解到表查询的顺序，表连接的方式等，并根据这些信息判断 select 执行效率，决定是否添加索引或改写 SQL 语句优化表连接方式以提高执行效率。本文参考官方文档：EXPLAIN Output Format 对 explain 输出的内容进行说明，同时也对自己之前使用 explain 不清晰的方面进行总结。

本文使用的 MySQL 版本为官方社区版 5.7.24。

主要用法

 

1. EXPLAIN 和 DESCRIBE(可以简写成 DESC)都可以用来查看语句的执行计划，但通常使用 EXPLAIN 较多；
2. FORMAT 选项可以指定执行计划输出信息为 JSON 格式，而且包含一些更详细的指标说明；
3. EXTENDED 和 PARTITIONS 选项可以输出更详细选项说明，语法上是为了兼容低版本 MySQL，未来会废弃，默认使用 EXPLAIN 命令即可。

测试数据

本文基于 MySQL 官方示例数据库 employee：Example Databases 进行解析说明，使用到的表如下：

输出说明

通过以上示例语句得出 explain 输出有 12 个字段，主要说明如下表：

 

id

id 为 select 标识符，语句在执行计划当中的执行顺序。id 值的出现有如下几种情况：

1. id 值全相同，则按由上到下顺序执行；
2. id 值全不相同，则按 id 值大小，由大到小顺序执行；
3. id 值部分相同，部分不相同，则同组 id 值大的优先执行(组内 id 值相同的顺序执行)。

select_type

select_type 为表查询的类型，根据官方文档总结几种常见类型如下表：

 

1. SIMPLE：最常见的查询类型，通常情况下没有子查询、union 查询就是 SIMPLE 类型。

​​​​​​​

2. PRIMARY 和 SUBQUERY：在含有子查询的语句中会出现。

3. UNION 和 UNION RESULT：在有 union 查询的语句中出现。

4. DEPENDENT UNION 和 DEPENDENT SUBQUERY：当语句中子查询和 union 查询依赖外部查询会出现。

5. DERIVED：当查询涉及生成临时表时出现。l root@localhost:employees> explain select * from (select * from departments limit 5) de;+----+-------------+-------------+------------+-------+---------------+-----------+---------+--------+------+----------+-------------+| id | select_type | table       | partitions | type  | possible_keys | key       | key_len | ref    | rows | filtered | Extra       |+----+-------------+-------------+------------+-------+---------------+-----------+---------+--------+------+----------+-------------+| 1  | PRIMARY     | <derived2>  | <null>     | ALL   | <null>        | <null>    | <null>  | <null> | 5    | 100.0    | <null>      || 2  | DERIVED     | departments | <null>     | index | <null>        | dept_name | 122     | <null> | 9    | 100.0    | Using index |+----+-------------+-------------+------------+-------+---------------+-----------+---------+--------+------+----------+-------------+2 rows in setTime: 0.012s
6. table

指执行计划当中当前是从哪张表获取数据，如果为表指定了别名，则显示别名，如果没有涉及对表的数据读取，则显示 NULL，还有如下几种情形：

• <unionM,N>：数据来自union查询的id为M和N的结果集；
• ：数据来自派生表id为N的结果集；
• ：数据来自子查询id为N的结果集。

7. partitions

指执行计划中当前从分区表哪个表分区获取数据，如果不是分区表，则显示为 NULL。

type

type 应该被认为是解读执行计划当中最重要的部分，根据 type 显示的内容可以判断语句总体的查询效率。主要有以下几种类型：

1. system：表只有一行(系统表)，是 const 的一种特殊情况。

2. const：对于主键或者唯一索引键的等值查询，只返回一行数据。

3. eq_ref：对于前表的每一行数据，都只能匹配当前表唯一一行数据。除了 system 与 const 之外这是最好的一种连接查询类型，主键或者是非空唯一索引的所有部分都可以在连接时被使用，通常使用的是'='操作符，比较值可以是一个常量，也可以是一个在该表之前读取该表的字段表达式。

4. ref：对于前表的每一行数据，都从当前表读取所有匹配索引值的行。与 eq_ref 相比，连接查询字段不是主键或者唯一索引，又或者是复合索引的部分左前缀，如果连接查询匹配的是少量几行数据，ref 是个不同错的选择，通常使用的运算符是'='、'<='或者'>='等。

5. ref_or_null：同ref类型，但是包含了对NULL值的搜索。

6. index_merge：使用了索引合并优化进行查询。如果查询指定条件涉及对多个索引的使用时，会将多个索引合并操作。

7. range：使用索引扫描条件指定范围内的数据。常用的操作符有 '>'、'<'、'is null'、'between'、'in' 和 'like' 等。

8. index：使用索引全扫描。类似于全表扫描，只是扫描对象是索引，出现于以下两种情况：

• 如果索引是覆盖索引，即索引包含查询所需要的所有表数据，就只扫描索引，并且在 Extra 中出现 Using index。通常情况下扫描索引比打描表要更快，因为索引一般比表来的小；
• 全表扫描采用索引的顺序来读取数据，本质上还是全表扫描，并且在 Extra 中不会出现 Using index，避免再进行排序消耗性能，因为索引本身就是排序好的。

9. all：使用全表扫描。

通过以上各种主要类型的分析，可以总结出各个类型性能排序(从左到右性能从高到低)：

•  

system > const > eq_ref > ref > range > index > all

possible_keys

显示了 MySQL 在查找当前表中数据的时候可能使用到的索引，如果该字段值为 NULL，则表明没有相关索引可用。

key

显示了 MySQL 在实际查找数据时决定使用的索引，如果该字段值为 NULL，则表明没有使用索引。

key_len

显示了 MySQL 实际使用索引的键大小，单位字节。可以通过 key_len 的大小判断评估复合索引使用了哪些部分，如果 key 字段值为 NULL，则 key_len 的值也为 NULL。

几种常见字段类型索引长度大小如下，假设字符编码为 UTF8：

• 字段属性是否允许 NULL，如果允许 NULL，则需要额外增加一个字节；
• 字符型：
  • char(n)：3n个字节
  • varchar(n)：3n+2个字节
• 数值型：
  • tinyint：1 个字节
  • int：4 个字节
  • bigint：8 个字节
• 时间型：
  • 1~2位：1 个字节
  • 3~4位：2 个字节
  • 5~6位：3 个字节
  • date：3 个字节
  • datetime：5 个字节+秒精度字节
  • timestamp：4 个字节+秒精度字节
  • 秒精度字节(最大 6 位)：

ref

显示哪些常量或者字段被用于查询索引列键值，以获取表中数据行。

1. 如果是常量等值查询，则显示为 const；
2. 如果是连接查询，则被驱动表的该字段会显示驱动表的所关联字段；
3. 如果条件当中使用函数表达式，或者值导致条件字段发生隐式转换，这里显示为 func。

rows

显示预估需要查询的行数。对 InnoDB 表来说这是个预估值，并非是个准确值。

filtered

显示按表条件过滤的表行的估计百分比。

Extra

显示查询时的额外信息。常见的有如下几种：

1. Using index

仅查询索引树就可以获取到所需要的数据行，而不需要读取表中实际的数据行。通常适用于 select 字段就是查询使用索引的一部分，即使用了覆盖索引。

2. Using index condition

显示采用了Index Condition Pushdown (ICP) 特性通过索引去表中获取数据。关于ICP特性可以参考官方文档：Index Condition Pushdown Optimization。简单说法如下：

• 如果开启 ICP 特性，部分 where 条件部分可以下推到存储引擎通过索引进行过滤，ICP 可以减少存储引擎访问基表的次数；
• 如果没有开启 ICP 特性，则存储引擎根据索引需要直接访问基表获取数据并返回给 server 层进行 where 条件的过滤。

3. Using index for group-by

跟 Using index 访问表的方式类似，显示 MySQL 通过索引就可以完成对 GROUP BY 或 DISTINCT 字段的查询，而无需再访问表中的数据。

4. Using where

显示 MySQL 通过索引条件定位之后还需要返回表中获得所需要的数据。

5. Impossible WHERE

where 子句的条件永远都不可能为真。

​​​​​​​

6. Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)

在表联接过程当中，将先前表的部分数据读取到 join buffer 缓冲区中，然后从缓冲区中读取数据与当前表进行连接。

主要有两种算法：Block Nested Loop和Batched Key Access，关于这两种算法说明可以参考官方文档：Block Nested-Loop and Batched Key Access Joins，也可以参考另一篇博文说明：MySQL 查询优化之 Block Nested-Loop 与 Batched Key Access Joins。

7. Using MRR

读取数据采用多范围读 (Multi-Range Read) 的优化策略。关于MRR特性也可以参考官方文档：Multi-Range Read Optimization

8. Range checked for each record (index map: N)

MySQL 在获取数据时发现在没有索引可用，但当获取部分先前表字段值时发现可以采用当前表某些索引来获取数据。index map展示的是一个掩码值，如 index map:0x19，对应二进制值为 11001，表示当前表索引编号为 1、4 和 5 号索引可能被用来获取数据，索引编号通过 SHOW INDEX 语句获得。

​​​​​​

9. Select tables optimized away

MySQL 优化器能够确定以下两点：

• 最多只有一行记录被返回；
• 为了获取这一行数据，有一定的结果集需要获取。

当语句在优化器阶段过程中可以获取查询结果(如获取行数，只需要读取相应索引数据)，而无需再返回表中查询数据，可能会出现 Select tables optimized away。例如针对 MyISAM 引擎的表，使用 select count(*) 获取表的总行数，而且又没有 where 子句或者条件总是为真，也没有 GROUP BY 子句时，其实就包含了以上的条件且隐式含有 GROUP BY 分组的效果。

10. Using temporary

MySQL 需要创建临时表来存放查询结果集。通常发生在有 GROUP BY 或 ORDER BY 子句的语句当中。

11. Using filesort

1. type：展示了表的查询/连接类型，体现查询效率；
2. key/key_len：实际使用了什么索引，使用了哪些部分索引；
3. Extra：对执行计划步骤额外的说明，采用了哪些查询特性。