MySQL之groupby详解

x33g5p2x  于2021-10-16 转载在 Mysql  
字(0.9k)|赞(0)|评价(0)|浏览(306)

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

groupby的原理

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

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

groupby的流程如下:
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;

相关文章

微信公众号

最新文章

更多