首頁 > 軟體

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。


IT145.com E-mail:sddin#qq.com