Group by 優(yōu)化
點(diǎn)擊上方藍(lán)色字體，選擇“標(biāo)星公眾號(hào)”
優(yōu)質(zhì)文章，第一時(shí)間送達(dá)
? 作者?|? 等不到的口琴
來源 |? urlify.cn/YFbyQv
76套java從入門到精通實(shí)戰(zhàn)課程分享
一個(gè)標(biāo)準(zhǔn)的 Group by 語句包含排序、分組、聚合函數(shù)，比如 select a,count(*) from t group by a ;? 這個(gè)語句默認(rèn)使用 a 進(jìn)行排序。如果 a 列沒有索引，那么就會(huì)創(chuàng)建臨時(shí)表來統(tǒng)計(jì) a和 count(*)，然后再通過 sort_buffer 按 a 進(jìn)行排序。
標(biāo)準(zhǔn)的執(zhí)行流程
結(jié)構(gòu)：
create?table?t1(id?int?primary?key,?a?int,?b?int,?index(a));
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();
函數(shù)就是向 t1 中插入1000條語句，從(1,1,1)?到(1000,1000,1000)。
執(zhí)行? ?select id%10 as m, count(*) as c from t1 group by m;
解析：
Using index，表示這個(gè)語句使用了覆蓋索引，選擇了索引 a，不需要回表；
Using temporary，表示使用了臨時(shí)表；
Using filesort，表示需要排序。
過程：
1、創(chuàng)建內(nèi)存臨時(shí)表，表里有兩個(gè)字段 m 和 c，主鍵是 m；
2、掃描表 t1 的索引 a，依次取出葉子節(jié)點(diǎn)上的 id 值，計(jì)算 id%10 的結(jié)果，記為 x；
1）如果臨時(shí)表中沒有主鍵為 x 的行，就插入一個(gè)記錄 (x,1);
2）如果表中有主鍵為 x 的行，就將 x 這一行的 c 值加 1；
第2 步如果發(fā)現(xiàn)內(nèi)存臨時(shí)表存儲(chǔ)的總字段長(zhǎng)度到達(dá)參數(shù) tmp_table_size 設(shè)置的大小，那么就會(huì)將內(nèi)存臨時(shí)表升級(jí)為磁盤臨時(shí)表，然后重新開始遍歷計(jì)算。
3、遍歷完成后，再根據(jù)字段 m 做排序，得到結(jié)果集返回給客戶端。
最后的排序就是下圖虛線框中的操作，如果 sort_buffer 設(shè)置的大小不夠大，那么就會(huì)使用臨時(shí)表來輔助排序。
?
優(yōu)化
未優(yōu)化（也就是分組列沒有索引）的 group by 的總過程可以概括為：因?yàn)閿?shù)據(jù)是無序的，所以需要?jiǎng)?chuàng)建臨時(shí)表，然后一個(gè)一個(gè)判斷屬于哪個(gè)分組，最后再根據(jù)分組列進(jìn)行排序。所以，優(yōu)化可以有兩個(gè)思路：
去掉排序
在明確返回的數(shù)據(jù)不需要排序的情況下，可以禁止排序，也就是將上面的語句改成 select a,count(*) from t group by a order by null。
順序排列
如果記錄都按照排序字段排序，那么數(shù)據(jù)就變成了下面的結(jié)構(gòu)：
這樣在實(shí)際獲取要返回的字段或計(jì)算聚合函數(shù)時(shí)，只需要按順序依次訪問，等到列值變成下一個(gè)就知道當(dāng)前組訪問結(jié)束，將之前統(tǒng)計(jì)的數(shù)據(jù)直接返回。這樣就避免了創(chuàng)建臨時(shí)表，同時(shí)排序也不需要使用 sort_buffer 進(jìn)行額外排序。這樣就極大地提高了執(zhí)行的效率。
實(shí)現(xiàn)
1、如果分組字段適合創(chuàng)建索引就直接為分組字段創(chuàng)建索引。
MySQL 5.7 版本支持了 generated column 機(jī)制，用來實(shí)現(xiàn)列數(shù)據(jù)的關(guān)聯(lián)更新。你可以用下面的方法創(chuàng)建一個(gè)列 z，然后在 z 列上創(chuàng)建一個(gè)索引（如果是 MySQL 5.6 及之前的版本，你也可以創(chuàng)建普通列和索引，來解決這個(gè)問題）
alter?table?t1?add?column?z?int?generated?always?as(id?%?100),?add?index(z);
然后解析：
這時(shí)沒有用到臨時(shí)表和額外排序，所以性能提升。
?
2、如果分組字段不適合（使用率很低），那么可以使用 SQL_BIG_RESULT 來嘗試優(yōu)化。
在 group by 語句中加入 SQL_BIG_RESULT 這個(gè)提示（hint），就可以告訴優(yōu)化器：這個(gè)語句涉及的數(shù)據(jù)量很大，請(qǐng)直接用磁盤臨時(shí)表。MySQL 的優(yōu)化器一看，磁盤臨時(shí)表是 B+ 樹存儲(chǔ)，存儲(chǔ)效率不如數(shù)組來得高。所以，既然使用SQL_BIG_RESULT來說明數(shù)據(jù)量很大，那從磁盤空間考慮，還是直接用數(shù)組來存吧。所以在使用 SQL_BIG_RESULT 后優(yōu)化器會(huì)使用數(shù)組結(jié)構(gòu)的磁盤臨時(shí)表。
但是如果在未達(dá)到磁盤臨時(shí)表的使用條件是不會(huì)使用磁盤臨時(shí)表的，也就是在 sort_buffer 空間能夠存儲(chǔ)要返回和排序的總字段長(zhǎng)度時(shí)，就使用數(shù)組結(jié)構(gòu)的 sort_buffer ，如果總字段超過 sort_buffer 大小，那么就再加上數(shù)組結(jié)構(gòu)的磁盤臨時(shí)表來幫助排序。
那么在 sort_buffer?空間足夠的情況下， sort_buffer?內(nèi)部就會(huì)對(duì)數(shù)據(jù)進(jìn)行排序，這樣也就起到了索引的作用，
還是以上面的例子來看，使用?SQL_BIG_RESULT
alter?table?t1?add?column?z?int?generated?always?as(id?%?100),?add?index(z);
具體過程如下：
1、初始化 sort_buffer，確定放入一個(gè)整型字段，記為 m；
2、掃描表 t1 的索引 a，依次取出里面的 id 值, 將 id%10 的值存入 sort_buffer 中；
3、掃描完成后，對(duì) sort_buffer 的字段 m 做排序（如果 sort_buffer 內(nèi)存不夠用，就會(huì)利用磁盤臨時(shí)文件輔助排序）；
4、排序完成后，就得到了一個(gè)有序數(shù)組。
解析：
可以看到此時(shí)就沒有使用臨時(shí)表了，而是直接使用 sort_buffer 進(jìn)行排序，這樣就省去了使用臨時(shí)表帶來的性能消耗。
?
總結(jié)
1、如果對(duì) group by 語句的結(jié)果沒有排序要求，要在語句后面加 order by null；那么一般情況就不需要使用臨時(shí)表了（上面兩個(gè)優(yōu)化都是在要求排序的前提下提出的優(yōu)化方式）
2、盡量讓 group by 過程用上表的索引，確認(rèn)方法是 explain 結(jié)果里沒有 Using temporary 和 Using filesort；
3、如果 group by 需要統(tǒng)計(jì)的數(shù)據(jù)量不大，盡量只使用內(nèi)存臨時(shí)表；也可以通過適當(dāng)調(diào)大 tmp_table_size 參數(shù)，來避免用到磁盤臨時(shí)表；
4、如果數(shù)據(jù)量實(shí)在太大，使用 SQL_BIG_RESULT 這個(gè)提示，來告訴優(yōu)化器直接使用排序算法得到 group by 的結(jié)果。
鋒哥最新SpringCloud分布式電商秒殺課程發(fā)布
??????
??長(zhǎng)按上方微信二維碼?2 秒
感謝點(diǎn)贊支持下哈?