从源码角度,深度解读 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
文章为作者独立观点不代本网立场,未经允许不得转载。