MySQL臨時表機制的演變(5.6-8.0)

一、背景

  最近在使用MySQL5.7的過程中，碰到了一個問題，問題是這樣的：由于一個大查詢導緻臨時表空間ibtmp1暴漲，進而導緻磁盤打滿，資料庫無法響應。但是在相同配置的8.0中卻無法複現，為此我分析了MySQL各版本對臨時表的處理方式，特此分享。

二、MySQL的臨時表和臨時檔案

2.1 臨時表分類

  MySQL的臨時表分為兩種，一種是使用者建立的臨時表，另一種是由優化器建立的内部臨時表；臨時表同時又可以分為記憶體臨時表和磁盤臨時表。

2.2 記憶體臨時表

  記憶體臨時表有Memory引擎和Temptable引擎，Memory引擎從MySQL5.6開始可以使用，Temptable引擎是8.0引入的新的引擎。Memory引擎不管實際字元多少，都是用定長的空間存儲，Temptable引擎會用變長的空間存儲，提高了記憶體中的存儲效率，有更多的資料可以放在記憶體中處理而不是轉換成磁盤臨時表。

2.3 磁盤臨時表

  磁盤臨時表分為MyISAM臨時表、InnoDB臨時表。在MySQL5.6以及以前的版本，磁盤臨時表和臨時檔案都是放在臨時目錄tmpdir下的，磁盤臨時表的undolog都是與普通表的undo放在一起(由于磁盤臨時表在資料庫重新開機後就被删除了，不需要redolog通過崩潰恢複來保證事務的完整性，是以不需要寫redolog，但是undolog還是需要的，因為需要支援復原)。在MySQL 5.7之前，這個SQL 運作中産生的臨時表是MYISAM，而且隻能是MYISAM。從5.7開始提供了參數internal_tmp_disk_storage_engine來定義磁盤臨時表引擎，可選值為MYISAM和INNODB，并且把内部的臨時表預設儲存在臨時表空間ibtmp1(可以用參數innodb_temp_data_file_path 設定初始大小，最大大小和步長)下，推薦設定最大,否則可能磁盤空間會因為大查詢打滿，出現文章開頭的問題。

  但是在MySQL 5.7中沒有解決如下問題:

1、VARCHAR的變長存儲
如果臨時表的字段定義是VARCHAR(200)，那麼映射到記憶體裡處理的字段變為CHAR(200)，造成浪費；
2、大對象的記憶體存儲
比如 TEXT，BLOB， JSON等，都會直接轉化為磁盤存儲。           

  從MySQL8.0開始，臨時表可以使用特有的引擎TempTable,解決了VARCHAR字段的變長存儲以及大對象的記憶體存儲問題。由變量internal_tmp_mem_storage_engine來控制，可選值為TempTable和Memory；新引擎的大小由參數temptable_max_ram來控制，預設為1G。超過了則存儲在磁盤上。并且計數器由表performance_schema.memory_summary_global_by_event_name來存儲。如果設定的磁盤臨時表是InnoDB或者MYISAM，則需要一個轉換拷貝的消耗。為了盡可能減少消耗，Temptable提出了一種overflow機制，即如果記憶體臨時表超過配置大小，則使用磁盤空間map的方式，即打開一個檔案，然後删除，留一個句柄進行讀寫操作。讀寫檔案格式和記憶體中格式一樣，這樣就略過了轉換這一步，進一步提高性能。這個功能是在MySQL8.0.16版本中才有的。

  在MySQL5.7中，磁盤臨時表的資料和undo都被獨立出來，放在臨時表空間ibtmp1中。之是以把臨時表獨立出來，主要是為了減少建立删除表時維護中繼資料的開銷。

  在MySQL8.0中，磁盤臨時表的資料單獨放在會話臨時表空間池(#innodb_temp目錄下的ibt檔案)裡面，臨時表的undo放在全局表空間ibtmp1裡面。另外一個大的改進是，8.0的磁盤臨時表資料占用的空間在連接配接斷開後，就能釋放給作業系統，而5.7的版本中需要重新開機才能釋放。

2.3.1 MySQL5.6中的臨時表

  在MySQL5.6中，磁盤臨時表位于tmpdir下，檔案名類似#sql_4d2b_8_0，其中#sql是固定的字首，4d2b是程序号的十六進制表示，8是MySQL線程号的十六進制表示(show processlist中的id)，0是每個連接配接從0開始的遞增值，ibd是innodb的磁盤臨時表(通過參數default_tmp_storage_engine控制)。在5.6中，磁盤臨時表建立好後，對應的frm以及引擎檔案就可以在tmpdir下檢視到。在連接配接斷開後，相應檔案會自動删除。是以，在5.6的tmpdir裡面看到很多類似格式檔案名，可以通過檔案名來判斷是哪個程序，哪個連接配接使用的臨時表，這個技巧在排查tmpdir目錄占用過多空間的問題時尤其适用。使用者顯式建立的這種臨時表，在連接配接釋放的時候，會自動釋放并把空間釋放回作業系統。臨時表的undolog存在undo表空間中。

2.3.2 MySQL5.7中的臨時表

  在MySQL5.7中，臨時磁盤表位于ibtmp1檔案中，ibtmp1檔案位置及大小控制方式由參數innodb_temp_data_file_path控制。顯式建立的表的資料和undo都在ibtmp1裡面。使用者連接配接斷開後，臨時表會釋放，但是僅僅是在ibtmp1檔案裡面标記一下，空間是不會釋放回作業系統的。如果要釋放空間，需要重新開機資料庫。另外，需要注意的一點是，5.6可以在tmpdir下直接看到建立的檔案，但是5.7是建立在ibtmp1這個表空間裡面，是以是看不到具體的表檔案的。如果需要檢視，則需要檢視INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO這個表，裡面有一列name，這裡可以看到表名。

2.3.3 MySQL8.0中的臨時表

  在MySQL8.0中，臨時表的資料和undo被進一步分開，資料存放在ibt檔案中(由參數innodb_temp_tablespaces_dir控制)，undo依然存放在ibtmp1檔案中(由參數innodb_temp_data_file_path控制)。存放ibt檔案的叫做會話臨時表空間，存放undo的ibtmp1叫做全局臨時表空間。會話臨時表空間，在磁盤上的表現是一組以ibt檔案組成的檔案池。啟動的時候，資料庫會在配置的目錄下重新建立，關閉資料庫的時候删除。啟動的時候，預設會建立10個ibt檔案，每個連接配接最多使用兩個，一個給使用者建立的臨時表用，另外一個給優化器建立的隐式臨時表使用。當然隻有在需要臨時表的時候，才會建立，如果不需要，則不會占用ibt檔案。當10個ibt都被使用完後，資料庫會繼續建立，最多建立四十萬個。當連接配接釋放時候，會自動把這個連接配接使用的ibt檔案給釋放，同時回收空間。如果要回收全局臨時表空間，依然需要重新開機。但是由于已經把存放資料的檔案分離出來，且其支援動态回收，是以在5.7中的空間占用問題，已經得到了很好的緩解。在理論上，很多空間在某些SQL(如使用者drop了某個顯式建立的臨時表)執行後，即可以釋放。另外，如果需要檢視表名，依然檢視INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO這個表。但是在MySQL8.0中顯式臨時表不能是壓縮表，而在5.6和5.7中可以。

2.4 優化器隐式建立臨時表

  優化器隐式建立臨時表是資料庫為了輔助某些複雜SQL的執行而建立的。與使用者顯式建立的臨時表直接建立磁盤檔案不同，如果優化器覺得SQL需要臨時表輔助，會先使用記憶體臨時表，如果超過配置的記憶體((tmp_table_size、max_heap_table_size)二者最小值)，就會轉化成磁盤臨時表，這種磁盤臨時表就類似使用者顯式建立的，引擎類型通過參數internal_tmp_disk_storage_engine控制。

  SQL中存在下列操作會使用到臨時表：

union查詢
對于視圖的操作，比如使用一些TEMPTABLE算法、union或aggregation
子查詢
semi-join 包括not in、exist等
查詢産生的派生表
複雜的group by 和 order by
Insert select 同一個表，mysql會産生一個臨時表緩存select的行
多個表更新
GROUP_CONCAT()或者COUNT(DISTINCT)語句           

  SQL在下列情況會直接使用磁盤臨時表：

表中含有BLOB或者TEXT列
使用union或者union all時，select子句有大于512位元組的列
Show columns或者 desc 表的時候，有BLOB或者TEXT
GROUP BY 或者 DISTINCT 子句中包含長度大于512位元組的列           

2.5 臨時檔案

  臨時檔案更多的被使用在緩存資料，排序資料的場景中。一般情況下，被緩存或者排序的資料，首先放在記憶體中，如果記憶體放不下，才會使用磁盤臨時檔案的方式。臨時檔案的使用方式與一般的表也不太一樣，一般的表建立完後，就開始讀寫資料，使用完後，才把檔案删除，但是臨時檔案的使用方式不一樣，在建立完後，馬上調用unlink删除檔案，但是不close檔案，後續使用原來的句柄操作檔案。這樣的好處是，當程序異常crash，不會有臨時檔案因為沒被删除而殘留，這種檔案需要使用lsof +L1來檢視。

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

1、DDL中的臨時檔案

2、BinLog中的緩存操作

3、優化建立的臨時檔案

4、Load data中用的臨時檔案

5、MYISAM表内部排序的臨時檔案

三、相關參數

tmpdir:這個參數是臨時目錄的配置，在5.6以及之前的版本，臨時表/檔案預設都會放在這裡。這個參數可以配置多個目錄，這樣就可以輪流在不同的目錄上建立臨時表/檔案，如果不同的目錄分别指向不同的磁盤，就可以達到分流的目的。
innodb_tmpdir: 這個參數隻要是被DDL中的排序臨時檔案使用的。其占用的空間會很大，建議單獨配置。這個參數可以動态設定，也是一個會話變量。
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機制，temptable引擎是否磁盤資料轉換成Innodb存儲，還是記憶體映射檔案。           

四、總結

  MySQL的臨時表以及臨時檔案在不同版本中變化較大，隻有清除原理才能更友善的去處理問題。

本文參考：

https://www.cnblogs.com/coderyuhui/p/10773143.html