萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL CPU 100% 排查實戰:從慢查詢到索引優化

一線排查筆記:Windows + IIS + PHP + MySQL 環境

現象:mysqld-nt.exe CPU 長期 100%,停用某站點即恢復。經排查,核心原因是高頻 SQL 缺乏關鍵索引,配合參數微調彻底解決。

排查步驟復盤

  1. 確認癥結點:停用站點 A → CPU 降下來 → 啟用 → 升回 100%,鎖定站點 A。
  2. 導出當前配置基線:mysqld --help > output.txt 發現 tmp_table_size=32M(默認)。
  3. 調大臨時表內存:my.ini 設 tmp_table_size=200M,重啟後 CPU 微降至 97-100%,說明有效但非根因。
  4. 實時抓慢查詢:SHOW PROCESSLIST; 秒級刷新,發現兩條高頻 SQL:

問題 SQL 1:三表 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.userid 無索引,導致 LEFT JOIN _myuser 全表掃描。
修復:ALTER TABLE `_mydata` ADD INDEX `idx_userid` (`userid`);
效果:CPU 降至 80%。

問題 SQL 2:關鍵字模糊匹配無索引

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%。

順手優化 Discuz 論壇表

同理為高頻 JOIN/WHERE 欄位補索引,CPU 進一步下降至正常水位。

經驗總結:MySQL CPU 100% 通用排查清單

步驟動作工具/命令
1確認是否特定業務導致停站點/應用隔離
2檢查基礎參數mysqld --help, SHOW VARIABLES
3調大 tmp_table_size / sort_buffer_sizemy.cnf 修改後重啟
4抓取高頻 SQLSHOW 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 趨勢圖

索引設計鐵律(官方文檔精簡版)

  • WHERE / JOIN / MAX()/MIN() / ORDER BY 涉及的列 → 建索引
  • 多列索引遵循「最左前綴」: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」機制,比事後救火划算百倍。

copyright © 萬盛學電腦網 all rights reserved