优化从大表中每日获取最新贷款ConditionDate的查询性能

针对5亿行的dbo.Loans表，要获取每个LoanName对应的最新ConditionDate（>= '20180101'），现有GROUP BY + MAX查询性能不足，可从以下几个方向优化：

1. 精准优化索引 #

虽然你提到已有完善索引，但可以确认是否创建了覆盖索引：

CREATE NONCLUSTERED INDEX IX_Loans_LoanName_ConditionDate
ON dbo.Loans (LoanName, ConditionDate DESC)
WHERE ConditionDate >= '20180101'; -- 过滤索引，进一步缩小索引范围

这个索引按LoanName分组、ConditionDate降序排列，数据库可直接从索引中获取每个分组的第一条数据（即最大日期），无需扫描全表或回表查询。

2. 预聚合构建每日汇总表 #

既然是每日运行的任务，完全可以构建一张汇总表（比如dbo.LoanLatestDate），只存储每个LoanName的最新ConditionDate，避免每次全表扫描：

• 初始化时跑一次全量查询填充汇总表
• 每日任务只处理当日新增/更新的Loan数据，用增量更新逻辑：

-- 插入新增的LoanName记录
INSERT INTO dbo.LoanLatestDate (LoanName, ConditionDate)
SELECT LoanName, MAX(ConditionDate)
FROM dbo.Loans
WHERE ConditionDate = CAST(GETDATE() AS DATE) -- 假设每日新增数据的ConditionDate为当日
GROUP BY LoanName
WHERE NOT EXISTS (SELECT 1 FROM dbo.LoanLatestDate ld WHERE ld.LoanName = Loans.LoanName);

-- 更新已有LoanName的最新日期
UPDATE ld
SET ld.ConditionDate = l.MaxDate
FROM dbo.LoanLatestDate ld
JOIN (
    SELECT LoanName, MAX(ConditionDate) AS MaxDate
    FROM dbo.Loans
    WHERE ConditionDate = CAST(GETDATE() AS DATE)
    GROUP BY LoanName
) l ON ld.LoanName = l.LoanName
WHERE l.MaxDate > ld.ConditionDate;

这样每日任务仅处理当日增量数据，数据量远小于5亿行，性能会大幅提升。

3. 实施数据分区 #

对dbo.Loans表按ConditionDate进行分区（比如按年或季度分区），查询时数据库只会扫描20180101之后的分区，而非全表：

• 分区函数示例：

CREATE PARTITION FUNCTION pf_Loans_ConditionDate (DATE)
AS RANGE RIGHT FOR VALUES ('20180101', '20190101', '20200101', ...);

绑定分区方案到表后，查询会自动过滤早于2018年的分区，减少扫描的数据量。

4. 查询改写优化 #

如果索引优化后仍有瓶颈，可尝试用TOP 1 WITH TIES结合窗口函数改写查询，部分场景下执行计划会更高效：

SELECT DISTINCT LoanName, ConditionDate
FROM (
    SELECT LoanName, ConditionDate,
           ROW_NUMBER() OVER (PARTITION BY LoanName ORDER BY ConditionDate DESC) AS rn
    FROM dbo.Loans
    WHERE ConditionDate >= '20180101'
) t
WHERE rn = 1;

或者用TOP 1 WITH TIES简化写法：

SELECT TOP 1 WITH TIES LoanName, ConditionDate
FROM dbo.Loans
WHERE ConditionDate >= '20180101'
ORDER BY ROW_NUMBER() OVER (PARTITION BY LoanName ORDER BY ConditionDate DESC);

注意：不同数据库的执行计划优化逻辑不同，需实际测试哪种改写更适配你的环境。

5. 历史数据归档 #

若2018年之前的数据无需频繁查询，可将这部分数据归档到历史表，减少主表数据量，从根源提升查询性能。

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