线上SQL性能突降排查指南：从CPU飙升到执行计划突变的完整路径

这类线上 SQL 性能突然劣化的问题是后端和 DBA 每天都要面对的典型故障。它不像那种一直很慢的 SQL可以慢慢优化而是昨天还好好的今天就“暴雷”直接导致数据库 CPU 飙升到 90%服务响应变慢甚至超时。处理这种问题不能上来就改 SQL 或加索引必须先有一套清晰的排查路径快速定位根因。最核心的思路是从现象倒推先看外部变化再看内部状态最后锁定具体 SQL 和它的执行计划。整个过程要像侦探破案排除各种可能性。下面我按实际线上排查的顺序拆解一遍完整的流程和每个环节要看的重点。1. 第一步确认现象与收集基础信息避免误判接到报警说“SQL 变慢CPU 高”第一反应不是立刻登录数据库而是先尽可能把问题框定清楚。很多“性能劣化”其实是误报或者问题根源不在数据库本身。1.1 确认“慢”和“CPU高”是否真实且关联首先要核实几个基本事实慢的标准是什么是应用监控如 APM发现接口超时还是数据库慢日志里出现了这条 SQL从 50 毫秒到 5 秒这个数据来源要确认。最好能拿到具体的慢日志记录或 APM 链路追踪的截图上面会有精确的执行时间。CPU 90% 是持续还是瞬时通过监控系统如 Zabbix, Prometheus Grafana查看数据库主机过去 24 小时的 CPU 使用率图表。是突然飙升到 90% 并持续高位还是间歇性尖峰如果只是瞬时高峰可能伴随有批量任务如果是持续高位那问题更严重。两者是否同时发生将 SQL 变慢的时间点和 CPU 飙升的时间点在监控图上对齐。如果 CPU 先高SQL 后慢可能是其他进程拖累了数据库如果 SQL 慢的时间点和 CPU 飙升曲线高度吻合那关联性就非常强。关键动作立即保存故障时间点前后至少 1 小时的各项监控图表CPU、内存、IO、网络、数据库连接数、QPS、TPS。这是后续分析的基线。1.2 排查数据库外部因素在深入数据库之前先快速排除外部干扰。这些因素常常被忽略但能最快解决问题。应用层发布检查故障时间点前后是否有应用版本发布新的代码可能改变了 SQL 的传参如多了一个null值、调用次数循环内误调用、或者事务范围。流量变化是正常业务高峰还是突发的营销活动、爬虫流量查看 QPS 图表。流量上涨本身就会导致资源紧张但结合“单条 SQL 从 50ms 到 5s”更可能是 SQL 本身执行计划变了。资源竞争同一台主机上是否部署了其他突然活跃的服务抢占了 CPU、内存或磁盘 IO检查主机整体监控。网络问题对于分布式数据库或读写分离架构应用连接的是否是正确的、健康的数据库节点是否存在网络延迟或丢包注意如果外部因素排查完毕如无发布、流量平稳那么问题焦点就必须收缩到数据库内部和这条 SQL 本身。2. 第二步深入数据库内部定位问题 SQL 与会话锁定是数据库内部问题后我们需要进入数据库找到正在“作恶”的会话和具体的 SQL 语句。2.1 使用系统视图定位高负载会话登录数据库后不要急着去查那条已知的慢 SQL先看全局。因为可能不止那一条变慢了。对于 MySQL-- 查看当前正在执行的会话按消耗时间或资源排序 SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND ! Sleep ORDER BY TIME DESC; -- 或使用 performance_schema更详细 SELECT ps.*, es.* FROM performance_schema.threads ps INNER JOIN performance_schema.events_statements_current es ON ps.THREAD_ID es.THREAD_ID WHERE es.SQL_TEXT IS NOT NULL ORDER BY es.TIMER_WAIT DESC LIMIT 10; -- 查看当前哪些SQL消耗了最多的CPU通过执行时间间接判断 SHOW FULL PROCESSLIST;重点关注TIME值大、STATE处于Sending data、Sorting result、Creating sort index等操作状态的会话。记录下它们的Id。对于 PostgreSQLSELECT pid, usename, application_name, client_addr, backend_start, state, query, query_start FROM pg_stat_activity WHERE state ! idle ORDER BY query_start ASC;对于 SQL Server-- 查看当前开销高的查询 SELECT TOP 10 s.session_id, r.status, r.cpu_time, r.logical_reads, r.writes, SUBSTRING(st.text, (r.statement_start_offset/2)1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2)1) AS statement_text FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_requests AS r ON s.session_id r.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.status NOT IN (background, sleeping) ORDER BY r.cpu_time DESC;2.2 捕获问题 SQL 的完整执行上下文找到疑似会话后需要获取完整的 SQL 文本和它的执行计划。光有SHOW PROCESSLIST里截断的 SQL 可能不够。获取完整 SQL通过上述查询找到SQL_TEXT或query字段。如果被截断可能需要结合应用日志或 APM 工具根据其参数特征如特定的user_id,order_no来定位。查看执行计划最关键的一步将抓取到的 SQL 语句带上具体的参数值特别是故障发生时使用的参数手动执行EXPLAIN或EXPLAIN ANALYZE。-- MySQL EXPLAIN FORMATJSON SELECT * FROM your_table WHERE ...; -- 或者使用 optimizer trace更深入 SET SESSION optimizer_traceenabledon; SELECT * FROM your_table WHERE ...; SELECT * FROM information_schema.OPTIMIZER_TRACE; SET SESSION optimizer_traceenabledoff; -- PostgreSQL EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM your_table WHERE ...; -- SQL Server SET STATISTICS PROFILE ON; -- 然后执行你的SQL SET STATISTICS PROFILE OFF;重点对比将今天抓到的慢 SQL 的执行计划与昨天正常时如果历史执行计划有留存或你预估的正常执行计划进行对比。差异点就是突破口。3. 第三步分析执行计划突变的原因执行计划变了是 SQL 性能突然劣化的最常见原因。为什么计划会变主要有以下几个方向。3.1 统计信息过时或不准这是导致执行计划突变的头号嫌疑犯。数据库优化器依赖表和索引的统计信息如数据行数、唯一值数量、数据分布直方图来选择“最优”路径。如果统计信息很久没更新或者自动更新失败优化器就会基于错误的数据做出判断。如何检查MySQL为例-- 查看表的最后统计信息更新时间 SELECT TABLE_NAME, UPDATE_TIME FROM information_schema.TABLES WHERE TABLE_SCHEMA your_db AND TABLE_NAME your_table; -- 如果UPDATE_TIME很久远或者表数据量变化很大如从10万行激增到1000万行统计信息很可能不准。如何解决手动更新统计信息。-- MySQL ANALYZE TABLE your_table; -- PostgreSQL ANALYZE your_table; -- SQL Server UPDATE STATISTICS your_table;更新后立即重新执行慢 SQL 并检查性能是否恢复。如果恢复基本可以定案。3.2 索引失效或未被使用检查执行计划看预期应该使用的索引是否真的被用上了key字段。索引失效索引可能因为ALTER TABLE操作、存储引擎问题如 InnoDB 损坏或文件系统问题而损坏。使用CHECK TABLE命令检查表健康状态。索引未被使用如果执行计划显示typeALL全表扫描可能的原因有SQL 写法导致索引失效对索引列进行了函数操作如WHERE DATE(create_time) ...、隐式类型转换如字符串列用数字查询、或者使用了!、NOT IN、LIKE %xxx等。优化器认为全表扫描更快在统计信息不准的情况下优化器可能误判。强制使用索引FORCE INDEX可以验证但这不是长久之计。索引选择性太差如果索引列的值重复率极高如“性别”列优化器可能放弃使用索引。3.3 参数绑定与执行计划缓存问题对于支持执行计划缓存的数据如 SQL Server, OracleMySQL 8.0 也有一定缓存一个“坏”的计划被缓存后后续所有相同模式的 SQL 都会使用这个坏计划即使它不适合当前的参数值。参数嗅探Parameter Sniffing这是 SQL Server 的经典问题。第一次编译 SQL 时传入的参数值如user_id1数据很少生成了一个针对少量数据的计划。当后续传入一个数据量巨大的参数如user_id99999时数据库依然沿用旧计划导致性能灾难。如何排查清空计划缓存在测试环境或业务低峰期尝试清空相关缓存观察 SQL 是否恢复正常。-- SQL Server DBCC FREEPROCCACHE; -- MySQL 8.0 (重置performance_schema相关表) -- 更常见的是让SQL强制重新解析使用本地变量或优化器提示修改应用 SQL使用OPTION (RECOMPILE)SQL Server或WHERE column paramMySQL 存储过程等方式避免重用不合适的缓存计划。3.4 数据量突变与资源瓶颈数据量激增检查 SQL 涉及的表是否在故障点附近有大量数据写入如数据迁移、批量导入。这会导致统计信息瞬间过时并且物理 IO 成本剧增。锁竞争加剧高并发下如果这条 SQL 或相关事务持有了锁如行锁、表锁其他会话会被阻塞表现为执行时间变长、CPU 等待锁的时间增加。使用数据库的锁查看命令如 MySQL 的SHOW ENGINE INNODB STATUS关注LATEST DETECTED DEADLOCK和TRANSACTIONS部分。磁盘 IO 瓶颈监控显示 CPU 高但%iowait也可能很高。如果 SQL 需要做大型排序filesort或临时表而临时表被写到磁盘上会引发大量 IOCPU 在等待 IO利用率显示为“高”。检查执行计划中是否有Using temporary; Using filesort。4. 第四步系统性验证与根治方案找到疑似原因后不能直接在线上进行大刀阔斧的修改如删索引、改 SQL。需要制定验证和根治方案。4.1 在测试环境或从库上复现与验证数据准备将生产环境的数据或故障时间点的数据快照同步到测试环境。场景复现在测试环境执行故障 SQL使用相同的参数并捕获其执行计划。尝试更新统计信息、重建索引等操作再次执行 SQL对比性能变化。压力测试模拟生产环境的并发度观察修改后的 SQL 在高并发下是否稳定。4.2 制定并实施根治方案根据排查结果选择最合适的方案更新统计信息如果确定是统计信息问题可以调整数据库自动更新统计信息的策略如更低的阈值、更频繁的更新并在重大数据变更后手动更新。优化索引增加缺失的索引。修改现有索引的列顺序使其更符合查询条件。考虑使用覆盖索引索引包含所有查询字段来避免回表。删除冗余或从未使用过的索引。重写 SQL避免在WHERE和JOIN的列上使用函数。将隐式转换改为显式转换。拆分复杂 SQL或使用临时表/公共表表达式CTE分步处理。重写子查询为JOIN。使用优化器提示在极少数情况下当优化器始终无法选择最优计划时可以使用提示如FORCE INDEX,USE INDEX来引导。但这应是最后手段因为数据分布变化后提示可能反而有害。调整数据库参数例如增大排序缓冲区sort_buffer_size、连接缓冲区join_buffer_size或临时表大小以避免磁盘临时表。修改参数需谨慎并充分测试。应用架构优化引入缓存如 Redis减少对数据库的重复查询。对大数据量查询进行分页或异步处理。读写分离将报表类、分析类慢查询导向只读从库。4.3 建立监控与告警预防机制问题解决后要思考如何避免再次发生。慢 SQL 监控确保数据库的慢查询日志slow_query_log已开启并设置合理的阈值如 1 秒。使用工具如 pt-query-digest, pgBadger定期分析慢日志。执行计划监控对于核心业务 SQL可以定期如每天采集其执行计划并归档便于对比历史变化。一些 APM 工具也支持执行计划的对比告警。资源与性能基线建立数据库 CPU 使用率、IOPS、连接数、QPS/TPS 的基线。当指标偏离基线一定范围时触发告警。变更管控严格管控数据库结构变更DDL和应用发布流程。任何可能影响 SQL 性能的变更都应在测试环境进行充分的性能回归测试。排查这类问题经验很重要但比经验更重要的是清晰的排查路径和严谨的证据链。从监控到数据库会话从执行计划到数据变化每一步都要有依据。最忌讳的是拍脑袋“我觉得是索引问题”就直接加索引。很多时候真正的问题隐藏在你忽略的第一个环节里——比如一次不经意的应用发布或者一个失效的缓存策略。