Mysql數據庫group by原理詳解

Posted on by

引言

日常開發中，我們經常會使用到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，你會怎麼優化呢？有想法的小夥伴可以留言討論哈，也可以加我微信加群探討。如果你覺得文章那裡寫得不對，也可以提出來哈，一起進步，加油呀

參考

https://www.jb51.net/article/222520.htm

以上就是Mysql數據庫group by原理詳解的詳細內容，更多關於Mysql數據庫group by的資料請關註WalkonNet其它相關文章！

推薦閱讀：