MySQL 执行计划分析

执行计划概述

执行计划（Execution Plan）是 MySQL 查询优化器生成的查询执行方案，它展示了 MySQL 如何执行 SQL 查询，包括如何使用索引、如何连接表、如何排序等。通过分析执行计划，可以识别查询性能瓶颈，进行针对性优化。

版本差异

特性	MySQL 5.6	MySQL 5.7	MySQL 8.0
EXPLAIN 输出格式	基础格式	扩展格式	扩展格式 + JSON 格式
EXPLAIN ANALYZE	不支持	不支持	支持（实际执行统计）
EXPLAIN FORMAT=JSON	支持	支持	支持
OPTIMIZER_TRACE	支持	支持	支持（增强）
物化子查询	不支持	支持	支持
索引跳跃扫描	不支持	不支持	支持
直方图统计	不支持	支持	支持
多值索引	不支持	不支持	支持

如何获取执行计划

使用 EXPLAIN

最常用的获取执行计划的方式是使用 EXPLAIN 关键字：

EXPLAIN SELECT * FROM users WHERE age > 30 ORDER BY created_at;

使用 EXPLAIN ANALYZE

MySQL 8.0 引入了 EXPLAIN ANALYZE，它不仅展示执行计划，还显示实际执行的统计信息：

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30 ORDER BY created_at;

使用 SHOW WARNINGS

结合 EXPLAIN 使用，可以查看优化器对查询的改写：

EXPLAIN SELECT * FROM users WHERE age > 30 ORDER BY created_at;
SHOW WARNINGS;

执行计划字段详解

id

查询中每个 SELECT 语句的标识符，用于标识查询的层次关系：

• 相同 id：表示同一层次的查询，按顺序执行
• 不同 id：表示子查询，id 越大优先级越高
• id 为 NULL：表示结果集的合并操作

select_type

查询类型，常见值包括：

类型	说明
SIMPLE	简单查询，无 UNION 或子查询
PRIMARY	主查询
UNION	UNION 中的第二个或后续查询
DEPENDENT UNION	依赖外部查询的 UNION
UNION RESULT	UNION 的结果集
SUBQUERY	子查询中的第一个 SELECT
DEPENDENT SUBQUERY	依赖外部查询的子查询
DERIVED	派生表（FROM 子句中的子查询）
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	结果无法缓存的子查询

table

查询涉及的表名，可能是：

• 实际表名
• 派生表（如 <derived2>）
• UNION 结果（如 <union1,3>）

partitions

查询涉及的分区，如果表未分区则为 NULL。

type

访问类型，是判断查询效率的重要指标，从优到劣依次为：

类型	说明
system	表只有一行数据（如系统表）
const	通过主键或唯一索引一次查询就能找到数据
eq_ref	连接查询中，被连接表通过主键或唯一索引访问
ref	通过非唯一索引访问表
fulltext	使用全文索引
ref_or_null	类似 ref，但包含 NULL 值查询
index_merge	使用索引合并优化
unique_subquery	子查询中使用唯一索引
index_subquery	子查询中使用非唯一索引
range	使用索引范围扫描（如 BETWEEN、>、< 等）
index	全索引扫描（比 ALL 好，因为索引数据量通常比表小）
ALL	全表扫描（性能最差）

possible_keys

可能使用的索引列表。

key

实际使用的索引，如果为 NULL 表示未使用索引。

key_len

使用索引的长度，用于判断使用了索引的哪些列：

• CHAR(n)：n 字节（utf8 为 3n）
• VARCHAR(n)：n + 2 字节（可变长度标识）
• INT：4 字节
• BIGINT：8 字节
• NULL：额外 1 字节

ref

使用索引列的值或常量，用于匹配索引。

rows

MySQL 估计需要扫描的行数，是优化的重要参考。

filtered

返回结果占扫描行的百分比，值越大越好。

Extra

额外信息，包含重要的优化提示：

信息	说明
Using index	覆盖索引扫描，不需要回表
Using where	使用 WHERE 条件过滤
Using index condition	使用索引条件下推优化
Using temporary	需要创建临时表，性能较差
Using filesort	需要外部排序，性能较差
Using join buffer	使用连接缓冲区
NULL	查询效率较高
Using index for group-by	使用索引进行分组
Using index for order by	使用索引进行排序
Using index for skip scan	使用索引跳跃扫描优化

执行计划分析案例

全表扫描案例

EXPLAIN SELECT * FROM users WHERE name = 'John';

执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	ALL	NULL	NULL	NULL	NULL	1000	10.00	Using where

分析：

• type 为 ALL，表示全表扫描
• possible_keys 为 NULL，说明没有可用索引
• rows 为 1000，需要扫描 1000 行
• Extra 为 Using where，使用 WHERE 条件过滤

优化建议：为 name 列添加索引。

索引扫描案例

-- 添加索引
ALTER TABLE users ADD INDEX idx_name (name);

EXPLAIN SELECT * FROM users WHERE name = 'John';

执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	ref	idx_name	idx_name	152	const	100	100.00	NULL

分析：

• type 为 ref，使用非唯一索引扫描
• key 为 idx_name，实际使用了索引
• rows 为 100，只需要扫描 100 行
• filtered 为 100.00，不需要额外过滤

优化效果：扫描行数从 1000 减少到 100，性能提升显著。

覆盖索引案例

EXPLAIN SELECT id, name FROM users WHERE name = 'John';

执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	ref	idx_name	idx_name	152	const	100	100.00	Using index

分析：

• Extra 为 Using index，表示使用了覆盖索引
• 查询只需要访问索引，不需要回表查询实际数据

优化效果：减少了磁盘 I/O，提升查询速度。

临时表和文件排序案例

EXPLAIN SELECT * FROM users WHERE age > 30 ORDER BY created_at;

执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	ALL	NULL	NULL	NULL	NULL	1000	50.00	Using where; Using temporary; Using filesort

分析：

• Extra 包含 Using temporary 和 Using filesort
• 需要创建临时表，并进行外部排序
• 性能较差

优化建议：为 age 和 created_at 创建联合索引。

ALTER TABLE users ADD INDEX idx_age_created_at (age, created_at);

EXPLAIN SELECT * FROM users WHERE age > 30 ORDER BY created_at;

优化后执行计划：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	range	idx_age_created_at	idx_age_created_at	5	NULL	500	100.00	Using index condition

分析：

• type 为 range，使用索引范围扫描
• key 为 idx_age_created_at，使用联合索引
• Extra 为 Using index condition，使用索引条件下推
• 没有 Using temporary 和 Using filesort

优化效果：消除了临时表和文件排序，性能大幅提升。

执行计划优化原则

优化访问类型

• 尽量避免 ALL（全表扫描）
• 优先考虑 const、eq_ref、ref 等高效访问类型
• 为查询条件添加合适的索引

减少扫描行数

• 优化 rows 列，减少扫描行数
• 使用覆盖索引，避免回表
• 优化 WHERE 条件，减少返回行数

避免临时表和文件排序

• 为排序和分组字段添加索引
• 合理设计联合索引，考虑排序和分组顺序
• 减少 SELECT 语句中的列，只查询需要的列

优化 Extra 信息

• 优先出现 Using index、Using index condition 等优化提示
• 避免 Using temporary、Using filesort 等不良提示

高级执行计划分析

复杂查询执行计划

对于复杂查询（如多表连接、子查询），需要分析：

• 查询的执行顺序（id 字段）
• 表连接的方式（type 字段）
• 索引的使用情况
• 临时表和文件排序的出现位置

使用 EXPLAIN ANALYZE

MySQL 8.0 的 EXPLAIN ANALYZE 提供更详细的执行信息：

EXPLAIN ANALYZE SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.age > 30;

输出示例：

-> Nested loop inner join  (cost=100.00 rows=500) (actual time=0.123..1.234 rows=456 loops=1)
    -> Filter: (u.age > 30)  (cost=50.00 rows=500) (actual time=0.056..0.678 rows=500 loops=1)
        -> Table scan on u  (cost=50.00 rows=1000) (actual time=0.012..0.345 rows=1000 loops=1)
    -> Index lookup on o using idx_user_id (user_id=u.id)  (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=500)

执行计划的局限性

• 执行计划是基于统计信息生成的，可能与实际执行情况有偏差
• 对于动态查询，执行计划可能会变化
• 某些优化可能不会在执行计划中体现

执行计划分析工具

内置工具

• EXPLAIN：基础执行计划分析
• EXPLAIN ANALYZE：详细执行计划和实际统计
• SHOW WARNINGS：查看查询改写
• OPTIMIZER_TRACE：查看优化器决策过程

第三方工具

• MySQL Workbench：提供可视化执行计划分析
• phpMyAdmin：集成执行计划分析功能
• Percona Toolkit：包含 pt-visual-explain 工具，可视化执行计划
• Prometheus + Grafana：监控查询性能，结合执行计划分析

执行计划分析最佳实践

定期分析

• 定期分析核心业务查询的执行计划
• 在系统升级或数据量变化后重新分析
• 对慢查询进行执行计划分析

结合实际执行时间

• 执行计划是预估，需结合实际执行时间
• 使用 EXPLAIN ANALYZE 获取实际执行统计
• 监控查询的实际响应时间

考虑数据分布

• 了解表的数据分布情况
• 考虑索引的选择性
• 避免在低选择性列上创建索引

模拟真实场景

• 在生产环境或类似环境下分析执行计划
• 使用真实数据量进行测试
• 考虑并发查询的影响

总结

执行计划分析是 MySQL 性能优化的重要手段，通过分析执行计划，可以识别查询瓶颈，针对性地进行优化。

建议：

• 掌握执行计划各字段的含义
• 定期分析核心查询的执行计划
• 结合实际执行情况进行优化
• 持续监控和调整

通过本文的介绍，您应该能够掌握 MySQL 执行计划的分析方法，并能够根据执行计划进行查询优化，提升数据库性能。