MySQL臨時表可以重名的原因是什麼

今天我們就從這個問題說起：臨時表有哪些特徵，適合哪些場景？

這裡，我需要先幫你釐清一個容易誤解的問題：有的人可能會認為，臨時表就是記憶體表。但是，這兩個概念可是完全不同的。

• 記憶體表，指的是使用Memory引擎的表，建表語法是create table …engine=memory 。 **這種表的資料都保存在記憶體裡，系統重新啟動的時候會被清空，但是表結構還在。 **除了這兩個特性看起來比較「奇怪」外，從其他的特徵來看，它就是一個正常的表。

• 臨時表，可以使用各種引擎類型。如果是使用InnoDB引擎或MyISAM引擎的臨時表，寫資料的時候是寫到磁碟上的。當然，臨時表也可以使用Memory引擎。

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

臨時表的特性

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

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

• 建表語法是create temporary table …。

• 其他執行緒無法存取由某個session建立的臨時表，僅限該session可見。所以，圖中session A所建立的臨時表t，對於session B就是不可見的。

• 臨時表可以與普通表同名。

• session A內有同名的臨時表和普通表的時候，showcreate語句，以及增刪改查語句存取的是臨時表。

• showtables指令不顯示臨時表。

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

上篇文章中的join最佳化場景特別適合使用臨時表，原因在於臨時表具備這個特性。為什麼呢？原因主要包括以下兩個面向：

• 不同session的臨時表是可以重新命名的，如果有多個session同時執行join最佳化，不需要擔心表名重複導致建表失敗的問題。

• 不需要擔心資料刪除問題。如果使用普通表，在流程執行過程中客戶端發生了異常斷開，或者資料庫發生異常重啟，還需要專門來清理中間過程中產生的資料表。而臨時表由於會自動回收，所以不需要這個額外的操作。

臨時表的應用程式

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

一般分庫分錶的場景，就是要把一個邏輯上的大表分散到不同的資料庫實例上。比如。對於給定的欄位f，將大表ht拆分成1024個分錶，並將這些分錶分佈到32個資料庫實例上。如下圖所示：

一般情況下，這種分庫分錶系統都有一個中間層proxy。不過，也有一些方案會讓客戶端直接連接資料庫，也就是沒有proxy這一層。

在這個架構中，選擇分區鍵是基於「減少跨資料庫和跨表操作」的原則。如果大部分的語句都會包含f的等值條件，那麼就要用f做分區鍵。解析過SQL語句的proxy會決定要將其路由到哪個分錶進行查詢。

例如下面這條語句：

select v from ht where f=N;

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

但是，如果這個表上還有另外一個索引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的进程，它的线程id分别为4和5，分别属于会话A和会话B。因此，可以看出，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中也有DROP TEMPORARY 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中才会记录临时表的操作。

在这种情况下，执行创建临时表语句的操作会被传递到备用数据库进行处理，从而触发备用数据库的同步线程创建相应的临时表。主库在线程退出的时候，会自动删除临时表，但是备库同步线程是持续在运行的。因此，我们需要在主数据库中再运行一个DROP TEMPORARY 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臨時表可以重名的原因是什麼的詳細內容。更多資訊請關注PHP中文網其他相關文章！