1.什么是臨時表

內部臨時表是sql語句執(zhí)行過程中，用來存儲中間結果的的數據表，其作用類似于：join語句執(zhí)行過程中的joinbuffer，order by語句執(zhí)行過程中的sortBuffer一樣。

這個表是mysql自己創(chuàng)建出來的，對客戶端程序不可見。那么mysql什么時候會創(chuàng)建內部臨時表呢？創(chuàng)建的內部臨時表的表結構又是怎么樣的呢？

2.臨時表的使用場景

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

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

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

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是有在多個數據集中排重的語義的。

下面我們執(zhí)行下面這條語句：

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

在這條語句的語義是：將t1中的數據，按照id倒序排列后，取出前兩行數據的id，與"1000"取并集。

這條語句在mysql中的執(zhí)行流程如下：

1.創(chuàng)建一個內存臨時表，這個內存臨時表只有一個整形字段f，并且f字段為主鍵(因為要進行排重)。

2.執(zhí)行第一個子查詢，得到1000這個值，放入到內存臨時表中。

3.執(zhí)行第二個子查詢：取出第一個滿足條件數據行中的id=1000，嘗試寫入臨時表，這時會出現(xiàn)違反唯一性約束的情況，導致插入失敗，然后繼續(xù)執(zhí)行。取出第二行數據的id=999，插入成功。

4.從臨時表中取出數據，返回客戶端結果，并刪除臨時表。

同時，我們可以查看上述查詢語句的執(zhí)行計劃，來驗證上述執(zhí)行流程：

從以上過程中，我們可以知道，內存臨時表的作用：通過唯一鍵約束，實現(xiàn)了union的語義。

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

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

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

通過查詢sql的執(zhí)行計劃，我們會發(fā)現(xiàn)，查詢語句執(zhí)行過程中，不在需要臨時表了。

整個查詢語句的執(zhí)行流程如下：

1.執(zhí)行第一個子查詢，將查詢的結果，作為結果集的一部分，返回給客戶端。

2.執(zhí)行第二個子查詢，將查詢的結果作為結果集的一部分，返回給客戶端。

groupby

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

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

該語句的語義，將表中所有數據中的id值，對10進行取模，并將取模后的結果進行分組，然后統(tǒng)計出每組數據的個數。查詢語句執(zhí)行計劃如下：

該查詢語句的執(zhí)行流程如下：

1.創(chuàng)建臨時表，表中有兩個字段：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中的排序流程，可以參考 如何優(yōu)化sql中的orderBy。

3.groupby 如何優(yōu)化

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

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

首先，我們知道，在日常開發(fā)過程中，我們使用groupby主要就是為了實現(xiàn)：將表中所有的數據，按照指定字段進行分組。把字段值相同的數據劃分為一個組，然后在對組內的數據執(zhí)行聚合函數，聚合函數計算的結果，作為結果集中的一行數據。

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

如果表中的數據本身就是按照"groupby字段"有序的話，也就是屬于同一個組的數據都分布在一起，那么就不需要臨時表，也可以對數據進行分組。 舉例如下圖，如果執(zhí)行groupby，同時計算每組數據個數。執(zhí)行流程大致如下:

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

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

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

在mysql中，如果分組字段上有索引的話，執(zhí)行查詢過程中，mysql就不會建立臨時表了。

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

explain
select id as m from t1 group by id;

通過查看執(zhí)行計劃，我們可以發(fā)現(xiàn)，因為分組字段id，是主鍵，本身是有序的。這里并沒有使用臨時表：

但是很多時候，分組字段并不是表中的一個具體字段。而是通過一定計算后的邏輯字段，如：

select id%10 as m from t1 group by m

這里分組字段m，并不是t1表中的一個字段，而是對id對10取模后的一個邏輯字段。為了讓分組字段有序，下面給大家介紹兩種優(yōu)化手段。

1.生成伴生字段，并建立索引

從mysql 5.7開始，支持了generated column機制，來實現(xiàn)字段數據的關聯(lián)更新。如下語句：

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

為t1表增加字段z，z的字段值為id值與10取模后的結果，同時在z上添加索引。這樣當我們再執(zhí)行：

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 

執(zhí)行計劃如下：

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

上面的伴生字段的方案，需要我們向表中添加額外字段，如果業(yè)務場景比較復雜，分組的場景比較多，使用伴生字段方案需要在表中增加的額外字段就會比較多。這將會使我們的數據表結果變得比較復雜。

2.直接對分組字段進行排序

如果我們可以預估到，在執(zhí)行groupby語句時，分組后的數據量比較大，使用的內存臨時表可能都無法存儲，那么內存臨時表就會被替換成磁盤臨時表，這個替換的閾值，由變量"tmp_table_size"控制，該變量的默認值為16M，如果在查詢語句執(zhí)行過程，需要存放到臨時表中的數據量超過16M，那么使用的臨時表就會變成磁盤臨時表，磁盤臨時表默認的存儲引擎是InnoDB，磁盤臨時表的性能相比內存臨時表性能更低。

對于這種情況，mysql提供了 SQL_BIG_RESULT語句，該語句的作用就是告訴優(yōu)化器：這個語句涉及到的數據量比較大，直接使用磁盤臨時表。但是這里使用的磁盤臨時表，會調整存儲的數據結構，數據結構不再是B+樹，而是數組。

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

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

執(zhí)行流程：

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

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

3.數據表掃描完后，對sort_buffer中的m進行排序。

4.排序后，就得到了一個針對分組字段的有序數組。

有了針對分組字段的有序數組，那么就可以通過遍歷該數組實現(xiàn)groupby的語義了。

通過查看上述查詢語句的執(zhí)行計劃，可以發(fā)現(xiàn)，不在使用臨時表了。

總結

為了保證groupby的執(zhí)行性能，在使用groupby的時候要做到以下幾點：

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

2.如果 group by 需要統(tǒng)計的數據量不大，盡量只使用內存臨時表；也可以通過適當調大 tmp_table_size 參數，來避免用到磁盤臨時表。

3.如果數據量實在太大，使用 SQL_BIG_RESULT 這個提示，來告訴優(yōu)化器直接使用排序算法得到 group by 的結果。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論