30款热门AI模型一站整合DeepSeek/GLM/Qwen 随心用限时 5 折。 点击领海量免费额度在实际数据库运维和开发工作中SQL性能突然劣化是典型的线上故障场景。一条昨天执行还很快的查询今天突然变得极慢不仅直接影响用户体验更可能瞬间耗尽数据库资源导致CPU飙升、连接池打满甚至引发服务雪崩。这类问题排查起来往往时间紧迫需要一套清晰、高效的诊断思路而不是盲目地重启服务或添加索引。本文将以一个虚构但典型的场景为例一条线上核心SQL昨日平均执行时间50毫秒今日突然飙升至5秒数据库服务器CPU使用率随之达到90%。我们将模拟一位资深DBA或开发者的视角从现象出发层层递进构建一套从紧急止血到根因分析的完整排查路径。整个过程将覆盖监控指标查看、执行计划分析、系统状态检查、参数与数据变更追溯等关键环节并提供具体的命令、脚本和排查清单。无论你使用的是MySQL、PostgreSQL还是其他关系型数据库其核心排查逻辑是相通的。1. 理解问题本质为什么SQL会“突然”变慢在开始敲命令之前必须先理解“突然变慢”背后的几种可能性。这决定了后续排查的优先级和方向。性能劣化很少是无缘无故的通常源于环境或数据的某种变化。1.1 性能突变的常见根因分类我们可以将原因大致归为以下几类并按排查的常见顺序排列执行计划改变这是最经典的原因。数据库优化器为同一条SQL生成了不同的执行计划新计划可能错误地选择了全表扫描、错误的连接顺序或低效的索引。这通常由统计信息过时、索引失效、参数变更或数据分布变化触发。系统资源竞争SQL本身没变但运行环境变了。例如同一时段有大量并发查询竞争CPU和I/O服务器其他进程如备份、批处理占用了大量资源或者云数据库实例遇到了“邻居吵闹”问题。锁竞争与阻塞查询需要访问的行或表被其他事务长时间锁定如未提交的事务持有写锁导致当前查询必须等待锁释放。这在更新频繁的OLTP系统中很常见。数据量剧增SQL访问的表在短时间内插入了大量数据导致即使使用相同的索引需要扫描的数据页也大幅增加。或者查询条件本身过滤出的数据量发生了数量级变化。数据库对象状态异常例如索引损坏Index Corruption、表空间不足、临时表空间不足等这些会导致I/O效率急剧下降。网络或客户端问题虽然数据库端执行很快但结果集传输到客户端的过程变慢或者应用程序处理结果集的逻辑发生了变化。对于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.logPostgreSQL 检查与设置在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 FORMATJSON获取详细计划。-- 替换成你的问题SQL EXPLAIN FORMATJSON 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输出时要关注的关键点访问类型type列这是效率的关键。顺序为性能从好到坏systemconsteq_refrefrangeindexALL如果看到ALL全表扫描出现在大表上这就是一个强烈的危险信号。可能用到的索引possible_keys与实际用到的索引key如果possible_keys有值而key为NULL说明优化器认为没有合适的索引或者索引成本太高。扫描行数rows估算的需要扫描的行数。这个数字是否比昨天大得多额外信息Extra列Using filesort表示需要额外的排序步骤可能很耗资源。Using temporary表示需要创建临时表常见于GROUP BY、DISTINCT、UNION。Using where在存储引擎层之后进行了过滤。连接顺序与类型检查多表连接的顺序是否合理连接算法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 ...。数据分布变化查询条件中的某个字段值分布是否发生了剧变例如原本status1的记录只有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 针对性解决方案根据根因采取相应措施根因类别可能解决方案操作注意事项统计信息过时执行ANALYZE TABLE更新统计信息。选择业务低峰期进行。对于超大表考虑使用采样分析。索引失效/缺失1. 修复损坏索引 (REPAIR TABLE或重建索引)。2. 创建更合适的索引。创建索引是DDL操作可能锁表需在维护窗口进行。评估索引对写操作的影响。优化器参数不当调整优化器相关参数。修改全局参数前先在会话级测试。记录修改前的值以便回滚。锁竞争1. 定位并终止阻塞事务谨慎操作。2. 优化应用逻辑缩短事务时间及时提交/回滚。终止事务可能破坏数据一致性务必确认该事务可中断。数据量/分布变化1. 考虑分区表Partitioning。2. 优化查询条件增加更有效的过滤条件。3. 引入归档策略将历史数据移出热点表。分区表设计需谨慎并非所有查询都能受益。归档需要应用配合。系统资源不足1. 扩容数据库实例CPU、内存、IOPS。2. 优化并发度实施限流。3. 分离读写使用只读副本。扩容是成本最高的方案应先排除软件层问题。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 定期健康检查清单可以建立一个周期性如每周执行的检查脚本或流程内容涵盖关键表的数据增长情况。索引碎片率MySQL可检查INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS等。未使用的冗余索引。优化器统计信息的更新时间。数据库参数与基线配置的差异。通过这次从CPU飙升告警到根因定位的完整推演我们可以看到SQL性能排查是一项结合了操作系统知识、数据库原理、SQL优化经验和系统变更管理的综合性工作。它没有银弹但有一套可循的方法论保持冷静由外而内从全局到局部用数据监控、执行计划、日志说话最终关联变更找到差异点。掌握这套方法不仅能解决眼前的故障更能逐步构建起对数据库性能的深度掌控力。 30款热门AI模型一站整合DeepSeek/GLM/Qwen 随心用限时 5 折。 点击领海量免费额度