1.什么是臨時表

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

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

2.臨時表的使用場景

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

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

為了方便下文的描述，我們建立如下表結(jié)構(gòu)：

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條數(shù)據(jù)：

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兩端的結(jié)果集取并集，也就是兩個結(jié)果集加起來，重復(fù)的數(shù)據(jù)行，只取其中一行。這里需要注意，unoin是有在多個數(shù)據(jù)集中排重的語義的。

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

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

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

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

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

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

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

4.從臨時表中取出數(shù)據(jù)，返回客戶端結(jié)果，并刪除臨時表。

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

從以上過程中，我們可以知道，內(nèi)存臨時表的作用：通過唯一鍵約束，實現(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í)行第一個子查詢，將查詢的結(jié)果，作為結(jié)果集的一部分，返回給客戶端。

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

groupby

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

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

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

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

1.創(chuàng)建臨時表，表中有兩個字段：m和c，其中m為主鍵，因為group by字段m的值，必須是唯一的。

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

3.遍歷完成后，在根據(jù)字段m做排序，得到最終結(jié)果返回給客戶端。

對于步驟3中的排序流程，可以參考 如何優(yōu)化sql中的orderBy。

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

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

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

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

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

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

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

2.當(dāng)遇到第一個3的時候，說明已經(jīng)積累了2個2了，此時結(jié)果集的第二行數(shù)據(jù)就是(2,2)；

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

在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)字段數(shù)據(jù)的關(guān)聯(lián)更新。如下語句：

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

為t1表增加字段z，z的字段值為id值與10取模后的結(jié)果，同時在z上添加索引。這樣當(dāng)我們再執(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è)務(wù)場景比較復(fù)雜，分組的場景比較多，使用伴生字段方案需要在表中增加的額外字段就會比較多。這將會使我們的數(shù)據(jù)表結(jié)果變得比較復(fù)雜。

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

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

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

下面我們舉例說明，執(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.數(shù)據(jù)表掃描完后，對sort_buffer中的m進行排序。

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

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

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

總結(jié)

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

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

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

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

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

最新評論