mysql中的臨時表如何使用

2022-09-28 14:01:16

1.什麼是臨時表

內部臨時表是sql語句執行過程中，用來儲存中間結果的的資料表，其作用類似於：join語句執行過程中的joinbuffer，order by語句執行過程中的sortBuffer一樣。

這個表是mysql自己建立出來的，對使用者端程式不可見。那麼mysql什麼時候會建立內部臨時表呢？建立的內部臨時表的表結構又是怎麼樣的呢？

2.臨時表的使用場景

在mysql中常見的使用臨時表的場景，有兩個：unoin語句和groupby語句。

為了更好的瞭解內部臨時表在unoin和groupby中是如何起作用的，我們先了解一下unoin和groupby的執行流程。

為了方便下文的描述，我們建立如下表結構：

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` int(11)  DEFAULT NULL,
  `b` int(11) default null,
  PRIMARY KEY (`id`) USING BTREE,
  key (`a`) using BTREE
) ENGINE=InnoDB;

建立表t1，其中id為主鍵，a為普通索引，然後向表中插入1000條資料：

drop procedure idata;
delimiter ;;
create procedure idata()
begin
	declare i int;
	set i=1;
	while(i<=1000)do
		insert into `t1` values(i,i,i);
		set i=i+1;
	end while;
end;;
delimiter ;
call idata();

union

我們都知道union的語意是對 unoin兩端的結果集取並集，也就是兩個結果集加起來，重複的資料行，只取其中一行。這裡需要注意，unoin是有在多個資料集中排重的語意的。

下面我們執行下面這條語句：

(select 1000 as f) union (select id from t1 order by id desc limit 2);

在這條語句的語意是：將t1中的資料，按照id倒序排列後，取出前兩行資料的id，與"1000"取並集。

這條語句在mysql中的執行流程如下：

1.建立一個記憶體臨時表，這個記憶體臨時表只有一個整形欄位f，並且f欄位為主鍵(因為要進行排重)。

2.執行第一個子查詢，得到1000這個值，放入到記憶體臨時表中。

3.執行第二個子查詢：取出第一個滿足條件資料行中的id=1000，嘗試寫入臨時表，這時會出現違反唯一性約束的情況，導致插入失敗，然後繼續執行。取出第二行資料的id=999，插入成功。

4.從臨時表中取出資料，返回使用者端結果，並刪除臨時表。

同時，我們可以檢視上述查詢語句的執行計劃，來驗證上述執行流程：

從以上過程中，我們可以知道，記憶體臨時表的作用：通過唯一鍵約束，實現了union的語意。

如果把上述語句中的union，替換成 union all的話，那查詢語句就失去了"去重"的語意了。那麼，mysql在執行查詢語句的過程中，是否還會使用臨時表呢？

我們使用以下查詢語句進行驗證：

(select 1000 as f) union all (select id from t1 order by id desc limit 2);

通過查詢sql的執行計劃，我們會發現，查詢語句執行過程中，不在需要臨時表了。

整個查詢語句的執行流程如下：

1.執行第一個子查詢，將查詢的結果，作為結果集的一部分，返回給使用者端。

2.執行第二個子查詢，將查詢的結果作為結果集的一部分，返回給使用者端。

groupby

除了unoin查詢語句在執行過程中會使用臨時表外，groupby 查詢語句在執行過程中，也會使用臨時表。為了方便說明問題，我們執行如下查詢語句：

select id%10 as m, count(1) as c from t1 group by m order by m;

該語句的語意，將表中所有資料中的id值，對10進行取模，並將取模後的結果進行分組，然後統計出每組資料的個數。查詢語句執行計劃如下：

該查詢語句的執行流程如下：

1.建立臨時表，表中有兩個欄位：m和c，其中m為主鍵，因為group by欄位m的值，必須是唯一的。

2.掃描表t1的索引a，依次取出葉子結點上的id值，並計算id%10，將計算結果記為x，如果臨時表中沒有m=x的行，就插入一個記錄(x,1)。如果表中有m=x資料行，那麼就將x這一行的c值加1。

3.遍歷完成後，在根據欄位m做排序，得到最終結果返回給使用者端。

對於步驟3中的排序流程，可以參考如何優化sql中的orderBy。

3.groupby 如何優化

通過上面的描述，我們知道了groupby的執行流程。groupby在執行過程中，需要建立一個帶有唯一鍵索引的臨時表，其中唯一鍵索引欄位就是groupby的欄位。這個執行代價還是比較高的，而且這個臨時表還是一次性的。

為了提高groupby語句的執行效能，我們可以從"不使用臨時表"的角度下手。首先我們可以這樣想：要想讓groupby的過程中不使用臨時表，我們就要知道，臨時表在groupby的過程中，解決了什麼問題？如果，我們能找到另外一種不使用臨時表，也能解決這個問題的方案，那麼我們就可以不使用臨時表了。

首先，我們知道，在日常開發過程中，我們使用groupby主要就是為了實現：將表中所有的資料，按照指定欄位進行分組。把欄位值相同的資料劃分為一個組，然後在對組內的資料執行聚合函數，聚合函數計算的結果，作為結果集中的一行資料。

而在這個過程中，臨時表的作用就是在掃描資料表的時候，對每行資料屬於哪個組，進行記錄，同時執行聚合函數的邏輯。之所以需要一個臨時表來記錄每行資料屬於哪個組，主要是因為表中的資料，按照"group by欄位"維度，不是有序的。

如果表中的資料本身就是按照"groupby欄位"有序的話，也就是屬於同一個組的資料都分佈在一起，那麼就不需要臨時表，也可以對資料進行分組。舉例如下圖，如果執行groupby，同時計算每組資料個數。執行流程大致如下:

1.從左到右掃描資料，並依次累加，當遇到第一個2時，說明已經積累了3個1了，此時結果集的第一行資料就是(1,3)。

2.當遇到第一個3的時候，說明已經積累了2個2了，此時結果集的第二行資料就是(2,2)；

3.按照以上邏輯逐個計算，就可以得到最終結果。

在mysql中，如果分組欄位上有索引的話，執行查詢過程中，mysql就不會建立臨時表了。

我們可以執行如下查詢語句進行驗證：

explain
select id as m from t1 group by id;

通過檢視執行計劃，我們可以發現，因為分組欄位id，是主鍵，本身是有序的。這裡並沒有使用臨時表：

但是很多時候，分組欄位並不是表中的一個具體欄位。而是通過一定計算後的邏輯欄位，如：

select id%10 as m from t1 group by m

這裡分組欄位m，並不是t1表中的一個欄位，而是對id對10取模後的一個邏輯欄位。為了讓分組欄位有序，下面給大家介紹兩種優化手段。

1.生成伴生欄位，並建立索引

從mysql 5.7開始，支援了generated column機制，來實現欄位資料的關聯更新。如下語句：

alter table t1 add column z int generated always as (id % 10), add index (z);

為t1表增加欄位z，z的欄位值為id值與10取模後的結果，同時在z上新增索引。這樣當我們再執行：

explain 
select id%100 as m,count(*) as c from t1 group by m

或者：

explain 
select z as m,count(id) as c from t1 group by m

執行計劃如下：

此時就不在使用臨時表了。

上面的伴生欄位的方案，需要我們向表中新增額外欄位，如果業務場景比較複雜，分組的場景比較多，使用伴生欄位方案需要在表中增加的額外欄位就會比較多。這將會使我們的資料表結果變得比較複雜。

2.直接對分組欄位進行排序

如果我們可以預估到，在執行groupby語句時，分組後的資料量比較大，使用的記憶體臨時表可能都無法儲存，那麼記憶體臨時表就會被替換成磁碟臨時表，這個替換的閾值，由變數"tmp_table_size"控制，該變數的預設值為16M，如果在查詢語句執行過程，需要存放到臨時表中的資料量超過16M，那麼使用的臨時表就會變成磁碟臨時表，磁碟臨時表預設的儲存引擎是InnoDB，磁碟臨時表的效能相比記憶體臨時表效能更低。

對於這種情況，mysql提供了 SQL_BIG_RESULT語句，該語句的作用就是告訴優化器：這個語句涉及到的資料量比較大，直接使用磁碟臨時表。但是這裡使用的磁碟臨時表，會調整儲存的資料結構，資料結構不再是B+樹，而是陣列。

下面我們舉例說明，執行如下查詢語句的的流程如下：

explain 
select sql_big_result id%100 as m,count(id) as c from t1 group by m ;

執行流程：

1.初始化sort_buffer，確定放入一個整形欄位，記為m。

2.掃描t1索引a，依次取出葉子節點中的主鍵id的值，並對100取模，然後插入到sort_buffer中。

3.資料表掃描完後，對sort_buffer中的m進行排序。

4.排序後，就得到了一個針對分組欄位的有序陣列。

有了針對分組欄位的有序陣列，那麼就可以通過遍歷該陣列實現groupby的語意了。

通過檢視上述查詢語句的執行計劃，可以發現，不在使用臨時表了。

總結

為了保證groupby的執行效能，在使用groupby的時候要做到以下幾點：

1.儘量讓 group by 過程用上表的索引，確認方法是 explain 結果裡沒有 Using temporary 和 Using filesort。

2.如果 group by 需要統計的資料量不大，儘量只使用記憶體臨時表；也可以通過適當調大 tmp_table_size 引數，來避免用到磁碟臨時表。

3.如果資料量實在太大，使用 SQL_BIG_RESULT 這個提示，來告訴優化器直接使用排序演演算法得到 group by 的結果。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。