MySQL执行计划详解

一、执行计划

1. 基础介绍

执行计划可谓是每个数据库中十分重要的一环，故名思意即通过执行计划可以了解 SQL 脚本对应的执行信息。

在排查慢 SQL 时更尤为重要，通过执行计划可以查看到语句的执行顺序，是否使用索引等等信息，帮助我们更好的优化语句脚本。

2. 数据测试

在开始之前先准备两张测试表，结构相对简单对应的脚本语句如下，另一测试表 tb_02 表结构与其相同。

CREATE TABLE `tb_01` (
  `Column1` int(11) NOT NULL,
  `Column2` varchar(100) DEFAULT NULL,
  `Column3` varchar(100) DEFAULT NULL,
  `Column4` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`Column1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. 使用方式

执行计划的使用方式十分简单，只需要在对应的语句前添加 explain 关键字执行即可，执行结果将返回语句执行的详细信息。

上图执行结果中字段的描述信息参考下表，后面会详细介绍。

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

二、属性解读

在上面的表格中可以看到执行结果所返回的属性并不少，下面挑选几个比较重要的属性进行介绍。

1. ID

ID 属性返回的值是一个由 1 开始递增的数据，用于标识查询时每张表的查询顺序，当都为 1 时则执行顺序为依次向下。

例如下述示例中执行连表查询，返回结果中两张表对应的 ID 均为 1，则二者的执行顺序从上执行即 t1 先于 t2。

可以看出对于 ID 相同的查询属于归属同一分组，执行优先级自上而下，而当涉及到子查询等操作时，子查询的优先级更高相对的 ID 值也越大。如下图中子查询 tb_02 对应的 ID 为 2，表明其执行优先级高于 tb_01。

2. Type

Type 属性是执行计划中十分重要的一项指标，也是我们判断一条语句是否高效的一大依据。其表示连接类型，常见的类型如下，性能从上至下越来越高，一般好的 SQL 语句至少要达到 range 级别，而 all 级别应当杜绝。

• ALL：全表扫描，应当避免该类型;
• index：索引全局扫描，index 与 ALL 区别为 index 类型只遍历索引树;
• range：检索索引一定范围的行;
• ref：非唯一性索引扫描，返回匹配某个单独值的所有行;
• eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见主键或唯一索引扫描;
• const：表示通过一次索引就找到了结果，常见主键或唯一索引扫描;
• system：system 是 const 类型的特例，当查询的表只有一行的情况下使用 system;
• NULL：MySQL 在优化过程中分解语句，执行时甚至不用访问表或索引，是最高的登记;

以之前的测试表为例，查询主键字段 column1 为 1 的数据，查看返回结果可以看到对应 type 的值为 const，即通过主键索引单次即实现数据查询。

3. Possible_keys

possible_keys 与 key 属性都表示所用到的索引，前者是 MySQL 判断可能用到的索引，而后者是实际查询所用到的索引。

在上一点的查询中，通过主键查询数据时可以看到返回 possible_keys 与 key 均为 PRIMARY，表示预测和实际查询的所使用的索引都是主键索引。

同理，在查询中将条件中的字段替换为普通属性，可以看到结果中的 possible_keys 与 key 都为 null，且结果中的 type 为 ALL，表明查询没有使用任何索引而是执行了全表扫描。

对于这种全表扫描通常则需要进行优化，最简单的方式即给对应的条件字段条件索引，执行下述脚本添加索引后重新执行上述语句。

create index c2_idx
on tb_01 (column2)

从下图中的结果可以看到此时的 type 为 ref 且 possible_keys 与 key 则为 c2_idx，表明此时查询已经通过索引执行。

3. Extra

Extra 表示详细说明，一般该列存在下列值，常见的不太友好的值有：Using filesort，Using temporary。

• Using where: 表示不用读取表中所有信息，仅通过索引就可以获取所需数据，即使用列覆盖索引;
• Using temporary: 表示需要使用临时表来存储结果集，常见于 group by 与 order by;
• Using filesort: 表示无法利用索引完成的排序，此类情景通常需要进行优化;
• Using join buffer: 表示使用了连接缓存，如果出现了这个值，建议根据查询的添加索引;
• Impossible where: 表示 where 语句会一直 false，导致没有符合条件的行;
• Select tables optimized away: 这个值意味着 sql 优化到不能在优化了;
• No tables used: Query 语句中使用 from dual 或不含任何 from 子句;

三、操作句柄

1. 基础介绍

除了执行计划之外，在 MySQL 还提供了操作句柄 Handler 用于描述脚本对数据发生的变更。

通过下述命令即可查看当前会话的脚本执行句柄信息，通常在执行查询之前通过 FLUSH 先执行清空。

-- 清空句柄
FLUSH STATUS;

-- 查询句柄信息
SHOW SESSION STATUS LIKE 'Handler%';

执行查询返回的结果如下：

各项属性描述参考下表，其中若 Handler_read_rnd_next 值很高，可能表示表的全表扫描次数很多；若 Handler_read_key 值很高，说明通过索引查找行的次数很多，索引在查询中被有效利用；若 Handler_update 和 Handler_delete 的值很高，说明有很多行更新和删除操作。

属性	描述
Handler_commit	会话中提交的次数。每执行 COMMIT 语句或自动提交事务时，该计数器会增加。
Handler_delete	会话中删除行的次数。每执行 DELETE 语句删除一行时，该计数器会增加。
Handler_discover	会话中自动发现表的次数。这通常用于 NDB Cluster 和某些存储引擎。
Handler_external_lock	会话中外部锁定操作的次数，主要用于内部目的。
Handler_mrr_init	会话中多范围读取（Multi-Range Read, MRR）初始化的次数。
Handler_prepare	会话中准备事务的次数，主要用于内部目的。
Handler_read_first	会话中读取索引中的第一行的次数。通常在查询需要读取索引中的第一行时增加。
Handler_read_key	会话中通过键读取行的次数。每次通过索引键查找行时该计数器会增加。
Handler_read_last	会话中读取索引中的最后一行的次数。通常在查询需要读取索引中的最后一行时增加。
Handler_read_next	会话中读取索引中的下一行的次数。通常在索引扫描中使用。
Handler_read_prev	会话中读取索引中的前一行的次数。通常在倒序索引扫描中使用。
Handler_read_rnd	会话中通过固定位置读取行的次数。通常在需要直接读取行而不通过索引时使用。
Handler_read_rnd_next	会话中读取下一行的次数。通常在全表扫描时使用。
Handler_rollback	会话中回滚的次数。每次执行 ROLLBACK 语句或事务失败回滚时，该计数器会增加。
Handler_savepoint	会话中保存点操作的次数。
Handler_savepoint_rollback	会话中回滚到保存点的次数。
Handler_update	会话中更新行的次数。每执行 UPDATE 语句更新一行时，该计数器会增加。
Handler_write	会话中插入行的次数。每执行 INSERT 语句插入一行时，该计数器会增加。

2. 示例演示

下面通过具体的示例介绍效果，依次执行下述的三个语句。

-- 清空
FLUSH STATUS;

-- 执行脚本
select 
    *
from
    tb_01
where
    column1 = 1

-- 查询句柄
SHOW SESSION STATUS LIKE 'Handler%';

执行后得到下图结果，由于默认的增删改是自动提交的因此可以看出 Handler_commit 值为 1，同时根据主键字段查询整个查询过程中的涉及的索引数量为 1，因此 Handler_read_key 为 1。

同时，对于 MySQL 而言默认的事务级别为 REPEATABLE_READ，在执行查询时将会添加写锁，每次加锁与释放锁 Handler_external_lock 值都将会增加，因此整个查询动作过程 Handler_external_lock 值为 2。