MySQL：Group By的具体介绍与内部执行原理

迷南。 2023-10-01 15:56 19阅读 0赞

### 文章目录 ###

*  一、前言
 *  1、使用group by的简单例子
 *  2、group by 原理分析
 *   *  2.1 explain 分析
     *  2.2 group by 的简单执行流程
     *   *  2.2.1 临时表的排序是怎样的呢？
 *  3、where 和 having的区别
 *   *  3.1 group by + where 的执行流程
     *  3.2 group by + having 的执行
     *  3.3 同时有where、group by 、having的执行顺序
     *  3.4 where + having 区别总结
 *  4、 使用 group by 注意的问题
 *   *  4.1 group by一定要配合聚合函数使用嘛？
     *  4.2 group by 后面跟的字段一定要出现在select中嘛。
     *  4.3 \`group by\`导致的慢SQL问题
 *  5、group by的一些优化方案
 *   *  5.1 group by 后面的字段加索引
     *  5.2 order by null 不用排序
     *  5.3 尽量只使用内存临时表
     *  5.4 使用SQL\\\_BIG\\\_RESULT优化
 *  6、 一个生产慢SQL如何优化

# 一、前言 #

*  **声明：**
    
     *  **原作者：** 捡田螺的小男孩，公众号：捡田螺的小男孩
     *  **原文地址：** 掘金：https://juejin.cn/post/7053966777088213005
 *  日常开发中，我们经常会使用到 **`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** 如何优化

# 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='员工表';

*  **表存量的数据如下：**

![format_png][]

*  我们现在有这么一个需求：**统计每个城市的员工数量**。对应的 SQL 语句就可以这么写：

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

*  **执行结果如下：**

![format_png 1][]

*  这条SQL语句的逻辑很清楚啦，但是它的底层执行流程是怎样的呢？

# 2、group by 原理分析 #

## 2.1 explain 分析 ##

*  我们先用 **`explain`** 查看一下执行计划

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

![format_png 2][]

*  **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的执行流程哈**

1.  创建内存临时表，表里有两个字段 **`city` 和 `num`**；
2.  全表扫描 **`staff`** 的记录，依次取出 **city = ‘X’** 的记录。

*  判断 **临时表** 中是否有为 city=‘X’ 的行，没有就插入一个记录 (X,1);
 *  如果临时表中有 city=‘X’ 的行的行，就将 x 这一行的 num 值加 1；

1.  遍历完成后，再根据字段 **`city`** 做**排序**，得到结果集返回给客户端。

**这个流程的执行图如下：**

![format_png 3][]

### 2.2.1 临时表的排序是怎样的呢？ ###

就是把需要排序的字段，放到sort buffer，排完就返回。在这里注意一点哈，排序分 **全字段排序** 和 **rowid排序**

*  如果是 **`全字段排序`**，需要查询返回的字段，都放入 **`sort buffer`**，根据 **排序字段** 排完，直接返回
 *  如果是 **`rowid排序`**，只是需要排序的字段放入 **`sort buffer`**，然后多一次 **回表** 操作，再返回。
 *  怎么确定走的是全字段排序还是 **rowid** 排序排序呢？由一个数据库参数控制的，**`max_length_for_sort_data`**

对排序有兴趣深入了解的小伙伴，可以观看原作者这篇文章：

*  [看一遍就理解：order by详解][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;

![format_png 4][]

*  从 **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；

1.  继续重复 2,3 步骤，找到所有满足条件的数据，
2.  最后根据字段 **`city`** 做 **排序**，得到结果集返回给客户端。

## 3.2 group by + having 的执行 ##

*  如果你要查询每个城市的员工数量，获取到员工数量不低于 3 的城市，**having** 可以很好解决你的问题，**SQL这样写：**

select city ,count(*) as num from staff  group by city having num >= 3;

**查询结果如下：**

![format_png 5][]

*  **`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;

1.  执行 **`where`** 子句查找符合年龄大于 19 的员工数据
2.  **`group by`** 子句对员工数据，根据城市分组。
3.  对 **`group by`** 子句形成的城市组，运行聚集函数计算每一组的员工数量值；
4.  最后用 **`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;

**查询结果是：**  
![format_png 6][]

**大家对比看下，返回的就是每个分组的第一条数据** ![format_png 7][]

*  当然，平时大家使用的时候，**group by** 还是配合聚合函数使用的，除非一些特殊场景，比如你想 **去重**，当然去重用 **`distinct`** 也是可以的。

## 4.2 group by 后面跟的字段一定要出现在select中嘛。 ##

**不一定，比如以下SQL：**

select max(age)  from staff group by city;

**执行结果如下：**  
![format_png 8][]

*  分组字段 **`city`** 不在 **select** 后面，并不会报错。当然，这个可能跟 **不同的数据库，不同的版本** 有关吧。
 *  大家使用的时候，可以先验证一下就好。有一句话叫做，**纸上得来终觉浅，绝知此事要躬行**。

## 4.3 `group by`导致的慢SQL问题 ##

到了最重要的一个注意问题啦，`group by`使用不当，很容易就会产生慢 **SQL** 问题。因为它既用到**临时表**，又默认用到**排序**。有时候还可能用到**磁盘临时表**。

*  如果执行过程中，会发现内存临时表大小到达了**上限**（控制这个上限的参数就是`tmp_table_size`），会把**内存临时表转成磁盘临时表**。
 *  如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间。
 *  这些都是导致慢 **SQL** 的因素，我们一起来探讨优化方案哈。

# 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;

*  它的执行逻辑

![format_png 9][]

*  如果我们给它加个联合索引 **`idx_age_city（age,city）`**

alter table staff add index idx_age_city(age,city);

*  再去看执行计划，发现既不用排序，也不需要临时表啦。 ![format_png 10][]  
    **加合适的索引** 是优化 **`group by`** 最简单有效的优化方式。

## 5.2 order by null 不用排序 ##

*  并不是所有场景都适合加索引的，如果碰上不适合创建索引的场景，我们如何优化呢？
    
     *  如果你的需求并不需要对结果集进行排序，可以使用 **`order by null`**。

select city ,count(*) as num from staff group by city order by null

执行计划如下，已经没有 **`filesort`** 啦

![format_png 11][]

## 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`** 字段可以看到，执行没有再使用临时表，而是只有排序 ![format_png 12][]

**执行流程如下：**

1.  初始化 sort\_buffer，放入city字段；
2.  扫描表staff，依次取出city的值,存入 sort\_buffer 中；
3.  扫描完成后，对 sort\_buffer的city字段做排序
4.  排序完成后，就得到了一个有序数组。
5.  根据有序数组，统计每个值出现的次数。

# 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}

[format_png]: https://img-blog.csdnimg.cn/img_convert/ea9ea0e55df279345972b285292e762b.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/a5f84f5ae7ad0617e81c5e11f70d947d.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/919c479825812266a05a17fd4ca9aaef.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/576ef076b8e40a9fbd2e253b994fbff6.webp?x-oss-process=image/format,png
[order by]: https://link.juejin.cn?target=https%3A%2F%2Fmp.weixin.qq.com%2Fs%3F__biz%3DMzg3NzU5NTIwNg%3D%3D%26mid%3D2247490571%26idx%3D1%26sn%3De8638573ec8d720fd25da5b2b0d90ed2%26chksm%3Dcf21c322f8564a34461acd9811730d14d12075cf5c7438a3a11433725b9ce463fcb78e7916a1%26token%3D574771970%26lang%3Dzh_CN%23rd
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/328215f5669471d04cc04cb94555a457.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/406e739a5057343ef198d18cdcff825b.webp?x-oss-process=image/format,png
[format_png 6]: https://img-blog.csdnimg.cn/img_convert/e1c6005f49a6efdc40db0f8f11769662.webp?x-oss-process=image/format,png
[format_png 7]: https://img-blog.csdnimg.cn/img_convert/50d26f6807216654db3be6613aaad7cb.webp?x-oss-process=image/format,png
[format_png 8]: https://img-blog.csdnimg.cn/img_convert/7df23fcd1edbc5f408a08e047e23e1d3.webp?x-oss-process=image/format,png
[format_png 9]: https://img-blog.csdnimg.cn/img_convert/a4ad2c6908e7c18310c211f432c4c490.webp?x-oss-process=image/format,png
[format_png 10]: https://img-blog.csdnimg.cn/img_convert/877645fcd29cb81aaec4a0687a3e537f.webp?x-oss-process=image/format,png
[format_png 11]: https://img-blog.csdnimg.cn/img_convert/ad339b1a8e8be1d2f644c6cafd53e774.webp?x-oss-process=image/format,png
[format_png 12]: https://img-blog.csdnimg.cn/img_convert/c4c3c566ef523ba23c48b3dc582ea29b.webp?x-oss-process=image/format,png