背景
最近遇到了一個關於 MySQL 完全耗盡磁盤空間的問題。這是個嚴重的問題，因為會導致服務不斷地中斷，客戶會不斷的重啟服務並在不久後再次宕機。
究竟發生了什麼？在本文中，將解釋原因並找到解決方案。
隱式臨時表
MySQL 需要創建隱式臨時表來解決某些類型的查詢。往往查詢的排序階段需要依賴臨時表。例如，當您使用 GROUP BY，ORDER BY 或DISTINCT 時。這樣的查詢分兩個階段執行：首先是收集數據並將它們放入臨時表中，然後是在臨時表上執行排序。
對於某些 UNION 語句，不能合併的 VIEW，子查詢時用到派生表，多表 UPDATE 以及其他一些情況，還需要使用臨時表。
如果臨時表很小，可以到內存中創建，否則它將在磁盤上創建。MySQL 在內存中創建了一個表，如果它變得太大，就會被轉換為磁盤上存儲。內存臨時表的最大值由 tmp_table_size 或 max_heap_table_size 值定義，以較小者為準。MySQL 5.7 中的默認大小為 16MB。如果運行查詢的數據量較大，或者尚未查詢優化，則可以增加該值。設置閾值時，請考慮可用的 RAM 大小以及峰值期間的併發連接數。你無法無限期地增加變量，因為在某些時候你需要讓 MySQL 使用磁盤上的臨時表。
注意：如果涉及的表具有 TEXT 或 BLOB 列，則即使大小小於配置的閾值，也會在磁盤上創建臨時表。
臨時表的存儲引擎
在 MySQL 5.6 之前，所有磁盤上的臨時表都默認創建為 MyISAM 類型。臨時表是在內存中，還是在磁盤上創建，具體取決於配置，並在查詢結束時立即刪除。從 MySQL 5.7 開始，它們默認創建為 InnoDB 類型。
新默認值可提升整體性能，大多數情況下都是最佳選擇。
可以使用新的配置項來設置臨時表的存儲引擎：internal_tmp_disk_storage_engine ，可選值為 InnoDB（默認）或 MyISAM。
InnoDB 類型的臨時表存在的潛在問題
儘管使用 InnoDB 是性能最佳的，但可能會出現新的潛在問題。在某些特定情況下，您可能會出現磁盤耗盡和服務器中斷。
與數據庫中的任何其他 InnoDB 表一樣，臨時表具有自己的表空間文件。新文件與通用表空間一起位於數據目錄中，名稱為 ibtmp1。它存儲所有 tmp 表。不運行手動運行 OPTIMIZE TABLE，表空間文件就會不斷增長。如果你不能使用 OPTIMIZE，那麼唯一能將 ibtmp1 大小縮小為零的方法，就是重新啟動服務器。
幸運的是，即使文件無法減小，在執行查詢後，臨時表也會自動刪除，表空間可回收使用。
現在，我們想一想以下情境：
- 存在未優化的查詢，需要在磁盤上創建非常大的的臨時表
- 存在優化的查詢，但他們正在磁盤上創建非常大的臨時表，因為你正在對此數據集進行計算（統計，分析）
- 高併發連接時，運行相同的查詢，伴隨臨時表的創建
- 沒有很多可用空間
在這些情況下，文件 ibtmp1 大大增加，很容易耗盡可用空間。這種情況每天發生幾次，並且必須重啟服務器才能完全縮小 ibtmp1 表空間。使用不可收縮的文件可以輕鬆耗盡磁盤空間！
那麼，如何避免磁盤耗盡和中斷呢？
簡單的解決方案：使用更大的磁盤
雖然可以暫時解決問題，但這不是最佳解決方案。實際上，您可以通過逐步增加磁盤大小，來猜測具體需要的空間。如果環境位於雲中，或者在非常大的虛擬平臺，這很容易實現。但是使用這種解決方案，您可能會面臨不必要的開支。
您還可以通過設置以下配置變量將 ibtmp1 文件移動到專用大型磁盤上：
<code> [mysqld] innodb_temp_data_file_path = ../../tmp/ibtmp1:12M:autoextend/<code>
需要重啟 MySQL 。注意，必須將路徑指定為相對於數據目錄。
設置 ibtmp1 大小的上限
例如：
<code> [mysqld] innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G/<code>
在這種情況下，文件不能超過 10GB。可以降低宕機概率，但也是一個危險的解決方案。當數據文件達到最大值時，會查詢失敗並顯示一個錯誤，提示表已滿。
退回 MyISAM 將臨時表存儲在磁盤上
這個解決方案似乎違反直覺，但它可能是快速避免中斷的最佳方法，並保證使用所有需要的臨時表。
<code> internal_tmp_disk_storage_engine = MYISAM/<code>
由於變量是動態的，您也可以在運行時設置它：
<code> SET GLOBAL internal_tmp_disk_storage_engine = MYISAM;/<code>
回到 MyISAM，您將大大降低寫滿磁盤空間的可能性。實際上，臨時表將創建到不同的文件中，並在查詢結束時立即刪除。
雖然總是有可能看到相同的問題，以防你可以在同一時間運行查詢或非常接近。在我的實際案例中，這是避免所有中斷的解決方案。
優化你的查詢
在將存儲引擎退回到 MyISAM 以減輕中斷髮生後，必須花時間分析查詢。
目標是減小磁盤上臨時表的大小。本文的目的不是解釋如何調查查詢，而是可以依賴慢速日誌，像 pt-query-digest 和 EXPLAIN 這樣的工具。
一些技巧：
- 在表上創建缺少的索引
- 如果不需要，可以在查詢中添加更多過濾條件以更少收集的數據
- 重寫查詢以優化執行計劃
- 可以在應用程序中使用隊列管理器來序列化它們的執行或減少併發性
但希望在所有優化之後，您可以返回將臨時存儲引擎設置為 InnoDB 以獲得更好的性能。
結論
有時這些改進會產生意想不到的副作用。用於磁盤上臨時表的 InnoDB 存儲引擎是一個很好的改進，但在某些特定情況下，例如，如果您有未優化查詢和很少的可用空間，則可能因“磁盤已滿”錯誤而中斷。將 tmp 存儲引擎退回到 MyISAM 是避免中斷的最快方法，但是為了返回到 InnoDB，查詢的優化是更重要的事情。更大或專用的磁盤也可能有所幫助。但這是一個微不足道的建議。
