group by
引入
日常开发中，我们经常会使用到group by。你是否知道group by的工作原理呢？group by和having有什么区别呢？group by的优化思路是怎样的呢？使用group by有哪些需要注意的问题呢？
- 使用group by的简单例子
- group by 工作原理
- group by 使用注意点
使用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语句的逻辑很清楚啦，但是它的底层执行流程是怎样的呢？
group by 原理分析
explain 分析
我们先用explain查看一下执行计划
explain select city ,count(*) as num from staff group by city;
- 1
- Extra 这个字段的Using temporary表示在执行分组的时候使用了临时表
- Extra 这个字段的Using filesort表示使用了排序
group by 怎么就使用到临时表和排序了呢？我们来看下这个SQL的执行流程
group by 的简单执行流程
explain select city ,count(*) as num from staff group by city;
- 创建内存临时表，表里有两个字段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
使用 group by 注意的问题
使用group by 主要有这几点需要注意：
- group by一定要配合聚合函数一起使用嘛？
- group by的字段一定要出现在select中嘛
- group by导致的慢SQL问题
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也是可以的。
group by 后面跟的字段一定要出现在select中吗？
不一定，比如以下SQL：
select max(age) from staff group by city;
执行结果如下：
分组字段city不在select 后面，并不会报错。当然，这个可能跟不同的数据库，不同的版本有关吧。大家使用的时候，可以先验证一下就好。有一句话叫做，纸上得来终觉浅，绝知此事要躬行。