內(nèi)存表與臨時表的區(qū)別

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

臨時表的特性

• 建表語法是 create temporary table …。

• 一個臨時表只能被創(chuàng)建它的 session 訪問，對其他線程不可見。

  
  

所以，圖中 session A 創(chuàng)建的臨時表 t，對于 session B 就是不可見的。

• 臨時表可以與普通表同名。session A 內(nèi)有同名的臨時表和普通表的時候，show create 語句，以及增刪改查語句訪問的是臨時表。
• show tables 命令不顯示臨時表。

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

原因主要包括以下兩個方面：

• 不同 session 的臨時表是可以重名的，如果有多個 session 同時執(zhí)行 join 優(yōu)化，不需要擔(dān)心表名重復(fù)導(dǎo)致建表失敗的問題。
• 不需要擔(dān)心數(shù)據(jù)刪除問題。如果使用普通表，在流程執(zhí)行過程中客戶端發(fā)生了異常斷開，或者數(shù)據(jù)庫發(fā)生異常重啟，還需要專門來清理中間過程中生成的數(shù)據(jù)表。而臨時表由于會自動回收，所以不需要這個額外的操作。

臨時表為什么可以重名

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

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

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

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

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

備庫執(zhí)行怎么處理主庫上不同的線程創(chuàng)建同名的臨時表

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

備庫線程在執(zhí)行的時候，要把這兩個 t1 表當(dāng)做兩個不同的臨時表來處理。

MySQL 在記錄 binlog 的時候，會把主庫執(zhí)行這個語句的線程 id 寫到 binlog 中。這樣，在備庫的應(yīng)用線程就能夠知道執(zhí)行每個語句的主庫線程 id，并利用這個線程 id 來構(gòu)造臨時表的 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 不同，所以這兩個表在備庫的應(yīng)用線程里面是不會沖突的。