使用EF Core优化SQL Server查询性能：执行时间不一致

Question

我在我的.NET 7项目中使用最新版本的SQL Server和EF Core。

我有一个名为“Deposits”的表，其中有400万行。我遇到了一个小问题，想知道如何解决它。

我有一个非常简单的查询：

var deposits = await dataContext.Deposits
                                .Where(d => d.OwnerId == userId
                                            && d.DepositReason == DepositReason.Purchase)
                                .Select(p => p.Id)
                                .OrderByDescending(id => id)
                                .ToListAsync();

我注意到执行时间非常长（略超过10秒）。

经过一番思考，我交换了查询中的条件：

之前：

d.OwnerId == userId
&& d.DepositReason == DepositReason.Purchase

之后：

d.DepositReason == DepositReason.Purchase
&& d.OwnerId == userId

查询开始执行大约8毫秒（附上截图）。

好的，我已经构建并部署到生产环境了，一切都很顺利，查询执行速度也很快。然而，几个小时后，似乎SQL Server改变了执行计划或类似的东西，查询再次执行需要大约11秒的时间。

此外，通过SSMS执行没有任何区别。

select * 
from deposits
where DepositReason = 2 
  and OwnerId = 1

或者

select * 
from deposits
where OwnerId = 1 
  and DepositReason = 2

速度总是很快。

我真的很想知道如何处理这样的异常。如果有人了解这是如何工作的以及可以做些什么，请分享你的知识。谢谢！

最慢的查询：

SELECT [d].[Id]  FROM [Deposits] AS [d]  WHERE [d].[OwnerId] = @__userId_0 AND [d].[DepositReason] = CAST(2 AS tinyint)  ORDER BY [d].[Id] DESC

- Nikita Khromov

---

这可能是一个参数嗅探问题，其中一些OwnerId在deposits表中有比其他人更多的行。如果计划编译用于行数较少的OwnerId，则会编译一个带有非覆盖索引查找和查找操作的计划；如果编译用于不同的OwnerId，则可能会进行扫描操作，以及在这里出现了ORDER BY语句可能需要排序。您需要获取“慢速”和“快速”情况下的执行计划。这是您可以从查询存储(Query Store)中获取的信息。 - Martin Smith

SSMS查询中的常量（字面）值是无关紧要的，因为EF Core会生成参数化查询（正如它应该做的那样），而SqlServer已知存在所谓的“参数嗅探”问题。正如其他人提到的，where子句中谓词的顺序并不重要。此外，看起来您没有索引可以同时覆盖两个谓词，所以始终使用外键的索引，而不考虑其他谓词值的基数。也许您应该查看并添加一些影响计划的提示。无论如何，这纯粹是SqlServer的问题，与EF Core无关。 - Ivan Stoev

@IvanStoev 是的，我确定这是一个 SQL Server 问题，谢谢，我会检查提示并处理它们。 - Nikita Khromov

不过，EF Core 的问题在于如何让它添加查询提示 :) 在这方面，我们可能可以提供帮助。至少可以添加 option (recompile)，通常可以解决此类问题。 - Ivan Stoev

正如其他人所提到的，WHERE子句中谓词的顺序并不重要。嗯，并不完全是这样。在其他所有条件相等的情况下，它根本不应该有任何影响。除了一些例外，比如：即使在T-SQL语句中改变一个字节（甚至是空格），也意味着它的哈希值不同，这意味着它在执行计划缓存中有一个单独的计划，这意味着SQL Server有机会根据当前统计数据和基数估计生成一个完全不同的计划。 - AlwaysLearning

Answer 1

您的问题主要是由于执行计划不佳引起的。服务器在决定是使用索引进行基于OwnerId的查找，然后进行进一步的键查找到聚集索引，还是只扫描整个聚集索引之间存在困难。

参数嗅探可能加剧了这个问题，因为这意味着无论哪个值首先被查询，都会被用于编译查询。有时候您会得到重新编译，通常是由于查询最终退出计划缓存。

RECOMPILE可以通过每次重新编译来解决这个问题，但它其实只是一个补丁，并没有真正解决根本原因。而且每次运行查询都会带来显著的CPU成本。

真正的答案是修复您的索引。为了使您的查询性能良好，您需要更改IX_Deposits_OwnerId索引：

(OwnerId, DepositReason, ID DESC) INCLUDE (all_other_columns_here)

很不幸，因为您正在返回每一列，所以您需要在索引中包括所有这些列。如果您可以限制要返回的列，那么您只需添加这些列。

- Charlieface