“昨天还好好的今天突然就崩了”——这大概是后端工程师最怕听到的一句话。尤其是当一条核心 SQL 语句的执行时间从毫秒级飙升到秒级连带数据库 CPU 直接拉满 90%整个系统响应变慢告警短信响个不停。面对面试官这个经典拷问很多同学的第一反应是“加索引”但这往往只是隔靴搔痒甚至可能让情况更糟。这篇文章要解决的就是当线上 SQL 性能突然断崖式下跌时你该如何像侦探一样系统性地、高效地定位根因。这不是一个简单的“慢 SQL 优化”教程而是一套完整的、从现象到本质的线上应急排查 SOP标准作业程序。我们将从“昨天 50ms今天 5s”这个具体场景切入拆解出数据变化、执行计划变更、资源争抢、外部干扰等四大核心排查方向并提供可直接落地的命令、工具和决策树。读完本文你将掌握的不只是几个EXPLAIN命令而是一套面对生产环境数据库性能突变的结构化排查思维。无论是 MySQL、PostgreSQL 还是其他关系型数据库这套方法论都能让你临危不乱快速找到问题源头。1. 问题本质为什么“突然变慢”比“一直很慢”更棘手在深入排查之前我们必须先理解“性能突变”问题的特殊性。一条 SQL “一直很慢”通常是设计问题比如缺少索引、表结构不合理、写法糟糕。而“突然变慢”则意味着系统从一个相对稳定的状态因为某个变量的改变跳变到了另一个糟糕的状态。这个“变量”可能来自数据本身数据量突变、数据分布倾斜如某个字段突然大量重复。数据库内部执行计划Query Execution Plan被优化器错误地更改。运行环境服务器资源CPU、内存、IO被其他进程抢占或数据库内部资源锁、Buffer Pool出现争用。外部依赖网络波动、中间件故障、甚至是被恶意攻击。因此排查思路的核心是“对比”对比昨天和今天什么发生了变化我们的所有工具和命令都是为这个对比服务的。2. 第一反应建立现场快照与监控指标遇到突发性能问题切忌盲目操作如重启服务、狂加索引。第一步永远是保留现场和收集信息。2.1 立即采集的关键指标数据库连接与活动会话-- MySQL SHOW PROCESSLIST; -- 或使用更强大的 information_schema SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND ! Sleep AND TIME 2\G -- PostgreSQL SELECT * FROM pg_stat_activity WHERE state ! idle;重点关注这条慢 SQL 的状态State、执行时间Time、正在等待什么Info显示其当前语句。同时看是否有大量其他慢查询或阻塞锁。数据库全局状态-- MySQL 关键性能计数器 SHOW GLOBAL STATUS LIKE Threads_running; SHOW GLOBAL STATUS LIKE Innodb_row_lock%; SHOW GLOBAL STATUS LIKE Table_locks_waited; SHOW GLOBAL STATUS LIKE Slow_queries;Threads_running高说明并发高Innodb_row_lock_waits增长快说明行锁争用严重。操作系统资源# 1. 整体CPU使用情况重点看%us, %sy, %wa top # 2. 更精细的CPU和IO监控每2秒刷新一次 vmstat 2 # 输出解读 # r: 运行队列长度大于CPU核心数说明饱和 # b: 阻塞进程数 # us, sy, id, wa, st: CPU时间百分比用户态、系统态、空闲、等待IO、被偷 # 如果 ussy 接近100%且 wa 很低是CPU瓶颈如果 wa 高是IO瓶颈。 # 3. 磁盘IO状态 iostat -x 2 # 关注 %util设备利用率和 await平均等待时间 # 4. 网络连接排查网络问题或连接风暴 ss -s netstat -nat | awk {print $6} | sort | uniq -c | sort -rn2.2 锁定问题SQL的当前执行计划这是最核心的一步。你必须立刻获取这条 SQL 在今天变慢时数据库优化器实际选择的执行计划。-- MySQL (注意在真实环境执行EXPLAIN可能消耗资源需谨慎) EXPLAIN FORMATJSON SELECT /* 你的慢SQL */ ... FROM ... WHERE ...; -- 或者使用更详细的 optimizer trace需开启 SET optimizer_traceenabledon; SELECT /* 你的慢SQL */ ...; SELECT * FROM information_schema.OPTIMIZER_TRACE; SET optimizer_traceenabledoff; -- PostgreSQL EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ... FROM ... WHERE ...;关键点把EXPLAIN的输出特别是FORMATJSON或ANALYZE的结果完整保存下来。它将是你与“昨天正常状态”进行对比的基准。3. 核心排查方向一数据与统计信息之变优化器决定如何执行 SQL依赖于它对表数据分布的了解即“统计信息”。如果统计信息过时或不准优化器就会选择错误的执行计划。3.1 检查数据量突变询问业务方或查看日志昨天至今目标表是否发生了大规模数据导入/删除特别是WHERE条件或JOIN关联字段的数据分布是否剧变-- 快速估算表大小变化MySQL InnoDB SELECT TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA your_db AND TABLE_NAME your_table;3.2 检查与更新统计信息-- MySQL (InnoDB) ANALYZE TABLE your_table; -- 查看统计信息更新时间 SHOW TABLE STATUS LIKE your_table\G -- 关注Update_time字段 -- PostgreSQL ANALYZE your_table; -- 查看统计信息 SELECT schemaname, tablename, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE tablename your_table;最佳实践对于数据变化频繁的表如订单表应设置自动ANALYZE。在 MySQL 8.0 中innodb_stats_auto_recalc默认是开启的但可能不及时。手动执行ANALYZE TABLE是排查时的重要手段。4. 核心排查方向二执行计划对比与索引失效拿到了今天的执行计划接下来就要和“昨天的正常状态”对比。如果你有数据库性能监控平台如 Percona Monitoring and Management, Prometheus Grafana with MySQL exporter可以调出历史执行计划。如果没有就需要靠推理和实验。4.1 对比执行计划的关键差异索引选择今天是否走了全表扫描type: ALL而昨天走的是索引扫描type: range, ref, eq_ref连接顺序JOIN查询表的连接顺序是否被改变错误的连接顺序可能导致中间结果集暴涨。访问方法是否错误地使用了索引合并index_merge或临时表Using temporary预估行数rows列优化器预估需要扫描的行数是否严重偏离实际这直接指向统计信息问题。4.2 常见索引失效场景排查即使有索引也可能因为以下原因失效隐式类型转换WHERE varchar_column 123会导致索引失效。函数操作索引列WHERE DATE(create_time) 2023-10-27create_time上的索引无法使用。前导列缺失复合索引(a, b, c)查询条件只有b和c无法使用该索引。OR条件不当WHERE a1 OR b2如果a和b上都有单列索引有时优化器会选择全表扫描而非索引合并。索引选择性太差比如在“性别”字段上建索引因为只有两个值优化器可能认为走索引不如全表扫描。排查命令-- 查看表上有哪些索引 SHOW INDEX FROM your_table; -- 使用优化器提示强制使用某个索引进行测试仅用于诊断 SELECT /* INDEX(your_table idx_name) */ ... FROM your_table WHERE ...; -- 对比强制索引和不强制索引的执行时间5. 核心排查方向三系统资源与并发争用当数据库 CPU 飙到 90%除了 SQL 本身慢还可能是因为它在“等待”或“打架”。5.1 锁争用排查-- MySQL (InnoDB 锁信息5.7) SELECT * FROM information_schema.INNODB_LOCKS; SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 更直观的视图 (需安装sys schema或使用percona工具) SELECT * FROM sys.innodb_lock_waits; -- PostgreSQL SELECT * FROM pg_locks WHERE NOT granted; SELECT pg_blocking_pids(pid) FROM pg_stat_activity WHERE wait_event_type IS NOT NULL;现象你的慢 SQL 可能正在等待一个行锁、表锁或者它持有的锁阻塞了其他事务形成链式反应。检查SHOW PROCESSLIST中慢 SQL 的State是否为Waiting for table metadata lock、Waiting for row lock等。5.2 Buffer Pool 与内存争用Innodb_buffer_pool命中率低频繁的磁盘 IO。SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%; -- 计算命中率 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)如果命中率突然下降可能是业务高峰或某个大查询扫掉了热数据。临时表与排序Using temporary; Using filesort会导致在磁盘上创建临时表消耗大量 IO 和 CPU。SHOW GLOBAL STATUS LIKE Created_tmp%tables;如果值增长很快说明很多查询在创建临时表。5.3 CPU 瓶颈的进一步诊断使用top或htop查看是哪个进程/线程消耗 CPU。如果是 MySQL可以用performance_schema或sysschema 定位到具体线程和 SQL。-- MySQL (需开启performance_schema) SELECT THREAD_ID, EVENT_NAME, CURRENT_SCHEMA, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID IN ( SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID CONNECTION_ID() ); -- 更简单的方式使用sys schema SELECT * FROM sys.session WHERE conn_id ! CONNECTION_ID() ORDER BY cpu_time DESC LIMIT 5;6. 核心排查方向四外部因素与“黑天鹅”事件排查完数据库内部如果还没找到原因就要把视野放宽。网络问题应用服务器与数据库之间的网络延迟是否增加可以用ping、traceroute或从应用端抓包简单判断。中间件问题是否使用了数据库连接池如 HikariCP, Druid连接池配置是否合理是否有连接泄漏检查应用日志中关于连接获取超时的错误。定时任务/批量作业是否有定时的报表查询、数据归档、ETL 任务在同时运行它们可能消耗了大量资源。版本与配置变更这是最容易被忽略的一点仔细核对昨天至今数据库、操作系统、JDBC 驱动是否有过重启或配置变更是否有人手动清理了缓存或执行了FLUSH命令是否进行了在线 DDL 操作如加索引、改字段这可能导致元数据锁MDL等待。安全事件是否遭遇 SQL 注入攻击或爬虫导致数据库执行了大量非预期查询7. 实战演练一个完整的排查案例推演场景复现订单查询接口超时对应 SQLSELECT * FROM orders WHERE user_id? AND statusACTIVE ORDER BY create_time DESC LIMIT 10昨天 50ms今天 5s。数据库 CPU 90%。排查步骤保存现场立刻执行SHOW PROCESSLIST找到该 SQL记录其Id。同时运行vmstat 2和iostat -x 2。获取当前执行计划EXPLAIN FORMATJSON SELECT * FROM orders WHERE user_id12345 AND statusACTIVE ORDER BY create_time DESC LIMIT 10;发现计划显示type: ALL全表扫描key: NULL。而我们知道user_id上有索引idx_user。对比与假设为什么优化器不用索引假设A统计信息过时。执行ANALYZE TABLE orders;后再次EXPLAIN计划未变。假设B索引失效。检查WHERE条件发现user_id是BIGINT但应用传入的是字符串检查代码和日志确认传参类型正确。假设C数据倾斜。查询某个特定user_id如 12345的订单。检查该用户的数据量SELECT COUNT(*) FROM orders WHERE user_id12345;。发现结果有50万行而statusACTIVE的只有10行。真相浮出这个用户是个测试账号或异常账号其历史订单数据量巨大。优化器认为根据user_id筛选出50万行再从中过滤status成本可能高于直接全表扫描假设表总共1000万行。昨天该用户数据少所以走了索引。验证与解决验证使用优化器提示强制走索引看是否变快。SELECT /* INDEX(orders idx_user) */ * FROM orders ...;执行时间恢复到约100ms。说明索引本身有效是优化器的成本估算出了问题。解决方案短期考虑在应用层对该异常用户进行限流或特殊处理。或者建立更合适的复合索引(user_id, status, create_time)让筛选和排序更高效。长期优化统计信息收集策略或使用直方图MySQL 8.0 的histogram来帮助优化器更好地理解user_id字段的数据分布。根因报告问题根本原因是“数据分布倾斜导致优化器成本估算错误选择了次优执行计划”。CPU 飙高是因为全表扫描产生了巨大的临时排序和磁盘 IO。8. 常用排查工具箱与命令清单将上述过程工具化形成你的排查清单排查阶段目标关键命令/工具1. 现象确认确认问题SQL及影响范围SHOW PROCESSLIST,top,vmstat 22. 执行计划分析获取当前SQL执行路径EXPLAIN FORMATJSON,EXPLAIN ANALYZE(PgSQL)3. 数据/统计信息检查数据量与统计信息健康度ANALYZE TABLE,SHOW TABLE STATUS, 查询数据分布4. 索引有效性确认索引是否被使用及为何失效SHOW INDEX, 检查查询条件使用优化器提示5. 资源争用检查锁、内存、IO竞争INNODB_LOCKS,INNODB_LOCK_WAITS,SHOW GLOBAL STATUS LIKE ...6. 外部因素排除环境、网络、变更影响检查变更记录、网络监控、中间件日志7. 深度剖析定位具体线程和开销performance_schema,sysschema,pt-query-digest高级工具推荐Percona Toolkitpt-query-digest分析慢日志pt-index-usage分析索引使用情况。MySQL ShellPerformance Schema进行更深入的性能剖析。Prometheus Grafana建立长期的数据库监控有了历史基线对比“突变”将易如反掌。9. 最佳实践与防患于未然排查是亡羊补牢预防才是根本。完善的监控与告警监控数据库的 QPS、TPS、连接数、慢查询数、CPU/内存/IO 使用率、Buffer Pool 命中率、锁等待数量。设置合理的告警阈值。慢查询日志常态化分析开启慢查询日志long_query_time设置为1秒或更低定期使用工具如pt-query-digest进行分析即使没有告警也能发现潜在的性能退化。变更管理任何数据库结构变更DDL、配置变更、批量数据操作必须在低峰期进行并做好回滚预案。上线前进行性能影响评估。使用执行计划绑定对于极其重要且执行计划必须稳定的 SQL可以考虑使用执行计划绑定如 MySQL 的optimizer hint或 SQL Plan Management来固定最优计划防止优化器“抽风”。容量规划与架构设计提前规划数据增长对可能产生数据倾斜的业务场景如超级用户进行特殊设计例如分表、读写分离、引入缓存等。回到面试官的问题“线上有一条SQL昨天跑50毫秒今天突然跑了5秒数据库CPU直接飙到90%你怎么排查”你现在可以这样回答“这是一个典型的性能突变问题我会按照‘保存现场、对比分析、逐层下钻’的思路进行。首先我会立即捕获数据库当前状态SHOW PROCESSLIST、vmstat和问题SQL的当前执行计划。然后围绕‘数据/统计信息变化’、‘执行计划变更’、‘系统资源争用’、‘外部环境干扰’四个核心方向进行对比排查。具体会检查统计信息是否过时、索引是否失效、是否有锁等待、Buffer Pool是否被冲刷并核对近期是否有相关变更。整个过程会借助EXPLAIN、performance_schema、INNODB锁表等工具定位到根因后再制定针对性的优化或应急方案。”这套方法的价值在于它不仅是面试话术更是在真实生产环境中能让你快速稳住阵脚、找到问题根源的实战指南。建议你将此排查流程固化为团队的应急手册下次告警响起时你就能成为那个最冷静的故障终结者。
