聚集索引和非聚集索引有什么区别?

聚集索引

• 每张桌子只有一个
• 由于数据是按索引顺序物理存储的,因此读取速度比非群集快

非簇集索引

• 每张桌子可以用很多次
• 插入和更新操作比聚集索引更快

这两种类型的索引在使用索引的字段选择数据时都会提高性能,但会减慢更新和插入操作的速度。

由于插入和更新的速度较慢,应该在通常是递增的IE ID或时间戳的字段上设置聚集索引。

如果选择性高于95%,SQL Server通常只使用索引。

聚集索引对磁盘上的数据进行物理排序。这意味着索引不需要额外的数据,但只能有一个聚集索引(显然)。使用聚集索引访问数据是最快的。

所有其他索引必须是非聚集索引。非聚集索引具有索引列中的重复数据,这些数据与指向实际数据行的指针(如果存在聚集索引,则指向该索引的指针)保持有序。这意味着通过非聚集索引访问数据必须经过额外的间接层。但是,如果只选择索引列中可用的数据,则可以直接从重复的索引数据中获取数据(这就是为什么最好只选择所需的列而不使用*)

聚集索引物理存储在表中。这意味着它们是最快的,每个表只能有一个聚集索引。

非聚集索引单独存储,您可以拥有任意数量的索引。

最好的选择是在最常用的唯一列(通常是pk)上设置聚集索引。在您的表中应该总是有一个选择良好的聚集索引,除非有一个非常令人信服的原因——不能想到一个单一的聚集索引,但是,嘿,它可能在外面——因为没有这样做。

聚集索引

1. 一个表只能有一个聚集索引。
2. 通常在主键上生成。
3. 聚集索引的叶节点包含数据页。

非聚集索引

1. 一个表只能有249个非聚集索引(直到2005年的SQL版本更高版本支持高达999个非聚集索引)。
2. 通常在任何钥匙上制造。
3. 非聚集索引的叶节点不包含数据页。相反,叶节点包含索引行。

聚集索引

• 表中只能有一个聚集索引
• 对记录进行分类并按顺序进行物理存储
• 数据检索比非聚集索引快
• 不需要额外的空间来存储逻辑结构

非簇集索引

• 表中可以有任意数量的非聚集索引
• 不要影响物理顺序。为数据行创建逻辑顺序并使用指向物理数据文件的指针
• 数据插入/更新比聚集索引快
• 使用额外空间存储逻辑结构

除了这些差异之外,您还必须知道,当表是非聚集的(当表没有聚集索引时),数据文件是无序的,它使用堆数据结构作为数据结构。

集群基本上意味着数据在表中是按物理顺序排列的。这就是为什么每个表只能有一个。

未分类意味着它“只是”一个逻辑顺序。

赞成的意见:

聚集索引在范围内工作得很好(例如,从我的\表中选择*,其中我的\键介于@min和@max之间)

在某些情况下,如果使用orderby语句,DBMS将不必进行排序工作。

欺骗:

聚集索引可以减慢插入速度,因为如果新键不按顺序排列,则必须在放入记录时修改记录的物理布局。

聚集索引实际上描述了记录物理存储在磁盘上的顺序,因此您只能有一个记录。

非聚集索引定义的逻辑顺序与磁盘上的物理顺序不匹配。

聚集索引本质上是索引列中数据的排序副本。

聚集索引的主要优点是,当查询(seek)定位索引中的数据时,不需要额外的IO来检索该数据。

维护聚集索引的开销,特别是在频繁更新的表中,可能会导致性能低下,因此最好创建非聚集索引。

索引数据库由两部分组成:一组按任意顺序排列的物理记录,以及一组标识读取记录的顺序的索引,以产生按某种标准排序的结果。如果物理排列和索引之间没有关联,那么按顺序读取所有记录可能需要进行大量独立的单记录读取操作。由于一个数据库可以在比读取两个非连续记录所需的时间更短的时间内读取几十个连续记录,因此如果索引中连续的记录也连续存储在磁盘上,则性能可能会得到提高。指定索引是集群的将使数据库做出一些努力(不同的数据库的排列程度不同),以便索引中连续的记录组在磁盘上是连续的。

例如,如果从一个空的非集群数据库开始,随机添加10000条记录,那么这些记录很可能会按照添加的顺序添加到最后。按索引顺序读取数据库需要10000个一条记录读取。但是,如果要使用集群数据库,系统可能会在添加每个记录时检查以前的记录是否是单独存储的;如果发现是这样,则可能会在数据库末尾用新记录写入该记录。然后,它可以查看移动记录所在的槽之前的物理记录,并查看后面的记录是否是自己存储的。如果它发现是这样的话,它可以把记录移到那个位置。使用这种方法将导致许多记录成对分组在一起,从而可能使顺序读取速度增加一倍。

实际上,集群数据库使用的算法比这更复杂。不过,需要注意的一个关键问题是,在更新数据库所需的时间和按顺序读取数据库所需的时间之间存在权衡。维护集群数据库将显著增加以任何方式添加、删除或更新记录所需的工作量,从而影响排序顺序。如果按顺序读取数据库的频率要比更新数据库的频率高得多,那么集群将是一个巨大的胜利。如果它经常更新,但很少按顺序读取,那么集群可能是一个巨大的性能消耗,特别是当添加到数据库中的项目的顺序与集群索引的排序顺序无关时。

//从msdn复制,其他答案中没有明确提到非聚集索引的第二个点。

群集的

• 聚集索引对表或视图中的数据行进行排序和存储 基于他们的关键价值观。这些是包含在 索引定义。每个表只能有一个聚集索引, 因为数据行本身只能按一个顺序存储。
• 表中数据行按排序顺序存储的唯一时间是 当表包含聚集索引时。当一张桌子有一个 聚集索引,该表称为聚集表。如果一张桌子 没有聚集索引,其数据行存储在无序的 结构称为堆。

非聚集的

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

你可能已经从上面的文章中学习了理论部分:

-我们可以看到的聚集索引直接指向记录,即它的直接索引,因此搜索所需的时间更少。此外,它不会占用任何额外的内存/空间来存储索引。

-而在非聚集索引中,它间接指向聚集索引,然后它将访问实际的记录,由于它的间接性质,它将需要更多的时间来访问,而且它还需要自己的内存/空间来存储索引。