MySQL CPU 100% 故障排查實戰：從參數調優到索引缺失根因定位

案例背景

Windows 2003 + IIS + PHP + MySQL 環境，10 個網站共用單一 MySQL 實例。某網站（日 IP 2000、PV 3萬、資料量 60 萬筆/45MB）啟用後 mysqld-nt.exe CPU 長期 100%，停用該站 CPU 即降正常。

排查時間軸與關鍵發現

第一階段：調大 tmp_table_size 觀察效果

• 發現預設 tmp_table_size=32M，臨時表超限寫磁碟導致效能抖動
• 修改 my.ini：tmp_table_size=200M
• 結果：CPU 從持平 100% 降為 97%~100% 震盪，有改善但未根治

第二階段：SHOW PROCESSLIST 定位高頻 SQL

mysql> SHOW PROCESSLIST;
-- 反覆出現兩條慢查詢：
-- Query 1
SELECT t1.pid, t2.userid, t3.count, t1.date
FROM _mydata AS t1
LEFT JOIN _myuser AS t3 ON t1.userid = t3.userid
LEFT JOIN _mydata_body AS t2 ON t1.pid = t3.pid
ORDER BY t1.pid LIMIT 0, 15;

-- Query 2
SELECT COUNT(*)
FROM _mydata AS t1, _mydata_key AS t2
WHERE t1.pid = t2.pid AND t2.keyWords = '孔雀';

第三階段：SHOW COLUMNS 發現索引缺失

第四階段：建立索引驗證效果

ALTER TABLE `_mydata` ADD INDEX (`userid`);
-- CPU 降至 80% 左右

ALTER TABLE `_mydata_key` ADD INDEX (`keyWords`);
-- CPU 降至 50%~70% 震盪，目標 SQL 消失

第五階段：順手優化同機其他 Discuz 論壇表

補齊相同模式的缺失索引，CPU 進一步下降至正常水位。

核心經驗總結

1. 臨時表參數調優

• tmp_table_size、max_heap_table_size 成對調大，避免臨時表落盤
關注 Created_tmp_disk_tables 狀態變數，持續增長即需調大

2. 索引設計鐵律

WHERE、JOIN、ORDER BY、GROUP BY、MAX()/MIN() 條件欄位必須建立索引

• 索引讓 MySQL 以 B-Tree 快速定位，避免全表掃描
• 複合索引遵循「最左前綴」原則
• 關聯查詢雙邊欄位型別、編碼、長度必須一致

3. 系統化排查流程

1. 參數基線檢查（tmp_table_size、innodb_buffer_pool_size 等）
2. SHOW PROCESSLIST 高頻捕捉慢 SQL
3. EXPLAIN 分析執行計畫，找 type=ALL（全表掃描）
4. SHOW INDEX / SHOW COLUMNS 對照條件欄位，補索引
5. 驗證效果，迭代優化

結語

本案例中，單純調參數僅治標；補齊 JOIN/WHERE 條件欄位的索引才是治本。開發階段即納入索引設計評審，配合上線後慢查詢日誌持續巡檢，才能從根源避免 CPU 100% 故障。