EF Core参数化OFFSET FETCH NEXT查询性能慢10倍问题求助

看起来这个4.6万行的SQL Azure表上，简单的分页查询跑5秒确实有点拉胯，咱们从几个核心方向入手优化：

• 创建精准的覆盖索引：这是最可能解决问题的关键。你的查询按Forename+Id排序，然后返回特定列，当前如果没有对应的索引，数据库会先全表扫描再做排序，这在4万多行的数据上开销极大。直接创建包含排序键和查询列的覆盖索引：

  CREATE NONCLUSTERED INDEX IX_Associates_Forename_Id 
  ON [Associates] ([Forename], [Id]) 
  INCLUDE ([Surname], [AzureId], [Email]);
  

  这个索引能让数据库直接按排序顺序读取需要的列，完全避免全表扫描和额外排序操作，性能会有质的提升。

• 检查执行计划找瓶颈：在SSMS里按Ctrl+M打开「包括实际执行计划」，跑一遍你的查询，重点看有没有Sort运算符或者表/聚集索引扫描。如果看到Sort，说明索引没生效，数据库在做全表排序；如果是扫描，说明缺少合适的索引。另外SQL Azure的「查询存储」也能帮你定位这个查询的资源消耗情况，看看是不是有异常的CPU或IO占用。

• 排查参数嗅探问题：EF Core生成的参数化查询偶尔会遇到参数嗅探，尤其是当不同参数对应的结果集差异较大时。你可以试试在查询末尾加上OPTION (RECOMPILE)测试下：

  DECLARE @__TypedProperty_0 as int = 20 
  DECLARE @__TypedProperty_1 as int = 20 
  SELECT [dto].[Id], [dto].[Forename], [dto].[Surname], [dto].[AzureId], [dto].[Email] 
  FROM [Associates] AS [dto] 
  ORDER BY [dto].[Forename], [dto].[Id] 
  OFFSET @__TypedProperty_0 ROWS FETCH NEXT @__TypedProperty_1 ROWS ONLY
  OPTION (RECOMPILE);
  

  如果加了之后速度变快，说明是参数嗅探导致的执行计划不佳。你可以在EF Core里通过UseQueryHint("RECOMPILE")给这个查询加上编译提示，或者调整数据库的参数嗅探相关配置。

• 确认SQL Azure资源是否足够：有时候慢不是查询的问题，而是数据库的资源不够用。去Azure门户看看你的数据库的DTU/vCore使用率、CPU、内存指标，如果经常接近上限，那可能需要升级服务层级，或者调整资源分配，毕竟SQL Azure的资源是按需分配的，资源不够会直接影响查询性能。

• 提前优化大分页场景：虽然你现在用的是OFFSET 20，但如果后续业务需要深分页（比如OFFSET几万行），OFFSET/FETCH的性能会急剧下降。这种情况下可以改用键集分页，比如基于上一页最后一条数据的Forename和Id来做过滤：

  SELECT [dto].[Id], [dto].[Forename], [dto].[Surname], [dto].[AzureId], [dto].[Email] 
  FROM [Associates] AS [dto] 
  WHERE ([dto].[Forename] > @lastPageForename) 
     OR ([dto].[Forename] = @lastPageForename AND [dto].[Id] > @lastPageId)
  ORDER BY [dto].[Forename], [dto].[Id] 
  FETCH NEXT 20 ROWS ONLY;
  

  这种方式能稳定利用索引，即使分页很深也能保持良好性能。

内容的提问来源于stack exchange，提问作者Henry Ing-Simmons