SQL Server之索引解析（一）
微软专门给出SQL Server设计思路及实现路线，从7大体系结构阐述是如何实现，通过了解这些，我们就可以总结出数据库设计原则、编程中sql写法及注意事项，从而优化我们的系统性能，本系列着重讨论SQL Server索引体系。
以下为主要内容：
页和区体系结构
表和索引数据结构体系结构
查询处理体系结构
文件->系统页->区->页
页是SQL Server存储数据的最小单元，每个页文件大小8K，每个页会存放不同页类型的数据，页主要由以下部分组成。
页头：标头
数据区：数据行和可用空间
行偏移数组：行偏移量数组指示页上行的逻辑顺序
另外，需要注意的是，磁盘I/O操作在页级执行。
区就是一个集合概念，它是由8个物理上连续的页构成的，区有助于有效管理页。
对SQL Server而言，其页类型有很多种
|
类型 |
英文 |
描述 |
作用 |
|
1 |
Data Page |
数据页 |
存储行数据、聚集索引叶、位置随机 |
|
2 |
Index Page |
索引页 |
非聚集索引、聚集索引非叶子、位置随机 |
|
3 |
Text Mixed Page |
位置随机 |
|
|
4 |
Text Page |
LOB的单列值 |
位置随机 |
|
7 |
Sort Page |
临时表操作页 |
位置随机 |
|
8 |
GAM Global allocation Map |
全局分配表 |
每个区都记录一个对象，每个文件第二页时GAMP，每位表示该区是否可用 |
|
9 |
SGAM Shared Global Allocation map |
全局共享分配表 |
每个区都记录混合对象数据，每个文件的第三页 |
|
10 |
IAM Index Allocation Map |
索引分配图 |
记录表对象数据存储在哪些区，将这些区连在一起就构成一个表数据存储。 |
|
11 |
Page Free Space |
*空间页 |
记录每页*空间情况，每个字节代表一个页 |
|
13 |
Boot Page |
启动页信息 |
|
|
14 |
Server Configuration Page |
服务配置信息 |
|
|
15 |
File Header Page |
文件头信息 |
总是在第0页 |
|
16 |
Differential Changed map |
每个文件第6页，上次备份差异化 |
|
|
17 |
Bulk Change Map |
每个文件第7页，上次备份批量差异化 |
问题
1. 头部为什么要96字节，它记录什么东西？
2. 数据行系统开销是什么，为什么是7字节，它怎么记录数据的？
3. 行偏移量数据干啥用的？
4. 数据是怎么通过数据页完成CURD的？
5. 怎么观测到页内容？
存储页的系统信息，固定长度96字节。
记录页所在存储位置，页间双链表关系，页存储空间信息，页数据事务数据情况。
根据这些情况，可判定是否要插入新数据、插入什么位置、涉及事务如何回滚、还原
行头系统信息存储：
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个字节，表示数据行从第几个字节后开始记录，也就是距离页头多少偏移量开始记录
存储方式是从游往左存储，用槽位来描述，slot 0 ，slot 1 ....
行偏移量记录的内容是什么呢？该行记录从哪个字节开始，一般情况下，slot 1 从第96个字节后开始
常说的聚集索引存储顺序是物理排序，指的不是行记录物理排序，而是行偏移量物理排序，数据页中，行记录都是顺序往后添加的，通过修改行偏移量来达到聚集索引的顺序查找
Slot array，行偏移量数组系统至少会保留36字节，每2字节表示1列。
每一行的数据内容最多不能超过8053，因为SQL Server还需要对每一行的数据最少附加7字节的系统数据。
表设计注意事项
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 *????
索引页和索引叶子页结构
-
非聚集索引存储结构:
索引值+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字节。