SQL server 容易让人误解的问题之 聚集表的物理顺序问题

      对于MS SQL server 数据库，有几个容易让人产生误解的问题，对于这几个问题，即使很多 SQL server DBA 都有错误认识或者认识不充分，所以我想撰文几篇，把这些容易理解错误的问题前前后后深入阐述一下,也希望纠正一下网上对这几个问题的讹传(我也可能有认识不对的地方，欢迎批评指正)。

      第一个问题：聚集表的物理顺序问题。这个问题很有迷惑性，因为很多教科书在讲到聚集索引的时候都会出现“聚集索引是按照聚集键的排序顺序物理地存储数据” 类似的说法，因此我们很容易产生以下几种误解：

      误解一：“按顺序物理地存储”就是磁盘本身；

      误解二：聚集表的页在物理上是顺序的；

      误解三：聚集表的页在物理上是顺序的，并且是连续的。

 

     下面我们分别说明上面三种观点确实是误解。

     对于误解一，我们必须要了解数据行如何被存储在数据页上。

              

上图是我从MSDN上截来的，从上图可以看到，在每个数据页的末尾，都有一个“行偏移量”的数组，这个数组记录了每一个数据行的开头在页面中的起始位置，即每行数据开头应该从页头偏移多少个字节。我将上图改了一下，可能更便于正确理解：

           

我改编的这个图中表示，从这个数据页头偏移96个字节即是第一行数据的开始位置，偏移200字节即是第二行数据的开始位置，偏移300个字节即是第三行数据的开始位置。

 

下面我们举个实际的例子来说明聚集表的数据在一个数据页上可能的样子。

CREATE TABLE test
(
   RowId   int not null primary key ,
   Column1 char(100)
)

 
INSERT INTO test(RowId,Column1)
Select 1, ' '
Union
Select 2,' '
Union
Select 10,' '

对于上表，数据第一次插入时，这三行数据在数据页上的表现如下：

       

而如若我再往里面插入如下数据：

INSERT INTO test(RowId,Column1)
Select 7, ' '

因为这个表的RowId 列有聚集索引（primary key 默认创建聚集索引），而数字7大约2，且小于10，那么SQL server 在执行INSERT 操作时，是不是会把RowId = 10 这行数据往下挪呢？显然SQL server 不会这么笨，而只会将RowId = 7这行数据数据附加到RowId = 10 这行数据的后面，然后再修改行偏移量数组，示意图如下：

          

      现在我们应该可以明白：一个聚集索引表数据页上数据行的物理顺序，仅依靠行偏移列表来决定，并不取决于在磁盘上的物理位置。

 

     对于误解二和误解三可以放在一起论述，在论述之前，我们需要先了解一下SQL server 存储引擎中页跟区的概念。
         1. SQL server 中数据存储的基本单位是页。
         2. 区是八个逻辑上连续的页的集合，用来有效的管理页，这也说明，所有的数据页一定属于某个区。
         3. 区分为混合区和统一区。混合区中的页可以被分配给多个数据库对象；统一区中的页一定是被分配给了某一个数据库对象。
         4. SQL server 在为某个数据库对象申请空间时，需要使用GAM,SGAM,PFS 系统页的信息，同时在空间被分配后，也会维护好GAM,SGAM,PFS系统页的息                      。

         上面我简单列出了几个要点，更多详细信息大家可以参考下面的链接信息：
         http://msdn.microsoft.com/zh-cn/library/cc280360(v=sql.100).aspx 

         然后我将列举具体的情况来证明二跟三确实是误解：
         1. 当我们新建一个聚集表，并且往表里插数据，当这个表所占用空间不够8个数据页时，SQL server存储引擎都将从混合区寻找空闲的页面分配给表。而在这个阶段内，很容易出现一个混合区被同时分配给多个数据库对象（最多可达8个数据库对象）。那么，如果我们的聚集表需要再次申请磁盘空间，就很可能在起初的混合区内分配到不连续的页(相对于已分配给这个聚集表的页来说)，或者要从另外一个混合区查找空闲页面，这样，是无论如何也保证不了“聚集表的页在物理上是顺序的”，更保证不了“页是连续的”。

         2. 如果一个聚集表满8个页，那么，后续所有的空间申请都将分配完整的统一区，这后续的统一区是否可以顺序，或者连续呢？ 当然不行，当一个申请空间的动作发出后，存储引擎都会从表所在数据文件的第一个GAM去遍历查找空闲的统一区，如果某个统一区在之前已经分配给某个其它的数据库对象，但当前这个区已经被释放，那么，SQL server 就会将这个空闲的区分配给我们的聚集表，这样也自然保证不了页在物理上的“顺序和连续”；另外，即使SQL server每次都分配从来没有分配给任何数据库对象的区给我们的聚集表，也没有办法保证页在物理上的“顺序和连续”。

         3. 如果聚集表出现分页的情况，那么新申请页的页面链的“上一页”会指向被分割的数据页，“下一页”会指向被分割页在分割前指向的下一页。显然，这种情况也没有办法保证页在物理上的“顺序和连续”，而且它是索引碎片情况中的一种。

 

结论：对于聚集索引表数据行的物理顺序问题，在页与页的角度来看，唯一能指明聚集索引表数据页顺序的是数据页上的页面链表。因为页面链表清楚的指明了本页的上一页，及下一页的页面号分别是啥，而页面号就决定了上一页及下一页的物理位置；如果将眼光缩小到一个数据页的范围内，决定数据行物理位置的因素只有页脚的行偏移量数数组。