测试索引对数据物理存储顺序的影响

 昨天去参加了微软BI开拓者的一个培训，对索引的理解又加深了一层，受益匪浅，呵呵~！~！

数据并不总是按照聚集索引来排序的。

 

--  =============================================
--  测试索引对数据物理存储顺序的影响
--  作者：dobear
--  环境：SQL2005
--  日期：2008-03-31
--  =============================================
use  Testdb     -- 选择数据库，请使用服务器上已有的数据库

if   object_id (N ' tb ' ,  ' U ' )  is   not   null
     drop   table  tb

go
--  1 创建测试表，添加测试数据
create   table  tb
(
    id  int   primary   key ,
    name  nvarchar ( 32 )
)

insert  tb  select   1 ,  ' dobear '
insert  tb  select   3 ,  ' fcuandy '
insert  tb  select   2 ,  ' dawugui '

insert  tb  select   9 ,  ' haiwer '
insert  tb  select   7 ,  ' hxgh '
insert  tb  select   8 ,  ' happyflystone '


-- select * from tb


--  2 查看数据的物理存储顺序
go
--  2.1 找到存储数据的物理文件、页
declare   @extentinfo   table ( file_id   int , page_id  int , pg_alloc  int , ext_size  int
                            ,  object_id   bigint , index_id  int , partition_number  int
                            , partition_id  bigint , iam_chain_type  nvarchar ( 255 ), pfs_bytes  bigint )
declare   @sql   nvarchar ( max )
set   @sql = ' DBCC EXTENTINFO( '   +   db_name ()  +   ' , tb) '
insert   @extentinfo   exec ( @sql )

-- select * from @extentinfo
--  2.2 根据文件及页信息，查看数据的物理存储情况
set   @sql = ''
select   @sql = @sql   +   ' DBCC PAGE( '   +   db_name ()  +   ' ,  '   +   rtrim ( file_id )  +   ' ,  '   +   rtrim (page_id)  +   ' , 1)  '   from   @extentinfo

DBCC  TRACEON( 3604 )
exec ( @sql )


print   ' ===================================== '
print   '            华丽的分隔线 1              '
print   ' ===================================== '


--  3 重建索引
go
-- 找到聚集索引(一般主键上会默认创建一个聚集索引)，然后重建
declare   @sql   nvarchar ( max ),  @index  sysname
select   @index = name  from  sys.indexes  where   object_id = object_id (N ' tb ' )  and  type = 1
set   @sql = ' alter index  '   +   @index   +   '  on tb rebuild '  
exec ( @sql )

--  4 重新查看数据的物理存储顺序
go
--  4.1 找到存储数据的物理文件、页
declare   @extentinfo   table ( file_id   int , page_id  int , pg_alloc  int , ext_size  int
                            ,  object_id   bigint , index_id  int , partition_number  int
                            , partition_id  bigint , iam_chain_type  nvarchar ( 255 ), pfs_bytes  bigint )
declare   @sql   nvarchar ( max )
set   @sql = ' DBCC EXTENTINFO( '   +   db_name ()  +   ' , tb) '
insert   @extentinfo   exec ( @sql )

-- select * from @extentinfo
--  4.2 根据文件及页信息，查看数据的物理存储情况
set   @sql = ''
select   @sql = @sql   +   ' DBCC PAGE( '   +   db_name ()  +   ' ,  '   +   rtrim ( file_id )  +   ' ,  '   +   rtrim (page_id)  +   ' , 1)  '   from   @extentinfo

DBCC  TRACEON( 3604 )
exec ( @sql )

--  可以看到，重建索引后，数据从第109页移动到了第120页，并且排列顺序也发生了改变(观察Row - Offset)，现在是完全按照索引来排序的。
--  狼老大的讲解完全正确，OVER。


print   ' ===================================== '
print   '            华丽的分隔线 2              '
print   ' ===================================== '


--  5 加入新数据

insert  tb  select   5 ,  ' hellowork '
insert  tb  select   6 ,  ' paoluo '
insert  tb  select   4 ,  ' libin '

--  6 再次查看数据的物理存储顺序
go
--  6.1 找到存储数据的物理文件、页
declare   @extentinfo   table ( file_id   int , page_id  int , pg_alloc  int , ext_size  int
                            ,  object_id   bigint , index_id  int , partition_number  int
                            , partition_id  bigint , iam_chain_type  nvarchar ( 255 ), pfs_bytes  bigint )
declare   @sql   nvarchar ( max )
set   @sql = ' DBCC EXTENTINFO( '   +   db_name ()  +   ' , tb) '
insert   @extentinfo   exec ( @sql )

-- select * from @extentinfo
--  6.2 根据文件及页信息，查看数据的物理存储情况
set   @sql = ''
select   @sql = @sql   +   ' DBCC PAGE( '   +   db_name ()  +   ' ,  '   +   rtrim ( file_id )  +   ' ,  '   +   rtrim (page_id)  +   ' , 1)  '   from   @extentinfo

DBCC  TRACEON( 3604 )
exec ( @sql )

输出的结果（部分）： 

/**/ /*
PAGE: (1:174)
Row - Offset                         
5 (0x5) - 181 (0xb5)                 
4 (0x4) - 231 (0xe7)                 
3 (0x3) - 208 (0xd0)                 
2 (0x2) - 123 (0x7b)                 
1 (0x1) - 152 (0x98)                 
0 (0x0) - 96 (0x60)   

=====================================
           华丽的分隔线 1             
=====================================

PAGE: (1:77)
Row - Offset                         
5 (0x5) - 245 (0xf5)                 
4 (0x4) - 204 (0xcc)                 
3 (0x3) - 181 (0xb5)                 
2 (0x2) - 152 (0x98)                 
1 (0x1) - 123 (0x7b)                 
0 (0x0) - 96 (0x60)              


=====================================
           华丽的分隔线 2             
=====================================

PAGE: (1:77)
Row - Offset                         
8 (0x8) - 245 (0xf5)                 
7 (0x7) - 204 (0xcc)                 
6 (0x6) - 181 (0xb5)                 
5 (0x5) - 305 (0x131)                
4 (0x4) - 272 (0x110)                
3 (0x3) - 332 (0x14c)                
2 (0x2) - 152 (0x98)                 
1 (0x1) - 123 (0x7b)                 
0 (0x0) - 96 (0x60)        
*/

 

在生成的结果中，通过对Row - Offset的观察可以发现：

表建立后，第一次存储的６条数据是按插入的先后次序来存储的；

重建聚集索引之后，所有的数据按照聚集索引的顺序来存储，并且数据从第174页转移到了第77页；

再次插入３条数据，这３条数据存储在了页的尾部(观察Row为3,4,5的行对应的Offset)，也没有按照聚集索引的顺序来存储。

结论：只有在重建聚集索引之后，数据才会按照聚集索引的顺序来存储。