MySQL 臨時檔案與隱藏空間管理：tmpdir 設定、排序溢位與復制從庫陷阱

核心概念：tmpdir 控制什麼？

MySQL 執行 ORDER BY、GROUP BY、DISTINCT、UNION、ALTER TABLE、LOAD DATA、建立臨時表等操作時，需將中間結果寫入臨時檔案。tmpdir（環境變數 TMPDIR 或啟動參數 --tmpdir）決定這些檔案的儲存位置。

預設與多路徑配置

• 未設定時：Linux /tmp、/var/tmp、/usr/tmp；Windows C:\Windows\TEMP
• 單路徑：--tmpdir=/mnt/ssd/tmp
• 多路徑循環分佈（5.1+）：Linux --tmpdir=/path1:/path2:/path3（冒號分隔）；Windows --tmpdir=D:\tmp;E:\tmp（分號分隔）
• 最佳實踐：多路徑分佈在不同物理磁碟，而非同磁碟不同分區，實現 I/O 並行

排序操作的臨時空間估算

最大磁碟空間 ≈ (排序行長度 + 行指標 4B) × 匹配行數 × 2

• 行指標未來大表可能擴大至 8B
• 大查詢前預估空間，防止 The table ... is full 錯誤

隱含臨時檔案特性

• MySQL 以 unlink() 開啟後立即刪除目錄項，僅保留檔案描述符
• 進程結束自動釋放空間，ls -l 看不見大檔案
• 可用 lsof -p mysqld_pid | grep tmp 觀察開啟的臨時檔

復制從庫的特殊限制

• 從庫需保留臨時表定義與 LOAD DATA INFILE 中間檔以支援重啟後繼續複製
• 若重啟丟失臨時檔 → 複製中斷、報錯、需重搭從庫
• 建議：從庫 tmpdir 指向持久化磁碟（SSD 最佳）

ALTER TABLE 的臨時表位置

舊版：與原表同目錄建立臨時表 → 同磁碟 I/O 競爭。
新版（5.6+）：可設定 innodb_tmpdir 將 InnoDB 臨時表導向獨立磁碟。

監控與告警建議

• 監控 Created_tmp_disk_tables / Created_tmp_tables 比率 > 10% → 需調大 tmp_table_size/max_heap_table_size 或優化 SQL
• 磁碟剩餘空間 < 20% → 告警
• 定期檢查 df -h tmpdir 掛載點

結語

tmpdir 看似不起眼，實則關係到大查詢能否跑完、從庫能否存活。獨立高速磁碟（NVMe SSD）、多路徑分佈、持久化、監控告警 四字訣，守住 MySQL 臨時空間生命線。