mysql 創建大量磁盤臨時表

發現 mysql 中創建的臨時表基本都是磁盤臨時表，對此進行排查。最后為臨時表機制說明。

通過查看 mysql 狀態變量，看到創建的臨時表基本都是磁盤臨時表。

查看臨時表配置，默認配置 16M，配置并沒有問題。

創建兩個表，做一個查詢測試

create table t1(f1 int auto_increment primary key, f2 char(50), f3 datetime default now());
insert into t1 (f2) values ('趙'),('錢'),('孫'),('李');
create table t2(f1 int auto_increment primary key, f2 int, f3 char(50), f4 datetime default now());
insert into t2 (f2, f3) values ('3', '悟空'),('1', '四'),('4', '白');

刷新狀態，進行查詢，發現創建的是磁盤表

兩張小表的查詢完全滿足創建內存臨時表的條件，看來是配置的問題。查看配置文件中有配置 big_tables。

查看官方文檔對 big_tables 的說明：

    如果設置為1，則所有臨時表都存儲在磁盤上而不是內存中。 這有點慢，但是對于需要大型臨時表的 SELECT 操作，不會報 The table tbl_name is full 的錯誤。 新連接的默認值為 0（使用內存中的臨時表）。 通常，您不需要設置此變量，因為根據需要將內存表自動轉換為基于磁盤的表。

修改該系統變量再測試一下，這次創建的臨時表就是內存臨時表了。

mysql 中內部臨時表（internal temporary table）機制

某些情況下，mysql 在執行語句的時候會創建內部臨時表。用戶不能直接控制何時發生。

服務器在以下情況會創建臨時表：

  ● 評估 UINON 語句；

  ● 評估一些視圖，例如使用 TEMPTABLE 算法，UNION 或聚合的視圖；

  ● 派生表的評估（FROM子句中的子查詢）；

  ● 為子查詢或半連接實現創建表；

  ● 評估包含 ORDER BY 子句和不同 GROUP BY 子句的語句，或 ORDER BY 或 GROUP BY 包含來自除加入隊列中第一個表之外的表的列的語句；

  ● DISTINCT與ORDER BY結合使用可能需要一個臨時表；

  ● 對于使用 SQL_SMALL_RESULT 修飾符的查詢，MySQL 使用內存中的臨時表，除非查詢還包含需要磁盤存儲的元素；

  ● 評估多表 UPDATE 語句；

  ● 評估 GROUP_CONCAT() 或 COUNT(DISTINCT) 表達式。

想要知道一條語句有沒有使用臨時表，通過 EXPLAIN 查看 Extra 列有沒有說明 Using temporary。EXPLAIN 不一定會對臨時表或派生臨時表使用臨時表。

當服務器創建一個內部臨時表的時候（無論是內存表或磁盤表），會增加 Created_tmp_tables 狀態變量的值。如果服務器創建一個磁盤臨時表（直接建立或者從內存表轉換）會增加 Created_tmp_disk_tables 狀態變量的值。通過 show global status like 'Create%'; 語句查看。

某些查詢條件會阻止使用內存中的臨時表，在這種情況下，服務器會使用磁盤上的表：

  ● 表中存在BLOB或TEXT列；

  ● 如果使用 UNION 或 UNION ALL，則在 SELECT 列表中存在最大長度大于512（二進制字符串的字節，非二進制字符串的字符）的任何字符串列

  ● SHOW COLUMNS 和 DESCRIBE 語句使用 BLOB 作為某些列的類型，因此用于結果的臨時表是一個磁盤上表。