存储结构与索引

一、SQL数据存储的基本介绍

       数据库中的数据存储涉及页（Page）和区（Extent)这两个概念了。SQL server中数据存储的基本单位是页。为数据库中的数据文件（.mdf或.ndf）分配的磁盘空间可以从逻辑上划分成页（从0到n连续编号），磁盘I/O操作在页级执行。也就是说，SQL Server读取或写入数据的最小单位是以8KB为单位的页。区是8个物理上连续的页的集合，用来有效地管理页。如果区内的8个页属于同一个表，则这种区称为统一区；如果区内的8个页分别属于至少两个不同的表，则这种区称为混合区。页在SQLScrver中，大小为8KB。这意味着SQL Server数据库中每MB有128页。每页开头是一个96字节的页头，用于存储有关页的系统信息。包括页码、页类型、页的可用空间，以及拥有该页的对象的分配单元ID。不同类型的数据，存储在不同类型的页面里。在数据页的存储结构里，每个页的前面96个字节是页头。SQL server通过这96个字节的页头和系统表，从逻辑层面上把表的存储结构管理起来。针对表的存储结构，SQLserver引入了一些概念：这些概念包括：object（对象）、partition（分区）、Hobt（堆或 B 树）、allocation_unit（分配单元）。

       表存储结构的关系如图所示、每张表会有一个对应的objectID，同时每张表拥有一个或者多个Partition（通常一个Partition对应一个索引）。每个Partition会有一个或者多个Heap or B一Tree（简称为Hobt）。Hobt的结构是预留的，通常可以认为，Partition与Hobt是一样的，PartitionID就是HobtID。每个Hobt会有至多三个分配单元用于存放数据，分别是data(数据)、LOB（大数据字段类型）、Row-Overflow(行溢出)。最频繁使用的分配单元是Data。如果有LOB数据或者长度超过8000字节的记录，则可能有另外的LOB分配单元和Row一Overflow分配单元：一个表可以有多个Partition，但是每个Partition以Hobt最多有三个分配单元，每个分配单元可以有许多页。

 

二、索引对数据存储的影响

对于每个分配单元内的数据页，根据表是否有索引，以及索引是聚焦索引或非聚焦索引，其组织方式常见有以下三种：

1、没有索引

在这种情况下，数据是按堆的结构存储，只有一个分区，在系统表里，对于这个分区下面的每个分配单元都有一个连接指向Index Allocation Map页（IAM，索引指引页）,在IAM页里，描述了区的信息。数据页之间没有任何关系，完全依赖IAM页组织起来。对于这种表的查询，数据库会先查询IAM页，任何根据其提供的信息，遍历所有区，将符合条件的页返回。其结构入下图所示：

 

PS:表、分区信息等可通过以下系统表查询

 

--查询表的信息
select *from sys.objects where name='test3'
--查询分区信息
select *from sys.partitions where object_id='1909581841'
--查询分配单元信息
select *from sys.allocation_units where container_id='72057594041335808'

 

 

2.有非聚集索引但没聚集索引

 这种情况下，数据依然是以堆结构存储，不过针对每个非聚集索引，都会有一个对应的partition。对于这个partition下面的每个分配单元,都有一个连接指向根页。数据页之间通过前后指针互相关联，在其结构底层，会有一个连接（文件号、页号、行号）指向真正的数据。其结构如图所示：

 

 

3、表有聚集索引

当表有索引时，其索引号为1，它有一个对应的partition，同样这个partition下的分配单元都会连接到一个根页，不过对于聚集索引来说，它在叶子节点上直接存储真正的数据，其结构如下：

 

 三、样例分析

创建3个样例表并插入相同的数据，其中test1无索引，test2只有非聚集索引，test3有聚集索引：

use TEST;

create table test1
(
     id int not null,
     name char(10) null,
     test varchar(max) null,
)


create table test2
(
     id int not null,
     name char(10) null,
     test varchar(max) null,
)
 CREATE NONCLUSTERED INDEX NCL_TEST_ID ON test2 (id)


create table test3
(
     id int not null primary key,
     name char(10) null,
     test varchar(max) null,
)

然后执行以下语句，查询分配单元情况：

select e.*from sys.objects as a
inner join
sys.partitions as b
on a.object_id=b.object_id
inner join
sys.allocation_units as c
on b.partition_id=c.container_id
inner join
sys.system_internals_allocation_units as e
on c.allocation_unit_id=e.allocation_unit_id
where a.name='test1'


select e.*from sys.objects as a
inner join
sys.partitions as b
on a.object_id=b.object_id
inner join
sys.allocation_units as c
on b.partition_id=c.container_id
inner join
sys.system_internals_allocation_units as e
on c.allocation_unit_id=e.allocation_unit_id
where a.name='test2'

select e.*from sys.objects as a
inner join
sys.partitions as b
on a.object_id=b.object_id
inner join
sys.allocation_units as c
on b.partition_id=c.container_id
inner join
sys.system_internals_allocation_units as e
on c.allocation_unit_id=e.allocation_unit_id
where a.name='test3'

得到结果如下图所示：

 

 

 

 

 

 从图中可以看到，它们都有data、lob这两种分配单元，无索引的跟只有聚集索引的都是具有相同数量的partition以及页，有非聚集索引的多了一个partition以及2页数据来记录。

PS:可通过以下语句查询更详细的partition信息：

dbcc ind('test','test1',1)
dbcc ind('test','test2',1)
dbcc ind('test','test3',1)

--dbcc page可查看详细页的信息

四、总结

1.无索引情况下数据以堆结构存储，开iam页来引导检索，对于大表来说，建议建立聚集索引使数据有序化，便于处理；

2.非聚集索引是使用额外的存储来增加检索效率，对最底层的数据结构没影响；

3.聚集索引不会增加额外的存储，但会使底层的数据有序化；