記錄第一次排查生成環(huán)境下，mysql CPU飚高100%占用，應(yīng)用程序拖慢，記錄，解決后占用降為正常。

一、現(xiàn)象：CPU 100%

前天收到使用QA語(yǔ)料構(gòu)建系統(tǒng)同學(xué)反饋，語(yǔ)料系統(tǒng)響應(yīng)緩慢，請(qǐng)求超時(shí)。用 top 一看，MySQL CPU 使用率持續(xù) 100%+，但進(jìn)入容器執(zhí)行 SHOW PROCESSLIST 卻沒有任何活躍查詢，全是 Sleep 狀態(tài)。有點(diǎn)莫名其妙。

top - 08:30:00 up 30 days, load average: 2.50, 2.30, 2.10
MiB Swap: 4096.0 total, 3964.7 free, 131.3 used. 6434.6 avail Mem

PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
1878724 lxd    20   0 2809352 560612  34364 S 102.3   6.9   4:45.44 mysqld
1788 root   20   0 1007448  38620   8908 T   9.6   0.5  7053:32 oneav

二、Redo Log 滿了

既然表面看不到活躍查詢，先看 InnoDB 內(nèi)部狀態(tài)：

SHOW ENGINE INNODB STATUS\G

關(guān)鍵輸出：

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

分析：構(gòu)建 MySQL 容器時(shí)沒有配置 redo log 和 buffer pool 大小，全是默認(rèn)值。

• Redo Log 總?cè)萘績(jī)H 96MB（48MB × 2 個(gè)文件），已寫滿 100%
• Buffer Pool 僅 128MB（8192 頁(yè) × 16KB），命中率僅 94.8%
• 配置嚴(yán)重偏小，導(dǎo)致 InnoDB 頻繁刷臟頁(yè)釋放日志空間，消耗大量 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     |
+---------------------------+-------+

三、第一次優(yōu)化：調(diào)大 Redo Log 和 Buffer Pool

修改配置文件并重啟容器：

[mysqld]
# Redo Log（核心問題）
innodb_log_file_size = 256M
innodb_log_files_in_group = 3     # 總?cè)萘?768MB
# Buffer Pool
innodb_buffer_pool_size = 2G
# 其他優(yōu)化
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

重啟后，MySQL CPU 從 100%+ 降到了 50% 左右，但依然偏高，問題沒有徹底解決。

四、第二次優(yōu)化：開啟慢查詢?nèi)罩?/h2>

懷疑還有隱藏的慢查詢，于是開啟慢查詢?nèi)罩荆?/p>

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = ON;

-- 驗(yàn)證配置
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                         |
+---------------------+----------------------------------+

慢查詢?nèi)罩居涗?/h3>

# 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: 310516
SET timestamp=1780488576;
SELECT count(*) AS count_1 
FROM (SELECT ... 31個(gè)字段 ... FROM datasets WHERE ...) AS anon_1;

# Time: 2026-06-03T12:09:46.161278Z
... 相同 SQL，每 5 秒執(zhí)行一次

分析：這是任務(wù)執(zhí)行日志定時(shí)查詢接口 暴露出的慢 SQL。

• SQL 寫法問題：外層 COUNT(*) 套了一個(gè)子查詢，且子查詢里 SELECT *
• 缺少索引：WHERE 條件涉及三個(gè)字段（user_id, current_stage, created_at），沒有復(fù)合索引
• 高頻執(zhí)行：每 5 秒一次，每次掃描 31 萬(wàn)行，導(dǎo)致數(shù)據(jù)庫(kù)疲于奔命

五、解決方案

1. 添加復(fù)合索引

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

2. 優(yōu)化 SQL 寫法

-- 改造前（慢）：先查全部字段再計(jì)數(shù)
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;

-- 改造后（快）：直接 COUNT
SELECT COUNT(*) FROM datasets 
WHERE user_id = 11 
  AND current_stage = 'question_generate' 
  AND created_at >= '2026-06-01 11:46:14';

3. 應(yīng)用層代碼優(yōu)化

# 不要這樣做（慢）
count = session.query(func.count()).select_from(
    session.query(Dataset).filter(...).subquery()
).scalar()

# 應(yīng)該這樣做（快）
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()

六、優(yōu)化效果對(duì)比

索引使用驗(yàn)證

EXPLAIN SELECT COUNT(*) FROM datasets 
WHERE user_id = 11 
  AND current_stage = 'question_generate' 
  AND created_at >= '2026-06-01 11:46:14'\G

-- 優(yōu)化前：
-- type: ALL (全表掃描)
-- rows: 310516
-- Extra: Using where

-- 優(yōu)化后：
-- type: ref (索引查找)
-- key: idx_user_stage_time (使用索引)
-- rows: 1847
-- Extra: Using index (覆蓋索引)

七、總結(jié)與反思

1. MySQL 默認(rèn) Redo Log 僅 96MB、Buffer Pool 128MB，生產(chǎn)環(huán)境必須調(diào)優(yōu)。
2. SHOW PROCESSLIST 只能看到"此刻"的快照：慢查詢執(zhí)行時(shí)間很短（5 秒），如果查看時(shí)剛好落在空閑期，就會(huì)看到全是 Sleep。慢查詢?nèi)罩?/strong>才是定位問題的關(guān)鍵。
3. COUNT 不要套子查詢：直接 COUNT(*) 即可，避免無謂的全表掃描和大量數(shù)據(jù)傳輸。
4. 為高頻查詢條件建立復(fù)合索引，掃描行數(shù)從 31 萬(wàn)降到 2 千，性能提升 100 倍。
5. 高頻輪詢接口：每 5 秒一次的定時(shí)任務(wù)，加上低效 SQL，會(huì)把數(shù)據(jù)庫(kù)拖垮?？梢钥紤]降低頻率或改用增量查詢。