看一遍就理解：Group By詳解
前言
大家好，我是撿田螺的小男孩。
日常開發(fā)中，我們經常會使用到group by。親愛的小伙伴，你是否知道group by的工作原理呢?group by和having有什么區(qū)別呢?group by的優(yōu)化思路是怎樣的呢?使用group by有哪些需要注意的問題呢?本文將跟大家一起來學習，攻克group by~
- 使用group by的簡單例子
- group by 工作原理
- group by + where 和 having的區(qū)別
- group by 優(yōu)化思路
- group by 使用注意點
- 一個生產慢SQL如何優(yōu)化
1. 使用group by的簡單例子
group by一般用于分組統(tǒng)計，它表達的邏輯就是根據一定的規(guī)則，進行分組。我們先從一個簡單的例子，一起來復習一下哈。
假設用一張員工表，表結構如下：
- CREATE TABLE `staff` (
- `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
- `id_card` varchar(20) NOT NULL COMMENT '身份證號碼',
- `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='員工表';
表存量的數據如下：
我們現(xiàn)在有這么一個需求：統(tǒng)計每個城市的員工數量。對應的 SQL 語句就可以這么寫：
- select city ,count(*) as num from staff group by city;
執(zhí)行結果如下：
這條SQL語句的邏輯很清楚啦，但是它的底層執(zhí)行流程是怎樣的呢?
2. group by 原理分析
2.1 explain 分析
我們先用explain查看一下執(zhí)行計劃
- explain select city ,count(*) as num from staff group by city;
Extra 這個字段的Using temporary表示在執(zhí)行分組的時候使用了臨時表
Extra 這個字段的Using filesort表示使用了排序
group by 怎么就使用到臨時表和排序了呢?我們來看下這個SQL的執(zhí)行流程
2.2 group by 的簡單執(zhí)行流程
- explain select city ,count(*) as num from staff group by city;
我們一起來看下這個SQL的執(zhí)行流程哈
- 創(chuàng)建內存臨時表，表里有兩個字段city和num;
- 全表掃描staff的記錄，依次取出city = 'X'的記錄。
- 判斷臨時表中是否有為 city='X'的行，沒有就插入一個記錄 (X,1);
- 如果臨時表中有city='X'的行的行，就將x 這一行的num值加 1;
遍歷完成后，再根據字段city做排序，得到結果集返回給客戶端。
這個流程的執(zhí)行圖如下：
臨時表的排序是怎樣的呢?
就是把需要排序的字段，放到sort buffer，排完就返回。在這里注意一點哈，排序分全字段排序和rowid排序
- 如果是全字段排序，需要查詢返回的字段，都放入sort buffer，根據排序字段排完，直接返回
- 如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次回表操作，再返回。
- 怎么確定走的是全字段排序還是rowid 排序排序呢?由一個數據庫參數控制的，max_length_for_sort_data
對排序有興趣深入了解的小伙伴，可以看我這篇文章哈。
看一遍就理解：order by詳解
3. where 和 having的區(qū)別
- group by + where 的執(zhí)行流程
- group by + having 的執(zhí)行流程
- 同時有where、group by 、having的執(zhí)行順序
3.1 group by + where 的執(zhí)行流程
有些小伙伴覺得上一小節(jié)的SQL太簡單啦，如果加了where條件之后，并且where條件列加了索引呢，執(zhí)行流程是怎樣?
好的，我們給它加個條件，并且加個idx_age的索引，如下：
- select city ,count(*) as num from staff where age> 30 group by city;
- //加索引
- alter table staff add index idx_age (age);
再來expain分析一下：
- explain select city ,count(*) as num from staff where age> 30 group by city;
從explain 執(zhí)行計劃結果，可以發(fā)現(xiàn)查詢條件命中了idx_age的索引，并且使用了臨時表和排序
Using index condition:表示索引下推優(yōu)化，根據索引盡可能的過濾數據,然后再返回給服務器層根據where其他條件進行過濾。這里單個索引為什么會出現(xiàn)索引下推呢?explain出現(xiàn)并不代表一定是使用了索引下推，只是代表可以使用，但是不一定用了。大家如果有想法或者有疑問，可以加我微信討論哈。
執(zhí)行流程如下：
- 創(chuàng)建內存臨時表，表里有兩個字段city和num;
- 掃描索引樹idx_age，找到大于年齡大于30的主鍵ID
- 通過主鍵ID，回表找到city = 'X'
- 判斷臨時表中是否有為 city='X'的行，沒有就插入一個記錄 (X,1);
- 如果臨時表中有city='X'的行的行，就將x 這一行的num值加 1;
- 繼續(xù)重復2,3步驟，找到所有滿足條件的數據，
- 最后根據字段city做排序，得到結果集返回給客戶端。
3.2 group by + having 的執(zhí)行
如果你要查詢每個城市的員工數量，獲取到員工數量不低于3的城市，having可以很好解決你的問題，SQL醬紫寫：
- select city ,count(*) as num from staff group by city having num >= 3;
查詢結果如下：
having稱為分組過濾條件，它對返回的結果集操作。
3.3 同時有where、group by 、having的執(zhí)行順序
如果一個SQL同時含有where、group by、having子句，執(zhí)行順序是怎樣的呢。
比如這個SQL：
- select city ,count(*) as num from staff where age> 19 group by city having num >= 3;
- 執(zhí)行where子句查找符合年齡大于19的員工數據
- group by子句對員工數據，根據城市分組。
- 對group by子句形成的城市組，運行聚集函數計算每一組的員工數量值;
- 最后用having子句選出員工數量大于等于3的城市組。
3.4 where + having 區(qū)別總結
- having子句用于分組后篩選，where子句用于行條件篩選
- having一般都是配合group by 和聚合函數一起出現(xiàn)如(count(),sum(),avg(),max(),min())
- where條件子句中不能使用聚集函數，而having子句就可以。
- having只能用在group by之后，where執(zhí)行在group by之前
4. 使用 group by 注意的問題
使用group by 主要有這幾點需要注意：
- group by一定要配合聚合函數一起使用嘛?
- group by的字段一定要出現(xiàn)在select中嘛
- group by導致的慢SQL問題
4.1 group by一定要配合聚合函數使用嘛?
group by 就是分組統(tǒng)計的意思，一般情況都是配合聚合函數如(count(),sum(),avg(),max(),min())一起使用。
- count() 數量
- sum() 總和
- avg() 平均
- max() 最大值
- min() 最小值
如果沒有配合聚合函數使用可以嗎?
我用的是Mysql 5.7 ，是可以的。不會報錯，并且返回的是，分組的第一行數據。
比如這個SQL：
- select city,id_card,age from staff group by city;
查詢結果是
大家對比看下，返回的就是每個分組的第一條數據
當然，平時大家使用的時候，group by還是配合聚合函數使用的，除非一些特殊場景，比如你想去重，當然去重用distinct也是可以的。
4.2 group by 后面跟的字段一定要出現(xiàn)在select中嘛。
不一定，比如以下SQL：
- select max(age) from staff group by city;
執(zhí)行結果如下：
分組字段city不在select 后面，并不會報錯。當然，這個可能跟不同的數據庫，不同的版本有關吧。大家使用的時候，可以先驗證一下就好。有一句話叫做，紙上得來終覺淺，絕知此事要躬行。
4.3 group by導致的慢SQL問題
到了最重要的一個注意問題啦，group by使用不當，很容易就會產生慢SQL 問題。因為它既用到臨時表，又默認用到排序。有時候還可能用到磁盤臨時表。
- 如果執(zhí)行過程中，會發(fā)現(xiàn)內存臨時表大小到達了上限(控制這個上限的參數就是tmp_table_size)，會把內存臨時表轉成磁盤臨時表。
- 如果數據量很大，很可能這個查詢需要的磁盤臨時表，就會占用大量的磁盤空間。
這些都是導致慢SQL的x因素，我們一起來探討優(yōu)化方案哈。
5. group by的一些優(yōu)化方案
從哪些方向去優(yōu)化呢?
- 方向1：既然它默認會排序，我們不給它排是不是就行啦。
- 方向2：既然臨時表是影響group by性能的X因素，我們是不是可以不用臨時表?
我們一起來想下，執(zhí)行group by語句為什么需要臨時表呢?group by的語義邏輯，就是統(tǒng)計不同的值出現(xiàn)的個數。如果這個這些值一開始就是有序的，我們是不是直接往下掃描統(tǒng)計就好了，就不用臨時表來記錄并統(tǒng)計結果啦?
- group by 后面的字段加索引
- order by null 不用排序
- 盡量只使用內存臨時表
- 使用SQL_BIG_RESULT
5.1 group by 后面的字段加索引
如何保證group by后面的字段數值一開始就是有序的呢?當然就是加索引啦。
我們回到一下這個SQL
- select city ,count(*) as num from staff where age= 19 group by city;
它的執(zhí)行計劃
如果我們給它加個聯(lián)合索引idx_age_city(age,city)
- alter table staff add index idx_age_city(age,city);
再去看執(zhí)行計劃，發(fā)現(xiàn)既不用排序，也不需要臨時表啦。圖片
加合適的索引是優(yōu)化group by最簡單有效的優(yōu)化方式。
5.2 order by null 不用排序
并不是所有場景都適合加索引的，如果碰上不適合創(chuàng)建索引的場景，我們如何優(yōu)化呢?
如果你的需求并不需要對結果集進行排序，可以使用order by null。
- select city ,count(*) as num from staff group by city order by null
執(zhí)行計劃如下，已經沒有filesort啦
5.3 盡量只使用內存臨時表
如果group by需要統(tǒng)計的數據不多，我們可以盡量只使用內存臨時表;因為如果group by 的過程因為內存臨時表放不下數據，從而用到磁盤臨時表的話，是比較耗時的。因此可以適當調大tmp_table_size參數，來避免用到磁盤臨時表。
5.4 使用SQL_BIG_RESULT優(yōu)化
如果數據量實在太大怎么辦呢?總不能無限調大tmp_table_size吧?但也不能眼睜睜看著數據先放到內存臨時表，隨著數據插入發(fā)現(xiàn)到達上限，再轉成磁盤臨時表吧?這樣就有點不智能啦。
因此，如果預估數據量比較大，我們使用SQL_BIG_RESULT 這個提示直接用磁盤臨時表。MySQl優(yōu)化器發(fā)現(xiàn)，磁盤臨時表是B+樹存儲，存儲效率不如數組來得高。因此會直接用數組來存
示例SQl如下：
- select SQL_BIG_RESULT city ,count(*) as num from staff group by city;
執(zhí)行計劃的Extra字段可以看到，執(zhí)行沒有再使用臨時表，而是只有排序
執(zhí)行流程如下：
- 初始化 sort_buffer，放入city字段;
- 掃描表staff，依次取出city的值,存入 sort_buffer 中;
- 掃描完成后，對 sort_buffer的city字段做排序
- 排序完成后，就得到了一個有序數組。
- 根據有序數組，統(tǒng)計每個值出現(xiàn)的次數。
6. 一個生產慢SQL如何優(yōu)化
最近遇到個生產慢SQL，跟group by相關的，給大家看下怎么優(yōu)化哈。
表結構如下：
- CREATE TABLE `staff` (
- `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
- `id_card` varchar(20) NOT NULL COMMENT '身份證號碼',
- `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è)號',
- `legal_cert_no` varchar(64) NOT NULL COMMENT '法人號碼',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='員工表';
查詢的SQL是這樣的：
- select * from t1 where status = #{status} group by #{legal_cert_no}
我們先不去探討這個SQL的=是否合理。如果就是這么個SQL，你會怎么優(yōu)化呢?有想法的小伙伴可以留言討論哈，也可以加我微信加群探討。如果你覺得文章那里寫得不對，也可以提出來哈，一起進步，加油呀
參考與感謝
mySQL 45講 (https://time.geekbang.org/column/article/80477?cid=100020801)
本文轉載自微信公眾號「撿田螺的小男孩」，可以通過以下二維碼關注。轉載本文請聯(lián)系撿田螺的小男孩公眾號。
