MySQL為什麼臨時表可以重名

2022-03-22 13:01:48

記憶體表，指的是使用Memory引擎的表，建表語法是create table …engine=memory。**這種表的資料都儲存在記憶體裡，系統重啟的時候會被清空，但是表結構還在。**除了這兩個特性看上去比較“奇怪”外，從其他的特徵上看，它就是一個正常的表。
臨時表，可以使用各種引擎型別。如果是使用InnoDB引擎或者MyISAM引擎的臨時表，寫資料的時候是寫到磁碟上的。當然，臨時表也可以使用Memory引擎。

弄清楚了記憶體表和臨時表的區別以後，我們再來看看臨時表有哪些特徵。

臨時表的特性

為了便於理解，我們來看下下面這個操作序列：

可以看到，臨時表在使用上有以下幾個特點：

建表語法是create temporary table …。
一個臨時表只能被建立它的session存取，對其他執行緒不可見。所以，圖中session A建立的臨時表t，對於session B就是不可見的。
臨時表可以與普通表同名。
session A內有同名的臨時表和普通表的時候，showcreate語句，以及增刪改查語句存取的是臨時表。
showtables命令不顯示臨時表。

由於臨時表只能被建立它的session存取，所以在這個session結束的時候，會自動刪除臨時表。

也正是由於這個特性，臨時表就特別適合上篇文章中join優化這種場景。為什麼呢？原因主要包括以下兩個方面：

不同session的臨時表是可以重名的，如果有多個session同時執行join優化，不需要擔心表名重複導致建表失敗的問題。
不需要擔心資料刪除問題。如果使用普通表，在流程執行過程中使用者端發生了異常斷開，或者資料庫發生異常重啟，還需要專門來清理中間過程中生成的資料表。而臨時表由於會自動回收，所以不需要這個額外的操作。

臨時表的應用

由於不用擔心執行緒之間的重名衝突，臨時表經常會被用在複雜查詢的優化過程中。其中，分庫分表系統的跨庫查詢就是一個典型的使用場景。

一般分庫分表的場景，就是要把一個邏輯上的大表分散到不同的資料庫範例上。比如。將一個大表ht，按照欄位f，拆分成1024個分表，然後分佈到32個資料庫範例上。如下圖所示：

一般情況下，這種分庫分表系統都有一箇中間層proxy。不過，也有一些方案會讓使用者端直接連線資料庫，也就是沒有proxy這一層。

在這個架構中，分割區key的選擇是以“減少跨庫和跨表查詢”為依據的。如果大部分的語句都會包含f的等值條件，那麼就要用f做分割區鍵。這樣，在proxy這一層解析完SQL語句以後，就能確定將這條語句路由到哪個分表做查詢。

比如下面這條語句：

select v from ht where f=N;

這時，我們就可以通過分表規則（比如，N%1024)來確認需要的資料被放在了哪個分表上。這種語句只需要存取一個分表，是分庫分表方案最歡迎的語句形式了。

但是，如果這個表上還有另外一個索引k，並且查詢語句是這樣的：

select v from ht where k >= M order by t_modified desc limit 100;

這時候，由於查詢條件裡面沒有用到分割區欄位f，只能到所有的分割區中去查詢滿足條件的所有行，然後統一做order by的操作。這種情況下，有兩種比較常用的思路。

第一種思路是，在proxy層的程序程式碼中實現排序。這種方式的優勢是處理速度快，拿到分庫的資料以後，直接在記憶體中參與計算。不過，這個方案的缺點也比較明顯：

需要的開發工作量比較大。我們舉例的這條語句還算是比較簡單的，如果涉及到複雜的操作，比如group by，甚至join這樣的操作，對中間層的開發能力要求比較高；
對proxy端的壓力比較大，尤其是很容易出現記憶體不夠用和CPU瓶頸的問題。

另一種思路就是，把各個分庫拿到的資料，彙總到一個MySQL範例的一個表中，然後在這個彙總範例上做邏輯操作。

比如上面這條語句，執行流程可以類似這樣：

在彙總庫上建立一個臨時表temp_ht，表裡包含三個欄位v、k、t_modified；
在各個分庫上執行select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;
把分庫執行的結果插入到temp_ht表中；
執行select v from temp_ht order by t_modified desc limit 100;

得到結果。這個過程對應的流程圖如下所示：

在實踐中，我們往往會發現每個分庫的計算量都不飽和，所以會直接把臨時表temp_ht放到32個分庫中的某一個上。

為什麼臨時表可以重名？

你可能會問，不同執行緒可以建立同名的臨時表，這是怎麼做到的呢？

我們在執行

create temporary table temp_t(id int primary key)engine=innodb;

這個語句的時候，MySQL要給這個InnoDB表建立一個frm檔案儲存表結構定義，還要有地方儲存表資料。

這個frm檔案放在臨時檔案目錄下，檔名的字尾是.frm，字首是“#sql{程序id}_ {執行緒id}_ 序列號”。

從檔名的字首規則，我們可以看到，其實建立一個叫作t1的InnoDB臨時表，MySQL在儲存上認為我們建立的表名跟普通表t1是不同的，因此同一個庫下面已經有普通表t1的情況下，還是可以再建立一個臨時表t1的。

先來舉一個例子。

這個程序的程序號是1234，session A的執行緒id是4，session B的執行緒id是5。所以你看到了，session A和session B建立的臨時表，在磁碟上的檔案不會重名。

MySQL維護資料表，除了物理上要有檔案外，記憶體裡面也有一套機制區別不同的表，每個表都對應一個table_def_key。

一個普通表的table_def_key的值是由“庫名+表名”得到的，所以如果你要在同一個庫下建立兩個同名的普通表，建立第二個表的過程中就會發現table_def_key已經存在了。
而對於臨時表，table_def_key在“庫名+表名”基礎上，又加入了“server_id+thread_id”。

也就是說，session A和session B建立的兩個臨時表t1，它們的table_def_key不同，磁碟檔名也不同，因此可以並存。

在實現上，每個執行緒都維護了自己的臨時表連結串列。這樣每次session內操作表的時候，先遍歷連結串列，檢查是否有這個名字的臨時表，如果有就優先操作臨時表，如果沒有再操作普通表；在session結束的時候，對連結串列裡的每個臨時表，執行 “DROPTEMPORARY TABLE +表名”操作。

這時候你會發現，binlog中也記錄了DROPTEMPORARY TABLE這條命令。你一定會覺得奇怪，臨時表只線上程內自己可以存取，為什麼需要寫到binlog裡面？這，就需要說到主備複製了。

臨時表和主備複製

既然寫binlog，就意味著備庫需要。你可以設想一下，在主庫上執行下面這個語句序列：

create table t_normal(id int primary key, c int)engine=innodb;/*Q1*/
create temporary table temp_t like t_normal;/*Q2*/
insert into temp_t values(1,1);/*Q3*/
insert into t_normal select * from temp_t;/*Q4*/

如果關於臨時表的操作都不記錄，那麼在備庫就只有create table t_normal表和insert intot_normal select * fromtemp_t這兩個語句的binlog紀錄檔，備庫在執行到insert into t_normal的時候，就會報錯“表temp_t不存在”。

你可能會說，如果把binlog設定為row格式就好了吧？因為binlog是row格式時，在記錄insert intot_normal的binlog時，記錄的是這個操作的資料，即：write_rowevent裡面記錄的邏輯是“插入一行資料（1,1)”。

確實是這樣。如果當前的binlog_format=row，那麼跟臨時表有關的語句，就不會記錄到binlog裡。也就是說，只在binlog_format=statment/mixed的時候，binlog中才會記錄臨時表的操作。

這種情況下，建立臨時表的語句會傳到備庫執行，因此備庫的同步執行緒就會建立這個臨時表。主庫線上程退出的時候，會自動刪除臨時表，但是備庫同步執行緒是持續在執行的。所以，這時候我們就需要在主庫上再寫一個DROPTEMPORARY TABLE傳給備庫執行。

主庫上不同的執行緒建立同名的臨時表是沒關係的，但是傳到備庫執行是怎麼處理的呢？

現在，我給你舉個例子，下面的序列中範例S是M的備庫。

主庫M上的兩個session建立了同名的臨時表t1，這兩個create temporary table t1 語句都會被傳到備庫S上。

但是，備庫的應用紀錄檔執行緒是共用的，也就是說要在應用執行緒裡面先後執行這個create 語句兩次。（即使開了多執行緒複製，也可能被分配到從庫的同一個worker中執行）。那麼，這會不會導致同步執行緒報錯？

顯然是不會的，否則臨時表就是一個bug了。也就是說，備庫執行緒在執行的時候，要把這兩個t1表當做兩個不同的臨時表來處理。這，又是怎麼實現的呢？ MySQL在記錄binlog的時候，會把主庫執行這個語句的執行緒id寫到binlog中。這樣，在備庫的應用執行緒就能夠知道執行每個語句的主庫執行緒id，並利用這個執行緒id來構造臨時表的table_def_key：

session A的臨時表t1，在備庫的table_def_key就是：庫名+t1+“M的serverid”+“session A的thread_id”;
session B的臨時表t1，在備庫的table_def_key就是：庫名+t1+“M的serverid”+“session B的thread_id”。

由於table_def_key不同，所以這兩個表在備庫的應用執行緒裡面是不會衝突的。

到此這篇關於MySQL為什麼臨時表可以重名的文章就介紹到這了,更多相關MySQL臨時表重名內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！