OB小优系列文章 | 聚合类相关子查询提升（下）
摘要：查询优化器是关系数据库系统的核心模块，也是衡量整个数据库系统成熟度的“试金石”。OceanBase的查询优化器历经了九年多时间的磨练，逐步提炼出一套独有的工程实 践哲学。 本系列文章将重点介绍聚合类相关子查询的改写机制，欢迎探讨～
传送门：
OB小优系列（一）：OceanBase查询优化器的设计之道和工程实践
OB小优系列（二）：OceanBase并行执行引擎实现
OB小优系列（三）：OceanBase查询改写的最佳实践
OB小优系列（四）：聚合类相关子查询提升（上）
引言
聚合类相关子查询（下面简称为 JA 子查询，Join Aggregation）使用的情景通常是： 用户使用一个相关的子查询来计算一个统计值，然后利用该统计值来对主查询的结果进行过滤。 在 上一篇文章《OB小优系列文章 | 聚合类相关子查询提升（上）》 中，我们介绍了JA改写来优化这类子查询，同时也指出了这种改写的场景是有局限性的。 本文给出第二种形态的JA改写，它能够改写更多种类的J A子查询。
场景分析
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改写，因此并不担心。
然而，这次遇到的问题并不完全相同。
他发现没有办法构造出一个
<span>P</span>LAY
表上的分组查询，用于计算每部电影上映期间影院的总收入。
因为这个查询里面相关条件是一个非等值条件，它没有办法转换出一个分组条件。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 改写。
