主页 / dba / 问题 / 317762
Accepted
bigchief
Asked: 2022-10-04 04:17:06 +0800 CST

当表上有两个唯一索引且一个索引中的所有列也在另一个索引中时，是否存在性能损失？

  • 772

在我的数据库中，我有一个带有两个索引的表。出于数据完整性的原因，我需要在 a、b 列上有一个唯一索引 #1。我有另一个索引#2，列 c、a、b 用于性能原因。我注意到这个索引#2 也是独一无二的。

索引#2 的唯一性在我看来是多余的，因为你不能在索引#2 中有重复的值，而在索引#1 中也没有重复的值。我很想更改索引#2，因此它不再是唯一的，因为我想数据库引擎可能会对索引#2 中的 c、a、b 执行第二次检查，以确保每次插入一行时这些列的唯一性，导致即使永远不会有重复的值，也会影响性能。这个对吗？

有什么方法可以删除 a,b 上的索引 #1 并保留 c,a,b 上的索引 #2 但仍然对列 a,b 强制唯一约束而不维护两个单独的索引？这将允许我只有一个包含所有三列的索引，但仍然对 a、b 强制执行我的数据完整性约束。我不需要 a,b 上的索引来提高性能，因为我的所有选择查询都在 where 子句中包含 c 列。这会是唯一约束而不是索引的用例吗？我认为数据库引擎基本上将这两个构造视为相同（参见这篇文章：我何时应该使用唯一约束而不是唯一索引？）。

请记住，索引不是多余的，但索引的“唯一性”是多余的。似乎让索引#2 不唯一是一件轻而易举的事。但这会带来任何实际的性能提升吗？即使索引#1 中的列完全包含在索引#2 中，数据库是否会检查两个索引的唯一性？

一些答案询问了用于从该表中选择数据的示例查询。以下是最常见的：

Select [some other columns] from table where c=1 and a=2
Select [some other columns] from table where c=1
Select [some other columns] from table where c=1 and a=2 and b=3

这些查询通常包括选择许多不在任何索引中的其他列。

我们通常从不运行的是这样的查询：

Select [stuff] from table where a=2 and b=3
sql-server index-tuning

3 个回答

  1. Best Answer

    索引#2 的唯一性在我看来是多余的，因为你不能在索引#2 中有重复的值，而在索引#1 中也没有重复的值。

    是的，声明索引#2唯一是多余的。索引#1 需要存在以支持外键，因此它被丢弃的可能性很小。

    我很想更改索引#2，因此它不再是唯一的，因为我想数据库引擎可能会对索引#2 中的 c、a、b 执行第二次检查，以确保每次插入一行时这些列的唯一性，导致即使永远不会有重复的值，也会影响性能。

    这是错误的关注点。SQL Server 总是需要定位插入点。如果它找到提供的键的现有行并且索引被标记为唯一，则会引发错误。否则，将在正确的点插入新行。

    不必要地将索引标记为唯一会产生更重要的后果。任何更改其中一个键列的更新都需要特殊处理，以避免在更新处理期间发生临时键违规。在 SQL Server 中，这意味着额外的Split、Sort和Collapse运算符以及受影响索引的广泛（每个索引）维护。

    拆分/排序/折叠以维护唯一索引

    这比您担心的问题要贵得多。

    有什么方法可以删除 a,b 上的索引 #1 并保留 c,a,b 上的索引 #2 但仍然对列 a,b 强制唯一约束而不维护两个单独的索引？

    不。

    我不需要 a,b 上的索引来提高性能，因为我的所有选择查询都在 where 子句中包含 c 列。

    示例查询表明，仅列 c 上的相等谓词具有足够的选择性，优化器会选择非聚集索引查找加查找计划。优化器对选择这种类型的计划很谨慎，这意味着 c 列确实非常有选择性（或者估计很差）。

    由于无论如何您都在检索索引之外的列，因此您很可能会发现仅列 c 上的非聚集非唯一索引就足够了。

    如果足够的查询受益于更精确的索引搜索以证明额外的空间和维护是合理的，那么将 a 和/或 b 添加到索引键将很有用。只有你有足够的信息来做出这个判断。

    • 7

  2. SQL Server 使索引与基表数据保持同步。当一行插入到表中时，它也会添加到所有相关索引中。

    索引是有序的。所以必须将新行添加到每个索引中的正确位置。找到添加新行的正确位置后，查看该位置是否存在现有值是微不足道的。检查唯一性的开销是微不足道的。

    是的，在插入期间，表上有多个索引会产生开销。但这是因为有多个索引必须与表保持一致。它与索引是否唯一无关。

    读取数据时使用重叠索引有很多好处。格兰特的回答涵盖了这一点。开销和收益之间的平衡将根据您的工作量而有所不同。

    如果索引是唯一的，最好这样声明它。查询优化器在构建查询计划时使用该信息。由于这两个索引都被声明为唯一的，因此优化器平均而言更有可能提出比其他情况更好的计划。尽可能多地为优化器提供有关数据的信息，以帮助其完成工作。

    无法将索引列的子集声明为对另一个索引的（部分）唯一和冗余。

    • 4

  3. 因此，这是两个不同的索引，具有不同的键结构。是的，A,B 上的#1 在#2 中复制，但是，添加列 C 意味着它也必须是唯一的，并与 A&B 结合。这不是索引#1 行为的重复。它是分开的和不同的。

    如果设置 #1 是 A,B 并且 #2 是 A,B,C，那么 #1 将是一个冗余索引，可能不需要。然而，即使在这里，也必须谨慎行事。因为，在这种情况下，#1 小于 #2。因此，根据情况，#1 的扫描可能比#2 的扫描更快。在这种情况下，#1 用于某些查询，即使它是 #2 的副本。

    此外，当不同的列是索引中的第一列时，它们会在统计信息中产生不同的直方图。这些不同的直方图可能对不同的查询有用。因此，列 A、B 上的唯一索引可能支持与列 B、A 上的唯一索引不同的查询。尽管从逻辑角度来看，这些是相同的索引。

    测试查询以了解索引的使用方式很重要。随着时间的推移监控性能也很重要。然后，当您确实找到一个合法的重复索引时，您可以判断删除它是否对性能无影响、有助于性能还是会损害性能。因为只看索引及其结构，不会告诉你它在系统中是如何使用的。

    编辑：忘了包括这个。当然，由于索引过多，维护会产生开销，甚至可能会阻塞和资源争用。这就是为什么消除重复索引很重要的原因。我并不是说你不应该消除重复。只是您需要非常清楚什么构成重复项（在这种情况下您没有），以及该重复项是否存在用于其他目的。

    • 3

相关问题