看一遍就理解：group by詳解

前言

大家好，我是撿田螺的小男孩。

日常開(kāi)發(fā)中，我們經(jīng)常會(huì)使用到group by。親愛(ài)的小伙伴，你是否知道group by的工作原理呢？group by和having有什么區(qū)別呢？group by的優(yōu)化思路是怎樣的呢？使用group by有哪些需要注意的問(wèn)題呢？本文將跟大家一起來(lái)學(xué)習(xí)，攻克group by~

• 使用group by的簡(jiǎn)單例子
• group by 工作原理
• group by + where 和 having的區(qū)別
• group by 優(yōu)化思路
• group by 使用注意點(diǎn)
• 一個(gè)生產(chǎn)慢SQL如何優(yōu)化

1. 使用group by的簡(jiǎn)單例子

group by一般用于分組統(tǒng)計(jì)，它表達(dá)的邏輯就是根據(jù)一定的規(guī)則，進(jìn)行分組。我們先從一個(gè)簡(jiǎn)單的例子，一起來(lái)復(fù)習(xí)一下哈。

假設(shè)用一張員工表，表結(jié)構(gòu)如下：

CREATE?TABLE?`staff`?(
??`id`?bigint(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵id',
??`id_card`?varchar(20)?NOT?NULL?COMMENT?'身份證號(hào)碼',
??`name`?varchar(64)?NOT?NULL?COMMENT?'姓名',
??`age`?int(4)?NOT?NULL?COMMENT?'年齡',
??`city`?varchar(64)?NOT?NULL?COMMENT?'城市',
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?AUTO_INCREMENT=15?DEFAULT?CHARSET=utf8?COMMENT='員工表';

表存量的數(shù)據(jù)如下：

我們現(xiàn)在有這么一個(gè)需求：統(tǒng)計(jì)每個(gè)城市的員工數(shù)量。對(duì)應(yīng)的 SQL 語(yǔ)句就可以這么寫(xiě)：

select?city?,count(*)?as?num?from?staff?group?by?city;

執(zhí)行結(jié)果如下：

這條SQL語(yǔ)句的邏輯很清楚啦，但是它的底層執(zhí)行流程是怎樣的呢？

2. group by 原理分析

2.1 explain 分析

我們先用explain查看一下執(zhí)行計(jì)劃

explain?select?city?,count(*)?as?num?from?staff?group?by?city;

• Extra 這個(gè)字段的Using temporary表示在執(zhí)行分組的時(shí)候使用了臨時(shí)表
• Extra 這個(gè)字段的Using filesort表示使用了排序

group by 怎么就使用到臨時(shí)表和排序了呢？我們來(lái)看下這個(gè)SQL的執(zhí)行流程

2.2 group by 的簡(jiǎn)單執(zhí)行流程

explain?select?city?,count(*)?as?num?from?staff?group?by?city;

我們一起來(lái)看下這個(gè)SQL的執(zhí)行流程哈

1. 創(chuàng)建內(nèi)存臨時(shí)表，表里有兩個(gè)字段city和num；
2. 全表掃描staff的記錄，依次取出city = 'X'的記錄。

• 判斷臨時(shí)表中是否有為 city='X'的行，沒(méi)有就插入一個(gè)記錄 (X,1);
• 如果臨時(shí)表中有city='X'的行的行，就將x 這一行的num值加 1；

3. 遍歷完成后，再根據(jù)字段city做排序，得到結(jié)果集返回給客戶端。

這個(gè)流程的執(zhí)行圖如下：

臨時(shí)表的排序是怎樣的呢？

就是把需要排序的字段，放到sort buffer，排完就返回。在這里注意一點(diǎn)哈，排序分全字段排序和rowid排序

• 如果是全字段排序，需要查詢返回的字段，都放入sort buffer，根據(jù)排序字段排完，直接返回
• 如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次回表操作，再返回。
• 怎么確定走的是全字段排序還是rowid 排序排序呢？由一個(gè)數(shù)據(jù)庫(kù)參數(shù)控制的，max_length_for_sort_data

對(duì)排序有興趣深入了解的小伙伴，可以看我這篇文章哈。

• 看一遍就理解：order by詳解

3. where 和 having的區(qū)別

• group by + where 的執(zhí)行流程
• group by + having 的執(zhí)行流程
• 同時(shí)有where、group by 、having的執(zhí)行順序

3.1 group by + where 的執(zhí)行流程

有些小伙伴覺(jué)得上一小節(jié)的SQL太簡(jiǎn)單啦，如果加了where條件之后，并且where條件列加了索引呢，執(zhí)行流程是怎樣？

好的，我們給它加個(gè)條件，并且加個(gè)idx_age的索引，如下：

select?city?,count(*)?as?num?from?staff?where?age>?30?group?by?city;
//加索引
alter?table?staff?add?index?idx_age?(age);

再來(lái)expain分析一下：

explain?select?city?,count(*)?as?num?from?staff?where?age>?30?group?by?city;

從explain 執(zhí)行計(jì)劃結(jié)果，可以發(fā)現(xiàn)查詢條件命中了idx_age的索引，并且使用了臨時(shí)表和排序

Using index condition:表示索引下推優(yōu)化，根據(jù)索引盡可能的過(guò)濾數(shù)據(jù),然后再返回給服務(wù)器層根據(jù)where其他條件進(jìn)行過(guò)濾。這里單個(gè)索引為什么會(huì)出現(xiàn)索引下推呢？explain出現(xiàn)并不代表一定是使用了索引下推，只是代表可以使用，但是不一定用了。大家如果有想法或者有疑問(wèn)，可以加我微信討論哈。

執(zhí)行流程如下：

1. 創(chuàng)建內(nèi)存臨時(shí)表，表里有兩個(gè)字段city和num；
2. 掃描索引樹(shù)idx_age，找到大于年齡大于30的主鍵ID
3. 通過(guò)主鍵ID，回表找到city = 'X'

• 判斷臨時(shí)表中是否有為 city='X'的行，沒(méi)有就插入一個(gè)記錄 (X,1);
• 如果臨時(shí)表中有city='X'的行的行，就將x 這一行的num值加 1；

4. 繼續(xù)重復(fù)2,3步驟，找到所有滿足條件的數(shù)據(jù)，
5. 最后根據(jù)字段city做排序，得到結(jié)果集返回給客戶端。

3.2 group by + having 的執(zhí)行

如果你要查詢每個(gè)城市的員工數(shù)量，獲取到員工數(shù)量不低于3的城市，having可以很好解決你的問(wèn)題，SQL醬紫寫(xiě)：

select?city?,count(*)?as?num?from?staff??group?by?city?having?num?>=?3;

查詢結(jié)果如下：

having稱為分組過(guò)濾條件，它對(duì)返回的結(jié)果集操作。

3.3 同時(shí)有where、group by 、having的執(zhí)行順序

如果一個(gè)SQL同時(shí)含有where、group by、having子句，執(zhí)行順序是怎樣的呢。

比如這個(gè)SQL：

select?city?,count(*)?as?num?from?staff??where?age>?19?group?by?city?having?num?>=?3;

1. 執(zhí)行where子句查找符合年齡大于19的員工數(shù)據(jù)
2. group by子句對(duì)員工數(shù)據(jù)，根據(jù)城市分組。
3. 對(duì)group by子句形成的城市組，運(yùn)行聚集函數(shù)計(jì)算每一組的員工數(shù)量值；
4. 最后用having子句選出員工數(shù)量大于等于3的城市組。

3.4 where + having 區(qū)別總結(jié)

• having子句用于分組后篩選，where子句用于行條件篩選
• having一般都是配合group by 和聚合函數(shù)一起出現(xiàn)如(count(),sum(),avg(),max(),min())
• where條件子句中不能使用聚集函數(shù)，而having子句就可以。
• having只能用在group by之后，where執(zhí)行在group by之前

4. 使用 group by 注意的問(wèn)題

使用group by 主要有這幾點(diǎn)需要注意：

• group by一定要配合聚合函數(shù)一起使用嘛？
• group by的字段一定要出現(xiàn)在select中嘛
• group by導(dǎo)致的慢SQL問(wèn)題

4.1 group by一定要配合聚合函數(shù)使用嘛？

group by 就是分組統(tǒng)計(jì)的意思，一般情況都是配合聚合函數(shù)如（count(),sum(),avg(),max(),min())一起使用。

• count() 數(shù)量
• sum() 總和
• avg() 平均
• max() 最大值
• min() 最小值

如果沒(méi)有配合聚合函數(shù)使用可以嗎？

我用的是Mysql 5.7 ，是可以的。不會(huì)報(bào)錯(cuò)，并且返回的是，分組的第一行數(shù)據(jù)。

比如這個(gè)SQL：

select?city,id_card,age?from?staff?group?by??city;

查詢結(jié)果是

大家對(duì)比看下，返回的就是每個(gè)分組的第一條數(shù)據(jù)

當(dāng)然，平時(shí)大家使用的時(shí)候，group by還是配合聚合函數(shù)使用的，除非一些特殊場(chǎng)景，比如你想去重，當(dāng)然去重用distinct也是可以的。

4.2 group by 后面跟的字段一定要出現(xiàn)在select中嘛。

不一定，比如以下SQL：

select?max(age)??from?staff?group?by?city;

執(zhí)行結(jié)果如下：

分組字段city不在select 后面，并不會(huì)報(bào)錯(cuò)。當(dāng)然，這個(gè)可能跟不同的數(shù)據(jù)庫(kù)，不同的版本有關(guān)吧。大家使用的時(shí)候，可以先驗(yàn)證一下就好。有一句話叫做，紙上得來(lái)終覺(jué)淺，絕知此事要躬行。

4.3 group by導(dǎo)致的慢SQL問(wèn)題

到了最重要的一個(gè)注意問(wèn)題啦，group by使用不當(dāng)，很容易就會(huì)產(chǎn)生慢SQL 問(wèn)題。因?yàn)樗扔玫?strong>臨時(shí)表，又默認(rèn)用到排序。有時(shí)候還可能用到磁盤(pán)臨時(shí)表。

• 如果執(zhí)行過(guò)程中，會(huì)發(fā)現(xiàn)內(nèi)存臨時(shí)表大小到達(dá)了上限（控制這個(gè)上限的參數(shù)就是tmp_table_size），會(huì)把內(nèi)存臨時(shí)表轉(zhuǎn)成磁盤(pán)臨時(shí)表。
• 如果數(shù)據(jù)量很大，很可能這個(gè)查詢需要的磁盤(pán)臨時(shí)表，就會(huì)占用大量的磁盤(pán)空間。

這些都是導(dǎo)致慢SQL的x因素，我們一起來(lái)探討優(yōu)化方案哈。

5. group by的一些優(yōu)化方案

從哪些方向去優(yōu)化呢？

• 方向1：既然它默認(rèn)會(huì)排序，我們不給它排是不是就行啦。
• 方向2：既然臨時(shí)表是影響group by性能的X因素，我們是不是可以不用臨時(shí)表？

我們一起來(lái)想下，執(zhí)行group by語(yǔ)句為什么需要臨時(shí)表呢？group by的語(yǔ)義邏輯，就是統(tǒng)計(jì)不同的值出現(xiàn)的個(gè)數(shù)。如果這個(gè)這些值一開(kāi)始就是有序的，我們是不是直接往下掃描統(tǒng)計(jì)就好了，就不用臨時(shí)表來(lái)記錄并統(tǒng)計(jì)結(jié)果啦?

• group by 后面的字段加索引
• order by null 不用排序
• 盡量只使用內(nèi)存臨時(shí)表
• 使用SQL_BIG_RESULT

5.1 group by 后面的字段加索引

如何保證group by后面的字段數(shù)值一開(kāi)始就是有序的呢？當(dāng)然就是加索引啦。

我們回到一下這個(gè)SQL

select?city?,count(*)?as?num?from?staff?where?age=?19?group?by?city;

它的執(zhí)行計(jì)劃

如果我們給它加個(gè)聯(lián)合索引idx_age_city（age,city）

alter?table?staff?add?index?idx_age_city(age,city);

再去看執(zhí)行計(jì)劃，發(fā)現(xiàn)既不用排序，也不需要臨時(shí)表啦。

加合適的索引是優(yōu)化group by最簡(jiǎn)單有效的優(yōu)化方式。

5.2 order by null 不用排序

并不是所有場(chǎng)景都適合加索引的，如果碰上不適合創(chuàng)建索引的場(chǎng)景，我們?nèi)绾蝺?yōu)化呢？

如果你的需求并不需要對(duì)結(jié)果集進(jìn)行排序，可以使用order by null。

select?city?,count(*)?as?num?from?staff?group?by?city?order?by?null

執(zhí)行計(jì)劃如下，已經(jīng)沒(méi)有filesort啦

5.3 盡量只使用內(nèi)存臨時(shí)表

如果group by需要統(tǒng)計(jì)的數(shù)據(jù)不多，我們可以盡量只使用內(nèi)存臨時(shí)表；因?yàn)槿绻鹓roup by 的過(guò)程因?yàn)閮?nèi)存臨時(shí)表放不下數(shù)據(jù)，從而用到磁盤(pán)臨時(shí)表的話，是比較耗時(shí)的。因此可以適當(dāng)調(diào)大tmp_table_size參數(shù)，來(lái)避免用到磁盤(pán)臨時(shí)表。

5.4 使用SQL_BIG_RESULT優(yōu)化

如果數(shù)據(jù)量實(shí)在太大怎么辦呢？總不能無(wú)限調(diào)大tmp_table_size吧？但也不能眼睜睜看著數(shù)據(jù)先放到內(nèi)存臨時(shí)表，隨著數(shù)據(jù)插入發(fā)現(xiàn)到達(dá)上限，再轉(zhuǎn)成磁盤(pán)臨時(shí)表吧？這樣就有點(diǎn)不智能啦。

因此，如果預(yù)估數(shù)據(jù)量比較大，我們使用SQL_BIG_RESULT 這個(gè)提示直接用磁盤(pán)臨時(shí)表。MySQl優(yōu)化器發(fā)現(xiàn)，磁盤(pán)臨時(shí)表是B+樹(shù)存儲(chǔ)，存儲(chǔ)效率不如數(shù)組來(lái)得高。因此會(huì)直接用數(shù)組來(lái)存

示例SQl如下：

select?SQL_BIG_RESULT?city?,count(*)?as?num?from?staff?group?by?city;

執(zhí)行計(jì)劃的Extra字段可以看到，執(zhí)行沒(méi)有再使用臨時(shí)表，而是只有排序

執(zhí)行流程如下：

1. 初始化 sort_buffer，放入city字段；
2. 掃描表staff，依次取出city的值,存入 sort_buffer 中；
3. 掃描完成后，對(duì) sort_buffer的city字段做排序
4. 排序完成后，就得到了一個(gè)有序數(shù)組。
5. 根據(jù)有序數(shù)組，統(tǒng)計(jì)每個(gè)值出現(xiàn)的次數(shù)。

6. 一個(gè)生產(chǎn)慢SQL如何優(yōu)化

最近遇到個(gè)生產(chǎn)慢SQL，跟group by相關(guān)的，給大家看下怎么優(yōu)化哈。

表結(jié)構(gòu)如下：

CREATE?TABLE?`staff`?(
??`id`?bigint(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'主鍵id',
??`id_card`?varchar(20)?NOT?NULL?COMMENT?'身份證號(hào)碼',
??`name`?varchar(64)?NOT?NULL?COMMENT?'姓名',
??`status`?varchar(64)?NOT?NULL?COMMENT?'Y-已激活?I-初始化?D-已刪除?R-審核中',
??`age`?int(4)?NOT?NULL?COMMENT?'年齡',
??`city`?varchar(64)?NOT?NULL?COMMENT?'城市',
??`enterprise_no`?varchar(64)?NOT?NULL?COMMENT?'企業(yè)號(hào)',
??`legal_cert_no`?varchar(64)?NOT?NULL?COMMENT?'法人號(hào)碼',
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?AUTO_INCREMENT=15?DEFAULT?CHARSET=utf8?COMMENT='員工表';

查詢的SQL是這樣的：

select?*?from?t1?where?status?=?#{status}?group?by?#{legal_cert_no}

我們先不去探討這個(gè)SQL的=是否合理。如果就是這么個(gè)SQL，你會(huì)怎么優(yōu)化呢？有想法的小伙伴可以留言討論哈，也可以加我微信加群探討。如果你覺(jué)得文章那里寫(xiě)得不對(duì)，也可以提出來(lái)哈，一起進(jìn)步，加油呀

參考與感謝

• mySQL 45講 (https://time.geekbang.org/column/article/80477?cid=100020801)

推薦閱讀：

阿里、京東、美團(tuán)面試回來(lái)，這些面試題你會(huì)嗎？

更快的Maven來(lái)了，我的天，速度提升了8倍！

美團(tuán)面試：如何設(shè)計(jì)一個(gè)注冊(cè)中心？

常見(jiàn)消息中間件大 PK

如出一轍。。。

歡迎關(guān)注微信公眾號(hào)：互聯(lián)網(wǎng)全棧架構(gòu)，收取更多有價(jià)值的信息。