SQL性能突降排查：从CPU飙升到执行计划劣化的全链路诊断

🚀 30+款热门AI模型一站整合，DeepSeek/GLM/Qwen 随心用，限时 5 折。 👉 点击领海量免费额度

在实际数据库运维和开发工作中，SQL性能突然劣化是典型的线上故障场景。一条昨天执行还很快的查询，今天突然变得极慢，不仅直接影响用户体验，更可能瞬间耗尽数据库资源，导致CPU飙升、连接池打满，甚至引发服务雪崩。这类问题排查起来往往时间紧迫，需要一套清晰、高效的诊断思路，而不是盲目地重启服务或添加索引。

本文将以一个虚构但典型的场景为例：一条线上核心SQL，昨日平均执行时间50毫秒，今日突然飙升至5秒，数据库服务器CPU使用率随之达到90%。我们将模拟一位资深DBA或开发者的视角，从现象出发，层层递进，构建一套从紧急止血到根因分析的完整排查路径。整个过程将覆盖监控指标查看、执行计划分析、系统状态检查、参数与数据变更追溯等关键环节，并提供具体的命令、脚本和排查清单。无论你使用的是MySQL、PostgreSQL还是其他关系型数据库，其核心排查逻辑是相通的。

1. 理解问题本质：为什么SQL会“突然”变慢？

在开始敲命令之前，必须先理解“突然变慢”背后的几种可能性。这决定了后续排查的优先级和方向。性能劣化很少是无缘无故的，通常源于环境或数据的某种变化。

1.1 性能突变的常见根因分类

我们可以将原因大致归为以下几类，并按排查的常见顺序排列：

1. 执行计划改变：这是最经典的原因。数据库优化器为同一条SQL生成了不同的执行计划，新计划可能错误地选择了全表扫描、错误的连接顺序或低效的索引。这通常由统计信息过时、索引失效、参数变更或数据分布变化触发。
2. 系统资源竞争：SQL本身没变，但运行环境变了。例如，同一时段有大量并发查询竞争CPU和I/O；服务器其他进程（如备份、批处理）占用了大量资源；或者云数据库实例遇到了“邻居吵闹”问题。
3. 锁竞争与阻塞：查询需要访问的行或表被其他事务长时间锁定（如未提交的事务持有写锁），导致当前查询必须等待锁释放。这在更新频繁的OLTP系统中很常见。
4. 数据量剧增：SQL访问的表在短时间内插入了大量数据，导致即使使用相同的索引，需要扫描的数据页也大幅增加。或者，查询条件本身过滤出的数据量发生了数量级变化。
5. 数据库对象状态异常：例如索引损坏（Index Corruption）、表空间不足、临时表空间不足等，这些会导致I/O效率急剧下降。
6. 网络或客户端问题：虽然数据库端执行很快，但结果集传输到客户端的过程变慢，或者应用程序处理结果集的逻辑发生了变化。

对于CPU直接飙升至90%的情况，执行计划劣化和系统资源竞争往往是首要怀疑对象，因为它们最可能引发大量低效的磁盘扫描或计算，瞬间吃满CPU。

1.2 建立排查的黄金法则：先全局，后局部；先状态，后细节

面对紧急问题，切忌一头扎进某条SQL的细节。正确的做法是：

• 先看全局指标：CPU、内存、I/O、连接数。确认问题是全局性的还是局部性的。
• 再找问题SQL：从数据库的活跃会话或慢查询日志中定位到具体是哪条（或哪类）SQL导致了高负载。
• 深入分析该SQL：获取其当前的执行计划，并与历史正常时的计划进行对比。
• 关联系统变更：检查近期是否有过发布、配置变更、数据迁移等操作。

2. 紧急状态诊断与信息收集

当告警响起，CPU达到90%，第一步是快速登录数据库服务器，收集第一手现场信息，目的是定位问题范围和找到“元凶”SQL。

2.1 检查操作系统级资源状态

使用top,htop,vmstat,iostat等命令，快速了解资源瓶颈在哪里。

# 1. 查看整体CPU使用情况，找到消耗CPU的进程 top -c # 在top界面按 ‘1’ 可以查看每个CPU核心的详细使用率，确认是否是所有核心都高。 # 观察 `%Cpu(s)` 行，如果 `us` (用户态) 和 `sy` (内核态) 很高，而 `id` (空闲) 很低，说明CPU确实被进程占用。 # 2. 查看内存使用情况，排除内存不足导致大量Swap引起的CPU开销 free -h # 关注 `available` 列，如果很小，且 `swap` 列的 `si` (swap in) 和 `so` (swap out) 在 vmstat 中很高，则可能是内存问题。 # 3. 使用 vmstat 查看系统整体状态（每2秒刷新一次） vmstat 2 # 关键列： # r: 运行队列长度，如果持续大于CPU核心数，说明进程在排队等待CPU。 # us, sy, id: 同top。 # wa: I/O等待时间百分比。如果很高，说明瓶颈可能在磁盘I/O。 # 4. 使用 iostat 查看磁盘I/O状况 iostat -dx 2 # 关键列： # `%util`: 设备利用率。接近100%表示设备饱和。 # `await`: 平均I/O等待时间（毫秒）。值很大表示磁盘响应慢。

关键判断：如果top显示是数据库进程（如mysqld,postgres）的CPU占用率极高，且wa值不高，那么问题大概率出在数据库内部的计算逻辑上，进一步印证了执行计划劣化的可能性。

2.2 定位数据库内部的高负载会话

接下来，需要进入数据库，查看当前正在执行的会话。

MySQL 示例：

-- 查看当前所有连接和正在执行的SQL SHOW PROCESSLIST; -- 或者使用性能库（MySQL 5.6+） SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep' AND TIME > 2 ORDER BY TIME DESC; -- 更详细的信息，可以使用 sys 库（需安装） USE sys; -- 查看哪些SQL消耗了最多的资源 SELECT * FROM sys.session WHERE conn_id IS NOT NULL ORDER BY cpu_time DESC LIMIT 10; -- 或者直接查询 statements_with_runtimes_in_95th_percentile 等视图

PostgreSQL 示例：

-- 查看当前活动会话和其正在执行的查询 SELECT pid, usename, application_name, client_addr, state, query, query_start FROM pg_stat_activity WHERE state != 'idle' AND query NOT LIKE '%pg_stat_activity%' ORDER BY query_start; -- 查看锁等待情况 SELECT blocked_locks.pid AS blocked_pid, blocked_activity.query AS blocked_query, blocking_locks.pid AS blocking_pid, blocking_activity.query AS blocking_query FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid WHERE NOT blocked_locks.granted;

关键目标：从这些查询结果中，找到那些执行时间（TIME）长、状态为‘Executing’或‘Running’、且CPU消耗高的会话。记录下它们的完整SQL文本。这就是我们的“嫌疑犯”。

2.3 启用并检查慢查询日志

如果问题SQL没有正在执行，或者你想看过去一段时间内的“慢查询”，慢查询日志是关键。确保你的数据库已开启慢查询日志。

MySQL 检查与设置：

-- 查看慢查询日志相关参数 SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time%'; -- 临时开启并设置（生产环境慎用，可能影响性能） SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; -- 设置慢查询阈值为2秒 SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

然后，使用mysqldumpslow或pt-query-digest(Percona Toolkit) 工具分析慢日志，快速找到最耗时的SQL。

# 使用 mysqldumpslow 按总耗时排序 mysqldumpslow -s t /var/log/mysql/slow.log | head -20 # 使用 pt-query-digest 进行更专业的分析 pt-query-digest /var/log/mysql/slow.log

PostgreSQL 检查与设置：在postgresql.conf中设置：

log_min_duration_statement = 2000 # 记录执行超过2000毫秒的语句 logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

然后重启或重载配置，并查看日志文件。

3. 深入分析：对比执行计划与系统状态

找到问题SQL后，下一步是深入分析它为什么变慢。核心是获取并对比其当前执行计划与历史（或预期）的正常执行计划。

3.1 获取SQL的当前执行计划

以MySQL为例，使用EXPLAIN或EXPLAIN FORMAT=JSON获取详细计划。

-- 替换成你的问题SQL EXPLAIN FORMAT=JSON SELECT a.*, b.name FROM order_table a JOIN user_table b ON a.user_id = b.id WHERE a.create_time > '2023-10-26' AND a.status = 1 AND b.city = 'Shanghai' ORDER BY a.amount DESC LIMIT 100;

分析EXPLAIN输出时要关注的关键点：

1. 访问类型（type列）：这是效率的关键。顺序为（性能从好到坏）：
   • system>const>eq_ref>ref>range>index>ALL
   • 如果看到ALL（全表扫描）出现在大表上，这就是一个强烈的危险信号。
2. 可能用到的索引（possible_keys）与实际用到的索引（key）：如果possible_keys有值而key为NULL，说明优化器认为没有合适的索引，或者索引成本太高。
3. 扫描行数（rows）：估算的需要扫描的行数。这个数字是否比昨天大得多？
4. 额外信息（Extra列）：
   • Using filesort：表示需要额外的排序步骤，可能很耗资源。
   • Using temporary：表示需要创建临时表，常见于GROUP BY、DISTINCT、UNION。
   • Using where：在存储引擎层之后进行了过滤。
5. 连接顺序与类型：检查多表连接的顺序是否合理，连接算法（Nested Loop, Hash Join, Merge Join）是否高效。

3.2 检查表统计信息

优化器严重依赖统计信息（如行数、索引基数、数据分布直方图）来生成执行计划。如果统计信息过时，优化器可能会做出错误判断。

MySQL 检查与更新统计信息：

-- 查看表的统计信息 SHOW TABLE STATUS LIKE 'order_table'; -- 关注 `Rows` 列，但这只是估算值。 -- 查看某个索引的基数（Cardinality） SHOW INDEX FROM order_table; -- `Cardinality` 列表示索引中唯一值的估计数量。如果这个值远小于表行数，说明索引选择性不高。 -- 手动更新表的统计信息（对于InnoDB，这会触发异步分析） ANALYZE TABLE order_table, user_table; -- 注意：在大表上，ANALYZE TABLE 可能会消耗一些I/O资源。

PostgreSQL 使用ANALYZE命令。

3.3 检查索引状态

索引损坏或失效（虽然不常见）也会导致性能问题。

-- MySQL 检查表（会检查数据和索引） CHECK TABLE order_table; -- 如果发现错误，可能需要使用 REPAIR TABLE（仅对某些存储引擎有效，InnoDB通常不需要，它有自己的恢复机制）。

3.4 检查是否有锁阻塞

如果SQL在等待锁，它在SHOW PROCESSLIST中可能显示为Waiting for ... lock状态。使用数据库特定的锁查看命令。

MySQL (InnoDB) 查看锁信息：

-- 查看当前InnoDB事务和锁信息 SELECT * FROM information_schema.INNODB_TRX; -- 查看事务 SELECT * FROM information_schema.INNODB_LOCKS; -- 查看锁 SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 查看锁等待

如果发现有问题SQL在等待一个长时间未提交的事务，那么锁竞争就是根因。

4. 关联变更：寻找“昨天”和“今天”的不同

执行计划不会无缘无故改变。必须系统地回顾可能引发变化的操作。

4.1 数据变更检查

• 数据量激增：检查问题SQL涉及的表，在最近一段时间（例如过去24小时）内，是否发生了大规模的数据导入或业务峰值写入？SELECT COUNT(*) FROM table_name WHERE create_time > ...。
• 数据分布变化：查询条件中的某个字段值分布是否发生了剧变？例如，原本status=1的记录只有1%，现在变成了50%。这会导致基于该字段的索引选择性下降，优化器可能弃用索引。

4.2 数据库配置与对象变更检查

• 参数变更：是否有人修改了数据库的优化器相关参数？例如 MySQL 的optimizer_switch、join_buffer_size、sort_buffer_size，或者 PostgreSQL 的random_page_cost、effective_cache_size。检查配置文件的修改历史或数据库的参数变更记录。
• 索引变更：是否新增、删除了相关索引？是否有人不小心DROP了关键索引？
• 表结构变更：是否对表进行了ALTER TABLE操作（如增加字段、修改字段类型）？某些DDL操作可能会使索引失效或导致统计信息重置。
• 数据库版本/补丁：是否进行了数据库的小版本升级或打了补丁？不同版本的优化器行为可能有差异。

4.3 应用与系统环境变更检查

• 应用发布：是否发布了新版本的应用？可能改变了SQL的传入参数（如查询条件值）、连接池配置、或者执行SQL的框架逻辑。
• 连接池与客户端：应用连接池配置（如最大连接数、超时时间）是否被修改？客户端机器是否存在网络波动？
• 服务器资源：是否发生了虚拟机迁移、宿主机资源竞争、或磁盘性能下降？

5. 模拟、验证与解决方案

基于以上分析，你可能会形成一个或多个假设。接下来需要验证并解决。

5.1 验证假设：强制使用旧计划或对比测试

如果怀疑是执行计划问题，可以尝试“引导”优化器。

MySQL 使用索引提示：

-- 强制使用某个索引 SELECT a.*, b.name FROM order_table a FORCE INDEX (idx_create_time_status) JOIN user_table b USE INDEX (idx_city) ON a.user_id = b.id WHERE a.create_time > '2023-10-26' AND a.status = 1 AND b.city = 'Shanghai' ORDER BY a.amount DESC LIMIT 100;

执行此SQL，并再次用EXPLAIN查看计划，同时观察实际执行时间。如果性能恢复，则基本确认是索引选择问题。

注意：FORCE INDEX是临时验证手段，长期解决方案应该是修复统计信息或优化查询/索引。

在测试环境复现：尝试将生产环境的表结构和数据（采样）同步到测试环境，在测试环境中复现问题，并安全地进行各种对比实验。

5.2 针对性解决方案

根据根因，采取相应措施：

5.3 紧急止血措施

如果问题影响巨大，需要立即恢复服务，可以考虑以下临时方案，但必须同步进行根因排查：

• 重启数据库实例：最粗暴但有时有效，可以清除所有缓存和会话，但风险极高，可能导致数据丢失或服务中断。
• 杀死问题会话：通过KILL [session_id]命令终止正在执行的问题SQL。这能快速释放资源，但可能导致业务请求失败。
• 降级或熔断：在应用层对触发该SQL的功能进行降级或暂时屏蔽。

6. 构建预防体系与最佳实践

一次排查的结束，是预防体系建设的开始。避免类似问题再次发生，需要系统性的工作。

6.1 建立常态化监控与告警

• 数据库监控：监控CPU、内存、连接数、慢查询数量、QPS、TPS、锁等待数量等核心指标。设置合理的告警阈值（例如，CPU持续>80%超过5分钟）。
• SQL性能监控：定期（如每天）采集慢查询日志，使用工具进行聚合分析，关注执行时间、扫描行数的趋势变化。对新上线的SQL进行审核。
• 执行计划监控：对于核心SQL，可以定期（或在每次统计信息更新后）保存其执行计划，进行对比。MySQL 8.0的EXPLAIN ANALYZE可以获取实际执行数据。

6.2 规范变更流程

• 任何线上变更（DDL、参数、索引）必须经过评审和测试，并在低峰期执行。
• 建立SQL上线审核机制，特别是涉及大表、复杂查询的SQL。
• 对数据增长有预估，提前规划归档或分库分表方案。

6.3 优化开发习惯

• 避免使用SELECT *，只查询需要的字段。
• 合理使用索引，理解联合索引的最左前缀原则。
• 警惕隐式类型转换，它会导致索引失效。
• 分页查询优化，对于深度分页，使用WHERE id > ? LIMIT n代替LIMIT m, n。
• 理解业务，与开发人员沟通，明确查询的真实数据量和过滤条件。

6.4 定期健康检查清单

可以建立一个周期性（如每周）执行的检查脚本或流程，内容涵盖：

1. 关键表的数据增长情况。
2. 索引碎片率（MySQL可检查INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS等）。
3. 未使用的冗余索引。
4. 优化器统计信息的更新时间。
5. 数据库参数与基线配置的差异。

通过这次从CPU飙升告警到根因定位的完整推演，我们可以看到，SQL性能排查是一项结合了操作系统知识、数据库原理、SQL优化经验和系统变更管理的综合性工作。它没有银弹，但有一套可循的方法论：保持冷静，由外而内，从全局到局部，用数据（监控、执行计划、日志）说话，最终关联变更找到差异点。掌握这套方法，不仅能解决眼前的故障，更能逐步构建起对数据库性能的深度掌控力。

🚀 30+款热门AI模型一站整合，DeepSeek/GLM/Qwen 随心用，限时 5 折。 👉 点击领海量免费额度