現象:mysqld-nt.exe CPU 長期 100%,停用某站點即恢復。經排查,核心原因是高頻 SQL 缺乏關鍵索引,配合參數微調彻底解決。
mysqld --help > output.txt 發現 tmp_table_size=32M(默認)。my.ini 設 tmp_table_size=200M,重啟後 CPU 微降至 97-100%,說明有效但非根因。SHOW PROCESSLIST; 秒級刷新,發現兩條高頻 SQL: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.userid 無索引,導致 LEFT JOIN _myuser 全表掃描。
修復:ALTER TABLE `_mydata` ADD INDEX `idx_userid` (`userid`);
效果:CPU 降至 80%。
SELECT COUNT(*)
FROM _mydata AS t1, _mydata_key AS t2
WHERE t1.pid = t2.pid AND t2.keywords = '孔雀';
索引缺失:_mydata_key.keywords 無索引,33 萬行全表文本掃描。
修復:ALTER TABLE `_mydata_key` ADD INDEX `idx_keywords` (`keywords`);
效果:CPU 降至 50-70%。
同理為高頻 JOIN/WHERE 欄位補索引,CPU 進一步下降至正常水位。
| 步驟 | 動作 | 工具/命令 |
|---|---|---|
| 1 | 確認是否特定業務導致 | 停站點/應用隔離 |
| 2 | 檢查基礎參數 | mysqld --help, SHOW VARIABLES |
| 3 | 調大 tmp_table_size / sort_buffer_size | my.cnf 修改後重啟 |
| 4 | 抓取高頻 SQL | SHOW PROCESSLIST 高頻刷新、pt-query-digest 分析慢查詢日誌 |
| 5 | 分析執行計劃 | EXPLAIN SELECT ... 看 type=ALL、rows 大、Extra=Using temporary; Using filesort |
| 6 | 補齊缺失索引 | WHERE/JOIN/ORDER BY/GROUP BY 涉及列加索引 |
| 7 | 驗證效果 | 再次 SHOW PROCESSLIST、CPU 趨勢圖 |
INDEX (a,b,c) 支援 a/a,b/a,b,c[mysqld]
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 4M
join_buffer_size = 4M
innodb_buffer_pool_size = 物理內存 60-70%
innodb_log_file_size = 256M
90% 的 CPU 100% 都是「缺索引」或「錯索引」造成的。建立「上線必審索引、慢查詢日誌常態化、定期 EXPLAIN 核心 SQL」機制,比事後救火划算百倍。