溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章給大家分享的是有關MySQL學習之臨時表是什么的內容。小編覺得挺實用的，因此分享給大家做個參考。一起跟隨小編過來看看吧。
臨時表可以分為磁盤臨時表和內存臨時表，而臨時文件，只會存在于磁盤上，不會存在于內存中。具體來說，臨時表的內存形態(tài)有Memory引擎和Temptable引擎，主要區(qū)別是對字符類型(varchar, blob，text類型)的存儲方式，前者不管實際字符多少，都是用定長的空間存儲，后者會用變長的空間存儲，這樣提高了內存中的存儲效率，有更多的數據可以放在內存中處理而不是轉換成磁盤臨時表。Memory引擎從早期的5.6就可以使用，Temptable是8.0引入的新的引擎。另外一方面，磁盤臨時表也有三種形態(tài)，一種是MyISAM表，一種是InnoDB臨時表，另外一種是Temptable的文件map表。其中最后一種方式，是8.0提供的。
在5.6以及以前的版本，磁盤臨時表都是放在數據庫配置的臨時目錄，磁盤臨時表的undolog都是與普通表的undo放在一起(注意由于磁盤臨時表在數據庫重啟后就被刪除了，不需要redolog通過奔潰恢復來保證事務的完整性，所以不需要寫redolog，但是undolog還是需要的，因為需要支持回滾)。
在MySQL 5.7后，磁盤臨時表的數據和undo都被獨立出來，放在一個單獨的表空間ibtmp1里面。之所以把臨時表獨立出來，主要是為了減少創(chuàng)建刪除表時維護元數據的開銷。
在MySQL 8.0后，磁盤臨時表的數據單獨放在Session臨時表空間池(#innodb_temp目錄下的ibt文件)里面，臨時表的undo放在global的表空間ibtmp1里面。另外一個大的改進是，8.0的磁盤臨時表數據占用的空間在連接斷開后，就能釋放給操作系統(tǒng)，而5.7的版本中需要重啟才能釋放。
目前有以下兩種情況會用到臨時表：
這種是用戶通過顯式的執(zhí)行命令create temporary table創(chuàng)建的表，引擎的類型要么顯式指定，要么使用默認配置的值(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中，磁盤臨時表創(chuàng)建好后，對應的frm以及引擎文件就在tmpdir下創(chuàng)建完畢，可以通過文件系統(tǒng)ls命令查看到。在連接關閉后，相應文件自動刪除。因此，我們如果在5.6的tmpdir里面看到很多類似格式文件名，可以通過文件名來判斷是哪個進程，哪個連接使用的臨時表，這個技巧在排查tmpdir目錄占用過多空間的問題時，尤其適用。用戶顯式創(chuàng)建的這種臨時表，在連接釋放的時候，會自動釋放并把空間釋放回操作系統(tǒng)。臨時表的undolog存在undo表空間中，與普通表的undo放在一起。有了undo回滾段，用戶創(chuàng)建的這種臨時表也能支持回滾了。
在5.7中，臨時磁盤表位于ibtmp文件中，ibtmp文件位置及大小控制方式由參數innodb_temp_data_file_path控制。顯式創(chuàng)建的表的數據和undo都在ibtmp里面。用戶連接斷開后，臨時表會釋放，但是僅僅是在ibtmp文件里面標記一下，空間是不會釋放回操作系統(tǒng)的。如果要釋放空間，需要重啟數據庫。另外，需要注意的一點是，5.6可以在tmpdir下直接看到創(chuàng)建的文件，但是5.7是創(chuàng)建在ibtmp這個表空間里面，因此是看不到具體的表文件的。如果需要查看，則需要查看INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO這個表，里面有一列name，這里可以看到表名。命名規(guī)格與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文件組成的文件池。啟動的時候，數據庫會在配置的目錄下重新創(chuàng)建，關閉數據庫的時候刪除。啟動的時候，默認會創(chuàng)建10個ibt文件，每個連接最多使用兩個，一個給用戶創(chuàng)建的臨時表用，另外一個給下文描述的優(yōu)化器創(chuàng)建的隱式臨時表使用。當然只有在需要臨時表的時候，才會創(chuàng)建，如果不需要，則不會占用ibt文件。當10個ibt都被使用完后，數據庫會繼續(xù)創(chuàng)建，最多創(chuàng)建四十萬個。當連接釋放時候，會自動把這個連接使用的ibt文件給釋放，同時回收空間。如果要回收Global臨時表空間，依然需要重啟。但是由于已經把存放數據的文件分離出來，且其支持動態(tài)回收(即連接斷開即釋放空間)，所以5.7上困擾大家多時的空間占用問題，已經得到了很好的緩解。當然，還是有優(yōu)化空間的，例如，空間需要在連接斷開后，才能釋放，而理論上，很多空間在某些SQL(如用戶drop了某個顯式創(chuàng)建的臨時表)執(zhí)行后，即可以釋放。另外，如果需要查看表名，依然查看INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO這個表。需要注意的是，8.0上，顯式臨時表不能是壓縮表，而5.6和5.7可以。
這種臨時表，是數據庫為了輔助某些復雜SQL的執(zhí)行而創(chuàng)建的輔助表，是否需要臨時表，一般都是由優(yōu)化器決定。與用戶顯式創(chuàng)建的臨時表直接創(chuàng)建磁盤文件不同，如果需要優(yōu)化器覺得SQL需要臨時表輔助，會先使用內存臨時表，如果超過配置的內存(min(tmp_table_size, max_heap_table_siz))，就會轉化成磁盤臨時表，這種磁盤臨時表就類似用戶顯式創(chuàng)建的，引擎類型通過參數internal_tmp_disk_storage_engine控制。一般稍微復雜一點的查詢，包括且不限于order by, group by, distinct等，都會用到這種隱式創(chuàng)建的臨時表。用戶可以通過explain命令，在Extra列中，看是否有Using temporary這樣的字樣，如果有，就肯定要用臨時表。
在5.6中，隱式臨時表依然在tmpdir下，在復雜SQL執(zhí)行的過程中，就能看到這臨時表，一旦執(zhí)行結束，就被刪除。值得注意的是，5.6中，這種隱式創(chuàng)建的臨時表，只能用MyISAM引擎，即沒有internal_tmp_disk_storage_engine這個參數可以控制。所以，當我們的系統(tǒng)中只有innodb表時，也會看到MyISAM的某些指標在變動，這種情況下，一般都是隱式臨時表的原因。
在5.7中，隱式臨時表是創(chuàng)建在ibtmp文件中的，SQL結束后，會標記刪除，但是空間依然不會返還給操作系統(tǒng)，如果需要返還，則需要重啟數據庫。另外，5.7支持參數internal_tmp_disk_storage_engine，用戶可以選擇InnoDB或者MYISAM表作為磁盤臨時表。
在8.0中，隱式臨時表是創(chuàng)建在Session臨時表空間中的，即與用戶顯式創(chuàng)建的臨時表的數據放在一起。如果一個連接第一次需要隱式臨時表，那么數據庫會從ibt文件構成的池子中取出一個給這個連接使用，直到連接釋放。上文中，我們也提到過，在8.0中，用戶顯式創(chuàng)建的臨時表也會從池子中分配一個ibt來使用，每個連接最多使用兩個ibt文件用來存儲臨時表。我們可以查詢INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES來確定ibt文件的去向。這個表中，每個ibt文件是一行，當前系統(tǒng)中有幾個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在存儲格式有采用了變長存儲，可以節(jié)省存儲空間，進一步提高內存使用率，減少轉換成磁盤臨時表的次數。如果設置的磁盤臨時表是InnoDB或者MYISAM，則需要一個轉換拷貝的消耗。為了盡可能減少消耗，Temptable提出了一種overflow機制，即如果內存臨時表超過配置大小，則使用磁盤空間map的方式，即打開一個文件，然后刪除，留一個句柄進行讀寫操作。讀寫文件格式和內存中格式一樣，這樣就略過了轉換這一步，進一步提高性能。注意，這個功能是在還沒發(fā)布的8.0.16版本中才有的，因為還看不到代碼，只能通過文檔猜測其實現。在8.0.16中，參數internal_tmp_disk_storage_engine已經被去掉，磁盤臨時表只能使用InnoDB形式或者TempTable的這種overflow形式。從文檔中，我們似乎看出官方比較推薦使用TempTable這個新的引擎。具體性能提升情況，還需要等代碼發(fā)布后，測試過才能得出結論。
相比臨時表，臨時文件對大家可能更加陌生，臨時文件更多的被使用在緩存數據，排序數據的場景中。一般情況下，被緩存或者排序的數據，首先放在內存中，如果內存放不下，才會使用磁盤臨時文件的方式。臨時文件的使用方式與一般的表也不太一樣，一般的表創(chuàng)建完后，就開始讀寫數據，使用完后，才把文件刪除，但是臨時文件的使用方式不一樣，在創(chuàng)建完后(使用mkstemp系統(tǒng)函數)，馬上調用unlink刪除文件，但是不close文件，后續(xù)使用原來的句柄操作文件。這樣的好處是，當進程異常crash，不會有臨時文件因為沒被刪除而殘留，但是壞處也是明顯的，我們在文件系統(tǒng)上使用ls命令就看不到這個文件，需要使用lsof +L1來查看這種deleted屬性的文件。
目前，我們主要在一下場景使用臨時文件：
在做online DDL的過程中，很多操作需要對原表進行重建，對表重建前，需要對各種二級索引排序，而大量數據的排序，不太可能在內存中完成，需要依賴外部排序算法，MySQL使用了歸并排序。這個過程中就需要創(chuàng)建臨時文件。一般需要的空間大小與原表差不多。但是在使用完之后，會馬上清理，所以在做DDL的時候，需要保留出足夠的空間。用戶可以通過指定innodb_tmpdir來指定這種排序文件的路徑。這個參數可以動態(tài)修改，一般把他設置在有足夠磁盤空間的路徑上。臨時文件的名字一般是類似ibXXXXXX，其中ib是固定前綴，XXXXXX是大小寫字母以及數字的隨機組合。
在做online DDL中，我們是允許用戶對原表做DML操作的，即增刪改查。我們不能直接插入原表中，因此需要一個地方記錄對原表的修改操作，在DDL結束后，再應用在新表上。這個記錄的地方就是online log，當然如果改動少的話，直接存在內存里(參數innodb_sort_buffer_size可控制，同時這個參數也控制online log每個讀寫塊的大小)面即可。這個onlinelog也是用臨時文件存，創(chuàng)建在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)。因此我們也可以通過這個命名規(guī)則來找到哪個線程在做DDL。這里需要注意一點，這里說的中間文件，其實算是一個臨時表，并不是上文說中臨時文件，這些中間文件可以通過ls來查看。當在DDL中的最后一步，會把這兩個臨時文件命名回原來的表名。正因為這個特性，所以當數據庫中途crash的時候，可能會在磁盤上留下殘余無用的文件。遇到這種情況，可以先把frm文件重命名成與ibd文件一樣的名字，然后使用DROP TABLE#mysql50##sql-ib53-522550444`來清理殘余的文件。注意，如果不用drop命令，直接刪除ibd文件，可能會導致數據字典里面依然有殘余的信息，做法不太優(yōu)雅。當然，在8.0中，由于使用了原子的數據字典，就不會出現這種殘余文件了。
BinLog只有在事務提交的時候才會寫入到文件中，在沒提交前，會先放在內存中(由參數binlog_cache_size控制)，如果內存放慢了，就會創(chuàng)建臨時文件，使用方法也是先通過mkstemp創(chuàng)建，然后直接unlink，留一個句柄讀寫。臨時文件名類似MLXXXXXX，其中ML是固定前綴，XXXXXX是大小寫字母以及數字的隨機組合。單個事務的BinLog太大，可能會導致整個BinLog的大小也過大，從而影響同步，因此我們需要盡可能控制事務大小。
有些操作，除了在引擎層需要依賴隱式臨時表來輔助復雜SQL的計算，在Server層，也會創(chuàng)建臨時文件來輔助，比如order by操作，會調用filesort函數。這個函數也會先使用內存(sort_buffer_size)排序，如果不夠，就會創(chuàng)建一個臨時文件，輔助排序。文件名類似MYXXXXXX，其中MY是固定前綴，XXXXXX是大小寫字母以及數字的隨機組合。
在BinLog復制中，如果在主庫上使用了Load Data命令，即從文件中導數據，數據庫會把整個文件寫入到RelayLog中，然后傳到備庫，備庫解析RelayLog，從中抽取出對應的Load文件，然后在備庫上應用。備庫上這個文件存儲的位置由參數slave_load_tmpdir控制。文檔中建議這個目錄不要配置在物理機的內存目錄或者重啟后會刪除的目錄。因為復制依賴這個文件，如果意外被刪除，會導致復制中斷。
除了上文所述的幾個地方外，還有其他幾個地方也會用到臨時文件：
*** tmpdir: *** 這個參數是臨時目錄的配置，在5.6以及之前的版本，臨時表/文件默認都會放在這里。這個參數可以配置多個目錄，這樣就可以輪流在不同的目錄上創(chuàng)建臨時表/文件，如果不同的目錄分別指向不同的磁盤，就可以達到分流的目的。
*** innodb_tmpdir: *** 這個參數只要是被DDL中的排序臨時文件使用的。其占用的空間會很大，建議單獨配置。這個參數可以動態(tài)設置，也是一個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語句創(chuàng)建的臨時表的引擎。
*** tmp_table_size: *** min(tmp_table_size,max_heap_table_size)是隱式臨時表的內存大小，超過這個值會轉換成磁盤臨時表。
*** max_heap_table_size: *** 用戶創(chuàng)建的Memory內存表的內存限制大小。
*** big_tables: *** 內存臨時表轉換成磁盤臨時表需要有個轉化操作，需要在不同引擎格式中轉換，這個是需要消耗的。如果我們能提前知道執(zhí)行某個SQL需要用到磁盤臨時表，即內存肯定不夠用，可以設置這個參數，這樣優(yōu)化器就跳過使用內存臨時表，直接使用磁盤臨時表，減少開銷。
*** temptable_max_ram: *** 這個參數是8.0后才有的，主要是給Temptable引擎指定內存大小，超過這個后，要么就轉換成磁盤臨時表，要么就使用自帶的overflow機制。
*** temptable_use_mmap: *** 是否使用Temptable的overflow機制。
感謝各位的閱讀！關于MySQL學習之臨時表是什么就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！
免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng)、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
