线上慢SQL排查修复：从监控告警到索引优化的生产环境实战

线上慢SQL排查修复：从监控告警到索引优化的生产环境实战

在生产环境中遇到慢 SQL，危害是立竿见影的。它会直接导致应用吞吐量下降，响应延迟变大。更棘手的是，一个慢 SQL 可能会长时间占用数据库连接池，引发连锁反应，甚至因为 CPU 飙升而影响其他正常 SQL 的执行性能。很多人第一反应是使用 EXPLAIN 分析，但真实的线上故障处理远不止于此。本文将结合事前预防、事中应急和事后根治，系统性地探讨如何应对生产环境的慢 SQL 问题。

想要深入探讨后端架构与高并发设计，欢迎访问 云栈社区 的相关板块进行交流。

感知线上问题

监控是眼睛

想要发现问题，首先得“看得见”。对 SQL 执行时间的监控是重中之重，常见的方案有：

• 直接使用云厂商提供的云数据库服务，其内置的监控功能通常开箱即用。
• 自建监控体系，例如采用 Prometheus + Grafana + mysqld_exporter 的组合，并配合 MySQL 的慢查询日志配置。

除了 SQL 本身，对数据库服务器硬件资源的监控同样不可或缺，包括 CPU 使用率、内存、网络 I/O 和磁盘 I/O 等。这套完善的 运维/DevOps/SRE 监控体系能帮助我们把握系统负载，为扩容决策提供依据，并能在 CPU 异常飙升等风险出现时第一时间感知。

告警是耳朵

监控看到了异常，告警则需要及时通知我们。以下指标达到阈值时就应触发告警：

• SQL 执行时间：根据业务实际耗时设定，互联网业务通常可先设置为 1 秒。
• 硬件资源：尤其是 CPU 使用率。MySQL 在正常情况下 CPU 利用率通常不会持续过高，如果突然飙升到 80% 以上，极有可能就是慢 SQL 引发的。

及时止血

当告警响起，第一步不是分析，而是“止血”。需要对识别出的慢 SQL 进行限流或直接拉黑（Kill），目的是：

• 避免大量慢查询长时间占用连接，导致数据库吞吐量雪崩。
• 防止风险扩散，保障其他正常 SQL 的执行不受影响。

分析与解决

止血后，便是深入分析根因。我们可以从以下几个维度进行排查。

一条 SQL 慢，还是所有 SQL 都慢？

情况一：仅个别 SQL 慢
这是最常见的情况，可以从以下角度逐一排查：

1. 缺少索引：这是首要怀疑对象。检查 SQL 的 WHERE、ORDER BY、GROUP BY 或 JOIN 条件涉及的字段是否没有索引。不过，线上加索引需谨慎，可能会引发锁表，且不常用的索引会影响写入性能。
2. 索引未生效：明明加了索引，EXPLAIN 一看却没走。原因可能是索引失效（如对索引字段使用函数、LIKE 以 % 开头、违反最左前缀原则等），或是优化器基于统计信息误判，选择了全表扫描。
3. 深分页问题：SQL 带有 LIMIT 10000, 20 这类查询，偏移量 OFFSET 巨大。这会导致 MySQL 需要先扫描并丢弃大量数据，性能极差。
4. 查询数据量过大：例如做全量报表或历史记录查询时未加 LIMIT 限制，导致一次性拉取过多数据，引发全表扫描。
5. 文件排序（File Sort）：当 ORDER BY 或 GROUP BY 无法利用索引时，MySQL 可能需要在磁盘上创建临时表进行排序，消耗巨大。
6. 复杂的子查询或游标：某些复杂查询写法可能导致性能不佳，需要重构 SQL 甚至调整业务代码。从产品设计层面，也可以考虑用游标查询（如瀑布流）或限制查询页数来规避。

情况二：所有 SQL 都变慢
这通常意味着数据库实例的压力已超过其承载上限。

• 检查硬件资源：查看 CPU、内存、磁盘 I/O、网络带宽是否达到瓶颈，考虑对瓶颈资源进行升级。
• 架构升级：如果单实例性能到顶，则需要考虑分库分表、读写分离或直接扩容等更彻底的 后端 & 架构 解决方案。

深入理解 EXPLAIN

EXPLAIN 是分析单条 SQL 性能的利器，看懂其输出结果至关重要。几个关键字段：

• type：访问类型，性能从优到劣大致为：
  system > const > eq_ref > ref > range > index > ALL
ALL 表示全表扫描，是重点优化对象。
• possible_keys：查询可能使用的索引。
• key：查询实际使用的索引。如果为 NULL，说明未使用索引。
• key_len：使用的索引长度，对于联合索引，可判断使用了索引的几部分。
• rows：预估需要扫描的行数，值越小越好。
• filtered：查询条件过滤后剩余记录的百分比，值越大越好。
• Extra：额外信息，包含优化器的重要行为：
  • Using index：使用了覆盖索引，无需回表，性能佳。
  • Using index condition：使用了索引下推。
  • Using where：在 Server 层用 WHERE 条件再次过滤。
  • Using temporary：使用了临时表，常见于 GROUP BY、DISTINCT。
  • Using filesort：无法用索引排序，需文件排序，需优化。
  • Using index for skip scan：使用了索引跳跃扫描。

文件排序的典型场景与规避

文件排序通常发生在 ORDER BY 的字段与 WHERE 条件使用的索引不同时。例如：

SELECT * FROM orders WHERE created_at > ‘2023-01-01’ ORDER BY id DESC;

这里 created_at 有索引，但按 id 排序。尽管数据插入时 created_at 大的 id 通常也大，但 MySQL 优化器无法利用 created_at 索引的有序性来满足 ORDER BY id。

解决方案：

1. 将 ORDER BY id DESC 改为 ORDER BY created_at DESC（如果业务逻辑允许）。
2. 增加排序缓冲区大小，尽量让排序在内存中完成。
3. 放弃在数据库层面排序，将数据取到应用层（如 JVM）再进行排序。

写入慢，还是读取慢？

写入 SQL 慢
重点考察“锁”竞争。优化思路是减少锁持有时间、降低锁粒度。

• 避免大事务：事务越大，锁持有的时间就越长。尽量将大事务拆小。
• 调整 SQL 顺序：在事务内，先执行锁竞争小的 SQL，再执行锁竞争大的 SQL，可以略微减少后者的锁等待时间。

读取 SQL 慢
则回归到上述索引、分页、数据量、排序等问题进行排查。

扩展思考：事务隔离级别的影响
“读已提交（Read Committed）”级别通常比“可重复读（Repeatable Read）”性能更好，核心原因在于锁的粒度。“读已提交”一般只加行锁，而“可重复读”为了解决幻读，引入了间隙锁（Gap Lock）和临键锁（Next-Key Lock），锁定了范围，导致锁竞争更激烈。这本质上是数据库在“性能”和“数据一致性”之间做出的权衡。

大多数情况快，但偶尔会“卡顿”

这种间歇性慢查询可能的原因：

• 数据库连接池过小：并发请求稍高时，连接不够用，请求需要等待。适当调大连接池。
• 刷脏页（Flush）：当内存中的脏页需要刷到磁盘（如 Buffer Pool 刷盘、Redo Log 刷盘、Binlog 刷盘）时，如果 IO 繁忙，可能会阻塞用户查询。
  • 优化：可以尝试增大 InnoDB Buffer Pool 大小，或调整 Redo Log 和 Binlog 的刷盘策略。

关于“双1”刷盘策略
为了保证持久性，默认配置下（innodb_flush_log_at_trx_commit=1 且 sync_binlog=1），每次事务提交都会触发两次磁盘 IO，容易成为 IO 瓶颈。为了性能，可以调整：

• innodb_flush_log_at_trx_commit=2：Redo Log 只写入操作系统缓存，由系统决定刷盘。
• sync_binlog=N（N 设为 500 或 1000）：累积 N 个事务后再统一刷 Binlog 到磁盘。
  注意：调整后会降低数据的安全性，部署数据库的服务器必须配备 UPS（不间断电源），以防止断电导致数据丢失。

无法避免的慢 SQL

有些场景，慢 SQL 是“必要之恶”。例如运营临时拉取大量数据做分析、生成 T+1 报表等。这类查询数据量巨大，必然慢，但执行频率极低。应对策略如下：

• 结果缓存：对查询结果进行缓存，虽然命中率可能因条件多变而不高，但仍有价值。
• 应用层聚合：改为简单的单表查询，在应用层进行数据关联与计算。需警惕数据量过大导致的应用层 OOM。
• 专用从库：搭建一个只读从库，专门用于执行这类分析型查询，实现读写分离，不影响主库业务。
• 延迟执行：将查询任务提交到队列，安排在数据库低谷期（如凌晨）执行。
  对于这类“合法”的慢 SQL，务必将其加入监控的白名单，避免产生无谓的告警干扰。

总结

处理生产环境慢 SQL 是一个系统工程：

1. 事前监控：建立完善的 SQL 与硬件监控、告警体系。
2. 事中止血：发现问题后，优先限流或 Kill 慢查询，防止故障扩大。
3. 事后根因分析与解决：按“个别慢还是全部慢”、“读慢还是写慢”、“偶发还是持续”的路径深入分析。实践中，大部分慢 SQL 的根源还是索引问题（没加或失效）和深分页，可以从这两点优先切入。

通过这套从预警到根治的完整流程，我们才能游刃有余地应对线上数据库的性能挑战，而不仅仅依赖于 EXPLAIN 这一项工具。