《数据库原理》课堂笔记(2):SQL Server 2000的物理存储与索引结构

数据库与数据库文件

表是什么?
表名:列/字段/属性 * 行/记录(关系数据库)/元组(集合)
这是一个关系表
以后我们会讲到主索引

认识SQL Server数据库

  • 可以包含多达32767个数据库
  • 一个数据库最多可以包含2^31−1个对象
  • 可以横跨多个磁盘驱动器和多个操作系统文件(哇!)
  • 从1MB到1048516TB(理论上最大)

SQL Server系统数据库

1. Master数据库

  • 系统表:描述整个SQL Server的信息如磁盘空间、文件分配、空间使用率、系统级设置、登录账户、其他数据库和其他的SQL Server
  • 每个用户数据库自身的系统表
  • Master数据库是SQL Server的核心!

2. Model数据库

  • 模板数据库
  • 新创建的数据库从模板数据库继承内容:对象、权限等

3. Tempdb数据库

  • 工作空间:临时表、中间结果、游标的实体化数据
  • Tempdb的日志只记录回滚事务的信息而没有恢复事务的信息,这是为了提高insert语句的性能
  • 每次重启SQL Server时,Tempdb数据库被重新创建而不是恢复它

运算的中间结果不能都放在Cache里面,必须回写到磁盘

4. Pubs数据库

样例数据库,只占用2MB的空间

5. Northwind数据库

  • 样例数据库,最早是为Microsoft Access的用户开发的
  • 比Pubs数据库复杂一些
  • 占用4MB的空间

6. Msdb数据库

有一些Gcc的功能

  • SQL Server Agent服务使用的数据库
  • 这个服务主要执行一些事先安排好的任务,如备份和复制
  • 一般不需要直接访问Msdb的表,和系统表一样,更不需要直接更新Msdb中的数据

系统数据库里大概有一千多张表,但很多表大概是用不到的

SQL Server数据库文件

问:

  • 一个数据库是由几个操作系统文件构成的?
  • 数据库文件的空间是如何进行分配和管理的?
  • 数据库文件的结构是怎样的?

结构和空间的分配是很关键的

基本知识

  • 小型关系数据库管理系统:每个关系(或对象)对应一个OS文件;文件结构简单,管理依赖于OS,如dBase、Foxbase等
  • 大型关系数据库管理系统:一个OS文件包含所有的对象,包括表;文件结构复杂,管理依赖于DBMS,如Oracle、DB2、Sybase、SQL Server等

数据库文件

  • 主数据库文件(主文件),*.MDF:一定要有一个,且只能有一个;存放数据和索引及其他对象还用来跟踪数据库的其他文件
  • 次数据文件,*.NDF:可以有0个或多个;存放主文件容纳不下的数据库对象
  • 日志文件,*.LDF:至少要有一个

在建立库的时候可以要求它产生NDF文件,但是一般这都是隐藏的。
我们要了解MDF文件的结构和空间管理。
把MDF看成是一个逻辑结构,而不去考虑它在磁盘上的映射,那是磁盘控制器的工作。
MDF是页式文件,最小粒度是8kb,每个页都有编号,是某个数据库的某个文件的某个页。

每个数据库文件都有5个基本属性:

  • 逻辑文件名:name
  • 物理文件名:filename
  • 初始大小:size,以页(8KB)为单位
  • 最大尺寸:maxsize,以页为单位
  • 扩展增量:growth
  • 文件标识:fileid,在每个数据库内是唯一的,参阅sysfiles

SQL Server数据库文件组

  • 将一个数据库的多个数据文件归并到不同的文件组
  • 包含主数据文件的文件组叫做主文件组,每个数据库只能有一个主文件组
  • 除了主文件组以外,一个数据库也可以有一个或多个用户定义的文件组
  • 系统表的所有数据页总是从主文件组里的主文件中分配而来的。

缺省文件组:

  • 每个文件组都有DEFAULT这个属性
  • 在每个数据库中,只能有一个文件组可以是缺省文件组。通常情况下主文件组也就是缺省文件组。
  • 大多数SQL Server数据库都只有单独的一个数据文件在一个(缺省的主)文件组里。

为什么要使用多个文件?

  • 对性能没有任何益处
  • 根据备份恢复数据库时具有灵活性
  • 可以方便地将数据库移动到不同的驱动器上

SQL Server数据库文件结构

OS的文件和DBMS的文件的区别:
OS的文件 DBMS的文件
功能简单 须在文件目录、文件描述块、物理块等部分附加一些信息
面向批处理 要求即席访问、动态修改
用途比较单一,共享的程度较低 供所有数据库用户共享,在并发控制、故障恢复和数据安全等方面有特殊要求
数据及数据量都比较稳定 数据库文件的数据量变化较大
对OS的依赖性太大,不利于DBMS的移植 要适应各种操作系统的要求

用户数据库:

  • 数据库由磁盘空间构成,磁盘空间被分配在一个或多个由DBMS管理的OS文件上
  • 数据库文件可以看成是逻辑页的序列,每页大小为8kb,从0到X连续编号
  • 可以用dbid+fileid+pageid来标识一页
  • Master数据库包含50个系统表,其中有19个和用户数据库的系统表一模一样

SQL Server的空间分配与管理

SQL Server实际上是以盘区(Extent)为单位来管理空间的
一个盘区由8个逻辑上连续的页组成,或者说是64KB的空间
统一盘区:只被单个对象拥有的盘区,盘区中的所有8个逻辑页只能被拥有它的对象使用
混合盘区:最多被8个对象共享的盘区,即每个对象拥有盘区中的一页
对于一个表或者索引来说:

  • 前8页:必须来自混合盘区
  • 8页之后:全部使用同一盘区

所以如果一个表很小,就会有很多空余的空间,因为一个页只能属于一个对象。
不能控制用户表的增长速度,所以一个表可能被分隔开。
所以对表的大小是有考量的:表的存储空间不超过8页,就可以随意在盘区中寻找存储空间;如果超过8页,就是大表,从第9页开始完全按照盘区分布。

盘区的管理

全局分配映像页(GAM-Global Allocation Map)

  • 在一个GAM所覆盖的范围内,每个盘区在GAM页内都有1个Bit与之对应。0表示已被使用,1表示空闲;
  • 一个GAM页有8000Bytes,即64000Bits:4GB
  • 在一个数据库文件里,每隔4GB的磁盘空间就会有一个GAM页存在

共享全局分配映像页(SGAM - Shared Global Allocation Map)

  • SGAM与GAM的覆盖范围相同:4GB
  • 在数据库文件里,每隔4GB的磁盘空间,SGAN和GAM页就会成对出现
  • 每个盘区在SGAM页内都有1个Bit与之对应:1=混合盘区+有空闲;0=非混合盘区/混合盘区无空闲
可以概括为如下表格:
盘区当前使用情况 GAM位 SGAM位
空闲,没有被使用 1 0
统一盘区/没有空闲页的混合盘区 0 0
有空闲页的混合盘区 0 1
矛盾 1 1

页空闲空间页(PFS - Page Free Space)

  • 在数据库文件里,PFS页记录了每个单独的页是否已经被分配以及每一页上空闲空间的比例是多少,而GAM和SGAM页是针对盘区的
  • 对每一页,在PFS页里都有一个Byte而不是一个Bit来记录该页是空,1-50%满,51-80%满,81-95%满还是96-100%满
  • 每个PFS页覆盖了8088个连续的页,几乎64MB的空间,因此每隔8088页又是一个PFS页
  • 数据库文件的第一个PFS页是页1

索引分配映像页(IAM-Index Allocation Map)

  • IAM页映射了由堆(Heap)或索引(Index)所使用的盘区,堆是一个没有簇集索引的表
  • 每个堆或索引都有一个或多个IAM页来记录所有分配给该对象的盘区
  • 对每个文件来说,如果一个堆或索引在该文件上有盘区,那么在该文件上这个堆或索引至少要有一个IAM页。如果一个堆或索引在一个文件上的盘区范围超过了一个IAM页能够记录的范围,那么这个堆或索引在该文件上就要有多个IAM页。

IAM页的结构:

  • 一个小的页头:包含该IAM页映射范围内的第一个盘区的地址
  • 8个页指针槽:记录堆或索引的来自于混合盘区的前8页的页指针
  • 对一个对象来说,只有它的第一个IAM页在这些页指针槽才有值
  • 位映像:每个Bit代表了该IAM页映射范围内的一个盘区,而不管该盘区是否已经分配给拥有该IAM页的对象。值1表示已经分配给该对象,值0则相反

IAM页的管理:

  • SQL Server根据每个对象的需要来分配IAM页,IAM页在数据文件中的位置是随机的
  • 每个IAM页覆盖了约512000页的范围,sysindexes表的FirstIAM字段指向了对象的第一个IAM页
  • 一个对象的所有IAM页被链接成链表:双向链表
  • 对于堆来说数据页和页中的数据行都没有任何特定的顺序,也没有被链接在一起,数据页之间的唯一
  • 逻辑关系记录在IAM页上:表明分配给某个堆的盘区(8页);FirstIAM以及IAM页之间的双向链表

IAM页的问题:
用于备份和恢复数据库的特殊页:

  • 增量更新映射页(DCM - Differential Changed Map),它用来记录自上一次全数据库备份以来,数据库文件中已被更新的盘区
  • 块更新映射页(BCM Bulk Changed Map),当数据库文件的一个盘区被用于最低级的或者是块日志操作时,BCM页中就会有记录
  • 数据库文件的第一个DCM和BCM页分别是页6和7,像GAM和SGAM页一样,DCM和BCM页也用Bit来表示文件的每个盘区,每隔511230页它们就出现一次

盘区的页的顺序管理:

  • 在任何数据库文件里面页0(即第一页)永远是文件头页,每个文件只能有一个文件头页
  • 页1(第二页)是第一个PFS页,每隔8088页又是一个PFS页
  • 页2(第三页)是第一个GAM页,页3(第四页)是第一个SGAM页,每隔511230页就会出现另一个GAM页和SGAM页
pic.PNG

数据库的备份与恢复

备份类型:

  • 全备份:备份数据库中的每一页
  • 增量备份:只备份自上次全备份以来发生变化的盘区,DCM页
  • 日志备份:将自上次全备份或日只备份以来已经写到事务日志的所有日志记录拷贝下来

恢复模式:

  • NULL:对数据库的所有操作都要记入日志,包括BULK操作
  • BULK_LOGGED:BULK操作只是简单地被记入日志,而利用BCM页里的信息来完整地恢复数据库

表的物理存储

认识SQL Server数据库表

  • 理论上,一个数据库可以包括多达20亿个表
  • 每个表最多可以有1024个列
  • 表中行的最大长度(不包括text和image)不能超过8060Bytes,行不能跨页存储
  • 表中行数和表的大小都没有限制,只受你的存储设备大小的约束
  • 表可以没有主码,允许有重复的行出现(等到讲索引的时候再讲)

SQL DDL语言:CREATE TABLE 计51 (——)
最多创建1024个列
大对象:属于这一行,但和字符数据不存储在一起;记录不能跨页存储
记录可以有无限条
在实际模型中,可以有重复记录出现,但在关系模型中,这不是一个好表
ORACLE:列不超过256个,其中有一个系统调用列;对记录长度没有限制,可以跨页存,页有4k、8k、16k,可变长

内部存储概述

系统表也是表,前缀是sys:在机器上安装了SQL Server之后,自动生成MASTER.MDF(主数据库),放的是管理信息;一共有50多个,其中有19个会复制。
创建用户数据库时会自动拷贝一些系统表(比如下面这3个,这些是局部的,属于这个库的。虽然名字一样,但功能不同。)
这些表在文件头。
与表紧密相关的3个系统表:

  • Sysobjects:

储存这个库的对象(表)的性质,每个表至少有一条记录。
SQL Server的每个表在此系统表中都有单独的一行描述该表的基本信息
name id xtype uid info status base_scheme_ver replinfo parent_obj crdate

  • Syscolumns:

描述每个对象的每一列
SQL Server数据库表的每一列在该系统表中都有一行与之对应,内容包括列名、列的数据类型和长度等等;任何包含可变长数据的列在该系统表里的xoffset值都是负的id colid name xtype length xoffset

  • Sysindexes

描述每一个表的存储空间
FirstIAM:第一个IAM页的地址
FirstPage:第一个页的地址

当一个表刚被创建时,SQL Server2000不会立即为其分配存储空间
如果表没有定义任何PRIMARY KEY或UNIQUE约束,在该系统表中将只有一行与之对应,且该行的indid值为0,说明表是一个堆如果在表上定义了PRIMARY KEY或UNIQUE约束,这背后有一个簇集索引支持,则表在该系统表中有一行,且对应行的indid值为1
表的每个非簇集索引在该系统表中也有一行,而且该行的indid值在2到250之间

数据页

基本知识

  • DBMS的磁盘空间管理器隐藏了底层硬件和操作系统的细节,允许DBMS的I/O处理软件把数据看成是页的集合
  • 磁盘空间管理器支持作为数据单元的页的概念,为DBMS的I/O处理软件提供分配/回收页和读/写页的命令
  • 在SQL Server 2000的体系结构中有专门的页管理器和文本管理器
  • 在处理I/O问题时,数据是页的集合,但是DBMS的高层软件把数据看成是记录的集合
  • 如何在页中安排记录?将页在逻辑上划分为很多槽,每个槽里包含一条记录
  • 每条记录要有唯一的标识,简称RID,可以用<页ID,槽号>对来标识
  • 用一个唯一的整数作为每条记录的RID,同时维护一个表,列出每个RID对应的记录所在的页和槽

定长记录的数据页格式

压缩方式

  • 无论什么时候记录被删除,都把页中最后一条记录移到删除后空出的槽
  • 这种格式可以通过简单的偏移量计算来定位页中的第i个记录,并且所有的空槽都出现在页的尾部
  • 如果有外部引用指向被移动的记录,这种机制就不可运转了。

位图方式

  • 用一个位数组来处理删除操作,每个槽对应一位,用于跟踪空闲槽的信息
  • 在页中定位记录需要扫描位数组,找到那些对应位为1的槽
  • 当一条记录被删除时,其对应的位就被清为0
  • 不会对记录的外部引用造成破坏
pic2.PNG

变长记录的数据页格式

pic3.png
  • 页中槽的长度使不固定的
  • 插入记录时,必须为该记录分配大小合适的空间,空槽太长太小都不合适
  • 当记录被删除时,又必须移动记录来填充由删除操作产生的空洞,以确保页中所有的空闲空间是连续的
  • 在页中移动记录就变得非常重要。
  • 每一页为一个槽目录,每个槽都具有一个由“记录的偏移量”和“记录的长度”组成的<偏移量,长度>对
  • 删除操作只是简单地将要被删除记录的偏移量改为-1
  • 移动记录时,由页号和槽号组成的RID不会改变,只是存储在槽目录中的记录的偏移量改变了
  • 被删除记录所在的槽不能从槽目录中移走
  • 这种页格式也适用于定长记录,只不过所有的<偏移量,长度>对中表示纪录长度的值都相同
  • 变长记录的页格式也叫分槽的页结构,其最大特点就是在槽中维护记录的偏移量,记录的长度要视情况而定

SQL Server的数据页格式

数据页是包含所有非文本数据和非图像数据的存储结构
与SQL Server的所有其他类型的页一样,数据页的大小:8KB=8192Bytes
数据页由三部分组成:

  • 页头
  • 数据行
  • 行偏移数组
pic4.png

页头

字段 字段所包含的内容
pageID 数据库中该页的文件号和页号
nextpage 如果该页处于一个页链中,则该字段表示下一页的文件号和页号
prevpage 如果该页处于一个页链中,则该字段表示上一页的文件号和页号
objID 该页所属对象的ID
lsn 用于该页变化和更新的日志序列号(即LSN)值
slotCnt 该页上槽(行)的总数
level 该页在索引中的级别(对于叶级页来说该值总是0)
indexID 该页的索引ID(对于数据页来说该值总是0)
freeData 该页上第一个空闲空间的字节偏移量
pminlen 行的固定长部分的字节数
freeCnt 该页上空闲字节的数目
reservedCnt 由所有事务保留的字节数
xactreserved 由最近启动的事务保留的字节数
tornBits 每个扇区1位,用于检测使该页分裂的写
flagBits 一个两字节的位图,包含了有关该页的额外信息

数据行

  • 因为数据行不能跨页存储,所以单个数据行的最大长度是8060字节(记录大小要合适(防止出现4031Byte的记录))
  • 文本和图像列存储在数据页之外(需要一个定长的字段来存储地址)
  • SQL Server总是在每一页上存储尽可能多的行,这样可以减少I/O的次数并提高缓存的命中率
  • 在实践中要合适地确定记录的长度

行偏移数组

  • 行偏移数组是一个两字节条目的块,每个条目表示页中相关数据行开始的偏移量
  • 行的长度并没有存储在行偏移数组里,而有关行的长度信息包含在行本身里:行的定长部分的长度;行的变长部分的结束位置
  • 在表中没有为每一行设置一个全局的内部行号,SQL Server用文件号、页号和页中的槽号来唯一标识表中的每一行。
  • 行偏移数组表示页中数据行的逻辑顺序。如果一个表有簇集索引,那么SQL Server将按照簇集索引的顺序来存储数据行

检查数据页

使用DBCC PAGE命令可以查看任何数据页的页头、数据行和行偏移表,即行偏移数组
这是一个非文档命令,只有系统管理员才可以使用
object_id:描述的是每个对象的ID

数据行的结构

基本知识

定长记录:记录的字段数是固定的,每个字段都是定长的
变长记录:记录的字段数是固定的,某些字段是变长的

SQL Server数据行结构

定长与变长记录中字段组织方式的结合
为什么数据行中要存储列数?
内部的列可能会随时变化

大小 1字节 1字节 2字节 n字节 2字节 列数/8 2字节 2+变长列数 m字节
描述 状态位A 状态位B 行的定长部分的长度(不包括存储列数的2字节和NULL位图,包括前4字节) 定长数据 列数 NULL位图,每个列一位 变长列的数目 列偏移数组 变长列数据
信息 助记符 大小
状态位A TagA 1字节
状态位B(SQL Server 2000未使用) TagB 1字节
行定长部分的大小 Fsize 2字节
定长数据 Fdata Fsize-4字节
列数 Ncol 2字节
NULL位图(每个列占1位,其中值1表示相应的列是NULL) Nullbits Ncol/8字节
变长列数 VarCount 2字节
可变列的偏移数组 VarOffset 2*VarCount字节
变长数据 VarData Varoff[VarCount]-(Fsize + 4 + Ncol/8 + 2*VarCount)

状态位A:

  • 位0:版本信息,在SQL Server 2000中该位总是0
  • 位1到位3:作为一个3位的值对待,0表示该行是一个基本的记录,1表示该行是一个转向记录,2表示该行是一个转向存根,3表示该行是一个索引记录,4表示该行是一块碎片,5表示该行是一个幽灵索引记录,6表示该行是一个幽灵数据记录
  • 位4:表示NULL位图的存在性。在SQL Server 2000中,即使任何列都不允许NULL值,NULL位图也总要出现,因此值总为1
  • 位5:表示行中是否存在变长列
  • 位6和7:在SQL Server 2000中未被使用

列偏移数组:一个两字节的条目,表示变长列在行中结束的位置
在行的定长或变长数据块内,SQL Server按照创建表时定义的列顺序来存储数据

如何确定一个字符串类型的列(不管是定长的char列还是可变长的varchar列)的值是空串而不是空值(NULL)?
SQL Server 2000根据数据行中NULL位图里相应的位是0还是1来区分一个列的值是空值(NULL)还是空串

SQL Server的页链接

索引:

  • SQL Server将一个索引的同一级的所有页链接在一起:双向链表

堆:

  • SQL Server不会把堆中的数据页链接成一个双向链表
  • 确定某个页属于某个堆的唯一方法是检查该堆的IAM页

具有簇集索引的表

  • 由于簇集索引的叶级页就是真正的数据页(处于索引的同一级),所以SQL Server把具有簇集索引的数据页链接成双向链表
  • 可以使用页头信息中的M_NextPage和M_PrevPage的值来确定表中的数据页在链表中的顺序

文本和图像数据

大对象(LOB)数据的存储

  • 缺省情况下,SQL Server的数据行里只包含非LOB数据,而LOB数据要单独存储,但是数据行里会有指向LOB数据的指针
  • SQL Server的数据行绝对不能跨页存储,但是LOB数据可以跨页存储
  • 通过设置某个表选项可以让LOB数据存储在数据行本身里

LOB数据存储在表外

数据行里只包含一个16字节的指针指向LOB数据所在的页:LOB页
一个LOB也可以存放来自多个行的多个LOB列的数据,但一个LOB页只能存放来自单个表的LOB数据
构成LOB列的页集不需要互相定位,在逻辑上它们被组织成一个B-树结构。从串中间开始的操作非常高效
如果LOB列的数据量小于32KB

  • 数据行里的LOB指针指向一个84字节大小的LOB列的根结构,即B-树结构的根节点
  • 根节点指向LOB列的数据区。虽然LOB列的数据按照B-树结构进行逻辑组织,但是在物理上根节点和各个数据区在表的LOB页全面展开
  • LOB数据可放在页内任何有可用空间的地方,而且每个数据区的大小是由应用程序实际所写的大小确定的,不会有固定或非固定的预留空间
  • 表中不同行及列的小数据区结合在一起填充到LOB页里。如果一个LOB列的数据量小于63字节,那么它就全部存储在根结构里

如果LOB列的数据量大于32KB

  • SQL Server就开始在数据区和根节点之间建立中间节点
  • 根结构和数据区以和前面描述的同样方式在LOB页内交叉存储
  • 中间节点要存储在LOB列之间不共享的LOB页里,也就是说,每个存储中间节点的LOB页只能包含数据行里一个LOB列的中间节点

LOB数据存储在数据行里

如果把所有的LOB数据存储在数据页之外,访问数据时,SQL Server就需要执行额外的读页操作
如果需要频繁地访问LOB数据,而LOB数据又足够的小完全可以放在SQL Server的数据页里,就可以通过将部分LOB数据存储在数据行里来改善系统的性能
Text in Row选项

  • 可以将特定表的该选项设置为'ON':sp_tableoption Employees, 'text in row', 'on'
  • 也可以为存储在数据行里的LOB数据指定一个最大的字节数:sp_tableoption Employees, 'text in row', 500
  • 选项'ON'实际上制定了存储在数据行里的LOB数据的最大长度的256字节
  • 选项可以设置的范围是24到7000字节

如果Text in Row选项为OFF

  • 则数据行存储的是一个指向LOB数据的根结构的16字节的指针

如果Text in Row选项为ON

  • 如果LOB列的数据没有超过事先指定的最大值,则数据行里存储的就是LOB数据本身,像处理varchar列一样处理LOB列
  • 如果LOB列的数据超过了事先指定的最大值,则数据行存储的就是LOB数据的根结构

LOB数据根结构(根节点)的最小尺寸是24字节,最大尺寸是84字节
切换表的Text in Row选项

  • 从OFF变为ON时,所有的LOB数据并不立即移动,只有当LOB数据真正被更新时,LOB数据才会发生移动
  • 从ON变为OFF时,所有的LOB数据就从数据行里移出,取而代之的是一个16字节的指针,指向LOB数据的根结构

即使真正的LOB数据量小于Text in Row选项的限制,但也并不总意味着数据都将存储在数据行里

  • SQL Server要求所有的非LOB数据必须存储在数据页上
  • 数据页上单个数据行的最大长度还会受到8060字节的限制
  • 变长列比LOB列有优先权存储在数据行


新增一则回应

除非特别注明,本页内容采用以下授权方式: Creative Commons Attribution-ShareAlike 3.0 License