查询Website列表关联最新CheckLog耗时过长，该如何优化？

1. 能否通过优化索引解决？ #

现有[WebsiteId, CreatedTime]联合索引已经是该场景下最优的索引配置，哪怕调整索引顺序或排序规则，也无法解决核心性能问题。
性能瓶颈的根源是EF Core默认生成的窗口函数SQL需要全量扫描300万条CheckLog记录，给所有记录计算行号后再过滤出每个Website的第一条，哪怕Website只有20条，也要遍历全量日志表，这是查询逻辑的问题，不是索引能解决的。

2. 无N+1的高效实现方案 #

推荐用两次查询+内存关联的方案，全程只请求2次数据库，总耗时可以降到10毫秒级：

// 第一步：查询全量Website，仅20条，性能无损耗
var allWebsites = await ctx.Websites.AsNoTracking().ToListAsync();
var websiteIdList = allWebsites.Select(x => x.Id).ToList();
// 兼容空列表边界情况
if (!websiteIdList.Any()) return new List<WebsiteListItem>();

// 第二步：批量查询每个Website最新的1条CheckLog，完全命中联合索引
var latestLogQuery = websiteIdList
    .Select(id => ctx.CheckLogs
        .Where(cl => cl.WebsiteId == id)
        .OrderByDescending(cl => cl.CreatedTime)
        .Take(1))
    .Aggregate(Queryable.Concat);

var latestLogs = await latestLogQuery.AsNoTracking().ToListAsync();

// 第三步：内存中关联组装结果
var result = allWebsites.Select(w => new WebsiteListItem
{
    Website = w,
    LatestCheckLog = latestLogs.FirstOrDefault(cl => cl.WebsiteId == w.Id)
}).ToList();

return result;

该方案生成的第二次SQL是20个SELECT TOP 1 * FROM CheckLogs WHERE WebsiteId = @id ORDER BY CreatedTime DESC的UNION ALL查询，每个子查询都直接命中联合索引，不需要扫描全表，直接拿每个WebsiteId下的第一条索引记录即可。

3. 通用场景解法 #

这类需求属于经典的每组Top N查询，英文搜索关键词为greatest-n-per-group，中文搜索关键词为分组取最新N条，通用优化思路有三种：

• 当分组数量很少时（比如本场景只有20个Website）：优先用两次查询+内存关联的方案，实现简单性能高
• 当分组数量较多时：可以用SQL的横向连接（SQL Server的CROSS APPLY、MySQL的LATERAL JOIN），EF Core也支持生成这类SQL，性能优于窗口函数全表扫描
• 当读请求远高于写请求时：可以在主表（Website）加冗余字段比如LatestCheckLogId、LatestCheckTime，写入CheckLog时同步更新冗余字段，查询时直接读取主表即可，性能最高

4. 更高频场景的冗余优化 #

如果该查询是核心高频接口，还可以做字段冗余进一步提效：
给Website表新增LatestCheckLogId、LatestCheckTime字段，每次新增CheckLog时，判断是否比当前Website存储的LatestCheckTime新，如果是就更新冗余字段。查询时直接读Website表，最多再做一次主键Join拿CheckLog的其他字段，耗时可以降到1毫秒级。

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