MySQL 服務器 CPU 占用 100% 完整排查與優化實戰

問題背景

某網站日均 IP 2000，PV 3 萬，資料庫僅 39 張表、60 萬筆資料、45 MB，按理說 MySQL 不會佔用極高 CPU，但實際卻長期維持 100%。

第一步：調整 tmp_table_size 參數

先導出當前配置：

d:\web\mysql> mysqld.exe --help > output.txt

發現 tmp_table_size 為預設 32M，修改 my.ini：

[mysqld]
tmp_table_size=200M

重啟服務後 CPU 略微下降，波動在 97%~100%，證明調整有效但未徹底解決。

第二步：鎖定高頻 SQL

進入 MySQL Shell 執行：

mysql> show processlist;

每秒刷新兩次，發現網站 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 = '孔雀'

第三步：分析表結構並補齊索引

檢查三張表結構：

mysql> show columns from _myuser;
mysql> show columns from _mydata;
mysql> show columns from _mydata_body;

發現 _mydata 表只有主鍵 pid，userid 無索引，而 JOIN 條件正是 t1.userid = t3.userid。立即添加：

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

CPU 瞬間降至 80% 左右。

再檢查 _mydata_key，33 萬筆資料，keyWords 無索引，全表掃描極耗 CPU。添加：

mysql> ALTER TABLE `_mydata_key` ADD INDEX (`keyWords`);

CPU 進一步降至 50%~70% 震盪，show processlist 中網站 A 的 SQL 已極少出現。

第四步：順手優化同機其他 Discuz 論壇

同樣補齊相關表索引，CPU 再次下降。（Discuz 具體優化另文詳述）

經驗總結

1. 增加 tmp_table_size

預設 32M，臨時表超過會寫磁盤導致 The table tbl_name is full 錯誤。大量 GROUP BY 查詢時建議調大。官方說明：tmp_table_size 決定記憶體臨時表最大值，超過則轉 MyISAM 磁盤表。優化查詢避免臨時表為上策，不可避免時確保臨時表留在記憶體。觀察 processlist 中長時間 Using temporary 的查詢可作為調大信號。曾有案例從 32MB 調至 64MB 立竿見影，查詢加快導致活躍執行緒減少，整體受益。

2. 為 WHERE、JOIN、MAX()、MIN()、ORDER BY 條件列建立索引

索引用於快速定位特定值的行。無索引時 MySQL 必須從第一筆掃描全表，表越大越慢。有索引則能直接定位資料文件中間，無需掃描所有資料。1000 行表用索引比順序讀取快至少 100 倍。所有 MySQL 索引（PRIMARY、UNIQUE、INDEX）均儲存於 B+ 樹中。