KES数据库优化踩坑指南：深度剖析外连接消除导致的LEFTJOIN数据丢失问题

一、前言

平时做国产库迁移，或是线上SQL调优的时候，不少开发同事都会碰到一种很难看懂的异常现象。我们明明写了LEFT JOIN，本意是把左表里所有数据都查出来，最后返回的记录却少了一大截。

拿执行计划去核对的时候就能发现，原来的左外连接，被优化器悄悄改成了INNER JOIN。这个现象，行业里一般叫做外连接消除。

KES内核自带和Oracle对齐的等价改写逻辑，要是不清楚这套底层转换规则，线上业务很容易出现数据统计出错的问题。

下面我准备了可以直接在KES里跑的测试SQL，搭配执行计划对比，把触发条件、背后逻辑、规避办法全部讲清楚，适配国产化迁移、日常SQL优化这类场景。

先搭好两张测试表用来复现问题，直接复制执行就行：

-- 创建左表t1CREATE TABLE t1 (    id1 INT PRIMARY KEY,    name1 VARCHAR(20));-- 创建右表t2CREATE 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表里面一共四条记录：

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

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

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

大家心里预期的查询结果应该是四条，id1等于1的行带出cc，剩下三条t2字段全部为空：

我们用EXPLAIN ANALYZE看执行计划，就能确认优化器做了转换：

EXPLAIN ANALYZESELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2WHERE 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.id2WHERE t2.name2 IS NULL;

这条语句执行出来的结果：

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

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

原因也很好理解，IS NULL本身就是用来抓取关联后空行的逻辑，要是转成内连接，这部分数据直接就没了，优化器不会做这种转换。

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

方案1 把右表过滤条件挪到ON后面（优先推荐）

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

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

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

执行计划也能看到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, t2WHERE t1.id1 = t2.id2(+) AND t2.name2 = 'cc';

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

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

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

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

-- 只筛选t1里姓名等于张三的数据，左关联特性不会变SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2WHERE 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迁移的时候，不要单纯依赖优化器自动改写，数据正确比查询速度更重要。

七、全文总结

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