EF Core全文检索比原生SQL慢10倍的问题排查求助

摘要 #

• 测试数据：10000个项目，每个项目关联5条评论，每条评论包含100000个唯一字符
• Entity Framework Core（7.0.5）执行查询的速度比原生SQL慢10倍甚至更多
• 全文检索列类型为string
• 未启用延迟加载
• 已尝试多种全文检索列的实现方式
• 查询速度偶尔会突然快10倍
• 每次测试都等待全文索引创建完成（状态0）

问题场景 #

我使用EF Core 7.0.5执行一个半复杂的全文检索查询，相关代码如下：

BaseEntity模型 #

public class BaseEntity
{
    // 主数据
    [Key]
    public string Id { get; set; } = Guid.NewGuid().ToString();
    public string SearchKeywords { get; set; }

    // 审计数据
    [ForeignKey("TeamId")]
    public virtual Team Team { get; set; }
    public string TeamId { get; set; }

    public DateTime? CreatedDate { get; set; }
    [ForeignKey("CreatedByUserId")]
    public virtual ApplicationUser CreatedByUser { get; set; }
    public string CreatedByUserId { get; set; }

    //...
}

Project模型类 #

[Table("Projects")]
public class Project : BaseEntity
{
    // 主数据
    public string ProjectName { get; set; }
    public string ProjectDescription { get; set; }
    public string ProjectLocation { get; set; }
    public DateTime? ProjectStart { get; set; }
    public DateTime? ProjectEnd { get; set; }
    //...

    // 计算列（配置见ApplicationDbContext）
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public string MainProperty { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public string FullText { get; set; }

    // 关联数据
    [ForeignKey("ProjectStatusId")]
    public virtual DropDown ProjectStatus { get; set; }
    public string ProjectStatusId { get; set; }

    [ForeignKey("CustomerId")]
    public virtual Customer Customer { get; set; }
    public string CustomerId { get; set; }

    [ForeignKey("CustomerContactId")]
    public virtual CustomerContact CustomerContact { get; set; }
    public string CustomerContactId { get; set; }

    [ForeignKey("AccountOwnerId")]
    public virtual ApplicationUser AccountOwner { get; set; }
    public string AccountOwnerId { get; set; }

    // 关联集合
    public virtual IEnumerable<Comment> Comments { get; set; }
    public virtual IEnumerable<UploadedFile> UploadedFiles { get; set; }
    public virtual IEnumerable<ProjectCandidate> ProjectCandidates { get; set; }
}

OnModelCreating配置 #

protected override void OnModelCreating(ModelBuilder builder)
{
    // ...
    builder.Entity<Project>().Property(p => p.FullText).HasComputedColumnSql("CONCAT([SearchKeywords], ' ', [ProjectName], ' ', [ProjectDescription], ' ', [ProjectLocation])");
    // ...
}

查询方法GetProjects #

private void GetProjects(ApplicationDbContext context, string FullTextFilter)
{
    var projects = context.Projects.Where(x => x.TeamId == "85216daa-a05f-4c0a-92c4-0920f8349370").AsNoTracking();

    projects = projects.Where(x =>
         EF.Functions.Contains(x.FullText, FullTextFilter) ||
         EF.Functions.Contains(x.Customer.FullText, FullTextFilter) ||
         EF.Functions.Contains(x.CustomerContact.FullText, FullTextFilter) ||
         EF.Functions.Contains(x.AccountOwner.FullText, FullTextFilter) ||
         x.UploadedFiles.Any(c => EF.Functions.Contains(c.FullText, FullTextFilter)) ||
         x.Comments.Any(c => EF.Functions.Contains(c.FullText, FullTextFilter))
         );

    projects = projects.OrderBy(x => x.ProjectName);

    projects = projects.Skip(0);
    projects = projects.Take(20);

    var projectsDto = projects.Select(x => new ProjectOverviewDto
    {
         Id = x.Id,
         ProjectName = x.ProjectName,
         ProjectDescription = x.ProjectDescription,
         ProjectLocation = x.ProjectLocation,
         
         ProjectStatus = new DropDownMinimalDto { Name = x.ProjectStatus.Name },
         Customer = new CustomerMinimalDto { CompanyName = x.Customer.CompanyName },
         CustomerContact = new CustomerContactMinimalDto { LastName = x.CustomerContact.LastName },
         AccountOwner = new ApplicationUserMinimalDto { LastName = x.AccountOwner.LastName }
    });

    var projectsList = projectsDto.ToList();
}

测试调用 #

GetProjects(context, "\"*def*\"");

性能表现 #

查询执行时间波动极大：偶尔能达到约500ms的较快速度，但大多数情况下需要10000ms，部分测试甚至耗时高达20000ms。

生成的SQL（通过SQL Server Profiler获取） #

exec sp_executesql N'SELECT [t].[Id], [t].[ProjectName], [t].[ProjectDescription], [t].[ProjectLocation], [d].[Name], [t].[CompanyName], [t].[LastName], [t].[LastName0] AS [LastName]
FROM (
    SELECT [p].[Id], [p].[ProjectDescription], [p].[ProjectLocation], [p].[ProjectName], [p].[ProjectStatusId], [c].[CompanyName], [c0].[LastName], [a].[LastName] AS [LastName0]
    FROM [Projects] AS [p]
    LEFT JOIN [Customers] AS [c] ON [p].[CustomerId] = [c].[Id]
    LEFT JOIN [CustomerContacts] AS [c0] ON [p].[CustomerContactId] = [c0].[Id]
    LEFT JOIN [AspNetUsers] AS [a] ON [p].[AccountOwnerId] = [a].[Id]
    WHERE [p].[TeamId] = N''85216daa-a05f-4c0a-92c4-0920f8349370'' AND (CONTAINS([p].[FullText], @__FullTextFilter_1) OR CONTAINS([c].[FullText], @__FullTextFilter_1) OR CONTAINS([c0].[FullText], @__FullTextFilter_1) OR CONTAINS([a].[FullText], @__FullTextFilter_1) OR EXISTS (
        SELECT 1
        FROM [UploadedFiles] AS [u]
        WHERE [p].[Id] = [u].[ProjectId] AND CONTAINS([u].[FullText], @__FullTextFilter_1)) OR EXISTS (
        SELECT 1
        FROM [Comments] AS [c1]
        WHERE [p].[Id] = [c1].[ProjectId] AND CONTAINS([c1].[FullText], @__FullTextFilter_1)))
    ORDER BY [p].[ProjectName]
    OFFSET @__p_2 ROWS FETCH NEXT @__p_3 ROWS ONLY
) AS [t]
LEFT JOIN [DropDowns] AS [d] ON [t].[ProjectStatusId] = [d].[Id]
ORDER BY [t].[ProjectName]',N'@__FullTextFilter_1 nvarchar(4000),@__p_2 int,@__p_3 int',@__FullTextFilter_1=N'"*def*"',@__p_2=0,@__p_3=20

直接执行该SQL或用SqlDataReader执行，耗时约460ms，和EF Core的执行速度差距极大。

已尝试的优化方案 #

• 将FullText列设为带全文索引的计算列（如上代码）
• 将FullText列改为普通string列，手动复制计算列内容
• 在单个列（如SearchKeywords、ProjectName等）上创建全文索引
• 用EF.Functions.FreeText替代EF.Functions.Contains
• 通过配置services.AddDbContext<ApplicationDbContext>(options => options.UseLazyLoadingProxies(false).UseSqlServer("xxx"));禁用延迟加载（测试数据中无论是否启用均无代理）
• 等待EF预热完成后再测试

请问问题出在哪里？我理解EF会有一定性能开销，但这个差距实在太大了。

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

核心原因推测 #

1. 参数嗅探与执行计划问题：EF生成的参数化查询可能被SQL Server的参数嗅探机制分配了低效执行计划，而直接执行SQL时用的是具体值，刚好生成了最优计划；另外参数类型（如nvarchar(4000)）和全文索引列类型不匹配，会导致隐式转换，无法正常使用索引。
2. 计算列非持久化：当前配置的计算列未标记为持久化，每次查询都要重新计算CONCAT内容，大幅拖慢查询速度。
3. 多OR条件的执行计划缺陷：多个OR组合的过滤条件容易让SQL Server选择低效的执行路径，无法充分利用全文索引。

具体解决步骤 #

1. 将计算列设为持久化：修改OnModelCreating配置，确保计算列值被存储到磁盘，避免每次查询重新计算：

builder.Entity<Project>().Property(p => p.FullText)
    .HasComputedColumnSql("CONCAT([SearchKeywords], ' ', [ProjectName], ' ', [ProjectDescription], ' ', [ProjectLocation])")
    .IsStored();

更新数据库后重建全文索引。

2. 强制生成新执行计划：在查询末尾添加OPTION (RECOMPILE)，让SQL Server每次生成适配当前参数的最优计划，可通过EF拦截器或FromSqlRaw实现：

// 示例：用FromSqlRaw直接拼接带选项的SQL
var sql = @"SELECT ... /* 完整查询SQL */ OPTION (RECOMPILE)";
var projects = context.ProjectOverviewDto.FromSqlRaw(sql, parameters);

3. 拆分OR条件为Union查询：将多个OR条件拆分成独立查询后合并结果，避免SQL Server选择低效执行计划：

var teamId = "85216daa-a05f-4c0a-92c4-0920f8349370";
var query1 = context.Projects.Where(x => x.TeamId == teamId && EF.Functions.Contains(x.FullText, FullTextFilter));
var query2 = context.Projects.Where(x => x.TeamId == teamId && EF.Functions.Contains(x.Customer.FullText, FullTextFilter));
var query3 = context.Projects.Where(x => x.TeamId == teamId && EF.Functions.Contains(x.CustomerContact.FullText, FullTextFilter));
// 其他条件查询...

var combinedQuery = query1.Union(query2).Union(query3)
    .OrderBy(x => x.ProjectName)
    .Skip(0).Take(20);

4. 使用编译查询：将查询编译为委托，减少EF的查询解析与转换开销：

private static readonly Func<ApplicationDbContext, string, List<ProjectOverviewDto>> GetProjectsCompiled =
    EF.CompileQuery((ApplicationDbContext context, string fullTextFilter) =>
        context.Projects.Where(x => x.TeamId == "85216daa-a05f-4c0a-92c4-0920f8349370").AsNoTracking()
            .Where(x => 
                EF.Functions.Contains(x.FullText, fullTextFilter) ||
                EF.Functions.Contains(x.Customer.FullText, fullTextFilter) ||
                // 其他过滤条件
            )
            .OrderBy(x => x.ProjectName)
            .Take(20)
            .Select(x => new ProjectOverviewDto
            {
                // 投影字段
            })
            .ToList());

// 调用
var projectsList = GetProjectsCompiled(context, "\"*def*\"");

5. 更新统计信息与重建索引：定期更新数据库统计信息、重建全文索引，确保SQL Server能准确生成执行计划：

ALTER FULLTEXT INDEX ON Projects REBUILD;
UPDATE STATISTICS Projects;