MySQL 隱藏臨時檔案空間管理：TMPDIR、--tmpdir 輪替與複製奴障伺服器注意事項

臨時檔案目錄決定邏輯

1. 環境變數 TMPDIR。
2. 系統預設：/tmp、/var/tmp、/usr/tmp。
3. 啟動參數 --tmpdir=/path 覆蓋上述。

MySQL 5.1+ 多目錄輪替

• Unix：--tmpdir=/path1:/path2:/path3（冒號分隔）。
• Windows/NetWare/OS/2：--tmpdir=D:\tmp1;E:\tmp2（分號分隔）。
• 建議掛載於不同物理磁碟，而非同磁碟不同分區，以分散 I/O 壓力。

複製奴障伺服器特別限制

不可 指向記憶體檔案系統（tmpfs/ramfs）或重啟會清空的目錄。
原因：複製過程需保留臨時表與 LOAD DATA INFILE 中間檔案，重啟遺失會導致複製中斷、需完整重同步。

臨時檔案特性

• 隱含建立（unlink 後仍開啟），mysqld 結束自動刪除，ls 不可見。
• 缺點：無法直觀監控大臨時檔佔用空間。

空間估算公式（排序/群組）

(sorted_row_length + sizeof(row_pointer)) * matched_rows * 2
-- row_pointer 通常 4 bytes，大表可能增至 8 bytes

例：100 萬列、每列 100 bytes 排序 → 約 200 MB 臨時空間。

其他臨時檔案類型

• SELECT ... 複雜查詢可能建立 SQL_* 臨時表（可見、非隱含）。
• ALTER TABLE 在原表目錄建立臨時表 #sql-ib...。

監控與告警

• df -h /tmp 定時檢查可用空間。
• Prometheus node_filesystem_free_bytes + 告警規則 < 10%。
• 慢查詢日誌中 Created_tmp_disk_tables、Created_tmp_files 激增預警。