499购彩

让建站和SEO变得简单

让不懂建站的用户快速建站,让会建站的提高建站效率!

首页你的位置:499购彩 > 首页 >

看一遍就富厚:Group By详解

发布日期:2022-05-15 16:06    点击次数:189

   引子

环球好,我是捡田螺的小男孩。

日常成就中,咱们往往会使用到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 499购彩 @2013-2022 RSS地图 HTML地图

Copyright 365建站 © 2013-2021 365建站器 版权所有