MySQL 內部臨時表內存參數調優：tmp_table_size 與 max_heap_table_size 關係深度解析

核心結論

內部臨時表最大內存限制 = MIN(tmp_table_size, max_heap_table_size)。兩者中較小值決定臨時表何時從內存 (MEMORY 引擎) 轉為磁盤 (MyISAM/InnoDB)。

參數詳解

臨時表轉磁盤觸發條件

1. 內部臨時表大小超過 MIN(tmp_table_size, max_heap_table_size)。
2. 包含 TEXT/BLOB 列 (MEMORY 引擎不支援，直接用磁盤表)。
3. 列大小超過 MEMORY 引擎限制 (如 VARCHAR 過長)。
4. UNION / UNION ALL 涉及大列。

監控指標：判斷是否需要調大

SHOW GLOBAL STATUS LIKE 'Created_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_tables      | 15000 |  -- 內存臨時表總數
| Created_tmp_disk_tables |  300  |  -- 轉磁盤臨時表總數
+-------------------------+-------+

健康閾值： Created_tmp_disk_tables / Created_tmp_tables < 5% (理想 < 1%)。超過說明內存不足頻繁落盤，I/O 開銷大，需調優。

調優建議 (以 16GB 內存實例為例)

# /etc/my.cnf
tmp_table_size = 64M
max_heap_table_size = 64M

1. 成對調大：單調大無效，受限於較小值。
2. 步進式調整：16M → 32M → 64M → 128M，觀察磁盤臨時表比例下降曲線。
3. 上限參考：單表不超過 128MB~256MB，總佔用 = 並發查詢數 × 參數值，防止 OOM。
4. 優先優化 SQL：加索引消除 filesort、避免不必要的 GROUP BY、分頁優化 — 往往比單純調參數收益更大。

常見誤區

• 誤區 1：以為調大這兩個參數能加速用戶創建的 MEMORY 表。
  → 事實：max_heap_table_size 只影響 新建 MEMORY 表的最大行數限制，已存在表需 ALTER TABLE ... ENGINE=MEMORY 重建生效。
• 誤區 2：以為所有臨時表都在內存。
  → 事實：含 TEXT/BLOB、超長 VARCHAR、UNION 大列的臨時表直接創建為磁盤表，不受這兩個參數控制。
• 誤區 3：MyISAM 磁盤臨時表慢，InnoDB 快。
  → 事實：5.7+ 內部磁盤臨時表默認引擎由 internal_tmp_disk_storage_engine 控制 (默認 InnoDB)，InnoDB 支持行鎖、緩衝池，性能優於 MyISAM。

進階：內存臨時表引擎選擇 (MySQL 8.0)

8.0 引入 temptable 引擎作為內存臨時表默認引擎 (替代 MEMORY)，支援可變長列 (VARCHAR, VARBINARY) 高效存儲，溢出時自動溢寫到磁盤 (InnoDB)。
關鍵參數：temptable_max_ram (默認 1GB)，temptable_max_mmap (默認 1GB)。
建議： 8.0 實例重點關注 temptable_max_ram，而非傳統 tmp_table_size。