MySQL / 系统课时
使用 EXPLAIN ANALYZE 校验估算
使用 EXPLAIN ANALYZE 校验估算
普通 EXPLAIN 只告诉你优化器打算怎么执行,EXPLAIN ANALYZE 则真正运行查询并把估算与实测放到同一棵执行树里。本课用一个可复现实验读懂 rows、actual time 与 loops,并据此区分估算偏差和真实耗时瓶颈。
学习目标
- 能区分 estimated rows、actual rows、actual time 与 loops 的含义
- 能从执行树中找到明显的基数估算偏差和热点算子
- 能判断应该优先修正统计信息、索引还是 SQL 访问路径
先确认 EXPLAIN ANALYZE 做了什么
EXPLAIN ANALYZE 与普通 EXPLAIN 最大的区别是它会执行查询。输出仍是一棵迭代器树,但每个可测量节点同时带有优化器估算和真实执行数据。用于线上诊断时,应先确认语句是可安全执行的只读查询,并评估它本身可能产生的 I/O、CPU 与锁等待。
在 TREE 输出中,(cost=<VALUE> rows=<VALUE>) 属于执行前估算;(actual time=<VALUE>..<VALUE> rows=<VALUE> loops=<VALUE>) 属于执行后的测量。actual time 前一个值是取得首行的平均时间,后一个值是取完该节点结果的平均时间;当 loops 大于 1 时,必须把循环次数一起看。
构造一个可重复的基线查询
下面创建一张小型订单表。数据量故意保持很小,只用于确认字段含义;真实优化判断必须回到接近生产规模和分布的数据上。
DROP TABLE IF EXISTS explain_orders;
CREATE TABLE explain_orders (
id BIGINT PRIMARY KEY,
tenant_id INT NOT NULL,
status VARCHAR(16) NOT NULL,
created_at DATETIME NOT NULL,
amount DECIMAL(10,2) NOT NULL,
KEY idx_tenant_status_created (tenant_id, status, created_at)
) ENGINE=InnoDB;
INSERT INTO explain_orders
(id, tenant_id, status, created_at, amount)
VALUES
(1, 42, 'PAID', '2026-09-01 09:00:00', 100.00),
(2, 42, 'PAID', '2026-09-10 10:00:00', 120.00),
(3, 42, 'PAID', '2026-09-20 11:00:00', 80.00),
(4, 42, 'PAID', '2026-10-02 12:00:00', 90.00),
(5, 42, 'NEW', '2026-09-05 13:00:00', 70.00),
(6, 7, 'PAID', '2026-09-06 14:00:00', 60.00),
(7, 42, 'PAID', '2026-08-20 15:00:00', 50.00),
(8, 42, 'PAID', '2026-09-30 16:00:00', 110.00);
SELECT COUNT(*) AS row_count FROM explain_orders;预期结果
+-----------+
| row_count |
+-----------+
| 8 |
+-----------+对同一查询比较估算与实测
为了让这个极小数据集稳定展示目标索引,示例使用 FORCE INDEX。它只是教学手段,不代表生产优化时应该强制索引。
EXPLAIN ANALYZE
SELECT id, created_at, amount
FROM explain_orders FORCE INDEX (idx_tenant_status_created)
WHERE tenant_id = 42
AND status = 'PAID'
AND created_at >= '2026-09-01 00:00:00'
AND created_at < '2026-10-01 00:00:00';预期结果
-> <iterator using idx_tenant_status_created> (cost=<VALUE> rows=<VALUE>) (actual time=<VALUE>..<VALUE> rows=4 loops=1)不同 MySQL 小版本、统计信息和机器负载会改变算子描述、cost 与时间,因此这里用 <VALUE> 表示动态值;这组固定数据中,符合条件的实际行数应为 4。
按 rows、loops、time 的顺序读计划
| 指标 | 先问什么 | 常见信号 |
|---|---|---|
| estimated rows | 优化器以为会返回多少行 | 与实际值相差一个或多个数量级 |
| actual rows | 节点实际每轮产生多少行 | 远高于估算时会放大下游成本 |
| loops | 节点被调用了多少轮 | 内表查找或相关子查询可能被重复执行 |
| actual time | 首行和全部结果用了多久 | 高耗时节点值得继续拆解其子树 |
先比较 estimated rows 与 actual rows。若估算是几十行,实际却是几万行,优化器可能因为统计信息陈旧、数据分布倾斜、列之间存在相关性或缺少合适索引而选错访问路径。十倍偏差只是经验上的提醒,不是硬阈值;偏差越大,越应该优先调查估算来源。
再看 loops。循环节点的总工作量不能只看一次迭代的 rows 或时间,可以用“实际行数 × loops”和“取完结果的时间 × loops”做量级估算。不要把父节点和所有子节点的时间直接相加,因为父节点计时通常已经包含其子树工作。
最后找热点算子。如果估算与实际行数接近,但某个索引查找、排序或聚合仍然耗时很高,问题更可能在随机 I/O、回表次数、宽行读取、排序规模或资源竞争,而不是基数估算本身。
估算失真后再决定怎么修
看到偏差后不要立刻“加索引”。如果表的统计信息长期未更新,可以先执行 ANALYZE TABLE 刷新统计;如果非索引列高度倾斜,可评估直方图;如果多个谓词强相关,单列统计可能仍然无法准确表达联合选择性,此时更应结合联合索引和实际查询模式重新设计访问路径。
真正的验收标准不是“cost 变小”,而是重新运行同一条 EXPLAIN ANALYZE 后,关键节点的估算更接近实测、扫描行数下降,并且高耗时算子的真实时间随之降低。