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 时,必须把循环次数一起看。

构造一个可重复的基线查询

下面创建一张小型订单表。数据量故意保持很小,只用于确认字段含义;真实优化判断必须回到接近生产规模和分布的数据上。

sql
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;

预期结果

text
+-----------+
| row_count |
+-----------+
|         8 |
+-----------+

对同一查询比较估算与实测

为了让这个极小数据集稳定展示目标索引,示例使用 FORCE INDEX。它只是教学手段,不代表生产优化时应该强制索引。

sql
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';

预期结果

text
-> <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 后,关键节点的估算更接近实测、扫描行数下降,并且高耗时算子的真实时间随之降低。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

使用 EXPLAIN ANALYZE 校验估算

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通