共計 5882 個字符，預計需要花費 15 分鐘才能閱讀完成。
今天丸趣 TV 小編給大家分享一下 MySql 中如何用 group by 的相關知識點，內容詳細，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。
日常開發中，我們經常會使用到 group by。親愛的小伙伴，你是否知道 group by 的工作原理呢？group by 和 having 有什么區別呢？group by 的優化思路是怎樣的呢？使用 group by 有哪些需要注意的問題呢？本文將跟大家一起來學習，攻克 group by~
使用 group by 的簡單例子
group by 工作原理
group by + where 和 group by + having 的區別
group by 優化思路
group by 使用注意點
一個生產慢 SQL 如何優化
【相關推薦：mysql 視頻教程】
1. 使用 group by 的簡單例子
group by 一般用于分組統計，它表達的邏輯就是根據一定的規則，進行分組。我們先從一個簡單的例子，一起復習一下哈。
假設用一張員工表，表結構如下：
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= 員工表 表存量的數據如下：
我們現在有這么一個需求：統計每個城市的員工數量。對應的 SQL 語句就可以這么寫：
select city ,count(*) as num from staff group by city;執行結果如下：
這條 SQL 語句的邏輯很清楚啦，但是它的底層執行流程是怎樣的呢？
2. group by 原理分析 2.1 explain 分析
我們先用 explain 查看一下執行計劃
explain select city ,count(*) as num from staff group by city;
Extra 這個字段的 Using temporary 表示在執行分組的時候使用了臨時表
Extra 這個字段的 Using filesort 表示使用了排序
group by 怎么就使用到臨時表和排序了呢？我們來看下這個 SQL 的執行流程
2.2 group by 的簡單執行流程
explain select city ,count(*) as num from staff group by city;
我們一起來看下這個 SQL 的執行流程哈
創建內存臨時表，表里有兩個字段 city 和 num；
全表掃描 staff 的記錄，依次取出 city = X 的記錄。
判斷臨時表中是否有為 city= X 的行，沒有就插入一個記錄 (X,1);
如果臨時表中有 city= X 的行的行，就將 x 這一行的 num 值加 1；
遍歷完成后，再根據字段 city 做排序，得到結果集返回給客戶端。
這個流程的執行圖如下：
臨時表的排序是怎樣的呢？
就是把需要排序的字段，放到 sort buffer，排完就返回。在這里注意一點哈，排序分全字段排序和 rowid 排序
如果是全字段排序，需要查詢返回的字段，都放入 sort buffer，根據排序字段排完，直接返回
如果是 rowid 排序，只是需要排序的字段放入 sort buffer，然后多一次回表操作，再返回。
怎么確定走的是全字段排序還是 rowid 排序排序呢？由一個數據庫參數控制的，max_length_for_sort_data
對排序有興趣深入了解的小伙伴，可以看我這篇文章哈。
看一遍就理解：order by 詳解
3. where 和 having 的區別
group by + where 的執行流程
group by + having 的執行流程
同時有 where、group by、having 的執行順序
3.1 group by + where 的執行流程
有些小伙伴覺得上一小節的 SQL 太簡單啦，如果加了 where 條件之后，并且 where 條件列加了索引呢，執行流程是怎樣？
好的，我們給它加個條件，并且加個 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 執行計劃結果，可以發現查詢條件命中了 idx_age 的索引，并且使用了臨時表和排序
Using index condition: 表示索引下推優化，根據索引盡可能的過濾數據, 然后再返回給服務器層根據 where 其他條件進行過濾。這里單個索引為什么會出現索引下推呢？explain 出現并不代表一定是使用了索引下推，只是代表可以使用，但是不一定用了。大家如果有想法或者有疑問，可以加我微信討論哈。
執行流程如下：
1、創建內存臨時表，表里有兩個字段 city 和 num；
2、掃描索引樹 idx_age，找到大于年齡大于 30 的主鍵 ID
3、通過主鍵 ID，回表找到 city = X
判斷臨時表中是否有為 city= X 的行，沒有就插入一個記錄 (X,1);
如果臨時表中有 city= X 的行的行，就將 x 這一行的 num 值加 1；
4、繼續重復 2,3 步驟，找到所有滿足條件的數據，
5、最后根據字段 city 做排序，得到結果集返回給客戶端。
3.2 group by + having 的執行
如果你要查詢每個城市的員工數量，獲取到員工數量不低于 3 的城市，having 可以很好解決你的問題，SQL 醬紫寫：
select city ,count(*) as num from staff group by city having num = 3;查詢結果如下：
having 稱為分組過濾條件，它對返回的結果集操作。
3.3 同時有 where、group by、having 的執行順序
如果一個 SQL 同時含有 where、group by、having 子句，執行順序是怎樣的呢。
比如這個 SQL：
select city ,count(*) as num from staff where age 19 group by city having num = 3;執行 where 子句查找符合年齡大于 19 的員工數據
group by 子句對員工數據，根據城市分組。
對 group by 子句形成的城市組，運行聚集函數計算每一組的員工數量值；
最后用 having 子句選出員工數量大于等于 3 的城市組。
3.4 where + having 區別總結
having 子句用于分組后篩選，where 子句用于行條件篩選
having 一般都是配合 group by 和聚合函數一起出現如 (count(),sum(),avg(),max(),min())
where 條件子句中不能使用聚集函數，而 having 子句就可以。
having 只能用在 group by 之后，where 執行在 group by 之前
4. 使用 group by 注意的問題
使用 group by 主要有這幾點需要注意：
group by 一定要配合聚合函數一起使用嘛？
group by 的字段一定要出現在 select 中嘛
group by 導致的慢 SQL 問題
4.1 group by 一定要配合聚合函數使用嘛？
group by 就是分組統計的意思，一般情況都是配合聚合函數 如（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 后面跟的字段一定要出現在 select 中嘛。
不一定，比如以下 SQL：
select max(age) from staff group by city;執行結果如下：
分組字段 city 不在 select 后面，并不會報錯。當然，這個可能跟不同的數據庫，不同的版本有關吧。大家使用的時候，可以先驗證一下就好。有一句話叫做，紙上得來終覺淺，絕知此事要躬行。
4.3 group by 導致的慢 SQL 問題
到了最重要的一個注意問題啦，group by 使用不當，很容易就會產生慢 SQL
問題。因為它既用到臨時表，又默認用到排序。有時候還可能用到磁盤臨時表。
如果執行過程中，會發現內存臨時表大小到達了上限（控制這個上限的參數就是 tmp_table_size），會把內存臨時表轉成磁盤臨時表。
如果數據量很大，很可能這個查詢需要的磁盤臨時表，就會占用大量的磁盤空間。
這些都是導致慢 SQL 的 x 因素，我們一起來探討優化方案哈。
5. group by 的一些優化方案
從哪些方向去優化呢？
方向 1：既然它默認會排序，我們不給它排是不是就行啦。
方向 2：既然臨時表是影響 group by 性能的 X 因素，我們是不是可以不用臨時表？
我們一起來想下，執行 group by 語句為什么需要臨時表呢？group by 的語義邏輯，就是統計不同的值出現的個數。如果這個這些值一開始就是有序的，我們是不是直接往下掃描統計就好了，就不用臨時表來記錄并統計結果啦?
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;它的執行計劃
如果我們給它加個聯合索引 idx_age_city（age,city）
alter table staff add index idx_age_city(age,city);再去看執行計劃，發現既不用排序，也不需要臨時表啦。
加合適的索引是優化 group by 最簡單有效的優化方式。
5.2 order by null 不用排序
并不是所有場景都適合加索引的，如果碰上不適合創建索引的場景，我們如何優化呢？
如果你的需求并不需要對結果集進行排序，可以使用 order by null。
select city ,count(*) as num from staff group by city order by null執行計劃如下，已經沒有 filesort 啦
5.3 盡量只使用內存臨時表
如果 group by 需要統計的數據不多，我們可以盡量只使用內存臨時表；因為如果 group by 的過程因為數據放不下，導致用到磁盤臨時表的話，是比較耗時的。因此可以適當調大 tmp_table_size 參數，來避免用到磁盤臨時表。
5.4 使用 SQL_BIG_RESULT 優化
如果數據量實在太大怎么辦呢？總不能無限調大 tmp_table_size 吧？但也不能眼睜睜看著數據先放到內存臨時表，隨著數據插入發現到達上限，再轉成磁盤臨時表吧？這樣就有點不智能啦。
因此，如果預估數據量比較大，我們使用 SQL_BIG_RESULT 這個提示直接用磁盤臨時表。MySQl 優化器發現，磁盤臨時表是 B + 樹存儲，存儲效率不如數組來得高。因此會直接用數組來存
示例 SQl 如下：
select SQL_BIG_RESULT city ,count(*) as num from staff group by city;執行計劃的 Extra 字段可以看到，執行沒有再使用臨時表，而是只有排序
執行流程如下：
初始化 sort_buffer，放入 city 字段；
掃描表 staff，依次取出 city 的值, 存入 sort_buffer 中；
掃描完成后，對 sort_buffer 的 city 字段做排序
排序完成后，就得到了一個有序數組。
根據有序數組，統計每個值出現的次數。
6. 一個生產慢 SQL 如何優化
最近遇到個生產慢 SQL，跟 group by 相關的，給大家看下怎么優化哈。
表結構如下：
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 企業號 ,
`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}以上就是“MySql 中如何用 group by”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，丸趣 TV 小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注丸趣 TV 行業資訊頻道。
