如何优化‘取历史最新行+全部未来行’的TOP N查询性能？

我有一张包含数据的表，有分组属性列和日期列，需要优化以下查询的性能：获取TOP X行数据，要求仅返回历史数据中的最新行，同时返回全部未来数据。

示例数据 #

当前查询 #

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY GroupingId
            ORDER BY Date DESC) 
        as rnk
    FROM myData
    WHERE Date <= SYSUTCDATETIME()
    UNION
    SELECT *, 1 as rnk
    FROM myData
    WHERE Date > SYSUTCDATETIME()
)
SELECT *
FROM cte
WHERE rnk = 1
ORDER BY GroupingId
OFFSET 0 ROWS Fetch NEXT 100 ROWS ONLY

预期输出 #

性能瓶颈说明 #

实际数据结构是简化版，当前查询的性能瓶颈在于：SQL Server需要将CTE中历史数据部分全部具体化（从磁盘读取），因为存在ORDER BY和过滤条件。即使表中有数百万行，也需要查询能立即执行，避免加载所有数据到内存。

补充信息 #

• 已存在索引：

CREATE NONCLUSTERED INDEX [idx_groupingId_date_test] ON [dbo].[myData]
(
    [GroupingId] ASC,
    [Date] DESC
)

• 数据分布：历史数据远多于未来数据，每个GroupingId可能有少量未来行，但有几十到几百条历史行。
• 移除UNION后查询速度极快，这是引入性能问题的近期变更。

一、查询改写：避免全量扫描历史数据 #

核心思路是只获取每个GroupingId的最新历史行，而非先扫描所有历史数据再排重，同时直接获取所有未来行，最后合并结果并分页。

改写后的查询 #

WITH LatestHistory AS (
    -- 仅获取每个GroupingId的最新历史行，利用索引快速定位
    SELECT m.*
    FROM myData m
    WHERE Date <= SYSUTCDATETIME()
    AND NOT EXISTS (
        SELECT 1
        FROM myData m2
        WHERE m2.GroupingId = m.GroupingId
        AND m2.Date <= SYSUTCDATETIME()
        AND m2.Date > m.Date
    )
),
FutureData AS (
    -- 直接获取所有未来数据
    SELECT *
    FROM myData
    WHERE Date > SYSUTCDATETIME()
)
SELECT *
FROM (
    SELECT * FROM LatestHistory
    UNION ALL
    SELECT * FROM FutureData
) Combined
ORDER BY GroupingId
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

优化点 #

• 利用现有索引idx_groupingId_date_test，NOT EXISTS可以快速定位每个GroupingId的最大历史日期行，无需扫描所有历史数据。
• 使用UNION ALL替代UNION，避免重复数据检查（历史和未来数据无重叠，无需去重），减少额外开销。

二、索引优化：覆盖索引减少键查找 #

现有索引只包含GroupingId和Date，查询时可能需要回表获取其他列（如Id、Whatever），可以扩展为覆盖索引：

CREATE NONCLUSTERED INDEX [idx_groupingId_date_covering] ON [dbo].[myData]
(
    [GroupingId] ASC,
    [Date] DESC
)
INCLUDE ([Id], [Whatever]) -- 包含所有查询需要返回的列

作用 #

• 覆盖索引可以让查询直接从索引中获取所有需要的数据，避免键查找操作，大幅提升性能。
• 对于LatestHistory的NOT EXISTS查询，覆盖索引能让SQL Server直接在索引内完成比较，无需访问基表。

三、利用TOP + 索引定位最新历史行 #

如果NOT EXISTS的写法在你的环境中性能仍有瓶颈，可以尝试用TOP 1 WITH TIES结合窗口函数的简化写法，同样利用索引：

WITH LatestHistory AS (
    SELECT TOP 1 WITH TIES *
    FROM myData
    WHERE Date <= SYSUTCDATETIME()
    ORDER BY ROW_NUMBER() OVER (PARTITION BY GroupingId ORDER BY Date DESC)
),
FutureData AS (
    SELECT *
    FROM myData
    WHERE Date > SYSUTCDATETIME()
)
SELECT *
FROM (
    SELECT * FROM LatestHistory
    UNION ALL
    SELECT * FROM FutureData
) Combined
ORDER BY GroupingId
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

说明 #

• TOP 1 WITH TIES结合窗口函数，能快速筛选出每个GroupingId的最新历史行，同样依赖GroupingId + Date DESC的索引。

四、索引视图（可选，适合频繁查询场景） #

如果这个查询是高频执行的，可以创建索引视图预先计算每个GroupingId的最新历史行，结合未来数据查询：

创建索引视图 #

CREATE VIEW vw_LatestHistory
WITH SCHEMABINDING
AS
SELECT 
    GroupingId,
    MAX(Date) AS LatestHistoryDate,
    MAX(CASE WHEN Date = (SELECT MAX(Date) FROM dbo.myData m2 WHERE m2.GroupingId = m.GroupingId AND m2.Date <= SYSUTCDATETIME()) THEN Id END) AS Id,
    MAX(CASE WHEN Date = (SELECT MAX(Date) FROM dbo.myData m2 WHERE m2.GroupingId = m.GroupingId AND m2.Date <= SYSUTCDATETIME()) THEN Whatever END) AS Whatever,
    COUNT_BIG(*) AS RowCount -- 索引视图必须包含COUNT_BIG
FROM dbo.myData m
WHERE Date <= SYSUTCDATETIME()
GROUP BY GroupingId
GO

CREATE UNIQUE CLUSTERED INDEX idx_vw_LatestHistory ON vw_LatestHistory(GroupingId)

使用索引视图的查询 #

SELECT * FROM vw_LatestHistory
UNION ALL
SELECT * FROM myData WHERE Date > SYSUTCDATETIME()
ORDER BY GroupingId
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

注意事项 #

• 索引视图会占用额外存储空间，且数据更新时会有维护开销，适合数据更新频率低、查询频率高的场景。
• 需确保视图中的函数（如SYSUTCDATETIME()）符合索引视图的要求，若有问题可考虑用静态日期或改用触发器维护。

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