MySQL 臨時文件存儲位置完整指南

MySQL 臨時文件存儲機制詳解

MySQL 在運行過程中會產生大量臨時文件，了解其存儲位置與配置方式對資料庫效能調優至關重要。本文系統整理 MySQL 臨時文件的預設路徑、自訂配置、複寫環境注意事項及磁碟空間估算方法。

預設存儲目錄與環境變數

MySQL 啟動時會讀取環境變數 TMPDIR 作為臨時文件目錄。若未設定，則依序使用系統預設路徑：/tmp、/var/tmp 或 /usr/tmp。當預設文件系統空間不足時，可透過啟動參數 --tmpdir 指定擁有足夠空間的目錄。

多路徑循環分發（MySQL 5.1+）

自 MySQL 5.1 起，--tmpdir 支援多路徑列表，以循環方式分發 I/O 負載：

• Unix/Linux：路徑以冒號 : 分隔
• Windows/NetWare/OS/2：路徑以分號 ; 分隔

最佳實踐：各路徑應位於不同物理磁碟，而非同一磁碟的不同分區，才能真正分散 I/O 壓力。

複寫從庫的特殊限制

若 MySQL 作為複寫從庫運行，嚴禁將 --tmpdir 指向：

• 基於記憶體的文件系統（如 tmpfs）
• 主機重啟會被清空的目錄

原因：複寫過程中需保留某些臨時文件（如臨時表、LOAD DATA INFILE 產生的文件）以便重啟後續傳。若文件遺失，複寫將中斷失敗。

隱含文件機制與可見性

MySQL 以隱含（unlinked）方式建立所有臨時文件，確保 mysqld 異常終止時自動釋放空間。副作用是：在文件系統層面無法直接看到佔用大量空間的臨時文件，排查磁碟滿問題時需注意。

排序操作的臨時文件空間估算

執行 ORDER BY 或 GROUP BY 時，MySQL 通常會建立一個或多個臨時文件。所需最大磁碟空間公式：

(排序行長度 + 行指標大小) × 匹配行數 × 2

行指標預設 4 位元組，大表可能增加。預估空間時請納入此系數。

其他臨時表類型

• 隱含臨時表：內部排序、分組使用，文件名不可見。
• 顯式 SQL 臨時表：部分 SELECT 查詢會建立，名稱以 SQL_* 開頭，非隱含，可在文件系統看到。
• ALTER TABLE 臨時表：建立於原表同目錄下，執行期間佔用空間。

管理建議

1. 監控 TMPDIR 所在分區剩餘空間，預留足夠緩衝。
2. 生產環境建議掛載專用高速磁碟（NVMe/SSD）作為臨時目錄。
3. 複寫從庫務必使用持久化存儲，避免重啟導致複寫中斷。
4. 定期檢查 Created_tmp_disk_tables 狀態變數，評估磁碟臨時表頻率。