数据库查询停顿是数据库性能问题中最棘手的一类,其核心特点是查询响应时间从毫秒级突然飙升至秒级甚至分钟级,且往往没有规律性的触发条件。查询停顿的直接原因可以归结为三类:锁竞争、资源等待和糟糕的执行计划,其中锁竞争占据超过一半的比例。据美团的《数据库故障分析白皮书》数据显示:约有58%的线上数据库性能事故与锁等待直接相关。下面我结合自己参与过的几起线上故障,给出从现象到根因的具体排查路径。
锁竞争是查询停顿最常见的起因,但锁与锁之间差异极大。表锁阻塞整个表的并发访问,行锁只阻塞特定行,而间隙锁则用于可重复读隔离级别下防止幻读。在排查查询停顿之前,先判断当前数据库会话的锁等待类型,能节省大量时间。
以MySQL的InnoDB引擎为例,行锁和间隙锁都通过information_schema库中的INNODB_TRX和INNODB_LOCK_WAITS表查看。
SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread_id, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread_id, b.trx_query AS blocking_query FROM information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;
执行这条SQL后,如果返回结果非空,说明存在锁等待。阻塞线程ID对应的会话就是造成查询停顿的源头。此时用SHOW ENGINE INNODB STATUS查看LATEST DETECTED DEADLOCK部分,可以进一步确认锁的持有者与等待者之间的具体SQL语句。
当锁等待排除后,查询停顿往往来自硬件资源的不够用。分析资源等待时,优先关注两个指标:磁盘IOPS利用率和CPU的上下文切换次数。磁盘IOPS打满会导致每次磁盘读取排队,CPU上下文切换过高则意味着线程频繁被挂起和恢复,二者都会造成查询响应时间出现锯齿状抖动。
在Linux服务器上,使用iostat -x 1持续观察%util和await字段。
iostat -x 1 5 # 输出示例 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await r_await w_await svctm %util vda 0.00 0.00 0.00 112.00 0.00 1792.00 16.00 0.02 0.18 0.00 0.18 0.18 2.00 vdb 0.00 78.00 0.00 1042.00 0.00 128512.00 246.15 0.20 0.35 0.00 0.35 0.34 35.60
如果await持续超过20毫秒而svctm只有1毫秒左右,说明IO队列深度过大,即磁盘本身性能不够或SQL产生了大量随机读写。另一个容易忽略的点是临时表落盘:当ORDER BY或GROUP BY处理的数据量超过tmp_table_size和max_heap_table_size时,MySQL会创建磁盘临时表,直接拉高磁盘IO。查看慢查询日志中是否有Created_tmp_disk_tables计数增长,就能快速确认。
查询停顿的第三个高频根因是执行计划突然变得很差。同一个查询昨天还走索引,今天却全表扫描,这种变化通常指向统计信息不够准确。优化器选择索引的依据是基数估计,如果表的统计信息长时间未更新,优化器会低估某个过滤条件的区分度,从而放弃索引。
通过EXPLAIN ANALYZE命令可以精确看到每一步执行耗时。
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345 AND order_status = 'PAID' ORDER BY created_at DESC LIMIT 10;
输出结果中重点观察actual time与rows的比例。如果预估扫描行数为1000,但实际扫描了100万行,说明基数估算严重偏离。解决方式是执行ANALYZE TABLE orders更新统计信息。但如果统计信息更新后执行计划仍然劣化,则需要在SQL层面强制指定索引。
SELECT * FROM orders FORCE INDEX (idx_customer_status) WHERE customer_id = 12345 AND order_status = 'PAID' ORDER BY created_at DESC LIMIT 10;
强制索引是临时手段,根治方案是调整查询结构或重建更匹配的联合索引。从长期看,建议对高频写表设置innodb_stats_auto_recalc为ON,并在业务低峰期定期执行ANALYZE TABLE。
下面这张表格汇总了查询停顿三种根因的典型特征、诊断工具和快速验证方法,方便在故障时对照使用。
| 根因类别 | 典型现象 | 诊断工具 | 快速验证 |
|---|---|---|---|
| 锁竞争 | 慢查询日志中同一SQL大面积堆积,且线程状态为Waiting for lock | information_schema.innodb_trx | 查看是否有长事务未提交 |
| 资源等待 | 磁盘await持续高,CPU iowait超过30%,TPS下降 | iostat、perf | 执行简单查询如SELECT 1是否也变慢 |
| 执行计划劣化 | 同一SQL平时快,特定参数后突然慢,EXPLAIN显示全表扫描 | EXPLAIN ANALYZE、slow log | 重新ANALYZE TABLE后是否恢复 |
需要注意的是,这三种根因常常相互叠加。比如长事务持有锁,导致其他查询阻塞,进而堆积线程,最终CPU和IO资源耗尽。此时先处理锁事务,资源问题通常会随之缓解。
在这里分享一次真实处理过的案例。某个订单服务在白天高峰期突发查询停顿,接口平均响应时间从80毫秒涨到3.5秒,持续了约10分钟。我先抓取慢查询日志,发现大量SELECT * FROM order_item WHERE order_id = ?执行时间超过2秒,而订单表只有几百万行,索引也正常。
随即查询information_schema.innodb_trx,发现有一个事务已经运行了18分钟,该事务执行了一条UPDATE order_item SET status = ? WHERE order_id = ?,但没有提交。由于order_item表在order_id有非唯一索引,InnoDB对相同索引键的行会施加间隙锁,导致后续所有针对同一订单ID的查询全部阻塞。
处理过程分三步:第一步定位阻塞事务的线程,第二步向业务方确认该事务是否可以回滚,第三步在确认后执行KILL 12345杀掉该事务。查询停顿在1秒内恢复。后续整改措施是将该事务从脚本中移除,并建议在业务代码中为所有修改操作设置事务超时时间,避免事务长时间不提交。
查询停顿无法完全消除,但可以通过合理的配置和规范将发生概率降到最低。以下四条建议既能减少锁竞争,也能降低资源等待风险。
innodb_lock_wait_timeout为5秒以内,让锁等待快速失败而不是无限阻塞。另外建议定期执行pt-query-digest分析慢查询日志,它能把相似SQL聚合并计算平均耗时、方差以及样本数。从统计角度看,查询停顿往往呈周期性,比如每周一数据同步任务启动时出现,通过历史数据对比能提前发现潜在风险。
查询停顿的排查本质上是对数据库内部状态进行证据链分析。锁竞争、资源等待、执行计划劣化三者之间互相影响,但每个根因都有明确的特征信号。我推荐DBA或后端开发者维护一份《查询停顿排查手册》,把每次故障时的现象、诊断输出、处理命令和最终原因记录成条目。据阿里云数据库团队的实践数据显示:有手册的团队处理同类查询停顿故障的平均时间从45分钟缩短到12分钟。持续积累这些一手信息,才是防止同类问题反复发生的根本。
评论
加载中...