SQL外连接消除是怎么回事？从KES看优化器如何改写你的查询

我平时在做数据库教学嘛。经常会有学员跑来问一个问题。他们写的明明是 left join。但是执行计划里跑出来的却是 hash join。而且呢，查出来的数据比他们想的少了很多。

刚开始有人问我的时候，我也看不太懂那些密密麻麻的执行计划节点。但是后来看得多了，去深入查了一下。我发现这里面的逻辑其实挺有意思的。也就是说，优化器觉得它很聪明，那它到底聪明在什么地方呢？

你敲下回车到出结果，这中间数据库内核要跑很多流程。外连接消除就是其中一个环节。它跑得挺快的，但是也很容易让人搞错。今天这篇文章我就来聊聊这个事。从你看到的现象说起，接着讲里面的机制，最后说说 kes 具体是怎么做的。

一、优化器到底在干嘛：从“你说的”变成“最优的跑法”

我们先建立一个基本的认识。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 * from orders o
left join order_detail d on o.order_id = d.order_id
where d.amount > 0;

left join 跑完之后。那些 orders 里面没有对应 order_detail 的记录，它的 d.amount 就是 null。接着就到了 where 这一步。where d.amount > 0。你拿 null 去跟 0 比大小。结果是 unknown。那这行数据就被过滤掉了。

这就是触发点。如果“外连接加上过滤掉所有 null 行”跟“内连接加上同样的过滤”，跑出来的东西一模一样。优化器就会直接把 left join 改成 inner join。

下面这些常见条件，我给大家列了一下判定结果：

大家注意看最后两行。如果你写了 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 拼起来的复合条件做完整的布尔推导。我们看个例子：

where b.status = 'a' and (b.amount > 0 or b.amount is null)

面对这种复合条件，优化器会这么干：

1. 先看 b.status = 'a'。它是 null-rejecting 的。因为 null = ‘a’ 算出来是 unknown。
2. 接着看 b.amount > 0 or b.amount is null。如果 b 是 null。那就是 null > 0 or null is null。算出来是 unknown or true，最后是 true。所以这部分不是 null-rejecting。
3. 然后把两部分用 and 连起来看。unknown and true，结果是 unknown。所以整体来看，它还是 null-rejecting 的。

既然整体是 null-rejecting，那外连接就可以被消除了。

第三步：判断等不等价

优化器要确认一件事。那些满足 null-rejecting 的条件，是不是能把外连接产生的所有 null 行都给干掉。如果确实全都能过滤掉。那就说明“外连接加 where 过滤”跟“内连接加 where 过滤”结果是一样的。消除的条件就成立了。

第四步：动手改写计划

证明完之后，优化器就会把 left join 节点直接换成 inner join 节点。同时，它还会把原来应该在 join 后面才做的 where 过滤给下推下去。改完之后的逻辑计划，才会交到 cbo 那边去，由代价模型挑一个跑起来最快的物理算法。

四、哪些情况是不会被消除的

场景一：用了 is null 条件——其实就是想找那些空行

-- 查找没有对应订单详情的主订单
select o.order_id, o.customer
from orders o
left join order_detail d on o.order_id = d.order_id
where d.order_id is null;

这是一种很常见的写法。目的就是反着找，专门找左表里那些在右表没匹配上的数据。d.order_id is null 这个条件，恰恰就是靠外连接产生的 null 才起作用的。你如果把这个外连接给消掉了，那这些数据你永远也找不出来了。

kes 能认出这种情况。它会保留外连接。这说明它的优化器判定得很准。

场景二：or 条件里面带了 is null

where b.status = 'a' or b.status is null

这种写法的意思是啥呢？就是说右表里 status 是 ‘a’ 的数据我要。右表压根没记录（也就是 null）的数据我也要。因为有 is null 这个分支在兜底，所以外连接是不能消除的。

场景三：用 case when 把 null 单独拎出来处理了

where case when b.status is null then 'default' else b.status end = 'default'

里面有 is null 的分支。输入是 null 的时候它不会返回 unknown。所以外连接同样不能消除。

场景四：用 coalesce / nvl 把 null 替换掉了

-- coalesce 将 null 替换为 0，0 > -1 为 true，null 行被保留
where coalesce(b.amount, 0) > -1

用了 coalesce 或者 nvl 把 null 换成了一个具体的数。然后再去比大小。这就有可能让 null 行刚好满足条件给漏过去。这就会阻止外连接消除。当然，到底能不能漏过去，还得看你替换成了什么值，以及后面的比较条件是怎么写的。

五、kes 优化器做外连接消除的时候，有啥不一样

① 复合条件它会完整地推导一遍

前面其实提到了。kes 不会只看一眼 where 里有没有右表的列就下结论。它会把整个 where 子句拿来做完整的布尔分析。哪怕是 and 和 or 扭在一起，它也会一步步推导。这就保证了它判定得很准。该消除的它消除，不该消除的它绝对不会乱动。

② 能完整兼容 oracle 的(+)语法

kes 是支持 oracle 以前那种 (+) 外连接写法的。而且在语义处理上，它跟 oracle 保持了一致：

• 过滤条件没带 (+) 的 → 这就跟写在 where 里一样，有可能会触发外连接消除
• 过滤条件带上了 (+) 的 → 这就等同于放到了 on 子句里，外连接不会被消除

-- 可能触发消除（b.status 无 (+)）
where a.id = b.id(+) and b.status = 'a'

-- 不会触发消除（b.status 带 (+)，等同于 on 条件）
where a.id = b.id(+) and b.status(+) = 'a'

这点在把 oracle 迁移到 kes 的时候特别重要。以前老代码里 (+) 怎么用的人都有，挺乱的。kes 兼容了这点，你就不用去大批量改代码了。但也正因为这样，你得仔细去查一查，那个 (+) 到底加没加对，是不是符合你们现在的业务意思。

③ 执行计划看得见摸得着

你直接跑个 explain 或者 explain analyze。就能看出来 kes 到底有没有消除外连接：

explain
select a.id, b.name
from a left join b on a.id = b.id
where b.status = 'active';

如果执行计划里出来的是不带 “left” 前缀的 hash join 或者 nested loop。那就说明已经消除了。如果出来的是 left hash join 或者 left nested loop。那就说明外连接还在。这样排查起来就非常直接了。

五、外连接消除跟谓词下推搅在一起的情况：很容易看漏

外连接消除不是自己一个人在跑。它会跟优化器的其他规则搅和在一起。有时候就会产生一些让开发人员看不懂的结果。这里面最常见的就是外连接消除加上谓词下推。

谓词下推是个啥意思

谓词下推说白了就是：把 where 里面的过滤条件，尽量往数据源头那边挪。早点过滤掉没用的数据，中间产生的过程数据就少了。

-- 原始写法：外层查询加过滤
select * from (
    select o.order_id, o.customer, d.amount
    from orders o
    left join order_detail d on o.order_id = d.order_id
) sub
where sub.amount > 1000;

优化器拿到这段代码，它是这么处理的：

1. 它看出来 sub.amount > 1000 其实是在过滤里面那个子查询里的 d.amount。
2. 这个 amount 是从 left join 右边来的，属于 nullable-side。
3. 接着分析 amount > 1000。null 大于 1000 算出来是 unknown。所以这是个 null-rejecting 条件。
4. 它判定外连接可以消除，就直接改写成内连接了。
5. 同时它顺手把这个过滤条件给推到了子查询里面去。

最后实际跑的等价 sql 变成了这样：

select o.order_id, o.customer, d.amount
from orders o
inner join order_detail d on o.order_id = d.order_id
where d.amount > 1000;

你看，消除和下推这两步同时发生了。出来的结果集是很精确的。

这种复合场景里面的坑

危险的地方在哪呢？就是当外面有好几个条件去引用子查询的时候，复合变换可能就会搞出意外来。

select *
from (
    select a.id, a.name, b.detail, b.category
    from main_table a
    left join detail_table b on a.id = b.ref_id
) sub
where sub.category = 'vip'
   or sub.detail is null;   -- 这里显式捕获了 null 行

遇到这条 sql，优化器得把整个 where 条件拿来做布尔分析：

• 先看 sub.category = 'vip'。它是 null-rejecting 的。因为 null = ‘vip’ 是 unknown。
• 再看 sub.detail is null。它不是 null-rejecting 的。因为 null is null 是 true，null 行能过。
• 两个用 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 join
select * from a left join b on a.id = b.id where b.type = 'x';

-- ✅ 放在 on 里，外连接语义得到保留
select * from a left join b on a.id = b.id and b.type = 'x';

-- 两者结果集的差异：
-- 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、看执行计划的时候，这也是个绕不开的基础知识。

优化器做的每一个决定，底下都是有逻辑的。搞明白“为什么这么做”，比死记硬背“应该怎么写”要有用得多。

到此这篇关于sql外连接消除是怎么回事？从kes看优化器如何改写你的查询的文章就介绍到这了,更多相关数据库优化器外连接消除原理内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！