我对DB的接触有限,只是作为应用程序程序员使用过DB。我想知道关于聚集和非聚集索引。 我在谷歌上搜索了一下,我发现:

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

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

有人能用通俗易懂的语言解释一下吗?


当前回答

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

Non Clustered Index: Actual data of non clustered index is not directly found at leaf node, instead it has to take an additional step to find because it has only values of row locators pointing towards actual data. Non clustered Index can't be sorted as clustered index. There can be multiple non clustered indexes per table, actually it depends on the sql server version we are using. Basically Sql server 2005 allows 249 Non Clustered Indexes and for above versions like 2008, 2016 it allows 999 Non Clustered Indexes per table.

其他回答

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

Non-Clustered Index - A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. This is similar to a textbook where the book content is located in one place and the index is located in another. This allows for more than one non-clustered index per table.It is important to mention here that inside the table the data will be sorted by a clustered index. However, inside the non-clustered index data is stored in the specified order. The index contains column values on which the index is created and the address of the record that the column value belongs to.When a query is issued against a column on which the index is created, the database will first go to the index and look for the address of the corresponding row in the table. It will then go to that row address and fetch other column values. It is due to this additional step that non-clustered indexes are slower than clustered indexes

聚类索引和非聚类索引的区别

每个表只能有一个聚集索引。但是,你可以 在一个表上创建多个非聚集索引。 聚集索引只对表进行排序。因此,他们不消费 额外的存储。非聚集索引存储在单独的位置 从实际表中占用更多的存储空间。 聚集索引比非聚集索引快,因为它们 不要涉及任何额外的查找步骤。

有关更多信息,请参阅本文。

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

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

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

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

下面是聚类索引和非聚类索引的一些特征:

聚集索引

聚集索引是唯一标识SQL表中的行的索引。 每个表只能有一个聚集索引。 可以创建包含多个列的聚集索引。例如:create Index index_name(col1, col2, col.....)。 默认情况下,具有主键的列已经具有聚集索引。

非聚簇索引

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

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

我们也可以称之为聚类索引,它是一个或多个属性上的索引,这样所有具有该索引的搜索键的固定值的元组都出现在能够容纳它们的大致尽可能少的块上。

为了理解定义,让我们看一下教科书提供的例子15.10:

A relation R(a,b) that is sorted on attribute a and stored in that order, packed into blocks, is surely clusterd. An index on a is a clustering index, since for a given a-value a1, all the tuples with that value for a are consecutive. They thus appear packed into blocks, execept possibly for the first and last blocks that contain a-value a1, as suggested in Fig.15.14. However, an index on b is unlikely to be clustering, since the tuples with a fixed b-value will be spread all over the file unless the values of a and b are very closely correlated.

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

A related concept is clustered relation. A relation is "clustered" if its tuples are packed into roughly as few blocks as can possibly hold those tuples. In other words, from a disk block perspective, if it contains tuples from different relations, then those relations cannot be clustered (i.e., there is a more packed way to store such relation by swapping the tuples of that relation from other disk blocks with the tuples the doesn't belong to the relation in the current disk block). Clearly, R(a,b) in example above is clustered.

为了将两个概念连接在一起,聚类关系可以具有聚类索引和非聚类索引。但是,对于非聚类关系,除非索引构建在关系的主键之上,否则不可能实现聚类索引。

“集群”作为一个词在数据库存储端的所有抽象级别(三个抽象级别:元组、块、文件)上被大量发送。一个叫做“集群文件”的概念,它描述了一个文件(一组块(一个或多个磁盘块)的抽象)是否包含来自一个关系或不同关系的元组。它与集群索引概念无关,因为它是在文件级别上。

然而,一些教材喜欢根据聚类文件定义定义聚类索引。这两种类型的定义在集群关系级别上是相同的,无论它们是根据数据磁盘块还是文件来定义集群关系。从这段的链接中,

在以下情况下,文件属性A上的索引称为聚类索引:属性值A = A的所有元组按顺序(=连续)存储在数据文件中

连续存储元组就相当于说“元组被打包到尽可能少的块中,以容纳这些元组”(一个是文件,另一个是磁盘)。这是因为连续存储元组是实现“将这些元组打包到尽可能少的块中”的方法。

聚集索引

聚类索引决定了表中DATA的物理顺序。因此,一个表只有一个聚集索引(主键/组合键)。

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

非聚集索引

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

“生物书”在开头有一个单独的索引指向章节的位置,在“结尾”有另一个索引指向常用单词的位置