聚集索引(Clustered Index)与非聚集索引(Non-clustered Index)
聚集索引(Clustered Index) - storing all row data within the index)
Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.
Since the physical records are in this sort order on disk, the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required. The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them. Some databases separate the data and index blocks into separate files, others put two completely different data blocks within the same physical file(s).
非聚集索引(Non-clustered Index)- storing only references to the data within the index
The data is present in arbitrary order, but the logical ordering is specified by the index. The data rows may be spread throughout the table regardless of the value of the indexed column or expression. The non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the record (page and the row number in the data page in page-organized engines; row offset in file-organized engines).
In a non-clustered index,
- The physical order of the rows is not the same as the index order.
- The indexed columns are typically non-primary key columns used in JOIN, WHERE, and ORDER BY clauses.
There can be more than one non-clustered index on a database table.
索引数据的存储方式 - 聚集索引(聚簇索引,Clustered Index)
聚集索引(clustered index),也称为聚簇索引。
聚簇索引并不是一种单独的索引类型,而是一种索引数据的存储方式。
当表有聚簇索引时,它的行数据实际上存储在索引的叶子页(leaf page)中。因为无法同时把数据行存放在两个不同的地方,所以一张表只能有一个聚簇索引。
...