MySQL45講讀書筆記 36講為什麼臨時表可以重名

一 序

   本文屬于極客時間MySQL45講讀書筆記系列。本文老師主要講臨時表。

今天我們就從這個問題說起：臨時表有哪些特征，為什麼它适合這個場景？

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

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

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

臨時表的特性

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

圖1 臨時表特性示例

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

1. 建表文法是create temporary table …。
2. 一個臨時表隻能被建立它的session通路，對其他線程不可見。是以，圖中session A建立的臨時表t，對于session B就是不可見的。
3. 臨時表可以與普通表同名。
4. session A内有同名的臨時表和普通表的時候，show create語句，以及增删改查語句通路的是臨時表。
5. show tables指令不顯示臨時表。

由于臨時表隻能被建立它的session通路，是以在這個session結束的時候，會自動删除臨時表。也正是由于這個特性，臨時表就特别适合我們文章開頭的join優化這種場景。為什麼呢？

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

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

臨時表的應用

由于不用擔心線程之間的重名沖突，臨時表經常會被用在複雜查詢的優化過程中。

老師講的分表後分分表次元的跨表查詢的一種方案。

為什麼臨時表可以重名？

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

接下來，我們就看一下這個問題。

我們在執行

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

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

這個frm檔案放在臨時檔案目錄下，檔案名的字尾是.frm，字首是“#sql{程序id}_{線程id}_序列号”。你可以使用select @@tmpdir指令，來顯示執行個體的臨時檔案目錄。

而關于表中資料的存放方式，在不同的MySQL版本中有着不同的處理方式：

• 在5.6以及之前的版本裡，MySQL會在臨時檔案目錄下建立一個相同字首、以.ibd為字尾的檔案，用來存放資料檔案；
• 而從 5.7版本開始，MySQL引入了一個臨時檔案表空間，專門用來存放臨時檔案的資料。是以，我們就不需要再建立ibd檔案了。

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

為了便于後面讨論，我先來舉一個例子。

圖4 臨時表的表名

這個程序的程序号是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和sessionB建立的兩個臨時表t1，它們的table_def_key不同，磁盤檔案名也不同，是以可以并存。

在實作上，每個線程都維護了自己的臨時表連結清單。這樣每次session内操作表的時候，先周遊連結清單，檢查是否有這個名字的臨時表，如果有就優先操作臨時表，如果沒有再操作普通表；在session結束的時候，對連結清單裡的每個臨時表，執行 “DROP TEMPORARY TABLE +表名”操作。

除了上面通過顯式的執行指令

create temporary table

建立的表，

優化器隐式建立臨時表

   這種臨時表，是資料庫為了輔助某些複雜SQL的執行而建立的輔助表，是否需要臨時表，一般都是由優化器決定。與使用者顯式建立的臨時表直接建立磁盤檔案不同，如果需要優化器覺得SQL需要臨時表輔助，會先使用記憶體臨時表，如果超過配置的記憶體(min(tmp_table_size, max_heap_table_siz))，就會轉化成磁盤臨時表，這種磁盤臨時表就類似使用者顯式建立的，引擎類型通過參數

internal_tmp_disk_storage_engine

控制。一般稍微複雜一點的查詢，包括且不限于order by, group by, distinct等，都會用到這種隐式建立的臨時表。使用者可以通過explain指令，在Extra列中，看是否有Using temporary這樣的字樣，如果有，就肯定要用臨時表。

 In some cases, the server creates internal temporary tables while processing statements. Users have no direct control over when this occurs.

The server creates temporary tables under conditions such as these:

• Evaluation of 

  UNION

   statements, with some exceptions described later.
• Evaluation of some views, such those that use the 

  TEMPTABLE

   algorithm, 

  UNION

  , or aggregation.
• Evaluation of derived tables (see Section 13.2.10.8, “Derived Tables”).
• Tables created for subquery or semijoin materialization (see Section 8.2.2, “Optimizing Subqueries, Derived Tables, and View References”).
• Evaluation of statements that contain an 

  ORDER BY

   clause and a different 

  GROUP BY

   clause, or for which the 

  ORDER BY

   or 

  GROUP BY

   contains columns from tables other than the first table in the join queue.
• Evaluation of 

  DISTINCT

   combined with 

  ORDER BY

   may require a temporary table.
• For queries that use the 

  SQL_SMALL_RESULT

   modifier, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.
• To evaluate 

  INSERT ... SELECT

   statements that select from and insert into the same table, MySQL creates an internal temporary table to hold the rows from the 

  SELECT

  , then inserts those rows into the target table. See Section 13.2.5.1, “INSERT ... SELECT Statement”.
• Evaluation of multiple-table 

  UPDATE

   statements.
• Evaluation of 

  GROUP_CONCAT()

   or 

  COUNT(DISTINCT)

   expressions.

小結

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

在實際應用中，臨時表一般用于處理比較複雜的計算邏輯。由于臨時表是每個線程自己可見的，是以不需要考慮多個線程執行同一個處理邏輯時，臨時表的重名問題。線上程退出的時候，臨時表也能自動删除，省去了收尾和異常處理的工作。

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

另外，還有使用比如order by操作，會調用filesort函數。這個函數也會先使用記憶體(sort_buffer_size)排序，如果不夠，就會建立一個臨時檔案。還有從MySQL的版本來看，也是臨時表的資料和undo逐漸分開，也是為了性能考慮。

看了這麼多，還是覺得臨時表挺陌生的，應該有些排查性能用得到。

參考：

https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html

http://mysql.taobao.org/monthly/2019/04/01/