什么是集群索引_集群与非集群：什么索引适合我的数据？

什么是集群索引

您知道多少次添加索引可以提高查询性能，但是您不确定是否要添加哪种类型的索引？

在使用SQL Server的头几年（可能再过一两年）中，这一直发生在我身上。

今天，我想通过比较两种最常见的索引类型来缓解这种混乱：群集的和非群集的行存储索引。

在YouTube上 观看 这篇文章 ，或者，如果您的风格更多，请继续阅读以下内容。

聚集索引

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

如果顺序是随机的，并且未明确定义，则该表称为堆。 除了一些特殊情况外，我们通常不希望有堆。 对于大多数查询，堆的性能不佳，因为SQL Server不了解有关堆中数据存储位置的元信息。

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

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

聚集索引的好处是什么？

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

1. 在聚集索引中查找所需的数据只是知道在字母顺序的数据列表中应查找的位置。 计算机确实擅长于此。
2. 如果您的数据需要按照与存储时相同的顺序进行输出，那就可以了！ — SQL不需要执行任何其他排序。

许多人喜欢将聚簇索引放在表的主键（PK）上。 这通常很好，因为很多时候我们的主键可能是我们最常用的联接，where语句等字段。

有些人认为他们只能将聚簇索引放在PK上。 这不是真的！ 通常，将聚簇索引放在不是您的PK的内容上可能会更加有益，例如，另一个列的使用率高于我们的PK。 例如，您可能将一个身份列设置为PK，但是针对表的每个查询都将对datetime2列进行过滤和排序。 如果您总是要在datetime2列上过滤并返回数据，为什么要按PK顺序存储表？ 将该聚集索引放在datetime2列上！

按此顺序存储数据的不利之处在于，插入和更新之类的操作会花费很长时间，因为SQL必须将它们置于表页的正确排序顺序中–它不能只是将它们快速地固定到最后。

聚集索引的另一个主要优点是我们不必在索引中“包括”任何其他数据。 该行的所有数据都在索引列旁边。 其他索引类型不一定是这种情况（请参阅下面的非聚集索引）。

假设我们的聚集索引就像电话簿的白页（请注意未来的2030年SQL开发人员，他们不知道电话簿是什么：它存储您所在区域的名称，地址和座机电话号码。 ？ 好家伙…）

电话簿按字母顺序存储每个人的姓名，从而使查找某些人变得容易。 此外，如果我们查找某人，我们将立即在其姓名旁边找到他们的地址和电话号码-无需其他搜索！

这是聚集索引的一项重要功能-如果您需要从表中检索很多或所有列，则聚集索引通常将非常有效，因为一旦找到了您要搜索的索引值，它就无需走到任何地方否则从该行获取剩余数据。

非聚集索引

如果聚簇索引就像电话簿，则非聚簇索引就像化学课本后面的索引。 化学教科书具有一定的自然秩序（“第1章：物质，第2章：元素，第3章：化合物等”）。 但是，如果我们要查找某些特定内容（例如“稀有气体”）的位置，则此顺序无济于事。

那么我们该怎么办？ 我们转到教科书背面的索引，该索引按字母顺序列出了所有主题，从而可以轻松地找到“稀有气体”的列表及其讨论的页码。 一旦从索引中知道了稀有气体的页码，就可以转到正确的页面并获取所需的数据。

本书索引代表我们的非聚集索引。 非聚集索引包含该索引中指定列的排序数据，并带有指针（书页编号），这些指针告诉我们从该行中查找其余数据的位置（跳至右书页） 。 这意味着与始终存在所有数据的聚簇索引不同，使用非聚簇索引通常需要两个步骤：在索引中找到感兴趣的值，然后从磁盘上实际存在的位置开始查找该行的其余数据。

非聚集索引有什么好处？

我们可以在表上根据需要拥有任意数量的非聚集索引（ 好吧，我们的最大值为999 ）。 那很棒！ 为每列创建一个索引！

好吧，不，不要那样做。 创建非聚集索引会产生开销。 本质上，每次索引某些列时，您都在复制这些列中的唯一值，以便可以按排序顺序将它们存储在索引中。 我们在数据查找中获得了速度和效率，但是却损失了磁盘空间。 您需要测试并查看每个表和一组查询的最佳索引数。 添加额外的索引绝对会破坏性能，因此请始终测试您的更改！

此外，使用非聚集索引来查找索引列的值是快速的（SQL只是通过有序索引数据来查找所需的值-再次，计算机确实非常擅长于此操作）。 但是，如果您需要从刚刚查找的行中获取其他数据列，则SQL将不得不使用这些索引指针在磁盘上的其他位置查找该行的其余数据。 这确实会增加和降低性能。

如果这些额外的查询影响了性能，那么您可以做的是在非聚集索引中包含非索引列。 这基本上是做什么的，除了存储索引列的排序值之外，索引还将存储要包含在索引本身中的任何其他值。 再一次，您可能会获得更好的性能，因为SQL不必去磁盘上的其他地方来查找它所需的数据，但是您丢失了存储空间，因为您正在创建这些数据的副本作为索引的一部分。

示例使用场景

注意：我想澄清的是，上面的定义和下面的示例并未涵盖很多极端情况（blob值，碎片等）。 当人们不知道应该首先尝试添加哪种索引类型时，我希望这篇文章是一个简单的起点，因为这是我刚开始时遇到的瘫痪。

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

好吧，让我们看一些常见的情况以及最适合他们的索引。 阅读每种情况后，猜测一下要添加哪种索引，然后阅读答案以查看我在那种情况下会做什么。 除非另有说明，否则假定这些表上尚无索引。

• 您拥有仅用于事务性读取和写入新行的OLTP数据。 您知道主键是一个标识整数列。 您将为主键创建哪种类型的索引？
• 聚集索引-PK可能总是会查询您的查询以返回数据。 如果将数据存储在该PK排序的表中，SQL将能够非常快速地执行此操作。 由于会自动增加标识列，因此总是在表的最后添加新行，不会为必须在有序数据中的特定位置插入数据而造成任何开销。
• 您有一个查询，希望返回表中的大多数或所有列。 哪种类型的索引将使其最有效？
• 聚集索引-由于所有列值都存储在与索引字段相同的位置，因此SQL不必进行任何其他查找就可以从中获取所请求的所有数据。 如果创建了非聚集索引，则必须包含所有非索引列，这将占用大量空间，因为实际上是在复制整个表的数据。
• 您有一个不断更新值的表。 这些更新的值在您的JOIN和WHERE子句中使用。 您将添加哪种类型的索引？
• 非聚集索引-如果我们的值在不断变化，则SQL只需更新索引和指针，同时将实际数据放在磁盘上有可用空间的任何位置即可。 将其与聚集索引进行比较，在聚集索引中，插入/更新的数据必须以正确的顺序放置，这意味着如果该位置不存在可用空间，则可能需要执行大量操作来转移数据。
• 您有一个已经有聚集索引的表，但是它不覆盖JOIN和WHERE子句中的列。 你是做什么？
• 非聚集索引-由于聚集索引已经存在，因此您唯一的选择是添加非聚集索引。 但是，根据命中该表的查询，如果您认为将这些字段作为聚集索引的一部分将可以改善JOINs和WHERE子句，则可以考虑将聚集索引更改为非聚集索引。 测试一下！
• 您有一个小的登台表，您将始终从中读取所有行，然后截断它。 您不在乎订单。 您是否添加索引？
• 不，将其保留为堆-在这种情况下，不添加索引可以为您提供更好的性能，因为SQL中没有开销，无需按排序顺序存储内容或更新索引来指定顺序。 如果您真的不在乎顺序，并且始终会从表中读取所有行，然后将其截断，那么最好不要在表上有索引。

谢谢阅读。 您可能还喜欢 在Twitter上关注我。

翻译自: https://hackernoon.com/clustered-vs-nonclustered-what-index-is-right-for-my-data-717b329d042c

什么是集群索引