第 08 章：执行计划详解

zjc 于 2026-01-08 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。执行计划是 MySQL 告诉你“这条 SQL 打算怎么执行”的窗口。看懂执行计划，才能判断慢查询是缺少索引、扫描量过大、临时表排序，还是 JOIN 顺序不合适。

8.1 准备测试数据

为了让扫描路径更明显，先构造一批数据：

CREATE TABLE query_orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    merchant_id BIGINT UNSIGNED NOT NULL,
    order_status VARCHAR(16) NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    created_at DATETIME(3) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_user_created (user_id, created_at),
    KEY idx_status_created (order_status, created_at),
    KEY idx_merchant_status (merchant_id, order_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DELIMITER $$
CREATE PROCEDURE insert_query_orders(IN p_rows INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= p_rows DO
    INSERT INTO query_orders
      (order_no, user_id, merchant_id, order_status, amount, created_at)
    VALUES (
      CONCAT('NO', LPAD(i, 12, '0')),
      FLOOR(1 + RAND() * 10000),
      FLOOR(1 + RAND() * 100),
      ELT(FLOOR(1 + RAND() * 4), 'CREATED', 'PAID', 'SHIPPED', 'CANCELED'),
      ROUND(RAND() * 1000, 2),
      DATE_ADD('2026-01-01', INTERVAL FLOOR(RAND() * 240) HOUR_MICROSECOND)
    );
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

CALL insert_query_orders(100000);
ANALYZE TABLE query_orders;

测试后可删除过程：

DROP PROCEDURE insert_query_orders;

8.2 EXPLAIN 输出总览

EXPLAIN
SELECT id, order_no, amount
FROM query_orders
WHERE user_id = 1001
  AND created_at >= '2026-02-01';

输出主要列：

列	含义
`id`	查询块编号
`select_type`	查询块类型
`table`	当前行访问的表或派生表
`partitions`	命中的分区
`type`	访问类型
`possible_keys`	优化器认为可用的索引
`key`	实际选择的索引
`key_len`	使用到的索引键长度
`ref`	与索引比较的常量或列
`rows`	预估扫描行数
`filtered`	条件过滤后的估算比例
`Extra`	附加执行信息

rows 是估算值，不是精确结果行数。统计信息过期、数据分布倾斜、复杂表达式都会导致偏差。

8.3 id 与 select_type

多表查询：

EXPLAIN
SELECT o.order_no, u.user_id
FROM query_orders AS o
JOIN users AS u ON u.id = o.user_id
WHERE o.order_status = 'PAID';

相同 id 表示同一查询块中的 JOIN，执行顺序通常从上往下。不同 id 表示子查询、派生表或 UNION 分支。

常见 select_type：

值	含义
`SIMPLE`	不包含子查询或 UNION 的简单查询
`PRIMARY`	最外层查询
`SUBQUERY`	首次执行的子查询
`DEPENDENT SUBQUERY`	依赖外层行的相关子查询
`DERIVED`	派生表
`MATERIALIZED`	物化子查询
`UNION` / `UNION RESULT`	UNION 分支和合并结果

重点警惕 DEPENDENT SUBQUERY。如果外层行数很大，子查询可能被重复执行。

8.4 type 访问类型

常见类型从好到差：

system > const > eq_ref > ref > range > index > ALL

类型	场景	评价
`system`	系统表且只有一行	极少见
`const`	主键或唯一键等值查询	最好
`eq_ref`	JOIN 时按主键或唯一键匹配，最多一行	非常好
`ref`	普通二级索引等值匹配	常见且良好
`range`	索引范围扫描	需关注范围大小
`index`	扫描整个二级索引	好于全表但通常不理想
`ALL`	全表扫描	小表可接受，大表需优化

const

EXPLAIN
SELECT *
FROM query_orders
WHERE order_no = 'NO000000000001';

eq_ref

EXPLAIN
SELECT o.order_no, u.username
FROM query_orders AS o
JOIN users AS u ON u.id = o.user_id
WHERE o.id = 1;

ref

EXPLAIN
SELECT *
FROM query_orders
WHERE user_id = 1001;

range

EXPLAIN
SELECT *
FROM query_orders
WHERE created_at >= '2026-02-01';

当前索引 idx_user_created 的前导列是 user_id，只有 created_at 条件无法使用它，因此该语句大概率全表扫描。

8.5 possible_keys、key、key_len

查看索引选择：

SHOW INDEX FROM query_orders;

possible_keys 只是候选，不代表使用。key 为 NULL 表示没有使用二级索引。key_len 说明实际使用了联合索引中的多少字节。

示例：

EXPLAIN
SELECT *
FROM query_orders
WHERE user_id = 1001
  AND created_at >= '2026-02-01';

如果使用 idx_user_created(user_id, created_at)：

key 显示 idx_user_created；
key_len 包含 user_id 和 created_at 两列；
访问类型通常是 range。

key_len 计算与类型、是否可为 NULL、字符集有关。实际排障时不需要死记公式，重点是比较同一个联合索引在不同条件下使用了多少列。

8.6 rows 与 filtered

rows 是预估访问行数，filtered 是经过其他条件后预计保留的百分比。

EXPLAIN
SELECT *
FROM query_orders
WHERE merchant_id = 10
  AND order_status = 'PAID';

如果 rows=10000、filtered=25%，优化器估算最终约 2500 行。

常见问题：

现象	可能原因
rows 很大但结果很小	条件选择性差或索引不匹配
rows 很小但实际很慢	回表、锁等待、网络、临时表
filtered 很低	额外条件未索引化
两个索引都可用但只选一个	优化器基于代价选择
优化器选错	统计信息不准、数据倾斜、成本模型失真

更新统计信息：

ANALYZE TABLE query_orders;

8.7 Extra 常见值

Extra	含义	处理
`Using index`	覆盖索引，不回表	通常好
`Using where`	Server 层过滤	结合扫描量判断
`Using index condition`	ICP 下推过滤	通常好
`Using temporary`	使用临时表	重点排查 GROUP BY / DISTINCT / UNION
`Using filesort`	需要额外排序	检查 ORDER BY 索引
`Using join buffer`	JOIN 无可用索引	补 JOIN 键索引或考虑 Hash Join
`No tables`	没有访问表	正常
`Impossible WHERE`	条件恒假	检查逻辑

覆盖索引示例：

EXPLAIN
SELECT id, user_id, created_at
FROM query_orders
WHERE user_id = 1001;

查询列包含主键 id、user_id、created_at，都在二级索引中，因此可能显示 Using index。

8.8 EXPLAIN ANALYZE

MySQL 8.0.18 引入 EXPLAIN ANALYZE，会真实执行语句并输出实际时间、行数和循环次数：

EXPLAIN ANALYZE
SELECT id, order_no, amount
FROM query_orders
WHERE user_id = 1001
  AND created_at >= '2026-02-01';

示例输出结构：

-> Filter: ...
    -> Index range scan on query_orders using idx_user_created
       (actual time=... rows=... loops=1)

注意：

DML 语句不能直接 EXPLAIN ANALYZE；
慢 SELECT 会真实执行，生产需控制超时；
建议在只读实例或低峰执行；
可配合 LIMIT 缩小验证范围，但 LIMIT 会改变执行行为；
对比估算 rows 和 actual rows，判断统计信息偏差。

8.9 JSON 与 Tree 格式

JSON 格式包含更多代价信息：

EXPLAIN FORMAT=JSON
SELECT *
FROM query_orders
WHERE user_id = 1001;

MySQL 8.0.16+ 支持树形格式：

EXPLAIN FORMAT=TREE
SELECT *
FROM query_orders
WHERE user_id = 1001;

JSON 中重点看：

字段	含义
`cost_info`	估算成本
`used_columns`	使用列
`attached_condition`	附加过滤条件
`materialized_from_subquery`	子查询物化
`ordering_operation`	排序操作
`grouping_operation`	分组操作

8.10 优化器跟踪

当执行计划明显不合理时，可以查看优化器为什么选择某个索引：

SET optimizer_trace='enabled=on,one_line=off';
SET optimizer_trace_max_mem_size=1048576;

SELECT *
FROM query_orders
WHERE user_id = 1001
  AND order_status = 'PAID';

SELECT * FROM information_schema.OPTIMIZER_TRACE\G

SET optimizer_trace='enabled=off';

重点搜索：

range_analysis
index dives
rows_estimation
considered_execution_plans
best_plan

优化器跟踪输出很大，只适合单条 SQL 深度排查，不适合全量开启。

8.11 执行计划误判案例

案例一：隐式类型转换

EXPLAIN
SELECT *
FROM query_orders
WHERE order_no = 1;

order_no 是 VARCHAR，传入数字会导致隐式转换，可能无法正常使用索引。应改为：

SELECT *
FROM query_orders
WHERE order_no = '1';

案例二：函数包裹索引列

EXPLAIN
SELECT *
FROM query_orders
WHERE DATE(created_at) = '2026-02-01';

应改写为半开区间：

SELECT *
FROM query_orders
WHERE created_at >= '2026-02-01 00:00:00'
  AND created_at < '2026-02-02 00:00:00';

案例三：前导列缺失

EXPLAIN
SELECT *
FROM query_orders
WHERE created_at >= '2026-02-01';

idx_user_created 不能从 created_at 开始使用。需要新增 idx_created_at 或调整查询条件。

案例四：扫描行少但响应慢

执行计划 rows 很小，仍然可能慢：

回表次数多；
排序或临时表；
锁等待；
Buffer Pool 未命中；
网络传输大字段；
应用处理慢。

执行计划只描述数据库内部执行，不覆盖端到端耗时。

8.12 看执行计划的标准流程

拿到慢 SQL 后按顺序检查：

确认 SQL 业务语义和参数值；
看是否全表扫描或扫描量过大；
看是否缺少可用索引；
看联合索引前缀是否匹配；
看是否回表过多，能否覆盖索引；
看是否临时表或 filesort；
看 JOIN 顺序和关联索引；
对比 EXPLAIN 与 EXPLAIN ANALYZE；
更新统计信息后复测；
结合慢日志、锁等待和会话状态定位。

8.13 生产注意事项

不要看到 ALL 就立刻加索引，小表全扫可能更快；
不要看到 Using filesort 就认定故障，小结果排序成本可能很低；
索引提示只能作为临时手段；
优化前保存原始 SQL 和执行计划；
优化后验证结果集一致；
关注扫描行数、返回行数和耗时三者关系；
高危查询先在只读实例验证；
SQL 超时应由应用和数据库同时治理。

本章小结

执行计划的核心是回答三件事：访问哪些表、用什么方式取数、额外做了多少工作。type 说明访问路径，key 与 key_len 说明索引使用程度，rows 与 filtered 说明扫描估算，Extra 说明是否回表、排序和临时表。生产排查要结合参数值、真实数据分布、EXPLAIN ANALYZE 和锁等待，而不是机械地根据一个字段下结论。

思考题

ref、range、index、ALL 分别代表什么访问方式？
possible_keys 有索引但 key 为 NULL，可能是什么原因？
Using index 和 Using index condition 有什么区别？
为什么 rows 只是估算值？如何让它更可靠？
找一条生产慢 SQL，写出优化前后的执行计划差异和验证步骤。