一、背景
最近在使用MySQL5.6和5.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之前，産生的臨時表是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下，磁盤臨時表的undolog都是與普通表的undo放在一起(注意由于磁盤臨時表在資料庫重新開機後就被删除了，不需要redolog通過奔潰恢複來保證事務的完整性，是以不需要寫redolog，但是undolog還是需要的，因為需要支援復原)。檔案名類似#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目錄占用過多空間的問題時尤其适用。使用者顯式建立的這種臨時表，在連接配接釋放的時候，會自動釋放并把空間釋放回作業系統。
阿裡雲RDS有個loose_rds_max_tmp_disk_space參數控制最大磁盤臨時表空間，開源MySQL沒有這個參數。RDS這個參數也是為了避免磁盤臨時表過大導緻磁盤空間被打滿了導緻執行個體鎖定等問題。
2.3.2 MySQL5.7中的臨時表
在MySQL5.7中，臨時磁盤表位于ibtmp1檔案中，ibtmp1檔案位置及大小控制方式由參數innodb_temp_data_file_path控制，阿裡雲RDS預設ibtmp1可以無限擴充。顯式建立的表的資料和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中的臨時檔案
參數：innodb_sort_buffer_size、innodb_online_alter_log_max_size
2、BinLog中的緩存操作
參數：binlog_cache_size
3、優化器建立的臨時檔案
參數：sort_buffer_size
4、Load data中用的臨時檔案
5、MYISAM表内部排序的臨時檔案
參數：internal_tmp_disk_storage_engine
臨時檔案名字：
1、執行計劃filesort 檔案名字MY開頭
2、大事物binary log緩存 檔案名字ML開頭
3、壓縮的tempory table #sql
4、online DDL 涉及排序#ib
5、ALGORITHM copy 名字為 #sql-
6、ALGORITHM inplace 名字為 #sql-ib
三、相關參數
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