二，SQL Server数据库结构

1. 数据库物理存储结构

　　数据库管理系统体系结构最底层的管理器从原理上讲都可以统称为磁盘空间管理器，所以，数据库的物理存储是指数据库的数据以什么方式存储在计算机磁盘上，又是怎么在磁盘空间管理器管理下运行的。

1.1 数据文件和文件组

• 主数据库文件（Primary Database File）：是数据库的起点，可以指向数据库中文件的其它部分。每个数据库都有一个主数据库文件。文件扩展名一般是 .mdf。、
• 次数据库文件（Secondary Database File）：有些数据库可能没有次数据文件，而有的数据库则有多个次数据文件。文件扩展名多是 .ndf。
• 事务日志文件：日志文件包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个日志文件，但可以不止一个。推荐的文件扩展名是 .ldf。

　　数据库文件组

• 为了便于分配和管理，SQL Server允许将多个文件归纳为同一组，并赋予此组一个名称，这就是文件组。文件组能够控制各个文件的存放位置，其中的每个文件常建立在不同的硬盘驱动器上，这样可以减轻单个磁盘驱动器的存储负载，提高数据库的存储效率，从而达到提高系统性能的目的。
  
• 在存储数据时， SQL Server采用按比例填充策略使用文件组内每个文件所提供的存储空间。例如，如果一个服务器上有4个可供数据库使用的硬盘，他们提供给数据库的最大存储空间分别为100M，200M，300M和100M，我们可以在前面3个硬盘上建立一个数据文件组，包含三个文件，每个硬盘上分配一个文件，在第4个硬盘上建立数据库的日志文件，当发生数据库的读写操作时，日志数据写入第4个硬盘，而数据库数据写入前3个硬盘。在写入数据时，SQL Server根据文件组内每个数据文件中剩余的空间大小按比例分配写入其中的数据量，即1：2：3，这样不仅保证文件组内每个文件的空间基本上同时用完，而且将一次磁盘操作同时分配给多个磁盘控制器，可以减轻每个磁盘的负载，从而提高写入速度。

　　建立数据库文件和文件组时，应注意以下两点：

• 每个数据文件或文件组只能属于一个数据库，每个数据文件也只能成为一个文件组的成员。也就是说，数据文件不能跨文件组使用，数据文件和文件组不能跨数据库使用。
• 日志文件是独立的，它不能作为其他数据文件组的成员。即数据库内的数据和日志不能存入相同的文件或文件组。

　　主文件组和次文件组

• 主文件组：包含主数据文件和所有没有被包含在其它文件组里的文件。在创建数据库时，如果未指定其他数据文件所属文件组，这些文件将归属于主文件组。数据库的系统表都包含在主文件组里，所以，当主文件组的空间用完后，将无法向系统表中添加新的目录信息。
• 次文件组：也称用户自定义文件组，包括所有使用数据库创建语句（CREATE  DATABASES）或数据库修改语句（ALTER  DATABASES）时使用FILEGROUP关键字进行指定的文件。
• 默认文件组：任何时候，只能有一个文件组是默认文件组。默认情况下，主文件组被认为是默认文件组。

　　使用数据文件和文件组的建议：

• 创建数据库时，允许数据文件能够自动增长，但要设置一个上限。否则有可能充满磁盘。
• 主文件组要足够大以容纳所有的系统表。否则新的信息就无法添加到系统表，数据库也就无法追加修改。
• 建议把频繁查询的文件和频繁修改的文件分放在不同的文件组。
• 把索引、大型的文本文件、图象文件放到专门的文件组里。

1.2 数据文件的使用分配

　　1. 基本知识

• 在 SQL Server 中，数据文件存储的基本单位是页(Page)。
• 在 SQL Server 2000 中，页的大小是 8 KB。这意味着 SQL Server 2000 数据库每兆字节有 128 页。
• 每页的开始部分是 96 字节的页首，用于存储系统信息，如页的类型、页的可用空间量、页对象ID等。
• 根据页面所存储的不同信息，可以将它划分为8种页类型

　　8种页类型　

• 数据页面(Data)

　　　　存储数据行中除TEXT，nNTEXT和IMAGE列数据以外的数据

• 文本/图像页面(Binary Large Object)

　　　　存储数据行中的TEXT，nNTEXT和IMAGE列数据

• 索引页面(Index)

　　　　存储索引项

• 全局分配映射页面(Global and Shared Global Allocation Map)

　　　　存储数据文件的区域分配信息

• 页的可用空间信息页面(Page Free Space)

　　　　存储数据文件中可用的空闲信息页面

• 索引分配映射页面(Index Allocation Map)

　　　　存储表或索引所使用的区域信息

• 大容量更改信息页面(Bulk Changed Map)

　　　　存储有关自上次执行BACKUP LOG语句后的大容量操作所修改的扩展盘区的信息

• 差异更改信息页面(Differential Changed Map)

　　　　存储有关自上次执行BACKUP DATABASE语句后更改的扩展盘区信息

　　2.数据页面存储格式

　　在SQL Server中，数据文件的页按顺序编号，这个编号称为页码。文件首页的页码时0。众多数据页构成一个数据文件，每个数据文件都有一个文件ID。在数据库中唯一标识的一页需要同时使用文件ID和页码。

　　在数据页上，数据行紧接着页首按顺序放置。在页尾有一个行偏移表。页上的每一行在行偏移表中都有一个条目，每个条目记录对于行的第一字节与页首的距离。行偏移表中的条目序列与页中行的序列相反。

　　

 

 

 　　(1)数据页首

　　数据页包含数据行中除 text、ntext 和 image 数据外的所有数据，页头占用每个数据页的前96个字节，剩余的8096字节用于数据和行偏移数组。

　　(2)数据行

• 紧跟着页头的就是存储表的真正数据行区域。单个数据行的最大长度是8060字节。
• 数据行不能跨页存储（文本和图像例外）。页内数据行的多少依赖于表的结构和要存储的数据。
• 如果一个表的所有列都是定长，那么该表在每一页上存储相同数目的行。
• 如果一个表里有变长列，那么该表总是在每一页上存储尽可能多的行。
• 数据行越短，每一页存储的行数就越多。

　　(3)行偏移表

• 当单行数据长度为最大8060字节时，行偏移数组占用8096-8060=36个字节。
• 但实际中一个数据行大多不是8060字节，往往比这个小，所以数据行占用的总字节数目和行偏移数组占用的总字节数是系统动态调整，数据行字节越少，偏移数组字节越多，反之，数据行字节越多，偏移数组字节越少，但不能少于36个字节。
• 每两个字节构成一个条目块，每个条目表示页中相关数据行开始的偏移量。

　　注意：行偏移数组表示的是页中数据行的逻辑顺序，不是物理顺序。真正的物理顺序与聚集索引有关。

　　(4)页面链接

• 每个表或索引视图的数据行一般都分开存储在多个 8 KB 数据页中。如上面所述，每个数据页都有一个 96 字节的页头，其中包含拥有该页的表的标识符 (ID) 这样的系统信息，也包含指向下一页及前面用过的页的指针。
  

　　

 

 

 　　3. SQL Server空间使用分配

　　SQL Server 2000向表或索引分配空间的基本单位为区域(Extent)，一个区域长度为8个连续的页面，也就是64KB。区域分为以下两种类型：

　　统一区域：区域中的8个页面只能存储同一种数据库对象(对象是指表或索引)。

　　混合区域：区域中不同页面可以存储不同的数据库对象。但由于每个页面只能存储同一种数据库对象，所以一个混合区域最多可存储8种数据库对象。

　　当数据库对象较少时，可以分配混合区域，供数据库中不同类型的数据库对象使用。但当同种数据库对象所占用的空间达到8个页面时，可以在将它们转换为统一区域。

　　SQL Server使用以下两种类型的分配映射页面记录区域的分配使用情况

• 全局分配映射（Global Allocation Map，简称GAM）页面：GAM页面中的每一位记录一个区域的分配情况，当位值为1时，说明区域为空闲区域；当位值为0时，表示区域已经被分配使用。由于每个页面大小为8KB，所以一个GAM页面能够覆盖64000个区域，即4GB。
• 共享全局分配映射（Shared Global Allocation Map，简称SGAM）页面：SGAM页面用于记录有空闲页面的混合区域。每个SGAM页面覆盖64000个区域。当位值为1时，说明区域为混合区域，并且其中有空闲页面；当位值为0时，说明相应的盘区没有被用作混合区域，或者它是一个没有空闲页可分配的混合区域。

　　4. 索引分配映射管理

　　SQL Server使用索引分配映射（IAM）页面管理堆或索引所分配区域的使用情况。每个IAM页面的页面头记录该IAM页面所映射区域范围的起始区域，其映射区中的每一位说明一个区域的使用状态，其中第一位代表IAM页面所映射区域范围内的第一个区域，第二位代表第二个区域等等。当映射区中某位为0时，说明该位所映射区域仍未分配给拥有该IAM页面的对象使用；当其值为1时，说明该位所映射区域已经分配给拥有该IAM页面的对象使用。

　　每个堆或索引可以有一个或多个IAM页面记录分配给该对象区域的使用情况，堆或索引在每个分配区域的数据文件中至少有一个IAM页面。这些IAM页面在数据文件中没有固定位置，它们根据需要进行分配，并随即定位。一个对象的所有IAM页面组成一个链表，其第一个IAM页面的位置记录在sysindexes系统表的FisrtIAM列内.所以，根据FirstIAM列和IAM页面链表就可以查找到一个对象的所有IAM页面。SQL Server确定某个页面属于某个表的唯一方法就是检查该表的IAM页。

　　5. 页面自由空间管理

　　当一个区域分配给某个数据库对象时，SQL Server使用PFS（Page Free Space）页面记录区域内的单个页面是否分配使用，以及页面中的空间使用情况：即全部空闲、1%~50%满、51%~80%满、81%~95%满，还是96%~100%满。当SQL Server需要分配新的页面，或者查找有自由空间页面时，它使用PFS页面中所记录的这些信息。 数据库文件的第二页（页面1）是第一个PFS页，其后每经过8088页就又是一个PFS页。

　　在已分配的区域内查找不到足够的空间，它将分配一个新的区域给数据库对象。

　　一个完整数据文件的页面结构

• 0----文件头页面
• 1----PFS页面
• 2----GAM页面
• 3----SGAM页面‘
• ......
• 数据页面以及其他页面

1.3 事务日志文件结构

　　1. 事务日志

• SQL Server在内存中建立了一个缓冲区，在检索数据时，它将数据读入该缓冲区中，而在修改数据时，它并不是直接修改磁盘中的数据，而是先在缓冲区中建立修改数据副本，之后在页面刷新时再将它们写入磁盘。这样做可以提高数据处理速度，减少磁盘读写次数。在缓冲区中被修改后而没有写入磁盘的页面称作“脏页”，“脏页”由SQL Server迟写进程定期写入磁盘，这一操作叫做“页面刷新”。
• 每当对缓冲区中的数据页面进行修改时，SQL Server自动在日志缓存中构造该操作的日志记录。

　　日志记录所记录的操作类型

• 每个事务的开始和结束
• 数据的插入，修改和删除操作
• 事务操作的对象
• 修改前数据的旧值，修改后数据的新值
• 区域的分配和释放
• 表和索引的创建和删除

　　2. 事务日志的物理存储

　　一个数据库事务日志可以对应一个或多个物理文件，SQL Server  2000 在内部又将每个物理日志文件分成许多个虚拟日志文件。虚拟日志文件没有固定大小，且物理日志文件所包含的虚拟日志文件数不固定。 虚拟日志文件大小不能由管理员配置或设置，而由SQL Server代码动态确定。

　　事务日志是回绕的日志文件。例如，假设有一个数据库，它包含一个分成5个虚拟日志文件的物理日志文件。当创建数据库时，逻辑日志文件从物理日志文件的始端开始。在逻辑日志的末端添加新的日志记录，逻辑日志就向物理日志末端增长。截断操作发生时，删除最小恢复日志序号（MinLSN）之前的虚拟日志内的记录，这部分日志记录所占用的空间即可被重复使用。

　　

 

 

 　　当逻辑日志的末端到达物理日志文件的末端时，新的日志记录绕回物理日志文件的始端，如图所示。这个循环不断重复，只要逻辑日志的末端不到达逻辑日志的始端。如果经常截断旧的日志记录，使得总能为下一个检查点创建的所有新日志记录保留足够的空间，那么日志永远不会填满。 

　　

 

 

 2. 数据库的逻辑组织

　　一个数据库服务器上可以有多个数据库，这些数据库可以按功能进行划分，最终存储管理相应的数据。每个数据库又有各种不同的逻辑组件。

2.1数据库架构

　　数据库存储是按物理方式在磁盘上作为两个或更多的文件实现。用户使用数据库时使用的主要是逻辑组件，例如表、视图等。

　　

 

 

　　每个 SQL Server 实例有四个系统数据库（master、model、tempdb 和 msdb）以及一个或多个用户数据库。

　　

 

 

2.2 系统数据库

　　

 

 

　　(1)master数据库

• master数据库由系统表组成，记录了安装以及随后创建的所有数据库的信息。包括数据库所用磁盘空间、文件分配、空间使用率、系统级的配置设置、登录帐户密码、存储位置等。
• master数据库是系统的关键，不允许任何人对它进行修改。必须经常保留一份它的当前备份的拷贝。

　　(2)tempdb数据库

• tempdb数据库记录了用户创建的所有临时表、临时数据和临时的存储过程。该数据库是一个全局资源，允许所有可以连接上的SQL服务器访问。
• 注意，每次SQL Server重新启动，该数据库被重新创建而不是恢复，所以以前用户创建的任何临时对象（表、数据、存储过程等）都将丢失。也就是说，每次SQL Server重启，tempdb数据库都是空的。

　　(3)model数据库

• model数据库是建立新数据库的模板。每当创建一个新的数据库时（比如用企业管理器去创建，或用CREATE  DATABASE创建），SQL Server就会根据model数据库的内容来形成新数据库结构的基础，把后面初始化为空，以准备放数据；同时将系统表复制到刚创建的数据库中去。
• 严格禁止删除model数据库，否则SQL Server系统将无法使用。

　　(4)msdb数据库

• msdb数据库是由SQL Server Agent服务使用的数据库。由于SQL Server Agent主要执行一些事先安排好的任务，所以该数据库多用于进行复制、作业调度以及管理报警等活动。
• 如果不使用代理服务功能，我们可以忽略这个数据库。

2.3 用户数据库

• 在SQL Server中，一个用户数据库是由用户定义的用来永久存储像表和索引这样的数据库对象的磁盘空间构成，这些空间被分配在一个或多个操作系统文件上。
• 用户数据库和系统数据库一样，也被划分成许多逻辑页（每个逻辑页的大小是8KB），在每个数据库文件中页是从0到X连续编号，上限值X是由文件的大小决定的。
• 通过指定数据库ID(或数据库名称)、文件ID和页号，可以引用任何一页。当扩大文件时，新空间被追加到文件的末尾。

　　使用CREATE  DATABASE语句创建一个新的用户数据库，该数据库就被赋予一个惟一的数据库ID，者说dbid，同时在master数据库中的sysdatabases表中就会插入一个新行。