你好我是专注于后端开发和数据库优化的技术博主。在日常工作中我们经常会遇到一些“诡异”的线上问题比如一条昨天还运行良好的SQL今天突然性能暴跌连带数据库CPU飙升直接影响到整个服务的稳定性。这种问题排查起来往往千头万绪非常考验开发者的系统性思维和实战经验。今天我们就来深度拆解这个经典的面试题/实战场景“线上有一条SQL昨天跑50毫秒今天突然跑了5秒数据库CPU直接飙到90%你怎么排查” 本文将为你梳理一套从现象到根因的完整排查方法论涵盖监控、分析、定位、解决的全流程并提供大量可直接复用的命令和脚本。无论你是正在准备面试还是需要处理实际的线上故障这篇文章都能为你提供清晰的思路和实用的工具。1. 问题背景与核心挑战分析当数据库CPU突然飙升至90%以上并且伴随特定SQL语句执行时间从毫秒级暴增至秒级时这通常不是一个孤立的事件。它背后反映的是数据库执行计划的突变、资源争用或数据状态的剧烈变化。这类问题的排查难点在于时效性要求高CPU高企直接影响线上服务需要快速定位并止血。干扰因素多可能是SQL本身问题、数据库状态问题、硬件资源问题或并发负载问题。根因隐蔽像“执行计划变更”这种原因不深入数据库内部很难直接发现。因此我们需要一个系统化、层层递进的排查框架而不是盲目地尝试各种可能性。2. 环境准备与排查工具箱在开始具体排查前确保你拥有必要的工具和权限。以下清单适用于大多数关系型数据库如 MySQL, PostgreSQL, Oracle但具体命令可能略有不同本文将以MySQL为例进行演示。基础环境数据库MySQL 5.7 / 8.0 其他数据库原理相通操作系统Linux (CentOS/Ubuntu)权限需要具备查询数据库性能视图如INFORMATION_SCHEMA,PERFORMANCE_SCHEMA,sys库和操作系统监控的权限。必备工具箱数据库客户端mysql命令行工具或 MySQL Workbench、DBeaver等图形化工具。系统监控命令top,htop,vmstat,mpstat,pidstat。网络工具netstat或ss。数据库诊断命令SHOW PROCESSLIST;,SHOW ENGINE INNODB STATUS;,EXPLAIN。慢查询日志确保已开启并配置合理阈值。性能模式Performance SchemaMySQL 5.6 版本的重要性能数据来源。3. 系统性排查六步法面对CPU飙升和慢SQL建议按照以下六个步骤进行从宏观到微观逐步收敛问题。3.1 第一步确认现象与影响范围首先需要精确量化问题并判断其影响是全局性的还是局部性的。确认CPU使用情况登录数据库服务器使用top或htop命令观察是哪个进程很可能是mysqld的CPU使用率异常高。使用mpstat -P ALL 2可以查看每个CPU核心的利用率判断是否是单核跑满。# 查看整体CPU和进程情况 top -c # 查看每个CPU核心的详细利用率每2秒刷新一次 mpstat -P ALL 2确认数据库连接和活动状态连接到数据库查看当前所有连接和执行中的线程。-- 查看当前所有连接和正在执行的SQL SHOW FULL PROCESSLIST; -- 或者使用 sys 库的视图更清晰 USE sys; SELECT * FROM processlist WHERE command ! Sleep ORDER BY time DESC LIMIT 20;重点关注State列为Sending data,Sorting result,Creating sort index,locked等的线程以及Time值很大的线程。记录下疑似问题SQL的片段和其Id。定位问题SQL从PROCESSLIST中找到执行时间最长、状态异常的SQL后需要将其完整捕获。如果SQL过长被截断可以通过performance_schema的events_statements_current表来获取。-- 首先找到问题线程的THREAD_ID对应PROCESSLIST中的Id SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID [你的Processlist Id]);这一步的目标确认是数据库进程导致CPU高并初步锁定1条或几条可疑的慢SQL。3.2 第二步分析SQL执行计划突变这是最核心、最常见的原因。一条SQL的执行效率绝大部分取决于数据库优化器为其选择的“执行计划”Execution Plan。计划一旦变差性能就会指数级下降。获取当前糟糕的执行计划使用EXPLAIN或EXPLAIN FORMATJSON分析问题SQL。EXPLAIN FORMATJSON SELECT * FROM your_table WHERE your_column some_value AND create_time 2023-10-01; -- 或者使用传统格式 EXPLAIN SELECT * FROM your_table WHERE your_column some-value;重点关注type列是否是ALL全表扫描或index全索引扫描理想情况是ref,range,const。key列实际使用的索引。是否用了不合适的索引或者根本没用到索引NULLrows列预估扫描行数。这个数字是否异常巨大Extra列是否有Using filesort文件排序或Using temporary使用临时表这些操作非常消耗CPU和内存。对比历史执行计划如果你有SQL性能监控平台如Archery, Yearning, 或自建的slow_log分析可以对比该SQL昨天的执行计划。如果没有可以尝试通过数据库的优化器“提示”Hint或调整会话参数模拟旧的执行环境看性能是否恢复。但这需要你对历史情况有了解。分析执行计划变更的诱因统计信息过时/不准确这是头号嫌疑犯。当表中数据发生大量增删改例如夜间批量作业导入/删除大量数据后表的统计信息如总行数、数据分布直方图没有及时更新。优化器基于错误的统计信息可能选择了一个完全不同的、低效的索引。索引失效或变更索引被意外删除、损坏或新建了更“有吸引力”但实际不适合该查询的索引导致优化器“选错了路”。SQL写法或参数变化虽然SQL文本没变但传入的参数值变了。例如WHERE status ?昨天传的是1有索引的高选择性值今天传的是0占表中99%数据的低选择性值导致优化器认为全表扫描比走索引更划算。3.3 第三步检查数据库与系统资源状态执行计划是内因资源是外因。需要检查是否有资源瓶颈加剧了问题。检查数据库内部状态-- 查看InnoDB引擎状态关注SEMAPHORES信号量等待和LATEST DETECTED DEADLOCK死锁 SHOW ENGINE INNODB STATUS\G -- 查看锁等待情况 SELECT * FROM information_schema.INNODB_LOCKS; SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 查看缓冲池命中率如果过低会导致大量物理IO间接推高CPU SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%; -- 计算命中率 ≈ (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%检查系统资源内存使用free -m或vmstat 2查看是否发生大量交换Swapping。siswap in和soswap out不为0且持续增长是危险信号。数据库进程被换出到磁盘会引发灾难性性能下降。磁盘IO使用iostat -x 2查看%util利用率和await平均等待时间。如果磁盘利用率长时间接近100%说明IO是瓶颈。网络虽然可能性较小但可以检查网络连接数是否异常。3.4 第四步审查慢查询日志与性能模式数据如果问题SQL没有在当前的PROCESSLIST中抓到或者想看看同一时间段是否有其他慢查询“共犯”慢查询日志是黄金数据源。确认慢查询日志已开启SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time%;long_query_time通常设置为1秒或更低以便捕获问题。分析慢查询日志使用mysqldumpslow工具或pt-query-digestPercona Toolkit 的一部分进行聚合分析。# 使用mysqldumpslow按总耗时排序 mysqldumpslow -s t /path/to/slow.log | head -20 # 使用更强大的pt-query-digest pt-query-digest /path/to/slow.log --limit10分析报告会告诉你哪些SQL模板最慢、总耗时最长、执行次数最多、锁时间最长等。利用Performance Schema对于更精细的分析可以查询events_statements_summary_by_digest表它按SQL摘要Digest聚合了性能数据。USE performance_schema; SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_latency_s, AVG_TIMER_WAIT/1000000000 AS avg_latency_s, SUM_ROWS_EXAMINED, SUM_ROWS_SENT, FIRST_SEEN, LAST_SEEN FROM events_statements_summary_by_digest ORDER BY avg_latency_s DESC LIMIT 10;这可以帮助你发现那些平均执行时间突然变长的SQL模式。3.5 第五步深入诊断与场景归因综合以上信息我们可以将问题归因到几个常见场景场景一统计信息不准导致索引失效现象EXPLAIN显示本该走索引的查询变成了全表扫描typeALLrows预估严重偏离实际。验证手动更新统计信息看执行计划是否恢复正常。ANALYZE TABLE your_table; -- 对于MySQL -- 对于Oracle: EXEC DBMS_STATS.GATHER_TABLE_STATS(SCHEMA_NAME, TABLE_NAME); -- 对于PostgreSQL: ANALYZE your_table;解决更新后立即重跑问题SQL观察CPU和执行时间。如果恢复则需建立定期的统计信息更新任务。场景二低效的SQL写法现象SQL中存在SELECT *、在WHERE子句中对字段进行函数操作如WHERE DATE(create_time) ‘2023-10-01’、使用OR导致索引合并不佳、或嵌套过深的子查询。排查仔细审视SQL使用EXPLAIN验证每个条件。解决重写SQL。例如将WHERE DATE(create_time) …改为WHERE create_time ‘2023-10-01’ AND create_time ‘2023-10-02’。场景三锁竞争与并发问题现象SHOW PROCESSLIST显示大量线程处于Waiting for table metadata lock,Waiting for row lock状态。SHOW ENGINE INNODB STATUS显示较长的锁等待链。排查检查是否有未提交的大事务、长时间运行的ALTER TABLE操作或者应用逻辑导致死锁。解决优化事务粒度避免长事务。对于紧急情况可以 kill 掉阻塞源需谨慎。场景四资源不足或配置不当现象系统监控显示内存不足引发Swap或磁盘IO饱和。排查结合vmstat,iostat和数据库的Innodb_buffer_pool_size等参数判断。解决扩容硬件资源或优化数据库配置参数如增大缓冲池。3.6 第六步实施解决方案与验证根据定位到的根因采取相应措施紧急止血如果情况危急可以考虑临时操作。Kill 会话终止正在执行的问题SQL会话使用SHOW PROCESSLIST找到Id然后KILL [Id]。增加资源临时扩容CPU/内存云环境下。切换流量如果有从库将读流量切到从库。根本解决更新统计信息执行ANALYZE TABLE。优化SQL与索引根据EXPLAIN结果增加缺失的索引、删除冗余索引、使用覆盖索引。重写低效SQL。调整数据库参数例如增大innodb_buffer_pool_size优化sort_buffer_size等。优化应用逻辑避免在循环中执行SQL使用批量操作引入缓存。验证效果再次执行问题SQL观察执行时间是否恢复到毫秒级。监控数据库服务器CPU使用率看是否已显著下降并趋于平稳。在测试环境进行回归测试确保优化没有引入新的问题。4. 完整实战案例模拟假设我们有一个用户订单表orders昨天以下查询很快今天变慢。问题SQLSELECT customer_id, SUM(amount) FROM orders WHERE status ‘SHIPPED’ AND create_date CURDATE() - INTERVAL 7 DAY GROUP BY customer_id;排查过程模拟发现与确认监控报警CPU 90%SHOW PROCESSLIST发现上述SQL执行了4秒。分析执行计划EXPLAIN SELECT customer_id, SUM(amount) FROM orders WHERE status ‘SHIPPED’ AND create_date CURDATE() - INTERVAL 7 DAY GROUP BY customer_id;输出可能显示type: ALLkey: NULL说明进行了全表扫描。检查发现表上有idx_status(status) 和idx_create_date(create_date) 两个单列索引但优化器可能认为同时利用两个索引效率不高或者统计信息不准导致它放弃了索引。检查统计信息与数据SHOW TABLE STATUS LIKE ‘orders’; -- 查看表行数 SELECT COUNT(*) FROM orders WHERE status ‘SHIPPED’; -- 查看数据分布 SELECT COUNT(*) FROM orders WHERE create_date CURDATE() - INTERVAL 7 DAY;发现status’SHIPPED’的记录占了全表的80%选择性极差。而昨天这个比例可能只有10%。由于夜间批量作业更新了大量订单状态导致数据分布巨变。解决方案短期更新统计信息ANALYZE TABLE orders;。优化器可能会重新评估选择idx_create_date索引因为时间条件可能选择性更高。长期考虑建立复合索引(create_date, status)或(status, create_date)具体顺序需要根据实际查询频率和数据分布决定。对于此查询(create_date, status)可能更优因为它可以先快速定位最近7天的数据再过滤状态。ALTER TABLE orders ADD INDEX idx_create_date_status (create_date, status);验证添加索引后再次EXPLAIN确认使用了新索引类型变为range。执行SQL时间恢复。5. 常见问题排查清单Checklist当遇到类似问题时可以按此清单快速过一遍排查方向具体操作可能发现的问题1. 定位问题SQLSHOW PROCESSLIST;, 监控平台找到执行时间长、状态异常的SQL2. 分析执行计划EXPLAIN/EXPLAIN ANALYZE全表扫描、错误索引、文件排序3. 检查统计信息SHOW TABLE STATUS,ANALYZE TABLE表行数不准、统计信息过时4. 检查索引SHOW INDEX FROM table_name;索引缺失、冗余、损坏5. 检查锁竞争SHOW ENGINE INNODB STATUS\G, 查锁表元数据锁、行锁等待、死锁6. 检查系统资源top,vmstat,iostat,free -mCPU饱和、内存Swap、磁盘IO瓶颈7. 检查慢查询日志mysqldumpslow,pt-query-digest高频慢SQL、同模式问题8. 检查SQL写法审查SQL文本SELECT *、字段函数计算、OR滥用9. 检查数据量与分布SELECT COUNT(*),GROUP BY分析数据量激增、数据倾斜6. 最佳实践与预防措施“治未病”优于“治已病”。通过以下实践可以极大降低此类问题发生的频率建立完善的监控告警体系数据库层监控QPS、TPS、连接数、慢查询数量、锁等待时间、缓冲池命中率。系统层监控CPU、内存、磁盘IO、网络流量。设置智能阈值当慢查询数量突增、CPU使用率持续超过80%时立即告警。规范SQL上线流程强制代码评审所有上线的SQL必须经过DBA或资深开发者评审重点关注执行计划。使用SQL审核工具集成像Yearning、Archery、SOAR这样的工具自动检测潜在性能问题。预发环境压测对于核心或复杂的SQL在预发环境进行压力测试。实施定期维护定时更新统计信息在业务低峰期如凌晨配置定时任务对核心表进行ANALYZE。定期索引优化使用pt-duplicate-key-checker检查冗余索引使用pt-index-usage分析索引使用情况删除无用索引。归档历史数据对按时间增长的表如日志、订单建立归档机制控制单表数据量。优化数据库设计与开发习惯设计合理的索引理解最左前缀原则避免过多索引优先使用复合索引。避免隐式转换确保WHERE条件中的字段类型与传入值类型一致。使用绑定变量Prepared Statements防止SQL注入的同时也有利于执行计划稳定。读写分离将报表类、分析类等重查询负载导向只读从库。处理“SQL突然变慢导致CPU飙升”的问题是对开发者数据库知识、系统思维和应急能力的综合考验。核心思路是先全局监控定位问题点再深入数据库内部分析执行计划结合系统资源状态最终归因到统计信息、索引、SQL写法或资源竞争等具体原因。掌握这套方法论并配以完善的监控和规范流程你就能从容应对这类棘手的线上故障。记住每一个慢SQL的背后都有一个等待被发现的优化机会。