更新时间:2024-07-01 GMT+08:00
分享

案例:NOT IN转NOT EXISTS

NOT IN语句需要使用nestloop anti join来实现,而NOT EXISTS则可以通过hash anti join来实现。在join列不存在null值的情况下,not exists和not in等价。因此在确保没有null值时,可以通过将not in转换为not exists,通过生成hash join来提升查询效率。

优化前

创建两个基表t1、t2:

1
2
CREATE TABLE t1(a int, b int, c int not null) WITH(orientation=row);
CREATE TABLE t2(a int, b int, c int not null) WITH(orientation=row);

执行下列SQL语句,查询NOT IN的执行计划:

1
EXPLAIN VERBOSE SELECT * FROM t1 WHERE t1.c NOT IN (SELECT t2.c FROM t2);

返回结果如图:

从返回结果可知执行计划走NestLoop,因为NULL值跟任意值的OR运算结果都是NULL,WHERE条件表达式:

1
t1.c NOT IN (SELECT t2.c FROM t2)

等价于:

1
t1.c <> ANY(t2.c) AND t1.c IS NOT NULL AND ANY(t2.c) IS NOT NULL 

优化后

可将查询可以修改为:

1
SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t2.c = t1.c);

执行下列语句,查询NOT EXISTS的执行计划:

1
EXPLAIN VERBOSE SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.c = t1.c);

相关文档