Entity Framework查询性能问题：已加过滤与分页仍响应缓慢

问题描述 #

通过Entity Framework从数据库查询Sw01Module关联的客户端历史数据时，API响应耗时过长。已应用名称、起止日期过滤及分页逻辑，但请求仍需要大量时间返回结果。

原代码 #

[HttpGet("{id}/client-history-sw01-module")]
[Authorize(AuthenticationSchemes = "AdminSchema")]
public async Task<ActionResult<object>> GetClientHistoriesSw01Module(
    long id,
    [FromServices] IMapper mapper,
    string? name,
    DateTime? startDate,
    DateTime? endDate,
    int page = 1,
    int pageSize = 10)
{
    var sw01Module = await _context.Sw01Modules
        .Include(d => d.ClientHistoriesSw01Module)
        .FirstOrDefaultAsync(d => d.Id == id);

    if (sw01Module == null)
    {
        return StatusCode(404, new { message = "Equipamento não encontrado" });
    }

    if (sw01Module.ClientHistoriesSw01Module == null || !sw01Module.ClientHistoriesSw01Module.Any())
    {
        return StatusCode(404, new { message = "Sem histórico" });
    }

    var clientHistoriesSw01Module = sw01Module.ClientHistoriesSw01Module.AsQueryable();

    if (!string.IsNullOrEmpty(name))
    {
        clientHistoriesSw01Module = clientHistoriesSw01Module
            .Where(um => um.ClientName != null && um.ClientName.Contains(name, StringComparison.OrdinalIgnoreCase));
    }

    if (startDate.HasValue)
    {
        clientHistoriesSw01Module = clientHistoriesSw01Module
            .Where(um => um.CreatedAt >= startDate.Value);
    }
    
    if (endDate.HasValue)
    {
        clientHistoriesSw01Module = clientHistoriesSw01Module
            .Where(um => um.CreatedAt <= endDate.Value);
    }

    clientHistoriesSw01Module = clientHistoriesSw01Module
        .OrderByDescending(um => um.CreatedAt);

    var totalItems = clientHistoriesSw01Module.Count();

    var totalPages = (int)Math.Ceiling((double)totalItems / pageSize);

    var paginatedClientHistoriesSw01Module = clientHistoriesSw01Module
        .Skip((page - 1) * pageSize)
        .Take(pageSize);

    var clientHistoriesSw01ModuleGetDTO = mapper.Map<IEnumerable<ClientHistorySw01ModuleGetDTO>>(paginatedClientHistoriesSw01Module);

    var result = new
    {
        Page = page,
        PageSize = pageSize,
        TotalPages = totalPages,
        TotalItems = totalItems,
        Items = clientHistoriesSw01ModuleGetDTO.ToList()
    };

    return result;
}

核心问题分析 #

原代码的主要性能瓶颈在于提前加载了所有关联的ClientHistoriesSw01Module数据到内存，之后才在内存中执行过滤、排序和分页操作。当关联数据量较大时，这会导致大量不必要的数据传输和内存开销，同时无法利用数据库的索引优化能力。

优化方案 #

1. 直接从关联表查询，避免全量加载 #

跳过加载Sw01Module实体的步骤，直接查询ClientHistoriesSw01Module表，通过Sw01ModuleId关联过滤，让数据库层面完成所有计算。

2. 异步化所有数据库操作 #

将同步的Count()替换为异步的CountAsync()，保持整个请求流程的异步非阻塞，提升并发性能。

3. 复用查询逻辑，减少重复计算 #

构建基础查询后，复用该查询进行Count和分页操作，避免EF生成重复的SQL语句。

4. 添加数据库索引 #

在ClientHistoriesSw01Module表上创建以下复合索引，加速过滤和排序：

• IX_ClientHistoriesSw01Module_Sw01ModuleId_CreatedAt（Sw01ModuleId + CreatedAt）
• IX_ClientHistoriesSw01Module_ClientName（ClientName，支持模糊查询）

优化后的代码 #

[HttpGet("{id}/client-history-sw01-module")]
[Authorize(AuthenticationSchemes = "AdminSchema")]
public async Task<ActionResult<object>> GetClientHistoriesSw01Module(
    long id,
    [FromServices] IMapper mapper,
    string? name,
    DateTime? startDate,
    DateTime? endDate,
    int page = 1,
    int pageSize = 10)
{
    // 直接查询关联表，过滤Sw01ModuleId
    var query = _context.ClientHistoriesSw01Module
        .Where(ch => ch.Sw01ModuleId == id);

    // 应用过滤条件
    if (!string.IsNullOrEmpty(name))
    {
        query = query.Where(um => um.ClientName != null && um.ClientName.Contains(name, StringComparison.OrdinalIgnoreCase));
    }

    if (startDate.HasValue)
    {
        query = query.Where(um => um.CreatedAt >= startDate.Value);
    }
    
    if (endDate.HasValue)
    {
        query = query.Where(um => um.CreatedAt <= endDate.Value);
    }

    // 排序
    query = query.OrderByDescending(um => um.CreatedAt);

    // 异步获取总数
    var totalItems = await query.CountAsync();

    if (totalItems == 0)
    {
        return StatusCode(404, new { message = "Sem histórico" });
    }

    var totalPages = (int)Math.Ceiling((double)totalItems / pageSize);

    // 异步分页查询
    var paginatedData = await query
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

    // 映射DTO
    var dtoList = mapper.Map<IEnumerable<ClientHistorySw01ModuleGetDTO>>(paginatedData);

    var result = new
    {
        Page = page,
        PageSize = pageSize,
        TotalPages = totalPages,
        TotalItems = totalItems,
        Items = dtoList.ToList()
    };

    return result;
}

额外说明 #

• 如果需要验证Sw01Module是否存在，可以单独执行一个轻量的AnyAsync查询，避免加载不必要的数据：

  var moduleExists = await _context.Sw01Modules.AnyAsync(d => d.Id == id);
  if (!moduleExists)
  {
      return StatusCode(404, new { message = "Equipamento não encontrado" });
  }
  

  这一步可以根据业务需求选择是否添加，若ClientHistoriesSw01Module表的Sw01ModuleId是外键且有约束，那么当没有历史数据时返回"Sem histórico"即可，无需额外验证模块存在性。

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