您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL CPU 100% 根因排查實戰：show processlist 定位慢 SQL + 索引優化 + tmp_table_size 調優

編輯：MySQL 資料庫相關知識

現象

mysqld 進程 CPU 長期 100%，停止某網站後降下來，重啟網站即飆升。

第一步：排除配置與同步問題

檢查錯誤日誌 .err 無異常啟動錯誤。
確認非主從同步延遲導致（SHOW SLAVE STATUS 正常）。

第二步：調大 tmp_table_size（快速見效）

[mysqld]
tmp_table_size = 200M
max_heap_table_size = 200M
# 重啟生效
systemctl restart mysqld

原理：複雜 GROUP BY/ORDER BY 產生的內部臨時表超過預設 32M 會轉磁碟（MyISAM），磁碟 I/O 導致 CPU 飆升。調大後更多臨時表留在記憶體。

第三步：show processlist 定位高頻慢 SQL

mysql> SHOW FULL PROCESSLIST\G
-- 反覆執行，記錄高頻出現的查詢

案例 1：缺少 JOIN 索引

SELECT t1.pid, t2.userid, t3.count, t1.date
FROM _mydata t1
LEFT JOIN _myuser t3 ON t1.userid = t3.userid
LEFT JOIN _mydata_body t2 ON t1.pid = t2.pid
ORDER BY t1.pid LIMIT 0,15;

問題：_mydata.userid 無索引，LEFT JOIN 驅動表全表掃描。

修復：ALTER TABLE _mydata ADD INDEX idx_userid (userid); → CPU 從 100% 降至 80%。

案例 2：大表文本搜索無索引

SELECT COUNT(*) 
FROM _mydata t1, _mydata_key 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! 論壇核心表（pre_forum_thread、pre_forum_post 等）缺失索引，補全後 CPU 進一步下降至 10-20%。
應用層：開啟頁面靜態化、減少動態查詢、關鍵頁面 Redis 快取。

核心優化原則總結

維度	動作	依據
配置	tmp_table_size / max_heap_table_size ≥ 64M	避免臨時表落盤
索引	WHERE/JOIN/ORDER BY/GROUP BY 涉及欄位必建索引	B-Tree 索引將 O(N) 降為 O(log N)
SQL	避免 SELECT *、大 OFFSET 分頁、隱式轉換	減少 I/O 與 CPU 計算
架構	讀寫分離、快取層、靜態化	從源頭降低 DB 壓力

官方文檔依據

tmp_table_size：臨時表超限轉磁碟，建議調大配合 max_heap_table_size。
索引用於：WHERE、JOIN、MAX/MIN、ORDER BY/GROUP BY 最左前綴。

驗證腳本

#!/bin/bash
# check_mysql_cpu.sh
mysql -uroot -p -e "
  SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
  SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
  SHOW PROCESSLIST;
" | tee /tmp/mysql_cpu_check.log