先分享一个真实的排障经历。前天团队收到反馈，语料系统响应异常缓慢，请求直接超时。登录服务器用top命令查看，发现 MySQL 的 CPU 使用率稳稳地锚定在 100% 以上，但进入容器执行SHOW PROCESSLIST却让人困惑——没有任何活跃查询，全是Sleep状态。
这种表象确实有些诡异：表面看似平静，底层却在疯狂消耗 CPU 资源。不过别担心，排查这类问题其实有章可循，下面我们就一步步拆解。
一、现象：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 满了
直接执行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 |+---------------------------+-------+
三、第一次优化：调大 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 依然偏高，不够正常。
四、第二次优化：开启慢查询日志
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 万行，数据库只能疲于奔命
五、解决方案
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()
六、优化效果对比
| 指标 | 优化前 | 优化后 | 改善 |
|---|---|---|---|
| 扫描行数 | 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 默认的 Redo Log 仅 96MB、Buffer Pool 仅 128MB，生产环境务必根据实际负载进行调优。
SHOW PROCESSLIST只能看到“此刻”的快照：慢查询执行时间很短（5 秒），如果查看时刚好落在空闲期，就会看到全是Sleep状态。慢查询日志才是定位问题的关键工具。- COUNT 不要嵌套子查询：直接使用
COUNT(*)即可，避免无谓的全表扫描和大量数据传输。 - 为高频查询条件建立复合索引，扫描行数从 31 万降到 2 千，性能提升 100 倍。
- 高频轮询接口：每 5 秒一次的定时任务，配合低效 SQL，会严重拖垮数据库。建议降低轮询频率或改用增量查询方式。
最终，MySQL CPU 稳定在 5% 以下，接口响应时间也降到了毫秒级。
