数据库表何时变得足够大，使用索引才有益？

Question

数据库表何时变得足够大，使用索引才有益？

10

假设在SQL Server数据库中，如果我有一个包含两个int字段（例如一对多的关系）的表，该表参与连接另外两个表，那么在什么样的大小下，使用这两个int字段建立索引所带来的性能优势超过由此造成的开销？不同版本的SQL Server之间是否存在体系结构上的差异，会显著改变这个答案吗？

- Robert Harvey

9个回答

2

这取决于您的数据选择性，如果您的数据不够具有选择性，那么索引可能甚至不会被使用，因为成本太高了。如果表中只有2个值，并且这些值均匀分布，则会得到扫描而不是查找。

我仍然认为每个表都应该有一个主键，如果您有主键，那么您已经有了一个索引。

- SQLMenace

在我的假设中（多对多连接），外部表已经有索引是正确的。 - Robert Harvey

一个两列的连接表？如果两个其他表的主键选择性低是非常不寻常的。 - dkretz

@Robert：你的连接表怎么样了？它也应该有一个主键——如果没有其他的话，可以在两个外键列上创建一个复合主键。你当前的模式如何保护你免受重复记录的影响？ - GalacticCowboy

我总是在我的表中放置主键。在我考虑的情况下，不应该有重复的记录。连接是通过UI和一些复选框创建的，因此如果有重复的记录，则UI存在问题。这可能听起来像异端邪说，但我不喜欢组合键，直到你提到重复记录的情况，我从未找到过它们的用途。值得思考。 - Robert Harvey

你也可以使用复合唯一键或其他方式来实现，而不是使用主键。 - GalacticCowboy

Robert，我不是复合索引的粉丝，但两列连接表是一个例外。通常这些都是两个int列，在其他地方已经是主键了。您需要一种方法来确保表中数据的唯一性，因此无论如何都需要在这两列上添加唯一索引。在像这样的表上添加另一个主键实际上是浪费开销，最好只是在这种特殊情况下创建组合键。 - HLGEM

2

在索引的好处显现出来之前，插入操作的惩罚是可以忽略不计的。优化器在此之前会很聪明地忽略索引。所以最好一开始就为表创建索引。

- dkretz

你是在说SQL Server会根据索引的效益来决定是否使用索引来创建执行计划吗？ - Robert Harvey

1

好的。在 Stack Overflow 上有几个关于小表为什么没有使用索引的问题，答案是“你还没有足够的数据”。 - dkretz

所以我的问题变成了，“在多少条记录时，SQL Server优化器会启用索引？”答案是，“不关心？” - Robert Harvey

我假设您提供的情况是一个包含来自两个其他表的主键的连接表。在除了一些极端情况下（例如所有行中仅有一列的值），是的。将两个列（选择性最高的列首先）作为主键创建一个索引，另一个只针对另一列创建索引（如果您指定两个值，它将使用第一个）。或者如果您确定只会查询第一列，则省略第二个索引，这种情况相对较少。 - dkretz

2

无论大小，使用索引进行查找都会带来性能优势。

关于开销，问题变成了：您指的是哪种开销，以及您如何将其与查找的价值联系起来？毕竟，这两个值是分开的。

索引有两种开销形式：空间（通常可以忽略不计，具体取决于索引的结构）和插入时重新索引（服务器必须在每次插入后重新计算索引）。

正如我所提到的，空间问题可能并不是那么重要。但是重新索引却是一个问题。幸运的是，在该类开销成为问题之前，您需要进行大量连续插入。

因此，底线是：几乎总是更好地拥有索引。从这个位置开始，并等待重新索引成为瓶颈。然后您可以考虑替代方案。

- Randolpho

不正确。创建一个只有一行的表，添加索引，亲自看看。 - A-K

1

好的，“regardless of size” 的意思是“对于行数大于3的表格”。这样更清楚了吗？ - Randolpho

1

倾覆点真的只有三条记录吗？这似乎不太可能。 - Robert Harvey

还不太对。以下链接很有用：链接：“The Tipping Point Query Answers” http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Tipping-Point-Query-Answers.aspx - A-K

@AlexKuznetsov：我不明白这个链接的适用性。文章讨论了聚集索引会在什么时候被用于优先于另一种（非覆盖）索引的点。聚集索引 仍然是一个索引，因此提供性能优势；我的回答依然适用。原始问题明显暗示该表是一个没有聚集索引或 FK 索引的堆表。最后，请记住文章甚至建议任何可以覆盖查询的索引都不会“倾斜”到聚集索引。 - Randolpho

Randolpho，那篇文章的主要观点是进行基准测试而不是猜测，并且以页面读取为思考方式。只要所有数据都适合一页，就不需要查找 - 查找需要超过一页的读取。 - A-K

1

索引通常会提高查询的性能，但需要额外的内存和插入/删除时的性能成本（因为此时需要维护索引）。只有分析才是确定索引是否对您的特定情况有益的唯一方法。

一般来说，创建索引时，您正在交换内存以获取速度（除了插入的额外成本）。如果您进行许多查询（选择或更新）相对于插入/删除的行数，那么索引几乎总是会增加您的性能。

- Reed Copsey

如果情况相对标准（例如在表格中有两个int的示例），那么翻转点会相对平稳吗，还是会因为外部表中的附加列数量和其他未知因素而有所变化？ - Robert Harvey

优化器知道临界点在哪里，所以您不需要担心。如果加载惩罚很大，因为您正在快速加载行以便注意到它，那么您将很快达到该点。 - dkretz

索引将大大加快您的查询速度，几乎适用于所有情况。列数的多少并不会有太大影响，因为这只是定位要返回的适当行的问题（这与列数有些分离）。如果您的数据很少，索引可能帮助不大，但它几乎是免费的 - 我个人总是为任何位置查询频繁使用的列建立索引（包括更新行[只要您不更改索引列]），因为即使在相对较小的情况下，使用索引也会更快。 - Reed Copsey

1

另一个需要考虑的问题是编码性能的概念——有时索引可以简化思考如何管理不同数据之间关系的心理负担，但有时也可能会使其变得更加复杂...

- jwl

有一件事我没提到，就是我使用了 Linq to SQL。它似乎以字段名称和索引为线索来确定在模型中包含什么；特别是外键联接得到了特殊优待。 - Robert Harvey

1

非常有用的链接： "The Tipping Point Query Answers" http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Tipping-Point-Query-Answers.aspx

- A-K

谢谢。这告诉我没有固定的临界点，许多人对此没有很好的洞察力，我们有查询优化器是一件好事。 - Robert Harvey

1

最好的方法是让服务器自己来解决。您可以在有意义的列中创建索引（我确定有整章甚至整本书介绍如何以最佳方式执行此操作），然后让SQL服务器确定何时/如何使用索引。

在许多情况下，在优化时，您需要阅读特定DBMS的文档，了解它如何使用索引，并将其与您正在优化的应用程序使用的查询相关联。然后您可以微调索引使用。

- nos

0

我相信，一旦您开始在那些 int 字段上进行连接操作，您的表就已经足够大了。如果该表太小，无法从索引中受益，则开销不足以让您选择退出。

当我考虑索引开销时，通常会考虑表索引会因插入、删除和更新索引列而频繁更改的程度。

- nvuono

索引不仅会增加INSERT和UPDATE语句的开销，也会增加SELECT语句的开销。 - Robert Harvey

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

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

- Quassnoi · Accepted Answer

对于涉及表行的一小部分的查询，无论有 100 行还是 1,000,000 行，索引总是有益的。

请参阅我博客中的此条目，其中包含计划和性能详细信息的示例：

索引微小表格

像这样的查询：

SELECT  *
FROM    table1 t1
JOIN    table2 t2
ON      t2.col = t1.col

很可能会使用 HASH JOIN。一个小表的哈希表将被建立，并且来自大表的行将用于探测哈希表。

为此，不需要索引。

然而，这个查询:

SELECT  *
FROM    table1 t1
JOIN    table2 t2
ON      t2.col = t1.col
WHERE   t1.othercol = @value

将使用 NESTED LOOPS：从外部表(table1)获取的行将使用一个在table1.othercol上的索引进行搜索，并且从内部表(table2)获取的行将使用一个在table2.col上的索引进行搜索。

如果您没有在col1上创建索引，将会使用 HASH JOIN，需要扫描两个表中的所有行并使用一些额外的资源来构建哈希表。

索引也对此类查询非常有用：

SELECT  t2.col
FROM    table1 t1
JOIN    table2 t2
ON      t2.col = t1.col

如果是这种情况，引擎根本不需要读取table2本身：这个查询所需的所有数据都可以在索引中找到，而索引可能比表本身更小、更高效地进行读取。

当然，如果你需要对数据排序，并且在table1.col和table2.col上都有索引，那么以下查询就非常有用：

SELECT  *
FROM    table1 t1
JOIN    table2 t2
ON      t2.col = t1.col
ORDER BY
        t2.col

如果两个输入的数据集都已排序，那么查询可能会使用“合并连接（MERGE JOIN）”方法，这种方法非常快速，并且其输出也是有序的，这意味着“ORDER BY”不需要额外操作。

请注意，即使您没有索引，优化器也可能选择“Eager Spool”您的小表，这意味着在查询期间建立一个临时索引，并在查询结束后删除该索引。

如果查询很小，它将非常快速，但是再次强调，索引不会损害查询性能（指SELECT查询）。如果优化器不需要它，它就不会被使用。

请注意，创建索引可能会影响DML性能，但这是另一回事。