引子
宇宙好，我是捡田螺的小男孩。
日常设立中，咱们频频会使用到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()，范冰冰性xxxxhd杨幂性xxxxhdmax()，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啦
淌若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)
本文转载自微信公众号「捡田螺的小男孩」，不错通过以下二维码怜惜。转载本文请接洽捡田螺的小男孩公众号。
