MySQL 臨時文件存儲位置深度解析：配置、權限、磁盤空間與複製場景全覆蓋

默認位置與配置控制

• 未設置 TMPDIR 環境變量時，MySQL 使用系統默認：/tmp、/var/tmp 或 /usr/tmp。
• 啟動參數 --tmpdir=/path1:/path2:/path3（Unix 用冒號，Windows 用分號）可指定多目錄輪詢分發，**建議掛載在不同物理磁盤**以分散 I/O。
• MySQL 5.1+ 支持多路徑列表循環使用。

關鍵場景注意事項

復制從庫

禁止將 --tmpdir 指向內存文件系統（tmpfs）或重啟清空的目錄。從庫需在重啟後保留臨時文件以完成臨時表複製或 LOAD DATA INFILE 恢復，否則復制中斷。

隱含文件機制

MySQL 創建臨時文件時會立即 unlink（隱含刪除），文件句柄持有空間，進程結束自動釋放。優點：無殘留；缺點：ls -l 看不見大臨時文件佔用，排查磁盤滿需用 lsof | grep mysqld。

磁盤空間估算：排序操作

ORDER BY / GROUP BY 觸發磁盤排序時，最大臨時空間：

(排序行長度 + 行指針 4~8 字節) * 匹配行數 * 2

行指針在大表（>4GB）可能增至 8 字節。預留充足 tmpdir 空間至關重要。

臨時表類型

• 內部臨時表： 查詢中隱式創建（UNION、派生表、ORDER BY 非索引列等），名稱 #sql_*，**非隱含文件**，可見於 SHOW TABLES。
• ALTER TABLE 重建表： 在原表目錄下創建臨時表 #sql-ib*，操作完成後原子重命名替換。

最佳實踐建議

1. 獨立磁盤/分區掛載 /mnt/mysqltmp，設置 --tmpdir=/mnt/mysqltmp。
2. 監控 Created_tmp_disk_tables、Created_tmp_files 狀態變量。
3. 從庫 tmpdir 必須持久化存儲。
4. 大查詢前預估排序空間，必要時調大 sort_buffer_size 減少落盤。