MySQL tmp_table_size 優化實戰：多大合適？如何監控與調整

MySQL tmp_table_size 優化實戰：多大合適？如何監控與調整

核心原理

tmp_table_size 控制記憶體內部臨時表（HEAP 表）的最大體積。複雜 GROUP BY、ORDER BY、DISTINCT、JOIN 等操作產生的中間結果若超過此值，MySQL 會自動轉換為磁碟上的 MyISAM 表，導致 IO 性能驟降。另一參數 max_heap_table_size 共同限制記憶體臨時表上限，取兩者較小值。

關鍵監控指標

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% ≤ 25%。上例僅 1.2%，屬優秀。

實戰調優建議

• 預設值：16MB
• 推薦範圍：64MB ~ 256MB（線程獨佔，過大易導致記憶體耗盡）
• 動態頁面多：可調至 100MB+
• 靜態內容為主：64MB 通常足夠

案例：連接數飆升至 1000 的排查

發現大量 Copy to tmp table 查詢，耗時長並持有讀鎖，導致 UPDATE 排隊阻塞。原配置：

tmp_table_size = 33MB
max_heap_table_size = 16MB
join_buffer_size = 131KB

調整策略：

1. 將兩參數同步調大至 200MB（視記憶體容量而定）
2. join_buffer_size 調至 2MB~8MB
3. 針對慢查詢欄位（如 userid）建立索引，避免全表掃描產生臨時表

動態修改與永久生效

SET GLOBAL tmp_table_size = 64*1024*1024;  -- 即時生效
# /etc/my.cnf 永久生效
[mysqld]
tmp_table_size = 200M
max_heap_table_size = 200M

注意：SET GLOBAL tmp_table_size = 64M 會報錯，需用位元組數值。