聚集索引和非聚集索引实际上意味着什么？

2018-06-05 21:05:51

我对数据库的暴露程度有限，只使用数据库作为应用程序员。我想知道有关Clustered和Non clustered indexes 。我GOOGLE了一下，发现的是：

聚集索引是一种特殊类型的索引，用于重新排序表中记录的物理存储方式。因此表只能有一个聚集索引。聚集索引的叶节点包含数据页面。非聚簇索引是一种特殊类型的索引，其中索引的逻辑顺序与磁盘上行的物理存储顺序不匹配。非聚集索引的叶节点不包含数据页面。相反，叶节点包含索引行。

我在SO中发现的是，聚集索引和非聚集索引之间有什么区别？

有人能用简单的英语来解释吗？

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

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

如果要取回所有列，通常从聚簇索引读取速度更快。你不必先到索引，然后到表格。

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

聚集索引意味着您要让数据库存储实际上在磁盘上彼此接近的近值。这具有快速扫描/检索落入某些聚集索引值范围内的记录的好处。

例如，您有两个表，客户和订单：

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

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

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

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

关于多个指标。每个表只能有一个聚簇索引，因为这定义了数据的物理排列方式。如果你想比喻一下，想象一个有很多桌子的大房间。您可以将这些表格形成几行或将它们组合在一起形成一个大型会议桌，但不能同时进行。一个表可以有其他索引，然后它们将指向聚集索引中的条目，这些条目最终将说明在哪里查找实际数据。

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

在这里输入图像描述

（图片来源）

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

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

聚集索引是表的主要副本。

非聚集索引也可以通过使用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 - 用下面的箭头突出显示）几乎位于最后一个物理页面上。

在这里输入图像描述