SQL Server 索引基础知识(1)--- 记录数据的基本格式_Mssql数据库教程

由于需要给同事培训数据库的索引知识，就收集整理了这个系列的博客。发表在这里，也是对索引知识的一个总结回顾吧。通过总结，我发现自己以前很多很模糊的概念都清晰了很多。

不论是缓存的数据信息，还是物理保存的信息，他们的基本单位都是数据页。所以理解数据页是最最基础的知识点，本篇博客就介绍跟索引有关的数据页的一些基础知识。

数据页的基础知识

SQL Server 中数据存储的基本单位是页（Page）。数据库中的数据文件（.mdf 或 .ndf）分配的磁盘空间可以从逻辑上划分成页（从 0 到 n 连续编号）。磁盘 I/O 操作在页级执行。也就是说，SQL Server 每次读取或写入数据的最少数据单位是数据页。

注意：日志文件不是用这种方式存储的，而是一系列日志记录。

数据库被分成逻辑页面（每个页面8KB），并且在每个文件中，所有页面都被连续地从0到x编号，其中x是由文件的大小决定的。我们可以通过指定一个数据库ID、一个文件ID、一个页码来引用任何一个数据页。当我们使用ALTER DATABASE命令来扩大一个文件时，新的空间会被加到文件的末尾。也就是说，我们所扩大文件的新空间第一个数据页的页码是x+1。当我们使用DBCC SHRINKDATABASE或DBCC SHRINKFILE命令来收缩一个数据库时，将会从数据库中页码最高的页面（文件末尾）开始移除页面，并向页码较低的页面移动。这保证了一个文件中的页码总是连续的。

在 SQL Server 中，页的大小为 8 KB。这意味着 SQL Server 数据库中每 MB 有 128 页。依次类推。根据数据库的文件大小，我们可以算出数据库有多少数据页。

SQL Server 2005 有以下几种页类型：

页类型	内容
Data	当 text in row 设置为 ON 时，包含除 text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据之外的所有数据的数据行。
Index	索引条目。
Text/Image	大型对象数据类型： text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据。 数据行超过 8 KB 时为可变长度数据类型列： varchar、nvarchar、varbinary 和 sql_variant
Global Allocation Map、Shared Global Allocation Map	有关区是否分配的信息。
Page Free Space	有关页分配和页的可用空间的信息。
Index Allocation Map	有关每个分配单元中表或索引所使用的区的信息。
Bulk Changed Map	有关每个分配单元中自最后一条 BACKUP LOG 语句之后的大容量操作所修改的区的信息。
Differential Changed Map	有关每个分配单元中自最后一条 BACKUP DATABASE 语句之后更改的区的信息。

数据页（Data 类型页）的结构示意图：

每页的开头是 96 字节的标头，用于存储有关页的系统信息。此信息包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元 ID。

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

有关数据页的更多知识，可以通过下面这篇文章获得更详细的了解：

估计在堆中存储数据所需的空间量
http://technet.microsoft.com/zh-cn/library/ms189124.aspx

另外也可以看我收集的资料：怎样查看表的数据页的结构
http://blog.joycode.com/ghj/articles/113108.aspx

对大型行的支持

在 SQL Server 2005 中，行不能跨页，但是行的部分可以移出行所在的页，因此行实际可能非常大。
（比如：一行多列时，这一行的部分列在数据页A，部分列在数据页B）
页的单个行中的最大数据量和开销是 8,060 字节 (8 KB)。但是，这不包括用 Text/Image 页类型存储的数据。
在 SQL Server 2005 中，包含 varchar、nvarchar、varbinary 或 sql_variant 列的表不受此限制的约束。
当表中的所有固定列和可变列的行的总大小超过限制的 8,060 字节时，SQL Server 将从最大长度的列开始动态将一个或多个可变长度列移动到 ROW_OVERFLOW_DATA 分配单元中的页。
每当插入或更新操作将行的总大小增大到超过限制的 8,060 字节时，将会执行此操作。
将列移动到 ROW_OVERFLOW_DATA 分配单元中的页后，将在 IN_ROW_DATA 分配单元中的原始页上维护 24 字节的指针。
如果后续操作减小了行的大小，SQL Server 会动态将列移回到原始数据页。

SQL Server 的数据页缓存

SQL Server 数据库的主要用途是存储和检索数据，因此密集型磁盘 I/O 是数据库引擎的一大特点。此外，完成磁盘 I/O 操作要消耗许多资源并且耗时较长，所以 SQL Server 侧重于提高 I/O 效率。缓冲区管理是实现高效 I/O 操作的关键环节。SQL Server 2005 的缓冲区管理组件由下列两种机制组成：用于访问及更新数据库页的缓冲区管理器和用于减少数据库文件 I/O 的缓冲区高速缓存（又称为“缓冲池”）。

 

缓冲区管理的工作原理
一个缓冲区就是一个 8KB 大小的内存页，其大小与一个数据页或索引页相当。因此，缓冲区高速缓存被划分为多个 8KB 页。缓冲区管理器负责将数据页或索引页从数据库磁盘文件读入缓冲区高速缓存中，并将修改后的页写回磁盘。页一直保留在缓冲区高速缓存中，直到已有一段时间未对其进行引用或者缓冲区管理器需要缓冲区读取更多数据。数据只有在被修改后才重新写入磁盘。在将缓冲区高速缓存中的数据写回磁盘之前，可对其进行多次修改。

实验

下面做一个简单的实验来看你是否已经掌握的上面的知识点：

准备测试环境

在一个SQL 2005数据库中，执行下面脚本。

简单来说，就是创建了2个表，注意这两个表，一个是存储的 nchar(2019) 的字段，一个是存储的 nchar(2020) 的字段。 我们将来看这两个表在同样数据下，存储所花费的空间大小。由于缓存和物理存储的基本单位都是数据页，这个表物理存储的大小跟全部缓存的大小会是一样的。

然后我们每个表填充20个数据。

-- 创建2个测试表
CREATE TABLE [dbo].[Table_2019]([Data] [nchar](2019) NOT NULL) 
CREATE TABLE [dbo].[Table_2020]([Data] [nchar](2020) NOT NULL) 
go
-- 填充数据
declare @i int
set @i = 0
while(@i < 20)
begin
    insert Table_2019(Data) values('')
    insert Table_2020(Data) values('')
    select @i = @i + 1
end
go 

这里我们用 nchar 数据类型，是因为：
当指定了 NOT NULL 子句时，nchar 数据类型是一种长度固定的数据类型。
如果插入值的长度比 nchar NOT NULL 列的长度小，将在值的右边填补空格直到达到列的长度。
例如，如果某列定义为 nchar(10)，而要存储的数据是“music”，则 SQL Server 将数据存储为“music_____”，这里“_”表示空格。
http://technet.microsoft.com/zh-cn/library/ms175055.aspx
这样我们填充测试数据的脚本就非常简单。
而且计算数据行所占的空间也非常简单。
另外，我们建立的这两个表都没有索引，所以他们都是堆，有关估计在堆中存储数据所需的空间量请参看以下文章：
http://technet.microsoft.com/zh-cn/library/ms189124.aspx

完成准备工作后，我们来查看这两个所占空间的大小。在 SQL Server Management Studio 中，我们选择测试数据库， 然后在右键菜单中依次选择 
Reports --> Standard Reports --> Disk Usage by Top Tables 或者Disk Usage by Table 就可以看到下面统计数据。 

Disk Usage by Top Tables: [ghj_Demo]
on GHJ1976-PC\SQLEXPRESS at 2007/12/27 9:21:33
This report provides detailed data on the utilization of disk space by top 1000 tables within the Database.
Table Name	# Records	Reserved (KB)	Data (KB)	Indexes (KB)	Unused (KB)
dbo.Table_2020	20	200	160	8	32
dbo.Table_2019	20	136	80	8	48

这两个表同样20条记录。Table_2020 表数据占了 160kb ，即 20 个数据页。Table_2019 表数据占了 80 kb，即 10 个数据页。
为何会这样呢？
Table_2020 表的1个数据页只能放下1个数据行。
Table_2019 表的1个数据页只能放下2个数据行。
这两个表的字段长度只差2个字节，但是物理存储却是一倍的差距。

参考资料：

SQL Server数据库中存储引擎深入探讨
http://tech.ccidnet.com/art/1106/20070320/1040665_3.html

《Microsoft SQL Server 2005技术内幕：存储引擎》 这本书电子版的一部分
http://book.csdn.net/bookfiles/504/10050417350.shtml

MSDN 中关于“页和区”的描述
http://technet.microsoft.com/zh-cn/library/ms190969.aspx

聚集索引结构
http://technet.microsoft.com/zh-cn/library/ms177443.aspx

行溢出数据超过 8 KB
http://technet.microsoft.com/zh-cn/library/ms186981.aspx

缓冲区管理
http://technet.microsoft.com/zh-cn/library/aa337525.aspx

估计堆的大小
http://technet.microsoft.com/zh-cn/library/ms189124.aspx

nchar 和 nvarchar (Transact-SQL)
http://technet.microsoft.com/zh-cn/library/ms186939.aspx

Teched 2007 上 吴家震 主讲的"微软SQL服务器Always-On Tech-nologies: 高级索引策略" 录像下载地址:
http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032364059&Culture=zh-CN
注意, 这个页面标示的是 "SharePoint 2007 网站性能调优" ,但是其实是高级索引策略,微软弄错文件了,害得我一个个下下来看,哪个是需要的录像.

来源：http://blog.joycode.com/ghj/