如何改写含多OR条件的LEFT JOIN 优化增量插入性能

问题背景 #

执行增量逻辑后，从暂存表TableA向目标表TableB插入数据时，插入操作耗时3-4小时。已为两张表的对应字段添加索引，但原SQL中JOIN条件包含OR，导致索引无法有效发挥作用。原SQL如下：

Select
    s.[column1],
    s.column2
FROM
    TableA S with (nolock) 
    LEFT JOIN TableB t with (nolock) ON
        (
            ISNULL(t.column1, -1) = ISNULL(s.column1, -1)
            AND
            ISNULL(t.[column2],-1) = ISNULL(s.column2,-1)
        )
        OR
        (
            ISNULL(t.column2,-1) = -1
            AND
            ISNULL(s.column2,-1) <> -1
            AND
            ISNULL(t.column1, -1) = ISNULL(s.column1, -1)
        )
        OR
        (
            ISNULL(t.column1,-1) = -1
            AND
            ISNULL(s.column1,-1) <> -1
            AND
            ISNULL(t.column2, -1) = ISNULL(s.column2, -1)
        ) 
WHERE
    t.column2 IS NULL 

改写方案 #

核心思路是用NOT EXISTS替代LEFT JOIN + WHERE NULL的写法，同时去掉ISNULL函数对索引的阻塞，将原OR条件转化为等价的无OR逻辑，让查询优化器能有效利用索引。

改写后的SQL #

SELECT
    s.[column1],
    s.column2
FROM
    TableA s WITH (NOLOCK)
WHERE
    NOT EXISTS (
        SELECT 1
        FROM TableB t WITH (NOLOCK)
        WHERE
            -- column1匹配（含双方均为NULL的情况）
            (t.column1 = s.column1 OR (t.column1 IS NULL AND s.column1 IS NULL))
            AND
            (
                -- column2匹配（含双方均为NULL）
                (t.column2 = s.column2 OR (t.column2 IS NULL AND s.column2 IS NULL))
                -- TableB的column2为NULL，TableA的column2非NULL
                OR (t.column2 IS NULL AND s.column2 IS NOT NULL)
            )
            -- TableB的column1为NULL，TableA的column1非NULL，且column2匹配
            OR (
                t.column1 IS NULL 
                AND s.column1 IS NOT NULL
                AND (t.column2 = s.column2 OR (t.column2 IS NULL AND s.column2 IS NULL))
            )
    )

简化版逻辑 #

可以将条件合并，让逻辑更紧凑，同时保持等价性：

SELECT
    s.[column1],
    s.column2
FROM
    TableA s WITH (NOLOCK)
WHERE
    NOT EXISTS (
        SELECT 1
        FROM TableB t WITH (NOLOCK)
        WHERE
            (
                -- column1匹配，且column2满足原前两个分支条件
                (t.column1 = s.column1 OR (t.column1 IS NULL AND s.column1 IS NULL))
                AND (t.column2 IS NULL OR (t.column2 = s.column2 OR s.column2 IS NULL))
            )
            OR
            (
                -- column2匹配，且TableB的column1为NULL、TableA的column1非NULL
                (t.column2 = s.column2 OR (t.column2 IS NULL AND s.column2 IS NULL))
                AND t.column1 IS NULL 
                AND s.column1 IS NOT NULL
            )
    )

优化说明 #

1. 替换LEFT JOIN为NOT EXISTS：这种写法逻辑等价于原查询，但查询优化器对NOT EXISTS的处理更高效，尤其是当TableB有合适索引时。
2. 移除ISNULL函数：原SQL中ISNULL(t.column1, -1)会导致column1的索引无法被使用，改用直接的NULL比较可以让索引正常生效。
3. 消除JOIN中的OR：将OR条件转移到NOT EXISTS的子查询中，优化器更容易生成高效执行计划，配合TableB上的(column1, column2)复合索引，能大幅提升查询速度。

内容的提问来源于stack exchange，提问作者user23054621