面试官：MySQL 执行计划都有哪些属性？分别是什么含义？

脚本之家 设为“星标⭐”
第一时间收到文章更新

来源 | 君哥聊技术（ID：gh_1f109b82d301）

使用 MySQL 时，我们常常通过执行计划来判断 SQL 语句的执行效率。那 MySQL 执行计划有哪些属性，分别代表什么含义呢？今天来聊一下这个话题。

通过 EXPLAIN 或 EXPLAIN FORMAT=JSON 查看 MySQL 执行计划时，会看到执行计划里面有 12 个属性，这些属性展示了优化器执行 SQL 的详细信息，对分析和优化 SQL 性能非常有帮助。如下面的思维导图。

为了给属性增加说明，我们建两张表，表结构完全一样：

CREATE TABLE`test1` (
`id`INT(8) NOTNULL AUTO_INCREMENT,
`a`VARCHAR(10) COLLATE utf8_bin DEFAULTNULL,
`b`VARCHAR(10) COLLATE utf8_bin DEFAULTNULL,
`c`VARCHAR(10) COLLATE utf8_bin DEFAULTNULL,
  PRIMARY KEY (`id`),
KEY`idx_a` (`a`)
) ENGINE=INNODB AUTO_INCREMENT=5DEFAULTCHARSET=utf8 COLLATE=utf8_bin

CREATETABLE test2 LIKE test1;

用存储过程插入一些数据：

BEGIN 
 DECLARE i INT; 
SET i=1; 
WHILE(i<=1000)DO
INSERT INTO test1 VALUES(i, i, i, i); 
SET i=i+1; 
END WHILE;

INSERT INTO test2 SELECT * FROM test1;

执行计划

id

id 是 SQL 语句执行的顺序标识。

1. 如果 id 是相同的，表示子查询属于相同层级，这些子查询会依次被执行，比如 JOIN 语句：

EXPLAIN SELECT t1.*,t2.c AS c FROM test1 t1 JOIN test2 t2 ON t1.id = t2.id WHERE t1.id = 1 ;

2. 如果 id 不同，表示这些 SELECT 属于不同层级，id 值越大，执行优先级越高。比如子查询或嵌套子查询 id 值通常大于外层查询的 id。

EXPLAIN SELECT * FROM test1 t1 WHERE EXISTS(SELECT id FROM test2 t2 WHERE t2.id > t1.id);

3. id 也可能为 NULL，表示聚合查询，比如 UNION 语句。

EXPLAIN SELECT * FROM test1 t1 UNION SELECT * FROM test2;

select_type

select_type 表示查询类型，常见的 select_type 如下：

SIMPLE: 简单查询，不包括子查询、聚合查询等。
PRIMARY: 当 SQL 包含子查询或者嵌套查询时，PRIMARY 表示最外层查询。
SUBQUERY: 出现在 SELECT 列表或 WHERE 子句中的非相关子查询（不依赖外层查询结果）。

EXPLAIN SELECT * FROM test1 t1 WHERE t1.id =(SELECT id FROM test2 t2 WHERE t2.a=100)

DEPENDENT SUBQUERY: 出现在 SELECT 列表或 WHERE 子句中的相关子查询（依赖外层查询结果）。

执行流程如下：

a. 外部查询获取一行数据；

b. 将该行数据相关字段值传递给子查询；

c. 执行子查询获取结果；

d. 根据子查询结果决定是否返回当前行。

这类查询语句可能有性能问题，尤其是在数据量大的场景下。

EXPLAIN SELECT * FROM test1 t1 WHERE EXISTS(SELECT id FROM test2 t2 WHERE t2.id > t1.id);

DERIVED: 派生表，指子查询产生的临时表，性能较差。
MATERIALIZED: 优化器选择将子查询结果生成一个临时表（通常用于 IN 子查询优化）。
UNION: UNION 操作中第二个及以后的 SELECT 语句。

EXPLAIN SELECT * FROM test1 t1 WHERE a=1 UNION SELECT * FROM test2 WHERE a=2 UNION SELECT * FROM test2 WHERE a=3;

UNION RESULT: UNION 语句的结果集，id 为 NULL，见上面 SQL 执行计划。
DEPENDENT UNION: UNION 中的第二个及以后 SELECT 是相关子查询（依赖外层查询）。

EXPLAIN SELECT * FROM test1 WHERE id IN (SELECT id FROM test2 WHERE id=3 UNION ALL SELECT id FROM test2 WHERE a = 2)

UNCACHEABLE SUBQUERY: 结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估，出现极少。（可能包含非确定性函数如 RAND(), NOW()）。

EXPLAIN SELECT * FROM test1 t1 WHERE t1.id =(SELECT id FROM test2 t2 WHERE c=RAND())

table

表示当前查询正在访问的是哪个表，可以是正式表，也可以是派生表、物化子查询或者 UNION 结果。

EXPLAIN SELECT * FROM test1 t1 UNION SELECT * FROM test2;

partitions

如果查询涉及分区表，表示查询将要访问的分区表。如果值为 NULL，表示未使用分区表。

type

type 是 MySQL 执行计划中最重要的属性，表示 SQL 执行所需要的访问方式，性能从优到差大致排序如下：

system: 表中只有一行数据，一般是系统表，是 const 的一个特例。
const: 通过主键或唯一索引进行等值查询，最多只返回一行记录。优化器将其视为常量，性能非常好。

EXPLAIN SELECT * FROM test1 WHERE id=1

eq_ref: 在 JOIN 查询中，对于来自前表的每一行组合，从本表中使用主键或唯一非空索引进行等值匹配查找恰好找到一行记录，常见于使用主键或唯一索引进行等值连接，性能非常好。

EXPLAIN SELECT t1.* FROM test1 t1 


    
LEFT JOIN test2 t2 ON t2.id = t1.id

ref: 使用非唯一索引进行等值查询，可能返回多行匹配记录,或者在 JOIN 中使用非唯一索引进行部分前缀匹配。

EXPLAIN SELECT t1.*,t2.c AS c FROM test1 t1 JOIN test2 t2 ON t1.a = t2.a;

fulltext: 使用 FULLTEXT 索引执行查找。通常与 MATCH()、AGAINST() 语句一起使用来进行全文搜索。

ALTER TABLE test1 ADD FULLTEXT (c);
EXPLAIN SELECT * FROM test1 WHERE MATCH(c) AGAINST('keyword');

ref_or_null: 跟 ref 相似，不同的是 SQL 条件中包含 NULL 列的数据。

EXPLAIN SELECT * FROM test1 WHERE a='10' 


    
OR a = NULL;

index_merge: 表示优化器使用了索引合并优化（Index Merge Optimization），即对同一个表使用了多个索引进行扫描，然后将扫描结果合并，如果使用了 index_merge，Extra 属性会出现 Using union/Using sort_union/Using intersect。这个 type 不一定最高效。

EXPLAIN SELECT * FROM test1 WHERE a='900' OR id > 950;

unique_subquery: 在某些 IN 子查询中用来替换 eq_ref，效率更高。

EXPLAIN SELECT * FROM test1 t1 WHERE t1.id IN (SELECT t2.id FROM test2 t2 WHERE t1.a = t2.a) OR t1.a='100';

index_subquery: 类似于 unique_subquery，但适用于使用非唯一索引的 IN 子查询。
range: 使用索引进行范围查询。WHERE 子句中可以包含 BETWEEN, >, =, <=, IN(), LIKE 'prefix%'等范围查询操作符。range 类型比全表扫描效率高，但需注意范围大小。

EXPLAIN SELECT * FROM test1 WHERE a > '1' AND a '20';

index: 全索引扫描（Index Scan）。通常发生在使用覆盖索引或者按照索引顺序进行全表扫描。因为只扫描索引树，不读取文件，性能比全表扫描（ALL）快。

EXPLAIN SELECT a, b FROM test1 WHERE a > '10';

ALL: 全表扫描，性能较差，一种情况是优化器找不到合适的索引，另一种情况是表数据量很小，优化器认为全表扫描更快。

possible_keys

优化器可以考虑使用的索引列表，SQL 语句可能涉及索引都可能被列出。但是优化器最终不一定会选择哪个索引，如果这个属性为 NULL，表示没有可用的索引，需要考虑建索引进行优化。

key

优化器实际决定使用的索引。如果为 NULL，表示优化器决定不使用任何索引，需要全表扫描，一方面可能因为表小，使用全表扫描也很快，另一方面也可能是因为没有合适的索引，优化器认为全部扫描效率更高。

比如 possible_keys 属性中有索引，但是优化器认为使用该索引不如全表扫描效率高。

如果属性值是 PRIMARY 则表示使用了主键索引。

key_len

优化器选择的索引中使用到的字段长度（单位：字节）。

以文章开头的 SQL 为例，给 a、b 字段加了联合索引，下面 SQL 的执行计划看一下：

EXPLAIN SELECT a, b FROM test1;

那这个 66 是怎么来的呢？

varchr(N)变长字段且允许 NULL，索引长度 = N * 字符集长度参数 + 1(字段可以为 NULL) + 2(变长字段) = 10 * 3 + 1 + 2 = 33，a + b 联合索引长度就是 66.

字符集长度参数：utf8mb4 为 4 字节，utf8 = 3,gbk = 2,latin1 = 1

这个属性可以判断联合索引被使用了前几个字段，值越小表示使用的索引部分越少。

ref

显示 key 属性指定的索引中，查找值所用到的列或常量（即与索引进行比较的内容）。

常见值说明：

const：常量值。

EXPLAIN SELECT * 


    
FROM test1 WHERE id = '5';

列名，表示使用到另一个表的列，如 JOIN 语句。

EXPLAIN SELECT t1.* FROM test1 t1 LEFT JOIN test2 t2 ON t2.id = t1.id

func：表示 SQL 中使用了函数结果。
NULL：通常出现在 type 属性是 index（全索引扫描）或 ALL（全表扫描）类型中。

rows

优化器估算的执行该语句需要扫描的行数。只是一个估算值，不是精确值。

rows 基于表统计信息（SHOW TABLE STATUS）和索引统计信息，优化器可能根据这个值选择执行计划。

rows 数值越大，意味着需要处理的 I/O 和 CPU 越多，性能越差，因此 rows 属性值是识别性能瓶颈的重要指标。

filtered

使用 WHERE 子句中的过滤条件后，返回的数据占总数据的百分比，数值范围 0.00 ~ 100。

filtered 值越低，表示 WHERE 条件过滤效果越好。

Extra

SQL 执行的额外信息，提供了优化器的处理细节。常见的值如下：

Using index: 使用覆盖索引，查询的列完全包含在所使用的索引的列中，无需回表访问数据行，性能好。
Using where: 存储引擎返回数据行后，服务器层需要应用额外的 WHERE 条件进行过滤，如果 rows 很大，则服务器过滤会花不少时间。
Using temporary: 需要使用临时表，常见包括 GROUP BY, DISTINCT, UNION, ORDER BY 的 SQL 语句，可以考虑使用索引进行优化，比如让 ORDER BY 和 GROUP BY 字段走上索引。
Using filesort: 需要走非索引的排序，如果内存不够，需要在磁盘进行排序。尝试 ORDER BY 和 GROUP BY 字段走上索引排序。
Using index condition: 索引下推，减少回表次数，可以考虑作为性能优化的手段。
Select tables optimized away: 优化器确定查询可以只从索引中获取结果（例如 MIN(key_column), MAX(key_column), COUNT(*) 通过索引覆盖），甚至不需要访问表或索引数据（直接使用元数据统计），性能非常好。
Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access),Using join buffer (hash join): 表示 JOIN 操作使用了 Join Buffer 。
Impossible WHERE: WHERE 子句的条件始终为 false，比如 WHERE 1=0，不会返回数据。
Start temporary, End temporary: 用于半连接（semijoin）物化去重策略，可以优化 IN/EXISTS。
Distinct: 用于 DISTINCT 或 UNIQUE 子查询，找到第一个后停止继续查找。
Range checked for each record: 没有好的索引可以使用，但是发现在知道前表字段值的情况下后表可能用上某些索引。对于来自前表的每一行，MySQL 都会在后表中通过范围查询（range）或索引合并（index_merge）进行数据查询。性能不太好，但比完全不走索引的 join 语句好一些。

EXPLAIN SELECT * FROM test1 t1 WHERE EXISTS(SELECT id FROM test2 t2 WHERE t2.id > t1.id);

总结

本文对 MySQL 执行计划中的属性进行了详细介绍，使用执行计划优化 SQL 时，关注下面几点：

查看 type 避免全表扫描 ALL 和全索引扫描 index，尽量能使用 const, eq_ref, ref, range。
查看 key 是否使用了合适的索引。
查看 rows 是否过大，结合 filtered 估算最终结果集大小。
查看 Extra，避免使用 Using temporary 和 Using filesort，尽量使用 Using index（覆盖索引）。通过 Using where 过滤结果集，通过 Using index condition 的减少回表次数。
通过 key_len 查看联合索引使用情况。
查看 select_type，如果有 DEPENDENT SUBQUERY 或 DERIVED，要考虑进行优化。

END




    
  推荐阅读：