KES数据库优化踩坑指南：外连接消除引发的LEFT JOIN数据丢失问题深度剖析

在使用数据库进行查询优化时,特别是在处理外连接（LEFT JOIN）时,确实可能会遇到一些常见的问题,其中之一就是由于优化不当导致使用外连接（LEFT JOIN）时数据丢失,下面我将详细解析这个问题,并提供一些避免此类问题的策略

目录

• 一、前言
• 二、踩坑案例：错误写法触发外连接消除
• 1. 有问题的SQL写法，过滤条件放在WHERE子句
• 三、底层原理：WHERE写右表条件会触发消除的原因
• 1 SQL语句实际执行的先后顺序
• 2 优化器的等价转换逻辑
• 四、不会触发外连接消除的特殊场景
• 五、KES里三种标准解决办法，规避数据丢失
• 方案1 把右表过滤条件挪到ON后面（优先推荐）
• 2 Oracle兼容(+)语法场景专用写法
• 3 过滤条件放在左表WHERE不受影响
• 六、线上SQL排查、核对小技巧
• 七、全文总结

一、前言

平时做国产库迁移，或是线上SQL调优的时候，不少开发同事都会碰到一种很难看懂的异常现象。我们明明写了LEFT JOIN，本意是把左表里所有数据都查出来，最后返回的记录却少了一大截。

拿执行计划去核对的时候就能发现，原来的左外连接，被优化器悄悄改成了INNER JOIN。这个现象，行业里一般叫做外连接消除。

KES内核自带和Oracle对齐的等价改写逻辑，要是不清楚这套底层转换规则，线上业务很容易出现数据统计出错的问题。

下面我准备了可以直接在KES里跑的测试SQL，搭配执行计划对比，把触发条件、背后逻辑、规避办法全部讲清楚，适配国产化迁移、日常SQL优化这类场景。

先搭好两张测试表用来复现问题，直接复制执行就行：

-- 创建左表t1
CREATE TABLE t1 (
    id1 INT PRIMARY KEY,
    name1 VARCHAR(20)
);
-- 创建右表t2
CREATE TABLE t2 (
    id2 INT PRIMARY KEY,
    name2 VARCHAR(20)
);
-- 插入测试数据
INSERT INTO t1 VALUES (1, '张三'),(2, '李四'),(3, '王五'),(4, '赵六');
INSERT INTO t2 VALUES (1, 'cc'),(2, 'dd');
-- 查看原始数据
SELECT * FROM t1;
SELECT * FROM t2;

原始数据展示
t1表里面一共四条记录：

二、踩坑案例：错误写法触发外连接消除

1. 有问题的SQL写法，过滤条件放在WHERE子句

SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2
WHERE t2.name2 = 'cc';

大家心里预期的查询结果应该是四条，id1等于1的行带出cc，剩下三条t2字段全部为空：

我们用EXPLAIN ANALYZE看执行计划，就能确认优化器做了转换：

EXPLAIN ANALYZE
SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2
WHERE t2.name2 = 'cc';

打印出来的计划关键片段如下：

Hash Join (Inner Join)
  Hash Cond: (t1.id1 = t2.id2)
  -> Seq Scan on t1
  -> Hash
       -> Seq Scan on t2
            Filter: (name2 = 'cc'::character varying)

这里能清楚看到，算子变成了内连接Hash Join，也就是前面说的外连接消除。那些右表匹配为空的数据，直接被过滤丢掉了。

三、底层原理：WHERE写右表条件会触发消除的原因

1 SQL语句实际执行的先后顺序

标准SQL的执行顺序是先执行FROM和JOIN关联，之后才会走WHERE过滤。

• 第一步执行LEFT JOIN之后，t1里面id3、id4这两行，在t2这边没有匹配项，t2所有字段都会填充NULL；
• 第二步走到WHERE t2.name2 = 'cc’这一段，数据库判断NULL和任意常量对比，结果都不成立，这两行就直接被舍弃；
• 整条语句最终的执行效果，和先做内连接再过滤没有区别。

2 优化器的等价转换逻辑

KES优化器会自动做等价判断，这里的逻辑很直白：

• WHERE条件里面，如果是针对右表的等值、范围过滤，所有NULL行最后都会被筛掉。
• 这种场景下LEFT JOIN和INNER JOIN输出的数据完全一致。
• 内连接的计算开销会更低，不用额外保留空行，优化器就会自动替换连接类型，也就是外连接消除。

四、不会触发外连接消除的特殊场景

只有WHERE条件写IS NULL、IS NOT NULL这类判断的时候，优化器不会改动LEFT JOIN。

举一段查询左表无匹配记录的示例SQL：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2
WHERE t2.name2 IS NULL;

这条语句执行出来的结果：

对应的执行计划也能看到保留了左连接算子：

Hash Left Join
  Hash Cond: (t1.id1 = t2.id2)
  -> Seq Scan on t1
  -> Hash
       -> Seq Scan on t2
Filter: (t2.name2 IS NULL)

原因也很好理解，IS NULL本身就是用来抓取关联后空行的逻辑，要是转成内连接，这部分数据直接就没了，优化器不会做这种转换。

五、KES里三种标准解决办法，规避数据丢失

方案1 把右表过滤条件挪到ON后面（优先推荐）

整体思路调整为先过滤右表数据，再执行左关联，左表所有记录都会保留，不会触发消除：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 AND t2.name2 = 'cc';

执行出来的结果符合业务预期，四条数据全部存在：

执行计划也能看到Left Join算子保留完整：

Hash Left Join
  Hash Cond: (t1.id1 = t2.id2)
  -> Seq Scan on t1
  -> Hash
       -> Seq Scan on t2
            Filter: (name2 = 'cc'::character varying)

2 Oracle兼容(+)语法场景专用写法

KES支持Oracle老式(+)左连接语法，这里也容易踩同类坑。
错误写法，过滤条件不带(+)，会触发外连接消除：

SELECT * FROM t1, t2
WHERE t1.id1 = t2.id2(+) AND t2.name2 = 'cc';

正确写法，右表字段同步加上(+)，过滤逻辑下沉到关联阶段：

SELECT * FROM t1, t2
WHERE t1.id1 = t2.id2(+) AND t2.name2(+) = 'cc';

3 过滤条件放在左表WHERE不受影响

如果WHERE里面过滤的是左表字段，不会触发外连接消除，只会提前筛左表原始数据：

-- 只筛选t1里姓名等于张三的数据，左关联特性不会变
SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2
WHERE t1.name1 = '张三';

六、线上SQL排查、核对小技巧

平时调优、迁移排查碰到LEFT JOIN行数不对，可以按下面步骤定位问题：

• 1 先看执行计划里的连接算子名称
  正常保留左连接： Hash Left Join / Nested Loop Left Join
  出现消除： 只写Hash Join、Nested Loop，不带Left标识
• 2 SQL书写规范简单整理
  右表的等值、区间、模糊匹配过滤，统一写到ON子句；
  右表判断空、非空，WHERE里面写没问题；
  左表的过滤条件，WHERE或者ON里写都可以。
• 3 迁移阶段优先保证数据准确
  从Oracle往KES迁移的时候，不要单纯依赖优化器自动改写，数据正确比查询速度更重要。

七、全文总结

• 1 触发外连接消除的核心原因：WHERE子句对LEFT JOIN的右表做非空类等值、范围过滤，NULL行会被全部过滤，优化器自动换成内连接。
• 2 唯一不会触发转换的情况：WHERE条件使用IS NULL / IS NOT NULL判断右表字段。
• 3 最稳妥的修复方式：把右表的过滤条件移动到JOIN后面的ON条件中。
• 4 使用Oracle(+)兼容语法的时候，右表过滤字段也要同步带上(+)标识。
• 5 线上排查异常数据，可以直接拿EXPLAIN ANALYZE查看连接算子，快速确认是否发生外连接消除。

到此这篇关于KES数据库优化踩坑指南：外连接消除引发的LEFT JOIN数据丢失问题深度剖析的文章就介绍到这了,更多相关KES数据库实战避坑内容请搜索北冥有鱼【www.ufcn.cn】以前的文章或继续浏览下面的相关文章希望大家以后多多支持北冥有鱼【www.ufcn.cn】！

您可能感兴趣的文章:

• 使用LEFT JOIN 统计左右存在的数据问题
• MySQL在右表数据不唯一的情况下使用left join的方法
• 数据库中的左连接(left join)和右连接(right join)区别
• 数据库Left join , Right Join, Inner Join 的相关内容，非常实用
• MySQL中SQL连接操作左连接查询(LEFT JOIN)示例详解
• Mysql中left join、right join和inner join(join)的区别及说明
• MySql 字符集不同导致 left join 慢查询的问题解决
• MySQL group by和left join并用解决方式
• 图解Mysql中的LEFT JOIN、RIGHT JOIN与JOIN的区别
• sql语句LEFT JOIN拼接表详解