阿里二面：group by 怎么優(yōu)化？
01 前言
哈嘍，我是狗哥，好久不見(jiàn)呀！是的，我又又換了工作。最近一直在面試這幾天剛好整理下在面試中被問(wèn)到有意思的問(wèn)題，也借此機(jī)會(huì)跟大家分享下。
這家企業(yè)的面試官有點(diǎn)意思，一面是個(gè)同齡小哥，一起聊了兩個(gè)小時(shí)（聊到我嘴都干了）。二面是個(gè)從阿里出來(lái)的架構(gòu)師，視頻面試，我做完自我介紹之后，他一開(kāi)場(chǎng)就問(wèn)我：
對(duì) MySQL 熟悉嗎？
我一愣，隨之意識(shí)到這是個(gè)坑。他肯定想問(wèn)我某方面的原理了，恰好我研究過(guò)索引。就回答：
對(duì)索引比較熟悉。
他：
group by 是怎么實(shí)現(xiàn)分組的？
還好我又復(fù)習(xí)，基本上 group by 用法、工作原理、怎么優(yōu)化之類(lèi)的都答到點(diǎn)子上。今天也跟大家盤(pán)一盤(pán) group by，我將從原理講到最終優(yōu)化，給大家聊聊 group by，希望對(duì)你有所幫助。
國(guó)際慣例，先上思維導(dǎo)圖。
02 一個(gè)簡(jiǎn)單的例子
還是借我們之前講 order by 時(shí)創(chuàng)建的商品訂單表來(lái)演示。建表語(yǔ)句：
CREATE?TABLE?`sale_order`?(
??`id`?bigint(20)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵',
??`user_code`?varchar(64)?NOT?NULL?COMMENT?'用戶(hù)編號(hào)',
??`goods_name`?varchar(64)?NOT?NULL?COMMENT?'商品名稱(chēng)',
??`order_date`?timestamp?NULL?DEFAULT?CURRENT_TIMESTAMP?COMMENT?'下單時(shí)間',
??`city`?varchar(64)?DEFAULT?NULL?COMMENT?'下單城市',
??`order_num`?int(10)?NOT?NULL?COMMENT?'訂單數(shù)量',
??PRIMARY?KEY?(`id`)?USING?BTREE
)?ENGINE=InnoDB?AUTO_INCREMENT=10001?DEFAULT?CHARSET=utf8?ROW_FORMAT=COMPACT?COMMENT='商品訂單表';
數(shù)據(jù)如下，我之前就導(dǎo)入準(zhǔn)備好的：
數(shù)據(jù)準(zhǔn)備好了，需求也來(lái)了。現(xiàn)在產(chǎn)品要求統(tǒng)計(jì)表中每個(gè)城市的下單人數(shù)，這個(gè)需求是不是很簡(jiǎn)單？sql 語(yǔ)句我們也可以很快給出：
select?city,?count(*)?as?num?from?sale_order?group?by?city;
這條 sql 的結(jié)果也很快就出來(lái)了：
sql 我們會(huì)寫(xiě)，結(jié)果也很快就出來(lái)了。但是原理你知道么？執(zhí)行流程又是怎樣的呢？
03 group by 的原理
3.1 explain 分析
廢話(huà)不多說(shuō)，遇事不決 explain。想要知道 sql 的性能咋樣，怎么執(zhí)行的，都要用 explain 分析。想要知道 explain 的每個(gè)指標(biāo)代表啥意思，可以看我之前的文章：《explain 很重要嗎？》
注意到最后一列 Extra ，這列代表的是?sql 執(zhí)行過(guò)程中會(huì)做什么？上圖中這列有兩個(gè)值，一個(gè)是 Using temporary，一個(gè)是 Using filesort。
Using temporary：代表需要用到臨時(shí)表。OS：這是個(gè)啥？？？ Using filesort：需要排序。OS：挖草，還需要排序？？？
要想搞明白為什么需要臨時(shí)表和排序，我們就得分析 group by 的執(zhí)行流程了。
3.2 執(zhí)行流程
根據(jù) explain 分析，我們知道執(zhí)行過(guò)程中肯定有創(chuàng)建臨時(shí)表和排序兩個(gè)步驟，下面來(lái)分析一下：
創(chuàng)建內(nèi)存臨時(shí)表，表里面有兩個(gè)字段：city 和 num； 全表掃描 sale_order 表，取出 city = 某城市（比如廣州、深圳、上海，囊括你表里涉及到的城市）的記錄 臨時(shí)表沒(méi)有 city = 某城市的記錄，直接插入，并記為 (某城市，1)； 臨時(shí)表里有 city = 某城市的記錄，直接更新，把 num 值 +1 重復(fù)步驟 2 直至遍歷完成，根據(jù) city 字段做排序，然后把結(jié)果集返回客戶(hù)端。
至此整個(gè)過(guò)程就完事了。我知道這樣不直觀(guān)，所以我又畫(huà)個(gè)圖，方便你們理解：
圖中最后一步，對(duì)內(nèi)存臨時(shí)表的排序，具體的細(xì)節(jié)在之前的?《order by 是怎么排序的？》一文章中已經(jīng)有過(guò)介紹，歡迎點(diǎn)擊跳轉(zhuǎn)。同樣是非常細(xì)節(jié)的一個(gè) mysql 關(guān)鍵字，強(qiáng)烈推薦你去看下。
04 group by 中使用 where & having
寫(xiě)到這里，有小伙伴就說(shuō)了。狗哥你這里描述的只是 group by 的單獨(dú)執(zhí)行過(guò)程，很簡(jiǎn)單呀。我也會(huì)，如果加上 where 或者 having 或者兩者都加上的時(shí)候的執(zhí)行過(guò)程是怎樣的呢？
4.1 group by + where
現(xiàn)在產(chǎn)品又改需求統(tǒng)計(jì)每個(gè)城市下的下單人數(shù)，且下的訂單量要大于 2。OS：mmp，又改
按照慣例，看到 where 我們一般想到怎么優(yōu)化？沒(méi)錯(cuò)，加索引嘛。
加索引：
alter?table?sale_order?add?index?idx_order_num?(order_num);
最終語(yǔ)句：
select?city,?count(*)?as?num?from?sale_order?where?order_num?>?2?group?by?city;
結(jié)果：
explain 分析：
從上圖得知，加上索引之后。這條語(yǔ)句命中了索引 idx_order_number，并且此時(shí)的 Extra 多了 Using index Condition 的執(zhí)行計(jì)劃。type 變成了 range 說(shuō)明不用全表掃描。
解釋下 Using index Condition：會(huì)先條件過(guò)濾索引，過(guò)濾完索引后找到所有符合索引條件的數(shù)據(jù)行，常見(jiàn)于 where 中有 between > < 等條件的 sql 語(yǔ)句。
它的出現(xiàn)說(shuō)明這個(gè)語(yǔ)句先走索引過(guò)濾掉不符合 where 條件的數(shù)據(jù)，再去統(tǒng)計(jì)，然后排序，最后返回客戶(hù)端。流程如下：
創(chuàng)建內(nèi)存臨時(shí)表，表里面有兩個(gè)字段：city 和 num； 根據(jù)索引 idx_order_num 找到大于 2 的數(shù)據(jù)的主鍵 ID； 通過(guò)主鍵 ID 取出 city = 某城市（比如廣州、深圳、上海，囊括你表里涉及到的城市）的記錄； 臨時(shí)表沒(méi)有 city = 某城市的記錄，直接插入，并記為 (某城市，1)； 臨時(shí)表里有 city = 某城市的記錄，直接更新，把 num 值 +1。 重復(fù) 2、3 步驟，直至找到所有嗎，滿(mǎn)足 order_num > 2 的記錄。根據(jù) city 字段做排序，然后把結(jié)果集返回客戶(hù)端。
PS：回表的概念我就不說(shuō)了哈，有興趣的可以看我之前的《MySQL 索引詳解》文章，強(qiáng)烈建議你去看，非常重要的是概念。
4.2 group by + having
現(xiàn)在產(chǎn)品又改需求統(tǒng)計(jì)每個(gè)城市的下單的人數(shù)，且總的下單人數(shù)需要在 100 以上。OS：mmp，又改
根據(jù)需求很快寫(xiě)出 sql 語(yǔ)句：
select?city,?count(*)?as?num?from?sale_order?group?by?city?having?num?>?100;
再用 explain 分析一下，得出如下結(jié)果：
哇草，咋回事？跟沒(méi)加 having 的執(zhí)行流程一樣的？你沒(méi)看錯(cuò)，其實(shí) having 不直接參與到執(zhí)行計(jì)劃中去，它是對(duì)結(jié)果集操作的，所以這里的加的 having 跟沒(méi)加是一樣的執(zhí)行計(jì)劃。畫(huà)個(gè)圖，大概就是這樣的：
4.3 group by + where + having
現(xiàn)在產(chǎn)品又改需求統(tǒng)計(jì)每個(gè)城市的下單超過(guò)兩單的人數(shù)，且總的人數(shù)需要在 100 以上。OS：mmmp，又改
按照慣例，我們給 where 條件加上索引：
alter?table?sale_order?add?index?idx_order_num?(order_num);
根據(jù)需求很快寫(xiě)出 sql 語(yǔ)句：
select?city,?count(*)?as?num?from?sale_order?where?order_nunm?>?2?group?by?city?having?num?>?100;
explain 結(jié)果：
執(zhí)行流程：
創(chuàng)建內(nèi)存臨時(shí)表，表里面有兩個(gè)字段：city 和 num； 根據(jù)索引 idx_order_num 找到大于 2 的數(shù)據(jù)的主鍵 ID； 通過(guò)主鍵 ID 取出 city = 某城市（比如廣州、深圳、上海，囊括你表里涉及到的城市）的記錄； 臨時(shí)表沒(méi)有 city = 某城市的記錄，直接插入，并記為 (某城市，1)； 臨時(shí)表里有 city = 某城市的記錄，直接更新，把 num 值 +1。 重復(fù) 2、3 步驟，直至找到所有嗎，滿(mǎn)足 order_num > 2 的記錄。根據(jù) city 字段做排序。 having 對(duì)結(jié)果集進(jìn)行過(guò)濾，并返回客戶(hù)端
不難看出這里的執(zhí)行流程跟 4.1 一樣就多了個(gè) having 過(guò)濾
05 group by 優(yōu)化
根據(jù)上面的分析，我們知道 group by 是需要?jiǎng)?chuàng)建臨時(shí)表并且排序的。耗時(shí)也應(yīng)該在這兩個(gè)步驟，那我們應(yīng)該從這兩個(gè)步驟入手優(yōu)化。
如果分組字段本身就是有序的，我們是不是就不用排序了？或者我們的需求并沒(méi)有要求排序是不是就可以?xún)?yōu)化了？如果必須使用臨時(shí)表，我們是不是可以只用內(nèi)存臨時(shí)表呢？如果數(shù)據(jù)量實(shí)在是太大，是不是可以直接用磁盤(pán)臨時(shí)表，而不是發(fā)現(xiàn)內(nèi)存臨時(shí)表不夠大才用它呢？
以上可以總結(jié)出四個(gè)優(yōu)化方案：
分組字段加索引 order by null 不排序 盡量使用內(nèi)存臨時(shí)表 SQL_BIG_RESULT
5.1 分組字段加索引
select?city,?count(*)?as?num?from?sale_order?group?by?city;
上面的 sql 中，city 沒(méi)加索引，所以這時(shí)的 group by 還是要使用臨時(shí)表的。那我們可不可以個(gè)組合索引 idx_city，結(jié)果如下所示：
加索引：
alter?table?sale_order?add?index?idx_city?(city);
結(jié)果：
Extra 是不是 Using temporary 和 Using filesort 都沒(méi)了？所以不用排序也不用臨時(shí)表啦。那有小伙伴又問(wèn)了，那我有 where 條件怎么辦？那就加組合索引唄：
alter?table?sale_order?add?index?idx_order_num_city(order_num,city);
但是這種情況只適用于 where 條件是等值的，如果有大于、小于的情況還是避免不了排序和使用臨時(shí)表。適用情況：
select?city,?count(*)?as?num?from?sale_order?where?order_num?=?2?group?by?city;
不適用情況：
select?city,?count(*)?as?num?from?sale_order?where?order_num?>?2?group?by?city;
5.2 order by null 避免排序
如果需求是不用排序，我們就可以這樣做。在 sql 末尾加上 order by null
select?city,?count(*)?as?num?from?sale_order?where?order_num?>?2?group?by?city?order?by?null;
從分析結(jié)果看，還是需要使用臨時(shí)表的。
5.3 盡量使用內(nèi)存臨時(shí)表
有些小伙伴可能很懵哈，內(nèi)存臨時(shí)表是啥？其實(shí) mysql 臨時(shí)表分內(nèi)存臨時(shí)表和磁盤(pán)臨時(shí)表。但是這里就不展開(kāi)了，有時(shí)間專(zhuān)門(mén)寫(xiě)一篇文章介紹。
group by 在執(zhí)行過(guò)程中使用內(nèi)存臨時(shí)表還是不夠用，那就會(huì)使用磁盤(pán)臨時(shí)表。內(nèi)存臨時(shí)表的大小是有限制的，mysql 中 tmp_table_size 代表的就是內(nèi)存臨時(shí)表的大小，默認(rèn)是 16M。當(dāng)然你可以自定義社會(huì)中適當(dāng)大一點(diǎn)，這就要根據(jù)實(shí)際情況來(lái)定了。
比如：可以設(shè)置成 32M，也就是 33554432 字節(jié)。
set?tmp_table_size=33554432;
5.4 SQL_BIG_RESULT
如果數(shù)據(jù)量實(shí)在過(guò)大，大到內(nèi)存臨時(shí)表都不夠用了，這時(shí)就轉(zhuǎn)向使用磁盤(pán)臨時(shí)表。而發(fā)現(xiàn)不夠用再轉(zhuǎn)向這個(gè)過(guò)程也是很耗時(shí)的，那我們有沒(méi)有一種方法，可以告訴 mysql 從一開(kāi)始就使用 磁盤(pán)臨時(shí)表呢？
有的，在 group by 語(yǔ)句中加入 SQL_BIG_RESULT 提示 MySQL 優(yōu)化器直接用磁盤(pán)臨時(shí)表。優(yōu)化器分析，磁盤(pán)臨時(shí)表是 B+ 樹(shù)存儲(chǔ)，存儲(chǔ)效率不如數(shù)組來(lái)得高。所以直接用數(shù)組存儲(chǔ)。用法如下：
select?SQL_BIG_RESULT?city,?count(*)?as?num?from?sale_order?where?group?by?city;
此時(shí)的執(zhí)行過(guò)程就不需要?jiǎng)?chuàng)建臨時(shí)表啦：
初始化 sort_buffer（排序緩沖區(qū)），放入 city 字段； 掃描 sale_order 表，取出 city 的值存入 sort_buffer 中； 掃描完成后，對(duì) sort_buffer 的字段 city 做排序（如果 sort_buffer 內(nèi)存不夠用，就會(huì)利用磁盤(pán)臨時(shí)文件輔助排序）； 排序完成后，就得到了一個(gè)有序數(shù)組。 根據(jù)有序數(shù)組，得到數(shù)組里面的不同值，以及每個(gè)值的出現(xiàn)次數(shù)
06 group by 面試題
6.1 group by 一定要配合聚合函數(shù)使用嗎？
不一定，以下 sql 語(yǔ)句，我用的 MySQL 5.7.13 運(yùn)行是報(bào)錯(cuò)的；但是我司的 MySQL 8.0 版本是沒(méi)有問(wèn)題的。
select?goods_name,?city?from?sale_order?group?by?city;
出現(xiàn)這個(gè)錯(cuò)誤的原因是 mysql 的 sql_mode 開(kāi)啟了 ONLY_FULL_GROUP_BY 模式。查看 sql_mode：
select?@@GLOBAL.sql_mode;
如果想要不做限制的話(huà)，直接重新設(shè)置 sql_mode 的值，把 ONLY_FULL_GROUP_BY 去掉即可。當(dāng)然，開(kāi)啟這個(gè)要慎重，有可能會(huì)造成一些意想不到的錯(cuò)誤，一般情況下還是加上這個(gè)設(shè)置比較穩(wěn)妥。
6.2 group by 后面的一定要出現(xiàn)在 select 中嗎？
不一定，我的就沒(méi)報(bào)錯(cuò)。當(dāng)然，這個(gè)還跟版本有關(guān)系。大家可以回去自己實(shí)踐下。
select?max(order_num)?from?sale_order?group?by?city;
6.1 where & having 的區(qū)別？
where 用于條件篩選，having 用于分組后篩選 where 條件后面不能跟聚合函數(shù)，having 一般配合 group by 或者聚合函數(shù)（min、max、avg、count、sum）使用 where 用在 group by 之前，having 用在 group by 之后
07 參考鏈接
https://time.geekbang.org/column/article/80477?cid=100020801 https://www.cnblogs.com/perfei/p/14677933.html https://cloud.tencent.com/developer/article/1941787 https://blog.csdn.net/ryan007liu/article/details/91441479 https://www.cnblogs.com/muhy/p/10558849.html
08?總結(jié)
本文我們聊了 group by 的基本和進(jìn)階用法，還用 explain 分析了不同 group by 的執(zhí)行流程；從上面的分析中知道了 group by 的性能瓶頸是使用臨時(shí)表和排序，從這兩個(gè)方面提出了分組字段加索引、order by null、盡量使用內(nèi)存臨時(shí)表以及使用 SQL_BIG_RESULT 優(yōu)化等 4 個(gè)優(yōu)化方案，最后還聊了下 group by 常見(jiàn)的面試題。
完
往期推薦
巨坑！這公司的行為，挺適合清明節(jié)！
美國(guó)國(guó)家安全局是如何入侵你的電腦的?
我滴個(gè)乖乖，我復(fù)現(xiàn)了Spring的漏洞，害怕！
有道無(wú)術(shù)，術(shù)可成；有術(shù)無(wú)道，止于術(shù)
歡迎大家關(guān)注Java之道公眾號(hào)
好文章，我在看??