SQL Server 2014中高效排除全关联排除表出版商的方法

核心优化思路 #

原计数对比的方案需要对每条Work的所有关联Publisher做统计计算，在300万行的主表上会触发大量关联和聚合操作，直接导致性能暴跌。我们换个逻辑：只保留那些至少有一个关联Publisher不在ExcludedPublishers表中的Work，用存在性判断快速筛选出目标数据（≤1000行），再对这部分少量数据做CSV拼接，从根源上减少计算量。

具体实现代码 #

SELECT 
    w.WorkId,
    w.WorkName,
    -- 拼接关联Publishers为CSV列表（SQL Server 2014无STRING_AGG，用FOR XML PATH实现）
    STUFF((
        SELECT ',' + p.PublisherName
        FROM Publishers p
        WHERE p.WorkId = w.WorkId
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS PublisherList
FROM Works w
-- 关键筛选逻辑：存在至少一个未被排除的关联Publisher
WHERE EXISTS (
    SELECT 1
    FROM Publishers p
    WHERE p.WorkId = w.WorkId
      AND NOT EXISTS (
          SELECT 1
          FROM ExcludedPublishers ep
          WHERE ep.PublisherId = p.PublisherId
      )
)

性能提升关键点 #

• 替换计数为EXISTS半连接：EXISTS是短循环逻辑，只要找到符合条件的记录就停止扫描，不需要统计所有关联Publisher的数量，相比COUNT类聚合能大幅减少IO和计算开销。
• 先筛选后拼接：先通过WHERE子句把300万行主表过滤到≤1000行，再对这部分数据执行拼接操作，避免了对全表所有行执行耗时的子查询拼接。
• 配套索引优化：添加以下索引能进一步提速：
  • CREATE NONCLUSTERED INDEX IX_Publishers_WorkId_PubId_Name ON Publishers(WorkId, PublisherId, PublisherName);：覆盖关联筛选和拼接所需的所有字段，避免回表查询。
  • CREATE NONCLUSTERED INDEX IX_ExcludedPublishers_PubId ON ExcludedPublishers(PublisherId);：加速排除列表的存在性判断。

备选JOIN写法 #

如果更习惯用JOIN语法，也可以用以下实现，性能与EXISTS方案接近：

SELECT DISTINCT
    w.WorkId,
    w.WorkName,
    STUFF((
        SELECT ',' + p.PublisherName
        FROM Publishers p
        WHERE p.WorkId = w.WorkId
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS PublisherList
FROM Works w
JOIN Publishers p ON w.WorkId = p.WorkId
LEFT JOIN ExcludedPublishers ep ON p.PublisherId = ep.PublisherId
WHERE ep.PublisherId IS NULL

这里的DISTINCT是因为一个Work可能对应多个未被排除的Publisher，JOIN后会产生重复行，需要去重。

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