从15分钟到30秒：一次MySQL CPU飙升的实战排查全记录
目录导读
- 故障初现 – 生产环境CPU告警，服务响应迟缓
- 第一轮排查 – 系统层面与慢查询日志的初步定位
- 深入分析 – 通过
SHOW PROCESSLIST与EXPLAIN锁定嫌疑SQL - 根因揭秘 – 索引失效与隐式类型转换的“组合拳”
- 优化方案 – 索引重构、SQL改写与参数调优
- 复盘与预防 – 监控体系、规范制定与压测演练
- 常见问题FAQ – 关于CPU飙升的5个高频疑问
故障初现
某天上午10:15，运维监控大屏突然飘红：核心订单库的CPU使用率从5%直线飙升至98%，持续超过15分钟，随之而来的是应用层超时告警，用户反馈“提交订单卡顿”,DBA群内瞬间炸锅。
小知识：MySQL的CPU飙升通常由三类原因引起——①低效SQL频繁执行（占80%以上）；②连接数爆满导致线程切换开销激增；③内部锁竞争（如元数据锁、行锁等待）。
第一轮排查：系统层面与慢查询日志
系统资源确认
top -Hp <mysqld_pid> # 查看线程CPU占用
结果显示多个mysqld线程CPU占用超过80%,确认问题源于数据库内部。
慢查询日志分析
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; -- 记录超过1秒的SQL
查看日志后发现，一个高频出现的SQL平均执行时间3秒，被调用次数每分钟1200次：
SELECT * FROM orders WHERE user_phone = '138****1234' AND order_status = 1 ORDER BY create_time DESC LIMIT 20;
关键指标观察
Threads_running：持续在30-50之间（正常应<10）Innodb_row_lock_waits：每秒钟增长数千次Buffer_pool_hit_rate：98%（基本正常,排除内存问题）
深入分析：PROCESSLIST与EXPLAIN
抓取当前运行会话
SHOW FULL PROCESSLIST;
发现大量处于Sending data状态的会话，且Time列均超过3秒,执行的正是上述SQL。
执行计划解读（核心证据）
EXPLAIN SELECT * FROM orders WHERE user_phone = '138****1234' AND order_status = 1 ORDER BY create_time DESC LIMIT 20;
| 列名 | 实际值 | 问题诊断 |
|---|---|---|
| type | ALL |
全表扫描！(理想为ref或range) |
| key | NULL |
未使用任何索引 |
| rows | 500万 | 扫描了整个订单表 |
| Extra | Using filesort |
额外文件排序，性能雪上加霜 |
索引结构检查
SHOW INDEX FROM orders;
发现存在单列索引idx_user_phone和idx_order_status，但没有联合索引，更严重的是——user_phone字段定义为VARCHAR(20)，而代码中传入的是数值类型（Java Long类型）。
根因揭秘：隐式类型转换 + 索引失效
为什么走了全表扫描？
MySQL优化器对WHERE user_phone = 138****1234（数字）与VARCHAR列比较时，会自动将列转换为数字，导致索引失效,验证如下：
EXPLAIN SELECT * FROM orders WHERE user_phone = 13800001234; -- 数字类型触发隐式转换 -- type=ALL, rows=500万 EXPLAIN SELECT * FROM orders WHERE user_phone = '13800001234'; -- 字符串正确 -- type=ref, rows=20
额外的排序压力
即使user_phone正确走索引，ORDER BY create_time仍需对每个用户的所有订单排序，当某个大客户有数万订单时，Using filesort仍会导致性能瓶颈。
完整的性能损耗链条
- 全表扫描：5MB行扫描 → CPU大量用于行解析
- 隐式转换：每次比较都需将字符转数值 → 额外CPU计算
- 临时文件排序：
Using filesort触发磁盘临时表 → IO与CPU双重压力 - 高频调用：每分钟1200次 × 2.3秒 = 并发堆积
优化方案：三重组合拳
第一步：SQL改写（立即生效）
将代码中的参数类型强制为字符串：
// 错误示例（触发隐式转换）
orderMapper.selectByPhone(13800001234L);
// 正确示例
orderMapper.selectByPhone("13800001234");
同时增加覆盖索引,避免回表：
ALTER TABLE orders ADD INDEX idx_phone_status_time (user_phone, order_status, create_time);
第二步：SQL逻辑增强（消除filesort）
利用覆盖索引自带排序属性，去掉ORDER BY（因为联合索引已按create_time有序）：
SELECT * FROM orders WHERE user_phone = '138****1234' AND order_status = 1 LIMIT 20; -- 联合索引天然有序，无需显式排序
第三步：参数与架构调优
- 调整
tmp_table_size和max_heap_table_size，避免临时文件落盘 - 对于超大客户的查询，增加分页游标机制，避免一次扫描全部订单
- 引入Redis缓存热数据，降低数据库读压力
优化后效果对比
| 指标 | 优化前 | 优化后 |
|---|---|---|
| 执行时间 | 3秒 | 18毫秒 |
| 扫描行数 | 500万 | 20 |
| CPU使用率 | 98% | 12% |
| Threads_running | 40+ | 2 |
复盘与预防：构建三层防护
监控层
- 建立CPU水位告警（阈值80%持续5分钟）
- 新增
performance_schema监控，记录高频SQL的执行计划变化 - 设置索引失效告警：定期扫描
sys.statements_with_full_table_scans
规范层
- 代码审查强制要求：所有WHERE字段必须与表结构类型完全一致
- 每一次新索引上线，必须通过
EXPLAIN验证type级别至少为range - 订单表禁止无条件下
SELECT *
压测层
- 每季度进行全链路压测，模拟双11流量（QPS=5000）
- 压测场景包含：模糊查询、多条件组合、深度分页等边界情况
- 压测结果必须输出执行计划变更报告
常见问题FAQ
Q1：CPU飙升时，为什么不能直接kill掉SQL线程？
A：kill只能终止执行中的查询，但如果是高频调用模式（如每秒钟100次），新请求会立即填补空缺，问题仍然存在,必须先定位根因并优化SQL或索引。
Q2：如何快速判断是索引问题还是锁问题？
A：在SHOW PROCESSLIST中，如果大量线程是Sending data且Time长，多为索引或SQL问题；如果是Waiting for table metadata lock，则是锁冲突，也可观察Innodb_row_lock_current_waits指标。
Q3：隐式类型转换是否只发生在数值和字符串之间？
A：不只，例如VARCHAR与DATETIME比较、CHAR与VARCHAR对比，都可能导致索引失效，最稳妥的方法是用EXPLAIN检查type列。
Q4：覆盖索引为什么能消除filesort？
A：当索引包含所有查询字段时，索引本身就是有序的，例如(user_phone, order_status, create_time)，优化器可以直接按索引顺序读取前20条记录,无需再额外排序。
Q5：优化后CPU正常，但偶尔还有轻微波动，正常吗？
A：正常，MySQL的定期后台任务（如purge、统计信息更新）会短暂占用CPU，只要波动幅度在20%以内且快速回落，无需处理，可监控Threads_connected判断是否异常。
最后提醒：任何性能优化完成后，必须观察至少一个完整的业务周期（通常24小时），确认慢查询日志清零且CPU峰值显著下降，才算真正闭环，建议将本次案例沉淀为团队内部故障手册,避免同类问题二次发生。
