mysql 建立大量磁碟暫存資料表

標籤：暫存資料表   big_tables   

發現 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 作為某些列的類型，因此用於結果的暫存資料表是一個磁碟上表。

mysql 建立大量磁碟暫存資料表