MySQL CPU 100% 實戰優化：從 tmp_table_size 到索引缺失的完整排查過程

環境與現象

• 架構：Windows 2003 + IIS + PHP + MySQL
• 症狀：mysqld-nt.exe CPU 長期 100%，停用某網站 (網站 A) 後立即下降，啟用即飆升。
• 網站 A：日均 IP 2000，PV 3 萬，資料庫 39 張表，60 萬筆，45 MB。

優化步驟與發現

1. 調整 tmp_table_size (微調)

[mysqld]
tmp_table_size=200M

重啟 MySQL 後 CPU 從持續 100% 微降至 97~100%，證明參數調整有效但非核心原因。

2. 使用 SHOW PROCESSLIST 定位高頻 SQL

每秒刷新兩次，發現網站 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. 檢查表結構 → 發現索引缺失

• _mydata 只有主鍵 pid，userid 無索引 → JOIN 時全表掃描。
• _mydata_key 33 萬筆，keywords 無索引 → 文字搜尋全表掃描。

4. 建立索引 → CPU 顯著下降

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

ALTER TABLE `_mydata_key` ADD INDEX (`keywords`);
-- CPU 降至 50~70%

後續順手處理幾個 Discuz 論壇表的同類問題，CPU 進一步降低穩定。

核心經驗總結

配置參數調優

• tmp_table_size：預設 32M，臨時表超過會轉磁碟 (MyISAM)，大量 GROUP BY / 複雜查詢建議加大 (如 64M~256M)，減少磁碟 I/O。

索引設計原則 (MySQL 官方文件)

1. WHERE、JOIN、ORDER BY、GROUP BY、MAX()/MIN() 條件欄位必須建立索引。
2. 索引讓 MySQL 直接定位資料頁，避免全表掃描。1000 筆資料有索引比無索引快 100 倍以上。
3. 所有索引 (PRIMARY、UNIQUE、INDEX) 均以 B+Tree 儲存。
4. 複合索引遵循「最左前綴」原則：INDEX (col1, col2) 可支援 WHERE col1=... AND col2=... 或 ORDER BY col1, col2。
5. 若查詢只需索引欄位 (覆蓋索引)，可直接從索引樹取回資料，不讀資料檔，極大提升效能。

開發階段建議

資料表設計時須通盤考慮查詢模式，預先規劃索引；上線前用 EXPLAIN 分析執行計畫，確認 type 非 ALL (全表掃描)。