MySQL CPU 100% 排查實戰：從 tmp_table_size 到索引缺失的全鏈路優化

線上 MySQL 進程 mysqld CPU 長期 100%，導致 PHP 頁面卡頓、ASP 應用頻繁掉線。本文記錄一次真實排查：從配置參數調優 → SHOW PROCESSLIST 定位高頻 SQL → EXPLAIN 發現缺失索引 → 加索引瞬間 CPU 降至 50% 以下的完整鏈路。

一、 現場現象

• Windows 2003 + IIS + PHP + MySQL
• 10 個數據庫，10 個站點共享實例
• 停用站點 A → CPU 瞬降；啟用 → 秒升 100%
• 站點 A：7 日均日 IP 2000、PV 3萬，39 張表、60 萬行、45 MB

二、 排查步驟與關鍵動作

1. 導出實例配置基線

mysqld --help > output.txt
# 發現 tmp_table_size=32M（默認），調大至 200M 後 CPU 微降（97-100% 震蕩）

2. 進入高頻 SQL 現場

mysql> SHOW PROCESSLIST;
# 反復出現兩條 SQL：
# SQL1: 三表 LEFT JOIN 無索引關聯
# SQL2: 33 萬行全文檢索無索引

3. 結構分析與加索引

-- SQL1: _mydata 表 userid 無索引
ALTER TABLE `_mydata` ADD INDEX `idx_userid` (`userid`);
-- CPU 100% → 80%

-- SQL2: _mydata_key 表 keywords 無索引
ALTER TABLE `_mydata_key` ADD INDEX `idx_keywords` (`keywords`);
-- CPU 80% → 50-70% 震蕩

4. 順手優化同實例 Discuz 論壇表

同理為高頻 JOIN/WHERE 列補索引，CPU 進一步下降穩定。

三、 核心知識點總結

1. tmp_table_size 參數

• 內存臨時表超限會轉 MyISAM 磁盤表，產生 The table ... is full 錯誤並劇增 I/O。
• 高併發、大量 GROUP BY/ORDER BY 場景建議調大（如 64M~256M），但需注意 max_heap_table_size 同步調整，單線程內存 = 兩者較小值。

2. 索引覆蓋原則

WHERE / JOIN / ORDER BY / GROUP BY / MAX()/MIN() 涉及的列，必須有索引。
B+ 樹索引查找複雜度 O(log N)，全表掃描 O(N)。1000 行表，索引快 100 倍以上。

3. 索引類型選擇

• 單列索引：高選擇性（基數/行數 > 0.1）的過濾列
• 聯合索引：多列組合過濾/排序，遵循最左前綴原則
• 覆蓋索引：SELECT 列全部在索引中，避免回表

四、 經驗清單（下次直奔現場）

1. 導出配置 mysqld --help > cfg.txt，對比默認值
2. SHOW PROCESSLIST 連續觀察 1 分鐘，鎖定高頻 SQL
3. EXPLAIN 高頻 SQL，看 type、key、rows、Extra
4. SHOW CREATE TABLE / SHOW INDEX 核對索引缺失
5. ALTER TABLE ... ADD INDEX 秒級生效（Online DDL 5.6+）
6. 觀察 CPU、QPS、RT 變化，確認收斂

CPU 100% 多半是「缺索引」或「參數失配」，按圖索驥，分分鐘見效。