【华为云MySQL技术专栏】MySQL聚合函数原理剖析
1、背景介绍
聚合函数(Aggregate Functions)是SQL中用于对一组值执行计算并返回单个值的函数。它们通常与GROUP BY子句一起使用,用于对数据进行分组统计。常见的MySQL聚合函数包括:COUNT(), SUM(), AVG(), MAX(), MIN(), GROUP_CONCAT()等。
本文将以COUNT(), SUM(), AVG()为例,结合MySQL 8.0.22源码剖析聚合函数的实现原理。
2、工作原理
MySQL主要采用以下方式实现聚合:
2.1 流式聚合
流式聚合是MySQL中一种高效的聚合查询处理方式,它允许数据库在读取数据的同时就可以进行聚合计算,而不需要先获取所有数据再执行聚合操作。
针对能够使用索引的GROUP BY操作,MySQL会采用流式聚合,利用索引的有序性,按照GROUP BY列的顺序读取数据。在读取过程中,每当遇到新的分组值时,系统会先完成当前分组的计算并输出结果,由于这种处理方式只需要保持当前分组的聚合状态,因此内存消耗较小。
2.1.1 松散索引扫描
松散索引扫描就是MySQL完全利用索引扫描实现GROUP BY,不需要扫描所有满足条件的索引键即可完成操作得到结果。例如:Using index for group-by 表明使用了松散索引扫描,只需读取索引中的部分数据而非全表。
mysql> explain select customer_id, MAX(order_date) from orders group by customer_id; +----+-------------+--------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | orders | NULL | range | idx_customer_id_order_date_quantity | idx_customer_id_order_date_quantity | 4 | NULL | 3 | 100.00 | Using index for group-by | +----+-------------+--------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
2.1.2 紧凑索引扫描
如果查询不满足松散索引扫描条件,但where条件中为group by field提供了一个常量,该常量和group by field结合一起后能适用索引,仍然能走紧凑索引扫描。如果使用了紧凑索引扫描,在执行计划中会消除Using temporary,借助索引的有序性进行分组。
mysql> explain select customer_id, MAX(quantity) from orders where customer_id=1 group by order_date; +----+-------------+--------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | orders | NULL | ref | idx_customer_id_order_date_quantity | idx_customer_id_order_date_quantity | 4 | const | 4 | 100.00 | Using index | +----+-------------+--------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------------+
2.2 无索引聚合
针对无法使用索引的 GROUP BY操作,MySQL 优化器通常会选择以下两种执行策略来完成聚合计算:
2.2.1 sort + aggregate
MySQL会先对符合条件的数据进行全表扫描,然后按照GROUP BY的列进行排序。排序完成后,数据库引擎会遍历有序数据,对相同分组的值进行聚合计算。
mysql> explain format=tree select count(distinct customer_id) from orders group by quantity; +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Group aggregate: count(distinct orders.customer_id) -> Sort: orders.quantity (cost=1.05 rows=8) -> Table scan on orders (cost=1.05 rows=8) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
2.2.2 临时表
如果GROUP BY的列不适合排序(如分组基数大),MySQL可能会选择基于临时表的聚合方式。引擎会扫描表数据,并在内存中维护一个哈希表,以GROUP BY列作为key,聚合值作为value。
mysql> explain format=tree select customer_id, MAX(quantity) from orders where customer_id=1 group by order_date; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Table scan on <temporary> -> Aggregate using temporary table -> Filter: (orders.customer_id = 1) (cost=1.05 rows=1) -> Table scan on orders (cost=1.05 rows=8) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
3、源码剖析
3.1 整体流程
MySQL聚合查询的整体流程主要分为优化和执行两个阶段。以查询select count(distinct age) from t1的为例。
优化器阶段:
-
先调用make_sum_func_list()初始化查询中所有聚合函数项
-
再通过prepare_sum_aggregators()为聚合函数分配对应的聚合器
-
最后setup_sum_funcs()初始化聚合环境,对于DISTINCT聚合会通过Aggregator_distinct::setup()创建临时表和Tree结构用于去重存储
执行器阶段:
-
先调用Aggregator_distinct::clear()清空临时结构
-
再通过Aggregator_distinct::add()遍历每行数据,更新Tree/临时表实现去重累计
-
结果输出阶段通过Aggregator_distinct::endup()计算最终聚合值
【优化器逻辑】 JOIN::optimize() |--> JOIN::make_tmp_tables_info() | |--> make_sum_func_list() | |--> prepare_sum_aggregators() | |--> setup_sum_funcs() | | |--> Aggregator_distinct::setup() // 1、设置聚合所需要的内部结构(创建临时表和tree等) 【执行器逻辑】 SELECT_LEX_UNIT::execute |--> SELECT_LEX_UNIT::ExecuteIteratorQuery() | |--> AggregateIterator::Read() | | |--> Item_sum::reset_and_add() | | | |--> Item_sum::aggregator_clear() | | | |--> Aggregator_distinct::clear() // 2、对tree和table初始化 | | | |--> Item_sum::aggregator_add() | | | | |--> Aggregator_distinct::add() // 3、遍历每一行数据,更新tree和table | |--> Query_result_send::send_data | | |--> THD::send_result_set_row | | | |--> Item::send | | | | |--> Item_sum_count::val_int | | | | | |--> Aggregator_distinct::endup // 4、计算聚合函数的返回值,并返回给客户端
3.2 聚合类型
3.2.1 Sumfunctype
聚合函数类型是通过枚举Sumfunctype 来定义的,这个枚举目前包含了大约14种不同的聚合函数类型,每种类型对应着不同的聚合计算方式。
enum Sumfunctype { COUNT_FUNC, COUNT_DISTINCT_FUNC, SUM_FUNC, SUM_DISTINCT_FUNC, AVG_FUNC, AVG_DISTINCT_FUNC, MIN_FUNC, MAX_FUNC, STD_FUNC, VARIANCE_FUNC, SUM_BIT_FUNC, UDF_SUM_FUNC, GROUP_CONCAT_FUNC, JSON_AGG_FUNC, }
3.2.2 Item_sum
Item_sum类是所有聚合函数的基类,其定义和实现位于sql/item_sum.h和sql/item_sum.cc中。该类采用继承机制,将通用的聚合逻辑(如状态管理、分组计算等)封装在基类中,而具体的聚合运算则由不同的子类实现。
Item_sum 的聚合函数主要分为两类:
-
系统内置聚合函数:如 SUM()、COUNT()、AVG()、MAX()和 MIN()等;
-
用户自定义聚合函数:允许开发者扩展自定义聚合逻辑。
每个聚合函数子类专注于实现自身的计算逻辑,从而在统一的框架下支持多样化的聚合操作。例如:
-
Item_sum_sum用于实现计算总和的功能。
-
Item_sum_count用于计算指定列的数量。
-
Item_sum_avg用于计算平均值。
-
Item_sum_min和Item_sum_max分别用于找出最小值和最大值。
主要派生类如下:
Item_sum ├── Item_sum_sum ├── Item_sum_count ├── Item_sum_avg ├── Item_sum_min ├── Item_sum_max ├── Item_sum_std ├── Item_sum_variance ├── Item_sum_bit └── Item_sum_hybrid
3.3 聚合策略
3.3.1 Aggregator
在深入探讨聚合策略前,需首先理解MySQL中Aggregator类的核心作用。作为聚合操作的基础抽象基类,Aggregator定义了所有聚合函数的统一执行框架,其主要方法如下:
class Aggregator { public: virtual bool setup(TABLE *table) = 0; virtual bool add() = 0; virtual bool endup() = 0; virtual void clear() = 0; };
-
setup():初始化聚合函数。用于分配聚合计算所需的内存资源,初始化聚合函数的上下文状态。
-
add():处理一行数据,对查询结果集的每一行调用一次。用于提取当前行的聚合列值,更新聚合状态(如累加、最大值等),并应用GROUP BY分组逻辑。
-
endup():结束聚合,计算最终结果。在所有行处理完成后,计算最终聚合值(如平均值需要由总和/计数得到),将结果写入输出,并清理临时资源。
-
clear():清空聚合结果。在开始新的GROUP BY分组时或查询执行结束时,重置所有聚合状态为初始值。
在Aggregator类的基础上,MySQL实际实现了两种聚合器:Aggregator_simple与Aggregator_distinct。下面将详细介绍两种聚合器的适合用场景。
3.3.1.1 Aggregator_simple
Aggregator_simple为简单聚合器实现,它继承自基类Aggregator。重写了三个关键的聚合操作接口:setup、clear、add。借助item_sum成员调用到各个具体聚合函数的功能模块。
class Aggregator_simple : public Aggregator { bool setup(THD *thd) override { return item_sum->setup(thd); } void clear() override { item_sum->clear(); } bool add() override { return item_sum->add(); } };
Aggregator_simple在不需要去重的场景下使用,能直接遍历数据进行聚合计算。例如:COUNT(name)会计算所有name的数量,包括重复值。
3.3.1.2 Aggregator_distinct
Aggregator_distinct专门用来处理带有DISTINCT的聚合场景。在聚合计算前,先对数据使用红黑树去重,然后再计算。例如:SUM(DISTINCT salary) 只会计算不同的salary值的总和,不包括重复值。
为了实现先去重再计算,Aggregator_distinct比Aggregator_simple多了用于去重的红黑树(tree)、临时表(table)等。因此,在初始化聚合函数时,还需分配聚合计算所需的各种内存资源。
class Aggregator_distinct : public Aggregator { TABLE *table; Temp_table_param *tmp_table_param; Unique *tree; bool setup(THD *) override; void clear() override; bool add() override; void endup() override; };
除此以外,在遍历每一行数据时,首先将数据添加到红黑树中用于去重。
bool Aggregator_distinct::add() { ... for (Field **field = table->field; *field; field++) if ((*field)->is_real_null()) return false; // Don't count NULL if (tree) { return tree->unique_add(table->record[0] + table->s->null_bytes); } ... }
最后,在进行统计时,遍历红黑树统计所有数据即可。
void Aggregator_distinct::endup() { ... if (tree && tree->is_in_memory()) { sum->count = (longlong)tree->elements_in_tree(); endup_done = true; } ... }
3.3.2 TemptableAggregateIterator
TemptableAggregateIterator专门用于处理无法通过索引优化的 GROUP BY 聚合操作。当 GROUP BY 的列没有合适的索引,或者优化器判断排序聚合(Sort + Group)效率较低时,MySQL 可能会选择临时表聚合。
-
Init():负责初始化临时表,处理分组和聚合操作,以及将结果存储回临时表中。
-
Read():读取临时表聚合迭代器中的数据。
-
End():清理资源,结束迭代。
class TemptableAggregateIterator final : public TableRowIterator { bool Init() override; int Read() override; int End() override; };
以select count(age) from t1 group by class为例:
Init() 阶段:
-
创建临时表结构
-
初始化聚合函数计算环境
-
准备去重数据结构
Read() 阶段:
-
循环处理数据行
调用栈如下:
do_command() | |--> dispatch_command() | | |--> mysql_parse() | | | |--> mysql_execute_command() | | | | |--> Sql_cmd_dml::execute() | | | | | |--> Sql_cmd_dml::execute_inner() | | | | | | |--> SELECT_LEX_UNIT::execute() | | | | | | | |--> SELECT_LEX_UNIT::ExecuteIteratorQuery() | | | | | | | | |--> TemptableAggregateIterator::Init() | | | | | | | | |--> TemptableAggregateIterator::Read()
4、典型聚合函数实现分析
4.1 COUNT函数
在MySQL中,Item_sum_count是实现COUNT聚合功能的内部核心类,用于实现计数功能。
该类的工作原理是:当执行查询时,首先初始化计数器变量count为0;然后遍历表的每一行数据,通过add()函数检查每列的值,若非NULL则使 count 值加1;最终count作为统计结果。
对于查询:SELECT COUNT(*) FROM users GROUP BY department; MySQL会为每个department创建一个Item_sum_count实例来计算该部门的员工数量。
class Item_sum_count : public Item_sum_int { longlong count; void clear() override; bool add() override; longlong val_int() override; };
4.2 SUM函数实现
Item_sum_sum类用于实现SUM功能,与Item_sum_count类似,它将遍历每一行数据,并将值累加到变量sum中。
class Item_sum_sum : public Item_sum_num { double sum; ulonglong m_count; void clear() override; bool add() override; longlong val_int() override; };
4.3 AVG函数实现
Item_sum_avg类通过组合SUM和COUNT实现平均值计算,在遍历每一行数据时,先累加到sum上,同时计数器+1。最后,返回计算得到的平均值。
class Item_sum_avg final : public Item_sum_sum { double m_avg; void clear() override; bool add() override; double val_real() override; }; bool Item_sum_avg::add() { if (Item_sum_sum::add()) return true; m_count++; } double Item_sum_avg::val_real() { return Item_sum_sum::val_real() / ulonglong2double(m_count); }
总结
本文主要介绍了MySQL聚合函数的实现机制,以及执行流程包括初始化、数据处理和结果计算等流程。并以COUNT/SUM/AVG为例,分析了典型聚合函数的实现原理。
- 点赞
- 收藏
- 关注作者
评论(0)