什么是聚簇索引表? [英] What is a Clustered Index table?

问题描述

我可能是错的，但对于这些是什么，互联网似乎似乎有不同的看法. SQL Server，MySQL，DB2和PostgreSQL对这些表显示不同的定义.

I may be wrong, but it seems to be different opinions in the interwebs about what these are. SQL Server, MySQL, DB2, and PostgreSQL show different definitions for these tables.

从不同的供应商那里读了很多书(数据库手册，用户帖子等)后，我能够区分出三种感兴趣的表(对此问题有很多很多不感兴趣的表).请忍受我

After reading a ton from different vendors (database manuals, user posts, etc.) I was able to distinguish three types of tables of interest (there are many, many more types of no interest for this question). Please bear with me:

1. 堆表:

• 所有行都存储在堆表中(可能是无序的).
• 每行都有一个内部ROWID对其进行标识.
• 索引是可选的.如果添加了它们，它们将包括索引列作为索引键，再加上ROWID(以最终访问堆中的实际行).
• 注意:这种情况对这个问题没有意义，但是我在这里添加它是为了与下面的第三种情况有所不同.

• All rows are stored (probably unordered) in the heap table.
• Each row has an internal ROWID that identifies it.
• Indexes are optional. If added, they include the indexed columns as the index key, plus the ROWID (to eventually access the real rows in the heap).
• Note: this case is of no interest for this question, but I added it here to make a difference with the third case below.

纯索引表:<-这是一个聚集索引表吗?

Pure Index Table: <-- Is this a Clustered Index Table?

• 有一个主要索引，其中包含键列以及其中的非键列.所有数据都存储在索引中.
• 数据遵循主索引顺序，因此按定义按主索引排序.
• 不需要堆表来存储行.所有数据已经​​在索引中.因为没有堆表，所以没有ROWID.
默认情况下，
• SQL Server 表(通常)属于此类别.
• MySQL InnoDB表似乎也属于此类，因为它们似乎根本没有堆表.

• There's one main index that includes the key columns, as well as the non-key columns in it. All the data is stored in the index.
• The data follows the main index order, so it's by definition sorted by the main index.
• There's no need for a heap table to store the rows. All data is already in the index. There's no ROWID whatsoever, since there's no heap table.
• SQL Server tables (typically) fall by default in this category.
• MySQL InnoDB tables seem to also fall in this category since they don't seem to have a heap table at all.

索引+已排序堆表:<-这是一个聚集索引表吗?

Index + Sorted Heap Table: <-- Is this a Clustered Index Table?

• 有一个主要的聚集索引".
• 有一个堆表，其中的行按聚簇索引定义的顺序存储.
• 堆表中的每一行都有一个ROWID.
• 聚集索引不包括非键列，而是用于访问堆表中实际行的ROWID.
• DB2 似乎能够聚集"表.
• PostgreSQL 似乎也将这些表称为集群 ing 索引"表.

• There's one main "clustered index".
• There's a heap table where the rows are stored in the order defined by the clustered index.
• Each row in the heap table has a ROWID.
• The clustered index does not include non-key columns, but a ROWID to access the real row in the heap table.
• DB2 seems to be able to "Cluster" tables.
• PostgreSQL seems to also call these tables as "Clustering Index" tables.

现在，这些#2或#3中的哪些是聚集索引表"?谁在说实话，谁在说谎? :D

Now, which ones of these #2 or #3 is a "Clustered Index Table"? Who's telling the truth and who's lying? :D

换句话说，聚集索引表"一词是每个供应商自由定义的商业术语，还是根据某种官方数据库理论有一个官方唯一的定义?

In other words, is the term "Clustered Index Table" a commercial term that each vendor freely defines as he/she pleases, or is there an official unique definition according to some official database theory?

推荐答案

据我所知，聚集索引"是一个索引，其中索引的叶节点是数据页.这与非聚集索引不同，非聚集索引的叶子节点是对数据页上存储的行的引用.

As far as I know, a "clustered index" is an index where the leaf nodes of the index are the data pages. This is different from a non-clustered index where the leaf nodes are references to rows stored on the data pages.

一个表最多可以有一个聚集索引.在具有聚集索引的表中，数据通过索引键排序.

A table can have at most one clustered index. In a table with a clustered index, the data is sorted on by the index keys.

Postgres不支持聚集索引.它确实有一个名为"cluster" 的表优化基于索引的数据.但是，由于不保留此顺序，因此它与聚簇索引不同.

Postgres does not support clustered indexes. It does have a table optimization called "cluster" that sorts the data based on an index. However, this ordering is not maintained, so it is not the same as a clustered index.

这篇关于什么是聚簇索引表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！