无需临时表，如何让SQL分步执行过滤以提升查询性能？

首先，你遇到的这种情况在复杂查询里特别常见——查询优化器有时候会因为多重过滤条件、OR逻辑或者子查询嵌套，生成不够高效的执行计划。不用临时表的话，有几种更简洁的方式可以引导优化器按分步逻辑执行：

1. 使用公共表表达式（CTE）拆分逻辑块 #

CTE能把复杂的过滤逻辑拆成清晰的分步模块，很多数据库（比如SQL Server）会优先计算CTE的结果，再和主表关联，效果类似临时表但写法更简洁：

WITH FilteredExternalKeys AS (
    -- 先计算所有符合条件的RefDocument集合，包含两个查询的过滤规则
    SELECT ex.RefDocument
    FROM ExternalKey ex
    WHERE 
        ex.FieldB = 60 -- 第一个查询的条件
        OR (ex.FieldB = 60 AND ex.FieldC IN (SELECT Id FROM PARAM1))
        OR (ex.FieldB = 61 AND ex.FieldC IN (SELECT Id FROM PARAM3))
        OR (ex.FieldB = 62 AND ex.FieldC IN (SELECT Id FROM PARAM5))
        OR (ex.FieldB = 59 AND ex.FieldC IN (SELECT Id FROM PARAM7))
),
FilteredDocuments AS (
    -- 单独筛选符合FieldA条件的Document
    SELECT d.IdDocument
    FROM Document d
    WHERE 
        d.FieldA IN (SELECT Id FROM PARAM0)
        OR d.FieldA IN (SELECT Id FROM PARAM2)
        OR d.FieldA IN (SELECT Id FROM PARAM4)
        OR d.FieldA IN (SELECT Id FROM PARAM6)
)
-- 最后关联两个预过滤的结果集
SELECT DISTINCT fd.IdDocument
FROM FilteredDocuments fd
INNER JOIN FilteredExternalKeys fek ON fd.IdDocument = fek.RefDocument

这种拆分让优化器更容易识别高效的执行路径，尤其是你的两张表都有合适的索引，CTE的计算会非常快。

2. 用子查询提前筛选核心数据集 #

如果CTE没能引导优化器选择理想计划，你可以把过滤后的结果用子查询提前计算，先拿到所有符合条件的RefDocument，再和Document关联：

SELECT DISTINCT d.IdDocument
FROM Document d
INNER JOIN (
    -- 提前计算所有需要的RefDocument集合
    SELECT ex.RefDocument
    FROM ExternalKey ex
    WHERE 
        ex.FieldB = 60
        OR (ex.FieldB = 60 AND ex.FieldC IN (SELECT Id FROM PARAM1))
        OR (ex.FieldB = 61 AND ex.FieldC IN (SELECT Id FROM PARAM3))
        OR (ex.FieldB = 62 AND ex.FieldC IN (SELECT Id FROM PARAM5))
        OR (ex.FieldB = 59 AND ex.FieldC IN (SELECT Id FROM PARAM7))
) AS filtered_ex ON d.IdDocument = filtered_ex.RefDocument
WHERE 
    d.FieldA IN (SELECT Id FROM PARAM0)
    OR d.FieldA IN (SELECT Id FROM PARAM2)
    OR d.FieldA IN (SELECT Id FROM PARAM4)
    OR d.FieldA IN (SELECT Id FROM PARAM6)

子查询会先完成ExternalKey的过滤，生成一个小结果集，再和Document做JOIN，避免了优化器把多层过滤逻辑混在一起导致的低效扫描。

3. 使用查询提示强制执行顺序 #

如果优化器还是固执地选择了低效计划，你可以用查询提示来“引导”它：

• OPTION (RECOMPILE)：让优化器根据当前TVP的实际数据重新生成计划，避免参数嗅探问题
• OPTION (FORCE ORDER)：强制优化器按照你写的JOIN顺序执行，比如先过滤ExternalKey再关联Document

举个例子：

SELECT DISTINCT d.IdDocument
FROM Document d
INNER JOIN ExternalKey ex ON ex.RefDocument = d.IdDocument
WHERE 
    (d.FieldA IN (SELECT Id FROM PARAM0) AND ex.FieldB = 60 AND ex.FieldC IN (SELECT Id FROM PARAM1))
    OR (d.FieldA IN (SELECT Id FROM PARAM2) AND ex.FieldB = 61 AND ex.FieldC IN (SELECT Id FROM PARAM3))
    OR (d.FieldA IN (SELECT Id FROM PARAM4) AND ex.FieldB = 62 AND ex.FieldC IN (SELECT Id FROM PARAM5))
    OR (d.FieldA IN (SELECT Id FROM PARAM6) AND ex.FieldB = 59 AND ex.FieldC IN (SELECT Id FROM PARAM7))
    OR (ex.FieldB = 60)
OPTION (RECOMPILE, FORCE ORDER)

注意：查询提示是“最后的手段”，不同数据库语法不同，且可能随版本变化失效，优先尝试前两种方法。

4. 把IN子查询改成JOIN（针对TVP） #

TVP和IN子查询的组合有时候不如JOIN高效，你可以把多个TVP的FieldA允许值合并成一个集合，再通过JOIN关联：

WITH AllowedFieldA AS (
    SELECT Id FROM PARAM0
    UNION ALL SELECT Id FROM PARAM2
    UNION ALL SELECT Id FROM PARAM4
    UNION ALL SELECT Id FROM PARAM6
),
AllowedRefDocuments AS (
    SELECT ex.RefDocument
    FROM ExternalKey ex
    WHERE 
        ex.FieldB = 60
        OR (ex.FieldB = 60 AND ex.FieldC IN (SELECT Id FROM PARAM1))
        OR (ex.FieldB = 61 AND ex.FieldC IN (SELECT Id FROM PARAM3))
        OR (ex.FieldB = 62 AND ex.FieldC IN (SELECT Id FROM PARAM5))
        OR (ex.FieldB = 59 AND ex.FieldC IN (SELECT Id FROM PARAM7))
)
SELECT DISTINCT d.IdDocument
FROM Document d
INNER JOIN AllowedFieldA afa ON d.FieldA = afa.Id
INNER JOIN AllowedRefDocuments ard ON d.IdDocument = ard.RefDocument

这种方式利用FieldA的索引快速筛选Document，再和预过滤的RefDocument关联，效率会提升很多。

额外建议：检查执行计划 #

不管用哪种方法，一定要查看执行计划，确认：

• ExternalKey是否用到了(FieldB, FieldC)的包含索引
• Document是否用到了FieldA的索引或主键索引
• 是否存在全表扫描、键查找等低效操作

如果索引没被触发，可能需要更新统计信息（比如UPDATE STATISTICS Document），让优化器了解表的数据分布。

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