SQL Server索引成本

Question

SQL Server索引成本

8

我了解到在SQL Server中添加表索引的一个权衡是为了提高选择查询的性能而增加插入/更新/删除查询的成本。我可以从概念上理解插入操作的过程，因为SQL Server必须将新行的条目写入每个索引，但对于更新和删除操作，我还不太清楚数据库引擎需要做什么。

以DELETE操作为例，假设我有以下模式（请原谅伪SQL）：

TABLE Foo
 col1 int
,col2 int
,col3 int
,col4 int
PRIMARY KEY (col1,col2)

INDEX IX_1
col3
INCLUDE 
col4

现在，如果我发布这个语句

DELETE FROM Foo WHERE col1=12 AND col2 > 34

我了解引擎更新表格（或者如果您愿意，聚集索引）的必要步骤。索引被设置为使查找要删除的行范围变得容易并执行删除操作。

然而，在这个点上，它还需要更新IX_1，我提供的查询没有明显高效的方法让数据库引擎查找要更新的行。此时它是否被强制执行完整的索引扫描？引擎首先从聚集索引中读取行并生成更智能的内部删除索引吗？

如果我更好地理解底层正在发生的事情，这可能有助于我理解这个问题，但我想我的真正问题是：我的数据库在删除操作上花费了大量时间，我正在尝试找出我可以做些什么。

当我显示删除的执行计划时，它只显示对Foo表的“聚集索引删除”条目，详细信息部分列出了需要更新的其他索引，但我没有得到这些其他索引相对成本的指示。

在这种情况下，它们都是相等的吗？有没有一种方法可以估计删除一个或多个这些索引的影响，而不必实际尝试？

- yellowstar

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Andras Vass · Accepted Answer

非聚集索引也会存储聚集键。
它不需要进行完整扫描，因为：

您的查询将使用聚集索引定位行
行包含其他索引值（c3）
使用其他索引值（c3）和聚集索引值（c1，c2），它可以在其他索引中定位匹配条目。

（注意：我在解释文档时遇到了困难，但我可以想象IX_1在您的情况下可以定义为如果它也按c1，c2排序。由于这些已经存储在索引中，因此使用它们更有效地定位记录，例如更新和删除是完全有意义的。）

然而，所有这些都有成本。对于每个匹配的行：

它必须读取该行以查找c3的值
它必须在非聚集索引中找到（c3，c1，c2）的条目
它还必须从那里删除该条目。

此外，虽然在您的情况下，范围查询可以在聚集索引上高效执行（在找到匹配项后进行线性访问），但维护其他索引很可能会导致对每个匹配行进行随机访问。与仅枚举从给定匹配项开始的B+树叶节点相比，随机访问的成本要高得多。
给定上述查询，更多时间花费在非聚集索引的维护上 - 其数量严重取决于由col1 = 12 AND col2 > 34谓词选择的记录数。

我猜想，成本在概念上与没有辅助索引但具有例如一个单独的表相同，该表将（c3，c1，c2）作为唯一列在聚集键中持有，并使用（c3，c1，c2）为每个匹配的行执行DELETE。显然，索引维护是SQL Server内部的，速度更快，但在概念上，我猜想以上接近。

上述意味着索引的维护成本会非常接近，因为每个次要索引中的条目数量相同（即记录数），并且删除只能在每个索引上逐个进行。

如果您需要索引，在性能方面，根据删除记录的数量，您最好安排删除、删除期间未使用的索引-然后在删除之前将它们删除，并在删除后添加它们。根据受影响的记录数量，重新构建索引可能更快。