如果表数据具有聚簇索引，那么它是如何存储的 [英] How are the table data stored when it has a clustered index

问题描述

我发现了很多帖子，其开头就像很多时候我遇到过人们说群集索引根据群集索引关键字对表格内的数据进行物理排序。这不是真的！然后这些帖子继续描述它是如何实际存储的，通过链表等等。例如，这个发布表示

I have found umpteen posts which begin like Quite a lot of time I have come across people saying "Clustered Index Physically sorts the data inside the table based on the Clustered Index Keys". It's not true! Then such posts go on to describe how it is actually stored, via linked lists or whatever. For example, this post says that

每个Index行包含一个Key值和一个指向B-中
中级页面的指针树，或索引的叶级别
中的数据行。索引每个级别的页面链接在
双向链表中。数据链中的页面及其中的行
按Clustered Index键的值排序。

Each Index row contains a Key value and a pointer to either an Intermediate level page in the B-tree, or a Data row in the Leaf level of the Index. The Pages in each level of the Index are linked in a Doubly-linked list. The Pages in the Data chain and the rows in them are ordered on the value of the Clustered Index key.

带我回答我的问题，数据页是存储表数据的地方，对吧？因此，如果对它们进行排序并且其中的数据也根据索引列值进行排序，那么为什么说聚簇索引将表数据按排序顺序保存是错误的？这是来自Kalen Delaney的书中的图片，其中显示带有CI的表格中的叶子页面都根据CI值进行排序：

That brings me to my question, the data pages are the place where the table data are stored, right? So if they are sorted and the data within them also are sorted according to the indexed column value, why is it wrong to say that the clustered index keeps the table data in sorted order? Here is a pic from Kalen Delaney's book, which shows that the leaf pages in a table with CI are all sorted according to the CI value:

推荐答案

你对。

聚簇索引不根据聚簇索引键对表内的数据进行物理排序。如果是这种情况，那么插入到没有可用空间的大表中间将需要大量的IO来为新记录腾出空间。

Clustered indexes do not physically sort the data inside the table based on the Clustered Index Keys. If that was the case then the inserts into the middle of a large table with no free space would require huge amounts of IO to make room for the new record.

而是从文件中的任何位置分配新页面并链接到链接列表。

Instead a new page is allocated from anywhere in the file and linked into the linked list.

学位页面的物理顺序与逻辑顺序不同的是逻辑碎片的程度。重建或重新组织索引可以减少这种情况。

The degree to which the physical order of pages differs from the logical order is the extent of logical fragmentation. Rebuilding or reorganizing the index can reduce this.

这篇关于如果表数据具有聚簇索引，那么它是如何存储的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！