聚集索引和非聚集索引实际上是什么意思？

使用聚集索引，行以与索引相同的顺序物理存储在磁盘上。因此，聚集索引只能有一个。

对于非聚集索引，有第二个列表具有指向物理行的指针。您可以拥有许多非聚集索引，尽管每个新索引都会增加写入新记录所需的时间。

如果要取回所有列，通常从聚集索引中读取会更快。您不必先访问索引，然后再访问表。

如果需要重新排列数据，写入具有聚集索引的表可能会更慢。

---

聚集索引意味着您告诉数据库在磁盘上存储实际上彼此接近的接近值。这有利于快速扫描/检索落入某个聚集索引值范围的记录。

例如，您有两个表 Customer 和 Order：

Customer
----------
ID
Name
Address

Order
----------
ID
CustomerID
Price

如果您希望快速检索某个特定客户的所有订单，您可能希望在 Order 表的“CustomerID”列上创建一个聚集索引。这样，具有相同 CustomerID 的记录将在物理上彼此靠近地存储在磁盘上（集群），从而加快检索速度。

PS CustomerID 上的索引显然不是唯一的，因此您要么需要添加第二个字段来“唯一化”索引，要么让数据库为您处理，但这是另一回事。

关于多指标。每个表只能有一个聚集索引，因为这定义了数据的物理排列方式。如果您希望进行类比，请想象一个有很多桌子的大房间。你可以把这些桌子排成几排，也可以把它们拉在一起形成一个大会议桌，但不能同时使用这两种方式。一个表可以有其他索引，然后它们将指向聚集索引中的条目，而聚集索引最终会说明在哪里可以找到实际数据。

---

在 SQL Server 中，面向行的存储聚集索引和非聚集索引都被组织为 B 树。

https://i.stack.imgur.com/QF8d7.gif

(Image Source)

聚集索引和非聚集索引的主要区别在于聚集索引的叶级是表。这有两个含义。

聚集索引叶页上的行始终包含表中每个（非稀疏）列的内容（值或指向实际值的指针）。聚集索引是表的主副本。

非聚集索引也可以通过使用 INCLUDE 子句（自 SQL Server 2005 起）来明确包含所有非键列，但它们是辅助表示，并且始终存在另一个数据副本（表本身），从而实现第 1 点。

CREATE TABLE T
(
A INT,
B INT,
C INT,
D INT
)

CREATE UNIQUE CLUSTERED INDEX ci ON T(A, B)
CREATE UNIQUE NONCLUSTERED INDEX nci ON T(A, B) INCLUDE (C, D)

上面的两个索引几乎相同。包含键列 A, B 的值的上层索引页和包含 A, B, C, D 的叶级页

每个表只能有一个聚集索引，因为数据行本身只能按一种顺序排序。

上面引用的 SQL Server 在线书籍引起了很多混乱

在我看来，它会更好地表述为。

每个表只能有一个聚集索引，因为聚集索引的叶级行是表行。

这本书的在线报价并没有错，但您应该清楚，非聚集索引和聚集索引的“排序”是逻辑的，而不是物理的。如果您按照链表读取叶级别的页面并按插槽数组顺序读取页面上的行，那么您将按排序顺序读取索引行，但实际上页面可能未排序。人们普遍认为，对于聚集索引，行总是以与索引键相同的顺序物理存储在磁盘上，这是错误的。

这将是一个荒谬的实现。例如，如果将一行插入到 4GB 表的中间，SQL Server 不必在文件中复制 2GB 数据来为新插入的行腾出空间。

相反，会发生页面拆分。聚集索引和非聚集索引的叶级别的每个页面都具有按逻辑键顺序排列的下一页和上一页的地址 (File: Page)。这些页面不必是连续的或按密钥顺序排列。

例如，链接的页面链可能是 1:2000 <-> 1:157 <-> 1:7053

当页面拆分发生时，从文件组中的任何位置（从混合扩展区、小型表或属于该对象的非空统一扩展区或新分配的统一扩展区）分配新页面。如果文件组包含多个文件，这甚至可能不在同一个文件中。

逻辑顺序和连续性与理想化物理版本的不同程度就是逻辑碎片化程度。

在一个新创建的带有单个文件的数据库中，我运行了以下命令。

CREATE TABLE T
  (
     X TINYINT NOT NULL,
     Y CHAR(3000) NULL
  );

CREATE CLUSTERED INDEX ix
  ON T(X);

GO

--Insert 100 rows with values 1 - 100 in random order
DECLARE @C1 AS CURSOR,
        @X  AS INT

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT number
    FROM   master..spt_values
    WHERE  type = 'P'
           AND number BETWEEN 1 AND 100
    ORDER  BY CRYPT_GEN_RANDOM(4)

OPEN @C1;

FETCH NEXT FROM @C1 INTO @X;

WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO T (X)
      VALUES        (@X);

      FETCH NEXT FROM @C1 INTO @X;
  END

然后检查页面布局

SELECT page_id,
       X,
       geometry::Point(page_id, X, 0).STBuffer(1)
FROM   T
       CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
ORDER  BY page_id

结果到处都是。键顺序的第一行（值为 1 - 用下面的箭头突出显示）几乎位于最后一个物理页面上。

https://i.stack.imgur.com/OL6eY.png

通过重建或重组索引以增加逻辑顺序和物理顺序之间的相关性，可以减少或消除碎片。

运行后

ALTER INDEX ix ON T REBUILD;

我得到了以下

https://i.stack.imgur.com/ETaJd.png

如果表没有聚集索引，则称为堆。

非聚集索引可以建立在堆或聚集索引上。它们始终包含返回基表的行定位器。在堆的情况下，这是一个物理行标识符 (rid)，由三个组件组成 (File:Page: Slot)。在聚集索引的情况下，行定位符是逻辑的（聚集索引键）。

对于后一种情况，如果非聚集索引已经自然地包含 CI 键列作为 NCI 键列或 INCLUDE-d 列，则不会添加任何内容。否则，丢失的 CI 键列会被默默地添加到 NCI。

SQL Server 始终确保键列对于两种类型的索引都是唯一的。但是，对于未声明为唯一的索引执行此操作的机制在两种索引类型之间有所不同。

对于键值与现有行重复的任何行，聚集索引会添加一个 uniquifier。这只是一个升序整数。

对于未声明为唯一的非聚集索引，SQL Server 以静默方式将行定位符添加到非聚集索引键中。这适用于所有行，而不仅仅是那些实际重复的行。

聚集与非聚集命名法也用于列存储索引。论文 Enhancements to SQL Server Column Stores 指出

尽管列存储数据并没有真正“聚集”在任何键上，但我们决定保留将主索引称为聚集索引的传统 SQL Server 约定。

---

我意识到这是一个非常古老的问题，但我想我会提供一个类比来帮助说明上面的好答案。

聚集索引

如果您走进公共图书馆，您会发现所有书籍都按特定顺序排列（很可能是杜威十进制系统，或 DDS）。这对应于书籍的“聚集索引”。如果您想要的书的 DDS# 是 005.7565 F736s，您首先要找到标有 001-099 或类似名称的书架行。 （堆栈末尾的这个 endcap 符号对应于索引中的“中间节点”。）最终，您将向下钻取到标有 005.7450 - 005.7600 的特定书架，然后您将扫描直到找到具有指定 DDS 的书# ，到那时你已经找到你的书了。

非聚集索引

但是，如果您进入图书馆时没有记住您的图书的 DDS#，那么您将需要第二个索引来帮助您。在过去，您会在图书馆的前面找到一个精美的抽屉柜，称为“卡片目录”。里面有数千张 3x5 卡片——每本书一张，按字母顺序（可能是按标题）排序。这对应于“非聚集索引”。这些卡片目录以分层结构组织，因此每个抽屉都将标有它包含的卡片范围（例如Ka - Kl；即“中间节点”）。再一次，您会一直深入直到找到您的书，但是在 这种 的情况下，一旦找到它（即“叶节点”），您就没有书本身，但是只是一张带有 index 编号（DDS#）的卡片，您可以使用它在聚集索引中找到实际的书。

当然，没有什么能阻止图书管理员复印所有卡片并在单独的卡片目录中以不同的顺序对它们进行分类。 （通常至少有两个这样的目录：一个按作者姓名排序，一个按标题排序。）原则上，您可以拥有任意数量的这些“非聚集”索引。

---

在下面找到聚集索引和非聚集索引的一些特征：

聚集索引

聚集索引是唯一标识 SQL 表中的行的索引。每个表都可以只有一个聚集索引。您可以创建涵盖多个列的聚集索引。例如：创建索引 index_name(col1, col2, col.....)。默认情况下，具有主键的列已经具有聚集索引。

非聚集索引

非聚集索引类似于简单索引。它们仅用于快速检索数据。不确定是否有唯一数据。

---

聚集索引

聚集索引确定表中 DATA 的物理顺序。因此，一张表只有一个聚集索引（主键/复合键）。

“字典”不需要任何其他索引，它已经根据单词索引

非聚集索引

非聚集索引类似于书籍中的索引。数据存储在一个地方。索引存储在另一个位置，并且索引具有指向存储位置的指针。这有助于快速搜索数据。出于这个原因，一个表有超过 1 个非聚集索引。

“Biology Book”在盯着有一个单独的索引指向章节位置，在“END”有另一个索引指向公共 WORDS 位置

---

一个非常简单的、非技术性的经验法则是聚集索引通常用于您的主键（或至少是唯一列），而非聚集索引用于其他情况（可能是外键） .事实上，SQL Server 默认会在你的主键列上创建一个聚集索引。正如您将了解到的，聚集索引与数据在磁盘上的物理排序方式有关，这意味着它在大多数情况下都是一个很好的全面选择。

---

聚集索引

聚集索引基本上是一个树组织的表。聚集索引不是将记录存储在未排序的堆表空间中，而是实际上是 B+Tree 索引，其叶子节点按集群键列值排序，存储实际的表记录，如下图所示。

https://i.stack.imgur.com/mFpWj.png

聚集索引是 SQL Server 和 MySQL 中的默认表结构。尽管即使表没有主键，MySQL 也会添加隐藏的聚集索引，但如果表有主键列，SQL Server 总是会构建聚集索引。否则，SQL Server 将存储为堆表。

聚集索引可以加快按聚集索引键过滤记录的查询，就像通常的 CRUD 语句一样。由于记录位于叶节点中，因此在按主键值定位记录时，无需额外查找额外的列值。

例如，在 SQL Server 上执行以下 SQL 查询时：

SELECT PostId, Title
FROM Post
WHERE PostId = ? 

可以看到 Execution Plan 使用了 Clustered Index Seek 操作来定位包含 Post 记录的 Leaf Node，扫描 Clustered Index 节点只需要两次逻辑读取：

|StmtText                                                                             |
|-------------------------------------------------------------------------------------|
|SELECT PostId, Title FROM Post WHERE PostId = @P0                                    |
|  |--Clustered Index Seek(OBJECT:([high_performance_sql].[dbo].[Post].[PK_Post_Id]), |
|     SEEK:([high_performance_sql].[dbo].[Post].[PostID]=[@P0]) ORDERED FORWARD)      | 

Table 'Post'. Scan count 0, logical reads 2, physical reads 0

非聚集索引

由于聚集索引通常是使用主键列值构建的，因此如果要加快使用其他列的查询，则必须添加辅助非聚集索引。

二级索引将主键值存储在其叶子节点中，如下图所示：

https://i.stack.imgur.com/kPFIa.png

因此，如果我们在 Post 表的 Title 列上创建二级索引：

CREATE INDEX IDX_Post_Title on Post (Title)

我们执行以下 SQL 查询：

SELECT PostId, Title
FROM Post
WHERE Title = ? 

我们可以看到，使用了 Index Seek 操作来定位 IDX_Post_Title 索引中的 Leaf Node，可以提供我们感兴趣的 SQL 查询投影：

|StmtText                                                                      |
|------------------------------------------------------------------------------|
|SELECT PostId, Title FROM Post WHERE Title = @P0                              |
|  |--Index Seek(OBJECT:([high_performance_sql].[dbo].[Post].[IDX_Post_Title]),|
|     SEEK:([high_performance_sql].[dbo].[Post].[Title]=[@P0]) ORDERED FORWARD)|

Table 'Post'. Scan count 1, logical reads 2, physical reads 0

由于关联的 PostId 主键列值存储在 IDX_Post_Title 叶节点中，因此此查询不需要额外查找来定位聚集索引中的 Post 行。

---

聚集索引

聚集索引根据键值对表或视图中的数据行进行排序和存储。这些是索引定义中包含的列。每个表只能有一个聚集索引，因为数据行本身只能按一种顺序排序。

表中的数据行按排序顺序存储的唯一时间是表包含聚集索引时。当表具有聚集索引时，该表称为聚集表。如果表没有聚集索引，则其数据行存储在称为堆的无序结构中。

非聚集

非聚集索引具有与数据行分开的结构。非聚集索引包含非聚集索引键值，每个键值条目都有一个指向包含键值的数据行的指针。从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆还是聚集表中。对于堆，行定位器是指向行的指针。对于聚集表，行定位符是聚集索引键。

您可以将非键列添加到非聚集索引的叶级别以绕过现有索引键限制，并执行完全覆盖的索引查询。有关详细信息，请参阅创建包含列的索引。有关索引键限制的详细信息，请参阅 SQL Server 的最大容量规范。

参考：https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described

---

让我提供一个关于“聚类索引”的教科书定义，它取自 Database Systems: The Complete Book 的 15.6.1：

我们也可以说集群索引，它是一个或多个属性上的索引，使得该索引的搜索键具有固定值的所有元组出现在大致尽可能少的块上。

为了理解定义，我们看一下教科书提供的Example 15.10：

按属性 a 排序并按该顺序存储、打包成块的关系 R(a,b) 肯定是聚类的。 a 上的索引是聚集索引，因为对于给定的 a 值 a1，所有具有该 a 值的元组都是连续的。因此，它们似乎被打包成块，除了可能包含 a 值 a1 的第一个和最后一个块，如图 15.14 所示。但是，b 上的索引不太可能是聚类的，因为具有固定 b 值的元组将分布在整个文件中，除非 a 和 b 的值非常密切相关。

https://i.stack.imgur.com/gN8kX.png

请注意，该定义并不强制数据块在磁盘上必须是连续的；它只是说带有搜索键的元组被打包到尽可能少的数据块中。

一个相关的概念是聚集关系。如果一个关系的元组被打包到尽可能少的块中，则该关系是“聚集”的。换句话说，从磁盘块的角度来看，如果它包含来自不同关系的元组，那么这些关系就不能被聚类（即，通过将来自其他磁盘块的该关系的元组与元组不属于当前磁盘块中的关系）。显然，上面示例中的 R(a,b) 是聚类的。

要将两个概念连接在一起，聚集关系可以具有聚集索引和非聚集索引。但是，对于非聚集关系，除非索引建立在关系的主键之上，否则无法使用聚集索引。

“集群”作为一个词在数据库存储端的所有抽象级别（三个抽象级别：元组、块、文件）中被垃圾邮件发送。一个称为“clustered file”的概念，它描述文件（一组块（一个或多个磁盘块）的抽象）是否包含来自一个关系或不同关系的元组。它与文件级别的集群索引概念无关。

但是，有些teaching material 喜欢根据聚簇文件定义来定义聚簇索引。这两种类型的定义在集群关系层面上是相同的，无论是从数据磁盘块还是文件的角度来定义集群关系。从本段中的链接，

在以下情况下，文件上的属性 A 上的索引是聚簇索引： 属性值 A = a 的所有元组按顺序（= 连续）存储在数据文件中

连续存储元组与说“元组被打包成大约可以容纳这些元组的尽可能少的块”是一样的（一个谈论文件，另一个谈论磁盘略有不同）。这是因为连续存储元组是实现“打包到尽可能少的块中尽可能多地容纳这些元组”的方式。

---

聚集索引：如果表上不存在聚集索引，主键约束会自动创建聚集索引。聚集索引的实际数据可以存储在索引的叶级。

非聚集索引：非聚集索引的实际数据不是直接在叶节点上找到的，而是必须采取额外的步骤才能找到，因为它只有指向实际数据的行定位器的值。非聚集索引不能作为聚集索引排序。每个表可以有多个非聚集索引，实际上这取决于我们使用的 sql server 版本。基本上 Sql server 2005 允许 249 个非聚集索引，对于 2008、2016 等以上版本，它允许每个表有 999 个非聚集索引。

---

聚集索引 - 聚集索引定义了数据在表中物理存储的顺序。表数据只能以唯一方式排序，因此每个表只能有一个聚集索引。在 SQL Server 中，主键约束自动在该特定列上创建聚集索引。

非聚集索引 - 非聚集索引不对表内的物理数据进行排序。实际上，非聚集索引存储在一个地方，而表数据存储在另一个地方。这类似于一本教科书，其中书籍内容位于一个地方，而索引位于另一个地方。这允许每个表有一个以上的非聚集索引。这里重要的是要提到，在表内部，数据将按聚集索引排序。但是，非聚集索引里面的数据是按照指定的顺序存储的。索引包含创建索引的列值和该列值所属记录的地址。当针对创建索引的列发出查询时，数据库将首先转到索引并查找表中对应行的地址。然后它将转到该行地址并获取其他列值。正是由于这个额外的步骤，非聚集索引比聚集索引慢

聚集索引和非聚集索引之间的区别

每个表只能有一个聚集索引。但是，您可以在单个表上创建多个非聚集索引。聚集索引只对表进行排序。因此，它们不会消耗额外的存储空间。非聚集索引存储在与实际表不同的位置，需要更多存储空间。聚集索引比非聚集索引更快，因为它们不涉及任何额外的查找步骤。

有关详细信息，请参阅 this 文章。

---