SQL 改写系列六：谓词推导

系列文章导读

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

专栏作者介绍

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

系列内容构成

本次查询改写系列不仅包括子查询优化、聚合函数优化、 窗口函数优化、 复杂表达式优化四大模块，本文将会针对谓词的推导方式进行详细阐述，还有更多模块内容，敬请期待。

欢迎关注 OceanBase 开源用户群 （钉钉号：33254054），进群与 OceanBase 查询优化器团队一同交流。

一、为什么需要谓词推导

业务在访问数据库时通常只会读取部分数据，因此会指定一些谓词来过滤掉不需要的数据。实现一个查询语义时，我们可以采用多种不同的谓词组合。

例如：Q1 和 Q2 都是从数据库中读取编号为 1024 排片的余票信息。这两条查询使用了不同的谓词集合，实现了相同的查询效果。从查询的性能而言，Q2 的过滤谓词写的更好。Q2 中的 T.play_id = 1024 是一个基表过滤谓词。它可以提前过滤掉一批数据，减少参与连接的数据量。进一步的，当 TICKETS 表上存在 (play_id, sale_date, seat) 的索引时，查询优化器一方面可以确定出一个非常好的数据扫描范围；另一方面还可以利用索引序消除 ORDER BY 产生的排序操作。最终，整个查询最多只需要读取 T 表的 10 行数据。

Q1:
SELECT P.show_time, T.ticket_id, T.seat
FROM PLAY P, TICKETS T
WHERE P.play_id = T.play_id AND P.play_id = 1024 AND T.sale_date is NULL
ORDER BY T.seat LIMIT 10;

Q2:
SELECT P.show_time, T.ticket_id, T.seat
FROM PLAY P, TICKETS T
WHERE T.play_id = 1024 and P.play_id = 1024 AND T.sale_date is NULL
ORDER BY T.seat LIMIT 10;

为了保证良好的查询性能，数据库内核需要有能力为 Q1 来查询推导出 T.play_id = 1024 这样的谓词。这种能力我们称之为“谓词推导”。在 OceanBase 中，我们针对不同的谓词使用场景，设计和实现了多种谓词推导的策略。下文将主要介绍这些推导策略。

二、谓词推导

谓词推导是根据多个谓词，推导得到一些新的谓词。例如，Q1 中存在 P.play_id = T.play_id 和 P.play_id = 1024 两个谓词，可以推导得到一个新的谓词 T.play_id = 1024。这是一个 T 表上的单表过滤谓词，它可以提前过滤掉 T 表上的数据，减少参与多表连接的数据量。推导出新谓词在很多优化场景都是有意义的。

大小比较推导

给定多个大小比较的谓词，我们可以排列出多个表达式之间的大小关系。例如，下面这个查询中，存在 T1.C1 > T2.C1 和 T1.C1 < 10 两个谓词，那么我们可以排列出它们之间的大小关系为：T2.C1 <T1.C1 < 10 。显然，针对这种场景，我们可以推导出一个新的谓词 T2.C1 < 10 。这个谓词可以提前过滤 T2 表的数据，减少参与连接的数据量。

SELECT * FROM T1, T2 WHERE T1.C1 > T2.C1 AND T1.C1 < 10;

SELECT * FROM T1, T2 WHERE T1.C1 > T2.C1 AND T1.C1 < 10 AND T2.C1 > 10;

对 Q1 查询来说，我们也是可以根据谓词给定的大小关系（T.play_id = P.play_id = 1024），推导出一个新的谓词 T.play_id = 1024。进一步的，推导出新的谓词后，我们还可以消除一个冗余的连接谓词 P.play_id = T.play_id，最终得到查询 Q2。

复杂谓词推导

除了大小比较、等值比较的谓词外，查询中也经常会用到一些更加复杂的谓词。例如，使用 LIKE对字符串进行前缀匹配等。给定一个复杂谓词和一些等值比较关系，我们也可以推导出一些新的谓词出来。例如，下面的查询中存在 T1.C1 = T2.C1和 T1.C1 LIKE 'ABC%'两个谓词。由于T1.C1和 T2.C1存在等值关系，因此，T2.C1 LIKE 'ABC%'也必然成立。这个谓词同样可以提前过滤 T2 表的数据，减少参与连接的数据量。

SELECT *
FROM T1, T2 WHERE T1.C1 = T2.C1 AND T1.C1 LIKE 'ABC%';

SELECT *
FROM T1, T2 WHERE T1.C1 = T2.C1 AND T1.C1 LIKE 'ABC%' AND T2.C1 LIKE 'ABC%';

给定两个列之间的等值关系，以及其中一个列上的任意谓词，我们几乎可以推导产生另外一个列上的谓词。但这并不意味着，我们总要推导出新谓词。一些复杂谓词本身的计算代价可能就比较高昂，并且谓词本身的过滤性并不好，推导产生新的复杂谓词反而会导致查询性能下降。实际在决策时，应该首先判断推导得到的新谓词是否可以过滤掉掉大量的数据。

OR 谓词推导

OR 谓词在业务查询中也很常见。下面这个查询中，存在一个很有趣的 OR 谓词。首先，这个谓词引用到了多个表的数据，因此，这个谓词只能过滤多表连接之后的结果。有趣之处在于：这个 OR 的每个分支中，都包含了 T1 表上的谓词。我们可以构造出 T1 表上的过滤谓词：T1.C2 = 1 OR T1.C2 =2 。这是一个单表过滤谓词，可以提前过滤 T1 的数据，减少参与连接的行数。

SELECT * FROM T1, T2 
WHERE T1.C1 = T2.C1 AND 
     ((T1.C2 = 1) OR (T1.C2 = 2 AND T2.C2 = 2))
     
SELECT * FROM T1 ,T2
WHERE T1.C1 = T2.C1 AND 
      (T1.C2 = 1 OR T1.C2 = 2) AND
      ((T1.C2 = 1) OR (T1.C2 = 2 AND T2.C2 = 2));

MIN/MAX 谓词推导

以上两种场景的推导相对而言是比较直观的。下面我们介绍一种更“隐晦”的谓词推导方式。

在下面这个查询中，存在一个 MAX(C2) > 10的 HAVING谓词。根据这个谓词，我们可以推导产生一个 C2 > 10 的过滤谓词。这里的合理性在于：原始查询最终仅保留 MAX(C2) > 10 的分组聚合结果，如果给定一行不满足 C2 > 10，存在以下两种情况：

1、这一行不是同一分组内 C2的最大值（对分组聚合没有意义，可以过滤）

2、这一行是同一分组内 C2的最大值（会被 HAVING 谓词过滤）

在以上两种情况下，不满足 C2 > 10 的数据都是可以提前过滤的。因此，我们可以推导出一个新的谓词 C2 > 10。

SELECT C1, MAX(C2)
FROM T1
GROUP BY C1 HAVING MAX(C2) > 10;

=>

SELECT C1, MAX(C2)
FROM T1
WHERE C2 > 10
GROUP BY C1 HAVING MAX(C2) > 10;

类似的，给定如下带 MIN 聚合函数的查询，我们也可以推导产生一个新的谓词。这些谓词可以提前过滤掉部分数据，减少分组聚合操作的计算量，提升查询性能。

SELECT C1, MIN(C2)
FROM T1
GROUP BY C1 HAVING MIN(C2) < 10;

=>

SELECT C1, MIN(C2)
FROM T1
WHERE C2 < 10
GROUP BY C1 HAVING MIN(C2) < 10;

这种推导方式对查询的形态是有较多性质的。读者可以考虑一下，如果查询中存在其他的聚合函数，是否还可以做如上的谓词推导？

推导陷阱

推导新谓词也存在一些容易犯错的陷阱。例如：考虑如下查询 Q3，我们是否可以根据 T1.C_CI = 'A' 和 T1.C_CI = T2.C_BIN 推导产生一个新的谓词 T2.C_BIN = 'A' ？

这种推导方式是错误的。

这是因为，这里的谓词进行比较时，比较的方式并不相同。在 T1.C_CI = 'A' 中，字符串比较时大小写不敏感的，即：'a', 'A' 等均满足过滤条件。但 T1.C_CI = T2.C_BIN 是按照大小写敏感的方式比较字符串。结合这两个谓词，仅能推断出：T2.C_BIN的取值为 'a' 或者 'A'。但是 T2.C_BIN = 'A'是大小写敏感的比较，它会直接过滤掉取值为 'a' 的数据。因此，推导产生这个新谓词是不正确的。

CREATE TABLE T1 (C_CI VARCHAR(10) UTF8_GENERAL_CI);
CREATE TABLE T2 (C_BIN VARCHAR(10) UTF8_BIN);

Q3: SELECT * FROM T1, T2 
    WHERE T1.C_CI = 'ABC' AND T1.C_CI = T2.C_BIN;

=>

Q4: SELECT * FROM T1, T2 
    WHERE T1.C_CI = 'ABC' AND T1.C_CI = T2.C_BIN AND T2.C_BIN = 'ABC';

三、总结

本文主要介绍了一些谓词的推导方式。推导新谓词对查询优化而言是非常重要的。基于新的谓词，查询优化器可以选择更好的索引，生成更好的基表访问路径。因此，谓词推导是一项非常重要的优化技术。谓词相关的优化还有很多，在下一篇文章中，我们将介绍谓词移动的技术。它会调整谓词在查询中的位置，将谓词移动到更合理的位置，提上整个查询的性能。