从源码角度,深度解读 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  华为云开发者联盟  阅读(36)  评论(0编辑  收藏  举报