SQL 改写系列七：谓词移动

系列文章导读

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

专栏作者介绍

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

系列内容构成

本次查询改写系列不仅包括子查询优化、聚合函数优化、 窗口函数优化、 复杂表达式优化四大模块，另外还有更多模块内容，敬请期待！本文将通过介绍谓词移动的部分下推和上拉场景，来帮助读者快速理解这两个策略的概念和作用。欢迎关注 OceanBase 开源用户群 （钉钉号：33254054），进群与 OceanBase 查询优化器团队一同交流。

一、 引言

优化谓词推导提升查询性能是至关重要的。谓词可以出现在一个 SQL 的多个位置上，可以是主查询的 Where/Having 子句；也可以是子查询的 Where/Having 子句。上一篇文章《SQL 改写系列六：谓词推导》介绍了谓词推导只能对每个字句进行独立优化；无法对它们进行综合优化。本文将重点介绍谓词移动技术，它可以改进一个谓词在 SQL 中出现的位置，也可以综合多个位置上的谓词进行推导优化。

二、谓词下推

谓词用于过滤数据。一般来说，尽早地进行数据过滤，可以减少中间结果集的大小，减少后续计算需要处理的数据量。因此，谓词的一个重要优化点是谓词下推。这个优化策略的主要作用是，尽可能的“下压”谓词，提前过滤掉部分数据。

在这一节中，我们将以 Q1 为例，介绍谓词下推的几种场景。



Q1: 
SELECT V1.C1, V2.C2, V1.M1, V2.M2 
FROM
    (SELECT C1, 0 AS C2,  MAX(C3) as M1 FROM T1 GROUP BY C1) V1,
    (SELECT C1,      C2,  AVG(C3) as M2 FROM T2 GROUP BY C1, C2 HAVING M2 > 0) V2
WHERE V1.C1 = V2.C1 AND V1.C2 = V2.C2 AND V1.M1 > V2.M2 AND V1.C1 > 10;

Q1 的 FROM 部分是两个内联视图 V1 和 V2。Q1 主查询的 WHERE 部分有多个谓词。其中 V1.C1 > 10是视图 V1上的单表过滤条件。谓词下推可以将它推入到视图内部。

1、谓词推入视图

显然，我们可以将 V1.C1 > 10 这个谓词移到 V1 内部。在这里，由于 V1 存在分组聚合操作，因此，从外层下推的谓词是放到 Having 子句中。

V1.C1 > 10 推入视图

V1-1: SELECT C1, 0 AS C2,  MAX(C3) as M1 FROM T1 GROUP BY C1 HAVING C1 > 10

经过谓词下推之后，V1 的输出保证了 C1 > 10是必然成立的。因此，原始主查询中 V1.C1 > 10的判定结果是恒真的，这是一个冗余的谓词，可以直接移除。

2、谓词推入分组

Having 子句中的谓词还可以尝试进一步下推。可以看到， C1 > 10是分组列上的谓词。它在分组之前或者分组之后执行产生的效果是完全相同的。我们将其压入到 Where 子句中，可以提前过滤掉部分数据，减少 GROUP BY 操作需要处理的数据量。

C1 > 10 推入 WHERE

V1-2: SELECT C1, 0 AS C2, MAX(C3) AS M1 FROM T1 WHERE C1 > 10 GROUP BY C1;

当然， 并不是所有 Having 中的谓词都可以下推到 Where 中。V2 中存在一个谓词 M2 > 0，这个谓词引用了一个聚合函数 AVG(C3)。聚合函数的结果依赖于分组聚合计算。因此，这个谓词无法下压到分组操作之前。

3、谓词下推和谓词推导

Q1 原始查询中只有 V1 和 V2 的连接谓词、以及 V1 上的谓词。但通过上一篇文章中介绍的谓词推导技术，我们可以推导出 V2 的过滤谓词：V2.C1 > 10。这个推导谓词同样可以压入到 V2 中，优化 V2 的计算。可以看到，谓词推导和谓词下推是相互作用的。推导可以产生更多的下推的机会。同样的，下推之后，也可以产生更多的推导机会。利用这两种优化策略，我们可以将 Q1 优化为以下查询：

Q1 初步优化结果：
SELECT V1.C1, V1.C2, V1.M1, V2.M2 
FROM
 (SELECT C1, 0 AS C2, MAX(C3) AS M1
  FROM T1
  WHERE C1 > 10
  GROUP BY C1) V1,
 (SELECT C1, C2, AVG(C3) AS M2
  FROM T2
  WHERE C1 > 10
  GROUP BY C1, C2 HAVING M2 > 0) V2
WHERE V1.C1 = V2.C1 AND V1.C2 = V2.C2 AND V1.M1 > V2.M2;

通过谓词推导和下推，我们对 Q1 进行了一定的优化。那以上结果是否为优化的最终答案呢？答案是否定的。接下来，我们将介绍另外一种重要的优化技术：谓词上拉。它可以进一步优化查询。

三、谓词上拉

本节介绍另外一种谓词的优化技术--谓词上拉。从名字理解，读者可能会直观地认为这是一种反向的优化策略，用来延迟谓词的过滤。笔者需要强调，这种理解是错误的。谓词上拉并不是谓词下推的反向优化。它主要是将内层子查询中的谓词“上拉”到外层查询中，参与外层的谓词推导过程，帮助生成更多有意义的谓词。接下来，我们继续通过分析 Q1 的优化点，来向读者介绍一些谓词上拉的场景。

1、谓词上拉

V2 视图中存在一个 Having 谓词 M2 > 0。对主查询来说，V2 的输出必然满足了 V2.M2 > 0；结合主查询中已有谓词 V1.M1 > V2.M2，我们可以推导得到 V1.M1 > 0。显然，这个推导谓词可以下推到 V1 视图中。读者可以回忆上一篇中介绍的推导技术。V1根据 MAX(C3) > 0可以进一步推导产生一个 Where 过滤谓词 C3 > 0。

上拉 M2 > 0，推导 M1 > 0 并推入 V1，再次推导生成 C3 > 0 

V1-3: SELECT C1, 0 AS C2, MAX(C3) AS M1 
      FROM T1 WHERE C1 > 10 AND C3 > 0
      GROUP BY C1

2、常量上拉

V1 视图中存在一个特殊的投影表达式 0 AS C2。对主查询来说，V1 的输出必然满足了 V1.C2 = 0；结合主查询中已有的谓词 V1.C2 = V2.C2，我们可以推导得到 V2.C2 = 0。显然，这个谓词也可以推入到 V2中。



上拉 V1.C2 = 0，推导 V2.C2 = 0 并推入 V2

V2-1: SELECT C1, C2, AVG(C3) AS M2 
      FROM T2 WHERE C1 > 10 AND C2 = 0
      GROUP BY C1, C2 HAVING M2 > 0

可以看到，谓词上拉主要作用是把一个内联视图中的谓词“上拉”到主查询中，它可以参与主查询的谓词推导，帮助产生更多新谓词。这些新生成的谓词又会获得新的下压机会。综合三种优化技术，Q1 可以最终被优化为：

Q1 最终优化形式：
SELECT V1.C1, V1.C2, V1.M1, V2.M2 
FROM
 (SELECT C1, 0 AS C2, MAX(C3) AS M1 
  FROM T1
  WHERE C1 > 10 AND C3 > 0 
  GROUP BY C1) V1, 
 (SELECT C1, C2, AVG(C3) AS M2
  FROM T2
  WHERE C1 > 10 AND C2 = 0
  GROUP BY C1, C2 HAVING M2 > 0) V2
WHERE V1.C1 = V2.C1 AND V1.M1 > V2.M2;

四、总结

本文主要介绍了谓词移动的技术。它包含两个方面，谓词下推和谓词上拉。前者负责尽可能提前去执行过滤谓词，减少后续操作需要处理的数据量；后者主要构造更多的谓词来参与推导过程，强化推导的效果。谓词推导和谓词移动是互相协作、互相强化的，它们是非常重要的两项谓词优化技术。本文介绍了部分下推和上拉的场景，帮助读者理解这两个策略的概念和作用。针对谓词的优化还有很多可以介绍的内容。未来有机会，我们会继续介绍一些 OceanBase 在谓词推导或者移动方面积累的优化能力。