SQL Server之索引解析（一）

1、写在前面

微软专门给出SQL Server设计思路及实现路线，从7大体系结构阐述是如何实现，通过了解这些，我们就可以总结出数据库设计原则、编程中sql写法及注意事项，从而优化我们的系统性能，本系列着重讨论SQL Server索引体系。

以下为主要内容：

• 页和区体系结构
• 表和索引数据结构体系结构
• 查询处理体系结构

2、数据存储结构

文件->系统页->区->页

2.1 区与页

页是SQL Server存储数据的最小单元，每个页文件大小8K，每个页会存放不同页类型的数据，页主要由以下部分组成。

• 页头：标头
• 数据区：数据行和可用空间
• 行偏移数组：行偏移量数组指示页上行的逻辑顺序

另外，需要注意的是，磁盘I/O操作在页级执行。

区就是一个集合概念，它是由8个物理上连续的页构成的，区有助于有效管理页。

对SQL Server而言，其页类型有很多种

问题

1. 头部为什么要96字节，它记录什么东西？

2. 数据行系统开销是什么，为什么是7字节，它怎么记录数据的？

3. 行偏移量数据干啥用的？

4. 数据是怎么通过数据页完成CURD的？

5. 怎么观测到页内容？

2.1.1 页头

• 存储页的系统信息，固定长度96字节。
• 记录页所在存储位置，页间双链表关系，页存储空间信息，页数据事务数据情况。
• 根据这些情况，可判定是否要插入新数据、插入什么位置、涉及事务如何回滚、还原

2.1.2 数据行

行头系统信息存储：

Bit 0 位，版本信息

Bits 1-3 位，行记录类型

0，primary record，主记录

1：forwarded record

2：forwarding stub

3：index record，索引记录

4：blob或者行溢出数据

5：ghost索引记录

6：ghost数据记录

Bit 4 位，NULL位图

Bit 5 位，表示行中有变长列

Bit 6 位，保留

Bit 7 位，ghost record（幽灵记录）

数据行大小计算公式：定长位数据大小+变长数据大小+空行数+4

最小系统开销：行头(2)+定长列数(2)+空(1)+变长列数(2)

注意：定长和变长是针对字符型数据而言的

• 注意：表格中关于Char不定长字符数据的描述是错误，所以配了第二张图

1. 转发存根和转发记录的产生

数据更新时，产生转发存根、转发记录。

如果一个数据页已经填满了，但对它某行的某一变长列更新时，此列的更新长度大于当前长度导致该数据页的字节数大于8192字节时，系统会拿出最长的列，存到Row overflow Data数据页中。当前页的某行某列存放转发存根24字节，另外一个数据页存放列数据。转发行指针24字节。

由此产生几个问题

• 对查询危害是，查询该行时的数据至少要查询2个数据页。
• 对更新时，数据是否满，是否要新增或删除转发存根记录。

2. 鬼影索引、鬼影数据、鬼影版本？的产生

当做delete操作时，系统不是立马删除数据，而是在数据行里标记鬼影记录，为了防止与查询互斥，标记一个鬼影版本。如果该表有索引，那么相应的索引页里索引行也标记鬼影数据记录。方便事务回滚操作。如果事务都结束了，由鬼影服务程序每过默认5秒会删除鬼影数据。数据页的页头也会标记有鬼影记录。

2.1.3 行偏移量数据

• 行偏移是一个个小块组成的，每个小块2个字节，表示数据行从第几个字节后开始记录，也就是距离页头多少偏移量开始记录
• 存储方式是从游往左存储，用槽位来描述，slot 0 ，slot 1 ....
• 行偏移量记录的内容是什么呢？该行记录从哪个字节开始，一般情况下，slot 1 从第96个字节后开始
• 常说的聚集索引存储顺序是物理排序，指的不是行记录物理排序，而是行偏移量物理排序，数据页中，行记录都是顺序往后添加的，通过修改行偏移量来达到聚集索引的顺序查找

Slot array，行偏移量数组系统至少会保留36字节，每2字节表示1列。

每一行的数据内容最多不能超过8053，因为SQL Server还需要对每一行的数据最少附加7字节的系统数据。

2.1.4 数据库表设计与操作注意事项

表设计注意事项

1. 保持表中列的总长度不超过以公式为准(防止 row overflow data、blob data)

8192-96-（2+定长列总长度+2+空值列个数/8+变长列数2+（变成长列长度+2列偏移量））

• 只有定长位：固定长列总长度<8192-96-2-2-2-列数/8
• 只有变长列：变长列总长度<8192-96-2-2-2-列数/8-2*变长列数偏移量
• 有变长和定长：固定长列总长度+变长列总长度<8192-96-2-2-列数/8-2*变长列数-变长列数*2
• 完美表设计：（固定长列总长度+变长列总长度）N倍=8192-96-2-2-2-列数/8-2*变长列数偏移量-2N

2. 每个变长列最好是填充满，更新时页更新满。

如果有变长列，在做更新时列长度不一致。当一个数据页满时，如果更新的列数据长度大于现有数据，则会出现行溢出（row overflow data）。造成查询时多查找一次。

3. 最好不要出现null列，null列也会造成 row overflow data。当数据页满时，更新数据时也会产生。

操作注意事项

1． 更新操作变长列时，最好保持长度一致。避免row overflow data。

2． 查询时尽量指定列，如果查询所有列时会发生查找到的行的某一变长列，有可能另外row overflow data 页查找。多查询一次。

• 为什么不建议使用Select *????

2.1.5 相关类型页结构

索引页和索引叶子页结构

• 非聚集索引存储结构:
  • 索引值+Page页码 Btree中间位
  • 索引值+Page页码+主键值 Btree叶子节点
• 聚集索引存储结构
  • 索引值+Page页码 Btree中间位
  • 数据行 Btree叶子节点

1、系统空间分配情况

区分配情况GAM和SGAM

GAMP:Global Allocation Map Pages 全局映射分配表，区属于一个对象.

SGAMP:Shared Global Allocation Map Pages 全局共享映射分配表，区属于不同对象.

页空间分配情况PFS (Page free space)

用于跟踪页可用空间情况，每个字节代表一个页信息，总共可以记录8088页情况，

还有8个字节哪里去？

系统通过GAM和SGAM管理区空间分配情况，通过PFS管理页空间使用情况

2、IAM 索引分配页

将表或视图对象通过IAM将（Data page、Index page、row overflow page、blob page）对于同一对象，每种数据类型都会产生IAM页，IAM用于管控对象包含哪些页文件，每8个页为一个独立区。

GAM-SGAM-IAM状态位表示

3、备份与还原 DCM和BCM

DCM：differential change map 差异变更映射图。

差异化备份时,DCM有效。

用于记录每个区自上次完全备份后是否发生变更，每个位表示1个区，1表示修改过，0表示未变更。记录8096*8个字节。

如果一个区内某个页发生变更该区会被记录变更过，但进行差异化备份时，会对发生变更的区进行备份，差异化备份减少备份量。

BCM：Bulk Change Map:批量表更映射图。

在做简单备份和完全备份两个模式中，BCM无效。

当sqlserver发生批量操作时，日志并不是完全记录而是最小化记录，如果备份采用日志备份，并不能完全回滚数据。那么BCM就为这样的批量操作做了记录。BCM责任记录每个区自上次完全日志备份后是否发生批量操作。每位记录一个区是否有批量操作，每个页记录当前页是否有批量操作记录。记录8096*8字节。