MySQL tmp_table_size 參數深度調優：臨時表磁碟化根因、監控指標與動態調整實戰

tmp_table_size 控制內部記憶體臨時表最大容量，超限即轉為磁碟 MyISAM/InnoDB 表，導致 Copy to tmp table 狀態持久、I/O 飆升、鎖爭用連鎖反應。本文深度解析臨時表磁碟化根因、監控紅線、動態調整陷阱與實戰優化策略。

核心機制：雙參數共同約束

• tmp_table_size：SQL 層臨時表大小上限
• max_heap_table_size：MEMORY 引擎表大小上限
• 實際生效上限 = MIN(tmp_table_size, max_heap_table_size)
• 超限 → 自動轉換為磁碟表 (Created_tmp_disk_tables 計數器 +1)

監控紅線：三大關鍵指標

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 21197   |  -- 磁碟臨時表計數
| Created_tmp_files       | 58      |  -- 臨時檔案計數
| Created_tmp_tables      | 1771587 |  -- 總臨時表計數 (含記憶體)
+-------------------------+---------+

健康度判斷公式

磁碟化率 = Created_tmp_disk_tables / Created_tmp_tables × 100%

• < 1%： 極度健康
• 1-10%： 可接受，關注趨勢
• 10-25%： 警告，需調優
• > 25%： 嚴重，立即介入

上例：21197 / 1771587 ≈ 1.2% → 健康狀態

磁碟化連鎖反應災難鏈

大查詢 → 需臨時表 (GROUP BY/ORDER BY/DISTINCT/UNION/子查詢)
    → 超過 tmp_table_size
    → 轉磁碟 MyISAM/InnoDB 表 (Copy to tmp table)
    → 磁碟 I/O 延遲 (ms 級 vs 記憶體 μs 級)
    → 查詢持久時間大幅增加 (秒/分級)
    → 持有鎖時間延長 (表鎖/行鎖/MDL)
    → 併發查詢排隊阻塞
    → 連線數飆升 (Max_used_connections 衝頂)
    → 新連線被拒 (Too many connections)
    → 服務雪崩

實戰案例：連線數暴增至 1000 根因追溯

-- 現場觀察
SHOW PROCESSLIST;
-- 大量 "Copy to tmp table" 狀態，Time 高達 30-60 秒
-- 相關表被讀鎖，UPDATE 排隊阻塞

-- 參數現狀
tmp_table_size = 33554432 (32 MB)
max_heap_table_size = 16777216 (16 MB)  -- 實際生效上限僅 16 MB！

-- 業務特徵
動態頁面佔比高，大量 GROUP BY + ORDER BY 複雜報表查詢
結果集常超過 16 MB，強制磁碟化

優化動作

1. 確認記憶體充足（實體 32 GB，Buffer Pool 20 GB，餘量充裕）
2. 動態調大：SET GLOBAL tmp_table_size = 200M; SET GLOBAL max_heap_table_size = 200M;
3. 持久化：my.cnf 寫入 tmp_table_size=200M、max_heap_table_size=200M
4. 同步優化 join_buffer_size 從 128K → 1M (輔助非索引 Join)
5. 補充索引：ALTER TABLE _mydata ADD INDEX (userid); 消除非索引 Join

效果驗證

• Copy to tmp table 狀態消失/大幅減少
• 查詢耗時從 30s+ 降至 200ms 內
• 連線數從 1000 降至 50 以下穩定
• Created_tmp_disk_tables 增長趨勢歸零

動態調整陷阱與正確姿勢

業務場景化配置建議

索引協同優化：治本之道

臨時表磁碟化 80%+ 源於 缺乏適當索引 導致全表掃描、Filesort、非索引 Join。參數調大只能緩解症狀，補齊索引才能治本：

-- WHERE/JOIN/ORDER BY/GROUP BY/MIN/MAX 條件欄位建立複合索引
-- 索引欄位序 = WHERE等值 + GROUP BY/ORDER BY + SELECT欄位 (覆蓋索引)
ALTER TABLE `orders` ADD INDEX `idx_user_status_date` (`user_id`, `status`, `created_at`);
-- 覆蓋索引可直接從索引取數據，避免回表、臨時表、Filesort

官方文檔核心說明摘譯

"This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensure temporary tables are always stored in memory. Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread."

從 16 MB 瓶頸到 200 MB 寬闊空間，從連線數 1000 雪崩到 50 穩定，一個參數的精準調整，拯救了一個瀕臨崩潰的生產系統。