对于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 存储引擎中页跟区的概念。 上面我简单列出了几个要点,更多详细信息大家可以参考下面的链接信息: 然后我将列举具体的情况来证明二跟三确实是误解: 2. 如果一个聚集表满8个页,那么,后续所有的空间申请都将分配完整的统一区,这后续的统一区是否可以顺序,或者连续呢? 当然不行,当一个申请空间的动作发出后,存储引擎都会从表所在数据文件的第一个GAM去遍历查找空闲的统一区,如果某个统一区在之前已经分配给某个其它的数据库对象,但当前这个区已经被释放,那么,SQL server 就会将这个空闲的区分配给我们的聚集表,这样也自然保证不了页在物理上的“顺序和连续”;另外,即使SQL server每次都分配从来没有分配给任何数据库对象的区给我们的聚集表,也没有办法保证页在物理上的“顺序和连续”。 3. 如果聚集表出现分页的情况,那么新申请页的页面链的“上一页”会指向被分割的数据页,“下一页”会指向被分割页在分割前指向的下一页。显然,这种情况也没有办法保证页在物理上的“顺序和连续”,而且它是索引碎片情况中的一种。
结论:对于聚集索引表数据行的物理顺序问题,在页与页的角度来看,唯一能指明聚集索引表数据页顺序的是数据页上的页面链表。因为页面链表清楚的指明了本页的上一页,及下一页的页面号分别是啥,而页面号就决定了上一页及下一页的物理位置;如果将眼光缩小到一个数据页的范围内,决定数据行物理位置的因素只有页脚的行偏移量数数组。 |