外观
14 - EXPLAIN 到底怎么看:从执行计划找到真正的慢点
摘要与阅读定位
一条 SQL 只返回几行,为什么仍然很慢?因为返回行数只是最终结果,数据库可能在过滤、关联和汇总之前处理了大量记录。执行计划展示数据库准备怎样完成查询;带运行统计的计划进一步帮助判断哪些任务处理的记录多、重复次数多或耗时高。本文从四行小表开始,再用订单与明细演示:先核对结果,再比较估算与实际工作量,最后增加索引并检查不同参数。执行计划能解释数据库内部的工作,但不能代替应用全链路计时。
- 前置:11 索引访问成本、12 范围/过滤/排序/覆盖、05 JOIN 基数。
- 后续:13 基数估算与成本选择、15 排序临时空间;线上闭环:25 慢 SQL SOP。
- 基线:MySQL 8.0.18+ / 8.4、InnoDB。8.0.18 引入 EXPLAIN ANALYZE,细节按小版本核验;遵循 A。以下 SQL 用于隔离实验,行数与金额均为按数据生成规则推导的预期结果;具体访问路径和耗时取决于目标实例。计时示意不代表实测输出。
初次先完成下面的小查询,学会区分真实结果与普通 EXPLAIN;再读第 2—3 节的时间与行数。第 4—6 节综合关联和索引对照是第二层,第 7—8 节采集设施与跨产品字段是可选进阶。
第一份计划:先查对,再看数据库准备怎样查
执行计划可以理解为一组相互配合的任务:从表或索引取记录,按条件过滤,再进行关联、排序或汇总。每项任务称为算子。例如,过滤任务接收取数任务的记录,只把符合条件的记录交给上层;聚合任务接收这些记录,计算每组的汇总值。
TREE 是树形展示格式:父节点使用子节点产生的记录,一个节点连同它下面的所有节点称为子树。树形缩进表示这种依赖关系,不能仅按从上到下或从下到上的顺序理解整个查询。先用传统表格格式认识访问方式,再读树形数据流。
先在隔离实例、旧事务已明确结束的连接中建四行表;表已存在时停止并确认归属:
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit = 1;
CREATE TABLE plan_basic (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
KEY idx_plan_basic_customer (customer_id)
) ENGINE = InnoDB;
INSERT INTO plan_basic VALUES (1, 10, 100), (2, 20, 50), (3, 10, 40), (4, 30, 70);
SELECT id, amount FROM plan_basic WHERE customer_id = 10 ORDER BY id;推导预期两行:(1,100)、(3,40)。然后查看估算计划,不用先运行实际分析:
sql
EXPLAIN FORMAT=TRADITIONAL
SELECT id, amount FROM plan_basic WHERE customer_id = 10 ORDER BY id;初次只核对四项,不要求记住整张字段表:
| 字段 | 先回答的问题 | 不要误读 |
|---|---|---|
table | 正在解释哪张表的访问 | 表名顺序不等于全部算子的执行顺序 |
type、key | 是扫描还是按键查找,计划选哪个索引 | possible_keys 是候选,key 才是选中的索引 |
rows、filtered | 估计要检查多少行、过滤后剩多少百分比 | 不是实测,也不是精确业务 COUNT |
Extra | 是否还有过滤、额外排序、索引覆盖等线索 | Using filesort 表示额外排序,不等于磁盘写入 |
索引覆盖指查询需要的列可由索引提供,无需为这些列另取完整行。本例还要读取 amount,客户索引并不覆盖这些返回列。小表可能选择全表扫描:总共只有四行,使用索引不一定能减少整体取数成本。应按输出解释选择,而不是要求实验必须命中索引。
1. 现象:SQL 返回一百组,不代表只处理一百行
以客户月度报表为例,少量客户汇总行可能来自大量订单与明细。聚合把许多输入行压成少量结果,却不会消除已经发生的取数和关联工作。另一个容易忽视的情况是重复访问:即使一次索引点查很快,若外层记录触发了百万次点查,总成本仍可能很高。ALL 表示全表扫描,Using filesort 表示额外排序;这些标记说明任务类型,不能单独证明它就是慢点。
分析前保存 SQL、真实参数类型和值(敏感值按规范脱敏)、数据库版本与库名、统计信息、索引定义、隔离级别和测试数据分布。统计信息是优化器估计数据规模与分布的依据。归一化 SQL 会把不同参数归为相似语句,不能区分匹配很多记录的热门值与匹配很少记录的稀有值;即使访问路径相同,工作量也可能相差很大。
2. 安全:估算、运行统计和调用耗时不同
| 工具 | 能回答什么 | 风险与盲区 |
|---|---|---|
| EXPLAIN | 优化器准备选择的估算路径 | 本文普通 SELECT 不执行返回结果;仍需解析/优化/元数据访问,不是绝对零成本 |
| EXPLAIN FORMAT=TREE | 算子关系及估算行数/成本 | 无实际 loops 和运行耗时,不能证明实际瓶颈 |
| EXPLAIN ANALYZE | 实际执行时的 iterator 行数、loops 和时间 | 运行原查询且增加观测开销;不可在高峰盲跑未知成本 SQL |
| 语句统计/慢日志 | 语句级耗时、Rows_examined 等 | 采集配置与保留期限制;摘要不是单次参数证据 |
| 客户端/应用计时 | 连接等待、网络、结果消费等全链路 | 必须拆边界,不可全归因数据库执行 |
本文的 EXPLAIN ANALYZE 示例仅用于隔离环境中的实验 SELECT,不能直接推广到生产 UPDATE、DELETE;支持的语句范围需按目标版本确认。锁定读 SELECT 也可能持锁,用户函数可能有副作用;“SELECT”不是安全充分条件。连接池等待、连接建立和全部结果传输不等于根 iterator 时间,见 01、23 连接耗尽。
3. 原理:actual rows 与耗时的解释单位
MySQL 的树形运行计划用 **iterator(迭代器)**表示逐次产生记录的执行节点。父节点向子节点请求记录,再把处理结果交给自己的父节点;请求向下传递,记录向上交付。
在 EXPLAIN ANALYZE 的实际统计中,loops 表示节点执行了几次,rows 表示每次执行平均输出多少行。这里的输出是交给父节点的记录,不一定包含节点内部检查后丢弃的候选。普通 EXPLAIN 的 rows 是估计检查行数,不能与这里的实际输出混用。
以下是人为构造的计时示意,不是任何目标库输出:
text
Nested loop ... (actual time=0.200..25.000 rows=1000 loops=1)
Outer ... (actual time=0.100..4.000 rows=1000 loops=1)
Inner ... (actual time=0.010..0.020 rows=1 loops=1000)- time 单位为毫秒;前值是到首行的时间,后值是此执行任务完成的时间。没有输出行时不要将首行字段解释为已经返回过业务行。
- 多次
loops的时间显示每次循环的平均。Nested Loop是嵌套循环连接:外侧每产生一行,就驱动内侧查找匹配记录。上例外侧输出 1000 行,内侧因此执行 1000 次;内侧 0.020×1000≈20 ms,可作为累计耗时的线索。 - iterator 的计时包含子 iterator,不包含父 iterator。父节点完成任务时已经包含向子节点取数的时间,所以不能把 25+4+20 相加,得到所谓查询 49 ms。
- actual rows 在多次调用时按每 loop 平均输出理解,乘 loops 可估计累计输出;小数/舍入和空循环需要考虑。
- 输出行数不是设备 I/O(输入/输出)次数,也不必等于原始索引项扫描数。ICP(索引条件下推)会在存储引擎读取索引时先过滤部分候选;过滤位置、预读、缓存和
LIMIT都会影响输出与底层读取量的关系。
若计划单独展示过滤节点,可以看其子节点输出多少行、过滤后留下多少行,以判断条件减少了多少输入。若过滤已在引擎内部完成,TREE 不一定单独报告全部候选索引项。语句级 Rows_examined(检查行数)、Handler 类存储引擎操作计数和设备 I/O 统计分别观察不同层次,不能互相精确替代。
父节点累计时间可以定位昂贵子树;做父减子求“自身耗时”最多是同模型下的粗线索,流水执行、不同 loops 和嵌套重叠使简单差分不可靠。MySQL iterator 模型也不能直接推广到分布式并行算子的墙钟时间,即从开始到结束实际经过的时间。
4. 实现:一百订单,每单三条明细
下面把访问、过滤、连接和聚合放在同一个例子中。数字表的十位与个位交叉组合生成 1—100 的订单编号,再为每单生成三条明细。先核对生成的数据,再逐条查看普通查询结果、估算计划与实际计划,避免只看末尾耗时而忽略结果或输入量。
先结束实验事务,并确认隔离库中的同名 plan_* 表属于本实验;以下语句会删除并重建其中三张表。日期范围、客户分组和金额都可按生成规则复核。
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET autocommit=1;
DROP TABLE IF EXISTS plan_item;
DROP TABLE IF EXISTS plan_order;
DROP TABLE IF EXISTS plan_digit;
CREATE TABLE plan_digit (digit INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO plan_digit VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE plan_order (
id BIGINT PRIMARY KEY,customer_id BIGINT NOT NULL,
created_at DATETIME NOT NULL,status VARCHAR(16) NOT NULL,
KEY idx_plan_created (created_at,id)
) ENGINE=InnoDB;
CREATE TABLE plan_item (
id BIGINT PRIMARY KEY,order_id BIGINT NOT NULL,
quantity INT NOT NULL,price DECIMAL(10,2) NOT NULL,
payload VARCHAR(200) NOT NULL
) ENGINE=InnoDB;
INSERT INTO plan_order
SELECT num,MOD(num-1,10)+1,
TIMESTAMPADD(DAY,MOD(num-1,20),'2026-09-01'),
CASE WHEN num<=90 THEN 'PAID' ELSE 'CANCELLED' END
FROM (
SELECT 1+ones.digit+10*tens.digit AS num
FROM plan_digit AS ones CROSS JOIN plan_digit AS tens
) AS seq;
INSERT INTO plan_item
SELECT orders.id*10+line.digit,orders.id,line.digit,10,REPEAT('x',200)
FROM plan_order AS orders CROSS JOIN plan_digit AS line
WHERE line.digit BETWEEN 1 AND 3;
SELECT COUNT(*) AS order_rows FROM plan_order;
SELECT COUNT(*) AS item_rows,SUM(quantity*price) AS amount FROM plan_item;
ANALYZE TABLE plan_order,plan_item;预期 100 订单、300 明细、总额 6000;每单三行,金额 10+20+30=60。表初始故意没有 order_id 二级索引,建立之后再对照;缺索引不保证 MySQL 8.4 一定选择反复全扫描,可能选择 Hash Join(哈希连接):先按连接键为一侧记录建立哈希表,再用另一侧记录查找匹配项。这两侧分别称为构建侧与探测侧,应按实际计划识别。
5. 验证:先核结果,再读数据流
sql
SELECT orders.customer_id,SUM(item.quantity*item.price) AS amount
FROM plan_order AS orders
JOIN plan_item AS item ON item.order_id=orders.id
WHERE orders.created_at>='2026-09-01' AND orders.created_at<'2026-09-06'
GROUP BY orders.customer_id ORDER BY orders.customer_id;
EXPLAIN FORMAT=TREE
SELECT orders.customer_id,SUM(item.quantity*item.price) AS amount
FROM plan_order AS orders
JOIN plan_item AS item ON item.order_id=orders.id
WHERE orders.created_at>='2026-09-01' AND orders.created_at<'2026-09-06'
GROUP BY orders.customer_id ORDER BY orders.customer_id;
EXPLAIN ANALYZE
SELECT orders.customer_id,SUM(item.quantity*item.price) AS amount
FROM plan_order AS orders
JOIN plan_item AS item ON item.order_id=orders.id
WHERE orders.created_at>='2026-09-01' AND orders.created_at<'2026-09-06'
GROUP BY orders.customer_id ORDER BY orders.customer_id;预期日期条件选中 25 笔订单,连接后产生 75 条匹配明细,最终客户 1—5 各为 300,总额 1500。原因是日期每 20 笔订单重复一次,9 月 1—5 日各有 5 笔订单,分别对应客户 1—5;每单金额为 60,每个客户因此得到 5×60=300。聚合把 75 行归为五组,这五行结果不能代表扫描或连接只处理了五行。
按实际计划逐项核对:
| 项目 | 核对方式 |
|---|---|
| 访问路径 | 两表谁作为驱动/构建侧?扫描、范围、点查或 Hash Join? |
| 过滤 | 日期条件在哪一级?条件之前有多少输出候选? |
| 连接工作量 | Nested Loop 看外侧输出与内侧 loops;Hash Join 看构建/探测输入 |
| 估算偏差 | 同一个 iterator 的 estimated 与实际每 loop 输出对齐比较 |
| 聚合排序 | 75 明细进入何种聚合、是否额外排序?不靠名字断言落盘 |
| 耗时 | 从耗时较高的子树向下定位,父子不相加;保留客户端计时差异 |
连接节点有多个子节点,嵌套循环的外侧与内侧、哈希连接的构建侧与探测侧各有职责。因此,不能只凭树形图上哪个节点最靠下,或 SQL 中哪个表写在前面,确定访问和驱动关系。
6. 单变量改动:加关联索引再回归
DDL(数据定义语句)用于创建或修改表、索引等结构,应在业务事务外执行。下面只增加关联列索引,随后用 ANALYZE TABLE 更新表统计;记录索引定义和统计更新时间,以便解释前后计划差异。ANALYZE TABLE 是统计维护操作,与运行查询的 EXPLAIN ANALYZE 不同。
sql
ALTER TABLE plan_item ADD INDEX idx_plan_item_order (order_id);
ANALYZE TABLE plan_item;
EXPLAIN ANALYZE
SELECT orders.customer_id,SUM(item.quantity*item.price) AS amount
FROM plan_order AS orders
JOIN plan_item AS item ON item.order_id=orders.id
WHERE orders.created_at>='2026-09-01' AND orders.created_at<'2026-09-06'
GROUP BY orders.customer_id ORDER BY orders.customer_id;结果必须仍为五个客户各 300。新增索引使数据库可以按 order_id 定位明细,避免为每个订单都从无关明细中寻找匹配。若实际选择嵌套循环,并按 order_id 做索引查找(lookup),每次匹配三条明细应与预期一致;若仍用哈希连接,则比较输入规模和估算成本。小表耗时容易受缓存状态、调度和观测开销影响,不保证加索引明显变快。
参数回归是改动后检查不同代表性参数,确认收益和结果不只对某一个值成立。日期对照至少检查:上述窄日期区间(25 单)、全月(100 单)、空区间(0 单)。状态对照则统一使用全月范围:created_at >= '2026-09-01' AND created_at < '2026-10-01',仅改变状态值,比较热门 PAID 与稀有 CANCELLED。先核对订单数量:
sql
SELECT COUNT(*) AS order_rows
FROM plan_order AS orders
WHERE orders.created_at>='2026-09-01' AND orders.created_at<'2026-10-01'
AND orders.status='PAID';
SELECT COUNT(*) AS order_rows
FROM plan_order AS orders
WHERE orders.created_at>='2026-09-01' AND orders.created_at<'2026-10-01'
AND orders.status='CANCELLED';两条查询的预期输出分别为 90、10。所有订单日期都在 9 月 1—20 日,全月条件包含全部 100 单;编号 1—90 为 PAID,91—100 为 CANCELLED。若沿用上面的 9 月 1—5 日窄范围,预期数量则是 PAID=25、CANCELLED=0,不能用它验证 90 与 10 的状态分布。
比较状态对报表计划的影响时,将上面的报表日期范围统一改为全月,分别追加 AND orders.status='PAID' 和 AND orders.status='CANCELLED';两次查询的返回列、连接、分组、排序、索引和统计信息均保持一致。预期连接后分别产生 270、30 条匹配明细,均汇总为客户 1—10 的十组结果,每个客户的金额分别为 540、60。计数查询用于确认匹配订单数量,报表查询用于观察连接和聚合工作,两者的计划不能互相替代。
扩大数据前固定生成规则、索引、统计信息、缓存是否已预热以及并发条件;还要观察新增索引的写入维护成本和空间占用,不能只验证读取。
如果需要比较另一条候选路径,可在隔离环境使用索引提示,例如用 FORCE INDEX 限制候选索引,以检验访问路径是否影响工作量。一次对照不足以支持在业务代码中永久固定索引;估算偏差也不能一概归因于统计信息陈旧。13进一步解释估算偏差、列相关性、参数分布与成本选择。
7. 语句证据:单次、摘要与实例计数分层
MySQL 的 Performance Schema 是运行事件与统计的观测设施,其中语句事件记录某次 SQL 的运行信息,摘要则汇总多次相似 SQL。本节是可选进阶,语句历史采集 history 并非每个实例都已启用。先用明确业务结果和普通计划建立假设,确需定位某次运行时再确认采集配置与权限,不为教学默认打开全部监控。
采集可用时,在目标会话记录线程身份。CONNECTION_ID() 返回连接 ID;Performance Schema 的 THREAD_ID 是内部线程标识,EVENT_ID 标识该线程上的事件,两者用于定位观测记录,不能直接作为终止连接所用的 ID:
sql
SELECT CONNECTION_ID() AS connection_id;
SELECT THREAD_ID FROM performance_schema.threads
WHERE PROCESSLIST_ID=CONNECTION_ID();
DESCRIBE performance_schema.events_statements_history;启用 history 的环境可由独立观察会话按目标 THREAD_ID/EVENT_ID 查询 TIMER_WAIT(事件计时)、ROWS_EXAMINED(检查行数)、ROWS_SENT(发送行数)以及排序与临时表统计;字段先用 DESCRIBE 核验,计时单位也须确认后再换算。历史记录使用容量有限、旧记录会被新记录覆盖的环形缓冲,连续诊断可能挤掉目标事件;被观测的 EXPLAIN ANALYZE 也不是完全等同于普通 SELECT 事件,要保存 statement 类型。
摘要按归一化 SQL 汇总多次运行,不保存每次全部参数;全局 Created_tmp/Sort/Buffer Pool 计数则混合其他连接和后台行为。使用时间窗口差值,不把一个累计大数当当前故障。只读诊断也需要权限与采集成本控制,见 26。
Using filesort 表示不能直接用该访问路径的索引顺序完成排序,不等于一定写磁盘;Using temporary 也不等于磁盘临时表。是否溢写由运行统计、数据量、内存及临时文件证据联合判断,15提供后续排障,不能仅由 TREE 名称判断存储介质。
8. 跨产品边界:先固定计划类型与统计来源
Oracle 常用 DBMS_XPLAN.DISPLAY_CURSOR 读取目标游标;只有已收集运行统计、选对 SQL_ID/child cursor 和执行次数时,ALLSTATS LAST 才有可解释的实际行数。解读行数、启动次数、缓冲访问、物理读取与临时空间时,要同时查看谓词(过滤或连接条件)的位置,不能套用同一套乘法。使用时须确认 Oracle 版本、权限和统计采集方式。游标是数据库管理语句执行状态的对象;SQL_ID 标识语句,child cursor 是该语句的具体子游标。E-Rows 与 A-Rows 分别表示估算和实际行数,Starts 表示启动次数;Buffers、Reads、TempSpc 分别是缓冲访问、物理读取和临时空间相关指标,各有统计含义与单位,应按对应版本说明解释。
分布式计划还要识别协调任务、存储任务和节点间传输。Exchange 通常表示任务间的数据交换,RPC 是远程过程调用;它们可能引入传输与等待。多个子任务并行时,最大耗时与平均耗时回答的问题不同,一个节点慢不等于所有节点均慢。TiDB、OceanBase、GaussDB 的具体字段、落盘行为和算子支持须按目标版本确认。不能把 MySQL 毫秒与 loops 规则直接迁移。29用于理解跨分片数据搬运,30用于产品范围。
9. 排障闭环、取舍与清理
- 结果不对,先查 05 JOIN、06 过滤、07 聚合,不要优化错误结果。
- 服务慢但 iterator 快,拆连接排队、等待、结果体积与消费;不盲目加索引。
- iterator 显示多输入/多 loops,先减少工作量,再验证访问路径与估算。
- 只改一个因素,保存完整结果、计划与分布,覆盖代表性参数和写入成本。
- 记录回退与环境恢复方案;清理前结束所有事务,确认表归属后,在隔离库删除
plan_item、plan_order、plan_digit、plan_basic。
定位工作量之后,可用 13解释优化器为什么选择这个计划,用 15分析中间结果的资源成本;线上排查按 25组织证据和处置。
10. 名词解释
| 名词 | 含义 |
|---|---|
| EXPLAIN / EXPLAIN ANALYZE | 查看估算计划 / 实际运行并增加观测信息,风险不同 |
| 算子 / iterator | 执行任务 / 本文 MySQL 中逐次请求记录的执行接口与任务模型 |
| estimated / actual | 估计 / 实测;比较时必须是同一任务与同一统计单位 |
| rows / loops | 每次执行平均输出行数 / 执行次数,不直接等于设备 I/O |
| 子树时间 | 一个任务及其下层任务的累计时间,父子不可直接相加 |
| 语句事件 / 摘要 | 某次 SQL 的采集记录 / 多次相似 SQL 的汇总 |
| THREAD_ID / EVENT_ID | Performance Schema 内部线程身份 / 该线程事件身份,不能直接作 KILL 的连接 ID |
| 参数回归 | 改动后检查不同典型与极端参数,避免只优化某一个值 |
参考资料
- MySQL 8.4 Reference Manual:EXPLAIN Statement(EXPLAIN ANALYZE)、EXPLAIN Output Format、Performance Schema Statement Event Tables、Hash Join Optimization。
- 适用条件:本文实际计划、执行耗时、采集字段与跨产品命令应在目标环境核验。