MySQL中提供了group by关键字,用来对数据进行分组,用来统计分组的信息。

group by的原理

select id%10 as m, count(*) as c from t group by m;

上述的sql是对id进行分组,对10取模相同的id分到一组,然后获取组内的数量。

group by的流程如下:
1、创建一个内存临时表temp,表里有两个字段 m 和 c,m为主键

2、从表 t 中选取一个最合适的索引,依次的读取出对应的id值,然后将id%10 = mm,对于结果有两种处理方式:
a、如果temp中主键m中不存在mm,就将(m,1)插入到temp中
b、如果temp中主键m中存在对应的mm,就将mm对应的c的值+1

3、根据m的值对表temp排序,将对应的结果返回给客户端

group by 默认是会对group by的字段进行排序的,如果没有对应的排序要求,就在sql最后加上order by null

select id%10 as m, count(*) as c from t group by m order by null;

内存表or磁盘表

临时表既可以在内存中,也可以在磁盘上。

内存临时表使用的是Memory引擎。
磁盘临时表使用的是innodb引擎。

MySQL默认会提供一个内存临时表来存放分组的数据,但是当临时表的大小超过16MB的时候,会分组数据存放到磁盘中。

这样的话,需要额外的磁盘IO的开销,group by的效率会进一步降低。

group by的优化

为什么要对groupby建立临时表呢?

因为要统计对应分组的信息,因为同一个分组的数据不是连续排列的,无法通过一次扫描就可以得出对应的分组信息。

如果group by的字段如果是有序的就好了,我们可以在遍历的时候就可以统计相关的分组信息。

所以我们可以对group by的字段加索引来提高group by的效率。

但是对于上述的group by id % 10,我们是无法对其直接进行排序的。
我们可以对表新增加一列z,z的内容就是 id % 10,
然后对z建立一个索引,直接对z进行group by 就ok了。

alter table t1 add column z int generated always as(id % 100), add index(z);

select z, count(*) as c from t1 group by z;

group by的条件查询

当需要对group by统计的信息进行条件过滤的时候,需要用having关键字,而不是where。

相较于where,having加入了聚集函数,比如sum()、avg()、max()、count()。

having是where的一个超集,having是对分组进行过滤,在分组之后进行。where是在分组前过滤,在分组之前进行。

聚集函数是将多条数据中的一些信息转化为一个数字,比如sum()、avg()、max()、count()。

select id%10 as m, count(*) as c from t group by m having c > 5 order by null;

比如上述的sql语句,通过having c > 5 来查询 组中超过5条记录的对应的组。

group by 只能查询对应的分组字段和所有字段的聚集函数信息,比如sum()、avg()、max()等。
对应的having条件也只能从分组字段和所有字段的聚集函数信息来进行过滤

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐