1.什么是臨時(shí)表

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

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

2.臨時(shí)表的使用場(chǎng)景

在mysql中常見的使用臨時(shí)表的場(chǎng)景，有兩個(gè)：unoin語(yǔ)句和groupby語(yǔ)句。

為了更好的了解內(nèi)部臨時(shí)表在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的語(yǔ)義是對(duì) unoin兩端的結(jié)果集取并集，也就是兩個(gè)結(jié)果集加起來(lái)，重復(fù)的數(shù)據(jù)行，只取其中一行。這里需要注意，unoin是有在多個(gè)數(shù)據(jù)集中排重的語(yǔ)義的。

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

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

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

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

1.創(chuàng)建一個(gè)內(nèi)存臨時(shí)表，這個(gè)內(nèi)存臨時(shí)表只有一個(gè)整形字段f，并且f字段為主鍵(因?yàn)橐M(jìn)行排重)。

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

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

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

同時(shí)，我們可以查看上述查詢語(yǔ)句的執(zhí)行計(jì)劃，來(lái)驗(yàn)證上述執(zhí)行流程：

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

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

我們使用以下查詢語(yǔ)句進(jìn)行驗(yàn)證：

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

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

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

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

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

groupby

除了unoin查詢語(yǔ)句在執(zhí)行過(guò)程中會(huì)使用臨時(shí)表外，groupby 查詢語(yǔ)句在執(zhí)行過(guò)程中，也會(huì)使用臨時(shí)表。為了方便說(shuō)明問(wèn)題，我們執(zhí)行如下查詢語(yǔ)句：

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

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

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

1.創(chuàng)建臨時(shí)表，表中有兩個(gè)字段：m和c，其中m為主鍵，因?yàn)間roup by字段m的值，必須是唯一的。

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

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

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

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

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

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

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

而在這個(gè)過(guò)程中，臨時(shí)表的作用就是在掃描數(shù)據(jù)表的時(shí)候，對(duì)每行數(shù)據(jù)屬于哪個(gè)組，進(jìn)行記錄，同時(shí)執(zhí)行聚合函數(shù)的邏輯。之所以需要一個(gè)臨時(shí)表來(lái)記錄每行數(shù)據(jù)屬于哪個(gè)組，主要是因?yàn)楸碇械臄?shù)據(jù)，按照"group by字段"維度，不是有序的。

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

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

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

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

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

我們可以執(zhí)行如下查詢語(yǔ)句進(jìn)行驗(yàn)證：

explain
select id as m from t1 group by id;

通過(guò)查看執(zhí)行計(jì)劃，我們可以發(fā)現(xiàn)，因?yàn)榉纸M字段id，是主鍵，本身是有序的。這里并沒(méi)有使用臨時(shí)表：

但是很多時(shí)候，分組字段并不是表中的一個(gè)具體字段。而是通過(guò)一定計(jì)算后的邏輯字段，如：

select id%10 as m from t1 group by m

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

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

從mysql 5.7開始，支持了generated column機(jī)制，來(lái)實(shí)現(xiàn)字段數(shù)據(jù)的關(guān)聯(lián)更新。如下語(yǔ)句：

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

為t1表增加字段z，z的字段值為id值與10取模后的結(jié)果，同時(shí)在z上添加索引。這樣當(dāng)我們?cè)賵?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í)行計(jì)劃如下：

此時(shí)就不在使用臨時(shí)表了。

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

2.直接對(duì)分組字段進(jìn)行排序

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

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

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

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

執(zhí)行流程：

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

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

3.數(shù)據(jù)表掃描完后，對(duì)sort_buffer中的m進(jìn)行排序。

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

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

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

總結(jié)

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

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

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

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

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

最新評(píng)論