為什麼 MySQL 會產生這麼多臨時 MYD 文件？

在託管許多 PHP/MySQL 網站（照片庫）的 Debian Linux 伺服器上，有時我有“許多”文件，例如/tmp/#sql_6405_58.MYD.

例如今天：

[2012-12-15 15:18:11] /tmp/#sql_6405_6.MYD : 88MB
[2012-12-15 15:18:11] /tmp/#sql_6405_3.MYD : 22MB
[2012-12-15 15:18:11] /tmp/#sql_6405_4.MYD : 138MB
[2012-12-15 15:18:11] /tmp/#sql_6405_10.MYD : 88MB
...
[2012-12-15 15:18:11] /tmp/#sql_6405_9.MYD : 15MB
[2012-12-15 15:18:11] /tmp/#sql_6405_65.MYD : 49MB
[2012-12-15 15:18:11] /tmp/#sql_6405_44.MYD : 69MB

（同時 59 個文件，超過 6GB…是的，我在 /tmp 中監視大文件）

不幸的是，/tmp它在同一個分區上，/並且它暫時中斷了 Web 伺服器，因為/我想它已經滿了。然後文件消失，伺服器恢復正常。

所有文件名都遵循該#sql_6405_*.MYD模式。我想了解哪個 MySQL 操作意味著這麼多臨時文件。我在這台伺服器上有大約 2000 個數據庫。是否可以知道涉及哪個數據庫？

有一些選項可以導致臨時表實現為 MyISAM 表，或者可以配置為延遲它。請記住，對於基於磁碟的臨時表，沒有文件，.frm只有文件（當然。.MYI 文件從不使用，因為不可能索引內部臨時表）。.MYD``.MYI

以下是選項：

• 應具有相同大小的臨時表變數

  • tmp_table_size
  • max_heap_table_size

• 排序緩衝區大小

• 加入緩衝區大小

您還應該考慮有關內部臨時表使用的 MySQL 文件

製作記憶體臨時表的情況是

• 如果存在 ORDER BY 子句和不同的 GROUP BY 子句，或者如果 ORDER BY 或 GROUP BY 包含來自連接隊列中第一個表以外的表的列，則會創建一個臨時表。
• DISTINCT 與 ORDER BY 結合可能需要一個臨時表。
• 如果使用 SQL_SMALL_RESULT 選項，MySQL 將使用記憶體臨時表，除非查詢還包含需要磁碟儲存的元素（稍後描述）。

當記憶體中的臨時表超過（tmp_table_size 或 max_heap_table_size）的最小值時，mysqld 執行以下操作：

• 暫停查詢
• 將記憶體表的內容複製到 MyISAM 臨時表中
• 丟棄記憶體表
• 繼續查詢，將臨時數據發送到 MyISAM 臨時表

繞過記憶體臨時表以支持磁碟的情況是

• 表中存在 BLOB 或 TEXT 列
• GROUP BY 或 DISTINCT 子句中存在大於 512 字節的任何列
• 如果使用 UNION 或 UNION ALL，則 SELECT 列表中存在任何大於 512 字節的列

需要進行一些盡職調查以減少磁碟上的臨時表創建

• 將 join_buffer_size 設置得更大
• 將 sort_buffer_size 設置得更大
• 將 tmp_table_size 和 max_heap_table_size 設置得更大
• 調整查詢以最小化甚至阻止臨時表
• 創建索引以從單個表中創建數據的預排序視圖
• 安裝額外的 RAM 以容納大型記憶體臨時表

如果經過這樣的盡職調查，磁碟上仍有臨時表正在形成，這是一個絕望的舉動：將基於磁碟的臨時表創建映射到記憶體。

這是使用tmpdir設置 16GB RAM 磁碟的快捷方式

STEP01) 創建 RAM 磁碟文件夾

mkdir /var/mysql_tmpfs

STEP02) 將此添加到my.cnf

[mysqld]
tmpdir=/var/mysql_tmpfs

STEP03) 將此添加到 /etc/fstab

echo "none /var/mysql_tmpfs tmpfs defaults,size=16g 1 2" >> /etc/fstab

STEP04) 重新載入 /etc/fstab

mount -a

STEP05)service mysql restart

在此之後，所有成為 MyISAM 的臨時表都被寫入 RAM Disk。這應該會加快基於磁碟的臨時表的創建速度。

試一試 ！！！

這些查詢會滾動到磁碟上，因為結果對於記憶體來說太大了。

查詢完成後，空間將被清除。

沒有辦法將這些臨時文件與查詢明確匹配，但您可以從 SHOW FULL PROCESSLIST 中獲得線索來做出很好的猜測；或顯示 INNODB 狀態；或者在查詢失敗時查看錯誤日誌。

引用自：https://dba.stackexchange.com/questions/30505