网站要怎么创建,无锡便宜做网站,网页设计基础教学,wordpress调用模版引入
日常开发中#xff0c;我们经常会使用到group by。你是否知道group by的工作原理呢#xff1f;group by和having有什么区别呢#xff1f;group by的优化思路是怎样的呢#xff1f;使用group by有哪些需要注意的问题呢#xff1f;
使用group by的简单例子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)
) ENGINEInnoDB AUTO_INCREMENT15 DEFAULT CHARSETutf8 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’的记录。 判断临时表中是否有为 cityX’的行没有就插入一个记录 (X,1); 如果临时表中有cityX’的行的行就将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 后面并不会报错。当然这个可能跟不同的数据库不同的版本有关吧。大家使用的时候可以先验证一下就好。有一句话叫做纸上得来终觉浅绝知此事要躬行。
