（3.6）存储引擎--文件与数据的结构

一. 文件

（1）主数据文件.mdf初始大小至少为3MB（在sql2012/2008以后至少需要5M），次要数据文件.ndf初始大小，同日志文件一样至少为512KB（在sql2012/2008以后至少需要1M）；

（2）SQL SERVER在逻辑上用文件组将文件分批管理（类似ORACLE的TABLESPACE），一个文件组可以包含多个文件，插入数据时，同一个文件组内的所有文件等比例增长（ORACLE的一个TABLESPACE中逐个使用多个文件），例如：文件组中有两个文件，初始大小分别为100M和200M，此时插入3M数据，file1新增（100/300）*3M=1M，file2新增（200/300）*3M=2M，如下图：

（3）页（page），SQL SERVER中的数据文件由8K大小的数据页组成，每个数据文件中的页从0开始编号，页大小不可以自定义（ORACLE可自定义），且每个页只可以属于一个数据对象；

（4）区（extent），或者叫扩展，8个物理上连续的页为一个扩展，即64K，扩展的存在是为了避免不停地分配8K的页面，提高页面分配的效率。SQL SERVER有两种类型的区，如下图：

　　（4.1）混合区：为了节约空间，将少量数据的表或索引存放在混合区中，当表或索引的数据增长到8页时，再使用统一区来存放，一个混合区有8个页，每个页可以属于不同的数据对象，即每个混合区最多为8个数据对象共享；

　　（4.2）统一区：由单个数据对象所有，如果对表中现有数据创建索引，且索引的大小超过8页，则索引将全部使用统一区，没有混合区的分配过程。

二. 页

2.1、非数据页

（1）文件头（FILE HEADER），每个数据文件的第1页，页号为0，该页主要包括当前文件的属性描述，比如：文件组ID、文件ID、文件当前大小、文件最大/最小值、文件增量、一系列的LSN等；

（2）页面空闲空间（PFS），每个数据文件的第2页，页号为1，该页记录当前数据文件每个数据页的空间状态：该页是为空、已满 1% 到 50%、已满 51% 到 80%、已满 81% 到 95% 还是已满 96% 到 100%。PFS页内用1个字节来描述1个数据页的分配及空间状态，每个PFS页约有可用空间8088个字节，即数据文件内约每64M的空间会出现一个PFS页。PFS页描述数据页空间状态如下图：

（3）全局分配映射（GAM），每个数据文件的第3页，页号为2，该页记录当前数据文件每个区的分配状态，0为已使用（作为混合区或统一区，已被分配），1为未使用（自由区，未被分配）；

　　（3.1）结合PFS和IAM页，如果数据对象没有可用空间时，且数据大小已超过8页，GAM为数据对象分配一个统一区；若数据大小尚未超过8页，则GAM结合SGAM为其寻找或者分配一个混合区；

　　（3.2）GAM页内用1位来描述1个区的分配状态，每个GAM页约有可用空间8000个字节，即数据文件内约每4G的空间会出现一个GAM页；

（4）共享分配映射（SGAM），每个数据文件的第4页，页号为3，该页记录当前数据文件哪些区被用作混合区，1为含有自由页面的混合区，0为自由区或已满的混合区；

　　（4.1）当数据对象需要一个含有自由页面的混合区时，SGAM用来辅助GAM寻找或分配一个混合区；

　　（4.2）SGAM页内用1位来描述1个区的分配状态，每个SGAM页约有可用空间8000个字节，即数据文件内约每4G的空间会出现一个SGAM页；

（5）索引分配映射（IAM），该页跟踪数据文件中的页属于哪一个数据对象，IAM页头有8个页面指针，指向数据对象在混合区中的数据页（如果混合区中数据被删除可能少于8个指针），IAM页内比特位为1表示该区属于自己所属的数据对象，比特位为0表示该区不属于自己所属的数据对象；

　　（5.1）每个数据对象的每个分配单元拥有一个IAM页，IAM同GAM、SGAM一样可以管理约4G的空间，如果分配单元包含多个文件，或者文件大小超过4G，则需要另外的IAM页来管理，IAM页间通过双向链表连接；

　　（5.2）可以通过sysindexes或sys.system_internals_allocation_units系统目录得到first_IAM页面的位置，IAM页在数据文件中的位置是随机的，可能IAM页所在文件并不是所管理的那个文件；

　　（5.3）对于堆数据插入而言，通过IAM页和PFS页找到自己有剩余空间的页，直接插入数据即可，但索引数据插入的位置是由索引键的顺序决定的；

（6）差异更改映射（DCM），每个数据文件的第7页，页号为6（页号4，5为保留页），该页跟踪当前数据文件中，自上次全备份后被修改的区，以提高差异备份的效率，1为被修改过，0为未被修改；

　　（6.1）DCM页内用1位来描述1个区的分配状态，每个DCM页约有可用空间8000个字节，即数据文件内约每4G的空间会出现一个DCM页；

（7）大批量更改映射（BCM），每个数据文件的第8页，页号为7，该页跟踪当前数据文件中，自上次日志备份后被大批量操作修改的区， 1为被修改过，0为未被修改；

　　（7.1）大批量恢复模型只记载大批量操作的最小日志记录（不记录明细，只记载动作），所以比在全恢复模型下执行速度要快（因为省去写大量日志的成本），但为了保证数据库的可恢复，在进行日志备份时，仍然会使用BCM页将大批量操作所修改的区备份出来，所以此时会出现日志很小，但日志备份很大的情况；

　　（7.2）BCM页只有在大批量恢复模型下才有用，因为简单恢复模型下不记载任何大批量操作的日志，且日志自动截断，全恢复模型下有详细的日志纪录；

　　（7.3）BCM页内用1位来描述1个区的分配状态，每个BCM页约有可用空间8000个字节，即数据文件内约每4G的空间会出现一个BCM页；

2.2、数据页

2.2.1、DATA

（0）数据页，包括页头、数据行、行偏移矩阵三部分内容，如下图：

（0.1）页头固定大小为96B，包括页号、所属对象、LSN等页面信息；

（0.2）数据行累计不超过8060B，那么单行数据最长为8060B，由于数据行还存在一些行开销，所以建表时，数据类型的最大长度不允许超过8000B（LOB类型除外），行开销在下面会有介绍；

（0.3）行偏移矩阵以2B为一个指针，标识每一个数据行的起点位置。哪怕是索引页，数据行在页内的物理顺序也并不一定是有序的，数据读取时按行偏移矩阵的顺序读出（从slot0起），从逻辑上保证了数据行在页内的顺序，可能slot0对应的数据行是在页面内物理上的第N行（N<>1）；

（1）行内数据（IN_ROW_DATA）

　　单行未超过8060B的数据行，或者单行超过8060B但存储在当前页的数据，称为行内数据；

（2）行溢出数据（ROW_OVERFLOW_DATA）

　　在SQL SERVER 2005及以后的版本中，如果表中定义了变长数据类型，允许单行数据长度突破8060B，超过的部分即为行溢出数据，如果变长列被更新后缩短，可能会被移回行内数据页（通常减少1000字节以上时，SQL SERVER才会有检查是否可移回）；

　　（2.1）行溢出数据为超过8060B数据行的一部分列，在行内数据页上有24B的指针指向行溢出数据页；

　　（2.2）只会将变长数据类型的行溢出数据移出行内数据页，因为定长数据类型在定义表时就不允许累计长度超过8000B，所以行内数据页足够存放定长数据列；

　　（2.3）移出行内数据页的变长列，必然是整列数据，不会将变长列数据拆开存放，一个新的数据页也足够存放最大长度为8000B的变长列；

　　（2.4）如果变长列使用了MAX分类符，如：varchar(MAX)，则数据库自动根据varchar(MAX)的数据长度选择不同的数据页，如果MAX<=8000，则使用行溢出数据页；如果MAX>8000则使用大对象数据页；

（3）大对象数据（LOB_DATA）

　　存放如：TEXT/IMAGE/XML/varchar(MAX)等最大长度可超过8000B的数据类型的数据；

　　（3.1）大对象数据也是通过8k的数据页来存储数据，在行内数据页中包含一个16字节的指针指向大对象数据的根页，大对象数据通过B-树结构来组织多个数据页；

　　（3.2）可以通过打开text in row选项将大对象数据存储在行内数据页，当大对象数据被更新超过500B时，则会从行内数据页将大对象数据移出，这是个日志操作，因此移动操作比较耗时，所以不建议开启该选项；

（4）数据行

每个数据行，除了每个列的数据之外，还包括状态位、定长列偏移量、总列数、NULL位图、变长列数、列偏移矩阵，这些即为行开销。

创建全定长列的表，数据行如下图：

1 2 3 4 5 6 7 8 9 10 11

if object_id ('test_col') is not null     drop table test_col; GO create table test_col ( col1 char(1), col2 char(2) ) GO insert into test_col values('A','B')

（4.1）状态位A占1B标识列的类型、是否有变长列等信息，状态B占1B未启用；

（4.2）定长列偏移量占2B，标识定长列的截止位置，这里为1+1+2+1+2=7B，每个定长列的起始位置，可以在无文档记载的系统视图sys.system_internals_partition_columns中查到（leaf_offset字段），col1、col2为定长数据所在；

（4.3）在包含可为NULL的列时（定长列为NULL时列值为0，而变长列不存储列值），这时就需要总列数（占2B）和NULL位图了，比如：有4列，NULL位图为11110100（由低位向高位对应），则表示第3列为NULL，那么第1、2、4列返回数据，第3列返回NULL。上图中两列均可为NULL（11111100）但都有值，所以不返回NULL，NULL位图的长度随着表中列数的多少在变化，以BYTE为单位进行增长，最小为1B即8位，可以标识8列；

（4.4）全定长的数据行中，不包括变长列数、列偏移矩阵的行开销；

创建包含变长列的表，数据行如下图：

1 2 3 4 5 6 7 8 9 10 11

if object_id ('test_col2') is not null     drop table test_col2; GO create table test_col2 ( col1 char(1), col2 varchar(2) ) GO insert into test_col2 values('A','B')

（4.5）此时定长列偏移量为1+1+2+1=5B，因为只一个长度为1B的定长列，另外，总列数仍然是2，NULL位图也相同；

（4.6）变长列数占2B，表示表中有几个变长列，它决定了后面的列偏移矩阵中要放几个元素，列偏移矩阵标识表中每个变长列的截止位置，col2为变长数据所在；

创建全变长列的表，数据行如下图：

1 2 3 4 5 6 7 8 9 10 11 12

if object_id ('test_col3') is not null     drop table test_col3; GO create table test_col3 ( col1 varchar(1), col2 varchar(2), col3 varchar(3) ) GO insert into test_col3 values(NULL,'','B')

（4.7）此时定长列偏移量为1+1+2=4B，因为没有定长列，另外，总列数为3，NULL位图为11111001，表示第1列为NULL，第2列与第1列偏移量一样，表示没有值，但又不为NULL，即空字符串，变长列数为3，列偏移矩阵中共有3个元素；

（4.8）当表中的列被删除时，列在数据行中的占用的空间并不会立即被回收，除非立即重建索引，或者当空间不足时数据库才考虑回收；

（4.9）当表中的列被修改时：

　　（a）从NULL到NOT NULL，数据类型长度缩短都会带来全表的该列数据检查，对于大表将非常耗时；

　　（b）对于修改数据类型长度，并没有真正替换原有列，只是新增了一列，根据列偏移量读取数据时，原有列不会被读到而已，并且对于定长列，只是限制了新插入数据的值范围，列存储空间依旧使用改变前的数据类型的长度，如下图：

1 2 3 4

alter table test_col alter column col2 char(1) --以下语句将失败 insert into test_col values('A','BB')

此时定长列偏移量仍然为1+1+2+1+2=7B，总列数仍然为2，新增的列位于原有列之前；

　　（c）修改变长列数据类型长度后，列偏移矩阵会向后移动，如下图：

1	alter table test_col3 alter column col3 varchar(1)

此时变长列数为4，原来的第3列从0x11到0x12，长度为1，第4列为原有列，并且新的列值将会使用新的数据类型长度；

（4.10）当表中新增列时，无法指定新列的逻辑顺序（列号），只能排在后面，除非重建表，在某些图形工具中将新列插入到某个位置，事实上就是在重建表；新增列的物理顺序放在数据行的相应数据区域，如下图：

1	alter table test_col3 add col_new char(1) not null default 'a'

定长列col_new被放在定长数据区域，从列偏移矩阵可以看出，后面的变长数据相应的向后移动了1B；

转自：http://blog.51cto.com/qianzhang/1217431