摘要：查询优化器是关系数据库系统的核心模块，也是衡量整个数据库系统成熟度的“试金石”。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 PWHERE P.play_date BETWEEN M.start_date AND M.end_dateGROUP BY M.PKHAVING 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 改写。
