Group By 深度最佳化，真是絕了

導讀

當我們交友平臺在線上執行一段時間後，為了給平臺使用者在搜尋好友時，在搜尋結果中推薦並置頂他感興趣的好友，這時候，我們會對使用者的行為做資料分析，根據分析結果給他推薦其感興趣的好友。

這裡，我採用最簡單的SQL分析法：對使用者過去檢視好友的性別和年齡進行統計，按照年齡進行分組得到統計結果。依據該結果，給使用者推薦計數最高的某個性別及年齡的好友。

那麼，假設我們現在有一張使用者瀏覽好友記錄的明細表t_user_view，該表的表結構如下：

CREATE TABLE `t_user_view` （ `id` bigint（20） unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增id’， `user_id` bigint（20） DEFAULT NULL COMMENT ‘使用者id’， `viewed_user_id` bigint（20） DEFAULT NULL COMMENT ‘被檢視使用者id’， `viewed_user_sex` tinyint（1） DEFAULT NULL COMMENT ‘被檢視使用者性別’， `viewed_user_age` int（5） DEFAULT NULL COMMENT ‘被檢視使用者年齡’， `create_time` datetime（3） DEFAULT CURRENT_TIMESTAMP（3）， `update_time` datetime（3） DEFAULT CURRENT_TIMESTAMP（3） ON UPDATE CURRENT_TIMESTAMP（3）， PRIMARY KEY （`id`）， UNIQUE KEY `idx_user_viewed_user` （`user_id`，`viewed_user_id`）） ENGINE=InnoDB DEFAULT CHARSET=utf8；

為了方便使用SQL統計，見上面的表結構，我冗餘了被檢視使用者的性別和年齡欄位。

我們再來看看這張表裡的記錄：

現在結合上面的表結構和表記錄，我以

user_id=1

的使用者為例，分組統計該使用者檢視的年齡在18 ~ 22之間的女性使用者的數量：

SELECT viewed_user_age as age， count（*） as num FROM t_user_view WHERE user_id = 1 AND viewed_user_age BETWEEN 18 AND 22 AND viewed_user_sex = 1 GROUP BY viewed_user_age

得到統計結果如下：

可見：

該使用者檢視年齡為18的女性使用者數為2

該使用者檢視年齡為19的女性使用者數為1

該使用者檢視年齡為20的女性使用者數為3

所以，

user_id=1

的使用者對年齡為20的女性使用者更感興趣，可以更多推薦20歲的女性使用者給他。

如果此時，t_user_view這張表的記錄數達到千萬規模，想必這條SQL的查詢效率會直線下降，為什麼呢？有什麼辦法最佳化呢？

想要知道原因，不得不先看一下這條SQL執行的過程是怎樣的？

Explain

我們先用

explain

看一下這條SQL：

EXPLAIN SELECT viewed_user_age as age， count（*） as num FROM t_user_view WHERE user_id = 1 AND viewed_user_age BETWEEN 18 AND 22 AND viewed_user_sex = 1 GROUP BY viewed_user_age

執行完上面的

explain

語句，我們得到如下結果：

在

Extra

這一列中出現了三個

Using

，這3個

Using

代表了《導讀》中的

groupBy

語句分別經歷了3個執行階段：

Using where：透過搜尋可能的

idx_user_viewed_user

索引樹定位到滿足部分條件的

viewed_user_id

，然後，回表繼續查詢滿足其他條件的記錄

Using temporary：使用臨時表暫存待

groupBy

分組及統計欄位資訊

Using filesort：使用

sort_buffer

對分組欄位進行排序

這3個階段中出現了一個名詞：

臨時表

。這個名詞我在《MySQL分表時機：100w？300w？500w？都對也都不對！》一文中有講到，這是MySQL連線執行緒可以獨立訪問和處理的記憶體區域，那麼，這個臨時表長什麼樣呢？

下面我就先講講這張MySQL的臨時表，然後，結合上面提到的3個階段，詳細講解《導讀》中SQL的執行過程。

臨時表

我們還是先看看《導讀》中的這條包含

groupBy

語句的SQL，其中包含一個分組欄位

viewed_user_age

和一個統計欄位

count（*）

，這兩個欄位是這條SQL中統計所需的部分，如果我們要做這樣一個統計和分組，並把結果固化下來，肯定是需要一個記憶體或磁碟區域落下第一次統計的結果，然後，以這個結果做下一次的統計，因此，像這種儲存中間結果，並以此結果做進一步處理的區域，MySQL叫它

臨時表

。

剛剛提到既可以將中間結果落在記憶體，也可以將這個結果落在磁碟，因此，在MySQL中就出現了兩種臨時表：

記憶體臨時表

和

磁碟臨時表

。

記憶體臨時表

什麼是記憶體臨時表？在早期資料量不是很大的時候，以儲存分組及統計欄位為例，那麼，基本上記憶體就可以完全存放下分組及統計欄位對應的所有值，這個存放大小由

tmp_table_size

引數決定。這時候，這個存放值的記憶體區域，MySQL就叫它記憶體臨時表。

此時，或許你已經覺得MySQL將中間結果存放在記憶體臨時表，效能已經有了保障，但是，在《MySQL分表時機：100w？300w？500w？都對也都不對！》中，我提到過記憶體頻繁的存取會產生碎片，為此，MySQL設計了一套新的記憶體分配和釋放機制，可以減少甚至避免臨時表記憶體碎片，提升記憶體臨時表的利用率。

此時，你可能會想，我講了使用者態的記憶體分配器：ptmalloc和tcmalloc，無論是哪個分配器，它的作用就是避免使用者程序頻繁向Linux核心申請記憶體空間，造成CPU在使用者態和核心態之間頻繁切換，從而影響記憶體存取的效率。用它們就可以解決記憶體利用率的問題，為什麼MySQL還要自己搞一套？

或許MySQL的作者覺得無論哪個記憶體分配器，它的實現都過於複雜，這些複雜性會影響MySQL對於記憶體處理的效能，因此，MySQL自身又實現了一套記憶體分配機制：

MEM_ROOT

。它的記憶體處理機制相對比較簡單，記憶體臨時表的分配就是採用這樣一種方式。

下面，我就以《導讀》中的SQL為例，詳細講解一下分組統計是如何使用

MEM_ROOT

記憶體分配和釋放機制的？Spring Boot 學習筆記，這個分享給你，太全了。

MEM_ROOT

我們先看看

MEM_ROOT

的結構，

MEM_ROOT

設計比較簡單，主要包含這幾部分，如下圖：

free：一個單向連結串列，連結串列中每一個單元叫

block

，

block

中存放的是空閒的記憶體區，每個

block

包含3個元素：

left：

block

中剩餘的記憶體大小

size：

block

對應記憶體的大小

next：指向下一個

block

的指標

如上圖，

free

所在的行就是一個

free

連結串列，連結串列中每個箭頭相連的部分就是

block

，

block

中有

left

和

size

，每個

block

之間的箭頭就是

指標

used：一個單向連結串列，連結串列中每一個單元叫

block

，

block

中存放已使用的記憶體區，同樣，每個

block

包含上面3 個元素

min_malloc：控制一個

block

剩餘空間還有多少的時候從

free

連結串列移除，加入到

used

連結串列中

block_size：

block

對應記憶體的大小

block_num：

MEM_ROOT

管理的

block

數量

first_block_usage：

free

連結串列中第一個

block

不滿足申請空間大小的次數

pre_alloc：當釋放整個

MEM_ROOT

的時候可以透過引數控制，選擇保留

pre_alloc

指向的

block

下面我就以《導讀》中的分組統計SQL為例，看一下

MEM_ROOT

是如何分配記憶體的？

分配

初始化

MEM_ROOT

，見上圖：

min_malloc = 32block_num = 4first_block_usage = 0pre_alloc = 0block_size = 1000err_handler = 0free = 0used = 0

申請記憶體，見上圖：由於初始化

MEM_ROOT

時，

free = 0

，說明

free

連結串列不存在，故向Linux核心申請4個大小為

1000/4=250

的

block

，構造一個

free

連結串列，如上圖，連結串列中包含4個

block

，結合前面

free

連結串列結構的說明，每個

block

中

size

為250，

left

也為250

分配記憶體，見上圖：（1）遍歷

free

連結串列，從

free

連結串列頭部取出第一個

block

，如上圖向下的箭頭

（2）從取出的

block

中劃分

220

大小的記憶體區，如上圖向右的箭頭上面

-220

，

block

中的

left

從

250

變成

（3）將劃分的

220

大小的記憶體區分配給SQL中的

groupby

欄位

viewed_user_age

和統計欄位

count（*）

，用於後面的統計分組資料收集到該記憶體區

（4）由於第（2）步中，分配後的

block

中的

left

變成

，

30 < 32

，即小於第（1）步中初始化的

min_malloc

，所以，結合上面

min_malloc

的含義的講解，該

block

將插入

used

連結串列尾部，如上圖底部，由於

used

連結串列在第（1）步初始化時為0，所以，該

block

插入

used

連結串列的尾部，即插入頭部

釋放

下面還是以《導讀》中的分組統計為例，我們再來看一下

MEM_ROOT

是如何釋放記憶體的？

image-20210323233158459。png

如上圖，

MEM_ROOT

釋放記憶體的過程如下：

遍歷

used

連結串列中，找到需要釋放的

block

，如上圖，

block（30，250）

為之前已分配給分組統計用的

block

將

block（30，250）

中的

left + 220

，即

30 + 220 = 250

，釋放該

block

已使用的

220

大小的記憶體區，得到釋放後的

block（250，250）

將

block（250，250）

插入

free

連結串列尾部，如上圖曲線箭頭部分

透過

MEM_ROOT

記憶體分配和釋放的講解，我們發現

MEM_ROOT

的記憶體管理方式是在每個

Block

上連續分配，內部碎片基本在每個

Block

的尾部，由

min_malloc

成員變數控制，但是

min_malloc

的值是在程式碼中寫死的，有點不夠靈活。所以，對一個

block

來說，當

left

小於

min_malloc

，從其申請的記憶體越大，那麼

block

中的

left

值越小，那麼，該

block

的記憶體利用率越高，碎片越少，反之，碎片越多。這個寫死是MySQL的記憶體分配的一個缺陷。

磁碟臨時表

當分組及統計欄位對應的所有值大小超過

tmp_table_size

決定的值，那麼，MySQL將使用磁碟來儲存這些值。這個存放值的磁碟區域，MySQL叫它磁碟臨時表。

我們都知道磁碟存取的效能一定比記憶體存取的效能差很多，因為會產生磁碟IO，所以，一旦分組及統計欄位不得不寫入磁碟，那效能相對是很差的，所以，我們儘量調大引數

tmp_table_size

，使得組及統計欄位可以在記憶體臨時表中處理。

執行過程

無論是使用記憶體臨時表，還是磁碟臨時表，臨時表對組及統計欄位的處理的方式都是一樣的。《導讀》中我提到想要最佳化《導讀》中的那條SQL，就需要知道SQL執行的原理，所以，下面我就結合上面講解的臨時表的概念，詳細講講這條SQL的執行過程，見下圖：

建立臨時表

temporary

，表裡有兩個欄位

viewed_user_age

和

count（*）

，主鍵是

viewed_user_age

，如上圖，倒數第二個框

temporary

表示臨時表，框中包含兩個欄位

viewed_user_age

和

count（*）

，框內就是這兩個欄位對應的值，其中

viewed_user_age

就是這張臨時表的主鍵

掃描表輔助索引樹

idx_user_viewed_user

，依次取出葉子節點上的

值，即從索引樹葉子節點中取到表的主鍵id。如上圖中的

idx_user_viewed_user

框就是索引樹，框右側的箭頭表示取到表的主鍵id

根據主鍵id到聚簇索引

cluster_index

的葉子節點中查詢記錄，即掃描

cluster_index

葉子節點：

（1）得到一條記錄，然後取到記錄中的

viewed_user_age

欄位值。如上圖，

cluster_index

框，框中最右邊的一列就是

viewed_user_age

欄位的值

（2）如果臨時表中沒有主鍵為

viewed_user_age

的行，就插入一條記錄（

viewed_user_age

， 1）。如上圖的

temporary

框，其左側箭頭表示將

cluster_index

框中的

viewed_user_age

欄位值寫入

temporary

臨時表

（3）如果臨時表中有主鍵為

viewed_user_age

的行，就將

viewed_user_age

這一行的

count（*）

值加 1。如上圖的

temporary

框

遍歷完成後，再根據欄位

viewed_user_age

在

sort_buffer

中做排序，得到結果集返回給客戶端。如上圖中的最右邊的箭頭，表示將

temporary

框中的

viewed_user_age

和

count（*）

的值寫入

sort_buffer

，然後，在

sort_buffer

中按

viewed_user_age

欄位進行排序

透過《導讀》中的SQL的執行過程的講解，我們發現該過程經歷了4個部分：idx_user_viewed_user、cluster_index、temporary和sort_buffer，對比上面explain的結果，其中前2個就對應結果中的Using where，temporary對應的是Using temporary，sort_buffer對應的是Using filesort。

最佳化方案

此時，我們有什麼辦法最佳化這條SQL呢？

既然這條SQL執行需要經歷4個部分，那麼，我們可不可以去掉最後兩部分呢，即去掉temporary和sort_buffer？Spring Boot 學習筆記，這個分享給你，太全了。

答案是可以的，我們只要給SQL中的表

t_user_view

新增如下索引：

ALTER TABLE `t_user_view` ADD INDEX `idx_user_age_sex` （`user_id`， `viewed_user_age`， `viewed_user_sex`）；

你可以自己嘗試一下哦！用

explain

康康有什麼改變！

小結

本章圍繞《導讀》中的分組統計SQL，透過

explain

分析SQL的執行階段，結合臨時表的結構，進一步剖析了SQL的詳細執行過程，最後，引出最佳化方案：新增索引，避免臨時表對分組欄位的統計，及

sort_buffer

對分組和統計欄位排序。

當然，如果實在無法避免使用臨時表，那麼，儘量調大

tmp_table_size

，避免使用磁碟臨時表統計分組欄位。

思考題

為什麼新增了索引

idx_user_age_sex

可以避免臨時表對分組欄位的統計，及

sort_buffer

對分組和統計欄位排序？

提示：結合索引查詢的原理。

Group By 深度最佳化，真是絕了

相關文章