网络口碑营销的特点,无锡网站优化推广方案,高端网站定制北京,中国工程机械网前言 日常开发中#xff0c;我们经常会使用到group by。亲爱的小伙伴#xff0c;你是否知道group by的工作原理呢#xff1f;group by和having有什么区别呢#xff1f;group by的优化思路是怎样的呢#xff1f;使用group by有哪些需要注意的问题呢#xff1f;本文将跟大家… 前言 日常开发中我们经常会使用到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)
) ENGINEInnoDB AUTO_INCREMENT15 DEFAULT CHARSETutf8 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的记录。判断临时表中是否有为 cityX的行没有就插入一个记录 (X,1);如果临时表中有cityX的行的行就将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判断临时表中是否有为 cityX的行没有就插入一个记录 (X,1);如果临时表中有cityX的行的行就将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子句执行顺序是怎样的呢。比如这个SQLselect 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 是可以的。不会报错并且返回的是分组的第一行数据。比如这个SQLselect city,id_card,age from staff group by city;查询结果是大家对比看下返回的就是每个分组的第一条数据当然平时大家使用的时候group by还是配合聚合函数使用的除非一些特殊场景比如你想去重当然去重用distinct也是可以的。4.2 group by 后面跟的字段一定要出现在select中嘛。不一定比如以下SQLselect 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_RESULT5.1 group by 后面的字段加索引如何保证group by后面的字段数值一开始就是有序的呢当然就是加索引啦。我们回到一下这个SQLselect city ,count(*) as num from staff where age 19 group by city;它的执行计划如果我们给它加个联合索引idx_age_cityage,cityalter 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)
) ENGINEInnoDB AUTO_INCREMENT15 DEFAULT CHARSETutf8 COMMENT员工表;查询的SQL是这样的select * from t1 where status #{status} group by #{legal_cert_no}我们先不去探讨这个SQL的是否合理。如果就是这么个SQL你会怎么优化呢有想法的小伙伴可以留言讨论哈也可以加我微信加群探讨。如果你觉得文章那里写得不对也可以提出来哈一起进步加油呀参考与感谢 mySQL 45讲 (https://time.geekbang.org/column/article/80477?cid100020801)求点赞、在看、分享三连
