MySQL 臨時檔案目錄 (TMPDIR) 配置與隱藏空間管理詳解

臨時檔案產生場景

• ORDER BY / GROUP BY 需外部排序 (超過 sort_buffer_size)。
• DISTINCT、UNION、衍生表物化。
• ALTER TABLE 重建表 (同目錄建立臨時表)。
• LOAD DATA INFILE、複寫從庫應用 Relay Log。

空間需求估算公式

最大磁碟空間 = (排序行長度 + 行指標 4~8 bytes) * 匹配行數 * 2

行指標 32 位系統 4 bytes，64 位系統 8 bytes；大表可能更大。

配置參數

多路徑循環配置範例 (5.1+)

[mysqld]
tmpdir = /mnt/ssd1/tmp:/mnt/ssd2/tmp:/mnt/ssd3/tmp
# Windows
tmpdir = D:\tmp;E:\tmp;F:\tmp

原則： 路徑必須位於**不同物理磁碟**，而非同磁碟不同分區，才能真正分散 I/O。

複寫從庫特殊警示

禁止 將 tmpdir 指向記憶體檔案系統 (tmpfs/ramdisk) 或重啟會清空的目錄。
原因：從庫需保留臨時表與 LOAD DATA 檔案以便重啟後繼續複寫，遺失會導致 SQL Thread 中斷、複寫中止。

隱藏檔案機制

• MySQL 建立臨時檔案時使用 unlink() 開啟後立即刪除目錄項，僅保留 inode。
• 優點：進程崩潰自動釋放空間，無殘留垃圾。
• 缺點：ls -la、**圖形化檔案管理器均看不見**，df 顯示已用空間卻找不到檔案 → 「隱藏空間」謎團。

排查隱藏空間佔用

# 找出 mysqld 開啟的已刪除檔案
lsof -p $(pidof mysqld) | grep deleted

# 或查看 /proc
ls -l /proc/$(pidof mysqld)/fd/ | grep tmp

ALTER TABLE 臨時表位置

同原表目錄 (非 tmpdir)，故大表重建需確保**資料目錄磁碟剩餘空間 ≥ 表大小 × 2**。

最佳實務清單

1. 獨立高速磁碟掛載為 tmpdir。
2. 多路徑分散於不同物理裝置。
3. 監控 Created_tmp_disk_tables / Created_tmp_files 狀態變數。
4. 複寫從庫 tmpdir 必須持久化儲存。
5. 定期 lsof 排查隱藏空間洩漏。

臨時檔案管理雖非核心業務，卻是大查詢效能與磁碟穩定性的關鍵基礎設施。