本文是Groupby实现过程分析的第二篇文章。第一篇文章是MySQL如何使用索引实现groupby？<-点击阅读了解MySQL内部临时表包括哪些字段？哪些字段被索引？对理解临时表的使用和文件排序实现groupby很有帮助，所以之前写过一篇内部临时表的文章。你有没有想过MySQL的内部临时表中存储了什么？?<-点击阅读本文内容基于MySQL5.7.35源码。1.概览查看groupby语句的执行计划。在输出结果的Extra列中，有四种与groupby实现相关的信息：①Usingindexforgroup-by，表示使用松散索引扫描减少了需要扫描的记录数数量，节省执行时间。②使用索引进行group-by（扫描），在松散索引扫描过程中使用顺序扫描逻辑，避免使用临时表去重记录，这种方式就是顺序松散索引扫描（这个名字不是来自官方，它是我的名字是根据这个实现的特点取的）。③临时使用；使用filesort，就是使用临时表+文件排序，先使用临时表存储分组后的数据，然后对临时表中的记录进行排序。④使用filesort，表示只使用文件排序，先对from子句的表中的记录进行排序，然后对排序后的记录进行聚合。另一种实现方式是compactindexscan，在输出结果的Extra列中找不到。如果上述四类信息都没有出现在Extra列中，并且key列的值不为NULL，说明在实现groupby的时候也使用了索引，这种实现就是compactindexscan。松散索引扫描、顺序松散索引扫描、紧凑索引扫描三种实现方式在这篇文章：MySQL如何使用索引实现groupby？<-点击阅读接下来我们就来看看③和④两种方法（临时表+文件排序，文件排序）如何实现groupby。2、准备工作本文示例SQL中使用的表结构如下：CREATETABLE`t_group_by`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`e1`enum('北京','上海','广州','深圳','天津','杭州','成都','重庆','苏州','南京','哈尔滨','沉阳','长春','厦门','福州','南昌','泉州','德清','长沙','武汉')DEFAULT'北京',`i1`int(10)unsignedDEFAULT'0',`c1`char(11)DEFAULT'',`d1`decimal(10,2)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_e1`(`e1`)USINGBTREE)ENGINE=InnoDBDEFAULTCHARSET=utf8;3、研究中使用临时表+文件排序实现groupby之前，一直有一个疑问：为什么使用了临时表后还需要对文件进行排序？之所以有这个想法是因为我知道groupby字段会在临时表中建立索引。既然建立了索引，那么临时表中的记录就已经排序好了。当我理所当然地认为在groupby上创建的索引是B-TREE索引，却完全忽略了另一个索引，即HASH索引时，问题就来了。HASH索引中的记录是不排序的，但是包含groupby的查询语句隐含了查询结果按照groupby字段排序的逻辑，所以也需要进行文件排序。在临时表中按字段分组索引的目的是为了更快地找到要更新的记录，而不是为了让记录有序。因为包含groupby的查询语句一般都有聚合函数，而聚合函数的计算结果保存在临时表中，每次从from子句的表中读取一条记录，聚合函数计算后，group会以by字段为条件，将聚合函数的计算结果更新到临时表中。由于建立索引的目的是为了搜索，因此HASH索引的搜索速度明显比B-TREE索引快，使用HASH索引就成了这种场景下最合适的选择。使用临时表+文件排序实现groupby，临时表和文件排序的目的总结如下：临时表，保存groupby分组的结果记录。文件排序，将所有分组后的结果记录写入临时表后，将临时表中的记录按照groupby字段的值进行排序。下面我们通过一个具体的例子来分析使用临时表+文件排序实现groupby的过程。示例SQL如下：selecte1,count(i1)assum_i1fromt_group_bywhered1>5452415groupbye1示例SQL在执行过程中与groupby相关关键步骤如下：词法分析&语法分析阶段，count(i1)由于sum_i1被解析为Item_sum_count类的一个实例，两个实例属性如下：args，count()函数可以联合统计多个字段，args[0]~args[N]持有count()函数参数。在示例SQL中，args[0]持有对i1字段的Item_field类实例的引用。此时Item_field类实例还没有关联到i1字段的Field类实例。计数，保存组计数。e1字段的每一个不同的值是一个组，count是组中i1字段的值不为NULL的记录数。Item_field未关联Field在查询准备阶段的第一步，将i1字段的Item_field类实例关联到t_group_by表中i1字段的Field类实例。Item_field已经关联Field步骤2，创建临时表。临时表包含e1和sum_i1字段，sum_i1字段的值为groupcount，即Item_sum_count类实例的count属性的值。临时表中的e1字段也会建立唯一索引，索引方式（HASH或B-TREE）是临时表默认的存储引擎。对于MEMORY存储引擎，索引方式默认为HASH。对于MyISAM和InnoDB存储引擎，默认的索引方法是B-TREE。执行阶段临时表+文件排序执行过程第1步，读取满足where条件的记录。server层从存储引擎中读取一条记录，判断where条件。如果读取的记录不满足where条件，则继续读取下一条记录。如果读取的记录满足条件，则转步骤2。步骤2，分组计数。对i1字段值不为NULL的记录进行分组计数。如果当前读取的记录的e1字段值与上一条记录的e1字段值不同，则意味着需要开始新的分组。初始化分组计数，将Item_sum_count类的实例属性count设置为1，如果当前读取的记录的e1字段值与上一条记录的e1字段值相同，说明还是同一个分组.要增加组计数，请将Item_sum_count类的实例属性计数加1。第三步，更新组计数到临时表。以e1字段值作为where条件，将Item_sum_count类的实例属性count的值更新到临时表中。步骤1到步骤3是一个循环执行的过程，直到从存储引擎中读取完所有满足where条件的记录，循环执行过程才会结束。第四步，对临时表中的记录进行排序。从存储引擎中读取所有满足where条件的记录后，在将数据发送给客户端之前，需要根据临时表中的e1字段值对临时表中的记录进行排序。经过上面对执行过程的分析，相信大家对group使用临时表+文件排序的执行过程可以有一个更加清晰的认识。4.只用文件排序使用临时表+文件排序，只用文件排序。这两种方法虽然都包含文件排序，但是它们的含义是不同的。临时表+文件排序，这里的文件排序就是对临时表中的记录进行排序。只用文件排序，这里的文件排序就是对from子句的表中的记录进行排序。例如selecte1,count(i1)fromt_group_bygroupbye1为t_groupby表。为什么groupby操作对from子句的表中的记录进行排序后不需要使用临时表？要回答这个问题，我们先来看看包含groupby的查询语句通常实现的两种逻辑：分组和聚合。分组就是把按字段值分组相同的记录放在一起，这样就可以知道谁是该组的第一条记录，谁是该组的最后一条记录。判断上一组的结束和新一组的开始是非常重要的。简单的。排序后的记录便于判断组聚合的起止点，以及对组内的记录进行计数、求和、求平均等各种操作。如果可以用索引（仅指B-TREE索引）实现groupby，索引中的记录已经排序了，其实相当于分了组，可以进行聚合操作直接不借助临时表进行分组。话虽如此，问题的答案也呼之欲出了。想必大家已经想到了。将from子句的表中的记录按照groupby字段的值排序后，有点类似于为groupby字段建立索引。记录排序后，进行分组，可以直接聚合，而不需要使用临时表进行分组。关于上面关于分组和聚合的描述，你可能会有一个疑问：要聚合是不是必须先分组？当然不是。从实现的角度来看，可以不分组就进行聚合。但是，如果聚合不先分组，相邻的记录可能属于不同的组，执行时需要记录多个组的聚合结果。分组越多，记录分组聚合结果消耗的内存就越多，这对MySQL来说是不能接受的。所以，在MySQL中，要聚合，必须先分组。下面我们看一下只使用文件排序实现分组的过程。以一条SQL为例分析执行过程，例子如下：selecte1,count(i1)assum_i1fromt_group_bywhered1>5452415groupbye1词法分析&语法分析阶段，查询准备阶段同上使用临时表+文件排序，同篇文章不再赘述。这里只分析执行阶段。在仅使用文件排序的执行过程的第一步中，读取t_group_by表中所有符合条件的记录并对其进行排序。Step2，读取一条已经排序好的记录，判断上一组是否结束。如果当前读取的记录的e1字段的值与上一条记录的e1字段的值不同，说明分组发生了变化，需要结束旧的分组，开始新的分组，然后转第3步，如果当前读取的记录的e1字段值与上一条记录的e1字段值相同，说明还是同一组，转第4步。第3步，结束旧记录组并开始一个新组。结束旧数据包，将e1字段值和数据包计数发送给客户端。打开一个新组。如果i1字段的值不为NULL，则将Item_sum_count类的实例属性count设置为1，否则设置为0。然后回到第2步读取下一条记录。第四步，更新当前组数。如果i1字段的值不为NULL，则将Item_sum_count类的实例属性计数加1，然后进入步骤1继续执行。然后回到第2步，读取下一条记录。第2~4步是循环执行的过程，直到读取到所有符合where条件的记录，并将所有分组的数据发送给客户端。看过使用索引实现groupby一文的朋友应该能发现，上面执行过程中的第2~4步与紧凑索引扫描方式是一样的。5、总结第3节，以一个具体的SQL为例，分析groupby的具体执行过程。分组后的数据会写入到临时表中，并为groupby字段建立一个HASH索引。因为HASH索引中的记录是没有顺序的，所以将所有分组的数据写入临时表后，需要对临时表中的记录按照groupbyfield进行排序。第4节介绍了仅使用文件排序实现分组的过程。该方法的执行过程与紧凑索引扫描类似。不同的是多了一个步骤，对from子句的表中满足where条件的记录进行排序。排序后的执行过程与紧凑索引扫描相同。本文转载自微信公众号“一树一溪”，可通过以下二维码关注。转载本文请联系艺书艺熙公众号。
