EF Core基于日期范围过滤数据超时，求性能优化方案

问题原因 #

• 重复子查询导致多次表扫描：生成的SQL中，每个Test对应5次RecordData子查询（SELECT子句3次，WHERE子句2次），每条Test都要重复查询关联的RecordData数据。当数据量较大时，数据库会重复扫描RecordData表，IO开销急剧上升，这是超时的核心原因。
• 筛选逻辑后置浪费算力：当前逻辑先计算所有Test的聚合值（首次/末次上报时间、记录数），再做日期筛选，相当于先处理全量Test数据，再过滤不符合条件的结果，完全没利用数据库的提前过滤能力，白白消耗计算资源。
• 索引缺失放大性能瓶颈：如果RecordData表没有针对(IsActive, TestId, CollectionDate)的复合索引，每个子查询都要执行全表扫描或低效的索引查找，进一步拖慢查询速度。

优化建议 #

1. 重构Linq查询：先聚合后关联 #

将聚合和筛选逻辑前置，先从RecordData表聚合出符合条件的Test核心信息，再关联Test表获取详情，彻底避免重复子查询。示例代码如下：

// 先聚合RecordData，获取每个Test的关键统计值
var recordAggregates = dbContext.RecordData
    .Where(rd => rd.IsActive)
    .GroupBy(rd => rd.TestId)
    .Select(g => new 
    {
        TestId = g.Key,
        FirstReported = g.Min(rd => rd.CollectionDate),
        LastReported = g.Max(rd => rd.CollectionDate),
        NumberOfRecords = g.Count()
    });

// 应用日期筛选条件
if (providedStartDate is not null)
{
    var startDateUTC = providedStartDate.ToUniversalTime().Date;
    recordAggregates = recordAggregates.Where(ra => ra.LastReported >= startDateUTC);
}
if (providedEndDate is not null)
{
    var endDateUTC = providedEndDate.ToUniversalTime().Date;
    recordAggregates = recordAggregates.Where(ra => ra.FirstReported <= endDateUTC);
}

// 关联Test表并添加分页
var tests = recordAggregates
    .Join(dbContext.Tests.Where(t => t.IsActive), 
          ra => ra.TestId, 
          t => t.Id, 
          (ra, t) => new Test 
          {
              Id = t.Id,
              // 填充其他Test字段...
              FirstReported = ra.FirstReported,
              LastReported = ra.LastReported,
              NumberOfRecords = ra.NumberOfRecords
          })
    .AsNoTracking()
    .Skip(pageIndex * pageSize)
    .Take(pageSize);

// 单独处理计数查询
var totalCount = await recordAggregates.CountAsync();

优化后的SQL会通过一次GroupBy聚合完成统计，再关联Test表，避免了重复子查询，性能会显著提升。

2. 创建针对性复合索引 #

在RecordData表上创建复合索引，让聚合和筛选操作可以直接利用索引快速定位数据：

CREATE INDEX IX_RecordData_IsActive_TestId_CollectionDate 
ON public."RecordData" ("IsActive", "TestId", "CollectionDate");

这个索引可以让数据库先按IsActive过滤数据，再按TestId分组，同时直接取出最小/最大的CollectionDate，无需额外排序。

3. 优化大偏移量分页（可选） #

如果分页的OFFSET值很大（比如翻到第1000页），传统LIMIT+OFFSET的性能会急剧下降，可改用键集分页（Keyset Pagination）：

// 假设上一页最后一条数据的TestId和LastReported
var lastTestId = ...;
var lastReported = ...;

var tests = recordAggregates
    .Join(dbContext.Tests.Where(t => t.IsActive), 
          ra => ra.TestId, 
          t => t.Id, 
          (ra, t) => new { ra, t })
    .Where(x => x.ra.LastReported < lastReported || 
               (x.ra.LastReported == lastReported && x.t.Id < lastTestId))
    .OrderByDescending(x => x.ra.LastReported)
    .ThenByDescending(x => x.t.Id)
    .Take(pageSize)
    .Select(x => new Test 
    {
        Id = x.t.Id,
        // 填充其他Test字段...
        FirstReported = x.ra.FirstReported,
        LastReported = x.ra.LastReported,
        NumberOfRecords = x.ra.NumberOfRecords
    });

这种方式可以避免OFFSET带来的全表扫描，大幅提升大分页场景的性能。

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