1、写在前面
微软专门给出SQL Server设计思路及实现路线,从7大体系结构阐述是如何实现,通过了解这些,我们就可以总结出数据库设计原则、编程中sql写法及注意事项,从而优化我们的系统性能,本系列着重讨论SQL Server索引体系。
以下为主要内容:
2、数据存储结构
文件->系统页->区->页
2.1 区与页
页是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. 怎么观测到页内容?
2.1.1 页头
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)
注意:定长和变长是针对字符型数据而言的
1. 转发存根和转发记录的产生
数据更新时,产生转发存根、转发记录。
如果一个数据页已经填满了,但对它某行的某一变长列更新时,此列的更新长度大于当前长度导致该数据页的字节数大于8192字节时,系统会拿出最长的列,存到Row overflow Data数据页中。当前页的某行某列存放转发存根24字节,另外一个数据页存放列数据。转发行指针24字节。
由此产生几个问题
2. 鬼影索引、鬼影数据、鬼影版本?的产生
当做delete操作时,系统不是立马删除数据,而是在数据行里标记鬼影记录,为了防止与查询互斥,标记一个鬼影版本。如果该表有索引,那么相应的索引页里索引行也标记鬼影数据记录。方便事务回滚操作。如果事务都结束了,由鬼影服务程序每过默认5秒会删除鬼影数据。数据页的页头也会标记有鬼影记录。
2.1.3 行偏移量数据
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列偏移量))
2. 每个变长列最好是填充满,更新时页更新满。
如果有变长列,在做更新时列长度不一致。当一个数据页满时,如果更新的列数据长度大于现有数据,则会出现行溢出(row overflow data)。造成查询时多查找一次。
3. 最好不要出现null列,null列也会造成 row overflow data。当数据页满时,更新数据时也会产生。
操作注意事项
1. 更新操作变长列时,最好保持长度一致。避免row overflow data。
2. 查询时尽量指定列,如果查询所有列时会发生查找到的行的某一变长列,有可能另外row overflow data 页查找。多查询一次。
2.1.5 相关类型页结构
GAMP:Global Allocation Map Pages 全局映射分配表,区属于一个对象.
SGAMP:Shared Global Allocation Map Pages 全局共享映射分配表,区属于不同对象.
用于跟踪页可用空间情况,每个字节代表一个页信息,总共可以记录8088页情况,
还有8个字节哪里去?
系统通过GAM和SGAM管理区空间分配情况,通过PFS管理页空间使用情况
将表或视图对象通过IAM将(Data page、Index page、row overflow page、blob page)对于同一对象,每种数据类型都会产生IAM页,IAM用于管控对象包含哪些页文件,每8个页为一个独立区。
GAM-SGAM-IAM状态位表示
DCM:differential change map 差异变更映射图。
差异化备份时,DCM有效。
用于记录每个区自上次完全备份后是否发生变更,每个位表示1个区,1表示修改过,0表示未变更。记录8096*8个字节。
如果一个区内某个页发生变更该区会被记录变更过,但进行差异化备份时,会对发生变更的区进行备份,差异化备份减少备份量。
BCM:Bulk Change Map:批量表更映射图。
在做简单备份和完全备份两个模式中,BCM无效。
当sqlserver发生批量操作时,日志并不是完全记录而是最小化记录,如果备份采用日志备份,并不能完全回滚数据。那么BCM就为这样的批量操作做了记录。BCM责任记录每个区自上次完全日志备份后是否发生批量操作。每位记录一个区是否有批量操作,每个页记录当前页是否有批量操作记录。记录8096*8字节。