最近在技术社区看到一个高频问题“一条昨天还跑得飞快的SQL今天突然慢了几十倍数据库CPU直接飙到90%你会怎么查”这绝不是一道简单的面试题而是每个后端、DBA甚至架构师都可能遇到的真实生产事故。它背后隐藏的是一套完整的数据库性能问题诊断体系。很多人第一反应是“加索引”但盲目加索引可能让情况更糟也有人会想到“看执行计划”但如果连问题SQL都定位不准看计划也无从谈起。这篇文章我们不谈空洞的理论直接还原一个真实的线上故障排查现场。我会带你走完从问题感知 → 精准定位 → 根因分析 → 紧急止血 → 彻底修复的全链路。读完本文你将掌握一套可复用的“数据库性能急诊”方法论下次再遇到类似问题你将是团队里最冷静的那个。1. 问题本质为什么SQL会“突然”变慢在深入排查之前我们必须先理解“突然变慢”背后的几种核心可能性。这决定了你排查的起点和方向。1.1 数据量突变这是最直观的原因。例如某个业务表夜间进行了历史数据迁移或批量导入导致表体积暴涨原本高效的索引可能瞬间失效全表扫描拖垮性能。1.2 执行计划改变这是最隐蔽、也最常见的原因。数据库优化器如MySQL的Optimizer会根据表统计信息、索引选择性、系统参数等为SQL选择它认为最优的执行路径即执行计划。当这些因素发生变化优化器可能选择一个截然不同、效率低下的新计划。这就是所谓的“执行计划回退”。1.3 系统资源争抢你的SQL没变但环境变了。可能同一时段有大量报表任务、批量作业启动与你的SQL争抢CPU、内存或磁盘I/O资源。或者数据库服务器本身资源如内存不足导致频繁的磁盘交换。1.4 锁竞争加剧你的SQL需要访问的行或表正被其他长时间运行的事务锁定例如未提交的事务持有行锁或DDL操作持有元数据锁。你的SQL只能等待锁释放从外部看就是“执行变慢”。1.5 网络或中间件问题问题可能不在数据库本身。应用服务器与数据库之间的网络延迟增加或者连接池配置不当如连接数过少导致等待都会表现为SQL执行时间变长。核心判断面对“突然变慢”第一步不是盲目行动而是通过有限的线索如CPU飙高快速缩小怀疑范围。CPU持续90%通常指向数据库层正在“费力地”进行大量计算如排序、聚合或扫描全表/全索引扫描这让我们优先聚焦于数据变化和执行计划改变这两个最可能的内因。2. 建立思维框架五步定位法一套清晰的排查框架能让你在高压下保持思路不乱。我将其总结为“五步定位法”快速止血应急通过数据库管控命令临时终止问题会话恢复服务。精准定位找谁找到消耗资源最多的具体SQL和数据库会话。根因分析为啥深入分析该SQL变慢的根本原因。验证解决咋办制定并实施解决方案。复盘预防后续建立监控避免复发。下面我们结合一个模拟的MySQL环境一步步拆解。3. 环境准备与模拟问题为了让你有身临其境的感觉我们先搭建一个简单的测试环境并“制造”一个经典的执行计划回退问题。3.1 测试环境数据库: MySQL 5.7/8.0 (本文命令通用)工具:mysql命令行客户端或任何数据库管理工具如DBeaver。权限: 需要具备PROCESS,SELECT权限以及查询performance_schema或sys库的权限。3.2 创建测试表与数据我们创建一个订单表并插入一批数据其中status字段的值分布极度不均匀99%为‘COMPLETED’。-- 创建测试数据库和表 CREATE DATABASE IF NOT EXISTS trouble_shooting; USE trouble_shooting; DROP TABLE IF EXISTS orders; CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id INT NOT NULL, amount DECIMAL(10, 2) NOT NULL, status VARCHAR(20) NOT NULL, -- 状态字段 created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_status (status), -- 为status字段创建索引 INDEX idx_user_id (user_id) ) ENGINEInnoDB; -- 插入模拟数据100万条订单其中99万条状态为‘COMPLETED’1万条为‘PENDING’ DELIMITER $$ CREATE PROCEDURE insert_test_data() BEGIN DECLARE i INT DEFAULT 0; WHILE i 990000 DO INSERT INTO orders (order_no, user_id, amount, status) VALUES (CONCAT(ORD, LPAD(i, 8, 0)), FLOOR(RAND()*10000), RAND()*1000, COMPLETED); SET i i 1; END WHILE; SET i 0; WHILE i 10000 DO INSERT INTO orders (order_no, user_id, amount, status) VALUES (CONCAT(ORD, LPAD(990000i, 8, 0)), FLOOR(RAND()*10000), RAND()*1000, PENDING); SET i i 1; END WHILE; END$$ DELIMITER ; -- 执行存储过程数据量较大可能需要一些时间 CALL insert_test_data(); -- 验证数据分布 SELECT status, COUNT(*) FROM orders GROUP BY status;执行后你会看到类似结果--------------------- | status | COUNT(*) | --------------------- | COMPLETED | 990000 | | PENDING | 10000 | ---------------------3.3 “制造”问题SQL我们有一条业务SQL用于查询特定状态的订单。在数据分布均匀或优化器信息准确时它可能走索引。-- 我们的“问题SQL” SELECT * FROM orders WHERE status PENDING ORDER BY created_at DESC LIMIT 10;当statusPENDING时仅有1万条数据走idx_status索引是非常高效的。4. 第一步快速止血与精准定位当报警响起CPU 90%你的首要任务是找到“罪魁祸首”。4.1 查看当前活跃会话与资源消耗使用SHOW PROCESSLIST或查询information_schema/performance_schema。-- 查看所有正在执行的会话MySQL SHOW FULL PROCESSLIST; -- 更详细的信息可以查看正在运行的语句及其资源消耗MySQL 5.7 SELECT p.ID AS process_id, p.USER, p.HOST, p.DB, p.TIME AS execution_time, p.STATE, p.INFO AS sql_text FROM information_schema.PROCESSLIST p WHERE p.COMMAND ! Sleep AND p.TIME 2 -- 查找执行时间超过2秒的 ORDER BY p.TIME DESC LIMIT 10;如果安装了sys库强烈推荐有更直观的命令-- 查看哪些会话消耗了最多的资源 SELECT * FROM sys.session WHERE command ! Sleep ORDER BY cpu_time DESC, rows_examined DESC LIMIT 5;4.2 识别问题SQL从上面的查询结果中你很可能找到那条执行时间 (TIME) 很长、状态 (STATE) 是Sending data、Sorting result或Copying to tmp table的SQL。记下它的完整文本和会话ID (Id)。4.3 紧急终止会话如需如果该SQL正在疯狂消耗资源且可以中断使用KILL命令。KILL [CONNECTION | QUERY] process_id; -- 例如KILL 12345;KILL CONNECTION: 终止整个连接。KILL QUERY: 只终止当前正在执行的查询连接保持。注意生产环境执行KILL需谨慎确认该会话可中断并评估对业务的影响。5. 第二步根因分析——为什么执行计划变了找到问题SQL后我们进入核心环节分析它为什么变慢。关键工具是执行计划EXPLAIN。5.1 获取当前执行计划对问题SQL使用EXPLAIN或EXPLAIN FORMATJSON。EXPLAIN SELECT * FROM orders WHERE status PENDING ORDER BY created_at DESC LIMIT 10;在数据分布正常、统计信息准确的情况下优化器知道statusPENDING只有少量数据应该会选择走idx_status索引。输出可能类似----------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ----------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | NULL | ref | idx_status | idx_status | 82 | const | 10000| 100.00 | Using where; Using filesort | -----------------------------------------------------------------------------------------------------------------------------------type: ref: 使用了非唯一索引扫描是高效的。key: idx_status: 确实使用了我们创建的索引。rows: 10000: 预估扫描行数正确。Extra: Using filesort: 因为要按created_at排序而索引不包含该字段所以需要额外排序。这在数据量小1万时可接受。5.2 模拟“统计信息失真”导致执行计划回退现在我们模拟一个常见场景表统计信息过时或失真。优化器依赖统计信息来判断数据分布如果信息不准它可能做出错误决策。假设由于某种原因如大量数据删除/更新后未及时分析或采样率问题优化器“认为”status字段的值分布很均匀或者idx_status索引选择性很差。我们可以通过“欺骗”优化器来模拟这种情况生产环境不会这么做这里仅为演示-- 告诉优化器这个表有非常多的行远大于实际影响其成本计算 -- 注意这是危险操作仅用于测试环境理解原理 ANALYZE TABLE orders PERSISTENT FOR ALL; -- 在测试环境我们更简单地通过强制不使用索引来观察效果但更真实的模拟是我们直接让优化器选择错误的计划。我们构造一个让优化器“误判”的场景查询一个它认为数据量很大的值。-- 假设优化器错误地认为 ‘COMPLETED’ 和 ‘PENDING’ 数据量差不多甚至认为‘PENDING’更多。 -- 我们无法直接控制但可以观察当它选择全表扫描时的计划。 -- 使用 FORCE INDEX 和 IGNORE INDEX 来对比 EXPLAIN SELECT * FROM orders IGNORE INDEX(idx_status) WHERE status PENDING ORDER BY created_at DESC LIMIT 10;此时执行计划可能变成------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 1000000 | 10.00 | Using where; Using filesort | -------------------------------------------------------------------------------------------------------------------------------灾难发生了type: ALL: 全表扫描。key: NULL: 未使用任何索引。rows: 1000000: 优化器认为需要扫描100万行实际只需扫描1万行符合条件的但全表扫描成本更高。filtered: 10.00: 优化器预估只有10%的数据符合条件但它依然选择了全表扫描因为成本计算模型认为这比回表更“划算”基于错误的统计信息。这就是CPU飙升至90%的元凶数据库服务器正在笨拙地扫描100万行数据对每一行应用WHERE条件然后对结果进行排序只为取出10条。5.3 如何查看和更新统计信息在MySQL中表统计信息存储在mysql.innodb_index_stats和mysql.innodb_table_stats对于InnoDB。你可以手动更新-- 更新表的统计信息 ANALYZE TABLE orders; -- 在MySQL 8.0中可以使用更激进的采样 ANALYZE TABLE orders PERSISTENT FOR ALL;最佳实践对于数据变化频繁的表如每日增量大的业务表应定期或在数据量发生重大变化后执行ANALYZE TABLE。许多公司将其作为夜间维护任务的一部分。6. 第三步解决方案与验证找到根因后我们有几个解决方案选择取决于具体情况。方案一更新统计信息最直接如果确认是统计信息过时立即执行。ANALYZE TABLE orders;然后再次检查执行计划看是否恢复为走索引。方案二使用SQL提示临时强制如果更新统计信息后优化器依然选错或者情况紧急需要立刻恢复服务可以使用FORCE INDEX提示强制使用正确索引。SELECT * FROM orders FORCE INDEX(idx_status) WHERE status PENDING ORDER BY created_at DESC LIMIT 10;注意FORCE INDEX是硬编码如果未来数据分布发生变化例如PENDING订单变成绝大多数这个提示可能反而有害。它应作为临时止血方案并需要后续优化。方案三优化索引或查询根本解决有时问题出在索引设计或SQL本身。覆盖索引我们的查询是SELECT *即使走了idx_status也需要回表查询所有列。如果业务上只需要部分列可以创建覆盖索引。-- 例如如果只需要id, order_no, created_at CREATE INDEX idx_status_created_at ON orders(status, created_at); -- 然后查询改为 SELECT id, order_no, created_at FROM orders WHERE status PENDING ORDER BY created_at DESC LIMIT 10;这样Extra列会显示Using index效率最高。优化排序ORDER BY created_at DESC导致filesort。如果created_at和status联合索引且查询能利用索引的最左前缀可以避免排序。但本例中WHERE status ? ORDER BY created_at创建(status, created_at)的联合索引是有效的。方案四重写查询在某些复杂情况下拆分查询或改变写法可能更优。例如对于分页深度查询不要使用LIMIT 1000000, 10而是使用WHERE id ? LIMIT 10。7. 第四步深入排查清单其他可能性如果更新统计信息和强制索引都无效或者CPU高但找不到明显慢SQL你需要扩大排查范围。7.1 检查锁等待-- 查看当前锁信息 (MySQL) SELECT * FROM information_schema.INNODB_LOCKS; SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 使用 sys 库更直观 SELECT * FROM sys.innodb_lock_waits;长时间锁等待会导致SQL“卡住”从应用看就是执行时间变长。7.2 检查系统资源磁盘I/O使用iostat,iotop命令查看磁盘是否成为瓶颈。内存检查innodb_buffer_pool_size配置是否合理缓冲池命中率是否过低。SHOW GLOBAL STATUS LIKE innodb_buffer_pool_read%; -- 计算命中率 (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%命中率低于99%可能意味着内存不足需要频繁从磁盘读数据。7.3 检查慢查询日志确保慢查询日志已开启并分析在问题时间段内记录的慢SQL。-- 查看慢查询日志配置 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time;7.4 检查是否有隐式类型转换WHERE status 123status是字符串类型会导致索引失效触发全表扫描。确保查询条件的数据类型与列定义一致。7.5 检查连接池与网络应用侧连接池配置过小会导致大量请求排队等待数据库连接表现为响应时间变长。同时检查网络延迟和丢包率。8. 最佳实践与长效预防机制一次救火成功是运气建立机制才能避免下次火灾。8.1 监控告警体系数据库层面监控CPU使用率、连接数、QPS、TPS、慢查询数量、InnoDB缓冲池命中率、锁等待数量等。SQL层面部署像Prometheus Grafana mysqld_exporter这样的监控栈或使用商业APM工具如Arthas, SkyWalking抓取并聚合慢SQL拓扑。设置智能基线告警不仅监控绝对值如CPU80%更监控增长率如CPU在5分钟内飙升50%。8.2 上线前SQL审核将EXPLAIN作为代码评审的必需步骤。重点审查type列避免ALL全表扫描尽量达到ref,range,const。rows列预估扫描行数是否过大。Extra列警惕Using filesort,Using temporary。使用开源SQL审核工具如SOAR、Yearning。8.3 定期维护与优化定期更新统计信息对核心表设置定时任务如每周一次或在批量作业后。定期分析慢查询日志使用pt-query-digest等工具进行聚合分析找出“最费资源”的SQL进行优化。索引管理定期审查冗余、未使用的索引。创建索引遵循“最左前缀原则”考虑索引合并与覆盖索引。8.4 架构层面考虑读写分离将报表类、分析类慢查询导向只读从库。缓存对热点静态数据使用Redis等缓存减少数据库压力。分库分表对于数据量持续高速增长的表提前规划水平拆分。9. 总结从救火队员到防火专家回到开头的面试题。现在你可以给出一个结构清晰、体现深度的回答应急响应首先通过SHOW PROCESSLIST或sys.session快速定位消耗CPU最高的会话和SQL必要时使用KILL命令紧急恢复服务。根因排查对问题SQL执行EXPLAIN重点观察执行计划是否改变是否从索引扫描变成了全表扫描。检查type,key,rows,Extra字段。分析原因最常见原因表统计信息过时导致优化器成本计算错误。解决方案ANALYZE TABLE。数据量突变检查表数据量是否因批量操作激增。索引失效检查是否有隐式类型转换、函数操作导致索引未命中。系统资源检查磁盘I/O、内存缓冲池命中率。锁竞争检查是否有阻塞的锁等待。解决方案短期更新统计信息、使用FORCE INDEX提示。长期优化索引设计如使用覆盖索引、重写低效SQL、调整数据库参数。预防机制建立SQL审核流程、部署慢查询监控与告警、定期进行数据库健康检查。这条SQL从50毫秒到5秒的蜕变本质上是一次“执行计划叛逃”。而你的价值不仅在于能用工具找到它更在于理解优化器为何“叛变”并通过一系列工程化手段构建一个让SQL性能稳定、可预测的系统环境。从被动救火到主动防火这才是高级工程师与普通开发者的分水岭。
