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

本文分享自华为云社区《【华为云MySQL技术专栏】MySQL优化器中GROUP BY优化策略介绍》，作者：GaussDB 数据库。

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 可以并行处理不同的分组，从而加速查询执行。并行查询详细介绍见官网地址。

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 子句的查询性能。

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

posted @ 2024-09-19 16:40 华为云开发者联盟阅读(37) 评论(0) 编辑收藏举报

刷新页面返回顶部

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

1. 背景介绍

2. 工作原理

1）松散索引扫描

2）紧凑索引扫描

3）临时表

4）外部排序

3、GROUP BY 优化源码介绍

1）数据结构

（1）JOIN

（2）Temp_table_param

2）优化 GROUP BY

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

（2）去除 GROUP BY 中的常量

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

3）临时表

（1）松散索引扫描优化

（2）创建临时表

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

1） GROUP BY 支持排序

2） GROUP BY 支持并行查询

5、优化建议

（1）创建合适的索引

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

（3）优化查询结构

（4）参数配置

（5）使用 ONLY_FULL_GROUP_BY 模式

6、总结

公告