logo标签列表

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

sql-serverindexingclustered-indexnon-clustered-index
307

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

8
每个表只能有一个聚集索引，但还有许多其他区别... - Tom Robinson
5
聚集索引实际上描述记录在磁盘上物理存储的顺序，这就是为什么只能有一个的原因。非聚集索引定义的是逻辑顺序，而不是磁盘上的物理顺序。 - Josh
1
集群化基本上意味着数据在表中以物理顺序排列。这就是为什么每个表只能有一个的原因。非集群化意味着它只是“逻辑顺序”。 - Biri
2
@biri 什么是“逻辑”顺序？非聚集索引在物理上按顺序存储索引键，并将指针存储到表中，即聚集索引键。 - Stephanie Page
@Stephanie Page：从表的角度来看是逻辑上的。当然，非聚集索引在索引本身中是按物理顺序排序的。 - Biri
显示剩余2条评论
13个回答
291

聚集索引

  • 每个表只能有一个
  • 读取速度比非聚集索引更快，因为数据按照索引顺序物理存储

非聚集索引

  • 每个表可以使用多次
  • 对于插入和更新操作比聚集索引更快

当使用带有索引的字段选择数据时，两种类型的索引都会提高性能，但会减慢更新和插入操作。

由于插入和更新较慢，应将聚集索引设置在通常是增量的字段上，例如 Id 或 Timestamp。

SQL Server 通常仅在其可选性超过 95% 时使用索引。

9
还需要考虑存储空间问题。当向没有聚集索引的表中插入行时，行将依次存储在页面上，更新某一行可能导致该行被移动到表的末尾，留下空白空间并使表和索引碎片化。 - anon
4
不需要关心x是什么，你只需知道对于拥有数百万用户的应用程序而言，x是非常重要的。 - Pacerier
15
这纯粹是教条主义。它并不是说“由于数据按顺序存储，所以阅读更快”。之所以阅读更快，是因为您避免了索引读取和表读取的步骤。如果意义明确，那么范围扫描会更快，因为数据按顺序存储。也就是说，聚簇因子是完美的。 - Stephanie Page
6
“95%的记录需要是唯一的”这个观点是错误的。比如，你有一个包含1,000,000行数据的表格，并在其中一个包含500,000键值的列上创建了索引。虽然0%的键值是唯一的，但每个键值都可以返回100万行数据中的2行。因此，即使没有任何记录是唯一的，这个索引也是非常有用的。 - Stephanie Page
2
“数据按索引顺序物理存储”是什么意思？在某个层面上，这是显而易见的，因为数据页和索引叶页是相同的——因此，一个的排序描述了另一个的排序。然而，这不一定是按照特定的顺序排列，例如索引键的顺序。https://dev59.com/yXM_5IYBdhLWcg3wvFzJ#24470091 - Martin Smith
显示剩余4条评论
87

聚集索引会按照物理顺序在磁盘上排列数据。这意味着索引不需要额外的数据，但是只能有一个聚集索引（显然）。使用聚集索引访问数据是最快的。

所有其他索引必须是非聚集索引。非聚集索引将索引列的数据副本保持在一起，并带有指向实际数据行的指针（如果有聚集索引，则为指向聚集索引的指针）。这意味着通过非聚集索引访问数据必须经过额外的间接层。但是，如果您仅选择可在索引列中使用的数据，则可以直接从复制的索引数据中获取数据（这就是为什么建议仅选择所需列并不使用 * 的原因）。

3
然而，如果你只选择已索引列中可用的数据，你可以直接从复制的索引数据中获取数据。是优先选择聚集索引方法的一个重要例外。我想在这种情况下，你基本上拥有一个聚集索引，但是查询的表中数据量较少，因此可能可以更快地从磁盘读取数据。 - satnhak
38

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

非聚集索引是单独存储的，你可以拥有任意数量的非聚集索引。

最佳选择是将聚集索引设置在最常用的唯一列上，通常是主键。除非出现了非常令人信服的理由（我想不到任何一个，但是嘿，可能存在），否则您应该在表中始终选择良好的聚集索引。

3
您能详细说明“我们应该始终在我们的表中拥有一个聚集索引”吗？如果没有详细说明，这个陈述就是错误的，因为它包含了“始终”这个词。 - Pacerier
1
你说得对，Pacerier，人们不应该轻易使用绝对语句。虽然我不知道有哪种情况下你不需要一个精心选择的聚集索引，但这种情况可能存在，所以我已经将我的答案改为更通用的版本。 - Santiago Cepas
30

聚簇索引

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

非聚簇索引

  1. 一个表最多可以有249个非聚簇索引（在SQL版本2005之前），后续版本支持高达999个非聚簇索引。
  2. 通常在任何键上建立。
  3. 非聚簇索引的叶节点不包含数据页，而是包含索引行。
26

聚集索引

  • 一张表只能有一个聚集索引。
  • 按照排序顺序物理存储记录。
  • 数据检索速度比非聚集索引快。
  • 不需要额外的空间来存储逻辑结构。

非聚集索引

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

除了这些区别外，您还需要知道，当表是非聚集的（即表没有聚集索引）时，数据文件是无序的，并使用堆数据结构作为数据结构。

13

优点：

对于范围查询（如 select * from my_table where my_key between @min and @max），聚集索引效果非常好。

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

缺点：

如果新键不是按顺序的，聚集索引可能会减慢插入速度，因为记录的物理布局必须随着记录的插入而修改。

12

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

Unclustered意味着它只是“逻辑顺序”。

8
一个索引数据库有两个部分：一组物理记录，按任意顺序排列，以及一组指示应按某个标准排序读取记录的索引。如果物理排列和索引之间没有关联，那么按顺序读取所有记录可能需要进行大量独立的单记录读取操作。因为数据库可以在较短的时间内读取数十条连续记录，而要读取两个非连续记录可能需要更长的时间，所以如果按照索引连续存储也是连续的话，性能可以得到提高。指定索引为聚集索引将会导致数据库做出一些努力（不同的数据库在这方面有所不同），以使在索引中连续的记录组也在磁盘上连续存储。
例如，如果从一个空的非聚集数据库开始，并以随机顺序添加10,000条记录，则记录可能按添加的顺序添加到末尾。按索引顺序读取数据库将需要10,000次单记录读取。但是，如果使用聚集数据库，系统在添加每个记录时可能会检查前一个记录是否单独存储；如果发现是这种情况，它可能会将该记录与新记录一起写入数据库末尾。然后，它可以查看移动记录曾经驻留的插槽之前的物理记录，并查看其后面是否存储了单独的记录。如果发现是这种情况，它可以将该记录移动到该位置。使用这种方法会导致许多记录成对地分组在一起，从而可能将连续读取速度几乎提高一倍。
实际上，聚集数据库使用比此更复杂的算法。但是，需要注意的关键事项是，在更新数据库所需的时间和顺序读取所需的时间之间存在权衡。维护聚集数据库将显着增加添加、删除或更新记录所需的工作量，以任何影响排序顺序的方式。如果数据库将经常按顺序读取而不是经常更新，则聚集可以大获全胜。如果它经常更新但很少按顺序读取，则聚集可能会大幅降低性能，特别是如果项目添加到数据库中的顺序与其相对于聚集索引的排序顺序无关。
8

聚集索引实际上描述了记录在磁盘上物理存储的顺序，这就是为什么只能有一个的原因。

非聚集索引定义了一个逻辑顺序，它与在磁盘上的物理顺序不匹配。

6

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

聚集索引的主要优势在于，当您的查询（查找）在索引中定位到数据时，则无需进行其他IO操作即可检索该数据。

在经常更新的表中维护聚集索引的开销可能会导致性能下降，因此创建非聚集索引可能更为可取。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接