sqlserver数据存储.docx

1、sqlserver数据存储概述最近要分享一个课件就重新把这块知识整理了一遍出来，篇幅有点长，想要理解的透彻还是要上机实践。正文聚集索引12345678910111213141516171819202122-创建测试数据库CREATE DATABASE IxdataGOUSE IxdataGO-创建测试表CREATE TABLE Orders(ID INT PRIMARY KEY IDENTITY(1,1),NAME CHAR(80)NOT NULL,IDATE DATETIME NOT NULL DEFAULT(GETDATE();GO-插入1000条测试数据DECLARE ID INT=1W

2、HILE(ID=1000)BEGININSERT INTO Orders(NAME)VALUES(商品+CONVERT(NVARCHAR(20),ID)SET ID=ID+1 ENDGOSELECT * FROM Orders GO分析新创建的表的页的信息12345678-显示跟踪标志的状态DBCC TRACESTATUS-开启跟踪标志DBCC TRACEON(3604,2588)-DBCC TRACEOFF(3604,2588)-获取对象的数据页，结构：数据库、对象、显示DBCC IND(Ixdata,Orders,-1)/*1：显示所有分页的信息，包括IAM分页,数据分页,所有存在的LOB

3、分页和行溢出页,索引分页-1： 显示所有IAM、数据分页、及指定对象上全部索引的索引分页.-2： 显示指定对象的所有IAM分页0:显示所有IAM、数据分页.*/DBCC IND的表结构还可以通过另一种方法来测试：1234567select so.name, so.object_id, sp.index_id, internals.total_pages, internals.used_pages, internals.data_pages,first_iam_page, first_page, root_pagefrom sys.objects soinner join sys.partiti

4、ons sp on so.object_id = sp.object_idinner join sys.allocation_units sa on sa.container_id = sp.hobt_idinner join sys.system_internals_allocation_units internals on internals.container_id = sa.container_idwhere so.object_id = object_id(orders)最后三个字段分别是IAM页，根页，和第一个数据页；它们分别用16进制来表示，拿first_iam_page来分析，

5、首先将编码从右往左一个字节接着一个字节反过来排行（0X代表16进制），结果就是0X,00 01,00 00 00 50;前两个字节代表文件组号，最后4个字节代表页号。16进制的0001转换成10进制就是1；16进制的00 00 00 50转换成10进制就是5*16的1次方=5*16=80，所以第一个数据页是4*16+15=79，根页是5*16+9=89结果和前面的查询出来的结果是一样的。从表格的otal_pages,used_pages,data_pages得到的结果也和前面查询出来的结果是一致的，总分配了17个页，使用了15个页包括13个数据页+1个IAM页+1个索引页。手绘一张当前表格的聚

6、集索引体系结构图：分析索引页12-DBCC page的格式为(数据库，文件id,页号，显示)DBCC page(Ixdata,1,89,3)分析结果89页下面的子页总共有13页，每页80条记录，89索引页记录了每页的的键值的最小值，第一页就是id为1-80,第二页81-160，所以当你要找ID为150的数据的时候直接就可以去第90页里面找了。PAGE HEADER分析数据页通过这些数据我们基本上可以知道90页的基本情况了，包括它的字段长度，上一页、下一页，还有该页的所以记录（这里没有截图出来）.插入20万条记录分析索引结构123456789101112131415161718192021222

7、32425262728293031323334353637383940414243444546474849505152535455-插入20万条记录分析索引结构 DECLARE ID INT=1WHILE(ID=200000)BEGININSERT INTO Orders(NAME)VALUES(商品+CONVERT(NVARCHAR(20),ID)SET ID=ID+1 ENDCREATE TABLE Page(PageFID TINYINT, PagePID INT, IAMFID TINYINT, IAMPID INT, ObjectID INT,IndexID TINYINT,Part

8、itionNumber TINYINT,PartitionID BIGINT,iam_chain_type VARCHAR(30), PageType TINYINT, IndexLevel TINYINT,NextPageFID TINYINT,NextPagePID INT,PrevPageFID TINYINT,PrevPagePID INT);GOINSERT INTO Page EXEC(DBCC IND(Ixdata,Orders,-1)-查询索引页SELECT PageFID,PagePID,IAMFID,IAMPID,ObjectID,IndexID,PartitionNumb

9、er,PartitionID,iam_chain_type,PageType,IndexLevel,NextPageFID,NextPagePID,PrevPageFID,PrevPagePIDFROM Ixdata.dbo.PageWHERE PageType=2goselect so.name, so.object_id, sp.index_id, internals.total_pages, internals.used_pages, internals.data_pages,first_iam_page, first_page, root_pagefrom sys.objects so

10、inner join sys.partitions sp on so.object_id = sp.object_idinner join sys.allocation_units sa on sa.container_id = sp.hobt_idinner join sys.system_internals_allocation_units internals on internals.container_id = sa.container_idwhere so.object_id = object_id(orders)通过两种方法查询到的索引页的数量是一样的，下面的这种计算方法是2524

11、-2513-1(IAM页)=10，其中807页是root_page页它在第二级，其它的是中间级索引页页就是第一级；页可以通过下面的16进制计算出来，IAM=5*16=80，ROOT_PAGE=3*16*16+2*16+7=807再分析89页12-DBCC page的格式为(数据库，文件id,页号，显示)DBCC page(Ixdata,1,89,3)查询结果总共有269行，页就是269个数据页，orders表总共插入了201000条记录，一个页面存80条记录，就需要2513个页面和上面查询到的data_page是一样的。每个索引页存储269个数据页面就需要（select 2513*1.0/26

12、9除不尽加1）10个索引页，查询最后一个索引页2698发现它还没分页共存储了361条记录，总共8*269+361=2513手绘存储结构手绘的有点难看，但是意思差不多表达出来了。大型对象 (LOB) 列根据聚集索引中的数据类型，每个聚集索引结构将有一个或多个分配单元，将在这些单元中存储和管理特定分区的相关数据。每个聚集索引的每个分区中至少有一个 IN_ROW_DATA 分配单元。如果聚集索引包含大型对象 (LOB) 列，则它的每个分区中还会有一个 LOB_DATA 分配单元。如果聚集索引包含的变量长度列超过 8,060 字节的行大小限制，则它的每个分区中还会有一个 ROW_OVERFLOW_DA

13、TA 分配单元。12345678910111213141516171819-创建测试表CREATE TABLE Orderslob(ID INT PRIMARY KEY IDENTITY(1,1),NAME CHAR(80)NOT NULL,Product NVARCHAR(MAX) NOT NULL,IDATE DATETIME NOT NULL DEFAULT(GETDATE();GO-插入1000条测试数据DECLARE ID INT=1WHILE(ID=1000)BEGININSERT INTO Orderslob(NAME,Product)VALUES(CONVERT(NVARCHA

14、R(20),ID)+商品,REPLICATE(ID,2)SET ID=ID+1 END-REPLICATE(ID,200)GODBCC IND(Ixdata,Orderslob,1)12-查看2719数据页的信息DBCC page(Ixdata,1,2719,1)结果记录了每一条记录的偏移量。每个人在自己的电脑上面测试页面id会不一样，但是反应的结果是一样的。本篇文章是关于堆的存储结构。堆是不含聚集索引的表（所以只有非聚集索引的表也是堆）。堆的 sys.partitions 中具有一行，对于堆使用的每个分区，都有index_id= 0。默认情况下，一个堆有一个分区。当堆有多个分区时，每个分区有

15、一个堆结构，其中包含该特定分区的数据。例如，如果一个堆有四个分区，则有四个堆结构；每个分区有一个堆结构。根据堆中的数据类型，每个堆结构将有一个或多个分配单元来存储和管理特定分区的数据。每个堆中的每个分区至少有一个 IN_ROW_DATA 分配单元。如果堆包含大型对象 (LOB) 列，则该堆的每个分区还将有一个 LOB_DATA 分配单元。如果堆包含超过 8,060 字节行大小限制的可变长度列，则该堆的每个分区还将有一个 ROW_OVERFLOW_DATA 分配单元。有关分配单元的详细信息，sys.system_internals_allocation_units系统视图中的列first_iam

16、_page指向管理特定分区中堆的分配空间的一系列 IAM 页的第一页。SQL Server 使用 IAM 页在堆中移动。堆内的数据页和行没有任何特定的顺序，也不链接在一起。数据页之间唯一的逻辑连接是记录在 IAM 页内的信息。正文堆结构可以通过扫描 IAM 页对堆进行表扫描或串行读操作来找到容纳该堆的页的扩展盘区。因为 IAM 按扩展盘区在数据文件内存在的顺序表示它们，所以这意味着串行堆扫描连续沿每个文件进行。使用 IAM 页设置扫描顺序还意味着堆中的行一般不按照插入的顺序返回。页面的组成一个SQL数据页面=标头+数据行+剩余空间+行偏移表（如果表中存在大数据类型字段）+溢出表（如果存在）行偏

17、移123456789101112131415161718192021222324-测试数据CREATE TABLE Theap(ID INT IDENTITY(1,1) NOT NULL,NAME NVARCHAR(MAX) NOT NULL,IDATE DATETIME DEFAULT(GETDATE() NOT NULL)GO-插入1000条测试数据DECLARE ID INT=1WHILE(ID00110000 ；右边第一位开始是0位，第4位和第5位是1，由于在2008中null bit map总是存在的，所以只考虑第五位，即存在变长字段。1字节：00；状态位B在SQLServer200

18、5/2008中未启用，所以为002字节：1000;这两个字节是表示定长列的字节数，反过来排0010=1*16=16个字节，表中的定长列ID(4个字节)+IDATE(8个字节)+4个字节（默认加的）=16个字节N个字节：01000000 76ff7401 64a40000；这N个字节是定长字段的内容，总共12个字节2个字节：0300；表中的字段数，由于表中只有3个字段所以用0300表示1个字节：b810111000;这个字节表示主要是判断对应的字段是否允许为空1代表允许为空，前三个字段都不允许为空，而且表只有三个字段所以不用看后面。2个字节：01 00；这个字段表示变长列的个数，根据刚才说的方法

19、倒过来00 01=1个字段，表中页只有NAME字段是变长字段。2个字节*变长字段的个数：1900；由于表中只有一个变长字段，所以只有两个字节，表示第一个变长列的终止位置=25N个字节：变长字段的内容，3100转换成字符刚好是1在线16进制转字符：查询1234567891011SELECT ID,NAME,IDATEFROM Ixdata.dbo.TheapWHERE NAME=1SELECT ID,NAME,IDATEFROM Ixdata.dbo.TheapWHERE NAME=900分析查询可以看出无论你查询的是1还是900,都是扫描一次，逻辑读取4次，因为存在4个页，用ID去查也是一样.

20、行溢出12345678910111213141516171819202122CREATE TABLE Theapover(ID INT IDENTITY(1,1) NOT NULL,NAME VARCHAR(5000) NOT NULL,NAME1 VARCHAR(5000) NOT NULL,IDATE DATETIME DEFAULT(GETDATE() NOT NULL)GO-插入1000条测试数据DECLARE ID INT=1WHILE(ID0 的 sys.partitions 中都有对应的一行。默认情况下，一个非聚集索引有单个分区。如果一个非聚集索引有多个分区，则每个分区都有一个包

21、含该特定分区的索引行的 B 树结构。例如，如果一个非聚集索引有四个分区，那么就有四个 B 树结构，每个分区中一个。根据非聚集索引中数据类型的不同，每个非聚集索引结构会有一个或多个分配单元，在其中存储和管理特定分区的数据。每个非聚集索引至少有一个针对每个分区的 IN_ROW_DATA 分配单元（存储索引 B 树页）。如果非聚集索引包含大型对象 (LOB) 列，则还有一个针对每个分区的 LOB_DATA 分配单元。此外，如果非聚集索引包含的可变长度列超过 8,060 字节行大小限制，则还有一个针对每个分区的 ROW_OVERFLOW_DATA 分配单元。有关分配单元的详细信息，请参阅表组织和索引组织。B 树的页集合由sys.system_internals_allocation_units系统视图中的root_page指针定位。要很好的理解这篇文章的内容之前需要先阅