在去 IOE 和国产化替代的大潮中Oracle 迁移至金仓 KESKingbaseES是很多金融、政企业务的核心任务。但在迁移过程中除了数据类型映射、存储过程语法差异这些“明坑”还有一些由优化器行为差异导致的“暗雷”。今天要聊的外连接消除Outer Join Elimination就是我在实际迁移项目中踩过的一个大坑也是导致业务数据“莫名消失”的头号杀手。前言一次诡异的数据“丢失”事件前段时间团队接手了一个核心交易系统的国产化适配工作。系统在 Oracle 环境下运行了十几年稳如老狗。当我们把数据迁移到金仓 KES应用侧代码几乎“零修改”地跑起来后测试同学突然报了个 Bug某报表的数据量比老系统少了 30%。起初我们怀疑是数据同步ETL的问题反复校验了源端和目标端的基数数据条数是对的。接着我们排查了权限也没问题。最后打开日志把那条“罪魁祸首”的 SQL 拿出来在 KES 里单跑结果确实比 Oracle 少。这就奇怪了。SQL 逻辑很简单就是一个LEFT JOIN。既然数据都在为什么 JOIN 完了就没了经过一番对执行计划的深挖我们发现了一个关键现象在 Oracle 中执行计划显示的是HASH JOIN OUTER而在金仓 KES 中执行计划变成了HASH JOIN。这就是本文要深入剖析的主角——外连接消除Outer Join Elimination。一、什么是外连接消除在数据库查询优化领域外连接消除是一种经典的优化器策略。简单来说就是优化器“自作主张”地把一个成本较高的外连接LEFT/RIGHT OUTER JOIN改写成了性能更好的内连接INNER JOIN。在大多数纯技术视角下这当然是好事毕竟内连接的算法复杂度通常低于外连接性能提升明显。但在业务视角下如果这种转换不是开发人员预期的就会导致灾难性的后果——因为外连接的核心特征保留驱动表全部记录在内连接中会丢失。特别是在从 Oracle 迁移到金仓 KES 的过程中由于优化器对 SQL 语义的判定逻辑存在细微差别这种“隐性转换”极易发生。二、现象复盘为什么我的 LEFT JOIN 失效了让我们还原一下当时那个让我头疼的 SQL 逻辑。为了简化说明我们用两个简单的表结构来模拟。假设有两张表t_order(订单表)左表驱动表无论如何都要展示。t_invoice(发票表)右表可能存在也可能不存在比如货发了票还没开。业务需求查询所有订单如果发票已开且发票类型为 VAT则显示发票金额否则显示为空。1. 容易出错的写法在代码评审中我们经常看到这样的写法SELECT o.order_id, o.order_name, i.invoice_amount FROM t_order o LEFT JOIN t_invoice i ON o.order_id i.order_id WHERE i.invoice_type VAT; -- 注意这里的条件位置开发人员的预期返回t_order的所有记录。对于那些没有对应发票或者有发票但不是 VAT 类型的订单invoice_amount字段应该显示为NULL。金仓 KES 的实际执行结果只返回了那些invoice_type恰好等于 VAT 的订单记录。那些没有发票的订单直接消失了。2. 执行计划说了真话我们在 KES 中使用EXPLAIN ANALYZE查看执行计划发现了关键变化- HASH JOIN (cost...) (actual time...) Hash Cond: (o.order_id i.order_id) - SEQ SCAN ON t_order o ... - HASH ... - SEQ SCAN ON t_invoice i ... Filter: (invoice_type VAT::character varying)注意这里的HASH JOIN并没有标注OUTER。这意味着优化器已经将它转成了内连接。为什么会这样这就涉及到 SQL 的执行顺序。标准的逻辑执行顺序是FROM-ON-JOIN-WHERE-SELECT-ORDER BY。JOIN 阶段LEFT JOIN会生成临时结果集。对于t_order中没有匹配t_invoice的记录i表的所有列都会被填充为NULL。WHERE 阶段此时SQL 引擎开始应用WHERE i.invoice_type VAT这个条件。对于那些有发票且类型是 VAT 的记录条件成立。对于那些没有发票的记录i.invoice_type的值是NULL。在 SQL 的三值逻辑True/False/Unknown中NULL VAT的结果是Unknown未知这在逻辑上被视为 False。因此所有因外连接产生的NULL行在WHERE阶段被无情地过滤掉了。优化器的逻辑既然你WHERE子句里的条件注定会过滤掉所有外连接产生的补齐行那我直接先用invoice_type VAT过滤右表再做一个内连接得到的最终结果集在数学逻辑上是完全等价的而且执行效率更高。于是外连接消除发生了。三、底层原理Nullable-Side 与优化器的博弈要彻底搞懂这个坑我们需要理解两个核心概念Nullable-Side可空侧和逻辑等价性。在外连接LEFT JOIN中右表被称为 Nullable-Side。因为一旦左表的记录在右表找不到匹配项右表的字段就必须为 NULL。金仓 KES 的优化器非常智能它会扫描WHERE子句。当它发现WHERE子句中引用了 Nullable-Side 的列例如t2.col value并且这个条件是一个严格条件Strict Condition——即该条件对于任何NULL输入都会返回 False 或 Unknown——优化器就会判定“用户并不关心右表缺失的情况因为他要求右表的字段必须有某个特定值。”在这种情况下优化器会启动外连接消除规则将查询重写为内连接。什么情况下不会消除这里有一个特例也是业务上经常需要的IS NULL​ 判断。SELECT * FROM t_order o LEFT JOIN t_invoice i ON o.order_id i.order_id WHERE i.invoice_id IS NULL; -- 查找未开票的订单在这个 SQL 中WHERE子句同样作用于 Nullable-Side。但是IS NULL是唯一一个能从外连接产生的NULL行中筛选出数据的条件。如果优化器把它消除了改成内连接那么i.invoice_id IS NULL的条件永远无法满足内连接不允许不匹配的行存在。因此为了保证业务逻辑的正确性金仓 KES 的优化器在面对IS NULL条件时绝对不会进行外连接消除。这也是为什么我们在排查问题时要特别留意WHERE子句中是否有对右表的非空判断。四、Oracle 兼容性带来的思维定势很多同学在 Oracle 环境下习惯了某种写法认为在金仓 KES 中也一定没问题。但实际上虽然 KES 提供了高度的 Oracle 兼容模式但在优化器的实现细节上仍有差异。警惕 Oracle 旧式外连接()在老旧的 Oracle 代码中经常能看到()语法。金仓 KES 为了兼容存量系统也支持这种语法。-- Oracle 风格 SELECT * FROM t1, t2 WHERE t1.id t2.id() AND t2.type A; -- 坑点在这里在上面的语句中t2.type A这个条件位于WHERE子句且没有()标记。这在语义上等同于 ANSI SQL 的LEFT JOIN ... WHERE t2.type A。迁移陷阱在迁移过程中如果不加思考地保留这种写法在 KES 中极有可能触发外连接消除。正确的做法是将其转换为 ANSI SQL或者给条件也加上()符号但这会影响语义需谨慎。推荐写法SELECT * FROM t1 LEFT JOIN t2 ON t1.id t2.id AND t2.type A;五、 避坑指南怎么写 SQL 才不会给自己挖坑这几个坑我是真真切切掉进去过尤其是刚接触金仓 KES 那会儿。那时候总觉得 SQL 只要 Oracle 能跑KES 顶多就是改改函数名。结果在外连接这块吃了好几次哑巴亏。折腾久了也摸索出几条还算靠谱的经验。先说最靠谱的一招把条件摁死在 ON 子句里这算是解决外连接问题的“万能钥匙”。以前写 SQL 图省事不管啥条件都往 WHERE 里堆。现在我的原则是只要你是 LEFT JOIN 的右表条件想过滤右表的数据但又不想影响左表的返回那就必须老老实实待在 ON 里。拿刚才那个订单和发票的例子正确的打开方式应该是这样的SELECT o.order_id, o.order_name, i.invoice_amount FROM t_order o LEFT JOIN t_invoice i ON o.order_id i.order_id AND i.invoice_type VAT; -- 注意看这里条件跟在 ON 后面为什么要这么写其实逻辑很简单。数据库看到这行会先去把t_invoice里invoice_type是 VAT 的数据挑出来相当于先做个子集。然后再拿这个子集去和t_order做左连接。这样一来t_order的数据是“铁饭碗”谁来也动不了。哪怕没匹配上发票订单记录照样在只是发票那头是 NULL。反过来如果你把这条件写到 WHERE 里在 KES 眼里你的潜台词就变成了“我只想要那些发票类型是 VAT 的记录”。既然你要确定的值那那些 NULL 的自然就被干掉了外连接也就顺势被优化成了内连接。左右表过滤逻辑差之毫厘还有一种情况比较绕就是给左表加条件。这地方稍微不留神业务逻辑就跑偏了。举个例子我们要查订单顺带看看发票。如果你这么写SELECT ... FROM t_order o LEFT JOIN t_invoice i ON o.id i.order_id WHERE o.status UNPAID;这意思是先别管怎么 JOIN最后给我展示的结果里订单必须是“待支付”状态的。不符合的连左表记录一起删掉。但如果你把条件挪到 ON 里SELECT ... FROM t_order o LEFT JOIN t_invoice i ON o.id i.order_id WHERE o.status UNPAID;这意思就变成了先把t_order里“待支付”的订单挑出来拿这些订单去关联发票。至于那些“已支付”的订单它们虽然还在结果集里但因为没参与匹配发票那栏全是 NULL。这两种写法出来的数据天差地别。在迁移的时候一定要跟业务方抠细搞清楚他到底是要“查待支付的订单”还是“查所有订单但只关联待支付的发票”。这事儿没商量余地错了就是 Bug。执行计划才是最终的裁判最后说个习惯我现在写完 SQL尤其是涉及多表关联的第一反应不是跑SELECT而是跑EXPLAIN ANALYZE。为什么因为 SQL 文本是会骗人的它只是你的意图而执行计划才是数据库的真实行动。在金仓 KES 里你得盯着几个关键点看 Join 类型这是最直观的。如果你写的是LEFT JOIN但在计划里看到的是Hash Join或者Nested Loop唯独少了Left这个词那你得当心了外连接八成是被干掉了。看 Filter 的下推有时候优化器会把条件“下推”到表扫描的时候执行。如果看到Filter直接作用在了右表扫描上且这个条件本来是在 WHERE 里的那基本上就是触发消除的前兆。看行数预估留意Rows Removed by Filter这个值。如果数字大得离谱或者预估行数和实际行数差得太远说明优化器的逻辑可能跟你想的不一样。有时候真的别太相信自己的直觉。在数据库内核面前咱们写的 SQL 只是个草稿执行计划才是终稿。多看两眼总比上线后被人追着骂强。六、实战案例一个复杂的报表 SQL 改造为了让大家更有体感我举一个稍微复杂一点的真实案例。SELECT a.dept_name, COUNT(DISTINCT b.emp_id) emp_count, SUM(c.salary) total_salary FROM departments a, employees b, salaries c WHERE a.dept_id b.dept_id() AND b.emp_id c.emp_id() AND c.pay_month 2024-05 AND c.status ACTIVE GROUP BY a.dept_name;问题分析这里salaries表别名c的条件c.pay_month和c.status没有带()。这会导致1. 只有 5 月份有有效工资记录的员工才会被统计。2. 那些 5 月份没发工资的部门或者部门里没人领工资的整个部门都不会显示在统计结果中因为c.status为 NULL被 WHERE 过滤了。3. 实际上业务需求是“统计所有部门的情况如果有 5 月份的工资则累加”。金仓 KES 改造后推荐写法SELECT a.dept_name, COUNT(DISTINCT b.emp_id) emp_count, SUM(c.salary) total_salary FROM departments a LEFT JOIN employees b ON a.dept_id b.dept_id LEFT JOIN salaries c ON b.emp_id c.emp_id AND c.pay_month 2024-05 AND c.status ACTIVE GROUP BY a.dept_name;改造要点1. 将所有()语法改为标准的 LEFT JOIN。2. 将salaries表的过滤条件从 WHERE 子句移动到 LEFT JOIN ... ON 子句中。3. 这样一来无论salaries表有没有 5 月份的数据departments表的数据都会完整保留。七、 写在最后优化器的“聪明”与业务的“底线”做了这么多轮迁移我越来越觉得国产化这事难的不是把库搭起来而是把那些藏在水面下的逻辑差异给磨平。说回今天这个外连接消除。讲道理优化器这么做没错甚至可以说很“聪明”。为了那点性能把 LEFT JOIN 拍扁成 INNER JOIN在纯数学逻辑里完全等价。但在业务世界里这“聪明”劲儿就容易办傻事。你本来是想查“所有订单顺便看看发票”结果优化器直接给你改成“只查有发票的订单”这业务逻辑直接就断片了。我在项目里吃过这亏后来就给自己立了个规矩也是对团队的要求凡是涉及 LEFT/RIGHT JOIN 的 SQL上线前必须过一遍脑子最好再过一遍 EXPLAIN ANALYZE。第一严格控制过滤条件的位置。这是最核心的一点。如果条件作用于右表Nullable-Side且目的是过滤关联数据而非最终结果集那么它必须出现在 ON 子句中。只有当明确需要过滤最终结果时才将其置于 WHERE 子句。简单讲想过滤匹配的数据用 ON想过滤输出的行用 WHERE。第二摒弃隐式连接拥抱标准 SQL。很多遗留系统习惯用逗号加()的 Oracle 风格。虽然金仓 KES 做了高度兼容但我强烈建议在迁移重构时统一改为 ANSI SQL 标准的JOIN ... ON语法。这不仅是为了可读性更是为了减少优化器在解析旧语法时的歧义空间。()符号在处理复杂视图或多表关联时极易引发非预期的逻辑转换。第三正确理解 ON 与 WHERE 的语义边界。这不仅是语法区别更是逻辑分层。ON 条件是构建数据集的“连接规则”而 WHERE 条件是对已构建数据集的“筛选规则”。混淆这两者尤其是在外连接场景下将右表的过滤条件误置于 WHERE 中是引发外连接消除导致数据丢失的主要原因。第四将执行计划审计纳入代码评审。不要轻信肉眼看到的 SQL 逻辑。在 KES 中EXPLAIN ANALYZE是检验真理的唯一标准。如果在执行计划中看到原本的Hash Left Join变成了Hash Join或者Nested Loop Left Join变成了Nested Loop且结果集行数异常基本可以断定发生了非预期的外连接消除。这种审计必须在上线前完成。金仓 KES 在 Oracle 兼容模式上做得相当扎实但“兼容”不等于“相同”。每个数据库优化器都有自己的偏好KES 偏向于在保证结果一致性的前提下尽可能提升性能。在迁移初期这种激进的优化策略如果不加以约束很容易成为隐蔽的雷区。迁移工作没有捷径。每一条核心 SQL 的背后都是业务逻辑的映射。保持对底层机制的敬畏多测、多看、多思考才能确保数据平滑迁移业务无缝衔接。希望这些实战中的体悟能帮大家在国产化的道路上少走一些弯路。如果你在迁移金仓 KES 的过程中遇到了其他棘手的优化器行为差异欢迎在评论区交流咱们一起探讨解决方案。