MySQL學(xué)習(xí)之臨時表是什么

這篇文章給大家分享的是有關(guān)MySQL學(xué)習(xí)之臨時表是什么的內(nèi)容。小編覺得挺實(shí)用的，因此分享給大家做個參考。一起跟隨小編過來看看吧。

創(chuàng)新互聯(lián)基于成都重慶香港及美國等地區(qū)分布式IDC機(jī)房數(shù)據(jù)中心構(gòu)建的電信大帶寬，聯(lián)通大帶寬，移動大帶寬，多線BGP大帶寬租用,是為眾多客戶提供專業(yè)服務(wù)器托管報價，主機(jī)托管價格性價比高，為金融證券行業(yè)成都聯(lián)通服務(wù)器托管，ai人工智能服務(wù)器托管提供bgp線路100M獨(dú)享，G口帶寬及機(jī)柜租用的專業(yè)成都idc公司。

臨時表

臨時表可以分為磁盤臨時表和內(nèi)存臨時表，而臨時文件，只會存在于磁盤上，不會存在于內(nèi)存中。具體來說，臨時表的內(nèi)存形態(tài)有Memory引擎和Temptable引擎，主要區(qū)別是對字符類型(varchar, blob，text類型)的存儲方式，前者不管實(shí)際字符多少，都是用定長的空間存儲，后者會用變長的空間存儲，這樣提高了內(nèi)存中的存儲效率，有更多的數(shù)據(jù)可以放在內(nèi)存中處理而不是轉(zhuǎn)換成磁盤臨時表。Memory引擎從早期的5.6就可以使用，Temptable是8.0引入的新的引擎。另外一方面，磁盤臨時表也有三種形態(tài)，一種是MyISAM表，一種是InnoDB臨時表，另外一種是Temptable的文件map表。其中最后一種方式，是8.0提供的。

在5.6以及以前的版本，磁盤臨時表都是放在數(shù)據(jù)庫配置的臨時目錄，磁盤臨時表的undolog都是與普通表的undo放在一起(注意由于磁盤臨時表在數(shù)據(jù)庫重啟后就被刪除了，不需要redolog通過奔潰恢復(fù)來保證事務(wù)的完整性，所以不需要寫redolog，但是undolog還是需要的，因?yàn)樾枰С只貪L)。

在MySQL 5.7后，磁盤臨時表的數(shù)據(jù)和undo都被獨(dú)立出來，放在一個單獨(dú)的表空間ibtmp1里面。之所以把臨時表獨(dú)立出來，主要是為了減少創(chuàng)建刪除表時維護(hù)元數(shù)據(jù)的開銷。

在MySQL 8.0后，磁盤臨時表的數(shù)據(jù)單獨(dú)放在Session臨時表空間池(#innodb_temp目錄下的ibt文件)里面，臨時表的undo放在global的表空間ibtmp1里面。另外一個大的改進(jìn)是，8.0的磁盤臨時表數(shù)據(jù)占用的空間在連接斷開后，就能釋放給操作系統(tǒng)，而5.7的版本中需要重啟才能釋放。

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

用戶顯式創(chuàng)建臨時表

這種是用戶通過顯式的執(zhí)行命令create temporary table創(chuàng)建的表，引擎的類型要么顯式指定，要么使用默認(rèn)配置的值(default_tmp_storage_engine)。內(nèi)存使用就遵循指定引擎的內(nèi)存管理方式，比如InnoDB的表會先緩存在Buffer Pool中，然后通過刷臟線程寫回磁盤文件。

在5.6中，磁盤臨時表位于tmpdir下，文件名類似#sql4d2b_8_0.ibd，其中#sql是固定的前綴，4d2b是進(jìn)程號的十六進(jìn)制表示，8是MySQL線程號的十六進(jìn)制表示(show processlist中的id)，0是每個連接從0開始的遞增值，ibd是innodb的磁盤臨時表(通過參數(shù)default_tmp_storage_engine控制)。在5.6中，磁盤臨時表創(chuàng)建好后，對應(yīng)的frm以及引擎文件就在tmpdir下創(chuàng)建完畢，可以通過文件系統(tǒng)ls命令查看到。在連接關(guān)閉后，相應(yīng)文件自動刪除。因此，我們?nèi)绻?.6的tmpdir里面看到很多類似格式文件名，可以通過文件名來判斷是哪個進(jìn)程，哪個連接使用的臨時表，這個技巧在排查tmpdir目錄占用過多空間的問題時，尤其適用。用戶顯式創(chuàng)建的這種臨時表，在連接釋放的時候，會自動釋放并把空間釋放回操作系統(tǒng)。臨時表的undolog存在undo表空間中，與普通表的undo放在一起。有了undo回滾段，用戶創(chuàng)建的這種臨時表也能支持回滾了。

在5.7中，臨時磁盤表位于ibtmp文件中，ibtmp文件位置及大小控制方式由參數(shù)innodb_temp_data_file_path控制。顯式創(chuàng)建的表的數(shù)據(jù)和undo都在ibtmp里面。用戶連接斷開后，臨時表會釋放，但是僅僅是在ibtmp文件里面標(biāo)記一下，空間是不會釋放回操作系統(tǒng)的。如果要釋放空間，需要重啟數(shù)據(jù)庫。另外，需要注意的一點(diǎn)是，5.6可以在tmpdir下直接看到創(chuàng)建的文件，但是5.7是創(chuàng)建在ibtmp這個表空間里面，因此是看不到具體的表文件的。如果需要查看，則需要查看INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO這個表，里面有一列name，這里可以看到表名。命名規(guī)格與5.6的類似，因此也可以快速找到占用空間大的連接。

在8.0中，臨時表的數(shù)據(jù)和undo被進(jìn)一步分開，數(shù)據(jù)是存放在ibt文件中(由參數(shù)innodb_temp_tablespaces_dir控制)，undo依然存放在ibtmp文件中(依然由參數(shù)innodb_temp_data_file_path控制)。存放ibt文件的叫做Session臨時表空間，存放undo的ibtmp叫做Global臨時表空間。這里介紹一下這個存放數(shù)據(jù)的Session臨時表空間。Session臨時表空間，在磁盤上的表現(xiàn)是一組以ibt文件組成的文件池。啟動的時候，數(shù)據(jù)庫會在配置的目錄下重新創(chuàng)建，關(guān)閉數(shù)據(jù)庫的時候刪除。啟動的時候，默認(rèn)會創(chuàng)建10個ibt文件，每個連接最多使用兩個，一個給用戶創(chuàng)建的臨時表用，另外一個給下文描述的優(yōu)化器創(chuàng)建的隱式臨時表使用。當(dāng)然只有在需要臨時表的時候，才會創(chuàng)建，如果不需要，則不會占用ibt文件。當(dāng)10個ibt都被使用完后，數(shù)據(jù)庫會繼續(xù)創(chuàng)建，最多創(chuàng)建四十萬個。當(dāng)連接釋放時候，會自動把這個連接使用的ibt文件給釋放，同時回收空間。如果要回收Global臨時表空間，依然需要重啟。但是由于已經(jīng)把存放數(shù)據(jù)的文件分離出來，且其支持動態(tài)回收(即連接斷開即釋放空間)，所以5.7上困擾大家多時的空間占用問題，已經(jīng)得到了很好的緩解。當(dāng)然，還是有優(yōu)化空間的，例如，空間需要在連接斷開后，才能釋放，而理論上，很多空間在某些SQL(如用戶drop了某個顯式創(chuàng)建的臨時表)執(zhí)行后，即可以釋放。另外，如果需要查看表名，依然查看INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO這個表。需要注意的是，8.0上，顯式臨時表不能是壓縮表，而5.6和5.7可以。

優(yōu)化器隱式創(chuàng)建臨時表

這種臨時表，是數(shù)據(jù)庫為了輔助某些復(fù)雜SQL的執(zhí)行而創(chuàng)建的輔助表，是否需要臨時表，一般都是由優(yōu)化器決定。與用戶顯式創(chuàng)建的臨時表直接創(chuàng)建磁盤文件不同，如果需要優(yōu)化器覺得SQL需要臨時表輔助，會先使用內(nèi)存臨時表，如果超過配置的內(nèi)存(min(tmp_table_size, max_heap_table_siz))，就會轉(zhuǎn)化成磁盤臨時表，這種磁盤臨時表就類似用戶顯式創(chuàng)建的，引擎類型通過參數(shù)internal_tmp_disk_storage_engine控制。一般稍微復(fù)雜一點(diǎn)的查詢，包括且不限于order by, group by, distinct等，都會用到這種隱式創(chuàng)建的臨時表。用戶可以通過explain命令，在Extra列中，看是否有Using temporary這樣的字樣，如果有，就肯定要用臨時表。

在5.6中，隱式臨時表依然在tmpdir下，在復(fù)雜SQL執(zhí)行的過程中，就能看到這臨時表，一旦執(zhí)行結(jié)束，就被刪除。值得注意的是，5.6中，這種隱式創(chuàng)建的臨時表，只能用MyISAM引擎，即沒有internal_tmp_disk_storage_engine這個參數(shù)可以控制。所以，當(dāng)我們的系統(tǒng)中只有innodb表時，也會看到MyISAM的某些指標(biāo)在變動，這種情況下，一般都是隱式臨時表的原因。

在5.7中，隱式臨時表是創(chuàng)建在ibtmp文件中的，SQL結(jié)束后，會標(biāo)記刪除，但是空間依然不會返還給操作系統(tǒng)，如果需要返還，則需要重啟數(shù)據(jù)庫。另外，5.7支持參數(shù)internal_tmp_disk_storage_engine，用戶可以選擇InnoDB或者M(jìn)YISAM表作為磁盤臨時表。

在8.0中，隱式臨時表是創(chuàng)建在Session臨時表空間中的，即與用戶顯式創(chuàng)建的臨時表的數(shù)據(jù)放在一起。如果一個連接第一次需要隱式臨時表，那么數(shù)據(jù)庫會從ibt文件構(gòu)成的池子中取出一個給這個連接使用，直到連接釋放。上文中，我們也提到過，在8.0中，用戶顯式創(chuàng)建的臨時表也會從池子中分配一個ibt來使用，每個連接最多使用兩個ibt文件用來存儲臨時表。我們可以查詢INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES來確定ibt文件的去向。這個表中，每個ibt文件是一行，當(dāng)前系統(tǒng)中有幾個ibt文件就有幾行。有一列叫做ID，如果此列為0，表示此ibt沒有被使用，如果非0，表示被此ID的連接在用，比如ID為8，則表示process_id為8的連接在用這個ibt文件。另外，還有一列purpose，值為INTRINSIC表示是隱式臨時表在用這個ibt，USER則表示是顯示臨時表在用。此外，還有一列size，表示當(dāng)前的大小。用戶可以查詢這個表來確定整個數(shù)據(jù)庫臨時表的使用情況，十分方便。

在5.6和5.7中，內(nèi)存臨時表只能使用Memory引擎，到了8.0，多了一種Temptable引擎的選擇。Temptable在存儲格式有采用了變長存儲，可以節(jié)省存儲空間，進(jìn)一步提高內(nèi)存使用率，減少轉(zhuǎn)換成磁盤臨時表的次數(shù)。如果設(shè)置的磁盤臨時表是InnoDB或者M(jìn)YISAM，則需要一個轉(zhuǎn)換拷貝的消耗。為了盡可能減少消耗，Temptable提出了一種overflow機(jī)制，即如果內(nèi)存臨時表超過配置大小，則使用磁盤空間map的方式，即打開一個文件，然后刪除，留一個句柄進(jìn)行讀寫操作。讀寫文件格式和內(nèi)存中格式一樣，這樣就略過了轉(zhuǎn)換這一步，進(jìn)一步提高性能。注意，這個功能是在還沒發(fā)布的8.0.16版本中才有的，因?yàn)檫€看不到代碼，只能通過文檔猜測其實(shí)現(xiàn)。在8.0.16中，參數(shù)internal_tmp_disk_storage_engine已經(jīng)被去掉，磁盤臨時表只能使用InnoDB形式或者TempTable的這種overflow形式。從文檔中，我們似乎看出官方比較推薦使用TempTable這個新的引擎。具體性能提升情況，還需要等代碼發(fā)布后，測試過才能得出結(jié)論。

臨時文件

相比臨時表，臨時文件對大家可能更加陌生，臨時文件更多的被使用在緩存數(shù)據(jù)，排序數(shù)據(jù)的場景中。一般情況下，被緩存或者排序的數(shù)據(jù)，首先放在內(nèi)存中，如果內(nèi)存放不下，才會使用磁盤臨時文件的方式。臨時文件的使用方式與一般的表也不太一樣，一般的表創(chuàng)建完后，就開始讀寫數(shù)據(jù)，使用完后，才把文件刪除，但是臨時文件的使用方式不一樣，在創(chuàng)建完后(使用mkstemp系統(tǒng)函數(shù))，馬上調(diào)用unlink刪除文件，但是不close文件，后續(xù)使用原來的句柄操作文件。這樣的好處是，當(dāng)進(jìn)程異常crash，不會有臨時文件因?yàn)闆]被刪除而殘留，但是壞處也是明顯的，我們在文件系統(tǒng)上使用ls命令就看不到這個文件，需要使用lsof +L1來查看這種deleted屬性的文件。

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

DDL中的臨時文件

在做online DDL的過程中，很多操作需要對原表進(jìn)行重建，對表重建前，需要對各種二級索引排序，而大量數(shù)據(jù)的排序，不太可能在內(nèi)存中完成，需要依賴外部排序算法，MySQL使用了歸并排序。這個過程中就需要創(chuàng)建臨時文件。一般需要的空間大小與原表差不多。但是在使用完之后，會馬上清理，所以在做DDL的時候，需要保留出足夠的空間。用戶可以通過指定innodb_tmpdir來指定這種排序文件的路徑。這個參數(shù)可以動態(tài)修改，一般把他設(shè)置在有足夠磁盤空間的路徑上。臨時文件的名字一般是類似ibXXXXXX，其中ib是固定前綴，XXXXXX是大小寫字母以及數(shù)字的隨機(jī)組合。

在做online DDL中，我們是允許用戶對原表做DML操作的，即增刪改查。我們不能直接插入原表中，因此需要一個地方記錄對原表的修改操作，在DDL結(jié)束后，再應(yīng)用在新表上。這個記錄的地方就是online log，當(dāng)然如果改動少的話，直接存在內(nèi)存里(參數(shù)innodb_sort_buffer_size可控制，同時這個參數(shù)也控制online log每個讀寫塊的大小)面即可。這個onlinelog也是用臨時文件存，創(chuàng)建在innodb_tmpdir，最大大小為參數(shù)innodb_online_alter_log_max_size控制，如果超過這個大小了，DDL就會失敗。臨時文件的名字也類似上述的排序臨時文件的名字。

在online DDL的最后階段，需要把排序完的文件和中途產(chǎn)生的DML全都應(yīng)用到一個中間文件上，中間文件文件名類似#sql-ib53-522550444.ibd，其中#sql-ib是固定的前綴，53是InnoDB層的table id，522550444是隨機(jī)生成的數(shù)字。同時，在server層也會生成一個frm文件(8.0中沒有)，文件名類似#sql-4d2b_2a.frm，其中#sql是固定前綴，4d2b是進(jìn)程號的十六進(jìn)制表示，2a是線程號的十六進(jìn)制表示(show processlist中的id)。因此我們也可以通過這個命名規(guī)則來找到哪個線程在做DDL。這里需要注意一點(diǎn)，這里說的中間文件，其實(shí)算是一個臨時表，并不是上文說中臨時文件，這些中間文件可以通過ls來查看。當(dāng)在DDL中的最后一步，會把這兩個臨時文件命名回原來的表名。正因?yàn)檫@個特性，所以當(dāng)數(shù)據(jù)庫中途crash的時候，可能會在磁盤上留下殘余無用的文件。遇到這種情況，可以先把frm文件重命名成與ibd文件一樣的名字，然后使用DROP TABLE#mysql50##sql-ib53-522550444`來清理殘余的文件。注意，如果不用drop命令，直接刪除ibd文件，可能會導(dǎo)致數(shù)據(jù)字典里面依然有殘余的信息，做法不太優(yōu)雅。當(dāng)然，在8.0中，由于使用了原子的數(shù)據(jù)字典，就不會出現(xiàn)這種殘余文件了。

BinLog中的緩存操作

BinLog只有在事務(wù)提交的時候才會寫入到文件中，在沒提交前，會先放在內(nèi)存中(由參數(shù)binlog_cache_size控制)，如果內(nèi)存放慢了，就會創(chuàng)建臨時文件，使用方法也是先通過mkstemp創(chuàng)建，然后直接unlink，留一個句柄讀寫。臨時文件名類似MLXXXXXX，其中ML是固定前綴，XXXXXX是大小寫字母以及數(shù)字的隨機(jī)組合。單個事務(wù)的BinLog太大，可能會導(dǎo)致整個BinLog的大小也過大，從而影響同步，因此我們需要盡可能控制事務(wù)大小。

優(yōu)化創(chuàng)建的臨時文件

有些操作，除了在引擎層需要依賴隱式臨時表來輔助復(fù)雜SQL的計算，在Server層，也會創(chuàng)建臨時文件來輔助，比如order by操作，會調(diào)用filesort函數(shù)。這個函數(shù)也會先使用內(nèi)存(sort_buffer_size)排序，如果不夠，就會創(chuàng)建一個臨時文件，輔助排序。文件名類似MYXXXXXX，其中MY是固定前綴，XXXXXX是大小寫字母以及數(shù)字的隨機(jī)組合。

Load data中用的臨時文件

在BinLog復(fù)制中，如果在主庫上使用了Load Data命令，即從文件中導(dǎo)數(shù)據(jù)，數(shù)據(jù)庫會把整個文件寫入到RelayLog中，然后傳到備庫，備庫解析RelayLog，從中抽取出對應(yīng)的Load文件，然后在備庫上應(yīng)用。備庫上這個文件存儲的位置由參數(shù)slave_load_tmpdir控制。文檔中建議這個目錄不要配置在物理機(jī)的內(nèi)存目錄或者重啟后會刪除的目錄。因?yàn)閺?fù)制依賴這個文件，如果意外被刪除，會導(dǎo)致復(fù)制中斷。

其他

除了上文所述的幾個地方外，還有其他幾個地方也會用到臨時文件：

在InnoDB層，啟動的時候會創(chuàng)建多個臨時文件用來存儲：最后一次外鍵或者唯一鍵錯誤; 最后一次死鎖的信息; 最后的innodb狀態(tài)信息。用臨時文件而不用內(nèi)存的原因猜測是，內(nèi)存使用率不會因?yàn)閷戇@些指標(biāo)而波動。
在Server層，分區(qū)表使用show create table時，會用到臨時文件。另外在MYISAM表內(nèi)部排序的時候也會用到臨時文件。

欧美一区二区三区老妇人-欧美做爰猛烈大尺度电-99久久夜色精品国产亚洲a-亚洲福利视频一区二区