MySQL臨時文件管理與隱藏空間問題深度解析

臨時文件目錄決策鏈

1. 環境變量 TMPDIR / TEMP / TMP
2. 啟動參數 --tmpdir=/path/to/dir
3. 編譯時默認值 (通常 /tmp、/var/tmp、/usr/tmp)

MySQL 5.1+ 支持多目錄輪詢：--tmpdir=/path1:/path2:/path3 (Unix冒號分隔，Windows分號)。建議掛載不同物理磁盤 分散 I/O 壓力。

復制從庫 tmpdir 特殊約束

嚴禁將 --tmpdir 指向內存文件系統 (tmpfs/ramfs) 或重啟清空的目錄

• 從庫需持久化臨時表結構/數據以支持斷點續傳
• LOAD DATA INFILE 產生的臨時文件需在重啟後可用
• 臨時文件丟失導致復制中斷，錯誤碼 ER_CANT_CREATE_FILE

隱式臨時文件機制

• MySQL 以 隱藏文件 (文件名以點開頭) 創建所有臨時文件
• 優勢：進程異常退出時 OS 自動回收，無殘留垃圾
• 劣勢：ls -la 默認不顯示，**難以發現大臨時文件佔用磁盤空間**
• 排查命令：ls -la /tmp/.mysql_* 或 lsof -p $(pidof mysqld) | grep -E "tmp|delete"

磁盤空間需求估算模型

排序操作 (ORDER BY / GROUP BY / DISTINCT)

所需空間 ≈ (排序行長度 + 行指針 4-8 字節) × 匹配行數 × 2
# ×2 因為歸並排序需雙緩衝區

• 行指針 64 位系統通常 8 字節，大表可能更大
• sort_buffer_size 內存不足時溢寫磁盤臨時文件

臨時表 (衍生表/UNION/視圖/子查詢物化)

• 顯式命名格式：#sql__
• 存儲引擎：MEMORY (HEAP) → 超限/含 BLOB/TEXT 時轉 MyISAM/InnoDB
• tmp_table_size / max_heap_table_size 雙閾值控制內存臨時表上限

DDL 操作 (ALTER TABLE / OPTIMIZE / REPAIR)

• 在原表目錄創建臨時表 #sql-ib (InnoDB) 或 #sql-.MYD/.MYI (MyISAM)
• 需**雙倍表空間** 暫存，大表變更前必須確認磁盤剩餘 > 2× 表大小
• InnoDB 5.6+ 支持 ALGORITHM=INPLACE 避免全表拷貝 (視操作類型)

最佳實踐配置模板

[mysqld]
# 多磁盤分散 I/O (假設 /mnt/ssd1 /mnt/ssd2 為高性能獨立磁盤)
tmpdir=/mnt/ssd1/mysql_tmp:/mnt/ssd2/mysql_tmp

# 內存排序緩衝 (按連接分配，謹慎調大)
sort_buffer_size=4M
read_rnd_buffer_size=4M

# 內存臨時表上限 (取較小者生效)
tmp_table_size=64M
max_heap_table_size=64M

# 監控臨時表落盤比率
# SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
# Created_tmp_disk_tables / Created_tmp_tables > 10% 需調優

空間洩漏應急排查

# 1. 找出佔用大臨時文件的進程
lsof -p $(pidof mysqld) | awk '$5 ~ /REG/ && $7 > 100000000 {print $0}'

# 2. 找出被刪除但未釋放空間的文件 (deleted 狀態)
lsof -p $(pidof mysqld) | grep deleted

# 3. 重啟 MySQL 釋放所有隱式臨時文件 (最終手段)