看一遍就理解:Group By详解

发布日期:2022-08-07 12:03    点击次数:102

前言

巨匠好,我是捡田螺的小男孩。

日常开发中,我们常常会运用到group by。敬爱的小搭档,你是否晓得group by的事变道理呢?group by和having有什么差别呢?group by的优化思路是怎么的呢?运用group by有哪些需要留心的成就呢?本文将跟巨匠一起来深造,攻克group by~

运用group by的俭朴例子 group by 事变道理 group by + where 和 having的差别 group by 优化思路 group by 运用留心点 一个临蓐慢SQL怎么优化 1. 运用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语句的逻辑很清楚啦,然则它的底层执行流程是怎么的呢?

2. group by 道理阐发 2.1 explain 阐发

我们先用explain查察一下执行设计

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

Extra 这个字段的Using temporary默示在执行分组的岁月运用了暂且表

Extra 这个字段的Using filesort默示运用了排序

group by 怎么就运用到暂且表和排序了呢?我们来看下这个SQL的执行流程

2.2 group by 的俭朴执行流程
explain select city ,count(*) as num from staff group by city; 

我们一起来看下这个SQL的执行流程哈

创立内存暂且表,表里有两个字段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

对排序有兴致深入相识的小搭档,可以或许看我这篇文章哈。

看一遍就理解:order by详解

3. where 和 having的差别 group by + where 的执行流程 group by + having 的执行流程 同时有where、group by 、having的执行按次 3.1 group by + where 的执行流程

有些小搭档感应上一小节的SQL太俭朴啦,假设加了where条件当前,并且where条件列加了索引呢,执行流程是怎么?

好的,我们给它加个条件,并且加个idx_age的索引,以下:

select city ,count(*) as num from staff where age> 30 group by city; //加索引 alter table staff add index idx_age (age); 

再来expain阐发一下:

explain select city ,count(*) as num from staff where age> 30 group by city; 

从explain 执行设计终局,可以或许缔造查询条件射中了idx_age的索引,并且运用了暂且表和排序

Using index condition:默示索引下推优化,痛处索引尽管即便的过滤数据,尔后再前去给服务器层痛处where别的条件举行过滤。这里单个索引为何会出现索引下推呢?explain出现实在不代表必定是运用了索引下推,只是代表可运用,然则不必定用了。巨匠假设有主见主张或许有疑问,可以或许加我微信探究哈。

执行流程以下:

创立内存暂且表,表里有两个字段city和num; 扫描索引树idx_age,找到大于年岁大于30的主键ID 经由过程主键ID,回表找到city = 'X' 鉴定暂且表中是否有为 city='X'的行,没有就拔出一个记载 (X,1); 假设暂且表中有city='X'的行的行,就将x 这一行的num值加 1; 延续重复2,3步伐,找到全体餍足条件的数据, 最后痛处字段city做排序,失去终局集前去给客户端。 3.2 group by + having 的执行

假设你要查询每个都会的员工数量,获失去员工数量不低于3的都会,having可以或许很好经管你的成就,SQL酱紫写:

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

查询终局以下:

having称为分组过滤条件,它对前去的终局集操作。

3.3 同时有where、group by 、having的执行按次

假设一个SQL同时含有where、group by、having子句,执行按次是怎么的呢。

比喻这个SQL:

select city ,count(*) as num from staff  where age> 19 group by city having num >= 3; 
执行where子句查找吻合年岁大于19的员工数据 group by子句对员工数据,痛处都会分组。 对group by子句组成的都会组,运行聚集函数计算每一组的员工数量值; 最后用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:

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

查询终局是

巨匠对比看下,前去的就是每个分组的第一条数据

固然,寻常巨匠运用的岁月,group by照旧共同聚合函数运用的,除非一些不凡场景,比喻你想去重,固然去重用distinct也是可以或许的。

4.2 group by 后面跟的字段必定要出当初select中嘛。

不必定,比喻下列SQL:

select max(age)  from staff group by city; 

执行终局以下:

分组字段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

select city ,count(*) as num from staff where age= 19 group by city; 

它的执行设计

假设我们给它加个联合索引idx_age_city(age,city)

alter table staff add index idx_age_city(age,city); 

再去看执行设计,缔造既不消排序,也不需要暂且表啦。图片

加相宜的索引是优化group by最俭朴有用的优化要领。

5.2 order by null 不消排序

实在不是全体场景都适合加索引的,假设碰上不适合创立索引的场景,我们怎么优化呢?

假设你的需务实在不需要对终局集举行排序,可运用order by null。

select city ,count(*) as num from staff group by city order by null 

执行设计以下,已经没有filesort啦

5.3 尽管即便只运用内存暂且表

假设group by需要统计的数据不多,我们可以或许尽管即便只运用内存暂且表;因为假设group by 的进程因为内存暂且表放不下数据,从而用到磁盘暂且表的话,是相比耗时的。因而可以或许适合调大tmp_table_size参数,来防止用到磁盘暂且表。

5.4 运用SQL_BIG_RESULT优化

假设数据量实在太大怎么办呢?总不克不迭有限调大tmp_table_size吧?但也不克不迭眼睁睁看着数据先放到内存暂且表,随着数据拔登程明抵达上限,再转成磁盘暂且表吧?这样就有点不智能啦。

因而,假设预估数据量相比大,我们运用SQL_BIG_RESULT 这个提示间接用磁盘暂且表。MySQl优化器缔造,磁盘暂且表是B+树存储,存储效劳不如数组来得高。因而会间接用数组来存

示例SQl以下:

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

执行设计的Extra字段可以或许看到,执行没有再运用暂且表,而是只要排序

执行流程以下:

初始化 sort_buffer,放入city字段; 扫描表staff,顺次取出city的值,存入 sort_buffer 中; 扫描实现后,对 sort_buffer的city字段做排序 排序实现后,就失去了一个有序数组。 根占据序数组,统计每个值出现的次数。 6. 一个临蓐慢SQL怎么优化

比来遇到个临蓐慢SQL,跟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 '姓名',   `status` varchar(64) NOT NULL COMMENT 'Y-已激活 I-初始化 D-已删除 R-查核中',   `age` int(4) NOT NULL COMMENT '年岁',   `city` varchar(64) NOT NULL COMMENT '都会',   `enterprise_no` varchar(64) NOT NULL COMMENT '企业号',   `legal_cert_no` varchar(64) NOT NULL COMMENT '法人号码',   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表'; 

查询的SQL是这样的:

select * from t1 where status = #{status} group by #{legal_cert_no} 

我们先不去筹商这个SQL的=是否公正。假设就是这么个SQL,你会怎么优化呢?有主见主张的小搭档可以或许留言探究哈,也可以加我微信加群筹商。假设你感应文章那里写得纰谬,也可以提进去哈,一起提高,加油呀

参考与谢谢冲动

mySQL 45讲 (https://time.geekbang.org/column/article/80477?cid=100020801)

本文转载自微信群众号「捡田螺的小男孩」,可以或许经由过程下列二维码关注。转载本文请联络捡田螺的小男孩群众号。

 



栏目分类



Powered by 【欧冠体育官方入口】 @2013-2022 RSS地图 HTML地图