KES数据库优化：外连接消除引发LEFT JOIN数据丢失

在国产数据库迁移或线上SQL调优过程中，不少开发人员都遇到过类似问题：明明写了LEFT JOIN，预期返回左表全部数据，结果实际记录数却少了一大截。排查执行计划时发现，原本的左外连接被优化器悄然转换成了INNER JOIN。这种现象在数据库领域称为外连接消除。

KES内核内置了一套与Oracle对齐的等价改写逻辑，如果对底层转换规则不够了解，线上业务很容易出现数据统计偏差。下面将直接使用可在KES中运行的测试SQL，配合执行计划对比，详细说明触发条件、底层逻辑以及规避方法。这套内容适用于国产化迁移和日常SQL优化等场景。

先创建两张测试表来复现问题，直接复制执行即可：

-- 创建左表t1
CREATE TABLE t1 (id1 INT PRIMARY KEY,name1 VARCHAR(20));
-- 创建右表t2
CREATE TABLE t2 (id2 INT PRIMARY KEY,name2 VARCHAR(20));
-- 插入测试数据
INSERT INTO t1 VALUES (1, '张三'),(2, '李四'),(3, '王五'),(4, '赵六');
INSERT INTO t2 VALUES (1, 'cc'),(2, 'dd');
-- 查看原始数据
SELECT * FROM t1;
SELECT * FROM t2;

原始数据展示如下。t1表共有四条记录：

id1	name1
1	张三
2	李四
3	王五
4	赵六
t2表只有两条匹配数据：
id2	name2
-----	-------
1	cc
2	dd
本次业务需求很简单：取出t1的全部四条数据，关联匹配t2里name2等于cc的内容。对于没有匹配的行，t2字段显示NULL即可。

二、踩坑案例：错误写法触发外连接消除

1. 有问题的SQL写法：过滤条件放在WHERE子句

SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE t2.name2 = 'cc';

预期的查询结果应为四条：id1等于1的行带出cc，其余三条t2字段全部为空：

id1	name1	id2	name2
1	张三	1	cc
2	李四	NULL	NULL
3	王五	NULL	NULL
4	赵六	NULL	NULL
但实际在KES中执行，只会返回单条数据，另外三条直接消失：
id1	name1	id2	name2
-----	-------	-----	-------
1	张三	1	cc

使用EXPLAIN ANALYZE查看执行计划，可以确认优化器所做的转换：

EXPLAIN ANALYZE SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE t2.name2 = 'cc';

打印出的计划关键片段如下：

Hash Join (Inner Join)  Hash Cond: (t1.id1 = t2.id2)
  -> Seq Scan on t1
  -> Hash
       -> Seq Scan on t2
            Filter: (name2 = 'cc'::character varying)

从执行计划可以清楚看到，算子已变为内连接Hash Join，这就是前面所说的外连接消除。那些右表匹配为空的数据，直接被过滤丢弃。

三、底层原理：WHERE写右表条件会触发消除的原因

1. SQL语句实际执行的先后顺序

标准SQL的执行顺序是先执行FROM和JOIN关联，之后才执行WHERE过滤。

• 第一步执行LEFT JOIN之后，t1中id3、id4这两行在t2中没有匹配项，t2所有字段都会填充NULL；
• 第二步执行WHERE t2.name2 = 'cc'时，数据库判断NULL与任意常量对比结果均不成立，这两行直接被舍弃；
• 整条语句的最终执行效果，与先做内连接再过滤没有区别。

2. 优化器的等价转换逻辑

KES优化器会自动进行等价判断，其逻辑较为直接：

• WHERE条件中，如果针对右表进行等值或范围过滤，所有NULL行最终都会被筛掉。
• 在此场景下，LEFT JOIN和INNER JOIN输出的数据完全一致。
• 内连接的计算开销更低，无需额外保留空行，优化器会自动替换连接类型，即外连接消除。

四、不会触发外连接消除的特殊场景

只有当WHERE条件使用IS NULL、IS NOT NULL这类判断时，优化器不会改动LEFT JOIN。

以下是一段查询左表无匹配记录的示例SQL：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE t2.name2 IS NULL;

该语句执行结果如下：

id1	name1	id2	name2
2	李四	2	dd
3	王五	NULL	NULL
4	NULL	NULL

对应的执行计划也保留了左连接算子：

Hash Left Join  Hash Cond: (t1.id1 = t2.id2)
  -> Seq Scan on t1
  -> Hash
       -> Seq Scan on t2
Filter: (t2.name2 IS NULL)

原因不难理解，IS NULL本身就是用来抓取关联后空行的逻辑，如果转为内连接，这部分数据会直接丢失，优化器不会做这种转换。

五、KES中三种标准解决办法，规避数据丢失

方案1：将右表过滤条件移到ON后面（优先推荐）

整体思路调整为先过滤右表数据，再执行左关联，左表所有记录都会保留，不会触发消除：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 AND t2.name2 = 'cc';

执行结果符合业务预期，四条数据全部存在：

id1	name1	id2	name2
1	张三	1	cc
2	李四	NULL	NULL
3	王五	NULL	NULL
4	NULL	NULL

执行计划也显示Left Join算子完整保留：

Hash Left Join  Hash Cond: (t1.id1 = t2.id2)
  -> Seq Scan on t1
  -> Hash
       -> Seq Scan on t2
            Filter: (name2 = 'cc'::character varying)

2. Oracle兼容(+)语法场景专用写法

KES支持Oracle旧式(+)左连接语法，此处也容易踩同类坑。

错误写法：过滤条件不带(+)，会触发外连接消除：

SELECT * FROM t1, t2 WHERE t1.id1 = t2.id2(+) AND t2.name2 = 'cc';

正确写法：右表字段同步加上(+)，过滤逻辑下沉到关联阶段：

SELECT * FROM t1, t2 WHERE t1.id1 = t2.id2(+) AND t2.name2(+) = 'cc';

3. 过滤条件放在左表WHERE不受影响

如果WHERE中过滤的是左表字段，不会触发外连接消除，只会提前筛选左表原始数据：

-- 只筛选t1中姓名等于张三的数据，左关联特性不会改变
SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE t1.name1 = '张三';

六、线上SQL排查与核对技巧

日常调优或迁移排查中遇到LEFT JOIN行数不对的情况，可以按以下步骤定位问题：

• 1 先查看执行计划中的连接算子名称

正常保留左连接：Hash Left Join / Nested Loop Left Join。

出现消除：只显示Hash Join、Nested Loop，不带Left标识。

• 2 SQL书写规范整理

右表的等值、区间、模糊匹配过滤，统一写到ON子句；右表判空或非空，在WHERE中书写没有问题；左表的过滤条件，写在WHERE或ON中均可。

• 3 迁移阶段优先保证数据准确

从Oracle向KES迁移时，不要单纯依赖优化器自动改写，数据正确性比查询速度更为重要。

七、全文总结

• 触发外连接消除的核心原因：WHERE子句对LEFT JOIN的右表进行非空类等值或范围过滤，NULL行会被全部过滤，优化器自动替换为内连接。
• 唯一不会触发转换的情况：WHERE条件使用IS NULL或IS NOT NULL判断右表字段。
• 最稳妥的修复方式：将右表的过滤条件移动到JOIN后面的ON条件中。
• 使用Oracle(+)兼容语法时：右表过滤字段也要同步带上(+)标识。
• 线上排查异常数据：直接使用EXPLAIN ANALYZE查看连接算子，快速确认是否发生外连接消除。