在上一篇文章中，我們?cè)趦?yōu)化join查詢的時(shí)候使用到了臨時(shí)表,。當(dāng)時(shí),，我們是這么用的：

create temporary table temp_t like t1;
alter table temp_t add index(b);
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

你可能會(huì)有疑問，為什么要用臨時(shí)表呢,？直接用普通表是不是也可以呢,？

今天我們就從這個(gè)問題說起：臨時(shí)表有哪些特征，為什么它適合這個(gè)場(chǎng)景,？

這里,，我需要先幫你厘清一個(gè)容易誤解的問題：有的人可能會(huì)認(rèn)為，臨時(shí)表就是內(nèi)存表,。但是,，這兩個(gè)概念可是完全不同的。

• 內(nèi)存表,，指的是使用Memory引擎的表,，建表語法是create table … engine=memory。這種表的數(shù)據(jù)都保存在內(nèi)存里,，系統(tǒng)重啟的時(shí)候會(huì)被清空,，但是表結(jié)構(gòu)還在。除了這兩個(gè)特性看上去比較“奇怪”外,，從其他的特征上看,，它就是一個(gè)正常的表。

• 而臨時(shí)表,，可以使用各種引擎類型 ,。如果是使用InnoDB引擎或者M(jìn)yISAM引擎的臨時(shí)表，寫數(shù)據(jù)的時(shí)候是寫到磁盤上的,。當(dāng)然,，臨時(shí)表也可以使用Memory引擎,。

弄清楚了內(nèi)存表和臨時(shí)表的區(qū)別以后，我們?cè)賮砜纯磁R時(shí)表有哪些特征,。

臨時(shí)表的特性

為了便于理解,，我們來看下下面這個(gè)操作序列：

可以看到，臨時(shí)表在使用上有以下幾個(gè)特點(diǎn)：

1. 建表語法是create temporary table …,。

2. 一個(gè)臨時(shí)表只能被創(chuàng)建它的session訪問,，對(duì)其他線程不可見。所以,，圖中session A創(chuàng)建的臨時(shí)表t,，對(duì)于session B就是不可見的。

3. 臨時(shí)表可以與普通表同名,。

4. session A內(nèi)有同名的臨時(shí)表和普通表的時(shí)候,，show create語句，以及增刪改查語句訪問的是臨時(shí)表,。

5. show tables命令不顯示臨時(shí)表,。

由于臨時(shí)表只能被創(chuàng)建它的session訪問，所以在這個(gè)session結(jié)束的時(shí)候,，會(huì)自動(dòng)刪除臨時(shí)表,。也正是由于這個(gè)特性，臨時(shí)表就特別適合我們文章開頭的join優(yōu)化這種場(chǎng)景,。為什么呢,？

原因主要包括以下兩個(gè)方面：

1. 不同session的臨時(shí)表是可以重名的，如果有多個(gè)session同時(shí)執(zhí)行join優(yōu)化,，不需要擔(dān)心表名重復(fù)導(dǎo)致建表失敗的問題,。

2. 不需要擔(dān)心數(shù)據(jù)刪除問題。如果使用普通表,，在流程執(zhí)行過程中客戶端發(fā)生了異常斷開,，或者數(shù)據(jù)庫發(fā)生異常重啟，還需要專門來清理中間過程中生成的數(shù)據(jù)表,。而臨時(shí)表由于會(huì)自動(dòng)回收，所以不需要這個(gè)額外的操作,。

臨時(shí)表的應(yīng)用

由于不用擔(dān)心線程之間的重名沖突,，臨時(shí)表經(jīng)常會(huì)被用在復(fù)雜查詢的優(yōu)化過程中。其中,，分庫分表系統(tǒng)的跨庫查詢就是一個(gè)典型的使用場(chǎng)景,。

一般分庫分表的場(chǎng)景，就是要把一個(gè)邏輯上的大表分散到不同的數(shù)據(jù)庫實(shí)例上,。比如,。將一個(gè)大表ht,，按照字段f，拆分成1024個(gè)分表,，然后分布到32個(gè)數(shù)據(jù)庫實(shí)例上,。如下圖所示：

一般情況下，這種分庫分表系統(tǒng)都有一個(gè)中間層proxy,。不過,，也有一些方案會(huì)讓客戶端直接連接數(shù)據(jù)庫，也就是沒有proxy這一層,。

在這個(gè)架構(gòu)中,，分區(qū)key的選擇是以“減少跨庫和跨表查詢”為依據(jù)的。如果大部分的語句都會(huì)包含f的等值條件,，那么就要用f做分區(qū)鍵,。這樣，在proxy這一層解析完SQL語句以后,，就能確定將這條語句路由到哪個(gè)分表做查詢,。

比如下面這條語句：

select v from ht where f=N;

這時(shí)，我們就可以通過分表規(guī)則（比如,，N%1024)來確認(rèn)需要的數(shù)據(jù)被放在了哪個(gè)分表上,。這種語句只需要訪問一個(gè)分表，是分庫分表方案最歡迎的語句形式了,。

但是,，如果這個(gè)表上還有另外一個(gè)索引k，并且查詢語句是這樣的：

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

這時(shí)候,，由于查詢條件里面沒有用到分區(qū)字段f,，只能到所有的分區(qū)中去查找滿足條件的所有行，然后統(tǒng)一做order by 的操作,。這種情況下,，有兩種比較常用的思路。

第一種思路是,，在proxy層的進(jìn)程代碼中實(shí)現(xiàn)排序,。

這種方式的優(yōu)勢(shì)是處理速度快，拿到分庫的數(shù)據(jù)以后,，直接在內(nèi)存中參與計(jì)算,。不過，這個(gè)方案的缺點(diǎn)也比較明顯：

1. 需要的開發(fā)工作量比較大,。我們舉例的這條語句還算是比較簡(jiǎn)單的,，如果涉及到復(fù)雜的操作，比如group by,，甚至join這樣的操作,，對(duì)中間層的開發(fā)能力要求比較高,；

2. 對(duì)proxy端的壓力比較大，尤其是很容易出現(xiàn)內(nèi)存不夠用和CPU瓶頸的問題,。

另一種思路就是,，把各個(gè)分庫拿到的數(shù)據(jù)，匯總到一個(gè)MySQL實(shí)例的一個(gè)表中,，然后在這個(gè)匯總實(shí)例上做邏輯操作,。

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

• 在匯總庫上創(chuàng)建一個(gè)臨時(shí)表temp_ht,，表里包含三個(gè)字段v,、k、t_modified,；
• 在各個(gè)分庫上執(zhí)行

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

• 把分庫執(zhí)行的結(jié)果插入到temp_ht表中,；
• 執(zhí)行

select v from temp_ht order by t_modified desc limit 100; 

得到結(jié)果。

這個(gè)過程對(duì)應(yīng)的流程圖如下所示：

在實(shí)踐中,，我們往往會(huì)發(fā)現(xiàn)每個(gè)分庫的計(jì)算量都不飽和,，所以會(huì)直接把臨時(shí)表temp_ht放到32個(gè)分庫中的某一個(gè)上。這時(shí)的查詢邏輯與圖3類似,，你可以自己再思考一下具體的流程,。

為什么臨時(shí)表可以重名？

你可能會(huì)問,，不同線程可以創(chuàng)建同名的臨時(shí)表,，這是怎么做到的呢？

接下來,，我們就看一下這個(gè)問題,。

我們?cè)趫?zhí)行

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

這個(gè)語句的時(shí)候，MySQL要給這個(gè)InnoDB表創(chuàng)建一個(gè)frm文件保存表結(jié)構(gòu)定義,，還要有地方保存表數(shù)據(jù),。

這個(gè)frm文件放在臨時(shí)文件目錄下，文件名的后綴是.frm,，前綴是“#sql{進(jìn)程id}_{線程id}_序列號(hào)”,。你可以使用select @@tmpdir命令，來顯示實(shí)例的臨時(shí)文件目錄,。

而關(guān)于表中數(shù)據(jù)的存放方式,，在不同的MySQL版本中有著不同的處理方式：

• 在5.6以及之前的版本里，MySQL會(huì)在臨時(shí)文件目錄下創(chuàng)建一個(gè)相同前綴,、以.ibd為后綴的文件，用來存放數(shù)據(jù)文件,；
• 而從 5.7版本開始,，MySQL引入了一個(gè)臨時(shí)文件表空間,，專門用來存放臨時(shí)文件的數(shù)據(jù)。因此,，我們就不需要再創(chuàng)建ibd文件了,。

從文件名的前綴規(guī)則，我們可以看到,，其實(shí)創(chuàng)建一個(gè)叫作t1的InnoDB臨時(shí)表,，MySQL在存儲(chǔ)上認(rèn)為我們創(chuàng)建的表名跟普通表t1是不同的，因此同一個(gè)庫下面已經(jīng)有普通表t1的情況下,，還是可以再創(chuàng)建一個(gè)臨時(shí)表t1的。

為了便于后面討論,，我先來舉一個(gè)例子,。

這個(gè)進(jìn)程的進(jìn)程號(hào)是1234，session A的線程id是4,，session B的線程id是5,。所以你看到了，session A和session B創(chuàng)建的臨時(shí)表,，在磁盤上的文件不會(huì)重名,。

MySQL維護(hù)數(shù)據(jù)表，除了物理上要有文件外,，內(nèi)存里面也有一套機(jī)制區(qū)別不同的表,，每個(gè)表都對(duì)應(yīng)一個(gè)table_def_key。

• 一個(gè)普通表的table_def_key的值是由“庫名+表名”得到的,，所以如果你要在同一個(gè)庫下創(chuàng)建兩個(gè)同名的普通表,，創(chuàng)建第二個(gè)表的過程中就會(huì)發(fā)現(xiàn)table_def_key已經(jīng)存在了。
• 而對(duì)于臨時(shí)表,，table_def_key在“庫名+表名”基礎(chǔ)上,，又加入了“server_id+thread_id”,。

也就是說，session A和sessionB創(chuàng)建的兩個(gè)臨時(shí)表t1,，它們的table_def_key不同，磁盤文件名也不同,，因此可以并存。

在實(shí)現(xiàn)上，每個(gè)線程都維護(hù)了自己的臨時(shí)表鏈表,。這樣每次session內(nèi)操作表的時(shí)候，先遍歷鏈表,，檢查是否有這個(gè)名字的臨時(shí)表，如果有就優(yōu)先操作臨時(shí)表,，如果沒有再操作普通表,；在session結(jié)束的時(shí)候，對(duì)鏈表里的每個(gè)臨時(shí)表,，執(zhí)行 “DROP TEMPORARY TABLE +表名”操作,。

這時(shí)候你會(huì)發(fā)現(xiàn)，binlog中也記錄了DROP TEMPORARY TABLE這條命令,。你一定會(huì)覺得奇怪,，臨時(shí)表只在線程內(nèi)自己可以訪問，為什么需要寫到binlog里面,？

這,，就需要說到主備復(fù)制了。

臨時(shí)表和主備復(fù)制

既然寫binlog,，就意味著備庫需要。

你可以設(shè)想一下,，在主庫上執(zhí)行下面這個(gè)語句序列：

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*/

如果關(guān)于臨時(shí)表的操作都不記錄,，那么在備庫就只有create table t_normal表和insert into t_normal select * from temp_t這兩個(gè)語句的binlog日志，備庫在執(zhí)行到insert into t_normal的時(shí)候,，就會(huì)報(bào)錯(cuò)“表temp_t不存在”,。

你可能會(huì)說，如果把binlog設(shè)置為row格式就好了吧,？因?yàn)閎inlog是row格式時(shí),，在記錄insert into t_normal的binlog時(shí)，記錄的是這個(gè)操作的數(shù)據(jù),，即：write_row event里面記錄的邏輯是“插入一行數(shù)據(jù)（1,1)”,。

確實(shí)是這樣。如果當(dāng)前的binlog_format=row,，那么跟臨時(shí)表有關(guān)的語句,，就不會(huì)記錄到binlog里。也就是說,，只在binlog_format=statment/mixed 的時(shí)候,，binlog中才會(huì)記錄臨時(shí)表的操作,。

這種情況下，創(chuàng)建臨時(shí)表的語句會(huì)傳到備庫執(zhí)行,，因此備庫的同步線程就會(huì)創(chuàng)建這個(gè)臨時(shí)表,。主庫在線程退出的時(shí)候，會(huì)自動(dòng)刪除臨時(shí)表,，但是備庫同步線程是持續(xù)在運(yùn)行的。所以,，這時(shí)候我們就需要在主庫上再寫一個(gè)DROP TEMPORARY TABLE傳給備庫執(zhí)行,。

之前有人問過我一個(gè)有趣的問題：MySQL在記錄binlog的時(shí)候，不論是create table還是alter table語句,，都是原樣記錄,，甚至于連空格都不變。但是如果執(zhí)行drop table t_normal,，系統(tǒng)記錄binlog就會(huì)寫成：

DROP TABLE `t_normal` /* generated by server */

也就是改成了標(biāo)準(zhǔn)的格式,。為什么要這么做呢 ？

現(xiàn)在你知道原因了,，那就是：drop table命令是可以一次刪除多個(gè)表的,。比如，在上面的例子中,，設(shè)置binlog_format=row,，如果主庫上執(zhí)行 "drop table t_normal, temp_t"這個(gè)命令，那么binlog中就只能記錄：

DROP TABLE `t_normal` /* generated by server */

因?yàn)閭鋷焐喜]有表temp_t,，將這個(gè)命令重寫后再傳到備庫執(zhí)行,，才不會(huì)導(dǎo)致備庫同步線程停止。

所以,，drop table命令記錄binlog的時(shí)候,，就必須對(duì)語句做改寫?！?* generated by server */”說明了這是一個(gè)被服務(wù)端改寫過的命令,。

說到主備復(fù)制，還有另外一個(gè)問題需要解決：主庫上不同的線程創(chuàng)建同名的臨時(shí)表是沒關(guān)系的,，但是傳到備庫執(zhí)行是怎么處理的呢,？

現(xiàn)在，我給你舉個(gè)例子,，下面的序列中實(shí)例S是M的備庫,。

主庫M上的兩個(gè)session創(chuàng)建了同名的臨時(shí)表t1，這兩個(gè)create temporary table t1 語句都會(huì)被傳到備庫S上,。

但是,，備庫的應(yīng)用日志線程是共用的,，也就是說要在應(yīng)用線程里面先后執(zhí)行這個(gè)create 語句兩次。（即使開了多線程復(fù)制,，也可能被分配到從庫的同一個(gè)worker中執(zhí)行）,。那么，這會(huì)不會(huì)導(dǎo)致同步線程報(bào)錯(cuò) ,？

顯然是不會(huì)的,，否則臨時(shí)表就是一個(gè)bug了。也就是說,，備庫線程在執(zhí)行的時(shí)候,，要把這兩個(gè)t1表當(dāng)做兩個(gè)不同的臨時(shí)表來處理。這,，又是怎么實(shí)現(xiàn)的呢,？

MySQL在記錄binlog的時(shí)候，會(huì)把主庫執(zhí)行這個(gè)語句的線程id寫到binlog中,。這樣,，在備庫的應(yīng)用線程就能夠知道執(zhí)行每個(gè)語句的主庫線程id，并利用這個(gè)線程id來構(gòu)造臨時(shí)表的table_def_key：

1. session A的臨時(shí)表t1,，在備庫的table_def_key就是：庫名+t1+“M的serverid”+“session A的thread_id”;

2. session B的臨時(shí)表t1,，在備庫的table_def_key就是 ：庫名+t1+“M的serverid”+“session B的thread_id”。

由于table_def_key不同,，所以這兩個(gè)表在備庫的應(yīng)用線程里面是不會(huì)沖突的,。

小結(jié)

今天這篇文章，我和你介紹了臨時(shí)表的用法和特性,。

在實(shí)際應(yīng)用中,，臨時(shí)表一般用于處理比較復(fù)雜的計(jì)算邏輯。由于臨時(shí)表是每個(gè)線程自己可見的,，所以不需要考慮多個(gè)線程執(zhí)行同一個(gè)處理邏輯時(shí),，臨時(shí)表的重名問題。在線程退出的時(shí)候,，臨時(shí)表也能自動(dòng)刪除,，省去了收尾和異常處理的工作。

在binlog_format='row’的時(shí)候,，臨時(shí)表的操作不記錄到binlog中,，也省去了不少麻煩，這也可以成為你選擇binlog_format時(shí)的一個(gè)考慮因素,。

需要注意的是,，我們上面說到的這種臨時(shí)表，是用戶自己創(chuàng)建的 ,，也可以稱為用戶臨時(shí)表,。與它相對(duì)應(yīng)的,，就是內(nèi)部臨時(shí)表，在第17篇文章中我已經(jīng)和你介紹過,。

最后,，我給你留下一個(gè)思考題吧。

下面的語句序列是創(chuàng)建一個(gè)臨時(shí)表,，并將其改名：

可以看到,，我們可以使用alter table語法修改臨時(shí)表的表名，而不能使用rename語法,。你知道這是什么原因嗎,？