优化多子查询TSQL语句：合并同表重复查询提升性能

场景说明 #

现有两张表：

Products表 #

History表（数据量极大） #

当前使用的查询语句（注：原语句中WHERE与FROM顺序错误，已修正）：

SELECT 
    p.reference,
    p.field3, p.field4,
    (SELECT TOP 1 datetime 
     FROM history h 
     WHERE h.reference = p.reference 
       AND physicalcode = 'TST' 
       AND issue = '0' 
     ORDER BY datetime DESC) AS latest_date_issue_0,
    (SELECT TOP 1 datetime 
     FROM history h 
     WHERE h.reference = p.reference 
       AND physicalcode = 'TST' 
       AND issue = '1' 
     ORDER BY datetime DESC) AS latest_date_issue_1,
    (SELECT TOP 1 datetime 
     FROM history h 
     WHERE h.reference = p.reference 
       AND utilcode = 'phone' 
     ORDER BY datetime DESC) AS latest_date_phone,
    (SELECT TOP 1 media 
     FROM history h 
     WHERE h.reference = p.reference 
       AND utilcode = 'phone' 
     ORDER BY datetime DESC) AS latest_media
    -- 还有更多类似条件的子查询
FROM
    products p
WHERE
    p.field3 = 'valX'
    AND p.field4 = 'valY'

由于History表数据量庞大，上述语句中每个子查询都会单独扫描表（或索引），当Products筛选结果集较大时，性能急剧下降。尝试过ROW_NUMBER()和CTE但未得到理想效果，需要优化查询逻辑，合并重复的表访问。

优化方案 #

方案一：使用窗口函数+条件聚合 #

通过一次扫描History表的目标数据，为每个reference的不同条件组生成行号，再关联Products表进行条件聚合提取最新记录：

WITH History_Ranked AS (
    SELECT 
        reference,
        datetime,
        media,
        physicalcode,
        issue,
        -- 为不同条件组生成行号，按时间倒序，行号=1即为最新记录
        ROW_NUMBER() OVER (
            PARTITION BY reference, physicalcode, issue 
            ORDER BY datetime DESC
        ) AS rn_tst_issue,
        ROW_NUMBER() OVER (
            PARTITION BY reference, utilcode 
            ORDER BY datetime DESC
        ) AS rn_utilcode
    FROM History
    -- 提前过滤无关数据，减少后续计算量
    WHERE 
        (physicalcode = 'TST' AND issue IN ('0','1')) 
        OR utilcode = 'phone'
)
SELECT 
    p.reference,
    p.field3,
    p.field4,
    -- 提取对应条件的最新日期
    MAX(CASE WHEN physicalcode = 'TST' AND issue = '0' AND rn_tst_issue = 1 THEN datetime END) AS latest_date_issue_0,
    MAX(CASE WHEN physicalcode = 'TST' AND issue = '1' AND rn_tst_issue = 1 THEN datetime END) AS latest_date_issue_1,
    MAX(CASE WHEN utilcode = 'phone' AND rn_utilcode = 1 THEN datetime END) AS latest_date_phone,
    MAX(CASE WHEN utilcode = 'phone' AND rn_utilcode = 1 THEN media END) AS latest_media
FROM Products p
LEFT JOIN History_Ranked h ON p.reference = h.reference
WHERE 
    p.field3 = 'valX'
    AND p.field4 = 'valY'
GROUP BY p.reference, p.field3, p.field4;

方案二：使用OUTER APPLY关联查询 #

APPLY可以针对Products的每一行，执行一次目标查询，相比原语句的子查询，它能复用关联逻辑，且可以在单个APPLY中同时获取多个字段（比如日期和media）：

SELECT 
    p.reference,
    p.field3,
    p.field4,
    t0.latest_date AS latest_date_issue_0,
    t1.latest_date AS latest_date_issue_1,
    phone.latest_date AS latest_date_phone,
    phone.latest_media AS latest_media
FROM Products p
-- 获取physicalcode=TST且issue=0的最新记录
OUTER APPLY (
    SELECT TOP 1 datetime AS latest_date
    FROM History h
    WHERE h.reference = p.reference 
      AND physicalcode = 'TST' 
      AND issue = '0'
    ORDER BY datetime DESC
) t0
-- 获取physicalcode=TST且issue=1的最新记录
OUTER APPLY (
    SELECT TOP 1 datetime AS latest_date
    FROM History h
    WHERE h.reference = p.reference 
      AND physicalcode = 'TST' 
      AND issue = '1'
    ORDER BY datetime DESC
) t1
-- 获取utilcode=phone的最新记录，同时提取日期和media
OUTER APPLY (
    SELECT TOP 1 datetime AS latest_date, media AS latest_media
    FROM History h
    WHERE h.reference = p.reference 
      AND utilcode = 'phone'
    ORDER BY datetime DESC
) phone
WHERE 
    p.field3 = 'valX'
    AND p.field4 = 'valY';

关键索引优化 #

针对History表建立以下组合索引，能大幅提升查询效率：

1. 适配physicalcode+issue条件的索引：

CREATE NONCLUSTERED INDEX IX_History_TestIssue 
ON History(reference, physicalcode, issue) 
INCLUDE (datetime);

2. 适配utilcode条件的索引：

CREATE NONCLUSTERED INDEX IX_History_UtilCode 
ON History(reference, utilcode) 
INCLUDE (datetime, media);

这些索引能让数据库直接定位到目标数据，避免全表扫描。

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