聚集与非聚集:什么索引适合我的数据？

原文：https://medium.com/hackernoon/clustered-vs-nonclustered-what-index-is-right-for-my-data-717b329d042c

有多少次你知道添加一个索引会提高查询性能，但是你不确定应该添加哪种类型的索引？

在我使用 SQL Server 的最初几年(之后可能还会有一两年)，这种情况一直发生在我身上。

今天，我想通过比较两种最常见的索引类型:聚集行存储索引和非聚集行存储索引来帮助缓解这种困惑。

观看 YouTube 上的 这篇文章 ，或者继续阅读下面的内容，如果那更符合你的风格。

聚集索引

每个表的数据都有一些自然的顺序。

如果顺序是随机的，并且没有明确定义，那么这个表就称为堆。除了少数特殊情况，我们通常不希望有堆。堆在大多数查询中表现不佳，因为 SQL Server 不知道数据存储在堆中的什么位置。

如果我们没有随机堆，这意味着我们已经定义了数据在表中的存储顺序。我们数据的物理存储顺序是由我们的聚集索引定义的。

每个表只能有一个聚集索引，因为一个表中的数据只能以一种顺序存储，也就是说，该表的数据不能以多种顺序物理存储在磁盘上。

聚集索引有什么好处？

聚集索引中的数据按顺序存储。这意味着:

要在聚集索引中找到您需要的数据，需要知道在按字母顺序排列的数据列表中的什么位置。电脑真的很擅长做这件事。
如果您的数据需要按照存储的顺序输出，马上！— SQL 不需要做任何额外的排序。

很多人喜欢将聚集索引放在他们的表的主键(PK)上。这通常没问题，因为很多时候我们的主键可能是我们最常用的连接、where 语句等字段

有些人认为他们只能将聚集索引放在 PK 上。不是这样的！通常情况下，将聚集索引放在不属于您的 PK 的地方会更有好处，比如比我们的 PK 更有用的不同列。例如，您可能将 identity 列设置为 PK，但是对表的每个查询都是在 datetime2 列上进行筛选和排序。如果总是要在 datetime2 列上筛选和返回数据，为什么要按 PK 顺序存储表呢？将聚集索引放在 datetime2 列上！

以这种顺序存储数据的缺点是，像插入和更新这样的操作需要很长时间，因为 SQL 必须将它们放入表页的正确排序顺序中——它不能快速地将它们放在末尾。

聚集索引的另一个主要好处是，我们不必在索引中“包括”任何额外的数据。我们行的所有数据都存在于索引列的旁边。其他索引类型不一定如此(请参见下面的非聚集索引)。

假设我们的聚集索引就像电话簿的白页(2030 年的 SQL 开发人员可能不知道电话簿是什么:它是存储您所在地区的姓名、地址和固定电话号码的东西。什么是座机？哦男孩…)

电话簿按字母顺序存储每个人的名字，便于查找特定的人。此外，如果我们查找某人，我们会立即在他的名字旁边找到他的地址和电话号码，无需额外搜索！

这是聚集索引的一个很好的特性，如果您需要从表中检索许多或所有列，聚集索引通常会很有效，因为一旦它找到了您要搜索的索引值，它就不需要去任何其他地方获取该行的剩余数据。

非聚集索引

如果聚集索引就像电话簿，那么非聚集索引就像化学教科书后面的索引。化学教科书有一些自然的顺序(“第一章:物质，第二章:元素，第三章:化合物，等等”)。然而，如果我们想查找某个特定的东西的位置，比如“惰性气体”，这个顺序对我们没有帮助。

那我们该怎么办？我们查阅教科书后面的索引，它按字母顺序列出了所有的主题，很容易找到“惰性气体”的目录和讨论它们的页码。一旦我们从索引中知道了惰性气体的页码，我们就可以翻到正确的一页，得到我们需要的数据。

该图书索引代表我们的非聚集索引。非聚集索引包含该索引中指定的列的有序数据，指针(书籍页码)告诉我们在哪里可以找到该行的其余数据(翻到正确的书籍页面)。这意味着，与所有数据总是存在的聚集索引不同，使用非聚集索引通常是一个两步过程:在索引中找到感兴趣的值，然后从该行数据在磁盘上的实际位置开始查找该行的其余数据。

非聚集索引有什么好处？

我们的表上可以有任意多的非聚集索引(好吧，我们的最大值是 999 )。太好了！为每列创建一个索引！

不，别这样。创建非聚集索引是有开销的。实际上，每次为某些列建立索引时，您都在复制这些列中的唯一值，以便它们可以按排序顺序存储在索引中。我们获得了数据查找的速度和效率，但代价是损失了磁盘空间。您需要测试并查看每个表和查询集的最佳索引数量。添加一个额外的索引绝对会破坏性能，所以请始终测试您的更改！

此外，使用非聚集索引来查找索引列的值速度很快(SQL 只是遍历有序索引数据来查找它需要的值，这也是计算机真正擅长做的事情)。但是，如果您需要刚刚查找的行中的其他数据列，SQL 将不得不使用这些索引指针在磁盘上的其他地方查找该行的其余数据。这真的会增加并降低性能。

如果这些额外的查找损害了性能，您可以做的就是在非聚集索引中包含非索引列。这基本上是除了存储索引列的排序值之外，索引还将存储您希望作为索引本身的一部分包含的任何附加值。同样，您可能会获得更好的性能，因为 SQL 不必到磁盘上的其他地方去查找它需要的数据，但是您会损失存储空间，因为您正在创建该数据的副本作为索引的一部分。

示例使用场景

注意:我想澄清的是，上面的定义和下面的例子并没有涵盖很多极端情况(斑点值、碎片等……)。当人们不知道他们应该首先尝试添加什么索引类型时，我希望这篇文章是一个简单的起点，因为这是我开始时的麻痹状态。

本文中的每一句话都可能在末尾加上一个星号，指出我写的某个建议 100%错误的例子。始终测试您的索引更改，因为可能改进一个查询的内容可能会损害已经在该表上运行的另一个查询，随着时间的推移，您将了解所有这些边缘情况以及它们如何影响索引性能。

好的，让我们来看看一些常见的场景，以及对它们来说最好的索引可能是什么。读完每个场景后，猜一猜你会添加什么样的索引，然后读一下答案，看看在这种情况下我会怎么做。除非另有说明，否则假设这些表上还不存在索引。

您有只用于事务性读取和写入新行的 OLTP 数据。您知道主键是一个整数列。你会为主键创建什么类型的索引？
聚集索引—您的查询可能总是通过 PK 进行查找以返回数据。如果您将数据存储在按该 PK 排序的表中，SQL 将能够非常快速地完成这项工作。由于自动递增的标识列，添加到表中的新行总是放在最后，不会因为必须在有序数据的特定位置插入数据而产生任何开销。
您有一个想要返回表中大多数或所有列的查询。什么类型的索引最有效？
聚集索引—因为所有的列值都存储在与索引字段相同的位置，所以 SQL 不必进行任何额外的查找来获取您请求的所有数据。如果创建了非聚集索引，则必须包含所有非索引列，这将占用大量空间，因为实际上是复制整个表的数据。
您有一个不断更新值的表。这些更新的值在您的连接和 WHERE 子句中使用。你会添加什么类型的索引？
非聚集索引—如果我们的值不断变化，SQL 只需更新索引和指针，同时将实际数据放在磁盘上任何有可用空间的地方。与聚集索引相比，聚集索引必须将插入/更新的数据按正确的顺序放置，这意味着如果该位置不存在可用的空闲空间，可能需要大量的操作来转移数据。
您有一个已经有聚集索引的表，但是它不包含连接和 WHERE 子句中的列。你是做什么的？
非聚集索引—由于聚集索引已经存在，您唯一的选择就是添加非聚集索引。但是，根据命中该表的查询，如果您认为将这些字段作为聚集索引的一部分可以改进联接和 WHERE 子句，则可能需要考虑将聚集索引更改为非聚集索引。测试一下！
您有一个小的临时表，您将总是从中读取所有行，然后将其截断。你不在乎秩序。你加指数吗？
不，让它作为一个堆——在这种情况下，不添加索引可以获得更好的性能，因为 SQL 中没有按排序顺序存储内容或更新索引来指定顺序的开销。如果您真的不关心顺序，并且总是从一个表中读取所有行，然后截断该表，那么最好不要在表上建立索引。

感谢阅读。你可能也会喜欢在推特上关注我的 。