MySQL 臨時表分為記憶體表（MEMORY 引擎）與磁碟表（MyISAM/InnoDB）。當臨時表超過大小限制或包含 TEXT/BLOB 欄位時，會強制落盤，引發嚴重 I/O 瓶頸。本文教你如何監控臨時表狀態、調整閾值，並將 tmpdir 掛載到記憶體 tmpfs 徹底解決落盤效能問題。

現狀診斷：監控臨時表生成情況

mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 49094    |
| Created_tmp_tables      | 37842181 |
+-------------------------+----------+

關鍵指標：Created_tmp_disk_tables / Created_tmp_tables 比率。若超過 5-10%，說明落盤頻繁，需優化。

閾值配置：控制記憶體臨時表上限

mysql> SHOW GLOBAL VARIABLES LIKE '%table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 25165824 |  -- 24MB
| tmp_table_size      | 25165824 |  -- 24MB
+---------------------+----------+

兩參數取較小值作為記憶體臨時表上限。超過此值或含 TEXT/BLOB → 強制轉 MyISAM 落盤。

調整建議：根據業務查詢特徵，適度增大至 64MB-256MB（但謹防單查詢佔用過多記憶體導致 OOM）：

[mysqld]
tmp_table_size = 128M
max_heap_table_size = 128M

根治方案：tmpdir 掛載至 tmpfs

即使調大閾值，複雜查詢、大結果集、TEXT/BLOB 仍會落盤。將 tmpdir 指向記憶體檔案系統，讓落盤也變成「記憶體 I/O」。

步驟 1：創建掛載點並設定權限

mkdir -p /mnt/ramdisk
chown mysql:mysql /mnt/ramdisk

步驟 2：掛載 tmpfs（建議 512MB-2GB 視記憶體而定）

mount -t tmpfs -o size=1G,mode=1777 tmpfs /mnt/ramdisk

參數說明：

• size=1G：最大佔用 1GB 物理記憶體（不預佔，按需分配）
• mode=1777：類似 /tmp 權限，防止非擁有者刪除檔案

步驟 3：持久化掛載（寫入 /etc/fstab）

tmpfs /mnt/ramdisk tmpfs rw,size=1G,mode=1777 0 0

步驟 4：修改 MySQL 配置並重啟

# 確認當前 tmpdir
mysql> SHOW GLOBAL VARIABLES LIKE 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+

# 修改 my.cnf
[mysqld]
tmpdir = /mnt/ramdisk

systemctl restart mysqld

驗證效果

mysql> SHOW GLOBAL VARIABLES LIKE 'tmpdir';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tmpdir        | /mnt/ramdisk |
+---------------+--------------+

# 執行會產生大量臨時表的查詢，觀察 Created_tmp_disk_tables 增長速度大幅放緩
# 同時用 iostat -x 1 觀察物理磁碟 I/O 明顯下降

注意事項與風險控制

風險	對策
記憶體耗盡 OOM	設定合理 size 參數；監控 free -h；關鍵業務配置 oom_score_adj
重啟資料丟失	tmpfs 重啟清空，臨時表本身無持久化需求，無影響
容器環境	Docker/K8s 需在宿主機掛載或使用 emptyDir + medium: Memory
權限問題	確保 mysql 用戶對掛載點有讀寫執行權限

進階：區分不同類型臨時目錄

MySQL 8.0+ 支援 innodb_temp_data_file_path 獨立控制 InnoDB 內部臨時表空間，可單獨掛載高效能 NVMe，而 tmpdir 專用於複雜查詢的外部臨時表。

總結

「調大閾值」治標，「tmpfs 掛載」治本。對於高並發、複雜查詢、大量 TEXT/BLOB 的業務，將 tmpdir 遷移至記憶體是性價比最高的優化手段之一。配合監控告警，讓臨時表落盤不再是效能黑洞。