MySQL 服務進程 CPU 100% 系統性排查與解決實戰

現象

Windows 2003 + IIS + PHP + MySQL，mysqld-nt.exe CPU 持續 100%。停用某站點（日 IP 2000，PV 3萬）即降下，啟用即升高。該站 DB 39 表、60 萬筆、45 MB，理論不應佔滿 CPU。

排查步驟與發現

1. 參數調優：tmp_table_size

# 導出當前配置
mysqld.exe --help > output.txt
# 發現 tmp_table_size=32M (預設)

# 修改 my.ini
[mysqld]
tmp_table_size=200M
# 重啟服務

效果：CPU 從 100% 直線 → 97%~100% 震盪，有改善但未解決。

2. 定位高頻 SQL：SHOW PROCESSLIST

mysql> SHOW PROCESSLIST;
# 反覆執行，發現兩條高頻 SQL

3. SQL 一：JOIN 缺索引

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

_mydata 只有 PK (pid)，userid 無索引，JOIN 時全表掃描。
→ ALTER TABLE `_mydata` ADD INDEX (`userid`);
CPU 降至 80%。

4. SQL 二：WHERE 文字搜尋無索引

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

_mydata_key 33 萬行，keywords 無索引，全表掃描字串比對。
→ ALTER TABLE `_mydata_key` ADD INDEX (`keywords`);
CPU 降至 50%~70%。

5. 連帶優化：Discuz 論壇表

同機器跑多個 Discuz，套用前文 8 條補索引，CPU 進一步降至正常區間。

經驗總結：兩大核心優化方向

方向一：調大 tmp_table_size

• 預設 32M，臨時表超過即落盤（MyISAM），產生 The table ... is full 錯誤與大量磁碟 I/O。
• 大量 GROUP BY、ORDER BY、UNION、DISTINCT 產生臨時表時需調大。
• 官方建議：觀察 Created_tmp_disk_tables / Created_tmp_tables 比例，磁碟臨時表佔比高時調大。

方向二：為條件欄位建立索引

必建索引場景：

• WHERE col = ...
• JOIN ON t1.col = t2.col
• ORDER BY col / GROUP BY col
• MAX(col) / MIN(col)

原理：B-Tree 索引將 O(N) 全掃降為 O(log N) 定位。1000 行表快 100 倍，百萬行快 10 萬倍。

多欄索引策略：INDEX (col1, col2) 可服務 WHERE col1=... AND col2=...；單欄索引由優化器選擇選擇性最高者。

開發階段檢查清單

1. 建表時同步規劃索引（EXPLAIN 驗證）。
2. 上線前跑負載測試，觀察 SHOW PROCESSLIST 與慢查詢日誌。
3. 定期 ANALYZE TABLE 更新統計資訊，防止優化器誤判。
4. 避免 SELECT *、隱式型別轉換、LIKE '%keyword%' 導致索引失效。