MySQL group by
日常开发中，经常会使用到group by。是否知道group by的工作原理呢？group by和having有什么区别呢？group by的优化思路是怎样的呢？使用group by有哪些需要注意的问题呢？

  • 使用group by的简单例子
  • group by 工作原理
  • group by + where 和 having的区别
  • group by 优化思路

  • group by 使用注意点

    1、使用group by的简单例子

    group by一般用于分组统计，它表达的逻辑就是根据一定的规则，进行分组。先从一个简单的例子，一起来复习一下哈。
    假设用一张员工表，表结构如下：

    1. CREATE TABLE `staff` (
    2. `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
    3. `id_card` varchar(20) NOT NULL COMMENT '身份证号码',
    4. `name` varchar(64) NOT NULL COMMENT '姓名',
    5. `age` int(4) NOT NULL COMMENT '年龄',
    6. `city` varchar(64) NOT NULL COMMENT '城市',
    7. PRIMARY KEY (`id`)
    8. ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表';

    表存量的数据如下：
    group by详解 - 图1
    现在有这么一个需求：统计每个城市的员工数量。对应的 SQL 语句就可以这么写：

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

    执行结果如下：
    group by详解 - 图2
    这条SQL语句的逻辑很清楚了，但是它的底层执行流程是怎样的呢？

    2、group by 原理分析

    2.1 explain 分析

    先用explain查看一下执行计划

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

    group by详解 - 图3

  • Extra 这个字段的Using temporary表示在执行分组的时候使用了临时表

  • Extra 这个字段的Using filesort表示使用了排序

group by 怎么就使用到临时表和排序了呢？来看下这个SQL的执行流程

2.2 group by 的简单执行流程

  1. 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做排序，得到结果集返回给客户端。

这个流程的执行图如下：
group by详解 - 图4
临时表的排序是怎样的呢？
就是把需要排序的字段，放到sort buffer，排完就返回。在这里注意一点哈，排序分全字段排序和rowid排序

  • 如果是全字段排序，需要查询返回的字段，都放入sort buffer，根据排序字段排完，直接返回
  • 如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次回表操作，再返回。

  • 怎么确定走的是全字段排序还是rowid 排序排序呢？由一个数据库参数控制的，max_length_for_sort_data

    3、where 和 having的区别

  • group by + where 的执行流程

  • group by + having 的执行流程
  • 同时有where、group by 、having的执行顺序

    3.1 group by + where 的执行流程

    如果加了where条件之后，并且where条件列加了索引呢，执行流程是怎样？
    给它加个条件，并且加个idx_age的索引，如下：
    1. select city ,count(*) as num from staff where age> 30 group by city;
    2. --加索引
    3. alter table staff add index idx_age (age);
    再来expain分析一下：
    1. explain select city ,count(*) as num from staff where age> 30 group by city;
    group by详解 - 图5
    从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这样写：

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

    查询结果如下：
    group by详解 - 图6having称为分组过滤条件，它对返回的结果集操作。

    3.3 同时有where、group by 、having的执行顺序

    如果一个SQL同时含有where、group by、having子句，执行顺序是怎样的呢。
    比如这个SQL：

    1. select city ,count(*) as num from staff  where age> 19 group by city having num >= 3;

  3. 执行where子句查找符合年龄大于19的员工数据

  4. group by子句对员工数据，根据城市分组。
  5. 对group by子句形成的城市组，运行聚集函数计算每一组的员工数量值；
  6. 最后用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：

  1. select city,id_card,age from staff group by  city;

查询结果是
group by详解 - 图7
大家对比看下，返回的就是每个分组的第一条数据group by详解 - 图8
当然，平时大家使用的时候，group by还是配合聚合函数使用的，除非一些特殊场景，比如想去重，当然去重用distinct也是可以的。

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

不一定，比如以下SQL：

  1. select max(age)  from staff group by city;

执行结果如下：
group by详解 - 图9
分组字段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
    1. select city ,count(*) as num from staff where age= 19 group by city;
    它的执行计划
    group by详解 - 图10
    如果给它加个联合索引idx_age_city（age,city）
    1. alter table staff add index idx_age_city(age,city);
    再去看执行计划，发现既不用排序，也不需要临时表。group by详解 - 图11
    加合适的索引是优化group by最简单有效的优化方式。

    5.2 order by null 不用排序

    并不是所有场景都适合加索引的，如果碰上不适合创建索引的场景，如何优化呢？
    如果需求并不需要对结果集进行排序，可以使用order by null。
    1. select city ,count(*) as num from staff group by city order by null
    执行计划如下，已经没有filesort了
    group by详解 - 图12

    5.3 尽量只使用内存临时表

    如果group by需要统计的数据不多，可以尽量只使用内存临时表；因为如果group by 的过程因为内存临时表放不下数据，从而用到磁盘临时表的话，是比较耗时的。因此可以适当调大tmp_table_size参数，来避免用到磁盘临时表。

    5.4 使用SQL_BIG_RESULT优化

    如果数据量实在太大怎么办呢？总不能无限调大tmp_table_size吧？但也不能眼睁睁看着数据先放到内存临时表，随着数据插入发现到达上限，再转成磁盘临时表吧？这样就有点不智能。
    因此，如果预估数据量比较大，使用SQL_BIG_RESULT这个提示直接用磁盘临时表。MySQl优化器发现，磁盘临时表是B+树存储，存储效率不如数组来得高。因此会直接用数组来存
    示例SQl如下：
    1. select SQL_BIG_RESULT city ,count(*) as num from staff group by city;
    执行计划的Extra字段可以看到，执行没有再使用临时表，而是只有排序group by详解 - 图13
    执行流程如下：
  1. 初始化 sort_buffer，放入city字段；
  2. 扫描表staff，依次取出city的值,存入 sort_buffer 中；
  3. 扫描完成后，对 sort_buffer的city字段做排序
  4. 排序完成后，就得到了一个有序数组。
  5. 根据有序数组，统计每个值出现的次数。