看一遍就理解:Group By詳解

語言: CN / TW / HK

前言

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

日常開發中,我們經常會使用到group by。親愛的小夥伴,你是否知道group by的工作原理呢?group by和having有什麼區別呢?group by的優化思路是怎樣的呢?使用group by有哪些需要注意的問題呢?本文將跟大家一起來學習,攻克group by~

  • 使用group by的簡單例子
  • group by 工作原理
  • group by + where 和 having的區別
  • group by 優化思路
  • group by 使用注意點
  • 一個生產慢SQL如何優化

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出現並不代表一定是使用了索引下推,只是代表可以使用,但是不一定用了。大家如果有想法或者有疑問,可以加我微信討論哈。

執行流程如下:

  • 建立記憶體臨時表,表裡有兩個欄位city和num;
  • 掃描索引樹idx_age,找到大於年齡大於30的主鍵ID
  • 通過主鍵ID,回表找到city = 'X'
    • 判斷臨時表中是否有為 city='X'的行,沒有就插入一個記錄 (X,1);
    • 如果臨時表中有city='X'的行的行,就將x 這一行的num值加 1;
  • 繼續重複2,3步驟,找到所有滿足條件的資料,
  • 最後根據欄位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} 

我們先不去探討這個SQL的=是否合理。如果就是這麼個SQL,你會怎麼優化呢?有想法的小夥伴可以留言討論哈,也可以加我微信加群探討。如果你覺得文章那裡寫得不對,也可以提出來哈,一起進步,加油呀

參考與感謝

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