踩坑实战向｜MySQL/PostgreSQL 迁移必踩：LEFT JOIN 莫名丢数据，金仓 KES 优化行为避坑指南

最近帮团队做异构数据库迁移从 MySQL/PostgreSQL 迁到金仓 KES。业务逻辑没改SQL 也没动上线后却发现报表数据莫名其妙变少了。排查一圈下来问题居然出在一个“看起来很合理”的 LEFT JOIN 写法上。本文把这次踩坑过程、原理分析和解决方案梳理出来希望能帮大家少走弯路。一、问题现场LEFT JOIN 怎么就把数据“吞”了先还原一下当时的业务场景。我们有两张表t1订单主表左表驱动表t2订单扩展信息表右表业务需求很简单查出所有订单如果扩展信息表中存在name2 cc的记录就一并展示如果没有扩展字段显示 NULL。当时开发同学写的 SQL 如下为了方便阅读做了简化SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 t2.id2 WHERE t2.name2 cc;在原来的 MySQL / PostgreSQL 环境中这条 SQL 的行为是“符合直觉”的t1的所有订单都会返回如果t2中有匹配且name2 cc则显示对应字段否则t2相关字段为 NULL。然而迁移到金仓 KES​ 后诡异的事情发生了结果集中大量原本应该出现的t1记录消失了。第一反应是是不是数据没同步全是不是有触发器没迁过来查了一圈数据没问题。于是把执行计划拉出来一看真相浮出水面- Hash Join (cost...) Hash Cond: (t1.id1 t2.id2) - Seq Scan on t1 - Hash - Seq Scan on t2 Filter: (name2 cc)注意到了吗计划中并没有出现Left Join而是直接变成了Hash Join内连接。也就是说KES 的优化器在这里做了一个等价变换外连接消除Outer Join Elimination。二、为什么会这样Nullable-Side 条件的“隐式强约束”要理解这个问题得先回到 SQL 的执行语义上。1. SQL 的逻辑执行顺序在标准 SQL 中一条带 JOIN 的查询逻辑上的执行顺序是FROM / JOIN先做连接生成中间结果集WHERE对中间结果集进行过滤SELECT投影需要的列ORDER BY / LIMIT 等对于LEFT JOIN来说如果右表t2没有匹配行那么t2的所有列都会被填充为NULL这些“补出来的 NULL 行”依然属于LEFT JOIN的结果集。2. Nullable-Side 条件带来的“副作用”现在看我们的 SQLWHERE t2.name2 cc;这里的t2.name2属于Nullable-Side右表的非主键列。考虑两种情况情况 At2有匹配行且name2 cc→ 条件成立保留情况 Bt2没有匹配行 →t2.name2为NULL→NULL cc的结果是Unknown→该记录被 WHERE 过滤掉。也就是说所有由 LEFT JOIN 产生的 NULL 行都会被这个 WHERE 条件无情地干掉。3. 优化器的“等价变换”逻辑站在优化器的角度看问题既然WHERE t2.name2 cc会把所有右表为 NULL 的行全部过滤掉那么“LEFT JOIN WHERE 过滤”的最终结果在数学逻辑上完全等价于“INNER JOIN WHERE 过滤”。为了降低执行代价优化器就会选择把 LEFT JOIN 改写成 INNER JOIN外连接消除这是一个完全合法、符合 SQL 标准的优化行为但在业务语义上却往往不是开发人员想要的。三、什么时候不会消除IS NULL 是个例外并不是所有涉及右表的条件都会触发外连接消除。一个非常典型的反例是IS NULL。SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 t2.id2 WHERE t2.name2 IS NULL;这条 SQL 的业务语义是找出在 t2 中没有匹配记录的 t1 行。如果优化器把它转成内连接那这些“缺失匹配”的记录根本不可能出现在结果集中逻辑就完全错了。因此在 KES 中这类包含Nullable-Side IS NULL的查询不会触发外连接消除执行计划中依然会保留Left Join的特征。这也是为什么很多“找差集”的 SQL比如“查找未绑定扩展信息的订单”在迁移过程中反而表现稳定的原因。四、Oracle()语法下的坑KES 兼容模式顺带一提我们在迁移过程中还遇到了 Oracle 风格外连接的写法。KES 支持 Oracle 的()语法但坑也不少。1. 错误示例SELECT * FROM t1, t2 WHERE t1.id1 t2.id2() AND t2.name2 cc;这里()只出现在 JOIN 条件上而t2.name2 cc是普通 WHERE 条件。效果和前面 ANSI JOIN 的例子一模一样外连接被消除数据“丢失”。2. 正确写法如果确实想保留左表全部数据同时过滤右表需要把过滤条件也标记为外连接侧SELECT * FROM t1, t2 WHERE t1.id1 t2.id2() AND t2.name2() cc;这种写法在语义上等同于SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 t2.id2 AND t2.name2 cc;不过从可读性和可维护性角度我更推荐直接使用 ANSI JOIN 语法避免()这种“上古语法”带来的心智负担。五、Non-Nullable-Side 条件左表条件的影响还有一种容易混淆的情况条件写在左表上。SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 t2.id2 WHERE t1.name1 a;这里t1.name1属于NonNullable-Side。这个条件的语义是先筛选出t1.name1 a的记录再对这些记录做 LEFT JOIN。结果是不符合t1.name1 a的左表记录被过滤掉这是符合预期的。但如果把条件写到ON子句中SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 t2.id2 AND t1.name1 a;语义就变成了t1所有记录都参与外连接只有t1.name1 a的行才会尝试匹配t2其他t1行仍然会出现在结果集中只是t2字段为 NULL。一句话总结WHERE是对最终结果集的过滤ON是对连接行为的控制。这个原则在 KES 中同样适用也是排查外连接行为异常的重要切入点。六、迁移实战中的排查套路在这次迁移项目中我们总结出了一套比较实用的排查流程供大家参考。1. 先看执行计划在 KES 中可以用EXPLAIN (ANALYZE, VERBOSE) SELECT ...重点关注JOIN 类型是否还是Left Join有没有变成Hash Join/Nested Loop但没有标明 Left 语义过滤条件被下推到了哪一步。如果发现原本期望的外连接在计划中“消失”就要警惕外连接消除了。2. 明确业务语义拿到一条 SQL先问自己三个问题是否必须保留左表全部记录过滤条件到底是限制左表还是限制右表是否允许右表为 NULL如果答案是“必须保留左表 过滤右表”那么右表的过滤条件一定要放到ON子句中。3. 改写 SQL 的正确姿势回到最开始的例子正确的写法应该是SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 t2.id2 AND t2.name2 cc;或者如果逻辑上更清晰也可以写成子查询形式视执行计划而定SELECT * FROM t1 LEFT JOIN ( SELECT * FROM t2 WHERE name2 cc ) t2_filtered ON t1.id1 t2_filtered.id2;在实际测试中这两种写法在 KES 中都能稳定保持外连接语义不会被优化器“偷偷”转成内连接。七、性能与一致性的取舍有同学可能会问外连接消除不是一种性能优化吗为什么要抵制它这是一个非常好的问题。在纯性能视角下外连接消除确实是好事内连接可以选择更丰富的连接算法可以减少中间结果集大小在很多 OLTP 场景下能显著降低延迟。但在数据库迁移场景​ 中优先级是不一样的第一优先级逻辑一致性报表数字不能错业务行为不能变迁移前后结果必须一致。第二优先级性能在逻辑正确的前提下再做调优必要时可以通过 hint 或 SQL 改写控制执行计划。因此在 KES 迁移项目中我的建议是默认假设优化器会做外连接消除显式写出你想要的语义条件放 ON再通过执行计划验证而不是“赌优化器不动它”。八、总结与避坑清单这次踩坑本质上是一次“优化器聪明过头”的典型案例。为了避免大家重蹈覆辙我把关键点整理成一个简短的避坑清单LEFT JOIN 右表 WHERE 条件 高风险除非你明确只想查匹配成功的记录否则请把右表过滤条件挪到ON子句。执行计划是第一真相来源不要只看 SQL 文本要看计划中 JOIN 的类型是否发生变化。ON vs WHERE语义截然不同ON控制连接行为WHERE控制最终结果。Oracle()语法要慎用过滤条件是否带()结果可能完全不同迁移时优先考虑改成 ANSI JOIN。迁移阶段以一致性为先性能可以慢慢调数据错误是事故。写在最后数据库迁移从来不是“语法替换”这么简单优化器行为的差异往往是最大的隐形雷区。这次在金仓 KES 上遇到的外连接消除问题其实在其他数据库中也有类似机制只是在 KES 的优化策略下表现得更加“积极”。理解其背后的 Nullable-Side 逻辑不仅能帮你避开迁移坑也能让你在日常 SQL 编写中写出更健壮、语义更清晰的查询。如果你也在做 MySQL / PostgreSQL 到 KES 的迁移欢迎在评论区聊聊你遇到的奇葩问题我们一起填坑。