MySQL臨時(shí)表可以重名的原因是什么

這篇文章主要為大家展示了“MySQL臨時(shí)表可以重名的原因是什么”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“MySQL臨時(shí)表可以重名的原因是什么”這篇文章吧。

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

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

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

• 臨時(shí)表，可以使用各種引擎類(lèi)型。如果是使用InnoDB引擎或者MyISAM引擎的臨時(shí)表，寫(xiě)數(shù)據(jù)的時(shí)候是寫(xiě)到磁盤(pán)上的。當(dāng)然，臨時(shí)表也可以使用Memory引擎。

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

臨時(shí)表的特性

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

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

• 建表語(yǔ)法是create temporary table …。

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

• 臨時(shí)表可以與普通表同名。

• session A內(nèi)有同名的臨時(shí)表和普通表的時(shí)候，showcreate語(yǔ)句，以及增刪改查語(yǔ)句訪問(wèn)的是臨時(shí)表。

• showtables命令不顯示臨時(shí)表。

由于臨時(shí)表只能被創(chuàng)建它的session訪問(wèn)，所以在這個(gè)session結(jié)束的時(shí)候，會(huì)自動(dòng)刪除臨時(shí)表。

也正是由于這個(gè)特性，臨時(shí)表就特別適合上篇文章中join優(yōu)化這種場(chǎng)景。為什么呢？ 原因主要包括以下兩個(gè)方面：

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

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

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

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

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

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

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

比如下面這條語(yǔ)句：

select v from ht where f=N;

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

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

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

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

第一種思路是，在proxy層的進(jìn)程代碼中實(shí)現(xiàn)排序。 這種方式的優(yōu)勢(shì)是處理速度快，拿到分庫(kù)的數(shù)據(jù)以后，直接在內(nèi)存中參與計(jì)算。不過(guò)，這個(gè)方案的缺點(diǎn)也比較明顯：

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

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

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

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

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

• 在各個(gè)分庫(kù)上執(zhí)行select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

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

• 執(zhí)行select v from temp_ht order by t_modified desc limit 100;

得到結(jié)果。 這個(gè)過(guò)程對(duì)應(yīng)的流程圖如下所示：

在實(shí)踐中，我們往往會(huì)發(fā)現(xiàn)每個(gè)分庫(kù)的計(jì)算量都不飽和，所以會(huì)直接把臨時(shí)表temp_ht放到32個(gè)分庫(kù)中的某一個(gè)上。

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

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

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

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

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

這個(gè)frm文件放在臨時(shí)文件目錄下，文件名的后綴是.frm，前綴是“#sql{進(jìn)程id}_ {線程id}_ 序列號(hào)”。

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

先來(lái)舉一個(gè)例子。

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

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

• 一個(gè)普通表的table_def_key的值是由“庫(kù)名+表名”得到的，所以如果你要在同一個(gè)庫(kù)下創(chuàng)建兩個(gè)同名的普通表，創(chuàng)建第二個(gè)表的過(guò)程中就會(huì)發(fā)現(xiàn)table_def_key已經(jīng)存在了。

• 而對(duì)于臨時(shí)表，table_def_key在“庫(kù)名+表名”基礎(chǔ)上，又加入了“server_id+thread_id”。

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

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

這時(shí)候你會(huì)發(fā)現(xiàn)，binlog中也記錄了DROPTEMPORARY TABLE這條命令。你一定會(huì)覺(jué)得奇怪，臨時(shí)表只在線程內(nèi)自己可以訪問(wèn)，為什么需要寫(xiě)到binlog里面？這，就需要說(shuō)到主備復(fù)制了。

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

既然寫(xiě)binlog，就意味著備庫(kù)需要。 你可以設(shè)想一下，在主庫(kù)上執(zhí)行下面這個(gè)語(yǔ)句序列：

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í)表的操作都不記錄，那么在備庫(kù)就只有create table t_normal表和insert intot_normal select * fromtemp_t這兩個(gè)語(yǔ)句的binlog日志，備庫(kù)在執(zhí)行到insert into t_normal的時(shí)候，就會(huì)報(bào)錯(cuò)“表temp_t不存在”。

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

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

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

主庫(kù)上不同的線程創(chuàng)建同名的臨時(shí)表是沒(méi)關(guān)系的，但是傳到備庫(kù)執(zhí)行是怎么處理的呢？

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

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

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

顯然是不會(huì)的，否則臨時(shí)表就是一個(gè)bug了。也就是說(shuō)，備庫(kù)線程在執(zhí)行的時(shí)候，要把這兩個(gè)t1表當(dāng)做兩個(gè)不同的臨時(shí)表來(lái)處理。這，又是怎么實(shí)現(xiàn)的呢？ MySQL在記錄binlog的時(shí)候，會(huì)把主庫(kù)執(zhí)行這個(gè)語(yǔ)句的線程id寫(xiě)到binlog中。這樣，在備庫(kù)的應(yīng)用線程就能夠知道執(zhí)行每個(gè)語(yǔ)句的主庫(kù)線程id，并利用這個(gè)線程id來(lái)構(gòu)造臨時(shí)表的table_def_key：

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

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

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

以上是“MySQL臨時(shí)表可以重名的原因是什么”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！