HR用户统计查询性能咨询：10条数据耗时3秒是否正常？如何优化？

问题背景 #

我编写了一段EF Core查询语句，用于统计HR用户的相关数据，当前查询10位HR用户的数据耗时3秒，想咨询这个性能是否正常，以及如何优化。

数据库规模：

• 用户表：12.5万条记录（其中仅55位是HR用户）
• 职位表：1900条记录
• 申请表：34万条记录

需要统计的内容包括：HR用户姓名、发布职位总数、已完成职位总数，以及申请表中15种不同状态的申请数量。

当前使用的查询代码：

var statisticsQuery = _context.Users
    .OrderBy(hrUser => hrUser.Id)
    .Where(hrUser => hrUser.Roles.Any(p => p.RoleId == "Hr"))
    .Select(hrUser => new VacancyByRecruiterModelDto
    {
        RecruiterId = hrUser.Id,
        RecruiterFullName = hrUser.Name + " " + hrUser.Surname,
        PublishedTotalVacancyCount = hrUser.Vacancies.Count(vac => vac.StatusId == 1),
        CompletedTotalVacancyCount = hrUser.Vacancies.Count(vac => vac.StatusId == 2),
        CvCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 1),
        ExamCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 2),
        InterviewCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 3),
        DocumentCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 4),
        HiringSuccessCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 5),
        CvFailedCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 6),
        CandidateFailedCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 7),
        DontSeeCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 8),
        ReserveCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 9),
        InternCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 10),
        SecurityCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 11),
        DontExamCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 12),
        DontInterviewCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 13),
        HiringStoppedCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 14),
        FailInternshipCount = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .Count(app => app.StatusId == 15),
    }).AsNoTracking();

if (loadMore?.Skip != null && loadMore?.Take != null)
{
    statisticsQuery = statisticsQuery.Skip(loadMore.Skip.Value).Take(loadMore.Take.Value);
}

性能判断 #

查询10位HR用户耗时3秒明显不正常，毕竟HR用户仅55位，职位表和申请表的规模也不算极端，合理的查询耗时应该在几百毫秒以内。

优化方案 #

1. 重构查询逻辑，避免重复遍历申请表

当前代码中每个申请状态都单独调用SelectMany+Count，会生成15个独立的子查询，导致数据库多次扫描申请表。可以改为一次性统计所有状态的数量，再映射到对应字段：

var statisticsQuery = _context.Users
    .Where(hrUser => hrUser.Roles.Any(p => p.RoleId == "Hr"))
    .OrderBy(hrUser => hrUser.Id)
    .Select(hrUser => new 
    {
        hrUser.Id,
        hrUser.Name,
        hrUser.Surname,
        Vacancies = hrUser.Vacancies,
        ApplicationStats = hrUser.Vacancies
            .SelectMany(vac => vac.Applications)
            .GroupBy(app => app.StatusId)
            .ToDictionary(g => g.Key, g => g.Count())
    })
    .Select(x => new VacancyByRecruiterModelDto
    {
        RecruiterId = x.Id,
        RecruiterFullName = x.Name + " " + x.Surname,
        PublishedTotalVacancyCount = x.Vacancies.Count(vac => vac.StatusId == 1),
        CompletedTotalVacancyCount = x.Vacancies.Count(vac => vac.StatusId == 2),
        CvCount = x.ApplicationStats.TryGetValue(1, out var count) ? count : 0,
        ExamCount = x.ApplicationStats.TryGetValue(2, out count) ? count : 0,
        InterviewCount = x.ApplicationStats.TryGetValue(3, out count) ? count : 0,
        DocumentCount = x.ApplicationStats.TryGetValue(4, out count) ? count : 0,
        HiringSuccessCount = x.ApplicationStats.TryGetValue(5, out count) ? count : 0,
        CvFailedCount = x.ApplicationStats.TryGetValue(6, out count) ? count : 0,
        CandidateFailedCount = x.ApplicationStats.TryGetValue(7, out count) ? count : 0,
        DontSeeCount = x.ApplicationStats.TryGetValue(8, out count) ? count : 0,
        ReserveCount = x.ApplicationStats.TryGetValue(9, out count) ? count : 0,
        InternCount = x.ApplicationStats.TryGetValue(10, out count) ? count : 0,
        SecurityCount = x.ApplicationStats.TryGetValue(11, out count) ? count : 0,
        DontExamCount = x.ApplicationStats.TryGetValue(12, out count) ? count : 0,
        DontInterviewCount = x.ApplicationStats.TryGetValue(13, out count) ? count : 0,
        HiringStoppedCount = x.ApplicationStats.TryGetValue(14, out count) ? count : 0,
        FailInternshipCount = x.ApplicationStats.TryGetValue(15, out count) ? count : 0,
    }).AsNoTracking();

if (loadMore?.Skip != null && loadMore?.Take != null)
{
    statisticsQuery = statisticsQuery.Skip(loadMore.Skip.Value).Take(loadMore.Take.Value);
}

2. 调整过滤与排序的顺序

原代码先对全表用户排序，再过滤HR用户，会导致数据库先排序12.5万条记录，再筛选出55位HR，效率极低。改为先过滤HR用户，再排序：

// 原顺序
.OrderBy(hrUser => hrUser.Id)
.Where(hrUser => hrUser.Roles.Any(p => p.RoleId == "Hr"))

// 优化后顺序
.Where(hrUser => hrUser.Roles.Any(p => p.RoleId == "Hr"))
.OrderBy(hrUser => hrUser.Id)

3. 添加必要的数据库索引

给以下字段创建组合索引，加速关联和过滤：

• 用户角色关联表：(RoleId, UserId)（快速筛选HR用户）
• 职位表：(UserId, StatusId)（快速统计每个HR的职位数量）
• 申请表：(VacancyId, StatusId)（快速统计每个职位各状态的申请数量）

4. 禁用客户端评估

开启EF Core的客户端评估警告（在DbContext配置中设置ConfigureWarnings(w => w.Throw(RelationalEventId.QueryClientEvaluationWarning))），确保所有查询逻辑都在数据库端执行，避免将大量数据加载到内存后再计算。

5. 考虑使用AsSplitQuery（EF Core 5+）

如果查询生成了复杂的笛卡尔积导致数据膨胀，可以在查询末尾添加.AsSplitQuery()，将关联查询拆分为多个独立的SQL查询，减少数据传输量：

var statisticsQuery = _context.Users
    // ... 其他逻辑 ...
    .AsNoTracking()
    .AsSplitQuery();

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