Group By 深度優(yōu)化，真是絕了！
點(diǎn)擊上方藍(lán)色字體，選擇“設(shè)為星標(biāo)”
、
來(lái)源：www.juejin.cn/post/6957696820621344775
導(dǎo)讀
當(dāng)我們交友平臺(tái)在線上運(yùn)行一段時(shí)間后，為了給平臺(tái)用戶在搜索好友時(shí)，在搜索結(jié)果中推薦并置頂他感興趣的好友，這時(shí)候，我們會(huì)對(duì)用戶的行為做數(shù)據(jù)分析，根據(jù)分析結(jié)果給他推薦其感興趣的好友。
這里，我采用最簡(jiǎn)單的SQL分析法：對(duì)用戶過(guò)去查看好友的性別和年齡進(jìn)行統(tǒng)計(jì)，按照年齡進(jìn)行分組得到統(tǒng)計(jì)結(jié)果。依據(jù)該結(jié)果，給用戶推薦計(jì)數(shù)最高的某個(gè)性別及年齡的好友。
那么，假設(shè)我們現(xiàn)在有一張用戶瀏覽好友記錄的明細(xì)表t_user_view，該表的表結(jié)構(gòu)如下：
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統(tǒng)計(jì)，見(jiàn)上面的表結(jié)構(gòu)，我冗余了被查看用戶的性別和年齡字段。
現(xiàn)在結(jié)合上面的表結(jié)構(gòu)和表記錄，我以user_id=1的用戶為例，分組統(tǒng)計(jì)該用戶查看的年齡在18 ~ 22之間的女性用戶的數(shù)量：
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
得到統(tǒng)計(jì)結(jié)果如下：
可見(jiàn)：
該用戶查看年齡為18的女性用戶數(shù)為2 該用戶查看年齡為19的女性用戶數(shù)為1 該用戶查看年齡為20的女性用戶數(shù)為3
所以，user_id=1的用戶對(duì)年齡為20的女性用戶更感興趣，可以更多推薦20歲的女性用戶給他。
如果此時(shí)，t_user_view這張表的記錄數(shù)達(dá)到千萬(wàn)規(guī)模，想必這條SQL的查詢效率會(huì)直線下降，為什么呢？有什么辦法優(yōu)化呢？
想要知道原因，不得不先看一下這條SQL執(zhí)行的過(guò)程是怎樣的？
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
執(zhí)行完上面的explain語(yǔ)句，我們得到如下結(jié)果：
在Extra這一列中出現(xiàn)了三個(gè)Using，這3個(gè)Using代表了《導(dǎo)讀》中的groupBy語(yǔ)句分別經(jīng)歷了3個(gè)執(zhí)行階段：
Using where：通過(guò)搜索可能的 idx_user_viewed_user索引樹(shù)定位到滿足部分條件的viewed_user_id，然后，回表繼續(xù)查找滿足其他條件的記錄Using temporary：使用臨時(shí)表暫存待 groupBy分組及統(tǒng)計(jì)字段信息Using filesort：使用 sort_buffer對(duì)分組字段進(jìn)行排序
這3個(gè)階段中出現(xiàn)了一個(gè)名詞：臨時(shí)表。這個(gè)名詞我在《MySQL分表時(shí)機(jī)：100w？300w？500w？都對(duì)也都不對(duì)！》一文中有講到，這是MySQL連接線程可以獨(dú)立訪問(wèn)和處理的內(nèi)存區(qū)域，那么，這個(gè)臨時(shí)表長(zhǎng)什么樣呢？
下面我就先講講這張MySQL的臨時(shí)表，然后，結(jié)合上面提到的3個(gè)階段，詳細(xì)講解《導(dǎo)讀》中SQL的執(zhí)行過(guò)程。
臨時(shí)表
我們還是先看看《導(dǎo)讀》中的這條包含groupBy語(yǔ)句的SQL，其中包含一個(gè)分組字段viewed_user_age和一個(gè)統(tǒng)計(jì)字段count(*)，這兩個(gè)字段是這條SQL中統(tǒng)計(jì)所需的部分，如果我們要做這樣一個(gè)統(tǒng)計(jì)和分組，并把結(jié)果固化下來(lái)，肯定是需要一個(gè)內(nèi)存或磁盤(pán)區(qū)域落下第一次統(tǒng)計(jì)的結(jié)果，然后，以這個(gè)結(jié)果做下一次的統(tǒng)計(jì)，因此，像這種存儲(chǔ)中間結(jié)果，并以此結(jié)果做進(jìn)一步處理的區(qū)域，MySQL叫它臨時(shí)表。
剛剛提到既可以將中間結(jié)果落在內(nèi)存，也可以將這個(gè)結(jié)果落在磁盤(pán)，因此，在MySQL中就出現(xiàn)了兩種臨時(shí)表：內(nèi)存臨時(shí)表和磁盤(pán)臨時(shí)表。
內(nèi)存臨時(shí)表
什么是內(nèi)存臨時(shí)表？在早期數(shù)據(jù)量不是很大的時(shí)候，以存儲(chǔ)分組及統(tǒng)計(jì)字段為例，那么，基本上內(nèi)存就可以完全存放下分組及統(tǒng)計(jì)字段對(duì)應(yīng)的所有值，這個(gè)存放大小由tmp_table_size參數(shù)決定。這時(shí)候，這個(gè)存放值的內(nèi)存區(qū)域，MySQL就叫它內(nèi)存臨時(shí)表。
此時(shí)，或許你已經(jīng)覺(jué)得MySQL將中間結(jié)果存放在內(nèi)存臨時(shí)表，性能已經(jīng)有了保障，但是，在《MySQL分表時(shí)機(jī)：100w？300w？500w？都對(duì)也都不對(duì)！》中，我提到過(guò)內(nèi)存頻繁的存取會(huì)產(chǎn)生碎片，為此，MySQL設(shè)計(jì)了一套新的內(nèi)存分配和釋放機(jī)制，可以減少甚至避免臨時(shí)表內(nèi)存碎片，提升內(nèi)存臨時(shí)表的利用率。
此時(shí)，你可能會(huì)想，我講了用戶態(tài)的內(nèi)存分配器：ptmalloc和tcmalloc，無(wú)論是哪個(gè)分配器，它的作用就是避免用戶進(jìn)程頻繁向Linux內(nèi)核申請(qǐng)內(nèi)存空間，造成CPU在用戶態(tài)和內(nèi)核態(tài)之間頻繁切換，從而影響內(nèi)存存取的效率。用它們就可以解決內(nèi)存利用率的問(wèn)題，為什么MySQL還要自己搞一套？
或許MySQL的作者覺(jué)得無(wú)論哪個(gè)內(nèi)存分配器，它的實(shí)現(xiàn)都過(guò)于復(fù)雜，這些復(fù)雜性會(huì)影響MySQL對(duì)于內(nèi)存處理的性能，因此，MySQL自身又實(shí)現(xiàn)了一套內(nèi)存分配機(jī)制：MEM_ROOT。它的內(nèi)存處理機(jī)制相對(duì)比較簡(jiǎn)單，內(nèi)存臨時(shí)表的分配就是采用這樣一種方式。
下面，我就以《導(dǎo)讀》中的SQL為例，詳細(xì)講解一下分組統(tǒng)計(jì)是如何使用MEM_ROOT內(nèi)存分配和釋放機(jī)制的？Spring Boot 學(xué)習(xí)筆記，這個(gè)分享給你，太全了。
MEM_ROOT
我們先看看MEM_ROOT的結(jié)構(gòu)，MEM_ROOT設(shè)計(jì)比較簡(jiǎn)單，主要包含這幾部分，如下圖：
free：一個(gè)單向鏈表，鏈表中每一個(gè)單元叫block，block中存放的是空閑的內(nèi)存區(qū)，每個(gè)block包含3個(gè)元素：
left： block中剩余的內(nèi)存大小size： block對(duì)應(yīng)內(nèi)存的大小next：指向下一個(gè) block的指針
如上圖，free所在的行就是一個(gè)free鏈表，鏈表中每個(gè)箭頭相連的部分就是block，block中有left和?size，每個(gè)block之間的箭頭就是next指針
used：一個(gè)單向鏈表，鏈表中每一個(gè)單元叫block，block中存放已使用的內(nèi)存區(qū)，同樣，每個(gè)block包含上面3 個(gè)元素
min_malloc：控制一個(gè)?block?剩余空間還有多少的時(shí)候從free鏈表移除，加入到used鏈表中
block_size：block對(duì)應(yīng)內(nèi)存的大小
block_num：MEM_ROOT?管理的block數(shù)量
first_block_usage：free鏈表中第一個(gè)block不滿足申請(qǐng)空間大小的次數(shù)
pre_alloc：當(dāng)釋放整個(gè)MEM_ROOT的時(shí)候可以通過(guò)參數(shù)控制，選擇保留pre_alloc指向的block
下面我就以《導(dǎo)讀》中的分組統(tǒng)計(jì)SQL為例，看一下MEM_ROOT是如何分配內(nèi)存的？
分配
初始化
MEM_ROOT，見(jiàn)上圖：min_malloc = 32block_num = 4first_block_usage = 0pre_alloc = 0block_size = 1000err_handler = 0free = 0used = 0申請(qǐng)內(nèi)存，見(jiàn)上圖：
由于初始化
MEM_ROOT時(shí)，free = 0，說(shuō)明free鏈表不存在，故向Linux內(nèi)核申請(qǐng)4個(gè)大小為1000/4=250的block，構(gòu)造一個(gè)free鏈表，如上圖，鏈表中包含4個(gè)block?，結(jié)合前面free鏈表結(jié)構(gòu)的說(shuō)明，每個(gè)block中size為250，left也為250分配內(nèi)存，見(jiàn)上圖：
(1) 遍歷
free鏈表，從free鏈表頭部取出第一個(gè)block，如上圖向下的箭頭(2) 從取出的
block中劃分220大小的內(nèi)存區(qū)，如上圖向右的箭頭上面-220，block中的left從250變成30(3) 將劃分的
220大小的內(nèi)存區(qū)分配給SQL中的groupby字段viewed_user_age和統(tǒng)計(jì)字段count(*)，用于后面的統(tǒng)計(jì)分組數(shù)據(jù)收集到該內(nèi)存區(qū)(4) 由于第(2)步中，分配后的
block中的left變成30，30 < 32，即小于第(1)步中初始化的min_malloc，所以，結(jié)合上面min_malloc的含義的講解，該block將插入used鏈表尾部，如上圖底部，由于used鏈表在第(1)步初始化時(shí)為0，所以，該block插入used鏈表的尾部，即插入頭部
釋放
下面還是以《導(dǎo)讀》中的分組統(tǒng)計(jì)為例，我們?cè)賮?lái)看一下MEM_ROOT是如何釋放內(nèi)存的？
如上圖，MEM_ROOT釋放內(nèi)存的過(guò)程如下：
遍歷 used鏈表中，找到需要釋放的block，如上圖，block(30,250)為之前已分配給分組統(tǒng)計(jì)用的block將 block(30,250)中的left + 220，即30 + 220 = 250，釋放該block已使用的220大小的內(nèi)存區(qū)，得到釋放后的block(250,250)將 block(250,250)插入free鏈表尾部，如上圖曲線箭頭部分
通過(guò)MEM_ROOT內(nèi)存分配和釋放的講解，我們發(fā)現(xiàn)MEM_ROOT的內(nèi)存管理方式是在每個(gè)Block上連續(xù)分配，內(nèi)部碎片基本在每個(gè)Block的尾部，由min_malloc成員變量控制，但是min_malloc的值是在代碼中寫(xiě)死的，有點(diǎn)不夠靈活。所以，對(duì)一個(gè)block來(lái)說(shuō)，當(dāng)left小于min_malloc，從其申請(qǐng)的內(nèi)存越大，那么block中的left值越小，那么，該block的內(nèi)存利用率越高，碎片越少，反之，碎片越多。這個(gè)寫(xiě)死是MySQL的內(nèi)存分配的一個(gè)缺陷。
磁盤(pán)臨時(shí)表
當(dāng)分組及統(tǒng)計(jì)字段對(duì)應(yīng)的所有值大小超過(guò)tmp_table_size決定的值，那么，MySQL將使用磁盤(pán)來(lái)存儲(chǔ)這些值。這個(gè)存放值的磁盤(pán)區(qū)域，MySQL叫它磁盤(pán)臨時(shí)表。
我們都知道磁盤(pán)存取的性能一定比內(nèi)存存取的性能差很多，因?yàn)闀?huì)產(chǎn)生磁盤(pán)IO，所以，一旦分組及統(tǒng)計(jì)字段不得不寫(xiě)入磁盤(pán)，那性能相對(duì)是很差的，所以，我們盡量調(diào)大參數(shù)tmp_table_size，使得組及統(tǒng)計(jì)字段可以在內(nèi)存臨時(shí)表中處理。
執(zhí)行過(guò)程
無(wú)論是使用內(nèi)存臨時(shí)表，還是磁盤(pán)臨時(shí)表，臨時(shí)表對(duì)組及統(tǒng)計(jì)字段的處理的方式都是一樣的?！秾?dǎo)讀》中我提到想要優(yōu)化《導(dǎo)讀》中的那條SQL，就需要知道SQL執(zhí)行的原理，所以，下面我就結(jié)合上面講解的臨時(shí)表的概念，詳細(xì)講講這條SQL的執(zhí)行過(guò)程，見(jiàn)下圖：
創(chuàng)建臨時(shí)表
temporary，表里有兩個(gè)字段viewed_user_age和count(*)，主鍵是viewed_user_age，如上圖，倒數(shù)第二個(gè)框temporary表示臨時(shí)表，框中包含兩個(gè)字段viewed_user_age和count(*)，框內(nèi)就是這兩個(gè)字段對(duì)應(yīng)的值，其中viewed_user_age就是這張臨時(shí)表的主鍵掃描表輔助索引樹(shù)
idx_user_viewed_user，依次取出葉子節(jié)點(diǎn)上的id值，即從索引樹(shù)葉子節(jié)點(diǎn)中取到表的主鍵id。如上圖中的idx_user_viewed_user框就是索引樹(shù)，框右側(cè)的箭頭表示取到表的主鍵id根據(jù)主鍵id到聚簇索引
cluster_index的葉子節(jié)點(diǎn)中查找記錄，即掃描cluster_index葉子節(jié)點(diǎn)：(1) 得到一條記錄，然后取到記錄中的
viewed_user_age字段值。如上圖，cluster_index框，框中最右邊的一列就是viewed_user_age字段的值(2) 如果臨時(shí)表中沒(méi)有主鍵為
viewed_user_age的行，就插入一條記錄 (viewed_user_age, 1)。如上圖的temporary框，其左側(cè)箭頭表示將cluster_index框中的viewed_user_age字段值寫(xiě)入temporary臨時(shí)表(3) 如果臨時(shí)表中有主鍵為
viewed_user_age的行，就將viewed_user_age這一行的count(*)值加 1。如上圖的temporary框遍歷完成后，再根據(jù)字段
viewed_user_age在sort_buffer中做排序，得到結(jié)果集返回給客戶端。如上圖中的最右邊的箭頭，表示將temporary框中的viewed_user_age和count(*)的值寫(xiě)入sort_buffer，然后，在sort_buffer中按viewed_user_age字段進(jìn)行排序
通過(guò)《導(dǎo)讀》中的SQL的執(zhí)行過(guò)程的講解，我們發(fā)現(xiàn)該過(guò)程經(jīng)歷了4個(gè)部分：idx_user_viewed_user、cluster_index、temporary和sort_buffer，對(duì)比上面explain的結(jié)果，其中前2個(gè)就對(duì)應(yīng)結(jié)果中的Using where，temporary對(duì)應(yīng)的是Using temporary，sort_buffer對(duì)應(yīng)的是Using filesort。
優(yōu)化方案
此時(shí)，我們有什么辦法優(yōu)化這條SQL呢？
既然這條SQL執(zhí)行需要經(jīng)歷4個(gè)部分，那么，我們可不可以去掉最后兩部分呢，即去掉temporary和sort_buffer？Spring Boot 學(xué)習(xí)筆記，這個(gè)分享給你，太全了。
答案是可以的，我們只要給SQL中的表t_user_view添加如下索引：
ALTER?TABLE?`t_user_view`?ADD?INDEX?`idx_user_age_sex`?(`user_id`,?`viewed_user_age`,?`viewed_user_sex`);
你可以自己嘗試一下哦！用explain康康有什么改變！
小結(jié)
本章圍繞《導(dǎo)讀》中的分組統(tǒng)計(jì)SQL，通過(guò)explain分析SQL的執(zhí)行階段，結(jié)合臨時(shí)表的結(jié)構(gòu)，進(jìn)一步剖析了SQL的詳細(xì)執(zhí)行過(guò)程，最后，引出優(yōu)化方案：新增索引，避免臨時(shí)表對(duì)分組字段的統(tǒng)計(jì)，及sort_buffer對(duì)分組和統(tǒng)計(jì)字段排序。
當(dāng)然，如果實(shí)在無(wú)法避免使用臨時(shí)表，那么，盡量調(diào)大tmp_table_size，避免使用磁盤(pán)臨時(shí)表統(tǒng)計(jì)分組字段。
思考題
為什么新增了索引idx_user_age_sex可以避免臨時(shí)表對(duì)分組字段的統(tǒng)計(jì)，及sort_buffer對(duì)分組和統(tǒng)計(jì)字段排序？
提示：結(jié)合索引查找的原理。