【华为云MySQL技术专栏】MySQL优化器中GROUP BY优化策略介绍

1. 背景介绍

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

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

2. 工作原理

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

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

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

• SELECT语句访问单表；

• GROUP BY fileld，fileld必须为索引的最左前缀；

• 查询中如果使用了聚合函数只能是MIN()和MAX()。聚合函数中的列必须在索引中，并且必须紧跟在GROUP BY子句中的列之后；

• 查询中除了GROUP BY子句中引用的部分外，索引的其他部分必须是常量（聚合函数MIN()和MAX()中的列除外）；

• 对于索引中的列，必须索引完整的列值，而不仅仅是前缀。如果仅仅使用前缀，是不能用于松散索引扫描的。

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

-- 创建表 
CREATE TABLE orders ( 
	id INT AUTO_INCREMENT PRIMARY KEY,
	customer_id INT NOT NULL,
	order_date DATE NOT NULL,
	product_id INT NOT NULL,
	quantity INT NOT NULL); 
-- 插入数据 
INSERT INTO orders (customer_id, order_date, product_id, quantity)
VALUES (1, '2024-01-01', 101, 5),
	(1, '2024-01-01', 102, 10),
	(2, '2024-01-02', 101, 3),
	(2, '2024-01-02', 103, 2),
	(1, '2024-01-03', 102, 15),
	(2, '2024-01-03', 101, 7),
	(1, '2024-01-04', 103, 1),
	(2, '2024-01-04', 102, 5);
-- 创建索引 
CREATE INDEX idx_customer_id_order_date_quantity ON orders(customer_id, order_date, quantity);

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

# 1、松散索引扫描
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 |
+----+-------------+--------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+--------------------------+

2）紧凑索引扫描

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

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

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

# 2、紧凑索引扫描
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 |
+----+-------------+--------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------------+

3）临时表

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

# 设置sql_mode(默认使用ONLY_FULL_GROUP_BY模式)
mysql> SET sql_mode =’’;
# 3、临时表
mysql> explain select customer_id, product_id from orders group by order_date;
+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+-----------------+
| id | select_type | table  | partitions | type | possible_keys                       | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | idx_customer_id_order_date_quantity | NULL | NULL    | NULL |    8 |   100.00 | Using temporary |
+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+-----------------+

4）外部排序

在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子句不会再对结果集做额外的外部排序操作。

3、GROUP BY优化源码介绍

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

1）数据结构

（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：存储与创建临时表相关的参数，用于处理分组查询时可能需要创建的临时表。

class JOIN {
  bool streaming_aggregation{false};  // 是否使用流式聚合来处理分组操作
  bool grouped;  // 标记查询是否包含GROUP BY子句
  bool implicit_grouping;  // 表示是否隐式分组
  bool group_optimized_away{false};  // 标记是否将GROUP BY子句优化掉了。
  
  enum ORDERED_INDEX_USAGE {
    ORDERED_INDEX_VOID = 0,  // No ordered index avail.
    ORDERED_INDEX_GROUP_BY,  // Use index for GROUP BY
    ORDERED_INDEX_ORDER_BY   // Use index for ORDER BY
  } m_ordered_index_usage{ORDERED_INDEX_VOID};
  
  ORDER_with_src order, group_list;
  Temp_table_param 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：记录查询中聚合函数的数量。如果优化器优化掉（使用常量替换聚合函数），此值需要更新。

class Temp_table_param {
  bool precomputed_group_by;
  bool allow_group_via_temp_table{true};
  uint sum_func_count;
}

2）优化GROUP BY

在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，从而提高查询效率。

bool JOIN::optimize_distinct_group_order() {   
    // 1、单表场景的GROUP BY优化
    if (!group_list.empty(); rollup_state == RollupState::NONE 
        list_contains_unique_index(tab, find_field_in_order_list,
                                   (void *)group_list.order)) {
      group_list.clean();
      grouped = false;
    }
  // 2、去除GROUP BY子句的常量
  ORDER *old_group_list = group_list.order;
  group_list = ORDER_with_src(
      remove_const(group_list.order, where_cond,
                   rollup_state == RollupState::NONE, simple_group, true), group_list.src);
  if (group_list.empty() && grouped) {
    group_optimized_away = true;
  }
  // 3、计算GROUP BY需要的buffer大小
  calc_group_buffer(this, group_list.order);
  send_group_parts = tmp_table_param.group_parts; /* Save org parts */
}

3）临时表

在优化器无法利用索引的时候，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子句，但需要保持分组结果的排序，那么它会将分组列表用作排序列表，以确保输出结果按照分组字段的顺序排列。这样做可以保证查询结果的一致性和预期的排序行为。

bool JOIN::make_tmp_tables_info() {
  // 1、松散索引扫描优化
  if (qep_tab && qep_tab[0].quick() &&
      qep_tab[0].quick()->is_loose_index_scan())
    tmp_table_param.precomputed_group_by =
        !qep_tab[0].quick()->is_agg_loose_index_scan();
    if (need_tmp_before_win) {
    tmp_tables++;
    // 2、创建临时表
    if (create_intermediate_table(&qep_tab[curr_tmp_table], *curr_fields, tmp_group, !group_list.empty() && simple_group))
      return true;
    // 3、设置写入函数
    setup_tmptable_write_func(&qep_tab[curr_tmp_table], & trace_this_outer);
	...
	// 4、检查group by是否必须考虑排序，由于MySQL 8.0 GROUP BY不支持排序
    // group->direction在一开始解析时就被设置为ORDER_NOT_RELEVANT
    if (exec_tmp_table->group) {
      if (order.empty() && !skip_sort_order) {
        for (ORDER *group = group_list.order; group; group = group->next) {
          if (group->direction != ORDER_NOT_RELEVANT) {
            order = group_list; /* order by group */
            break;
          }
        }
      }
      group_list.clean();
    }
    // 以下为ORDER BY排序逻辑(略)
    DBUG_PRINT("info", ("Sorting for order by/group by"));
}

4、GaussDB(for MySQL) 兼容性支持

1） GROUP 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语法

# 关闭隐式排序和语法兼容（默认）
mysql> explain select customer_id, product_id from orders group by order_date;
+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+-----------------+
| id | select_type | table  | partitions | type | possible_keys                       | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | idx_customer_id_order_date_quantity | NULL | NULL    | NULL |    8 |   100.00 | Using temporary |
+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)
mysql> set rds_compatibility_mode='ALLOW_GROUP_BY_IMPLICIT_SORTING,ALLOW_GROUP_BY_ASC_DESC';
Query OK, 0 rows affected (0.00 sec)
# 打开隐式排序和语法兼容
mysql> explain select customer_id, product_id from orders group by order_date;
+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table  | partitions | type | possible_keys                       | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | idx_customer_id_order_date_quantity | NULL | NULL    | NULL |    8 |   100.00 | Using temporary; Using filesort |
+----+-------------+--------+------------+------+-------------------------------------+------+---------+------+------+----------+---------------------------------+

2） GROUP BY支持并行查询

GaussDB(for MySQL)支持并行查询处理，这包括对带有 GROUP BY 子句的查询的并行处理。对于带有 GROUP BY 子句的查询，MySQL 可以并行处理不同的分组，从而加速查询执行。并行查询详细介绍见官网地址：https://support.huaweicloud.cn/kerneldesc-gaussdbformysql/gaussdbformysql_20_0005.html

GaussDB(for MySQL)PQ使用方式：

SET GLOBAL pq_master_enable=ON;
SET force_parallel_execute=ON;
SET parallel_cost_threshold=0;
SET parallel_rows_threshold=0;
SET parallel_default_dop=1;
# GROUP BY支持PQ需要额外开启开关
SET pq_group_having=1;

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

mysql> explain select customer_id, count(*) from orders group by customer_id;
+----+-------------+-----------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+-------------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys                       | key                                 | key_len | ref  | rows | filtered | Extra                                     |
+----+-------------+-----------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+-------------------------------------------+
|  1 | SIMPLE      | <gather1> | NULL       | ALL   | NULL                                | NULL                                | NULL    | NULL |    8 |   100.00 | Parallel execute (4 workers, test.orders) |
|  1 | SIMPLE      | orders    | NULL       | index | idx_customer_id_order_date_quantity | idx_customer_id_order_date_quantity | 11      | NULL |    8 |   100.00 | Using index                               |
+----+-------------+-----------+------------+-------+-------------------------------------+-------------------------------------+---------+------+------+----------+-------------------------------------------+

5、优化建议

（1）创建合适的索引

• 确保GROUP BY子句中的所有列都包含在一个索引中，尽可能走索引，尽量避免临时表的使用。

• 如果查询中还包括聚合函数（如SUM()、AVG()等），确保这些函数涉及的列也在索引中，这样可以避免回表操作。

（2）使用合适的聚合函数

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

（3）优化查询结构

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

（4）参数配置

• tmp_table_size：内存临时表内存大小， 默认是16M。增加内存临时表的大小，尽量避免走磁盘。

• max_heap_table_size：内存临时表内存大小， 默认是16M。增加内存临时表的大小，尽量避免走磁盘

• internal_tmp_mem_storage_engine：磁盘临时表默认存储引擎，允许的值为TempTable和 MEMORY。

• sort_buffer_size：控制排序操作时使用的缓冲区大小。增加排序操作的缓存大小，可以提高排序操作的性能。

（5）使用ONLY_FULL_GROUP_BY模式

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

6、总结

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