Entity Framework Core多关联表查询性能优化咨询

本人是Entity Framework新手，数据库中ProductIdentifiers为关联多张其他表的中心表。当前查询输入为非键字段Name，对应的EF Core查询代码如下：

public ProductIdentifier? GetFullCedMed(string v)
       => db.ProductIdentifiers.Where(a => a.Name == v)
            .Include(ced => ced.Project)
            .Include(ced => ced.LimitValues).ThenInclude(l => l.Parameter)
            .Include(ced => ced.LimitValues).ThenInclude(l => l.Bin)
            .Include(ced => ced.LimitValues).ThenInclude(l => l.Stage)
            .Include(ced => ced.LimitValues).ThenInclude(l => l.TestTypeNavigation)
            .Include(ced => ced.ConfigValues).ThenInclude(c => c.Parameter)
            .Include(ced => ced.ConfigValues).ThenInclude(c => c.Bin)
            .Include(ced => ced.ConfigValues).ThenInclude(c => c.Stage)
            .Include(ced => ced.ConfigValues).ThenInclude(c => c.TestTypeNavigation)
            .Include(ced => ced.FatherCedmed)
            .ToList().FirstOrDefault();

生成的SQL查询如下：

exec sp_executesql N'SELECT [p].[id], [p].[FatherCedmedId], [p].[Name], [p].[ProjectId], [m].[id], [m].[IsActive], [m].[Name], [m].[ProductLineName], [p0].[id], [t].[id], [t].[BinId], [t].[CED_MED], [t].[LSL], [t].[ParameterID], [t].[StageID], [t].[TestType], [t].[USL], [t].[id0], [t].[Enabled], [t].[FORMAT], [t].[IsLimit], [t].[ParamID], [t].[Parameter_Name], [t].[Print], [t].[Unit], [t].[id1], [t].[BinDescription], [t].[BinDescriptionOverride], [t].[BinNumber], [t].[BinNumberOverride], [t].[GroupID], [t].[ParamID0], [t].[StageID0], [t].[id2], [t].[Name], [t].[StageNumber], [t].[id3], [t].[LevelId], [t].[Name0], [t].[OrderingId], [t0].[id], [t0].[BinId], [t0].[CED_MED], [t0].[ParameterID], [t0].[StageID], [t0].[TestType], [t0].[Value], [t0].[id0], [t0].[Enabled], [t0].[FORMAT], [t0].[IsLimit], [t0].[ParamID], [t0].[Parameter_Name], [t0].[Print], [t0].[Unit], [t0].[id1], [t0].[BinDescription], [t0].[BinDescriptionOverride], [t0].[BinNumber], [t0].[BinNumberOverride], [t0].[GroupID], [t0].[ParamID0], [t0].[StageID0], [t0].[id2], [t0].[Name], [t0].[StageNumber], [t0].[id3], [t0].[LevelId], [t0].[Name0], [t0].[OrderingId], [p0].[FatherCedmedId], [p0].[Name], [p0].[ProjectId]
FROM [ProductIdentifiers] AS [p]
LEFT JOIN [main_Projects] AS [m] ON [p].[ProjectId] = [m].[id]
LEFT JOIN [ProductIdentifiers] AS [p0] ON [p].[FatherCedmedId] = [p0].[id]
LEFT JOIN (
    SELECT [l].[id], [l].[BinId], [l].[CED_MED], [l].[LSL], [l].[ParameterID], [l].[StageID], [l].[TestType], [l].[USL], [p1].[id] AS [id0], [p1].[Enabled], [p1].[FORMAT], [p1].[IsLimit], [p1].[ParamID], [p1].[Parameter_Name], [p1].[Print], [p1].[Unit], [b].[id] AS [id1], [b].[BinDescription], [b].[BinDescriptionOverride], [b].[BinNumber], [b].[BinNumberOverride], [b].[GroupID], [b].[ParamID] AS [ParamID0], [b].[StageID] AS [StageID0], [s].[id] AS [id2], [s].[Name], [s].[StageNumber], [m0].[id] AS [id3], [m0].[LevelId], [m0].[Name] AS [Name0], [m0].[OrderingId]
    FROM [LimitValues] AS [l]
    INNER JOIN [Parameters] AS [p1] ON [l].[ParameterID] = [p1].[id]
    INNER JOIN [Bins] AS [b] ON [l].[BinId] = [b].[id]
    LEFT JOIN [Stages] AS [s] ON [l].[StageID] = [s].[id]
    LEFT JOIN [main_TestTypes] AS [m0] ON [l].[TestType] = [m0].[id]
) AS [t] ON [p].[id] = [t].[CED_MED]
LEFT JOIN (
    SELECT [c].[id], [c].[BinId], [c].[CED_MED], [c].[ParameterID], [c].[StageID], [c].[TestType], [c].[Value], [p2].[id] AS [id0], [p2].[Enabled], [p2].[FORMAT], [p2].[IsLimit], [p2].[ParamID], [p2].[Parameter_Name], [p2].[Print], [p2].[Unit], [b0].[id] AS [id1], [b0].[BinDescription], [b0].[BinDescriptionOverride], [b0].[BinNumber], [b0].[BinNumberOverride], [b0].[GroupID], [b0].[ParamID] AS [ParamID0], [b0].[StageID] AS [StageID0], [s0].[id] AS [id2], [s0].[Name], [s0].[StageNumber], [m1].[id] AS [id3], [m1].[LevelId], [m1].[Name] AS [Name0], [m1].[OrderingId]
    FROM [ConfigValues] AS [c]
    INNER JOIN [Parameters] AS [p2] ON [c].[ParameterID] = [p2].[id]
    INNER JOIN [Bins] AS [b0] ON [c].[BinId] = [b0].[id]
    LEFT JOIN [Stages] AS [s0] ON [c].[StageID] = [s0].[id]
    LEFT JOIN [main_TestTypes] AS [m1] ON [c].[TestType] = [m1].[id]
) AS [t0] ON [p].[id] = [t0].[CED_MED]
WHERE [p].[Name] = @__v_0
ORDER BY [p].[id], [m].[id], [p0].[id], [t].[id], [t].[id0], [t].[id1], [t].[id2], [t].[id3], [t0].[id], [t0].[id0], [t0].[id1], [t0].[id2]',N'@__v_0 varchar(255)',@__v_0='NAME_OF_RECORD_FROM_ProductIdentifier_TABLE'

因表中数据量大，当前查询耗时较长，现咨询：该查询如何优化？通过ID检索是否能提升速度？同时需关联所有表，寻求其他优化建议。

1. 改用ID检索的性能提升 #

肯定会有明显提升。主键ID通常是聚集索引，数据库查找主键记录的速度远快于非键字段Name（除非Name有合适的索引）。主键查找是O(1)级别，而无索引的Name查找是全表扫描O(n)，数据量越大差距越明显。如果业务允许优先用ID作为查询条件，直接替换Where(a => a.Name == v)为Where(a => a.Id == idValue)即可，能大幅降低查询的初始过滤时间。

2. 给Name字段添加索引 #

如果必须通过Name查询，给ProductIdentifiers.Name字段创建非聚集索引，避免全表扫描：

CREATE NONCLUSTERED INDEX IX_ProductIdentifiers_Name ON ProductIdentifiers(Name);

如果Name字段存在重复值，考虑创建包含索引，把查询中需要的其他字段（比如Id、ProjectId、FatherCedmedId）包含进去，避免索引回表：

CREATE NONCLUSTERED INDEX IX_ProductIdentifiers_Name_Include ON ProductIdentifiers(Name)
INCLUDE (Id, ProjectId, FatherCedmedId);

3. 优化EF Core查询代码 #

• 去掉ToList().FirstOrDefault()：ToList()会把所有符合条件的记录加载到内存再取第一条，换成FirstOrDefaultAsync()（建议异步）或FirstOrDefault()，EF会直接生成TOP 1的SQL，减少数据传输量：

  public async Task<ProductIdentifier?> GetFullCedMedAsync(string v)
  => await db.ProductIdentifiers.Where(a => a.Name == v)
       .Include(ced => ced.Project)
       .Include(ced => ced.LimitValues)
           .ThenInclude(l => l.Parameter)
           .ThenInclude(l => l.Bin)
           .ThenInclude(l => l.Stage)
           .ThenInclude(l => l.TestTypeNavigation)
       .Include(ced => ced.ConfigValues)
           .ThenInclude(c => c.Parameter)
           .ThenInclude(c => c.Bin)
           .ThenInclude(c => c.Stage)
           .ThenInclude(c => c.TestTypeNavigation)
       .Include(ced => ced.FatherCedmed)
       .FirstOrDefaultAsync();
  

  注意：EF Core支持链式ThenInclude，不需要重复写Include(ced => ced.LimitValues)，写法更简洁。

• 按需加载而非全量Include：检查是否真的需要关联所有表的所有字段，比如如果某些关联表只需要部分字段，可以用Select投影到DTO，减少返回的数据量：

  // 示例：投影到自定义DTO，只取需要的字段
  public async Task<ProductIdentifierDto?> GetFullCedMedDtoAsync(string v)
  => await db.ProductIdentifiers.Where(a => a.Name == v)
       .Select(a => new ProductIdentifierDto
       {
           Id = a.Id,
           Name = a.Name,
           ProjectName = a.Project.Name,
           LimitValues = a.LimitValues.Select(l => new LimitValueDto
           {
               LSL = l.LSL,
               USL = l.USL,
               ParameterName = l.Parameter.Parameter_Name
               // 只保留需要的字段
           }).ToList(),
           // 其他关联字段同理
       })
       .FirstOrDefaultAsync();
  

4. EF Core特性优化 #

• 启用跟踪延迟加载（如果适合）：如果不是所有场景都需要加载所有关联表，可以开启延迟加载，只在实际访问关联属性时才查询对应数据。需要给导航属性加virtual关键字，并配置延迟加载：

  // 上下文配置中启用延迟加载
  builder.UseLazyLoadingProxies();
  

  但注意：延迟加载可能导致N+1查询问题，需根据业务场景评估。

• 使用显式加载：如果已经查询了主实体，后续需要关联数据时，再单独加载，避免一次性加载大量数据：

  var product = await db.ProductIdentifiers.FirstOrDefaultAsync(a => a.Name == v);
  if(product != null)
  {
      await db.Entry(product).Reference(p => p.Project).LoadAsync();
      await db.Entry(product).Collection(p => p.LimitValues).Query()
          .Include(l => l.Parameter)
          .Include(l => l.Bin)
          .LoadAsync();
      // 其他关联表同理
  }
  

5. 数据库层面优化 #

• 检查关联表的外键索引：LimitValues、ConfigValues的外键字段（CED_MED、ParameterID、BinId等）需要确保有索引，否则关联查询时会做全表扫描。比如给LimitValues.CED_MED创建索引：

  CREATE NONCLUSTERED INDEX IX_LimitValues_CED_MED ON LimitValues(CED_MED);
  

• 避免不必要的JOIN：看生成的SQL，LimitValues和Parameters、Bins用的是INNER JOIN，如果这些关联记录是必填的没问题；如果是可选的，换成LEFT JOIN（但根据代码里的Include，EF会自动判断，这里注意业务逻辑是否允许）。
• 查询计划分析：用SQL Server Management Studio的“包含实际执行计划”功能查看查询的瓶颈，比如是否有表扫描、键查找等，针对性优化索引。

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