將 MySQL 臨時目錄遷移至內存（tmpfs）實戰：消除磁盤臨時表 I/O 瓶頸

將 MySQL 臨時目錄遷移至內存（tmpfs）實戰：消除磁盤臨時表 I/O 瓶頸

為什麼要關注臨時表？

MySQL 執行複雜查詢（GROUP BY、ORDER BY、JOIN、子查詢）時會創建臨時表：

• 內存臨時表：ENGINE=MEMORY，速度極快。
• 磁盤臨時表：ENGINE=MyISAM/InnoDB，受限於 I/O，嚴重拖慢查詢。

通過 SHOW GLOBAL STATUS LIKE 'Created_tmp%tables' 可見比例：

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 49094    |
| Created_tmp_tables      | 37842181 |
+-------------------------+----------+

上例磁盤臨時表佔比約 0.13%，但高並發下絕對值大時 I/O 壓力劇增。

控制臨時表轉磁盤的兩個參數

注意： 含 TEXT/BLOB 列的查詢無法使用 MEMORY 表，直接落磁盤，計入 Created_tmp_disk_tables。

方案：掛載 tmpfs 到 MySQL tmpdir

1. 創建掛載點並掛載 tmpfs（建議 512M~2G，視內存而定）

mkdir -p /mnt/ramdisk
chown mysql:mysql /mnt/ramdisk
mount -t tmpfs -o size=512M tmpfs /mnt/ramdisk

2. 寫入 /etc/fstab 實現開機自動掛載

tmpfs /mnt/ramdisk tmpfs rw,mode=1777,size=512M 0 0

3. 修改 my.cnf 指向新目錄並重啟

[mysqld]
tmpdir = /mnt/ramdisk

4. 驗證生效

mysql> SHOW VARIABLES LIKE 'tmpdir';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| tmpdir        | /mnt/ramdisk |
+---------------+--------------+

關鍵優勢

• 臨時表讀寫全走內存，延遲從毫秒級降至微秒級。
• 避免磁盤 I/O 爭用，釋放數據盤帶寬給數據文件與 Redo Log。
• tmpfs 支援 Swap：內存不足時內核自動換出，比強制落 MyISAM 磁盤表更優雅。

風險與對策

替代方案對比

• ramfs：不支援大小限制、不支援 Swap，易撐爆內存，**不推薦**。
• SSD/NVMe 專用分區：次於 tmpfs，但優於機械盤，適合內存受限環境。

一句話總結： 給 MySQL 臨時目錄掛載限容量 tmpfs，是「低成本、高收益、零代碼改動」的查詢加速銀彈。