监控告警在晚上 9 点响了:订单列表接口 P99 从 30ms 涨到 3.2 秒,但没有报错。运维第一反应是「数据库压力大」,DBA 看了一眼,CPU 只有 30%,连接数正常。开发说「这条 SQL 白天测过,很快」。三个人各说各的,谁都没说错,但谁也没找到原因。
慢查询排查最怕一上来就改 SQL、加索引。先要分清「慢在哪一层」,否则容易在错误的方向上花几个小时。这篇按线上排查的真实顺序走:先看正在发生什么,再看历史统计,最后决定止血还是根治。
场景走查:白天快、晚上慢
这个接口的 SQL 是「按状态筛订单 + 按时间倒序 + 分页」。白天数据量小、缓存命中高,走索引很快;晚上 8 点到 10 点是下单高峰,同时还有对账批处理、备份任务、报表聚合在跑。可能的解释至少有四种:数据量增长导致计划变化、批处理占满 IO、锁冲突让查询排队、连接池被慢请求占满。
每一种的处理方式完全不同。加索引只对第一种有效,对锁冲突甚至可能更糟(索引变多,写入更慢)。所以第一步不是改,是观测。
四类「慢」,症状不一样
区分方法很直接:看数据库里的「语句执行时间」和「应用侧接口耗时」是否一致。如果数据库显示每条 SQL 都只要 1ms,接口却要 3 秒,问题多半在应用层或连接层;如果数据库里就有大量超过 1 秒的语句,再看是扫描慢还是等锁。
等锁和扫描慢的区别也能看出来:等锁的语句 EXPLAIN 往往很好,rows 很小,但实际耗时很长;扫描慢的语句 rows 很大,Extra 里有 Using filesort 或 Using temporary。
还有一个省事的判别方法:看同一条 SQL 在白天的表现。白天快、高峰期慢,优先怀疑锁和资源争抢;任何时候都慢,且耗时随数据量增长,优先怀疑执行计划和扫描量。这个对照能省掉很多盲目试错。
慢日志:最基础也最可靠
慢日志记录超过阈值的语句。先看当前配置:
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';
本机实测:slow_query_log=OFF、long_query_time=10、log_output=FILE。也就是说默认只记录超过 10 秒的语句,很多「3 秒」的慢查询根本不会进日志。线上通常调到 1 秒或更低,但要注意日志量和 IO。
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_output = 'FILE';
这些是全局变量,重启后可能失效,要写进配置文件。log_output 也可以设为 TABLE,把慢日志写进 mysql.slow_log 表,方便直接查询,但高频写入会增加数据库负担,生产上更多还是写文件。
还有两个开关容易被忽略。log_queries_not_using_indexes=ON 会把所有没走索引的语句都记进慢日志,哪怕它只跑了 1 毫秒,日志量会瞬间暴涨,通常只在短时间排查时打开;min_examined_row_limit 可以过滤「扫描行数低于阈值」的语句,避免把小而快的查询混进来。另外慢日志必须配轮转(logrotate 或定期执行 FLUSH SLOW LOGS),否则磁盘写满会比慢查询先出事。
日志文件可以用 mysqldumpslow 汇总,按出现次数、总耗时、平均耗时排序:
mysqldumpslow -s at -t 20 /var/log/mysql/slow.log
它会把参数归一化成 ?,把同类 SQL 聚在一起。先看「总耗时最高」和「平均最慢」两类,前者影响面大,后者单条最坑。
慢日志的单条记录长这样(本机把 long_query_time 临时调到 0.5 秒后实测):
# Time: 2026-11-06T10:43:07.909949Z
# User@Host: root[root] @ localhost [127.0.0.1] Id: 343
# Query_time: 0.814109 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1793961787;
SELECT SLEEP(0.8);
Query_time 是总耗时,Lock_time 是等锁耗时,Rows_sent 是返回行数,Rows_examined 是扫描行数。四个数字能快速定方向:Lock_time 占比高是锁问题;Rows_examined 远大于 Rows_sent 是扫描太多;两者都不高但 Query_time 很长,才轮到网络、临时表落盘或函数计算。
这条样例还揭示了一个容易误判的点:Rows_examined=0 并不代表查询没问题。SLEEP(0.8) 一行数据都没碰,照样被记成 0.81 秒的慢查询。所以看慢日志不能只盯 Rows_examined,要把 Query_time 和业务语义一起看。同理,一条语句扫描了 10 万行但只用了 20ms,不会进慢日志,可它对 IO 和缓冲池的消耗是实打实的——慢日志抓的是「单次超过阈值」,抓不到「高频小慢」。
performance_schema:不用开日志也能看
MySQL 默认开启 performance_schema,它按「语句摘要」聚合执行次数、总耗时、平均耗时、扫描行数。实测查询:
SELECT SCHEMA_NAME,
LEFT(DIGEST_TEXT, 80) AS digest,
COUNT_STAR,
ROUND(SUM_TIMER_WAIT/1000000, 2) AS total_ms,
ROUND(AVG_TIMER_WAIT/1000000, 2) AS avg_ms,
SUM_ROWS_EXAMINED AS rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
本机输出里,一条 UPDATE stock SET qty = qty - ? WHERE id = ? 的平均耗时高得离谱。原因不是这条 UPDATE 本身慢,而是它有一次在等锁,等待时间被计入了语句总耗时。这一点很重要:PFS 的 TIMER_WAIT 包含等锁时间,不能直接当成 CPU 执行时间。看到异常高的平均值,先怀疑锁,而不是 SQL 写法。
sys 库在 PFS 之上做了视图封装,更好读:
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;
SELECT * FROM sys.schema_table_statistics ORDER BY total_latency DESC LIMIT 10;
要看单次执行的历史,可以开 events_statements_history_long,但它占内存,线上默认通常不开。排查时临时开一小会儿,查完关掉。
digest 的作用是把「参数不同、结构相同」的 SQL 合并成一条。WHERE id = 1 和 WHERE id = 99 归成同一个 WHERE id = ?,DIGEST_TEXT 里用问号代替。这样看的是「这类 SQL 的整体成本」,不是某一次执行。副作用是常量被抹掉了,想按具体参数排查,就得看 events_statements_history_long 或慢日志原文。PFS 采集本身有开销,默认只保留部分表,全量打开会多占内存和 CPU,量大的库要评估之后再开。
翻车现场:一条 895 ms 的 JOIN,慢和错是同一个原因
某张报表要算「各状态订单金额的交叉汇总」,开发写了两张订单表自连接:
SELECT SUM(a.amount * b.amount)
FROM orders a
JOIN orders b ON a.status = b.status;
PFS 里这条语句的摘要是 COUNT_STAR=1、AVG_TIMER_WAIT=895.104 ms,而同一时段一条正常的「订单 + 明细」聚合只要 7.569 ms。慢只是表象,更严重的是结果:SUM(a.amount * b.amount) 把金额乘了金额,算出来的数字没有任何业务含义。慢和错来自同一个写法——用低区分度的 status 做连接键,笛卡尔积把 1998 行放大成几十万对,再逐对相乘。
为什么是几十万对?status 只有五种,每种约 400 行,同一状态内任意两行都会配对,单组就有 400×400 = 16 万对,五组合计约 80 万对。即使优化器用了索引,配对数量本身也不会少,只是省掉了一些回表。PFS 的 895 ms 主要花在配对和乘法上。
正确写法是先分组聚合,再按状态关联:
SELECT a.status, SUM(a.amount) AS total, COUNT(*) AS cnt
FROM orders a
GROUP BY a.status;
如果确实要两个维度的交叉,就把两边的聚合结果各算一次再 JOIN,参与连接的行数从几十万降到五行。这里有一条通用判断:JOIN 的右侧如果按非唯一列匹配,结果行数会按倍数膨胀,一对多还好,多对多就是乘积。看到 JOIN 之后 Rows_examined 暴涨,或者金额、数量出现「比预期大很多倍」,先怀疑连接键不唯一。
现在正在发生什么
历史统计告诉你「哪条慢」,实时视图告诉你「现在卡在哪」。三个常用入口:
SHOW FULL PROCESSLIST;
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id,
trx_query, trx_rows_locked, trx_rows_modified
FROM information_schema.innodb_trx
ORDER BY trx_started;
SELECT * FROM performance_schema.data_lock_waits;
SHOW PROCESSLIST 看连接在干什么,State 里出现 Waiting for ... lock 就是锁等待;innodb_trx 找运行时间最长、锁行数最多的事务;data_lock_waits 直接给出「谁在等谁」。MySQL 8.0 之前查锁要用 information_schema.innodb_locks,8.0 改成了 performance_schema.data_locks 系列。
如果发现某个事务开了十分钟还没提交,先看它的 trx_query 和发起连接。很多时候是代码里忘了 COMMIT,或者事务里包了一个慢查询/网络调用。
State 列的几个常见值值得记住:Sending data 其实在读数据并发送,耗时长多半是扫描多或回表多;Sorting result 在做额外排序;Creating sort index 是排序结果落盘;Waiting for table metadata lock 是在等元数据锁,常见于 DDL 没结束;Waiting for ... lock 才是行锁等待。看到 Sleep 不代表没问题,空闲连接可能正持有一个没提交的事务,所以还要配合 innodb_trx 判断。
止血和根治,顺序不能反
线上正在故障时,第一目标是恢复服务,不是找到完美方案。常见止血手段和风险:
| 手段 | 适用 | 风险 |
|---|---|---|
KILL 掉异常查询/连接 | 单条 SQL 卡死、长事务 | 可能中断业务请求,需确认不是关键任务 |
| 临时提高连接数/超时 | 连接池排队 | 把压力转移到数据库,可能雪崩 |
| 加缓存/限流/降级 | 读多写少、非核心功能 | 数据可能短暂不一致,需有开关 |
| 下线或回滚变更 | 刚发布的 SQL / 索引变更 | 回滚也有成本,要先确认变更点 |
| 在线加索引 | 确认是全表扫描导致 | 大表耗时长、占 IO,可能加剧当前故障 |
止血之后再做根治:改 SQL、补索引、拆批处理、调隔离级别、加缓存、归档历史数据。每一条都要有验证方案,别在生产上直接试。
止血时还要保留现场:把当时的 SHOW FULL PROCESSLIST、innodb_trx、data_lock_waits 输出和执行计划都存下来,再动手 KILL。否则服务恢复了,根因也跟着消失,下次同样的故障还得从头查。变更也一样,记录时间点、执行的语句、影响行数,方便和监控曲线对齐。
为什么同一个查询白天快晚上慢
这不是玄学,常见原因有一串:晚上备份任务占满磁盘 IO;对账/报表批处理抢 CPU 和 Buffer Pool;数据量在一天内增长,统计信息没更新导致计划变化;缓存命中率下降,回表从内存变成磁盘;锁冲突集中在高峰时段;连接池被慢请求占满,后来的请求排队;甚至机房网络在晚高峰抖动。
排查时把这些当成假设逐个排除:看监控的 IO/CPU、看备份任务时间、看 EXPLAIN 是否和白天不同、看 Buffer Pool 命中率、看锁等待、看连接池指标。不要一上来就认定是「数据库性能不行」。
验证缓存假设有个很直接的办法:SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%',用 Innodb_buffer_pool_reads(真正读磁盘的次数)除以 Innodb_buffer_pool_read_requests(逻辑读次数),得到物理读比例。白天这个比例可能是 0.1%,晚上批处理把缓存冲掉后升到 5% 以上,同样的回表就从内存变成了磁盘。再配合 SHOW ENGINE INNODB STATUS 里 BUFFER POOL AND MEMORY 段的命中率变化,基本能确认是不是缓存问题,而不是继续在 SQL 上改来改去。
还有一个经常被忽略的变量:统计信息更新。MySQL 的统计信息不是实时的,大批量写入后 rows 估算可能严重偏离,优化器据此选错索引。晚上批处理刚跑完、数据分布刚变化,计划可能和白天完全不同。把 EXPLAIN 的输出按时间点存下来对比,比凭印象更可靠。
上线前怎么防
慢查询最好的处理是别让它上线。几条可执行的措施:SQL 上线前必须附 EXPLAIN,重点关注 type=ALL/index、rows 过大、Using filesort、Using temporary;新索引在大表上先评估耗时;接口压测要覆盖数据量增长后的场景;慢日志阈值长期保持在 1 秒以内并接入告警;对高频 SQL 做定期巡检。
还有一条容易忽略:给数据库操作加指标。每条 SQL 的耗时、返回行数、错误码都打点,出问题时才能区分「这条 SQL 慢」还是「这个接口调用了太多次」。
长期看,还要把慢查询治理变成例行工作:每周拉一次 sys.statement_analysis,按总耗时排序,挑前十条过一遍;对超过阈值的 SQL 建工单,记录当时的计划、数据量、改动和验证结果;新增 SQL 上线前评审,重点看有没有全表扫描、有没有深分页、有没有在循环里查库。这些动作比事后救火便宜得多。
现象对照
| 现象 | 真实原因 | 第一步查什么 |
|---|---|---|
| 接口慢但数据库语句耗时正常 | N+1 查询、连接池排队、网络 RTT | 数一次请求的 SQL 条数,看连接池等待指标 |
| 慢日志里全是同一条 SQL | 高频调用,或它一直在等锁 | COUNT_STAR 和 AVG_TIMER_WAIT 一起看,再查锁 |
| PFS 平均耗时异常高 | 等锁时间被计入语句耗时 | 查 innodb_trx 和 data_lock_waits |
| 白天快晚上慢 | 备份/批处理抢 IO,或缓存命中下降 | 对齐时间线看监控,确认批处理窗口 |
| 加了索引后整体更慢 | 索引太多拖慢写入,或优化器选了更差计划 | 对比变更前后的写入耗时和新计划的 rows |
| 重启后查询突然变快 | Buffer Pool 被重新预热,或统计信息刷新 | 看缓存命中率和 ANALYZE 时间 |
| PFS 里一条 JOIN 平均 895 ms,金额也大得离谱 | 连接键不唯一,笛卡尔积放大行数,聚合语义也错了 | 看 SUM_ROWS_EXAMINED,核对 JOIN 键是否唯一 |
| 某条 3 秒的 SQL 在慢日志里找不到 | long_query_time 还是默认 10 秒,或日志根本没开 | 查 slow_query_log、long_query_time、log_output |
边界与代价
慢日志和 PFS 都有成本。慢日志写文件占 IO,阈值太低会产生大量日志;PFS 常驻内存,采集项越多开销越大。线上要按需开启,不要为了「什么都能查」把所有采集都打开。
EXPLAIN ANALYZE 会真正执行 SQL,在 8.0.18+ 才有;对一条会扫描百万行的查询用它,等于主动制造一次慢查询。生产上优先用普通 EXPLAIN 和 PFS,确有必要再在低峰期做。如果你在 8.0.18 及以上,可以这样验证本文的一个结论:在只读从库上对深分页语句执行 EXPLAIN ANALYZE,每个节点会显示 actual time、actual rows、loops,其中 Sort 节点的 actual rows 应该接近扫描行数,而不是最终返回的 10 行。8.0.12 没有这个命令,本文的 PFS 和慢日志结论都来自 8.0.12,这段输出格式未在本机验证,标记为待确认。
监控指标不能代替理解。看到 CPU 高不代表 SQL 慢,可能是锁自旋、可能是压缩、可能是备份;看到 CPU 低也不代表没问题,可能在等 IO。指标是线索,不是结论。
慢阈值也别设得太激进。把 long_query_time 调到 0.1 秒,看起来「什么都能抓到」,实际会写入海量日志,磁盘和 IO 先被拖慢,真正的问题反而被淹没。常规做法是先用 1 到 2 秒抓到主要矛盾,需要精查时再临时调低,查完记得恢复原值。
小结
先分层:应用层、连接层、数据库层、系统层;再看两类证据:历史统计(慢日志、PFS)和实时状态(PROCESSLIST、innodb_trx、data_lock_waits);最后按「止血优先、根治随后」的顺序处理。看到慢 SQL 先问一句:它是在扫数据,还是在等锁?
当天可以自己验证的练习:
- 查本机的
slow_query_log、long_query_time、log_output三个变量。 - 用
events_statements_summary_by_digest找出执行次数最多和总耗时最高的三条语句。 - 开两个连接制造锁等待,用
innodb_trx找出等待方和持有方。 - 对比一条 SQL 的
EXPLAIN估算rows和实际返回行数。 - 给一条查询加索引前后各压测一次,记录 P99 和写入耗时,确认收益与代价。
下一篇讲表设计与范式:该拆还是该合。排查到最后,很多慢查询的根因不在 SQL,而在表结构:该拆的没拆、该冗余的乱冗余、JSON 字段当万能筐。最后一篇回到设计阶段,讲清楚什么时候该拆表、什么时候该合表。