MSSQL分组取最新记录查询的性能优化方案

现有写法的问题 #

你当前使用的GROUP BY + JOIN写法存在两个明显问题：

• 性能缺陷：无索引场景下需要两次遍历全表，先聚合计算每个ModelGuid的最大GeneratedDate，再回表关联取对应Id，数据量越大IO开销越高
• 逻辑隐患：关联条件仅匹配GeneratedDate，未关联ModelGuid，如果不同ModelGuid存在相同时间戳的记录，会返回错误的关联结果

更高效的查询写法 #

优先使用窗口函数实现需求，所有MSSQL 2008及以上版本都支持该语法，仅需单次遍历全表即可完成计算，性能远高于自连接写法：

SELECT Id, ModelGuid, GeneratedDate
FROM (
    SELECT
        Id,
        ModelGuid,
        GeneratedDate,
        ROW_NUMBER() OVER (PARTITION BY ModelGuid ORDER BY GeneratedDate DESC) AS row_rank
    FROM DomainModels
) temp
WHERE row_rank = 1

说明：该写法会按ModelGuid分组，每组内按生成时间倒序排序，取每组第一条即为最新记录。如果同一ModelGuid下存在多条GeneratedDate完全相同的记录，会固定返回其中一条，完全符合你需要唯一最新记录的要求。

如果你使用的是MSSQL 2022、Azure SQL等更高版本，可以用更简洁的WITH TIES写法，执行效率和窗口函数完全一致：

SELECT TOP 1 WITH TIES Id, ModelGuid, GeneratedDate
FROM DomainModels
ORDER BY ROW_NUMBER() OVER (PARTITION BY ModelGuid ORDER BY GeneratedDate DESC)

大数据量场景的根本优化方案 #

SQL写法优化的收益有限，要彻底解决大数据量下的性能问题，必须建立匹配的覆盖索引：

CREATE NONCLUSTERED INDEX IX_DomainModels_ModelGuid_GeneratedDate
ON DomainModels (ModelGuid, GeneratedDate DESC)
INCLUDE (Id)

该索引按ModelGuid分区存储，同ModelGuid下的记录按GeneratedDate倒序排列，同时包含了查询需要返回的Id字段，所有查询写法都可以直接通过索引拿到结果，不需要回表扫描全表，百万级以上数据量下查询性能可以提升1~2个数量级。

注意：如果坚持使用原有自连接写法，必须修正关联条件，补充ModelGuid匹配逻辑，避免出现数据错配：

SELECT dm1.ModelGuid, dm1.GeneratedDate, dm1.Id
FROM DomainModels dm1
JOIN (
    SELECT ModelGuid, MAX(GeneratedDate) AS GeneratedDate 
    FROM DomainModels 
    GROUP BY ModelGuid
) dm2
ON dm1.ModelGuid = dm2.ModelGuid AND dm1.GeneratedDate = dm2.GeneratedDate

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