报表少了 40%那条从没被质疑过的 LEFT JOIN在迁 KES 时终于露馅了上个周五吧大概下午四点左右运营那边的人在群里开始找我了。问的是这个事“为什么用户报表今天的总数比昨天少了快 40% 啊”我一开始想的是是不是哪个定时任务没跑成功。就去翻了日志。翻了半天没看到有啥报错。接着我又去看了那条核心的统计 SQL。语法看过去没毛病执行也没抛异常。而且在我本地搭的测试环境里面跑出来的结果也是对的。但是生产的数据它就是少了。最后搞了两个小时才找到原因。原因是什么呢是一条 LEFT JOIN 的写法不对。它把优化器的外连接消除给触发了直接把一部分数据给过滤掉了而且没提示。更无奈的一个情况是这条 SQL 在以前用的 MySQL 里面其实也是这么跑的。只是说以前压根就没人去仔细对过账所以这个问题就一直没暴露出来。这种坑吧其实是我最近在做 MySQL 还有 PostgreSQL 往 KES 迁移的这个项目里头碰到频率最高的。情况往往是这样的SQL 语法能跑通执行也不报错测试的时候你也看不出来但是业务那边的实际结果呢它就是少了点东西。那么这篇文章我就把 LEFT JOIN 这个事儿从前往后拆解一下。顺便列个避坑的清单。也就是为了让大家以后碰到这个情况能有个参考。文章目录报表少了 40%那条从没被质疑过的 LEFT JOIN在迁 KES 时终于露馅了一、 事故复盘一条 SQL 引发的对账问题二、 底层机制为什么 LEFT JOIN 会变成 INNER JOIN1. SQL 的执行顺序假象2. 优化器的等价改写3. 主流数据库的一致态度三、 KES 的兼容性设计把意图还给业务1. 用 EXPLAIN 立即揪出被消除的外连接2. 显式补齐 IS NULL 条件时的兜底行为3. 走 ON 子句把过滤留给连接规则四、 MySQL/PG 到 KES 迁移的六个高频外连接消除陷阱陷阱 1使用非空对比操作符陷阱 2函数包裹的右表列陷阱 3IN / BETWEEN 子句陷阱 4混合了 IS NULL 和其他右表条件陷阱 5多层嵌套 JOIN 的传递消除陷阱 6视图/子查询里的外连接被外部 WHERE 反向消除五、 一份实操级避坑清单1. 静态代码扫描拦截疑似消除写法2. EXPLAIN 抽样验证3. 改写规则ON 子句 vs. COALESCE4. 视图与子查询的封装原则5. 生产环境回归测试6. 走金仓社区的同行者通道六、 总结一、 事故复盘一条 SQL 引发的对账问题先说一个我实际碰到的事儿吧。那个项目的背景是一个省级的政务平台。他们以前用的是 MySQL 8.0。最近这段时间呢是要整体切到金仓数据库 KES 去版本是 V9R1C10。结果业务刚上线的第一周运营那边就说了“用户订单综合报表”里面的数据总条数比以前的老系统少了差不多 40%。我当时把那条有问题的 SQL 拿过来做了一下脱敏简化一下差不多就是下面这样-- 业务需求查询所有用户并附带展示其已完成状态的订单信息-- 如果用户没有已完成订单也需要显示该用户订单相关字段为 NULL。SELECTu.user_id,u.user_name,o.order_id,o.order_amount,o.order_statusFROMt_user uLEFTJOINt_order oONu.user_ido.user_idWHEREo.order_statusFINISHED;你光看这段代码的话这条 SQL 你丢到 MySQL 里面或者 PostgreSQL、KES 里面它都能跑出来语法这关是没问题的。但是呢它跑出来的结果集大小在 MySQL 8.0 跟金仓 KES 里面居然是完全一样的。对你没看错。这里的问题其实不是“迁移完了之后两边行为不一样”。真正的问题是这条 SQL 它一开始写出来就是错的。只不过是在以前的老系统里面做业务的人一直没发现这个问题而已。后来让我确定这里面有问题的是我在老系统里看到还有一条配套的对账 SQL 在跑-- 原系统另一处对账逻辑SELECTCOUNT(DISTINCTu.user_id)FROMt_user uLEFTJOINt_order oONu.user_ido.user_id;这条 SQL 它查出来的就是全部的用户数量。为什么呢因为它没有在WHERE那里去加任何 nullable-side 的条件。这样的话LEFT JOIN本来的意思就还在。这就是为啥以前做业务的人总觉得“用户总数这栏是对的”但是那个报表却一直“少数据”的原因了。其实就是这两条 SQL 走了不一样的逻辑路径。只不过以前没人去顺着这个逻辑去查过。**我先说结论吧这事真不是 KES 自己优化得太猛了。而是最开始的那种写法它本身就有个问题就是“外连接被偷偷消除了”这么个语义上的毛病。其实 KES 跟 MySQL 还有 PG 在处理这条路径的时候做法是一模一样的。**只不过是因为做迁移的时候大家的眼睛都盯着新的数据库看。业务那边就以为哦是新数据库出毛病了。二、 底层机制为什么 LEFT JOIN 会变成 INNER JOIN要把这个坑说明白有一件事得先说一下。就是在关系代数这个层面去看的话你写LEFT JOIN然后后面跟着个WHERE去过滤右表不能为空的条件这玩意儿跟你直接写INNER JOIN加上一样的条件它们俩是划等号的。优化器只要看出来这两个东西是一样的它就会把外连接改成内连接去跑。这个操作呢有个叫法叫外连接消除。1. SQL 的执行顺序假象很多人觉得 SQL 的执行顺序是这样的FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY这个顺序你要是只谈“逻辑上的意思”那确实是这样。也就是说先执行LEFT JOIN。它会把左边的表全部行都留着右边没对上的那些行呢就用 NULL 填进去。然后轮到WHERE了再去对这个结果集做过滤。那这就会有个问题了。WHERE o.order_status FINISHED这个条件如果碰到了那些“外连接补出来的 NULL 行”会怎么样呢情况是这样的NULL FINISHED在三值逻辑里面它出来的结果是个Unknown。而WHERE呢它看到Unknown的行就直接给过滤掉了。这就导致一个结果那些靠外连接补出来的 NULL 行全给刷没了。那最后还剩下啥呢剩下的就仅仅是那些“真正匹配上了而且 order_status 也是 ‘FINISHED’”的行。你想想这不就是INNER JOIN的意思吗。2. 优化器的等价改写数据库的优化器在算代价之前它其实会先做一步逻辑等价变换的事情。它去扫一下WHERE那一串条件看看有没有能证明“外连接补的 NULL 行是不对的”这种条件。它判断的规则大概有这么两条条件作用对象条件里面用到的列必须得是从 Nullable-Side也就是外连接那边可以为空的一侧来的条件对 NULL 的态度这个条件碰到 NULL 的时候必须得返回 False 或者 Unknown。这也就是人家说的 “null-rejecting”拒空条件。拿我们这条 SQL 来说o.order_status FINISHED这一句刚好这两条都占上了。它用了右边的列而且它排斥 NULL。于是呢优化器就直接动手把LEFT JOIN改成了INNER JOIN。改完之后它再往下走 Hash Join 或者是 Nested Loop 这些实际的执行路径。这就是数据不见了的真正原因不是数据真没了而是按照 SQL 的语义来说那些数据本来就不该在结果集里面。3. 主流数据库的一致态度这里得提一嘴这事真不是某个国产数据库自己“独创”的玩法。基本上只要是支持 SQL:2008 标准的关系型数据库它都会去搞这个优化。你像 MySQL、PostgreSQL、Oracle、SQL Server还有金仓 KES它们全都会做外连接消除。因为它们都得按标准 SQL 引擎的那套规矩来。唯一的区别就在于优化器在什么时候能“发现”这种等价的情况。比如说你用了子查询或者套了视图再或者用了 UDF 的时候不同的数据库它认出来的能力是有差别的。这就导致了后面我们会说到的那种在生产环境里面特别难发现的坑。三、 KES 的兼容性设计把意图还给业务如果单纯从符合 SQL 标准角度看上面那条 SQL 被优化成 INNER JOIN 完全没错。但从迁移改造工程角度看这里其实有一个非常关键的产品设计取舍——新库到底应不应该复现旧库的错误行为金仓 KES 在这里的设计取舍我个人非常认可严格遵守 SQL 标准语义绝不歪打正着地为错误 SQL 兜底同时提供极其完备的执行计划可视化能力让开发者在改造阶段就能把语义陷阱暴露出来。1. 用 EXPLAIN 立即揪出被消除的外连接在金仓 KES 里我第一时间会用EXPLAIN或EXPLAIN ANALYZE去看执行计划EXPLAINANALYZESELECTu.user_id,u.user_name,o.order_id,o.order_amount,o.order_statusFROMt_user uLEFTJOINt_order oONu.user_ido.user_idWHEREo.order_statusFINISHED;如果外连接被消除返回的执行计划里会看到关键字不是Hash Left Join或Nested Loop Left Join而是直接的Hash Join内连接或Nested Loop。仅这一个观察点就足以在迁移代码审计阶段发现绝大部分外连接消除案例。-- 被消除后的执行计划关键行示意 Hash Join (cost... rows...) Hash Cond: (u.user_id o.user_id) - Seq Scan on t_user u - Hash - Seq Scan on t_order o Filter: (order_status FINISHED)请注意上面这个Filter: (order_status FINISHED)已经下推到了 t_order 的扫描节点而 JOIN 节点直接标记为Hash Join——外连接消失得干干净净。2. 显式补齐 IS NULL 条件时的兜底行为有一种非常常见的业务写法是故意用 LEFT JOIN 找出未匹配的记录比如哪些用户从未下过订单SELECTu.user_id,u.user_nameFROMt_user uLEFTJOINt_order oONu.user_ido.user_idWHEREo.order_idISNULL;这条 SQL 在 KES 里就不会被消除原因很简单IS NULL条件正是为了捕捉外连接补的 NULL 行。如果优化器强行把它改写为内连接业务语义直接被摧毁。 KES 的优化器在这里的判定规则严守边界只要 WHERE 条件不是 null-rejecting就不消除外连接。这个细节说明什么说明 KES 的优化器实现是语义安全优先的而不是能优化就优化的粗放模式。这一点在国产化迁移里非常重要——它意味着你在旧系统里写对的 SQL在 KES 里也一定跑得对写错的 SQLKES 会用与主流数据库一致的方式对待不会因为想显得自己更兼容而牺牲标准。3. 走 ON 子句把过滤留给连接规则回到本文开头那条报表 SQL业务方的真实意图是所有用户都要出现只是有匹配的已完成订单时显示订单信息。 正确写法是把过滤条件下推到ON子句里-- 正确写法ON 子句里过滤右表左表所有行保留SELECTu.user_id,u.user_name,o.order_id,o.order_amount,o.order_statusFROMt_user uLEFTJOINt_order oONu.user_ido.user_idANDo.order_statusFINISHED;这里的语义差别至关重要ON子句控制的是如何连接WHERE子句控制的是最终留下哪些行。ON里加过滤等价于先把 t_order 过滤成只剩 FINISHED再与 t_user 做外连接——用户表所有行完整保留即便对应的订单不满足条件也会以 NULL 出现在结果集里。这条改写完业务对账立即回到正确状态。四、 MySQL/PG 到 KES 迁移的六个高频外连接消除陷阱除了最典型的WHERE 里过滤右表列实际项目里还有大量变种写法会触发外连接消除。下面这六个是我在近半年迁移项目里反复看到的高频陷阱。陷阱 1使用非空对比操作符-- 反例、、!、LIKE 等对 NULL 都返回 UnknownSELECT*FROMt1LEFTJOINt2ONt1.idt2.idWHEREt2.amount100;t2.amount 100对 NULL 值同样返回 Unknown触发外连接消除。这类写法在报表 SQL 里极其常见。陷阱 2函数包裹的右表列-- 反例函数结果同样是 NULLSELECT*FROMt1LEFTJOINt2ONt1.idt2.idWHEREUPPER(t2.name)A;只要函数是严格函数strict function——即遇到 NULL 输入返回 NULL 输出——外连接依然会被消除。 KES 里绝大多数系统函数都是严格函数。陷阱 3IN / BETWEEN 子句-- 反例t2.status 为 NULL 时不会命中任何列表值SELECT*FROMt1LEFTJOINt2ONt1.idt2.idWHEREt2.statusIN(A,B,C);IN本质上是多个的 ORNULL 与任何值 IN 判断都是 Unknown。 同样触发消除。陷阱 4混合了 IS NULL 和其他右表条件-- 反例AND 一旦包含 null-rejecting 条件整体也变成 null-rejectingSELECT*FROMt1LEFTJOINt2ONt1.idt2.idWHEREt2.statusAANDt2.other_colISNULL;看起来有IS NULL就安全了不是的。整个AND表达式必须对 NULL 输入至少有可能返回 True才不会触发消除。 这里t2.status A已经把 NULL 全部 reject 掉了导致整体依然被消除。陷阱 5多层嵌套 JOIN 的传递消除-- 反例t3 的过滤条件传染到 t2间接把 t1→t2 的外连接也消除掉SELECT*FROMt1LEFTJOINt2ONt1.idt2.idLEFTJOINt3ONt2.idt3.idWHEREt3.statusA;这条 SQL 里t3.status A直接消除t2 LEFT JOIN t3一旦t2 → t3变成内连接t2.id就变成 not-null 的 join key进一步会消除t1 → t2的外连接。一个条件消除两个外连接损失惨重。陷阱 6视图/子查询里的外连接被外部 WHERE 反向消除-- 反例v_user_order 内部是 LEFT JOIN外层 WHERE 反向消除CREATEVIEWv_user_orderASSELECTu.user_id,u.user_name,o.order_id,o.order_amountFROMt_user uLEFTJOINt_order oONu.user_ido.user_id;SELECT*FROMv_user_orderWHEREorder_amount500;视图内部虽然是外连接但外层 WHERE 里带了 nullable-side 条件一旦优化器看穿视图View Inline / Query Rewrite外连接依然会被消除。 这类跨层陷阱最难排查因为视图定义看起来是对的问题出在业务侧的调用方式。五、 一份实操级避坑清单结合前面的分析我把日常在做迁移改造时执行的外连接消除排查动作整理成一份可直接落地的清单。这份清单我在几个项目里跑下来能在改造阶段拦截 90% 以上的类似语义陷阱。1. 静态代码扫描拦截疑似消除写法在 CI/CD 流水线里可以借助脚本对存量 SQL 做正则扫描输出**“包含 LEFT/RIGHT JOIN 且 WHERE 子句引用了 Nullable-Side 列且不是 IS NULL”**的可疑 SQL 清单。项目里我常用的简化版正则思路是# 匹配 LEFT JOIN ... WHERE ... right_alias.col 之类的模式 LEFT\sJOIN\s(\w)\s(\w).*?WHERE.*?\2\.\w\s*(?!IS\sNULL)这一步不追求 100% 精准目的是先把可疑清单圈出来交给 DBA 与业务共同评审。2. EXPLAIN 抽样验证对静态扫描输出的可疑 SQL 抽样在 KES 里执行EXPLAIN重点看JOIN 节点关键字是否包含Left/RightNullable-Side 表的 Filter 条件是否被下推到扫描节点若关键字为普通Hash Join/Nested Loop则确认外连接已被消除。对确认被消除、但业务意图确实需要保留外连接的 SQL进入下一步改写。3. 改写规则ON 子句 vs. COALESCE大部分场景推荐把过滤条件下推到ON-- 改写前LEFTJOINt2ONt1.idt2.idWHEREt2.statusA-- 改写后LEFTJOINt2ONt1.idt2.idANDt2.statusA如果业务逻辑复杂条件不能简单下推比如条件涉及左右表混合列的 CASE 表达式可以用COALESCE手工构造一个NULL 安全的比较-- 用 COALESCE 处理 NULL 值场景SELECT*FROMt1LEFTJOINt2ONt1.idt2.idWHERECOALESCE(t2.status,A)A;COALESCE(t2.status, A)遇到 NULL 时返回 ‘A’条件对 NULL 输入返回 True——这是标准的null-preserving写法KES 优化器会识别到它不是 null-rejecting从而不消除外连接。4. 视图与子查询的封装原则对视图 / CTE 里的外连接保持一个原则不允许调用方在外层 WHERE 里对视图输出的 Nullable-Side 列做 null-rejecting 过滤。如果必须过滤视图内部要用COALESCE把 NULL 兜住或者在视图定义里额外输出一个is_matched布尔标志列让调用方用这个标志列过滤避免 nullable-side 直接暴露。5. 生产环境回归测试改造完成后的关键动作是把改写前后的 SQL 在同一份测试数据上执行做EXCEPT差集对比-- 差集验证改写前后结果集是否完全一致(SELECT*FROMquery_before)EXCEPT(SELECT*FROMquery_after);(SELECT*FROMquery_after)EXCEPT(SELECT*FROMquery_before);任何一边返回非空都说明改写引入了语义变化需要复盘。6. 走金仓社区的同行者通道坦白讲外连接消除只是国产化迁移里几十种语义陷阱中的一种。 每次踩坑如果只靠自己团队闷头搜文档效率是非常低的。 我这半年养成的习惯是——遇到疑难杂症先去金仓社区 bbs.kingbase.com.cn 看看有没有同类问题的帖子很多时候原厂研发或者前辈们已经把避坑记录留下来了。 前不久金仓社区还发起了「同行者计划」鼓励一线工程师把自己所在企业的迁移场景、优化案例反哺回社区。 我个人觉得这种社区共建 → 集体经验沉淀的模式对于国产化落地阶段是非常有价值的尤其是像外连接消除这种官方文档写得对、但实战里最容易踩的坑靠一线的口口相传远比自己啃产品手册来得快。六、 总结咱们回到文章开头说的那个情况。就是报表少了40%数据的那次。我把改好的SQL上线以后业务那边看新报表看得很满意。他们甚至跑来问我说这个新数据库是不是更聪明一点为什么老系统就算不出来这个数。我当时听完也就是笑笑没多说话。其实吧老系统一直跑的都是错的数据。只不过以前没人去仔细抠过对账口径的细节。金仓 KES 的话它也就是按照SQL标准去跑了。结果呢把以前藏得很深的语义Bug给暴露出来了。这也就是在这次国产化改造的时候刚好碰上了。我自己平时做这种迁移改造做得比较多。所以关于外连接消除这个事我个人的话有三点想法。跟大家总结一下。这事儿真不是数据库的问题说白了就是SQL语义得把好关。 不管你要迁到哪个库里面去。在改造的时候外连接的语义你都得去查一遍。金仓 KES 它就是严格遵守SQL标准的。它跑出来的结果跟主流库基本是一样的。这样的话你换平台的时候数据才不会出问题。看执行计划才是最靠谱的办法。 文档写得再详细也没用。代码看着再怎么标准也没用。最后到底SQL是怎么跑的你还得看执行计划里面的那几个关键字。平时调试的话养成先敲 EXPLAIN 的习惯。这个习惯能帮你省很多事。以后生产上出了问题排查起来也快得多。国产化替换往往仅仅只是个借口其实就是让你去把老代码重新看一遍。 金仓 KES 的优化器在处理语义的时候比较严谨。它对标准的兼容卡得很严。而且它自带的诊断工具也多。这其实也就是逼着我们去把以前的存量代码重新捋一捋。以前老系统里谁也没注意到的语义Bug这次迁移的时候全给你找出来了。这其实不是什么坏事。我觉得这反而是这次国产化改造比较实在的一个用处。外连接消除这个点其实在国产化迁移里只是很小的一部分。后面的话我还会接着写。把遇到的各种迁移坑还有优化办法都发出来。也就是希望能给现在正在做同样事情的兄弟们一点参考。大家看完的话可能就能少踩几个坑了。