系列文章导读

OceanBase 是100% 自主研发，连续7年稳定支撑双11，创新推出“三地五中心”城市级容灾新标准，是全球唯一在 TPC-C 和 TPC-H 测试上都刷新了世界纪录的国产原生分布式数据库，于 2021 年 6 月份正式开放源代码。查询优化器是关系数据库系统的核心模块，是数据库内核开发的重点和难点，也是衡量整个数据库系统成熟度的“试金石”。为了帮助大家更好地理解 OceanBase 查询优化器，我们将撰写查询改写系列文章，带大家更好地掌握查询改写的精髓，熟悉复杂 SQL 的等价性，写出高效的 SQL。本文是 OceanBase 改写系列第三篇，将重点介绍聚合类相关子查询的改写机制，欢迎探讨～进入【SQL 改写专题】 查看系列内容

专栏作者介绍

OceanBase 优化器团队，由 OceanBase 高级技术专家溪峰、技术专家山文等领衔，致力于打造全球领先的分布式查询优化器。

系列内容构成

本次查询改写系列不仅包括子查询优化、聚合函数优化、 窗口函数优化、 复杂表达式优化四大模块，还有更多模块内容，敬请期待，欢迎关注 OceanBase 开源用户群 （钉钉号：3325 4054），进群与 OceanBase 查询优化器团队一同交流。

1. 引言

年少不知优化苦，遇坑方知优化难。

不少人可能都经历过这样的场景，

在刚开始写程序的时候，我们往往只在意返回结果正确即可。

然而，系统运行一段时间后，我们会渐渐感到力不从心，

产生一堆疑问，

我写的程序为什么那么慢？

程序为什么卡住不动了？

究竟是哪个程序造成了路阻？

这时候如果我们通过各种手段去监控慢 SQL，被动应对优化，很可能事倍功半。

如果我们置之不理，很有可能导致程序瘫痪、假死、崩溃。

怎么办呢？OceanBase 教你主动出击！

工欲性能调优，必先 get SQL 改写大法！

要想 SQL 跑得快，改写文章看起来！

在前几期查询改写文章中介绍了几种子查询相关改写策略，本期我们聚焦在分组聚合（Group By）的优化上，探讨分组聚合存在的等价变化可能性。在偏分析型的场景中，分组聚合主要用于满足业务的统计分析需求，例如：统计不同年龄段的人数；统计不同年龄段的平均工资等。典型的统计分析类 SQL 会首先连接（Join）多表中的数据，然后将连接结果按照某些维度分组，最后在每个分组进行求和、计数、算极值等聚合运算。在这类请求中，通常原始表中的数据量比较庞大，连接操作本身需要处理大量数据，并且多表连接可能进一步放大数据的规模，导致分组操作也需要处理庞大的数据。

针对这类统计分析类的查询，一种有效的优化策略是调节连接和分组操作之间的执行次序，先对原始表的数据进行一定的预聚合，然后再进行多表连接和最终的分组聚合，这种优化策略称之为分组下压。

2. 分组下压

考虑下面这个统计分析类型的查询 Q1，该查询统计了某个影院中每部电影上映以来的总票房。 不妨假定，该影院开放以来，总共为 100 部影片放映了 3,000 次排片（即 PLAY 表中共有 3,000 条记录，movie_name 有 100 个不同值）；售出了 200,000 张电影票（即 TICKETS 表中共有 200,000 条记录）

-- 排片表
PLAY(play_id, movie_name, time, price, seats)
-- 售票表
TICKETS(play_id, real_price, sale_date);

Q1:
SELECT P.movie_name,
       SUM(T.real_price)
FROM PLAY P,
     TICKETS T
WHERE P.play_id = T.play_id
GROUP BY P.movie_name;

图 （一）展示了 Q1 的逻辑执行计划树。首先，我们需要将 P 和 T 连接在一起，每一张电影票都唯一对应了一次排片，所以 P 和 T 连接会产生 200,000 条记录，最后需要对连接结果再次进行分组。可以看到，这里连接和分组都需要处理 200,000 条记录。整体的代价较高。

OceanBase 会对 Q1 使用分组下压优化，将分组操作提前至连接运算前进行，这时可以构造得到相同语义的查询 Q2。

Q2:
SELECT P.movie_name,
       SUM(V.total)
FROM PLAY P,
    (SELECT T.play_id,
            SUM(T.real_price) total
     FROM TICKETS T
     GROUP BY T.play_id) V
WHERE P.play_id = V.play_id
GROUP BY P.movie_id;

以上改写的一个重要观察是：在 TICKETS 表中存在大量 play_id 取值相同的记录，它们会被划分到相同的影片分组中，我们可以对相同 play_id 的数据做一次预聚合得到每场排片的票房，然后根据每场排票的票房来统计每部影片的总票房。

可以看到，在 Q2 中，视图 V 按照 play_id 进行分组聚合，可以得到每一场排片的票房，这轮分组聚合可以大大减少数据量（从 200,000 减少到 3,000），然后将分组结果和 PLAY 表中的排片记录按照 P.play_id = T.play_id 进行连接，最后对连接结果按照 movie_name 进行分组操作，得到每部电影的总票房。

图（二）展示了 Q2 的逻辑执行计划树。可以看到，经过分组下压之后，连接和分组操作运算的数据规模显著降低至 3,000。这种方式大大提升了查询的执行性能。

3. 性能分析

在上文分组下压的例子中，不难发现下压分组操作可以显著减少后续的连接和分组操作需要运算的数据量，那么是否分组下压总是会产生更好的执行性能呢？答案是否定的，在不同的场景下，Q1 和 Q2 孰优孰劣并不确定，需要根据实际的数据规模进一步判断。这里，不妨考虑两个场景。

场景1: 影院有大量巨幕放映室并且观众基本满座，此时表 TICKETS 中存在大量 play_id 相同的购票信息。在这种情况下，Q1 和 Q2 的行为类似于上文给定的例子。Q2 会有更好的表现。这里不再赘述。

场景2: 影院多为小型放映室，受到疫情的影响，售票情况不理想，大量座位空置，甚至有些排片空场放映。此时 TICKETS 表的规模比较小，并且 play_id 相同的购票信息数量也相对较少。不妨假定，这种情况下，TICKETS 表的规模为 1000。图（三）和图（四）展示了这种数据分布下，Q1 和 Q2 的逻辑执行计划树。

在图（四）中，预聚合无法显著地减少 TICKETS 表的数据规模。这使得，后续的连接和分组操作在数据处理规模上也不会有显著的变化。相对图（三）而言，图（四）额外增加了一轮预聚合的时耗。整体上看，进行分组下压反而会导致整个查询的执行性能变差。

可以看到，在以上两种场景中，Q2 性能有时并不优于 Q1。因此，为了保证改写后查询性能不会变差，分组下压是一种基于代价的查询改写策略：在完成改写后，通过“询问”物理优化器，根据实际数据分布获得改写前后执行计划的代价，并仅在改写后代价降低时才选择触发改写。经过代价评估触发的分组下压改写，能够有效地减少连接操作处理的数据规模，在多表连接后计算分组的场景中，可以有效地提升查询效率。

4. 总结

本文主要介绍了分组下压的查询改写策略，通过将一些分组运算先于连接执行，下压到单个表中去缩小数据集，减少参与连接运算的数据规模，提升查询性能。由于某些场景分组下压后会导致查询性能变差，该策略是一个基于代价的改写，只有评估改写后代价降低才会触发。本文介绍的分组下压主要是将分组操作调整到连接操作之前。在上文的讨论中也可以看到，在一些场景中，我们也会希望先执行连接操作，后执行分组操作。这种反向的优化策略将会在后续的文章中介绍，敬请期待。

5. 附录

1、首发！OceanBase 改写系列一：OceanBase 查询改写实践概述

2、OceanBase 改写系列二: 子查询提升首篇

3、OceanBase 改写系列第三篇：如何提升子查询性能（包含聚合函数）最佳实践

最后的最后，您有任何疑问都可以通过以下方式联系到我们～

联系我们

欢迎广大 OceanBase 爱好者、用户和客户随时与我们联系、反馈，方式如下：

社区版官网论坛

社区版项目网站提 Issue

钉钉群：33254054