OB小优系列文章 | 聚合类相关子查询提升（下）

OceanBase 2019-12-25

1015

OB君：查询优化器是关系数据库系统的核心模块，也是衡量整个数据库系统成熟度的“试金石”。OceanBase的查询优化器历经了九年多时间的磨练，逐步提炼出一套独有的工程实践哲学。本系列文章将重点介绍聚合类相关子查询的改写机制，欢迎探讨～

传送门：

OB小优系列（一）：OceanBase查询优化器的设计之道和工程实践

OB小优系列（二）：OceanBase并行执行引擎实现

OB小优系列（三）：OceanBase查询改写的最佳实践

OB小优系列（四）：聚合类相关子查询提升（上）

引言

聚合类相关子查询（下面简称为 JA 子查询，Join Aggregation）使用的情景通常是：用户使用一个相关的子查询来计算一个统计值，然后利用该统计值来对主查询的结果进行过滤。在上一篇文章《OB小优系列文章 | 聚合类相关子查询提升（上）》中，我们介绍了JA改写来优化这类子查询，同时也指出了这种改写的场景是有局限性的。本文给出第二种形态的JA改写，它能够改写更多种类的JA子查询。

场景分析

S 君的影院在经过上一轮排片价格分析和调整后，效益有了一定的提升，同时他也对JA 改写有了非常深刻的认识。果然是知识改变命运。时间飞逝，转眼间便到了年底，S 君想了解一下过去这一年中，哪些影片为影院带来了更多的收入，因此，写了查询 Q1 找出所有在上映期间排片率不低于10%的电影，其中子查询计算了在一部电影上映期间，整个影院的总收入，其中，MOVIE
表记录所有电影的统计信息； PLAY
表记录所有排片信息。

Q1:
SELECT * FROM MOVIE M WHERE M.profit >= 0.1 *
          (SELECT SUM(P.profit) FROM PLAY P
           WHERE P.play_date BETWEEN M.start_date AND M.end_date);

有了上次的经历后，S 君知道这次的查询又是一条聚合类的相关子查询，他深知这类查询不做改写效率是很堪忧的，代价近似于 M 表行数与 P 表行数的乘积。好在他已经学了JA改写，因此并不担心。

然而，这次遇到的问题并不完全相同。他发现没有办法构造出一个 PLAY
表上的分组查询，用于计算每部电影上映期间影院的总收入。因为这个查询里面相关条件是一个非等值条件，它没有办法转换出一个分组条件。S 君并不气馁，经过一番冥思苦想，S 君探索出了JA改写第二式，找出了等价查询 Q2。

Q2:
SELECT M.* FROM MOVIE M, PLAY P
WHERE P.play_date BETWEEN M.start_date AND M.end_date
GROUP BY M.PK
HAVING M.profit >= 0.1 * SUM(P.profit);

改写理念

这种形式的JA子查询提升的核心理念是：先将 MOVIE
与 PLAY
连接，然后以 MOVIE
的主键为分组列进行聚合，聚合后再执行过滤条件。这里选择 MOVIE
的主键作为分组列的原因是通过主键可以唯一确定 MOVIE 的每一行，保证了执行结果的正确性和唯一性。相比较而言，JA改写第一式是先做了分组查询，然后将分组结果与主查询表进行连接；而JA改写第二式是先按相关条件进行连接，然后分组计算所需的统计值。基于这种区别，S 君决定将第一式命名为“聚合优先的JA改写”，第二式命名为“连接优先的JA改写”。

在连接优先的JA改写中，我们将子查询提升到了与 MOVIE 相同的层次，从而打开了更多的优化空间，包括：

如果提升后的查询中只有非等值连接条件，那么该查询需要做NEST-LOOP JOIN，但是可以选择子查询中的表作为驱动表。

如果提升后的查询中存在等值连接条件，那么除了NEST-LOOP JOIN，还可以选择HASH JOIN或MERGE JOIN的连接方式。

实际上子查询除了出现在WHERE中还可以出现在SQL的其它位置，如SELECT中，那么出现在其它位置的子查询是否也能使用JA子查询提升改写呢，各位读者可以思考一下。

总结

本文主要介绍了JA改写第二式——连接优先的子查询提升。相对于第一式，它具有更加广泛的应用场景。它可以改写包含非等值相关条件的 JA 子查询。理论上，第一式可以改写的查询都可以被第二式改写。在发现了 JA 改写第二式之后，S 君更加困惑了。为什么在上个场景中给出的解决方案是 JA 改写第一式。在什么场景下使用聚合优先的改写，在什么场景下使用连接优先的改写？其实这并不复杂，优化器在选择改写方式时会分别计算两式 JA 改写的代价，并选择代价更低的 JA 改写。

We are Hiring!

OceanBase 九年如一日，不忘初心，砥砺前行，致力于实现一个中国人完全自主设计的分布式通用数据库系统，打破西方大厂在商业数据库领域的绝对垄断地位。时至今日，OceanBase 已经成功应用于蚂蚁金服的交易、支付、账务等核心系统和网商银行、印度Paytm等业务系统。

非常欢迎有志于让中国乃至全世界的企业用上中国人自己的通用商业数据库的同学加入我们，一起为实现这一目标而共同努力！发送简历到 OceanBase-Public@list.alibaba-inc.com，我们等的就是你！

直播报名啦！OceanBase征战TPC-C测试技术细节全解析

OceanBase在整个TPC-C测试的准备、优化和审计过程中，踩过了很多坑，也收获了很多经验。12月26日晚7点半，蚂蚁金服技术专家寒晖将为大家分享关于OceanBase征战TPC-C测试的所有技术细节，希望能够让所有对TPC-C和分布式数据库感兴趣的同学能够有所收获。

根据下方海报引导，赶快报名钉钉/微信直播吧！