groupby的用法及原理详解,groupby 用法 收藏 0

从源码角度，深度解读 MySQL 优化器的 GROUP BY 优化策略

摘要：本文主要探讨了 MySQL 8.0.22 中 GROUP BY 的工作原理，并从源码角度剖析了查询优化器中的优化逻辑。

本文分享自华为云社区《》，作者：GaussDB 数据库。

在 MySQL 中，GROUP BY 功能至关重要，它允许用户依据一个或多个列的值对结果集进行分组，通常与聚合函数（如 COUNT, SUM, AVG 等）结合使用。在日常查询中，包含 GROUP BY 子句的查询效率往往较低，主要原因是 GROUP BY 操作涉及临时表的构建，这会引发频繁的磁盘 I/O 操作，或是在计算聚合函数时增加了额外的计算开销。

本文主要介绍 GROUP BY 的工作原理，并结合代码剖析 MySQL 优化器对 GROUP BY 子句的优化策略。下文将基于 MySQL 8.0.22，聚焦 GROUP BY 在优化器中的源码实现。

在 MySQL 中，查询优化器对 GROUP BY 子句进行了多种优化，以提高处理复杂聚合查询的效率。总体来说，GROUP BY 的实现方式大概分为四种：

松散索引扫描实际上就是 MySQL 利用索引扫描实现 GROUP BY，并不需要扫描所有满足条件的索引键，即可完成操作得到结果。

松散索引扫描必须满足以下条件：

• SELECT 语句访问单表；
• GROUP BY fileld，fileld 必须为索引的最左前缀；
• 查询中如果使用了聚合函数只能是 MIN() 和 MAX()。聚合函数中的列必须在索引中，并且必须紧跟在 GROUP BY 子句中的列之后；
• 查询中除了 GROUP BY 子句中引用的部分外，索引的其他部分必须是常量（聚合函数 MIN() 和 MAX() 中的列除外）；
• 对于索引中的列，必须索引完整的列值，而不仅仅是前缀。如果仅仅使用前缀，是不能用于松散索引扫描的。

为了方便理解，我们可以创建一张 orders 表，包含一个二级索引。

我们先来看看这条 SQL 语句的执行计划，group by customer_id 可以使用二级索引，并且可以满足松散索引的条件。在执行计划的 Extra 列中显示Using index for group-by，表明该查询使用的是松散索引扫描。

如果查询不符合松散索引扫描的条件，仍有可能使用索引。如果 WHERE 子句与 GROUP BY 子句结合后的字段符合最左前缀原则，那么查询也可以利用索引，这种情况称为紧凑索引扫描。

例如，这条 SQL 的执行计划：group by order_date 无法使用二级索引，但 where customer_id=1 与 group by order_date 结合后的字段（customer_id，order_date）能满足最左前缀原则，因此也能走紧凑索引扫描，且走紧凑索引的过程中就完成分组操作，并且可以避免对结果进行额外的排序。

在执行计划中，如果使用了紧凑索引扫描，就会去除 Using temporary，使用 Using index 进行分组。

如果无法直接使用索引来优化分组操作，MySQL 可能会使用临时表来存储中间结果。在这种情况下，MySQL 会执行全表扫描或索引扫描，并创建一个临时表来存储每个分组的数据，同时还需要更新每个分组对应的值。如果结果集非常大甚至超过了内存的限制，MySQL 会将部分结果写入磁盘上的临时文件，然后再进行排序和分组操作。这样会导致大量的磁盘 I/O 操作，执行代价也会很大。

在 MySQL 8.0.13 以前的版本中，支持在 SQL 语句中使用带有 ASC 或 DESC 关键字的 GROUP BY 子句。此外，即使查询结果不需要排序，也会默认返回按组顺序排序的结果。但是在 MySQL 8.0.13 及其以后的版本中，GROUP BY 子句不再支持排序功能。如果 GROUP BY 走索引，那么返回的结果就是有序的；如果 GROUP BY 未走索引，那么返回的结果是无序的。

总之，MySQL 8.0.13 及其以后的版本的 GROUP BY 子句不会再对结果集做额外的外部排序操作。

MySQL使用查询优化器来决定如何执行GROUP BY查询。涉及对索引的选择、是否使用临时表等决策。

（1）JOIN

JOIN 类主要负责生成执行计划，它包含了处理带有 GROUP BY 子句的查询所需的一些关键属性：

• streaming_aggregation：表示是否使用流式聚合来处理分组操作。
• grouped：标记查询是否包含 GROUP BY 子句。如果查询中有 GROUP BY 子句，该值为 true。
• implicit_grouping：表示是否隐式分组。如果查询中没有显式的 GROUP BY 子句，但存在聚合函数（如 SUM(), AVG() 等），则视为隐式分组。
• group_optimized_away：标记是否将 GROUP BY 子句优化掉了。如果 GROUP BY 子句中的所有字段都是常量，MySQL 可以将这些字段优化掉，以简化查询处理。
• m_ordered_index_usage：是否使用有序索引进行分组或排序操作。
• group_list：group_list 用于存储 GROUP BY 子句的信息，包括分组字段。
• tmp_table_param：存储与创建临时表相关的参数，用于处理分组查询时可能需要创建的临时表。

（2）Temp_table_param

Temp_table_param 类主要用于管理内部临时表的参数和配置。当 MySQL 执行查询时，有时需要创建临时表来存储中间结果，特别是在进行复杂的连接操作、分组、排序或子查询时。Temp_table_param 类提供了创建和管理这些临时表所需的机制。临时表中涉及的 GROUP BY 的变量如下：

• precomputed_group_by：标记是否已经预先计算了分组操作。如果为 true，表示在查询执行的过程中，分组操作已经被优化或者通过索引直接完成，因此不需要创建临时表来处理分组。
• allow_group_via_temp_table：标记是否允许使用临时表来处理分组操作。如果为 true，表示 MySQL 可以创建临时表来存储分组后的结果。如果为 false，则表示 MySQL 不应使用临时表进行分组。
• sum_func_count：记录查询中聚合函数的数量。如果优化器优化掉（使用常量替换聚合函数），此值需要更新。

在 MySQL 8.0 中，查询优化器对 DISTINCT、GROUP BY 和 ORDER BY 的逻辑是一起实现的。函数 optimize_distinct_group_order() 用于优化涉及 DISTINCT、GROUP BY 和 ORDER BY 的查询。这个函数的目标是尽可能地减少排序操作和临时表的使用，从而提高查询效率。

（1）单表场景的 GROUP BY 优化

首先，代码检查是否是单表查询，并且存在 GROUP BY 子句（group_list 非空）。同时，检查是否有聚合函数（sum_func_count 为 0）。

如果存在 GROUP BY 子句并且没有 rollup 并且 GROUP BY 字段存在唯一索引，那么就不需要做 GROUP 操作。并将 group_list 清空。因为唯一索引的存在意味着每组只会对应一个结果行，无需再进行分组。这一步通过走唯一索引，可以避免额外的排序或分组操作，从而提高查询效率。

（2）去除 GROUP BY 中的常量

去除 GROUP BY 子句的常量，并检查 group_list 是否只包含简单的表达式。消除完常量后 group_list 为空并且原先就是有 GROUP BY 子句（grouped 为 true），这意味着 group_list 都被优化掉了，group_optimized_away 被设置为 true。

（3）计算 GROUP BY 所需要的 buffer

计算结果保存在 join 的 tmp_table_param 中。

总体来看，optimize_distinct_group_order() 函数通过单表场景的优化、去除常量表达式等方面来优化排序 GROUP BY，从而提高查询效率。

在优化器无法利用索引的时候，MySQL 就必须读取需要的数据至临时表，然后通过临时表完成 GROUP BY 操作。make_tmp_tables_info() 函数的主要目的是基于查询执行计划，为涉及到的每一个 QEP_TAB（Query Execution Plan Tab）对象生成临时表的元信息。这包括确定每个临时表的列、数据类型、存储引擎、以及是否需要排序等属性。

（1）松散索引扫描优化

松散索引扫描保证了 grouping+min/max 的提前完成，此时 tmp_table_param->precomputed_group_by=true，把分组聚集结果写入第一个 tmp table。

（2）创建临时表

如果无法走索引的情况，那么需要创建临时表。根据 GROUP BY 对应的字段和查询的字段生成临时表完成 GROUP BY。

• 初始化一个临时分组对象 tmp_group
• 计算fields中隐藏字段的数量
• 创建临时表。调用 create_intermediate_table 函数来创建中间临时表。

参数解释：qep_tab[curr_tmp_table]：指向当前要创建临时表的 QEP_TAB 对象的指针。*curr_fields：指向当前字段列表的指针。tmp_group：临时分组对象。

• 设置当前创建的临时表为执行临时表
• 临时表已经分组的，在某些情况下将分组列表转换为排序列表。如果临时表已经被分组，并且没有明确的 ORDER BY 子句，但需要保持分组结果的排序，那么它会将分组列表用作排序列表，以确保输出结果按照分组字段的顺序排列。这样做可以保证查询结果的一致性和预期的排序行为。

为了解决客户从 MySQL 5.7 版本迁移到 GaussDB(for MySQL) 的兼容性问题，GaussDB(for MySQL) 支持 GROUP BY 隐式排序能力和带有 ASC/DESC 关键字的 GROUP BY 子句的排序功能。

开关 rds_compatibility_mode 设置方式如下：

• ALLOW_GROUP_BY_IMPLICIT_SORTING：是否打开 group by 隐式排序
• ALLOW_GROUP_BY_ASC_DESC：兼容 GROUP BY field ASC/DESC 语法

GaussDB(for MySQL) 支持并行查询处理，这包括对带有 GROUP BY 子句的查询的并行处理。对于带有 GROUP BY 子句的查询，MySQL 可以并行处理不同的分组，从而加速查询执行。并行查询详细介绍见。

GaussDB(for MySQL) PQ 使用方式：

GROUP BY 走 PQ 的执行计划如下：

• 确保 GROUP BY 子句中的所有列都包含在一个索引中，尽可能走索引，尽量避免临时表的使用。
• 如果查询中还包括聚合函数（如 SUM()、AVG() 等），确保这些函数涉及的列也在索引中，这样可以避免回表操作。

如果查询不需要聚合函数，尽量避免使用它们，因为这会增加计算负担。

尽量减少 GROUP BY 子句中涉及的列的数量，这可以减少中间结果集的大小，从而提高性能。

• tmp_table_size：内存临时表内存大小， 默认是 16M。增加内存临时表的大小，尽量避免走磁盘。
• max_heap_table_size：内存临时表内存大小， 默认是 16M。增加内存临时表的大小，尽量避免走磁盘
• internal_tmp_mem_storage_engine：磁盘临时表默认存储引擎，允许的值为 TempTable 和 MEMORY。
• sort_buffer_size：控制排序操作时使用的缓冲区大小。增加排序操作的缓存大小，可以提高排序操作的性能。

MySQL 8.0 引入了一个更严格的 SQL 模式 ONLY_FULL_GROUP_BY，它要求任何未在 GROUP BY 子句中列出的非聚合列都不能在 SELECT 列表中出现。这增加了查询的正确性和一致性。

本文主要探讨了 MySQL 8.0.22 中 GROUP BY 的工作原理，并从源码角度剖析了查询优化器中的优化逻辑。此外，本文还介绍了 GaussDB(for MySQL) 对 GROUP BY 的兼容性以及并行查询方面的支持。最后，本文提供了实用的优化建议，以帮助提高带有 GROUP BY 子句的查询性能。

点击关注，第一时间了解华为云新鲜技术~

探索“Groupby”函数：从入门到上手，轻松掌握其使用方法！

Groupby函数是新增的一个超强大分类汇总函数，它有8个参数，小编录Groupby教程也用了上中下三集才说明白。所以今天也只能先说入门级用法。

1、分类汇总

L5公式

=GROUPBY(G4:H25,I4:J25,SUM,3)

公式说明：

• G4:H25是行字段，根据G列和H列进行统计
• I4:J25 是值字段
• SUM是求和，如果换成Count则会计数
• 3是生成标题行，如果是1则不生成，如果参数不选标题行，则对应的参数是0或2

2、排序

根据第3列倒序排列

=GROUPBY(B2:B23,C2:D23,SUM,3,,-3)

注：第6个参数表示根据哪一列排序，如果倒序排列则设置为负数。

3、添加总计小计

=GROUPBY(A2:B23,C2:C23,SUM,3,2)

注：当第5个参数为2时，添加总计和小计行

4、按月汇总

=GROUPBY(MONTH(A3:A23)&\”月\”,D3:E23,SUM,0)

注：groupby的参数可以用处理后的数组，所以这里用month提取月份。

5 筛选（不统计指定的值）

不统计A产品

=GROUPBY(B2:B23,C2:D23,SUM,3,,,B2:B23<>\”A\”)

注: 第7个参数可以设置条件，排除掉不统计的行

Pandas高级教程之：GroupBy用法

pandas中的DF数据类型可以像数据库表格一样进行groupby操作。通常来说groupby操作可以分为三部分：分割数据，应用变换和和合并数据。

本文将会详细讲解Pandas中的groupby操作。

分割数据的目的是将DF分割成为一个个的group。为了进行groupby操作，在创建DF的时候需要指定相应的label：

默认情况下，groupby的轴是x轴。可以一列group，也可以多列group：

在0.24版本中，如果我们有多index，可以从中选择特定的index进行group：

get_group 可以获取分组之后的数据：

默认情况下，NaN数据会被排除在groupby之外，通过设置 dropna=False 可以允许NaN数据：

groupby对象有个groups属性，它是一个key-value字典，key是用来分类的数据，value是分类对应的值。

对于多级index对象，groupby可以指定group的index层级：

group第一级：

group第二级：

得到group对象之后，我们可以通过for语句来遍历group：

如果是多字段group，group的名字是一个元组：

分组之后，就可以进行聚合操作：

对于多index数据来说，默认返回值也是多index的。如果想使用新的index，可以添加 as_index = False：

上面的效果等同于reset_index

grouped.size() 计算group的大小：

grouped.describe() 描述group的信息：

下面是通用的聚合方法：

可以同时指定多个聚合方法：

可以重命名：

NamedAgg 可以对聚合进行更精准的定义，它包含 column 和aggfunc 两个定制化的字段。

或者直接使用一个元组：

通过给agg方法传入一个字典，可以指定不同的列使用不同的聚合：

转换是将对象转换为同样大小对象的操作。在数据分析的过程中，经常需要进行数据的转换操作。

可以接lambda操作：

填充na值：

filter方法可以通过lambda表达式来过滤我们不需要的数据：

有些数据可能不适合进行聚合或者转换操作，Pandas提供了一个 apply 方法，用来进行更加灵活的转换操作。

可以外接函数：

本文已收录于 http://www.flydean.com/11-python-pandas-groupby/

最通俗的解读，最深刻的干货，最简洁的教程，众多你不知道的小技巧等你来发现！

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com