深入了解MySql中怎麼用group by？ （用法詳解）

MySql中怎麼用group by？以下這篇文章給大家深入解析下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

表示使用了

排序

1. group by 怎麼就使用到臨時表格和排序
了呢？我們來看下這個SQL的執行流程
2. 2.2 group by 的簡單執行流程

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

我們一起來看下這個SQL的執行流程哈
• 建立記憶體臨時表，表裡有兩個欄位
city
• 和
num

；

3. 全表掃描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 <span style="font-size: 16px;">group by</span>导致的慢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中怎麼用group by？ （用法詳解）的詳細內容。更多資訊請關注PHP中文網其他相關文章！