踩坑实战 | MySQL/PostgreSQL 迁移必踩：LEFT JOIN 莫名丢数据,KES 外连接消除避坑指南

问题背景

将业务系统从 MySQL 或 PostgreSQL 迁移到 KES 后,一类高频问题是：SQL 逐条平移、功能测试通过,上线后却发现某些用了 LEFT JOIN 的查询结果集变小,那些"右表没有匹配"的记录成批消失,回原库执行数据又完整。这不是数据同步或存储问题,而是命中了优化器的外连接消除（Outer Join Elimination）——优化器把逻辑上等价的外连接改写成了代价更低的内连接。本文说明它的触发条件、成因、例外,以及迁移时的正确写法。

现象复现

用最小数据集复现。建表并插入数据：

CREATETABLEt1(id1INT,name1VARCHAR(20));CREATETABLEt2(id2INT,name2VARCHAR(20));INSERTINTOt1VALUES(1,'a'),(2,'b'),(3,'c');INSERTINTOt2VALUES(1,'cc'),(2,'dd');

需求是查出 t1 的全部记录,并关联显示 t2 中 name2 为 ‘cc’ 的信息,匹配不上的显示 NULL。迁移前常见的写法是把过滤条件放在 WHERE：

SELECT*FROMt1LEFTJOINt2ONt1.id1=t2.id2WHEREt2.name2='cc';

按 LEFT JOIN 的语义,预期左表三行全部返回,只是不满足条件的行右表列为 NULL。实际结果却只剩一行 (1, a, 1, cc),t1 中的 b、c 两行连同它们的左表数据一起消失。

成因分析

问题出在 SQL 的执行顺序与 NULL 的三值逻辑上。WHERE 的过滤在逻辑上发生于 JOIN 之后,所以这条查询先做 LEFT JOIN,得到包含 NULL 补齐行的中间结果：id1=3 的那行因右表无匹配,name2 被补为 NULL。随后 WHERE t2.name2 = ‘cc’ 逐行筛选,name2=‘dd’ 的行因不等于 ‘cc’ 被淘汰,而 name2 为 NULL 的行,其判断 NULL = ‘cc’ 结果为 Unknown,在 WHERE 中同样不通过,也被淘汰。左表那些本应保留的不匹配行,恰恰因为右表列是 NULL,被这个针对右表的过滤条件全部滤除。

优化器进一步利用了这一点。它在生成计划时会检查 WHERE 条件是否会过滤掉外连接补出的全部 NULL 行,一旦确认只要 WHERE 中存在对可空侧（右表）非空列的过滤,如 t2.name2 = ‘cc’,这些 NULL 行就注定无法通过,那么"外连接加过滤"与"内连接加过滤"在结果上完全等价。内连接代价更低、可选路径更多,于是优化器改走内连接。这个改写在你所写 SQL 的语义下结果是正确的,问题在于这条 SQL 的实际语义本就与预期不符。

改写在执行计划中可以直接观察到。对上述查询执行 EXPLAIN：

EXPLAINSELECT*FROMt1LEFTJOINt2ONt1.id1=t2.id2WHEREt2.name2='cc';

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

写在 SQL 里的是 LEFT JOIN,计划里出现的却是 Hash Join 而非 Hash Left Join,Left 字样消失即是外连接被改写为内连接的直接证据。Nested Loop 场景同理,会显示为 Nested Loop 而非 Nested Loop Left Join。审查外连接相关的慢 SQL 时,定义的是 Left Join、计划里却没有 Left,再配合结果集行数偏少,即可判定发生了非预期的外连接消除。

不会被消除的情况

针对右表的条件并非都会触发消除,最典型的例外是 IS NULL：

SELECT*FROMt1LEFTJOINt2ONt1.id1=t2.id2WHEREt2.name2ISNULL;

这条查询优化器不会改写。外连接的价值之一正是找出右表缺失、未匹配的记录,而 IS NULL 捕捉的恰是外连接补出的 NULL 值；若转为内连接,这些缺失记录将被彻底过滤,永远不出现在结果中,那才是真正的错误。因此为保证结果正确,优化器在这类查空场景下保留外连接。这也是用 LEFT JOIN 配合 WHERE 右表列 IS NULL 实现反连接、求差集的经典手法,其行为在迁移时是稳定的。

正确写法

核心原则是 ON 决定连接的规则、WHERE 决定最终结果的筛选。针对右表的过滤条件,除查空外,应放入 ON 而非 WHERE。

将条件下推到 ON,即可在过滤右表的同时保留左表全部记录：

SELECT*FROMt1LEFTJOINt2ONt1.id1=t2.id2ANDt2.name2='cc';

此时系统按 id1 = id2 AND name2 = ‘cc’ 判定匹配,再对 t1 做外连接。id1=2 的行虽与 id2=2 关联,但因 name2=‘dd’ 不满足 ON 条件而视为未匹配、右表列补 NULL；id1=3 本无匹配,同样补 NULL。左表三行完整保留。ON 与 WHERE 的差别在此很清楚：条件放在 ON 上只决定右表哪些行算匹配,左表全部保留；放在 WHERE 上则是对连接结果做最终筛选,会连带滤除外连接补出的 NULL 行,进而触发消除。

从 Oracle 体系迁移的 SQL 需额外留意 (+) 语法。KES 兼容该语法,但它同样可能触发消除。若 (+) 只标在连接条件上、过滤条件不带 (+),其效果等同于把过滤条件写进 WHERE,照样丢数据；若过滤条件也带 (+),则等同于写进 ON,外连接不被消除。原则是 (+) 要么全带、要么按普通 WHERE 理解,针对可空侧的过滤条件漏带 (+) 是这类 SQL 的重点排查项。

还有一类需区分的是作用在非空侧（左表）的条件,如 WHERE t1.name1 = ‘a’。它表示只对满足条件的左表记录做外连接、其余左表记录直接丢弃,属于正常业务过滤,不改变连接性质,也不是需要防范的坑。但要注意与之相反的写法：若把左表条件放进 ON,如 ON t1.id1 = t2.id2 AND t1.name1 = ‘a’,左表所有记录仍会返回,只是不满足 name1=‘a’ 的行不参与连接、右表列补 NULL。二者结果不同,不可混用。

小结

LEFT JOIN 迁移后丢数据,多数情况不是缺陷,而是一条语义有歧义的 SQL 命中了外连接消除：当 WHERE 中带有对可空侧非空列的过滤时,外连接补出的 NULL 行本就会被滤除,“外连接加过滤"因而退化为等价的"内连接加过滤”。规避的要点是区分 ON 与 WHERE 的职责,ON 决定连接规则,WHERE 决定结果筛选。需要保留左表全量时,把针对右表的过滤放进 ON；需要找缺失记录时,用 WHERE 配合 IS NULL。迁移应以与原系统结果一致为第一优先级,先确保语义无歧义、结果对齐,再考虑性能优化。