EF Core生成的多关联组合条件TSQL查询运行缓慢求助

云原生阿岚

2026-8-27

问题根因

这个性能问题本质是SQL Server查询优化器生成了错误的执行计划，不是某一个JOIN或者WHERE条件本身慢，你测试时删部分关联、改CHARINDEX为LIKE就恢复正常，本质是你修改了查询的语法形态，触发优化器重新生成了执行计划，刚好选到了匹配数据分布的最优路径。具体触发点有几个：

多表关联基数估算偏差：整个查询关联了11张表，还包含4个左连接的派生子查询，同时存在参数化变量，优化器对关联后的结果集行数估算出现严重偏差，错误选择了嵌套循环+键查找的执行策略——当实际需要关联的数据量远大于优化器预估的行数时，嵌套循环的时间复杂度会指数级上升，导致耗时暴涨。
非SARG条件放大估算误差：CHARINDEX(N'we', [dil].[_Title]) > 0属于非SARG操作，完全无法利用_Title字段上的索引，优化器对这类条件的选择度估算默认按固定低比例（通常按10%匹配率计算），如果实际匹配行数远高于这个比例，结合多表JOIN的结果放大效应，会直接让执行计划的选择完全失准。你替换成LIKE后速度恢复，不是LIKE本身性能比CHARINDEX好，是这个语法改动触发了执行计划重编译。
派生表写法阻碍谓词下推：你把几个多语言表的过滤逻辑写在子查询内部，在多表关联的复杂场景下，优化器可能无法将外层的过滤条件下推到子查询提前执行，导致子查询先扫描全表数据再做关联，无谓放大了中间结果集的数据量。
无意义排序阻碍分页优化：ORDER BY (SELECT 1)是无效排序逻辑，OFFSET/FETCH分页时SQL Server必须先完成所有符合条件数据的全量关联、排序，才能取出前25行数据，完全无法利用索引做提前终止优化。

解决方案

按优先级从高到低操作即可：

先验证执行计划问题：在你捕获的慢查询末尾加上OPTION (RECOMPILE, HASH JOIN)后执行，如果耗时直接降到毫秒级，就能100%确认是基数估算错误导致的执行计划异常，和硬件、数据量本身无关。
重写子查询为普通JOIN，帮助优化器做谓词下推：把所有包裹多语言表的派生表全部改成直接JOIN，将语言过滤条件直接写在JOIN关联条件上，不要套一层子查询。比如原来的WorkbenchLanguage关联：
```
LEFT JOIN (
    SELECT [x].*
    FROM [QMS].[WorkbenchLanguage] AS [x]
    WHERE [x].[LanguageRef] = @__languageRef_0
) AS [t] ON [docw].[WorkbenchRef] = [t].[WorkbenchRef]
```
直接改写为：
```
LEFT JOIN [QMS].[WorkbenchLanguage] AS [t] 
  ON [docw].[WorkbenchRef] = [t].[WorkbenchRef] AND [t].[LanguageRef] = @__languageRef_0
```
其余t1（PersonLanguage）、t2（ItemRowLanguage）的子查询全部按这个逻辑改写。
替换EF Core的模糊匹配逻辑：把生成CHARINDEX的匹配写法，替换成EF.Functions.Like(p => p.Title, "%we%")，生成的LIKE N'%we%'和CHARINDEX语义完全一致，既符合业务需求，也能避开当前触发异常执行计划的语法形态。

补全覆盖索引消除回表：给核心关联、过滤字段建立覆盖索引，让查询不需要回表取数据，核心索引参考如下：

-- 文档主表覆盖索引
CREATE NONCLUSTERED INDEX IX_DocumentInfo_IsVisible_Id ON [QMS].[DocumentInfo](IsVisible, Id)
INCLUDE (Code, DocumentTypeRef, IsActive, IsGlobal, IsPrintable, RecordPrefix, Owner_PersonRef);
-- 文档多语言表覆盖索引
CREATE NONCLUSTERED INDEX IX_DocumentInfoLanguage_Lang_Ref ON [QMS].[DocumentInfoLanguage](LanguageRef, DocumentInfoRef)
INCLUDE (_Title, _Description);
-- 生效版本子查询覆盖索引
CREATE NONCLUSTERED INDEX IX_DocumentVersion_Valid ON [QMS].[DocumentVersion](DocumentInfoRef, EffectiveDate, ExpireDate)
INCLUDE (Id, Creator_PersonRef, IsActive, PublishDate, VersionNo, ReviewDate, ItemRowRef_DocVersionState);

其余多语言表（CompanyLanguage、DocumentTypeLanguage、PersonLanguage等）也可以按(语言字段, 关联外键)的顺序建覆盖索引，包含需要查询的标题、名称字段即可。

修正排序逻辑：删掉无意义的ORDER BY (SELECT 1)，换成明确的排序字段（比如ORDER BY di.Id），最好让排序字段和索引的键顺序一致，让分页操作不需要做全量数据排序。
兜底解决参数嗅探问题：如果以上操作后还是偶发慢查询，可以在EF Core查询末尾加OPTION (OPTIMIZE FOR UNKNOWN)提示，让优化器根据字段的数据分布平均值生成执行计划，避免复用之前缓存的错误执行计划。

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