外观
25 - 慢 SQL 的完整排查流程:从监控告警到执行计划
摘要与阅读定位
一条 SQL 在测试库很快,线上接口却频繁超时,应该先改索引还是先查连接和锁?先确定时间花在哪里,再用同一时间段的请求、参数、等待和执行计划验证原因。本文按“界定问题 → 保存证据 → 受控止损 → 验证单一改动 → 小范围发布与回退”组织排查流程,并用一个库存查询说明:查询工作量小,也可能因为等待而变慢。
- 前置分诊:24 资源与等待、14 运行计划。
- 分支解释:13 优化器、08 分页、15 临时空间、19 锁、23 连接生命周期。
- 基线 MySQL 8.0.18+ / 8.4、InnoDB;遵循 A。示例结果为推导预期,生产耗时、计划和收益应使用实际证据。
阅读入口:先定位等待,再决定是否改 SQL
初读按第 1 节问题边界 → 第 2 节证据 → 第 3 节止损 → 第 5 节分支 → 第 7、8 节验证与回退推进。第 4 节会话锁等待是可选进阶;初读先跑三行数据的普通 SELECT,不需要同时开三个连接。第 6 节日志配置先只读核验,不为练习开启全量慢日志。
一次数据库访问可以先按下面的路径理解:
text
等待池连接 → 借到连接 → 发送 SQL → 数据库执行并等待所需资源 → 接收、处理结果 → 归还连接连接池让应用重复借用数据库连接。等待借到连接的时间称为 acquire;借到之后,数据库可能在扫描和计算,也可能在等锁或磁盘读写;收到结果后,应用还需要读取、转换和处理数据。执行与传输可能重叠,显式事务还涉及提交或回滚,因此这条路径用于划分观察边界,不能直接当作耗时相加公式。
假设接口用了 1 秒,其中 900 ms 在应用池等待,而目标 SQL 只占 10 ms。此时先改索引未必改善主要延迟,应查连接为何迟迟不归还。反过来,如果同一时间窗口的实际扫描工作量暴增,再检查参数、表关联产生的行数和访问路径。这是用于说明计时边界的假设例子;完整计时定义见01 SQL 路径。
1. 现象:测试库快不是生产慢的反证
SQL Digest 是归一化 SQL 类别的摘要标识,用来把结构相似的语句归类统计,不包含每次完整绑定参数。同一 Digest 中,大租户可能有更多数据,宽日期范围可能需要读取更多行;并发写入可能引入锁等待,所需数据页不在缓存中时可能增加物理读取,连接池排队则发生在发送 SQL 之前。这些因素分别改变工作量或等待时间,不能仅凭 SQL 文本相同就认为执行条件相同。
排查前先定义 SLO(服务目标,如响应时长和错误率约定),明确计时终点:是数据库语句结束、接口全链路结束、收到首行,还是消费完全部结果?P99 是窗口内约 99% 样本不超过的时长,用于观察较慢一端的请求。超时和失败也要保存并纳入评估;只统计成功请求,可能因最慢请求被剔除而看到虚假的改善。
2. 固定一份证据账本
| 证据 | 最低记录项 | 不足时如何标记 |
|---|---|---|
| 请求身份 | request/trace ID、实例/库、用户、应用版本、绝对时间与时区 | 缺对应请求不能强行关联 SQL |
| 查询 | SQL/Digest、参数类型、租户规模、日期跨度、排序/分页深度 | 仅 Digest 不代表真实参数分布 |
| 时间 | acquire、发送/执行、首行、全部消费、提交/回滚及重试区间 | 客户端执行方法可能包含传输,不全归 CPU |
| 数据库事件 | CONNECTION_ID、THREAD_ID、EVENT_ID、语句统计与采集配置 | 历史事件记录被覆盖或未启用时记为缺证 |
| 计划/数据 | 完整计划、版本、索引、统计、输入/结果行数与分布 | 当前 EXPLAIN 不一定是历史那次计划 |
| 同期负载 | 到达/完成率、P99、锁链、CPU/I/O/临时空间及后台任务 | 采样间隔不一致不可作精确因果 |
这份账本用于把“哪个请求慢”与“它在数据库里做了什么”串起来。request/trace ID 标识请求或调用链;CONNECTION_ID 标识数据库连接,THREAD_ID 标识 Performance Schema 中的线程,EVENT_ID 定位线程内的事件。它们不能互换,关联方法见 26。Performance Schema 是 MySQL 的运行事件采集与统计设施,历史记录是否存在取决于采集配置和保留容量。
先用 DESCRIBE 确认目标版本的字段,再确定采集权限、频率和保留期。参数可能包含个人信息,原始证据保存在受控位置,工单只提供脱敏特征。缺少请求关联、历史事件或事故时计划时,应把对应判断列为待核验,不能用当前状态补成历史事实。
3. 先止损再验证,但不销毁现场
若业务已不可用,先受控暂停低优先报表/批处理、限制入口与重试、保留关键事务容量;保存足够现场后处理根阻塞。操作需责任人、范围、截止时间、停止条件及回退,不自动重启、清空统计或批量 kill。
KILL QUERY 可能只中止当前语句,事务及此前持有的锁仍存在;客户端超时也不证明数据库已停止执行。死锁、锁超时和未知提交结果需按 20分别处理。未知提交结果指客户端无法确认事务是否已经提交,此时盲目重试可能重复业务操作;故障期间的额外重试还会增加排队压力。
扩大连接池只允许更多请求同时持有连接,不会自动增加数据库处理能力。按 23确认本地池等待、连接持有时间和数据库容量,再决定是否调整池大小。
4. 小查询入口与可选锁等待实验
以下实验使用 MySQL 8.0.18+ / 8.4、InnoDB,需要创建实验库和表的权限。先结束所有实验事务,并确认 kb_database_lab 是隔离实验库:初始化会删除已有的同名 sop_stock 表。表中有三个商品,tenant_id 是租户标识,sku 是商品编码,quantity 是非负库存;同一租户的商品编码由联合唯一键约束。这里仅模拟索引定位与锁等待,不实现保证重复请求只扣一次库存的完整幂等协议。
sql
CREATE DATABASE IF NOT EXISTS kb_database_lab;
USE kb_database_lab;
SET @sop_init_autocommit=@@session.autocommit;
SET autocommit=1;
DROP TABLE IF EXISTS sop_stock;
CREATE TABLE sop_stock (
id BIGINT PRIMARY KEY,tenant_id BIGINT NOT NULL,sku VARCHAR(32) NOT NULL,
quantity INT NOT NULL,
UNIQUE KEY uk_sop_tenant_sku (tenant_id,sku),
CONSTRAINT chk_sop_quantity CHECK (quantity>=0)
) ENGINE=InnoDB;
INSERT INTO sop_stock VALUES (1,1,'A',10),(2,1,'B',20),(3,2,'A',30);
SELECT id,quantity FROM sop_stock WHERE tenant_id=1 AND sku='A';
EXPLAIN FORMAT=TRADITIONAL SELECT id,quantity FROM sop_stock WHERE tenant_id=1 AND sku='A';预期查询结果:ID 为 1、库存为 10。执行计划可能显示单行唯一键访问,具体计划文本由目标环境决定。三行表用于理解查询和等待机制,不能据此预测生产吞吐。
租户 1 的 A 与租户 2 的 A 是不同商品记录,查询条件包含联合唯一键的两个字段。执行计划描述数据库如何寻找和处理数据;TRADITIONAL 是表格形式的计划输出,先看候选索引、实际选择的索引、访问方式和估算行数。计划中的处理步骤称为算子,算子树与实际行数的读法见14。即使预计只访问一行,也不能排除执行时的等待。
4.1 锁等待对照(可选进阶)
锁定读是读取数据时申请相应锁的查询,本例使用 FOR UPDATE。A 更新记录后未提交,持有排他锁;B 要锁定同一记录,就需要等 A 结束事务。这样可以把“定位一行”与“等到能读取并锁定这一行”分开观察。
准备三个会话:A 与 B 使用独立连接,C 负责观察。各会话均执行 USE kb_database_lab,修改设置前先保存原值。B 的 30 秒锁等待超时用于避免实验一直悬挂,不是生产统一参数:
| 步骤 | 会话 | 操作与预期 |
|---|---|---|
| 1 | A | START TRANSACTION;更新 ID 1 为 9,先不提交,持有排他锁 |
| 2 | B | START TRANSACTION;按租户+sku 锁定读,预期等待 A |
| 3 | C | 按 19关联等待者与持有者,保存锁对象、线程和事务 |
| 4 | A | ROLLBACK 释放;B 返回库存 10,不是 A 未提交的 9 |
| 5 | B | ROLLBACK,恢复超时配置;检查表库存仍 10/20/30 |
A:
sql
USE kb_database_lab;
SET @sop_a_autocommit=@@session.autocommit;
SET autocommit=1;
START TRANSACTION;
UPDATE sop_stock SET quantity=quantity-1 WHERE id=1;B:
sql
USE kb_database_lab;
SET @sop_b_autocommit=@@session.autocommit;
SET @sop_old_timeout=@@session.innodb_lock_wait_timeout;
SET autocommit=1;
SET SESSION innodb_lock_wait_timeout=30;
START TRANSACTION;
SELECT id,quantity FROM sop_stock WHERE tenant_id=1 AND sku='A' FOR UPDATE;观察前先确认 B 正在等待;随后由 A 在超时前执行 ROLLBACK,撤销库存修改并释放锁。B 才能继续返回已提交的库存 10。若 B 已报锁等待超时错误 1205,仍需在 B 执行整体 ROLLBACK;这条分支不满足第 4 步的预期观察。B 最后执行:
sql
ROLLBACK;
SET SESSION innodb_lock_wait_timeout=@sop_old_timeout;
SELECT id,quantity FROM sop_stock ORDER BY id;
SET autocommit=@sop_b_autocommit;各连接设置块都在主动结束旧事务后执行。A 释放本例锁后,在无未完成事务时恢复 SET autocommit=@sop_a_autocommit;;初始化连接仅恢复自己保存的 @sop_init_autocommit。若同一物理连接兼作多个角色,应先记录一次原值并按角色操作结束后恢复,不反复保存已经修改过的值。重连后重新准备;C 观察账户需相关视图权限,不要求初读具备这些权限。
在持锁期间,另一个普通一致性 SELECT 可不等该记录锁并返回已提交库存。一致性读通过数据版本读取可见结果,具体读取版本与隔离级别见 18。因此,不能把 B 的锁定读行为直接套到普通报表查询上;这个实验只说明“平时访问一行很快”不能排除现场锁等待。
5. 分支排查:由证据决定下一步
先判断主要耗时属于连接获取、数据库执行或等待、结果消费中的哪一段,再选择下表对应分支。estimated 是计划估算,actual 是实际执行时观察到的数据,loops 是算子执行次数。表关联可能让一行输入匹配出多行,重复执行的内层算子也可能放大总工作量,因此最终结果少不代表中间处理量少。
MDL(元数据锁)协调表结构等元数据的使用与变更;filesort 是 MySQL 排序机制的名称,不能仅凭名称判断是否落盘;mmap 是内存映射文件。Keyset 分页按上一页的排序键继续查询,区别于 OFFSET 跳过前面的候选行。具体机制和条件可沿表中链接展开。
| 已确认的证据 | 最小假设 | 验证方向 |
|---|---|---|
| acquire 时间大、池 pending(等待借连接的任务数)高 | 连接持有过久、容量不足或连接泄漏 | 23串联借出与归还,避免先改索引 |
| 同对象锁/MDL 等待链 | 长事务、批任务或热点 | 19找根持有者,确认提交/回滚边界 |
| actual 输入行数或 loops 很大 | 表关联放大行数、范围过宽或重复访问 | 05、12核对关联粒度和访问路径 |
| estimated 与 actual 偏差大 | 参数对应的数据量差异、字段相关性或统计信息问题 | 13对照慢/快参数,避免缺少证据就对全库执行 ANALYZE |
| 临时文件/资源随报表并发增长 | 排序、聚合或物化压力 | 15分清 filesort、mmap 与磁盘临时表 |
| 根算子(iterator)或首行返回快,全部结果消费慢 | 大结果、传输或应用对象转换 | 01拆分计时边界,评估限列/限行是否符合业务语义 |
| 深页越读越慢 | OFFSET 候选跳过 | 08比较延迟关联/Keyset 与一致性需求 |
普通 EXPLAIN 对本文 SELECT 不执行查询来取得结果,但仍可能解析、优化和访问元数据,因此也有开销与等待风险。EXPLAIN ANALYZE 会实际执行查询并采集运行信息,只能在隔离环境或已批准的执行与观测预算内运行经过评估的查询。它能补充当前执行证据,却不能证明事故时使用了同一计划;历史计划缺失时,应限制根因结论的范围。
6. 慢日志:补充来源,不是完整请求追踪
先读配置而非立即开启全量记录:
sql
SHOW VARIABLES WHERE Variable_name IN (
'slow_query_log','long_query_time','min_examined_row_limit',
'log_queries_not_using_indexes','log_output'
);MySQL 官方说明初始锁获取时间不计入该执行时间,慢日志是在语句完成并释放相应锁后写出,顺序不一定等于开始顺序;不能把日志顺序当事务因果顺序,也不能扩大解释为所有 InnoDB 行锁等待都不计。Query_time/Lock_time 与客户端全链路不是同一边界,具体锁类型要由现场等待证据解释。
long_query_time 是慢查询时间阈值,min_examined_row_limit 是检查行数的最低门槛;它们与“不使用索引也记录”等额外规则共同决定日志覆盖。未记录不等于没有慢请求,仍在执行的语句也可能尚未写出完整日志。全局/会话设置、既有连接继承和文件/表输出模式需按版本核验;临时调整必须保存原值,并评估敏感 SQL、日志读写开销和保留期限。
Performance Schema 的历史事件记录容量有限,可能覆盖旧事件;摘要则累计多个事件的指标。累计次数和总耗时需按 24在同一统计生命周期内取两次采样差值,才能观察当前窗口。分位数不能用同样方式简单相减。不要用 TRUNCATE 清空全局摘要来制造“干净现场”,否则监控会失去连续性。
7. 最小改动与验收:明确证伪条件
最小改动不是只追求代码改得少,而是一次只改变一个关键因素,让结果能够检验假设。若同时改 SQL、加索引又扩大连接池,即使变快,也难以判断哪个动作有效、哪个增加了代价。每个方案先填写:
text
假设:哪部分工作/等待导致 SLO 违约,证据是什么?
改动:SQL、索引、统计、事务、并发或负载迁移中的哪一个?
正确性:完整结果、NULL、无匹配、重复键、并列和并发边界如何对比?
负载:相同参数分布、计划到达率、完成率、冷热状态及版本是什么?
收益:P99、错误、队列和工作量改善,代价落到哪些写入/空间/副本?
证伪:哪些指标不变/变坏意味着假设不成立?
回退:负责人、动作、期限和恢复验证;未知提交不盲目重放。先验收正确性,再比较性能。对同一静态数据集,按完整业务键对比每行、字段值、NULL 和重复次数;两组结果总和相同,仍可能有漏行、错行或重复,不能证明等价。更新/删除实验采用 09的提交前断言和受控原值协议,不在生产直接以实际执行计划重跑写入。
性能回归同时覆盖热门/稀有参数、空结果、日期边界、分页深度和并发写。到达率是单位时间进入目标环节的请求数,完成率是单位时间实际完成的请求数。闭环压测会等前次请求完成后再发下一次;响应变慢时,发送量可能随之下降。因此,完成量下降或输入变少不能当作优化收益,失败和超时也要纳入统计。
收益还要与代价一起评估:新增索引增加写入维护、缓存占用和 DDL(表结构变更)成本;更新统计可能改变其他 SQL 的计划;迁移副本涉及 27的读一致性;预聚合把部分查询工作提前到数据维护阶段,需按 04设计维护与重算协议。
8. 灰度、停止条件与闭环
灰度是先向限定流量或租户发布改动,在固定观察窗口内检查收益与风险。发布前明确停止线:错误率、P99、pending、锁等待、临时空间或复制延迟达到什么条件就撤回或暂停。阈值由正常基线和业务 SLO 决定,不能套用固定的万能数值。
回退 SQL/参数/索引也可能需要 DDL/MDL 或重建统计,不等于“立即恢复”。保留上线前后计划、参数分布和资源,以同到达率比较;恢复暂停任务后继续观测,避免将压力转移当作治愈。产出复盘必须包含证据缺口、根因与促成条件、改动代价、告警和回归项。
清理教学会话先由 A/B 明确 ROLLBACK,再删除隔离库 sop_stock;C 仅保存受控证据。工具风险见 26,大范围数据事故恢复见 28。
参考资料
- MySQL 8.4 Reference Manual:The Slow Query Log、EXPLAIN Statement、Performance Schema Statement Event Tables、InnoDB Locking Reads。
- 适用条件:全部 SQL 输出、三会话等待、统计覆盖、生产收益与其他产品行为应通过实际证据核验。
9. 名词解释
| 名词 | 本文含义 |
|---|---|
| 证据账本 | 可复查的请求、参数、时间、事件和计划记录 |
| acquire | 应用等待并获取池连接的阶段,不是数据库语句执行 |
| 访问路径 | 数据库寻找目标行的方式,如唯一键查找或范围扫描 |
| estimated/actual | 计划估算/实际执行时观察,不应混为同一证据 |
| SLO/P99 | 服务目标/窗口内约 99% 样本不超过的时长,必须说明统计边界 |
| 单变量验证 | 一次只改一个关键因素,便于判断假设是否成立 |
| 灰度/回退 | 限定范围发布观察/异常时撤回改动,后者也可能有成本 |
| 证伪条件 | 哪些结果说明原假设不成立,不能只收集支持证据 |