开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 4 天，点击查看活动详情

EXPLAIN 命令是查看查询优化器如何决定执行查询的主要方法。比如 delete\update\insert 都能使用 explain 但是我们常用于 select 。

使用方式：在完整的查询 SQL 前面直接加上 EXPLAIN 运行即可。

本次使用测试 Demo 使用 MySQL 8.0.31 InnoDB 。

调用 EXPLAIN

先展示一个最简单的 EXPLAIN 结果：EXPLAIN select 1

输出的这么多内容就是我们所说的执行计划。我们就是基于上面的参数以及参数值对 SQL 做出判断进行优化。

在查询中每个表在输出中只有一行。如果查询是两个表的联接，那么输出中将有两行。别名表单算为一个表。因此如果把一个表与自己联接，输出中也会有两行。表在这里可以是一个子查询，一个 UNION 结果等。

列名	描述
id	在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id
select_type	SELECT 关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	额外信息

id 列

这一列总是包含一个编号，标识 SELECT 所属行。如果在语句当中没有子查询或联合，那么只会有唯一的 SELECT ，于是每一行在这个列都将显示一个 1。

简单的连接查询

子查询

联合查询

• 首先说 UNION ALL ：不需要对最终的结果集去重，它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户。
• UNION ：把多个查询的结果集合并起来并对结果集中的记录去重。MySQL 使用的是内部临时表。id 为 3 的行表明这个临时表是为了合并两个查询的结果集而创建的。

特殊情况

查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。 所以我们想知道查询优化器对某个包含子查询的语句是否进行了重写，直接查看执行计划即可。

select_type 列

这一列显示了对应行是简单还是复杂 SELECT 。如果包含子查询或者UNION 等复杂字部份（除开上面子查询被优化为连接查询的），那么最外层部分的标记为 PRIMARY

simple

查询中不包含 UNION 或者子查询的查询都算作是 SIMPLE 类型，还有连接查询也是。部分子查询可优化为连接查询的也是 simple。

primary

对于包含 UNION、UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type 值就是 PRIMARY。

union

在 union 中的第二个和随后的 select 被标记为 union 。

union result

针对 union 使用的去重临时表会被标记为 union result。

SUBQUERY

包含在 select 列表中的子查询中的 select （不在 from 子句中）标记为 SUBQUERY。

上面这句话是《高性能MySQL》第三版的原话。也可以理解为不被优化器优化为连接的查询的子查询都会被标记为 SUBQUERY。

由于 select_type 为 SUBQUERY 的子查询由于会被物化，所以只需要执行一遍。物化：通过将子查询结果生成临时表加速查询。

DRIVED

表示该子查询是以物化的方式进行的。一般另一行会显示 drivered2，表示该查询是针对将派生表物化之后的表进行查询的。

partitions 列

好像目前看到的都是 null。

使用分区表的时候会展示所有分区的值。

type 列

就是 MySQL 如何查找表中的行。

下面从差到最优分析常用的访问方法：

ALL

通常说的全表扫描。一般来说就是使用的查询限制条件字段没有设置索引。

index

可以使用索引覆盖，但需要扫描全部的索引记录时，就是 index。

下面的 open_flag、level_type 同时在一个组合索引中，但是搜索条件不能直接使用该索引进行访问（不满足最左前缀）只能扫描整个组合索引的记录。

在 Extra 中使用了 using index：表示正在使用覆盖索引。

range

使用索引获取某些范围区间的记录，比如 bewteen-and 、>、<、in 等。不需要遍历全部索引

ref

通过普通的二级索引与常量值进行等值匹配查询时，可能返回多条数据。

eq_ref

在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref。

下面的两个 id 列都是主键。

const

根据主键或唯一二级索引与常数等值匹配时。

system

当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory，那么对该表的访问方法就是 system。

换成 InnoDB 则 type 是 ALL。

possible_keys & key 列

possible_keys：表示执行查询时可能用到的索引。

key：实际用到的索引。

possible_keys 列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

key_len 列

显示了 MySQL 在索引里使用的字节数。他是通过查找表的定义计算出，而不是表中的数据。它由三个部分组成：

• 对于固定长度类型的索引来说，它的实际占用的存储空间的最大长度就是该固定值。比如 int 显示 4、bigint 显示 8 。对于指定字符集的变长类型来说的索引列来说，如某个索引列类型是 VARCHAR(100) ，使用的字符集是 utf8mb4 ，那么该列实际占用的最大存储空间就是 100 x 4 = 400 个字节。
• 如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多 1 个字节。
• 对于变长字段来说，都会有 2 个字节的空间来存储该变长列的实际长度。

而在使用联合的时候，索引用上了几个字段就是几个字段的长度计算：

现在有一个联合索引是：status_type, open_flag, level_type都是 VARCHAR(255) NOT NULL

只用到了两个索引。

优化器用到了整个索引，即扫描整个索引的行。

只用到了最左前缀第一个字段的索引。

ref 列

当使用索引列等值匹配的条件去执行查询时，ref 列展示的就是与索引列作等值匹配的东东是什么。

rows 列

预计需要扫描的行数，或是预计扫描的索引记录行数。

filtered 列

对于单表意义不大，直接看连接查询：

从执行计划可以看出 a 表是驱动表，b 表是被驱动表。其中 6 x 16.67% 。说明还要对被驱动表执行大约 1 次。

Extra 列

记录额外信息的。

using index

将使用覆盖索引，不需要回表操作

Using index condition

查询使用到了索引下推。

using where

• 当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件时。
• 当使用索引访问来执行对某个表的查询，并且该语句的 WHERE 子句中有除了该索引包含的列之外的其他搜索条件时。

Using temporary

表示查询中需要用到内部临时表。

Using filesort

表示查询需要使用文件排序（内存中或磁盘上进行的排序方式）的方式执行

Using join buffer(Block Nested Loop)

连接查询 过程中，当被驱动表不能有效利用索引时，MySQL 一般会为其分配一块名叫 join buffer 的内存块来加快查询速度，也就是基于块的嵌套循环算法。

---