.NET 8 + EF Core 8分页计数查询性能优化求助

当前场景与问题 #

我们基于.NET 8 + EF Core 8开发，分页前的Claim计数查询存在性能瓶颈：初始查询耗时40秒，将Azure SQL弹性池的e-DTUs从5提升至50后，耗时降至4秒，但仍未达到预期。

表结构信息 #

• Action表：20行
• Event表：640,338行（服务多模块，通过ReferenceId与Claim软关联）
• Claim表：62,000行

现有代码与SQL #

EF Core生成的查询代码：

var query = Context.Set<ClaimEntity>().AsQueryable();
query = query.Where(x => x.Events.AsQueryable().OrderByDescending(e => e.CreatedOn).Select(e => e.Action.Code).FirstOrDefault() == "CREATE_CLAIM");
var total = query.Count();

对应的SQL语句：

SELECT COUNT(*)
    FROM [dc].[Claim] AS [c]
    WHERE 
     (
          SELECT TOP(1) [a].[Code]
          FROM [dc].[Event] AS [e]
          INNER JOIN [dc].[Action] AS [a] ON [e].[ActionId] = [a].[Id]
          WHERE [c].[Id] = [e].[ReferenceId]
          ORDER BY [e].[CreatedOn] DESC
        ) = N'CREATE_CLAIM' 
        AND 
        [c].[DeletedOn] IS NULL

已尝试的优化：使用SSMS数据库引擎调优顾问生成索引，但查询耗时仍为4秒：

CREATE NONCLUSTERED INDEX [_dta_index_Event_19_34099162__K3_K2_K10] ON [dc].[Event]
        (
            [ReferenceId] ASC,
            [ActionId] ASC,
            [CreatedOn] ASC
        )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

优化方案 #

1. 重构查询逻辑，消除相关子查询 #

当前SQL对每个Claim执行一次子查询获取最新Event的ActionCode，属于逐行触发的相关子查询，效率极低。改用JOIN+窗口函数先筛选每个ReferenceId的最新Event，再关联统计：

优化后的SQL：

SELECT COUNT(DISTINCT c.Id)
FROM [dc].[Claim] c
INNER JOIN (
    SELECT 
        e.ReferenceId,
        a.Code,
        ROW_NUMBER() OVER (PARTITION BY e.ReferenceId ORDER BY e.CreatedOn DESC) AS rn
    FROM [dc].[Event] e
    INNER JOIN [dc].[Action] a ON e.ActionId = a.Id
) latest_event ON c.Id = latest_event.ReferenceId
WHERE latest_event.rn = 1 
  AND latest_event.Code = N'CREATE_CLAIM'
  AND c.DeletedOn IS NULL

对应的EF Core代码调整：

var latestEventQuery = Context.Set<EventEntity>()
    .Join(Context.Set<ActionEntity>(), 
          e => e.ActionId, 
          a => a.Id, 
          (e, a) => new { e.ReferenceId, a.Code, e.CreatedOn })
    .GroupBy(x => x.ReferenceId)
    .Select(g => g.OrderByDescending(x => x.CreatedOn).FirstOrDefault())
    .Where(x => x.Code == "CREATE_CLAIM");

var total = Context.Set<ClaimEntity>()
    .Where(c => c.DeletedOn == null)
    .Join(latestEventQuery, 
          c => c.Id, 
          le => le.ReferenceId, 
          (c, le) => c)
    .Count();

2. 优化Event表的覆盖索引 #

现有索引的字段顺序和包含字段不合理，建议创建覆盖索引，让数据库无需回表查询：

CREATE NONCLUSTERED INDEX IX_Event_ReferenceId_CreatedOn
ON [dc].[Event] (ReferenceId ASC, CreatedOn DESC)
INCLUDE (ActionId); -- 包含关联Action所需的字段，避免键查找

3. 给Claim表添加过滤索引 #

如果DeletedOn IS NULL是高频过滤条件，创建过滤索引加速基础查询：

CREATE NONCLUSTERED INDEX IX_Claim_DeletedOn
ON [dc].[Claim] (Id)
WHERE DeletedOn IS NULL;

4. 预计算缓存（可选） #

若计数查询的实时性要求不高，可定期预计算结果并缓存（如Redis或数据库缓存表），比如每分钟计算一次符合条件的Claim数量，直接读取缓存值彻底解决性能问题。

5. 分析查询执行计划 #

在SSMS中执行原查询，查看实际执行计划，重点检查：

• 是否存在表扫描/聚集索引扫描
• 是否有高开销的键查找操作
• 排序步骤的资源消耗
  根据执行计划提示针对性调整索引或查询逻辑。

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