MySQL CPU 100% 疑難排解實戰：從參數調優到索引優化的完整過程

案例背景

• 環境：Windows 2003 + IIS + PHP + MySQL
• 症狀：mysqld-nt.exe CPU 長期 100%
• 架構：10 個資料庫對應 10 個網站
• 關鍵發現：停用「網站 A」→ CPU 立即下降；啟用 → 立即飆升

網站 A 資料概況

• 七日平均：2,000 IP / 30,000 PV
• 資料表：39 張
• 記錄數：60.1 萬筆
• 佔用空間：45 MB
• 結論：資料量不大，不應導致 CPU 100%

排解步驟完整記錄

步驟 1：檢視並調整配置參數

-- 匯出目前配置
mysqld.exe --help > output.txt

-- 發現 tmp_table_size = 32M (預設)
-- 修改 my.ini
[mysqld]
tmp_table_size = 200M

結果：CPU 從 100% 直線微降至 97%-100% 震盪。有改善但未根治。

步驟 2：分析高頻 SQL（核心突破）

-- 進入 MySQL Shell
SHOW PROCESSLIST;
-- 每秒重複執行 2-3 次觀察

發現網站 A 兩條高頻 SQL：

-- SQL 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;

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

步驟 3：檢查表結構找出缺失索引

SHOW COLUMNS FROM _myuser;
SHOW COLUMNS FROM _mydata;
SHOW COLUMNS FROM _mydata_body;
SHOW COLUMNS FROM _mydata_key;

問題 1：_mydata 缺 userid 索引

SQL 1 中 LEFT JOIN _myuser ... ON t1.userid = t3.userid 使用 _mydata.userid 做 JOIN 條件，但該欄無索引。

ALTER TABLE `_mydata` ADD INDEX (`userid`);

結果：CPU 降至 80% 左右。

問題 2：_mydata_key 缺 keywords 索引

SQL 2 對 33 萬筆資料在無索引的 keywords 欄位做全文掃描匹配。

ALTER TABLE `_mydata_key` ADD INDEX (`keywords`);

結果：CPU 立即降至 50%-70% 震盪。

步驟 4：擴大檢查其他資料庫

發現同主機的幾個 Discuz 論壇也有類似缺索引問題，順手修復，CPU 進一步下降。

核心經驗總結

1. 調大 tmp_table_size

• 預設 32M，臨時表超過會寫入磁碟（MyISAM），大幅降速
• 大量 GROUP BY、ORDER BY、複雜查詢易產生臨時表
• 官方建議：觀察 SHOW PROCESSLIST 中 Using temporary 且耗時久的查詢，適度調大
• 記憶體分配是 per-thread，避免設得過大導致 OOM

2. 為 WHERE/JOIN/ORDER BY/MAX/MIN 條件欄位建立索引

3. 多欄索引 vs 單欄索引選擇

WHERE col1=val1 AND col2=val2

• 有 (col1, col2) 複合索引 → 直接取行
• 只有單欄索引 → 優化器選擇選擇性較高（過濾行數較少）的索引

開發階段檢查清單

1. 建表時根據查詢模式預先規劃索引
2. 上線前用 EXPLAIN 驗證執行計畫
3. 定期審視 SHOW PROCESSLIST 與慢查詢日誌
4. 監控 Created_tmp_disk_tables 狀態變數

關鍵指令速查

-- 即時觀察執行緒
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 檢視表結構
SHOW COLUMNS FROM tbl_name;
DESC tbl_name;

-- 增加索引
ALTER TABLE tbl_name ADD INDEX idx_name (col_name);
CREATE INDEX idx_name ON tbl_name (col_name);

-- 調整配置 (my.cnf/my.ini)
[mysqld]
tmp_table_size = 200M
max_heap_table_size = 200M  -- 建議同步調整