MySQL學習之臨時表相關總結

比起普通的使用者資料表，MySQL/InnoDB中的臨時表，大家應該會陌生很多。再加上不同的臨時表創建的時機和創建的位置都不固定，這也進一步加大神秘感。最讓人捉摸不透的是，臨時表很多時候會先創建文件，然後什麼都不做，就把文件刪除，留一個句柄讀寫，給人的感覺是神龍見首不見尾。本文分析了詳細MySQL各版本臨時表的處理方式，希望對大家有幫助。

綜述

準確的說，我們常說的臨時表分為兩種，一種真的是表，用來儲存使用者發送的數，讀寫走的是表讀寫接口，讀寫的時候表一定在文件系統上存在，另外一種，應該是一種臨時文件，用來儲存SQL計算中間過程的數據，讀寫走的是文件讀寫接口，讀寫的時候文件可能已經被刪除了，留一個文件句柄進行操作。

相關教學：mysql影片教學

臨時表

##暫存表可以分成磁碟暫存表和記憶體暫存表，而臨時文件，只會存在於磁碟上，不會存在於記憶體中。具體來說，臨時表的記憶體形態有Memory引擎和Temptable引擎，主要區別是對字元類型(varchar, blob，text類型)的儲存方式，前者不管實際字元多少，都是用定長的空間存儲，後者會用變長的空間存儲，這樣提高了內存中的存儲效率，有更多的數據可以放在內存中處理而不是轉換成磁碟臨時表。 Memory引擎從早期的5.6就可以使用，Temptable是8.0引入的新的引擎。另外一方面，磁碟臨時表也有三種形態，一種是MyISAM表，一種是InnoDB臨時表，另外一種是Temptable的檔案map表。其中最後一種方式，是8.0提供的。

在5.6以及以前的版本，磁碟臨時表都是放在資料庫配置的臨時目錄，磁碟臨時表的undolog都是與普通表的undo放在一起(注意由於磁碟臨時表在資料庫重啟後來就被刪除了，不需要redolog透過奔潰恢復來保證事務的完整性，所以不需要寫redolog，但是undolog還是需要的，因為需要支援回滾)。

在MySQL 5.7後，磁碟臨時表的資料和undo都被獨立出來，放在一個單獨的表空間ibtmp1裡面。之所以把臨時表獨立出來，主要是為了減少建立刪除表時維護元資料的開銷。

在MySQL 8.0後，磁碟臨時表的資料單獨放在Session臨時表空間池(#innodb_temp目錄下的ibt檔案)裡面，臨時表的undo放在global的表空間ibtmp1裡面。另一個重大的改進是，8.0的磁碟臨時表資料佔用的空間在連線斷開後，就能釋放給作業系統，而5.7的版本中需要重新啟動才能釋放。

目前有以下兩種情況會用到臨時表：

使用者明確建立臨時表

這種是使用者透過明確的執行指令

create temporary table所建立的表，引擎的類型要麼明確指定，要麼使用預設配置的值(default_tmp_storage_engine)。記憶體使用就遵循指定引擎的記憶體管理方式，例如InnoDB的表會先緩存在Buffer Pool中，再透過刷髒線程寫回磁碟檔案。

在5.6中，磁碟暫存表位於tmpdir下，檔案名稱類似

#sql4d2b_8_0.ibd，其中#sql是固定的前綴，4d2b是進程號的十六進位表示，8是MySQL線程號的十六進位表示(show processlist中的id)，0是每個連接從0開始的遞增值，ibd是innodb的磁碟臨時表(透過參數default_tmp_storage_engine控制)。在5.6中，磁碟臨時表創建好後，對應的frm以及引擎檔案就在tmpdir下創建完畢，可以透過檔案系統ls指令查看。在連接關閉後，相應檔案會自動刪除。因此，我們如果在5.6的tmpdir裡面看到很多類似格式檔名，可以透過檔名來判斷是哪個進程，哪個連接使用的臨時表，這個技巧在排查tmpdir目錄佔用過多空間的問題時，尤其適用。使用者明確建立的這種臨時表，在連線釋放的時候，會自動釋放並把空間釋放回作業系統。臨時表的undolog存在undo表空間中，與普通表的undo放在一起。有了undo回滾段，使用者建立的這種臨時表也能支援回滾了。

在5.7中，暫存磁碟表位於ibtmp檔案中，ibtmp檔案位置及大小控制方式由參數innodb_temp_data_file_path控制。明確建立的表的資料和undo都在ibtmp裡面。使用者連線中斷後，臨時表會釋放，但是僅僅是在ibtmp檔案裡面標記一下，空間是不會釋放回作業系統的。如果要釋放空間，需要重新啟動資料庫。另外，要注意的一點是，5.6可以在tmpdir下直接看到創建的文件，但是5.7是創建在ibtmp這個表空間裡面，因此是看不到具體的表文件的。如果需要查看，則需要查看INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO這個表，裡面有一列name，這裡可以看到表名。命名規格與5.6的類似，因此也可以快速找到佔用空間大的連接。

在8.0中，臨時表的資料和undo被進一步分開，資料是存放在ibt檔案中(由參數innodb_temp_tablespaces_dir控制)，undo依然存放在ibtmp檔案中(依然由參數innodb_temp_data_file_path控制)。存放ibt檔案的叫做Session臨時表空間，存放undo的ibtmp叫做Global臨時表空間。這裡介紹一下這個存放資料的Session臨時表空間。 Session臨時表空間，在磁碟上的表現是一組以ibt檔案組成的檔案池。啟動的時候，資料庫會在配置的目錄下重新創建，關閉資料庫的時候刪除。啟動的時候，預設會創建10個ibt文件，每個連接最多使用兩個，一個給用戶創建的臨時表用，另外一個給下文描述的優​​化器創建的隱式臨時表使用。當然只有在需要臨時表的時候，才會創建，如果不需要，則不會佔用ibt檔案。當10個ibt都被使用完後，資料庫會繼續創建，最多創建四十萬個。當連線釋放時候，會自動把這個連線使用的ibt檔案給釋放，同時回收空間。如果要回收Global臨時表空間，依然需要重新啟動。但由於已經把存放資料的檔案分離出來，且其支援動態回收(即連線斷開即釋放空間)，所以5.7上困擾大家多時的空間佔用問題，已經得到了很好的緩解。當然，還是有優化空間的，例如，空間需要在連接斷開後，才能釋放，而理論上，很多空間在某些SQL(如用戶drop了某個顯式創建的臨時表)執行後，即可以釋放。另外，如果需要查看表名，依然查看INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO這個表。要注意的是，8.0上，顯式臨時表不能是壓縮表，而5.6和5.7可以。

優化器隱含建立臨時表

這種臨時表，是資料庫為了輔助某些複雜SQL的執行而建立的輔助表，是否需要臨時表，一般都是由優化器決定。與使用者明確建立的臨時表直接建立磁碟檔案不同，如果需要優化器覺得SQL需要臨時表輔助，會先使用記憶體臨時表，如果超過配置的記憶體(min(tmp_table_size, max_heap_table_siz))，就會轉換成磁碟臨時表，這種磁碟臨時表就類似使用者明確建立的，引擎類型透過參數internal_tmp_disk_storage_engine控制。一般稍微複雜一點的查詢，包括且不限於order by, group by, distinct等，都會用到這種隱含建立的臨時表。使用者可以透過explain指令，在Extra欄位中，看是否有Using temporary這樣的字樣，如果有，就一定要用臨時表。

在5.6中，隱式臨時表依然在tmpdir下，在複雜SQL執行的過程中，就能看到這臨時表，一旦執行結束，就被刪除。值得注意的是，5.6中，這種隱式建立的臨時表，只能用MyISAM引擎，也就是沒有internal_tmp_disk_storage_engine這個參數可以控制。所以，當我們的系統中只有innodb表時，也會看到MyISAM的某些指標在變動，這種情況下，一般都是隱式臨時表的原因。

在5.7中，隱式臨時表是建立在ibtmp檔案中的，SQL結束後，會標記刪除，但是空間仍然不會返還給作業系統，如果需要返還，則需要重新啟動資料庫。另外，5.7支援參數internal_tmp_disk_storage_engine，使用者可以選擇InnoDB或MYISAM表作為磁碟暫存表。

在8.0中，隱式臨時表是建立在Session臨時表空間中的，也就是與使用者明確建立的臨時表的資料放在一起。如果一個連接第一次需要隱式臨時表，那麼資料庫會從ibt檔案構成的池子中取出一個給這個連接使用，直到連接釋放。上文中，我們也提到過，在8.0中，使用者明確建立的臨時表也會從池中分配一個ibt來使用，每個連線最多使用兩個ibt檔案用來儲存臨時表。我們可以查詢INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES來決定ibt檔案的去向。這個表中，每個ibt檔案是一行，目前系統中有幾個ibt檔案就有幾行。有一列叫做ID，如果此列為0，表示此ibt沒有被使用，如果非0，表示被此ID的連接在用，例如ID為8，則表示process_id為8的連接在用這個ibt檔。另外，還有一列purpose，值為INTRINSIC表示是隱式臨時表在用這個ibt，USER則表示是顯示臨時表在用。另外，還有一列size，表示目前的大小。使用者可以查詢這個表來決定整個資料庫臨時表的使用情況，十分方便。

在5.6和5.7中，記憶體臨時表只能使用Memory引擎，到了8.0，多了一種Temptable引擎的選擇。 Temptable在儲存格式有採用了變長存儲，可以節省儲存空間，進一步提高記憶體使用率，減少轉換成磁碟臨時表的次數。如果設定的磁碟臨時表是InnoDB或MYISAM，則需要一個轉換拷貝的消耗。為了盡可能減少消耗，Temptable提出了一種overflow機制，即如果內存臨時表超過配置大小，則使用磁碟空間map的方式，即打開一個文件，然後刪除，留一個句柄進行讀寫操作。讀寫檔案格式和記憶體中格式一樣，這樣就略過了轉換這一步，進一步提升效能。注意，這個功能是在還沒發布的8.0.16版本中才有的，因為還看不到程式碼，只能透過文件猜測其實現。在8.0.16中，參數internal_tmp_disk_storage_engine已經被去掉，磁碟臨時表只能使用InnoDB形式或是TempTable的這種overflow形式。從文件中，我們似乎看出官方比較推薦使用TempTable這個新的引擎。具體效能提升情況，還需要等程式碼發布後，測試過才能下結論。

臨時檔案

比較臨時表，臨時檔案對大家可能更陌生，臨時檔案更多的被使用在快取數據，排序數據的場景中。一般情況下，被快取或排序的數據，先放在記憶體中，如果內存放不下，才會使用磁碟臨時檔案的方式。臨時文件的使用方式與一般的表也不太一樣，一般的表創建完後，就開始讀寫數據，使用完後，才把文件刪除，但是臨時文件的使用方式不一樣，在創建完後(使用mkstemp系統函數)，馬上呼叫unlink刪除文件，但是不close文件，後續使用原來的句柄操作文件。這樣的好處是，當進程異常crash，不會有臨時文件因為沒被刪除而殘留，但是壞處也是明顯的，我們在文件系統上使用ls命令就看不到這個文件，需要使用lsof L1來查看這種deleted屬性的檔案。

目前，我們主要在一下場景使用臨時檔案：

DDL中的暫存檔案

在做online DDL的過程中，很多操作需要對原表進行重建，對錶重建前，需要對各種二級索引排序，而大量資料的排序，不太可能在記憶體中完成，需要依賴外部排序演算法，MySQL使用了歸併排序。這個過程中就需要建立臨時檔案。一般需要的空間大小與原表差不多。但使用完後，會馬上清理，所以做DDL的時候，需要保留出足夠的空間。使用者可以透過指定innodb_tmpdir來指定這種排序檔案的路徑。這個參數可以動態修改，一般把他設定在有足夠磁碟空間的路徑上。臨時檔案的名字一般是類似ibXXXXXX，其中ib是固定前綴，XXXXXX是大小寫字母以及數字的隨機組合。

在做online DDL中，我們是允許使用者對原表做DML操作的，也就是增刪改查。我們無法直接插入原表中，因此需要一個地方記錄原表的修改操作，在DDL結束後，再套用在新表上。這個記錄的地方就是online log，當然如果改動少的話，直接存在內存裡(參數innodb_sort_buffer_size可控制，同時這個參數也控制online log每個讀寫塊的大小)面即可。這個onlinelog也是用暫存，建立在innodb_tmpdir，最大大小為參數innodb_online_alter_log_max_size控制，如果超過這個大小了，DDL就會失敗。臨時文件的名字也類似上述的排序臨時文件的名字。

在online DDL的最後階段，需要把排序完的檔案和中途產生的DML全都應用到一個中間檔案上，中間檔案檔案名稱類似#sql-ib53-522550444.ibd，其中#sql-ib是固定的前綴，53是InnoDB層的table id，522550444是隨機產生的數字。同時，在server層也會產生一個frm檔(8.0中沒有)，檔名類似#sql-4d2b_2a.frm，其中#sql是固定前綴，4d2b 是進程號的十六進位表示，2a是線程號的十六進位表示(show processlist中的id)。因此我們也可以透過這個命名規則來找到哪個執行緒在做DDL。這裡要注意一點，這裡說的中間文件，其實算是一個臨時表，並不是上文說中臨時文件，這些中間文件可以透過ls來查看。當在DDL的最後一步，會把這兩個臨時檔案命名回原來的表名。正因為這個特性，所以當資料庫中途crash的時候，可能會在磁碟上留下殘餘無用的檔案。遇到這種情況，可以先把frm檔案重新命名成與ibd檔案一樣的名字，然後使用DROP TABLE#mysql50##sql-ib53-522550444`來清理殘餘的檔案。注意，如果不用drop指令，直接刪除ibd文件，可能會導致資料字典裡面依然有殘餘的訊息，做法不太優雅。當然，在8.0中，由於使用了原子的資料字典，就不會出現這種殘餘檔了。

BinLog中的快取操作

BinLog只有在交易提交的時候才會寫入到檔案中，在沒提交前，會先放在記憶體中(由參數binlog_cache_size控制)，如果記憶體放慢了，就會創建臨時文件，使用方法也是先透過mkstemp創建，然後直接unlink，留一個句柄讀寫。臨時檔案名稱類似MLXXXXXX，其中ML是固定前綴，XXXXXX是大小寫字母以及數字的隨機組合。單一交易的BinLog太大，可能會導致整個BinLog的大小也過大，進而影響同步，因此我們需要盡可能控制交易大小。

優化建立的暫存檔案

有些操作，除了在引擎層需要依賴隱式暫存表來輔助複雜SQL的計算，在Server層，也會創建暫存檔案來輔助，例如order by操作，會呼叫filesort函數。這個函數也會先使用記憶體(sort_buffer_size)排序，如果不夠，就會建立一個暫存文件，輔助排序。檔案名稱類似MYXXXXXX，其中MY是固定前綴，XXXXXX是大小寫字母以及數字的隨機組合。

Load data中使用的臨時檔案

在BinLog複製中，如果在主庫上使用了Load Data命令，即從檔案中導數據，資料庫會把整個文件寫入RelayLog中，然後傳到備庫，備庫解析RelayLog，從中抽取對應的Load文件，然後在備庫上應用。備庫上這個檔案儲存的位置由參數slave_load_tmpdir控制。文件中建議這個目錄不要配置在實體機的記憶體目錄或重新啟動後會刪除的目錄。因為複製依賴這個文件，如果意外被刪除，會導致複製中斷。

其他

除了上文所述的幾個地方外，還有其他幾個地方也會用到暫存檔案：

• 在InnoDB層，啟動的時候會建立多個暫存檔案用來儲存：最後一次外鍵或是唯一鍵錯誤; 最後一次死鎖的資訊; 最後的innodb狀態資訊。用臨時檔案而不用記憶體的原因猜測是，記憶體使用率不會因為寫這些指標而波動。
• 在Server層，分割區表使用show create table時，會用到暫存檔案。另外在MYISAM表內部排序的時候也會用到臨時文件。

相關參數

*** tmpdir: *** 這個參數是暫存目錄的配置，在5.6以及之前的版本，臨時表/檔案預設都會放在這裡。這個參數可以配置多個目錄，這樣就可以輪流在不同的目錄上建立臨時表/文件，如果不同的目錄分別指向不同的磁碟，就可以達到分流的目的。
*** innodb_tmpdir: *** 這個參數只要是DDL中的排序暫存檔案所使用的。其佔用的空間會很大，建議單獨配置。這個參數可以動態設置，也是Session變數。
*** slave_load_tmpdir: *** 這個參數主要是給BinLog複製中Load Data時，設定備庫存放臨時檔案位置時使用。因為資料庫Crash後還需要依賴Load資料的文件，建議不要配置重啟後會刪除資料的目錄。
*** internal_tmp_disk_storage_engine: *** 當隱式臨時表被轉換成磁碟臨時表時，使用哪種引擎，預設只有MyISAM和InnoDB。 5.7及以後的版本才支援。 8.0.16版本後取消的這個參數。
*** internal_tmp_mem_storage_engine: *** 隱式臨時表在記憶體時用的儲存引擎，可以選擇Memory或Temptable引擎。建議選擇新的Temptable引擎。
*** default_tmp_storage_engine: *** 預設的明確臨時表的引擎，即使用者透過SQL語句所建立的臨時表的引擎。
*** tmp_table_size: *** min(tmp_table_size,max_heap_table_size)是隱式臨時表的記憶體大小，超過這個值會轉換成磁碟臨時表。
*** max_heap_table_size: *** 使用者建立的Memory記憶體表的記憶體限制大小。
*** big_tables: *** 記憶體臨時表轉換成磁碟臨時表需要有個轉換操作，需要在不同引擎格式中轉換，這個是需要消耗的。如果我們能事先知道執行某個SQL需要用到磁碟臨時表，也就是記憶體肯定不夠用，可以設定這個參數，這樣優化器就跳過使用記憶體臨時表，直接使用磁碟臨時表，減少開銷。
*** temptable_max_ram: *** 這個參數是8.0後才有的，主要是給Temptable引擎指定記憶體大小，超過這個後，要嘛就轉換成磁碟臨時表，要嘛就使用自帶的overflow機制。
*** temptable_use_mmap: *** 是否使用Temptable的overflow機制。

總結建議

MySQL的臨時表以及臨時文件其實是一個比較複雜的話題，涉及的模組比較多，出現的時機比較難把握，導致排查問題相比普通表也難不少。建議讀者結合程式碼細細研究，這樣才能定位在線上可能出現的棘手問題。

以上是MySQL學習之臨時表相關總結的詳細內容。更多資訊請關注PHP中文網其他相關文章！