MySQL慢查询导致CPU飙高的完整排查指南

先说一个排障故事。前天团队收到反馈，语料系统响应特别慢，请求直接超时。上机器用top一看，MySQL CPU 使用率稳稳地锚在100%以上，但进入容器执行SHOW PROCESSLIST却傻眼了——没有任何活跃查询，全是Sleep状态。

这现象说实话有点诡异，表面安静如水，底下却在疯狂烧CPU。不过别急，排查这种MySQL CPU飙高问题其实有章可循。

一、MySQL CPU飙高现象：占用100%

具体现象是这样的：top里mysqld进程CPU占用102.3%，内存用了560MB，但进去看活跃线程，全是Sleep。

top - 08:30:00 up 30 days, load a verage: 2.50, 2.30, 2.10MiB Swap: 4096.0 total, 3964.7 free, 131.3 used. 6434.6 a vail MemPID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND1878724 lxd    20   0 2809352 560612  34364 S 102.3   6.9   4:45.44 mysqld1788 root   20   0 1007448  38620   8908 T   9.6   0.5  7053:32 onea v

这种时候，第一反应肯定是先看InnoDB内部的健康状态。

二、Redo Log 满了导致CPU飙升

直接执行SHOW ENGINE INNODB STATUS\G，果然发现了关键线索：

LOG---Log capacity                 104857600Log capacity used            104857600   ← 100% 满！...BUFFER POOL AND MEMORY---Buffer pool size   8192Buffer pool hit rate 948 / 1000          ← 仅 94.8%（正常 >99%）...ROW OPERATIONS---175526.47 reads/s                        ← 异常高

问题原因其实很典型——构建MySQL容器时，redo log和buffer pool大小全用的默认值。Redo Log总容量只有96MB（48MB×2个文件），已经写满100%；Buffer Pool只有128MB（8192页×16KB），命中率掉到94.8%。配置偏小带来的后果就是InnoDB要频繁刷脏页来释放日志空间，CPU就被耗在上头了。

SHOW VARIABLES LIKE 'innodb_log_file_size';+----------------------+----------+| Variable_name        | Value    |+----------------------+----------+| innodb_log_file_size | 50331648 |   -- 48MB+----------------------+----------+SHOW VARIABLES LIKE 'innodb_log_files_in_group';+---------------------------+-------+| Variable_name             | Value |+---------------------------+-------+| innodb_log_files_in_group | 2     |+---------------------------+-------+

三、第一次MySQL优化：调大 Redo Log 和 Buffer Pool

既然定位到了原因，那就动手。调整配置后重启容器：

[mysqld]# Redo Log（核心问题）innodb_log_file_size = 256Minnodb_log_files_in_group = 3     # 总容量 768MB# Buffer Poolinnodb_buffer_pool_size = 2G# 其他优化innodb_flush_log_at_trx_commit = 2innodb_flush_method = O_DIRECT

重启之后，CPU从100%+降到了50%左右。虽然效果明显，但问题并没彻底解决——CPU还是偏高，不正常。

四、第二次MySQL优化：开启慢查询日志定位低效SQL

CPU没完全降下来，说明还有隐藏问题。这时候慢查询日志就是最好的侦查工具。

SET GLOBAL slow_query_log = ON;SET GLOBAL long_query_time = 2;SET GLOBAL log_queries_not_using_indexes = ON;-- 验证配置SHOW VARIABLES LIKE 'slow_query_log%';+---------------------+----------------------------------+| Variable_name       | Value                            |+---------------------+----------------------------------+| slow_query_log      | ON                               || slow_query_log_file | /var/lib/mysql/slow.log          || long_query_time     | 2.000000                         |+---------------------+----------------------------------+

日志一开，问题立马浮出水面：

# Time: 2026-06-03T12:09:41.527275Z# User@Host: swust[swust] @ [172.18.0.3] Id: 398# Query_time: 4.782904  Lock_time: 0.000003 Rows_sent: 1  Rows_examined: 310516SET timestamp=1780488576;SELECT count(*) AS count_1 FROM (SELECT ... 31个字段 ... FROM datasets WHERE ...) AS anon_1;# Time: 2026-06-03T12:09:46.161278Z... 相同 SQL，每 5 秒执行一次

所有线索都串起来了。这个SQL来自任务执行日志的定时查询接口，存在三个典型问题：

• SQL写法问题：外层COUNT(*)套了一个子查询，子查询里还SELECT *
• 缺少索引：WHERE条件涉及user_id、current_stage、created_at三个字段，完全没有复合索引
• 高频执行：每5秒一次，每次扫描31万行，数据库只能疲于奔命

五、MySQL优化解决方案

1. 添加复合索引

USE qa_gen;CREATE INDEX idx_user_stage_time ON datasets(user_id, current_stage, created_at);

2. 优化 SQL 写法

-- 改造前（慢）：先查全部字段再计数SELECT COUNT(*) FROM (    SELECT * FROM datasets     WHERE user_id = 11       AND current_stage = 'question_generate'       AND created_at >= '2026-06-01 11:46:14') AS t;-- 改造后（快）：直接 COUNTSELECT COUNT(*) FROM datasets WHERE user_id = 11   AND current_stage = 'question_generate'   AND created_at >= '2026-06-01 11:46:14';

3. 应用层代码优化

# 不要这样做（慢）count = session.query(func.count()).select_from(    session.query(Dataset).filter(...).subquery()).scalar()# 应该这样做（快）count = session.query(func.count(Dataset.id)).filter(    Dataset.user_id == 11,    Dataset.current_stage == 'question_generate',    Dataset.created_at >= '2026-06-01 11:46:14').scalar()

六、MySQL优化效果对比

指标	优化前	优化后	改善
扫描行数	310,516 行	~2,000 行	减少 99%
查询时间	5.08 秒	0.05 秒	快 100 倍
MySQL CPU	48–56%	<5%	恢复正常
数据传输量	62 MB/次	<1 KB/次	减少 99.9%

索引使用验证

EXPLAIN SELECT COUNT(*) FROM datasets WHERE user_id = 11   AND current_stage = 'question_generate'   AND created_at >= '2026-06-01 11:46:14'\G-- 优化前：-- type: ALL (全表扫描)-- rows: 310516-- Extra: Using where-- 优化后：-- type: ref (索引查找)-- key: idx_user_stage_time (使用索引)-- rows: 1847-- Extra: Using index (覆盖索引)

七、MySQL CPU飙高排查总结与反思

1. MySQL 默认 Redo Log 仅 96MB、Buffer Pool 128MB，生产环境必须调优。
2. SHOW PROCESSLIST只能看到"此刻"的快照：慢查询执行时间很短（5秒），如果查看时刚好落在空闲期，就会看到全是Sleep。慢查询日志才是定位问题的关键。
3. COUNT 不要套子查询：直接COUNT(*)即可，避免无谓的全表扫描和大量数据传输。
4. 为高频查询条件建立复合索引，扫描行数从31万降到2千，性能提升100倍。
5. 高频轮询接口：每5秒一次的定时任务，加上低效SQL，会把数据库拖垮。可以考虑降低频率或改用增量查询。

最终，MySQL CPU稳定在5%以下，接口响应也降到了毫秒级。