SQLServer索引基础知识

SQLServer索引基础知识(1)---记录数据的基本格式由于需要给同事培训数据库的索引知识，就收集整理了这个系列的博客。发表在这里，也是对索引知识的一个总结回顾吧。通过总结，我发现自己以前很多很模糊的概念都清晰了很多。不论是缓存的数据信息，还是物理保存的信息，他们的基本单位都是数据页。所以理解数据页是最最基础的知识点，本篇博客就介绍跟索引有关的数据页的一些基础知识。数据页的基础知识SQLServer中数据存储的基本单位是页（Page）。数据库中的数据文件（.mdf或.ndf）分配的磁盘空间可以从逻辑上划分成页（从0到n连续编号）。磁盘I/O操作在页级执行。也就是说，SQLServer每次读取或写入数据的最少数据单位是数据页。注意：日志文件不是用这种方式存储的，而是一系列日志记录。数据库被分成逻辑页面（每个页面8KB），并且在每个文件中，所有页面都被连续地从0到x编号，其中x是由文件的大小决定的。我们可以通过指定一个数据库ID、一个文件ID、一个页码来引用任何一个数据页。当我们使用ALTERDATABASE命令来扩大一个文件时，新的空间会被加到文件的末尾。也就是说，我们所扩大文件的新空间第一个数据页的页码是x+1。当我们使用DBCCSHRINKDATABASE或DBCCSHRINKFILE命令来收缩一个数据库时，将会从数据库中页码最高的页面（文件末尾）开始移除页面，并向页码较低的页面移动。这保证了一个文件中的页码总是连续的。在SQLServer中，页的大小为8KB。这意味着SQLServer数据库中每MB有128页。依次类推。根据数据库的文件大小，我们可以算出数据库有多少数据页。SQLServer2005有以下几种页类型：页类型内容Data当textinrow设置为ON时，包含除text、ntext、image、nvarchar(max)、varchar(max)、varbinary(max)和xml数据之外的所有数据的数据行。Index索引条目。Text/Image大型对象数据类型：text、ntext、image、nvarchar(max)、varchar(max)、varbinary(max)和xml数据。数据行超过8KB时为可变长度数据类型列：varchar、nvarchar、varbinary和sql_variantGlobalAllocationMap、SharedGlobalAllocationMap有关区是否分配的信息。PageFreeSpace有关页分配和页的可用空间的信息。IndexAllocationMap有关每个分配单元中表或索引所使用的区的信息。BulkChangedMap有关每个分配单元中自最后一条BACKUPLOG语句之后的大容量操作所修改的区的信息。DifferentialChangedMap有关每个分配单元中自最后一条BACKUPDATABASE语句之后更改的区的信息。数据页（Data类型页）的结构示意图：每页的开头是96字节的标头，用于存储有关页的系统信息。此信息包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元ID。在数据页上，数据行紧接着标头按顺序放置。页的末尾是行偏移表，对于页中的每一行，每个行偏移表都包含一个条目。每个条目记录对应行的第一个字节与页首的距离。行偏移表中的条目的顺序与页中行的顺序相反。有关数据页的更多知识，可以通过下面这篇文章获得更详细的了解：估计在堆中存储数据所需的空间量另外也可以看我收集的资料：怎样查看表的数据页的结构对大型行的支持在SQLServer2005中，行不能跨页，但是行的部分可以移出行所在的页，因此行实际可能非常大。（比如：一行多列时，这一行的部分列在数据页A，部分列在数据页B）页的单个行中的最大数据量和开销是8,060字节(8KB)。但是，这不包括用Text/Image页类型存储的数据。在SQLServer2005中，包含varchar、nvarchar、varbinary或sql_variant列的表不受此限制的约束。当表中的所有固定列和可变列的行的总大小超过限制的8,060字节时，SQLServer将从最大长度的列开始动态将一个或多个可变长度列移动到ROW_OVERFLOW_DATA分配单元中的页。每当插入或更新操作将行的总大小增大到超过限制的8,060字节时，将会执行此操作。将列移动到ROW_OVERFLOW_DATA分配单元中的页后，将在IN_ROW_DATA分配单元中的原始页上维护24字节的指针。如果后续操作减小了行的大小，SQLServer会动态将列移回到原始数据页。SQLServer的数据页缓存SQLServer数据库的主要用途是存储和检索数据，因此密集型磁盘I/O是数据库引擎的一大特点。此外，完成磁盘I/O操作要消耗许多资源并且耗时较长，所以SQLServer侧重于提高I/O效率。缓冲区管理是实现高效I/O操作的关键环节。SQLServer2005的缓冲区管理组件由下列两种机制组成：用于访问及更新数据库页的缓冲区管理器和用于减少数据库文件I/O的缓冲区高速缓存（又称为“缓冲池”）。缓冲区管理的工作原理一个缓冲区就是一个8KB大小的内存页，其大小与一个数据页或索引页相当。因此，缓冲区高速缓存被划分为多个8KB页。缓冲区管理器负责将数据页或索引页从数据库磁盘文件读入缓冲区高速缓存中，并将修改后的页写回磁盘。页一直保留在缓冲区高速缓存中，直到已有一段时间未对其进行引用或者缓冲区管理器需要缓冲区读取更多数据。数据只有在被修改后才重新写入磁盘。在将缓冲区高速缓存中的数据写回磁盘之前，可对其进行多次修改。实验下面做一个简单的实验来看你是否已经掌握的上面的知识点：准备测试环境在一个SQL2005数据库中，执行下面脚本。简单来说，就是创建了2个表，注意这两个表，一个是存储的nchar(2019)的字段，一个是存储的nchar(2020)的字段。我们将来看这两个表在同样数据下，存储所花费的空间大小。由于缓存和物理存储的基本单位都是数据页，这个表物理存储的大小跟全部缓存的大小会是一样的。然后我们每个表填充20个数据。--创建2个测试表CREATETABLE[dbo].[Table_2019]([Data][nchar](2019)NOTNULL)CREATETABLE[dbo].[Table_2020]([Data][nchar](2020)NOTNULL)go--填充数据declare@iintset@i=0while(@i20)begininsertTable_2019(Data)values('')insertTable_2020(Data)values('')select@i=@i+1endgo这里我们用nchar数据类型，是因为：当指定了NOTNULL子句时，nchar数据类型是一种长度固定的数据类型。如果插入值的长度比ncharNOTNULL列的长度小，将在值的右边填补空格直到达到列的长度。例如，如果某列定义为nchar(10)，而要存储的数据是“music”，则SQLServer将数据存储为“music_____”，这里“_”表示空格。这样我们填充测试数据的脚本就非常简单。而且计算数据行所占的空间也非常简单。另外，我们建立的这两个表都没有索引，所以他们都是堆，有关估计在堆中存储数据所需的空间量请参看以下文章：完成准备工作后，我们来查看这两个所占空间的大小。在SQLServerManagementStudio中，我们选择测试数据库，然后在右键菜单中依次选择Reports--StandardReports--DiskUsagebyTopTables或者DiskUsagebyTable就可以看到下面统计数据。DiskUsagebyTopTables:[ghj_Demo]onGHJ1976-PC\SQLEXPRESSat2007/12/279:21:33Thisreportprovidesdetaileddataontheutilizationofdiskspacebytop1000tableswithintheDatabase.TableName#RecordsReserved(KB)Data(KB)Indexes(KB)Unused(KB)dbo.Table_202020200160832dbo.Table_20192013680848这两个表同样20条记录。Table_2020表数据占了160kb，即20个数据页。Table_2019表数据占了80kb，即10个数据页。为何会这样呢？Table_2020表的1个数据页只能放下1个数据行。Table_2019表的1个数据页只能放下2个数据行。这两个表的字段长度只差2个字节，但是物理存储却是一倍的差距。参考资料：SQLServer数据库中存储引擎深入探讨《MicrosoftSQLServer2005技术内幕：存储引擎》这本书电子版的一部分中关于“页和区”的描述聚集索引结构行溢出数据超过8KB缓冲区管理估计堆的大小和nvarchar(Transact-SQL)上吴家震主讲的微软SQL服务器Always-OnTech-nologies:高级索引策略录像下载地址:=1032364059&Culture=zh-CN注意,这个页面标示的是SharePoint2007网站性能调优,但是其实是高级索引策略,微软弄错文件了,害得我一个个下下来看,哪个是需要的录像.打印|张贴于2008-01-0214:35:00|Tag:数据库开发管理心得留言反馈#re:SQLServer索引基础知识(1)---记录数据的基本格式编辑nchar(2019)应该是占用4038个字节，nchar(2020)应该是占用4040个字节，sqlserver页的单个行中的最大数据量和开销是8,060字节，所以搞不懂为什么Table_2019这个表的每页能放两行（8060显然是小于4038*2）的呀，还望博主能解释一下。）2010-01-2811:48:09|[匿名:刺客]#回复:SQLServer索引基础知识(1)---记录数据的基本格式编辑后面的例子没有看懂！楼主能不能解释一下！！2008-02-1314:47:00|[匿名:yuanlf]#回复:SQLServ