外连接转AntiJoin的应用场景与限制条件 | OceanBase SQL 查询改写系列

在《SQL 改写系列：外连接转内连接的常见场景与错误》一文中，我们了解到谓词条件可以过滤掉连接结果中的 null 情形的，将外连接转化为内连接的做法是可行的，正如图1中路径(a)所示。此时，敏锐的你或许会进一步思考：当谓词成功筛选出这些因连接补的NULL行后，是否还隐藏着其他的优化空间？

答案可以参考图1中路径(b)所示：对于左外连接（LeftJoin）或右外连接（RightJoin），OceanBase会把外连接转位AntiJoin。在LeftJoin和RIghtJoin场景，驱动表（在这个例子中是t1）需要扫描被驱动表(t2)的所有行，找出所有匹配的行。 但转换成AntiJoin之后，由于AntiJoin的目的是输出没有在被驱动表中找到匹配行的驱动表中的行，因此在AntiJoin场景，只要在被驱动表中找到一行满足连接条件的数据，我们就可以认为驱动表中的行不满足输出条件，就可以停止这轮扫描。综上，我们可以知道LeftJoin/RightJoin转换成AntiJoin之后可以减少扫描被驱动表的行数。

更多内容可以查看【OceanBase 查询改写】系列

 

外连接转AntiJoin

对于左外连接和右外连接，当针对基表的过滤谓词可以筛选出因为连接而补null的行时，我们可以把外连接转化为AntiJoin。图2以最简单的过滤谓词 column is null为例描述了不同外连转AntiJoin的场景。

然而在实际查询中，谓词不会一直像 t2.c1 is null 这么简单。在一个 SQL 语句中，is null 谓词的左边可以是复杂表达式{如(t2.c1 + t2.c2) is null}，于是，我们进一步推广支持外连接转AntiJoin的谓词形态，让这个改写在复杂谓词条件下也能发生，并且结合前文提到的谓词推导和谓词移动，使更多的查询能从外连接转AntiJoin中获益。在了解复杂条件的改写之前，我们需要先了解OceanBase中空值传递的概念。

对于复杂表达式 A = b+c+d，它是由表达式 b,c,d构成的。假设当b为null的时候，表达式A也会null，那我们则认为表达式A对于表达式b是空值传递的。 在这个例子中，表达式A对于b, c, d都是空值传递的。常见的空值传递判断条件有：

1. 表达式对自身是空值传递的
2. 基本的算数表达式对其子表达式都是空值传递的
3. 一些系统函数(SQRT，LOG_TEN，LOG_TWO，FLOOR，CEIL，LEAST，GREATEST，LEAST_INNER，GREATEST_INNER，MIN，MAX，SUM等）
4. 非AND/OR/IS/IS NOT的布尔表达式

在了解空值传递这个概念后，我们可以知道：只要is null谓词左边的表达式对于被驱动表中的列是空值传递的（如 (t2.c1+t2.c2) 对于t2.c1是空值传递的)，那在t2.c1是null的时候is null谓词左边的表达式也是null，is null谓词结果为true，如此便可以把被驱动表补null的行筛选出来。

综上所述，我们知道即便是对于 (t2.c1+t2.c2) is null这样的复杂谓词，只要满足对被驱动表中的列空值传递的条件，在特定条件也可以做外连接转AntiJoin的改写。至于这个特定条件是什么，我们接着往下看。

改写限制条件

条件1：对于补null侧的基表，谓词中的列不能存在null值。

-- 影片表
MOVIE(movie_id， movie_name)
movie_id	movie_name
1					'Gone With Wind'
2					'Leon'
-- 排片表
PLAY(play_id, movie_id, time, price)
play_id	movie_id	time					price
1				1					'2022-10-01'	35
2				1					NULL					40Q1: 
SELECT 	MOVIE.movie_name,PLAY.time
FROM	 	MOVIELEFT JOIN PLAYON	MOVIE.movie_id = PLAY.movie_id;
WHERE		PLAY.time is null;-- 外连接结果
R1:
movie_name				price
'Gone With Wind'	40
'Leon'						NULL-- AntiJoin结果
R2:
movie_name				time
'Leon'						NULL

条件2：对于非补null侧的基表， 谓词中的列不应该对谓词是空值传递的。

-- 影片表
MOVIE(movie_id， movie_name)
movie_id	movie_name
1					'Gone With Wind'
2					NULL
-- 排片表
PLAY(play_id, movie_id, time, price)
play_id	movie_id	time					price
1				1					'2022-10-01'	35
2				2					'2022-10-02'	40Q2: 
SELECT 	MOVIE.movie_name,PLAY.time
FROM	 	MOVIELEFT JOIN PLAYON	MOVIE.movie_id = PLAY.movie_id;
WHERE		(PLAY.time AND MOVIE.movie_name) is null;-- 外连接结果
R3:
movie_name				price
NULL							40-- AntiJoin结果
R4:
movie_name				time	

总结

看到这里，相信你已经了解了LeftJoin和RightJoin改写为AntiJoin的优点及适用场景，相较于LeftJoin和RightJoin需要扫描被驱动表的所有行，AntiJoin在找到第一行匹配的数据后就会停止扫描被驱动表，可以减少实际扫描数据的数量，因而在执行的时候有更好的性能。我们认为，谓词能筛选出被驱动表补null的行时，就能进行半连接转Anti的改写，但是依旧需要注意数据原本就可能为null的情况。此外，借助空值传递的概念，我们把能做改写的场景从简单谓词场景推广到了复杂谓词场景。