Entity Framework Core查询2000条数据耗时超1分钟求优化方案

针对你的EF Core查询耗时超1分钟的问题，试试以下优化手段，可实现秒级查询：

1. 给ReceiverRuc字段添加数据库索引 #

查询过滤条件依赖ReceiverRuc，如果该字段无索引，数据库会执行全表扫描，这是慢查询的核心原因之一。

用Fluent API添加索引：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<IncomingEmail>()
        .HasIndex(ie => ie.ReceiverRuc)
        .HasDatabaseName("IX_IncomingEmails_ReceiverRuc");
}

生成迁移并更新数据库：

Add-Migration AddReceiverRucIndex
Update-Database

2. 拆分大字段（Attachment）的加载 #

Attachment作为二进制大字段，一次性加载2000条会占用大量内存与带宽，直接拖慢查询速度。推荐两种处理方式：

• 拆分表：将附件数据单独存到EmailAttachments表，与IncomingEmails建立一对一关联，主查询仅加载邮件基础信息，附件按需单独查询。
• 延迟加载：暂时不改表结构的话，先查询不带附件的邮件列表，需要时再单独获取附件：

  // 先查邮件基础信息，跳过Attachment
  var incomingEmails = await acompDbContext.IncomingEmails.AsNoTracking()
      .Where(ie => ie.ReceiverRuc == req.ReceiverRuc)
      .Select(ie => new IncomingEmail
      {
          IncomingEmailId = ie.IncomingEmailId,
          IdStatusMail = ie.IdStatusMail,
          MessageId = ie.MessageId,
          From = ie.From,
          To = ie.To,
          Subject = ie.Subject,
          Body = ie.Body,
          AttachmentName = ie.AttachmentName,
          // Attachment = ie.Attachment, // 注释掉大字段
          AttachmentType = ie.AttachmentType,
          AttachmentId = ie.AttachmentId,
          EmitterRuc = ie.EmitterRuc,
          EmitterBusinessName = ie.EmitterBusinessName,
          ReceiverRuc = ie.ReceiverRuc,
          Date = ie.Date,
          Valid = ie.Valid
      }).ToListAsync();
  
  // 按需加载指定邮件的附件
  var targetEmailAttachment = await acompDbContext.IncomingEmails.AsNoTracking()
      .Where(ie => ie.IncomingEmailId == targetEmailId)
      .Select(ie => ie.Attachment)
      .FirstOrDefaultAsync();
  

3. 投影到DTO而非实体类 #

直接实例化实体类IncomingEmail会带来额外的实体映射开销，定义仅包含所需属性的DTO类，让EF生成更精简的SQL：

// 定义DTO
public class IncomingEmailDto
{
    public int IncomingEmailId { get; set; }
    public int IdStatusMail { get; set; }
    public string MessageId { get; set; }
    public string From { get; set; }
    public string To { get; set; }
    public string Subject { get; set; }
    public string Body { get; set; }
    public string AttachmentName { get; set; }
    public byte[] Attachment { get; set; } // 若必须加载则保留
    public string AttachmentType { get; set; }
    public string AttachmentId { get; set; }
    public string EmitterRuc { get; set; }
    public string EmitterBusinessName { get; set; }
    public string ReceiverRuc { get; set; }
    public DateTime Date { get; set; }
    public bool Valid { get; set; }
}

// 查询时投影到DTO
var incomingEmails = await acompDbContext.IncomingEmails.AsNoTracking()
    .Where(ie => ie.ReceiverRuc == req.ReceiverRuc)
    .Select(ie => new IncomingEmailDto
    {
        IncomingEmailId = ie.IncomingEmailId,
        IdStatusMail = ie.IdStatusMail,
        MessageId = ie.MessageId,
        // 映射其他属性...
    }).ToListAsync();

4. 检查生成的SQL语句 #

开启EF Core的SQL日志，验证生成的SQL是否合理，确认索引是否生效：
在Program.cs/Startup.cs中配置日志：

builder.Logging.AddFilter("Microsoft.EntityFrameworkCore.Database.Command", LogLevel.Information);

查看控制台输出的SQL，确认是否使用了IX_IncomingEmails_ReceiverRuc索引，若未使用，排查字段类型不匹配、索引失效等问题。

5. 其他辅助优化 #

• 确认AsNoTracking()生效，避免EF上下文跟踪实体的额外开销。
• 若使用SQL Server，更新表统计信息确保查询计划最优：

  UPDATE STATISTICS IncomingEmails;
  

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