SQL Server索引(1)-记录数据的格式

2008-01-28 13:08:28　来源:blog　作者:蝈蝈俊.net 点击:

这里收集整理了一系列数据库的索引知识，算是对索引知识的一个总结回顾吧。通过总结，你将发现自己以前很多很模糊的概念都清晰了很多。不论是缓存的数据信息，还是物理保存的信息，他们的基本单位都是数据页。所以理解数据页是最最基础的知识点，本文就介绍跟索引有关的数据页的一些基础知识。

m.cn/tips/182/7791182.shtml

　　对大型行的支持

　　在 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