本文分享自华为云社区《【华为云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 子句的查询性能。
标签:GROUP,id,索引,源码,MySQL,group,优化,order From: https://www.cnblogs.com/huaweiyun/p/18420892