前言大家好，我是捡蜗牛的小男孩。在日常开发中，我们经常会用到groupby。小伙伴们知道groupby的工作原理吗？groupby和having有什么区别？groupby的优化思路是什么？使用groupby需要注意哪些问题？本文将和大家一起学习起来，攻克groupby~一个使用groupby的简单例子groupby的工作原理+和groupby的区别在哪里groupby的优化思路使用注意点如何优化一个productionslowSQL1.使用groupbygroupby的简单例子一般用于分组统计，它表达的逻辑是按照一定的规则进行分组。让我们从一个简单的例子开始，一起回顾一下。假设一张员工表，表结构如下：CREATETABLE`staff`(`id`bigint(11)NOTNULLAUTO_INCREMENTCOMMENT'主键id',`id_card`varchar(20)NOTNULLCOMMENT'身份证号码',`name`varchar(64)NOTNULLCOMMENT'name',`age`int(4)NOTNULLCOMMENT'age',`city`varchar(64)NOTNULLCOMMENT'city',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=15DEFAULTCHARSET=utf8COMMENT='员工表';table存量数据如下：我们现在有这样一个需求：统计每个城市的从业人数。对应的SQL语句可以这样写：selectcity,count(*)asnumfromstaffgroupbycity;执行结果如下：这条SQL语句的逻辑很清晰，但是它的底层执行过程是怎样的呢？2.Groupby原理分析2.1explain分析下面用explain查看执行计划explainselectcity,count(*)asnumfromstaffgroupbycity;在Extra这个字段中使用temporary表示在进行分组时使用临时表。在Extra的这个字段中使用filesort表示使用了排序groupby。如何使用临时表和排序？下面看一下这条SQL的执行过程2.2groupbyexplainselectcity,count(*)asnumfromstaffgroupbycity的简单执行过程；我们来看看这条SQL的执行过程。创建临时内存表。表中有两个字段city和num；全表扫描staff的记录，依次取出city='X'的记录。判断临时表中是否有city='X'的行，如果没有则插入一条记录(X,1)；如果临时表中有city='X'的行，则加上x1行的num值；遍历完成后，根据字段city排序，返回结果集给客户端。这个过程的执行图如下：临时表的排序是怎样的？就是把需要排序的字段放入排序缓冲区，排序后返回。这里注意，排序分为full-field排序和rowid排序。如果是全字段排序，需要查询返回的字段全部放入排序缓冲区。按照排序字段排序后，直接返回。如果是rowid排序，只将需要排序的字段放入排序缓冲区，再返回一次表，再返回。如何判断是按所有字段排序还是按rowid排序？它是由一个数据库参数控制的，max_length_for_sort_data对排序感兴趣的朋友可以看我的文章。看完就明白了：orderby详解3.where和having的区别groupby+where的执行过程groupby+having的执行过程同时具有where、groupby、having的执行顺序3.1groupby+where的执行过程有点小小伙伴觉得上一节的SQL太简单了。如果加上where条件，对where条件列进行索引，执行过程是怎样的？好吧，我们给它加个条件，加个idx_age索引，如下：selectcity,count(*)asnumfromstaffwhereage>30groupbycity;//addindexaltertablestaffaddindexidx_age(age);让解释分析：explainselectcity,count(*)asnumfromstaffwhereage>30groupbycity;从explainexecutionplan的结果可以发现查询条件命中了idx_age的索引，使用临时表和排序Usingindexcondition：表示索引下推优化，尽可能根据索引过滤数据，然后返回到server层根据where其他条件进行过滤。为什么这里有单个索引的索引下推？explain的出现并不一定代表使用了索引下推，而是可以使用，但不一定使用。大家有什么想法或者问题可以加我微信一起讨论。执行过程如下：创建一个临时内存表，有两个字段city和num；扫描索引树idx_age，找到年龄大于30的主键ID通过主键ID，回表查找city='X'判断临时表中是否有如果没有rowwithcity='X',插入一条记录(X,1);如果临时表中有city='X'的行，x行的num值加1；继续重复2、步骤3，找到所有符合条件的数据，最后根据字段city排序，返回结果集给客户端。3.2groupby+having的执行如果你想查询每个城市的员工人数，得到至少有3个员工的城市，having可以很好的解决你的问题。SQLsauce写道：selectcity,count(*)asnumfromstaffgroupbycityhavingnum>=3;查询结果如下：having称为分组过滤条件，对返回的结果集进行操作。3.3where、groupby、having同时执行的顺序如果SQL同时包含where、groupby、having子句，执行顺序是什么？例如，这个SQL：selectcity,count(*)asnumfromstaffwhereage>19groupbycityhavingnum>=3;执行where子句查找年龄大于19的员工数据。groupby子句根据城市对员工数据进行分组。对于groupby子句组成的城市组，运行聚合函数计算每个组的员工人数的值；最后使用having子句选择员工数大于等于3的城市组。3.4where+having差异总结having子句用于分组后的过滤，where子句用于行条件过滤。having一般与groupby和聚合函数一起出现，如(count(),sum(),avg(),max(),min())where条件子句中不能使用聚合函数，而having子句可以。having只能用在groupby之后，在groupby之前执行的where应该出现在select中吗？4.1groupby引起的SQL慢问题groupby必须和聚合函数一起使用吗？(),max(),min())在一起。count()数量sum()求和avg()平均值max()最大值Min()最小值如果不与聚合函数一起使用，是否可以使用？我用的是Mysql5.7，是可以的。不会报错，返回的是分组后的第一行数据。例如这个SQL：selectcity,id_card,agefromstaffgroupbycity；查询结果是给你比较的，返回的数据是每组的第一条数据。当然你平时使用的时候，groupby还是和聚合函数一起使用，除非有一些特殊的场景，比如你要去重，当然复用distinct也是可以的。4.2groupby后面的字段必须出现在select中。不一定，比如下面的SQL：selectmax(age)fromstaffgroupbycity;执行结果如下：group字段city不在select后面，不会报错。当然，这可能与不同的数据库、不同的版本有关。当你使用它时，你可以先验证它。有句话说，写在纸上的东西最终会变得肤浅，但你永远不会知道你必须自己去做。4.3groupby导致的SQL慢问题是最需要注意的。groupby使用不当很容易造成SQL慢的问题。因为它默认同时使用了临时表和排序。有时也可能使用磁盘临时表。如果在执行过程中，你会发现临时内存表的大小已经达到了上限（控制这个上限的参数是tmp_table_size），临时内存表就会被转换为临时磁盘表。如果数据量很大，很可能这个查询需要的磁盘临时表会占用大量的磁盘空间。以上就是导致SQL变慢的x因素，一起来探讨优化方案吧。5、groupby的一些优化方案应该朝哪个方向优化？方向一：既然默认会排序，那我们不排序就好了。方向二：既然临时表是影响groupby性能的X因素，那能不能不用临时表？大家一起来想一想，为什么需要临时表来执行groupby语句呢？groupby的语义逻辑是统计不同值出现的次数。如果这些值是从头开始排序的，我们是不是直接往下扫描，直接计数，就不需要临时表来记录和计数结果了？groupby后面的字段按null索引orderby，不排序，尽量只用内存临时表使用SQL_BIG_RESULT5.1给groupby后面的字段加索引。如何保证groupby后面的字段值从头开始排序？当然是加索引了。我们回到这个SQLselectcity，count(*)asnumfromstaffwhereage=19groupbycity;如果我们将联合索引idx_age_city(age,city)altertablestaffaddindexidx_age_city(age,city)添加到其执行计划中；然后查看执行计划发现不需要排序，不需要临时表。为图像添加合适的索引是最简单有效的优化groupby的方法。5.2orderbynull不是所有的场景都适合索引不排序。如果遇到不适合做索引的场景，我们该如何优化呢？如果您的需求不需要对结果集进行排序，可以使用orderbynull。selectcity,count(*)asnumfromstaffgroupbycityorderbynull，执行计划如下，没有filesort了，表放不下数据，所以用磁盘上的临时表比较耗时。因此可以适当调整tmp_table_size参数，避免使用磁盘临时表。5.4使用SQL_BIG_RESULT优化数据量过大怎么办？你不能无限增加tmp_table_size，对吧？但是不能眼睁睁看着数据先放到内存临时表中，数据插入到上限后，再转成磁盘临时表。正确的？这有点不聪明。因此，如果估计数据量比较大，我们使用SQL_BIG_RESULT提示直接使用磁盘临时表。MySQl优化器发现磁盘临时表是作为B+树存储的，其存储效率不如数组高。因此，示例SQl会直接存储在一个数组中，如下：selectSQL_BIG_RESULTcity,count(*)asnumfromstaffgroupbycity;在执行计划的Extra字段可以看到，执行没有使用临时表，只是排序执行过程如下：初始化sort_buffer，放入city字段；扫描表staff，依次取出city的值，存入sort_buffer；扫描完成后，对sort_buffer的city字段进行排序，得到有序数组。根据排序后的数组，统计每个值出现的次数。6、如何优化生产慢的SQL最近遇到了一个和groupby相关的生产慢的SQL。让我告诉你如何优化它。表结构如下：CREATETABLE`staff`(`id`bigint(11)NOTNULLAUTO_INCREMENTCOMMENT'主键id',`id_card`varchar(20)NOTNULLCOMMENT'ID号',`name`varchar(64)NOTNULLCOMMENT'name',`status`varchar(64)NOTNULLCOMMENT'Y-activatedI-initializedD-deletedR-reviewing',`age`int(4)NOTNULLCOMMENT'age',`city`varchar(64)NOTNULLCOMMENT'city',`enterprise_no`varchar(64)NOTNULLCOMMENT'企业编号',`legal_cert_no`varchar(64)NOTNULLCOMMENT'法人编号',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=15DEFAULTCHARSET=utf8COMMENT='员工表';查询SQL是这样的：select*fromt1wherestatus=#{status}groupby#{legal_cert_no}先不讨论这个SQL的=是否合理。如果是这样的SQL，你会怎么优化呢？有想法的朋友可以留言讨论，也可以加我微信进群一起讨论。如果觉得文章不对，也可以提出来。让我们一起进步。快点。微信公众号“捡蜗牛的小男孩”可以通过以下二维码关注。转载请联系捡蜗牛的小男孩公众号。
