sql - 当 WHERE 子句返回高比例的行时，Azure SQL 查询性能会显着降低

Question

我一直在努力解决我正在开发的网络应用程序中的性能瓶颈。我已经设法识别出导致问题的 SQL 查询，但我不确定如何解决它。基本查询是:

SELECT *
FROM Table
WHERE ColumnA = 0
ORDER BY AnotherColumn
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

A 列的类型为 BIT，可为空且不包含默认值。目前，每行(表中大约 290,000 行)的值都是 0。目前，查询大约需要 1 分 50 秒才能完成。

我觉得奇怪的是，通过将数据库中 ColumnA 的一小部分值更改为 1，性能会显着提高。

只需运行:

UPDATE Table SET ColumnA = 1 WHERE ID % 100 = 0

在大约 1% 的行中切换值，查询时间减少到 7 秒 - 快了 90% 以上。

我不明白为什么会有如此巨大的差异，并且想不出如何优化查询来解决问题。删除 WHERE 子句会导致相同的约 7 秒查询时间，因此我认为这与返回的数据无关。

我将 AzureSQL 与 EFCore 结合使用，但一直在 SSMS 中运行上述查询以尝试找出问题的根源。

Answer 1

这里的问题是缺乏索引。根据您的数据，您的表上只有 1 个索引(主键)，没有其他索引。这意味着，如果您查看主键的值，事情会很好而且很快，但是，对于其他事情来说，情况就不那么好了。

当您开始在包含大量数据的表上查询内容时，尤其是在 WHERE、ORDER、ON 等子句中，如果这些列上没有索引，事情就会开始变慢。为什么？因为 SQL Server 不知道在哪里查找该数据，所以它必须检查每一行。

考虑您的数据，包括 ID 列和 A 列。A 列实际上是 ID 的派生值(让我们使用 ID % 100)，但是，您的列是一个持久值，但不是根据ID计算的。然后你问 SQL Server“我可以获取 A 列值为 0 的所有行吗？” SQL Server 不知道这些行包含什么，并且没有 INDEX 来帮助它，因此它会在遍历所有 ID 时检查每一行。

现在，假设您在该列上有一个 INDEX。现在，当你向服务器询问同样的问题时，它可以查看索引。索引，会有一个A列的有序列表，并告诉Server它对应的ID(即存储数据的地方)。然后，SQL Server 可以查看该数据并发现所有值为 0 的列 As 都已很好地放在其列表(索引)中；然后它只是转到它需要的每个 ID。它最终不会检查每一行 A 列的值。

当然，这是一种非常“简单化”的查看索引的方式。事实上，它们要复杂得多。索引通常会加快从服务器获取数据的速度，但值得注意的是，它们会减慢某些任务的速度，例如INSERT。这是因为当它写入数据时，它还必须更新索引。这也意味着更多的 IO，因此较慢的光盘也会出现性能问题(尽管 SELECT 也会出现这种情况)。 UPDATE 命令可能会更快，具体取决于您正在执行的操作。

正如我所说，这是一个非常基本的描述；但可能会帮助你了解更多。我绝不是说你应该在每一列上放置一个 INDEX 。知道对哪些列建立索引以及如何建立索引是一件非常重要的事情，但它绝不能通过 SO 上的单个答案来教授。