聚簇索引和非聚簇索引实际上是什么意思?

遇到的问题:

我对DB的接触有限,只使用DB作为应用程序程序员。 我想了解Non clustered indexesNon clustered indexes 我用谷歌搜索,发现的是:

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

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

有人可以用简单的英语解释吗?

解决方案:

解决方案一

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

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

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

如果需要重新排列数据,则写入具有聚集索引的表的速度可能会变慢。

解决方案二

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

例如,您有两个表Customer和Order:

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

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

如果希望快速检索一个特定客户的所有订单,则可能希望在“订单”表的“客户ID”列上创建聚簇索引。 这样,具有相同CustomerID的记录将在物理上彼此靠近存储在磁盘上(成簇),从而加快了检索速度。

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

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

解决方案三

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

在此处输入图片说明

图片来源

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

  1. 聚集索引叶子页上的行始终为表中的每个(非稀疏)列包含某些内容 (值或指向实际值的指针)。
  2. 聚集索引是表的主副本。

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

在此处输入图片说明

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

跑步后

 ALTER INDEX ix ON T REBUILD; 

我得到以下

在此处输入图片说明

如果表没有聚簇索引,则称为堆。

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

对于后一种情况,如果非聚簇索引已经自然地包括CI键列(作为NCI键列或INCLUDE -d列),则不添加任何内容。 否则,缺少的CI键列将被静默添加到NCI中。

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

聚簇索引会为具有重复现有行的键值的任何行添加一个uniquifier 这只是一个递增的整数。

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

聚类命名法与非聚类命名法也用于列存储索引。 本文对SQL Server列存储的增强状态

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

阅读 608 次发布于 2019年12月27日
推荐阅读
为什么处理排序数组要比处理未排序数组快?

这是一段C ++代码,显示了一些非常特殊的行为。 出于某些奇怪的原因,奇迹般地对数据进行排序使代码快了将近六倍: #include #include #include int main() { // Generate data const unsigned arraySize = 32768; int da...

2019-12-20 阅读 10

如何撤消Git中的最新本地提交?

我不小心将错误的文件提交给Git ,但是我还没有将提交推送到服务器。 如何撤消本地存储库中的那些提交?

2019-12-20 阅读 13

如何在本地和远程删除Git分支?

我想在本地和远程删除分支。 尝试删除远程分支失败 $ git branch -d remotes/origin/bugfix error: branch 'remotes/origin/bugfix' not found. $ git branch -d origin/bugfix error: branch 'origin/bugfix' not found. $ git branch ...

2019-12-20 阅读 9

'git pull'和'git fetch'有什么区别?

主持人注意:鉴于此问题已经发布了67个答案 (其中一些已删除),请在发布另一个问题之前考虑您是否正在贡献新内容 。 git pull和git fetch什么区别?

2019-12-20 阅读 8

什么是正确的JSON内容类型?

我一直在弄乱JSON一段时间,只是将其作为文本推出,并没有伤害任何人(据我所知),但是我想正确地做事。 我已经看到许多所谓的JSON内容类型的“标准”: application/json application/x-javascript text/javascript text/x-javascript text/x-json 但是哪一个是正确的,还是最好的? 我发现在它们之间存在安全性和浏览...

2019-12-20 阅读 10

目录