一条 SQL 背后的逻辑外连接消除的时候优化器到底干了什么我平时在做数据库教学嘛。经常会有学员跑来问一个问题。他们写的明明是 LEFT JOIN。但是执行计划里跑出来的却是 Hash Join。而且呢查出来的数据比他们想的少了很多。刚开始有人问我的时候我也看不太懂那些密密麻麻的执行计划节点。但是后来看得多了去深入查了一下。我发现这里面的逻辑其实挺有意思的。也就是说优化器觉得它很聪明那它到底聪明在什么地方呢你敲下回车到出结果这中间数据库内核要跑很多流程。外连接消除就是其中一个环节。它跑得挺快的但是也很容易让人搞错。今天这篇文章我就来聊聊这个事。从你看到的现象说起接着讲里面的机制最后说说 KES 具体是怎么做的。文章目录一条 SQL 背后的逻辑外连接消除的时候优化器到底干了什么一、优化器到底在干嘛从“你说的”变成“最优的跑法”二、外连接消除怎么触发一个很死板的逻辑推断什么是 Nullable-Side什么是 Null-Rejecting三、优化器是怎么做决定的一步步还原 KES 的处理过程第一步找出外连接节点看看哪边会产生 NULL第二步把 WHERE 里的条件拎出来分析第三步判断等不等价第四步动手改写计划四、哪些情况是不会被消除的场景一用了 IS NULL 条件——其实就是想找那些空行场景二OR 条件里面带了 IS NULL场景三用 CASE WHEN 把 NULL 单独拎出来处理了场景四用 COALESCE / NVL 把 NULL 替换掉了五、KES 优化器做外连接消除的时候有啥不一样① 复合条件它会完整地推导一遍② 能完整兼容 Oracle 的 () 语法③ 执行计划看得见摸得着五、外连接消除跟谓词下推搅在一起的情况很容易看漏谓词下推是个啥意思这种复合场景里面的坑五-B、迁移的时候执行计划变了旧库没问题不代表你写对了旧库当时为啥能跑通我们该怎么去理解这件事六、写代码的人得记住的两条原则① ON 和 WHERE 不是一个地方意思差别很大② 别拿“旧库碰巧没出事”来证明你的 SQL 写对了七、从外连接消除看 SQL 性能优化的整体思路优化器想要达到的两个目标开发的人能从这里面学到啥外连接消除其实就是白捡的性能提升八、简单总结一下一、优化器到底在干嘛从“你说的”变成“最优的跑法”我们先建立一个基本的认识。SQL 其实是一种声明式的语言。你只是告诉数据库“我要什么东西”。你并没有告诉它“怎么去拿这个东西”。那么优化器要干嘛呢它的活儿就是去找一条代价最低的路径。前提是语义得是等价的。这个“语义等价”非常关键。只要最后出来的结果集一模一样优化器就有权去改你的查询语句。外连接消除其实就是一种改写。我们看 KES金仓数据库的优化器。它内部其实分了两个阶段来处理逻辑优化阶段这个阶段就是做等价变换的。目标很简单把 SQL 换个写法意思不变但是更好跑。这里面的动作包括谓词下推、子查询展开还有常量折叠。外连接消除也在这里面。这一步靠的是等价规则。它要保证变换前后的结果集完全对得上。物理优化阶段到了这一步它就拿着前面改好的逻辑计划结合表里的统计信息和代价模型去挑一条最省资源的路。比如说是用 Hash Join 还是 Nested Loop。或者说走不走索引。这些都由它来定。那么外连接消除属于哪一步呢它属于逻辑优化阶段。它在算代价之前就发生了。优化器先通过分析证明“这玩意儿可以消除”。然后再交给物理优化阶段去选最优路径。这两步分工是很明确的。逻辑优化管的是“意思对不对”。物理优化管的是“跑得快不快”。外连接消除之所以归在逻辑优化里就是因为它是一种等价改写。它不是为了调优而调优。改完之后结果必须跟原来一样这是大前提。二、外连接消除怎么触发一个很死板的逻辑推断外连接消除要能成立得满足一个很死板的前提条件。什么条件呢就是WHERE 子句里面存在针对 Nullable-Side 的 Null-Rejecting 条件。什么是 Nullable-Side这个词听起来挺唬人。其实很简单。对于A LEFT JOIN B ON ...来说B 这一侧就是 Nullable-Side。因为 A 表里的数据如果在 B 表找不到匹配那 B 表的列全都会被填成 NULL。如果是A RIGHT JOIN B ON ...那 A 侧就是 Nullable-Side。逻辑是对称的。如果是A FULL JOIN B ON ...那两边都可能是产生 NULL 的一侧。这种情况消除起来就更复杂了。什么是 Null-Rejecting怎么去判断是不是 Null-Rejecting 呢方法就是你把 Nullable-Side 的列全当成 NULL。如果整个条件算出来的结果是 False 或者 Unknown。那它就是一个 Null-Rejecting 条件。我们拿个最常见的例子来看SELECT*FROMorders oLEFTJOINorder_detail dONo.order_idd.order_idWHEREd.amount0;LEFT JOIN 跑完之后。那些orders里面没有对应order_detail的记录它的d.amount就是 NULL。接着就到了 WHERE 这一步。WHERE d.amount 0。你拿 NULL 去跟 0 比大小。结果是 Unknown。那这行数据就被过滤掉了。这就是触发点。如果“外连接加上过滤掉所有 NULL 行”跟“内连接加上同样的过滤”跑出来的东西一模一样。优化器就会直接把 LEFT JOIN 改成 INNER JOIN。下面这些常见条件我给大家列了一下判定结果条件示例传入 NULL 后的结果是否 Null-Rejectingb.status ANULL ‘A’ → Unknown✅ 是b.amount 0NULL 0 → Unknown✅ 是b.name xNULL ‘x’ → Unknown✅ 是b.name LIKE %abc%NULL LIKE … → Unknown✅ 是b.score BETWEEN 60 AND 100NULL BETWEEN … → Unknown✅ 是b.id IS NOT NULLNULL IS NOT NULL → False✅ 是b.id IS NULLNULL IS NULL → True❌ 否b.status A OR b.status IS NULLUnknown OR True → True❌ 否COALESCE(b.amount, 0) 0COALESCE(NULL, 0) 0 → False✅ 是但0 0为FalseNULL行被过滤b.id IS NULL OR b.name xTrue OR Unknown → True❌ 否大家注意看最后两行。如果你写了OR b.status IS NULL。整个 OR 表达式在遇到 NULL 的时候会返回 True。那么 NULL 行就被留下来了。这个时候外连接是不能被消除的。平时写业务代码如果你要处理“允许为空”的情况往往就是用这个技巧。三、优化器是怎么做决定的一步步还原 KES 的处理过程我们拿 KES 来举例。外连接消除是在逻辑计划优化阶段发生的。大概有这么几个步骤第一步找出外连接节点看看哪边会产生 NULL优化器会去遍历那棵逻辑计划树。把所有的 Outer Join 节点找出来。然后确定哪一边是 Nullable-Side。就像前面说的如果是A LEFT JOIN B ON ...。那 B 侧输出列在 A 没匹配上的时候就都是 NULL。第二步把 WHERE 里的条件拎出来分析优化器会去扫 WHERE 子句。把那些引用了 Nullable-Side 列也就是 B 表的列的条件挑出来。然后逐个去做 Null-Rejecting 分析。KES 在这一步做得很细。它不是简单看一眼“WHERE 里有没有右表的列”就完事了。它会对那种用 AND/OR 拼起来的复合条件做完整的布尔推导。我们看个例子WHEREb.statusAAND(b.amount0ORb.amountISNULL)面对这种复合条件优化器会这么干先看b.status A。它是 Null-Rejecting 的。因为 NULL ‘A’ 算出来是 Unknown。接着看b.amount 0 OR b.amount IS NULL。如果 b 是 NULL。那就是NULL 0 OR NULL IS NULL。算出来是Unknown OR True最后是 True。所以这部分不是Null-Rejecting。然后把两部分用 AND 连起来看。Unknown AND True结果是 Unknown。所以整体来看它还是 Null-Rejecting 的。既然整体是 Null-Rejecting那外连接就可以被消除了。第三步判断等不等价优化器要确认一件事。那些满足 Null-Rejecting 的条件是不是能把外连接产生的所有 NULL 行都给干掉。如果确实全都能过滤掉。那就说明“外连接加 WHERE 过滤”跟“内连接加 WHERE 过滤”结果是一样的。消除的条件就成立了。第四步动手改写计划证明完之后优化器就会把 Left Join 节点直接换成 Inner Join 节点。同时它还会把原来应该在 JOIN 后面才做的 WHERE 过滤给下推下去。改完之后的逻辑计划才会交到 CBO 那边去由代价模型挑一个跑起来最快的物理算法。四、哪些情况是不会被消除的场景一用了 IS NULL 条件——其实就是想找那些空行-- 查找没有对应订单详情的主订单SELECTo.order_id,o.customerFROMorders oLEFTJOINorder_detail dONo.order_idd.order_idWHEREd.order_idISNULL;这是一种很常见的写法。目的就是反着找专门找左表里那些在右表没匹配上的数据。d.order_id IS NULL这个条件恰恰就是靠外连接产生的 NULL 才起作用的。你如果把这个外连接给消掉了那这些数据你永远也找不出来了。KES 能认出这种情况。它会保留外连接。这说明它的优化器判定得很准。场景二OR 条件里面带了 IS NULLWHEREb.statusAORb.statusISNULL这种写法的意思是啥呢就是说右表里 status 是 ‘A’ 的数据我要。右表压根没记录也就是 NULL的数据我也要。因为有 IS NULL 这个分支在兜底所以外连接是不能消除的。场景三用 CASE WHEN 把 NULL 单独拎出来处理了WHERECASEWHENb.statusISNULLTHENdefaultELSEb.statusENDdefault里面有 IS NULL 的分支。输入是 NULL 的时候它不会返回 Unknown。所以外连接同样不能消除。场景四用 COALESCE / NVL 把 NULL 替换掉了-- COALESCE 将 NULL 替换为 00 -1 为 TrueNULL 行被保留WHERECOALESCE(b.amount,0)-1用了 COALESCE 或者 NVL 把 NULL 换成了一个具体的数。然后再去比大小。这就有可能让 NULL 行刚好满足条件给漏过去。这就会阻止外连接消除。当然到底能不能漏过去还得看你替换成了什么值以及后面的比较条件是怎么写的。五、KES 优化器做外连接消除的时候有啥不一样① 复合条件它会完整地推导一遍前面其实提到了。KES 不会只看一眼 WHERE 里有没有右表的列就下结论。它会把整个 WHERE 子句拿来做完整的布尔分析。哪怕是 AND 和 OR 扭在一起它也会一步步推导。这就保证了它判定得很准。该消除的它消除不该消除的它绝对不会乱动。② 能完整兼容 Oracle 的()语法KES 是支持 Oracle 以前那种()外连接写法的。而且在语义处理上它跟 Oracle 保持了一致过滤条件没带()的 → 这就跟写在 WHERE 里一样有可能会触发外连接消除过滤条件带上了()的 → 这就等同于放到了 ON 子句里外连接不会被消除-- 可能触发消除b.status 无 ()WHEREa.idb.id()ANDb.statusA-- 不会触发消除b.status 带 ()等同于 ON 条件WHEREa.idb.id()ANDb.status()A这点在把 Oracle 迁移到 KES 的时候特别重要。以前老代码里()怎么用的人都有挺乱的。KES 兼容了这点你就不用去大批量改代码了。但也正因为这样你得仔细去查一查那个()到底加没加对是不是符合你们现在的业务意思。③ 执行计划看得见摸得着你直接跑个EXPLAIN或者EXPLAIN ANALYZE。就能看出来 KES 到底有没有消除外连接EXPLAINSELECTa.id,b.nameFROMaLEFTJOINbONa.idb.idWHEREb.statusactive;如果执行计划里出来的是不带 “Left” 前缀的Hash Join或者Nested Loop。那就说明已经消除了。如果出来的是Left Hash Join或者Left Nested Loop。那就说明外连接还在。这样排查起来就非常直接了。五、外连接消除跟谓词下推搅在一起的情况很容易看漏外连接消除不是自己一个人在跑。它会跟优化器的其他规则搅和在一起。有时候就会产生一些让开发人员看不懂的结果。这里面最常见的就是外连接消除加上谓词下推。谓词下推是个啥意思谓词下推说白了就是把 WHERE 里面的过滤条件尽量往数据源头那边挪。早点过滤掉没用的数据中间产生的过程数据就少了。-- 原始写法外层查询加过滤SELECT*FROM(SELECTo.order_id,o.customer,d.amountFROMorders oLEFTJOINorder_detail dONo.order_idd.order_id)subWHEREsub.amount1000;优化器拿到这段代码它是这么处理的它看出来sub.amount 1000其实是在过滤里面那个子查询里的d.amount。这个amount是从 LEFT JOIN 右边来的属于 Nullable-Side。接着分析amount 1000。NULL 大于 1000 算出来是 Unknown。所以这是个 Null-Rejecting 条件。它判定外连接可以消除就直接改写成内连接了。同时它顺手把这个过滤条件给推到了子查询里面去。最后实际跑的等价 SQL 变成了这样SELECTo.order_id,o.customer,d.amountFROMorders oINNERJOINorder_detail dONo.order_idd.order_idWHEREd.amount1000;你看消除和下推这两步同时发生了。出来的结果集是很精确的。这种复合场景里面的坑危险的地方在哪呢就是当外面有好几个条件去引用子查询的时候复合变换可能就会搞出意外来。SELECT*FROM(SELECTa.id,a.name,b.detail,b.categoryFROMmain_table aLEFTJOINdetail_table bONa.idb.ref_id)subWHEREsub.categoryVIPORsub.detailISNULL;-- 这里显式捕获了 NULL 行遇到这条 SQL优化器得把整个 WHERE 条件拿来做布尔分析先看sub.category VIP。它是 Null-Rejecting 的。因为 NULL ‘VIP’ 是 Unknown。再看sub.detail IS NULL。它不是Null-Rejecting 的。因为 NULL IS NULL 是 TrueNULL 行能过。两个用 OR 连起来。Unknown OR True结果是 True。所以整体不是Null-Rejecting 的。因此外连接就不会被消除。因为有 IS NULL 在里面“保护”了外连接的语义。KES 的推导逻辑能把这种复合情况看得很清楚。它不会因为 OR 前半截是 Null-Rejecting就脑子一热把外连接给消了。这其实就能看出来一个优化器到底是做得精细还是做得粗糙。有些简单的优化器它可能就去扫一眼 WHERE 里有没有右表的字段。它不做完整的布尔分析。一碰到 OR 的场景它就可能会把外连接错误地消掉。KES 这块是做了完整推导的语义上很安全。五-B、迁移的时候执行计划变了旧库没问题不代表你写对了把 Oracle 迁移到 KES 的时候有一个情况反复出现。同一条 SQL在 Oracle 上面跑了三年一点事没有。一迁到 KES结果集突然少了几十行。这真的是数据库本身的差异导致的吗答案往往是否定的。不是新库做错了而是老库的优化器当时“碰巧”没去充分优化它。旧库当时为啥能跑通① 统计信息不准确走了条保守的路优化器要算计划得看统计信息。比如有多少行、数据分布怎么样。如果旧库的统计信息很久没更新了。优化器算 JOIN 代价就算得不准。它可能就会选一条很保守的路。比如用 Nested Loop 去扫右表的时候恰好没有把右侧结果给物化出来。这就导致消除的逻辑根本没被触发。② 老版本的优化器能力确实有限有些低版本的数据库它的优化器没那么聪明。遇到简单的等值条件它可能还会做做外连接消除。但是一碰到那种 AND/OR 组合起来的复合条件它就分析不准了。所以它“碰巧”没把本该消除的外连接给消掉。你那错误的写法反而跑出了“正确”的结果。③ 那会儿数据量小看不出来表里数据少的时候不管走哪条路快慢都差不多。优化器觉得没必要去做复杂的等价变换。但是等数据量变大了或者换到了新库它觉得值得去优化了一优化问题就露出来了。我们该怎么去理解这件事一条 SQL 在旧库上跑出了“正确”的结果。其实只有两种可能语义确实是对的SQL 本身的逻辑没毛病。不管放在哪个库、哪个版本结果都应该是一样的。纯粹是侥幸SQL 写法本身有逻辑问题。只是旧库那个版本、那个优化器状态、那份数据恰好没把问题触发出来。要分清这两种情况你不能去纠结“它在哪个库上跑通了”。你得去审 SQL 的语义本身。也就是把 SQL 翻译成大白话看看你写的逻辑是不是真的就是你想要的逻辑。拿外连接来说如果你的业务意思是“左表的数据全都要右表没匹配上的就显示 NULL”。那你的 WHERE 里面就绝对不能写针对右表字段的过滤条件。除非你写的是 IS NULL 这种专门依赖 NULL 存在的逻辑。这条原则跟用什么数据库没关系。它就是 SQL 语义的基本规矩。六、写代码的人得记住的两条原则① ON 和 WHERE 不是一个地方意思差别很大这是外连接里面最容易搞混的地方。我觉得值得反复说一说ON 子句它管的是“连接规则”。也就是哪些行能凑在一块儿哪些行凑不到一块儿。就算右表没有行能满足 ON 的条件左表的行还是会留下来只不过输出 NULL 罢了。WHERE 子句它管的是“最后的结果筛选”。连接动作全做完了它再对最终结果动刀子。它才不管你这行数据是真实匹配出来的还是外连接硬生生造出来的 NULL。-- ❌ 放在 WHERE 里会触发消除等同于 INNER JOINSELECT*FROMaLEFTJOINbONa.idb.idWHEREb.typeX;-- ✅ 放在 ON 里外连接语义得到保留SELECT*FROMaLEFTJOINbONa.idb.idANDb.typeX;-- 两者结果集的差异-- WHERE 版本只返回 b.type X 的匹配行-- ON 版本返回所有 a 的行b.type X 的显示详情其余显示 NULL② 别拿“旧库碰巧没出事”来证明你的 SQL 写对了测试跑通了不等于语义就是对的。旧库可能在那个特定的数据量下或者特定的统计信息下没有触发消除。这仅仅是“恰好没出问题”。并不代表你的写法就是对的。等迁移到新库优化器做得更彻底了这问题自然就冒出来了。七、从外连接消除看 SQL 性能优化的整体思路去搞懂外连接消除不光是为了不踩坑。它其实给你开了一扇窗让你能看到优化器是怎么干活的。优化器想要达到的两个目标优化器在生成执行计划的时候心里有两个目标第一层保证意思不能错不管它怎么去变换最后的结果集必须是一模一样的。外连接消除只有在一个前提下才会触发。那就是能在数学上证明“LEFT JOIN 加上 WHERE 过滤”跟“INNER JOIN 加上 WHERE 过滤”结果是一样的。这个证明的过程就是我们说的 Null-Rejecting 分析。如果 WHERE 条件能把外连接产生的 NULL 全过滤掉那结果集就等价了。第二层找一条最快的路在保证意思没变的前提下它才会去根据代价模型CBO挑一条跑得最快的路。通常来说INNER JOIN 比 LEFT JOIN 有更多可以优化的地方。它能用更多的 Join 算法也能更随便地去调换 Join 的顺序。所以把外连接消掉往往能带来很明显的性能提升。开发的人能从这里面学到啥外连接消除其实告诉了我们一件事你写 SQL 时候的思考方式应该尽量去贴合优化器的分析方式。优化器是怎么看的呢它看你的条件放在了哪里是 ON 还是 WHERE。它看你的条件遇到 NULL 输入的时候会怎么样是 Null-Rejecting 还是 Null-Passing。它还要看变换完之后结果一不一致。如果你平时写完 SQL也能习惯性地想一想“我写在 WHERE 里的这个条件要是碰上右表的 NULL 行会把它们怎么处理”如果你有这个习惯那你写的时候就能避开大部分外连接的坑。根本不用等到看执行计划的时候才发现问题。外连接消除其实就是白捡的性能提升从性能的角度来说。如果你的 SQL 写法本身就满足了外连接消除的条件也就是说 WHERE 里面恰好有过滤右表非 NULL-Passing 数据的条件。那优化器就会自动去走更高效的路径。你不用自己动手去改 SQL也不用去加 Hint。优化器全给你搞定了。但是有个大前提你的 SQL 语义必须是对的。如果业务上就是需要外连接的语义也就是没匹配的左表行必须留着那你就老老实实把过滤条件写在 ON 里面。语义正确永远是性能优化的前提。你不能为了“让它触发消除”去硬改 SQL 的意思。八、简单总结一下说白了外连接消除就是优化器在“结果得一样”这个框框里做的合法改写。只要 WHERE 里面出现了针对 Nullable-Side 的 Null-Rejecting 条件优化器就会把外连接给干掉。KES 在做这件事的时候布尔推导做得很完整。Oracle 的()老写法它也兼容得很好。而且执行计划看得清清楚楚。去理解这个机制不光能让你写出来的 SQL 意思更明确。碰到数据库迁移、查慢 SQL、看执行计划的时候这也是个绕不开的基础知识。关键概念一句话总结Nullable-SideLEFT JOIN 中可能产生 NULL 的那一侧通常是右表Null-Rejecting条件在 NULL 输入时结果为 False 或 Unknown外连接消除触发条件WHERE 子句对 Nullable-Side 存在 Null-Rejecting 条件ON vs WHERE 的本质区别ON 控制连接规则WHERE 控制结果筛选KES 实现特点完整布尔推导精确识别复合条件Oracle()兼容排查手段EXPLAIN 看 Join 类型EXPLAIN ANALYZE 看实际行数优化器做的每一个决定底下都是有逻辑的。搞明白“为什么这么做”比死记硬背“应该怎么写”要有用得多。活动推荐如果你对 KES 数据库内核优化机制、运维工具开发有兴趣2026金仓数据库智能运维工具开发大赛https://bbs.kingbase.com.cn/forumDetail?articleId2394013b19f3ef84a43edb994692b88e是深入实践 KES 优化器能力的好机会目前正在报名阶段欢迎参与。