sqlserver数据存储

1、概述最近要分享一个课件就重新把这块知识整理了一遍出来，篇幅有点长，想要理解的透彻还是要上机实践。正文聚集索引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=1WHILE(ID<=1

2、000)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分页,数据分页,所

3、有存在的LOB分页和行溢出页,索引分页-1： 显示所有IAM、数据分页、及指定对象上全部索引的索引分页.-2： 显示指定对象的所有IAM分页0:显示所有IAM、数据分页.*/DBCC IND的表结构还可以通过另一种方法来测试：1234567select , 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 joi

4、n 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')最后三个字段分别是IAM页，根页，和第一个数据页；它们分别用16进制

5、来表示，拿first_iam_page来分析，首先将编码从右往左一个字节接着一个字节反过来排行（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个页包括1

6、3个数据页+1个IAM页+1个索引页。手绘一张当前表格的聚集索引体系结构图：分析索引页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万条记录分析索引结构 1

7、2345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455-插入20万条记录分析索引结构 DECLARE ID INT=1WHILE(ID<=200000)BEGININSERT INTO Orders(NAME)VALUES('商品'+CONVERT(NVARCHAR(20),ID)SET ID=ID+1 END CREATE TABLE Page(  PageFID   

8、;      TINYINT,   PagePID         INT,     IAMFID          TINYINT,   IAMPID          INT,   Ob

9、jectID        INT,  IndexID         TINYINT,  PartitionNumber TINYINT,  PartitionID     BIGINT,  iam_chain_type  VARCHAR(30),      Page

10、Type        TINYINT,   IndexLevel      TINYINT,  NextPageFID     TINYINT,  NextPagePID     INT,  PrevPageFID     TINYINT,  PrevPag

11、ePID     INT);GOINSERT INTO Page EXEC('DBCC IND(Ixdata,Orders,-1)') -查询索引页SELECT  PageFID      ,PagePID      ,IAMFID      ,IAMPID      ,Object

12、ID      ,IndexID      ,PartitionNumber      ,PartitionID      ,iam_chain_type      ,PageType      ,IndexLevel  

13、;    ,NextPageFID      ,NextPagePID      ,PrevPageFID      ,PrevPagePID  FROM Ixdata.dbo.Page  WHERE PageType=2  go select , so.object_id, sp.index_

14、id, internals.total_pages, internals.used_pages, internals.data_pages,first_iam_page,  first_page, root_pagefrom sys.objects soinner 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_

15、units internals on internals.container_id = sa.container_idwhere so.object_id = object_id('orders')通过两种方法查询到的索引页的数量是一样的，下面的这种计算方法是2524-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 p

16、age(Ixdata,1,89,3)查询结果总共有269行，页就是269个数据页，orders表总共插入了201000条记录，一个页面存80条记录，就需要2513个页面和上面查询到的data_page是一样的。每个索引页存储269个数据页面就需要（select 2513*1.0/269除不尽加1）10个索引页，查询最后一个索引页2698发现它还没分页共存储了361条记录，总共8*269+361=2513手绘存储结构 手绘的有点难看，但是意思差不多表达出来了。大型对象 (LOB) 列根据聚集索引中的数据类型，每个聚集索引结构将有一个或多个分配单元，将在这些单元中存储和管理特定分区的相关

17、数据。每个聚集索引的每个分区中至少有一个 IN_ROW_DATA 分配单元。如果聚集索引包含大型对象 (LOB) 列，则它的每个分区中还会有一个 LOB_DATA 分配单元。如果聚集索引包含的变量长度列超过 8,060 字节的行大小限制，则它的每个分区中还会有一个 ROW_OVERFLOW_DATA 分配单元。12345678910111213141516171819-创建测试表CREATE TABLE Orderslob(ID INT PRIMARY KEY IDENTITY(1,1),NAME CHAR(80)NOT NULL,Product NVARCHAR(MAX) NOT NULL,

18、IDATE DATETIME NOT NULL DEFAULT(GETDATE();GO-插入1000条测试数据DECLARE ID INT=1WHILE(ID<=1000)BEGININSERT INTO Orderslob(NAME,Product)VALUES(CONVERT(NVARCHAR(20),ID)+'商品',REPLICATE(ID,2)SET ID=ID+1 END-REPLICATE(ID,200)GO DBCC IND(Ixdata,Orderslob,1)12-查看2719数据页的信息DBCC page(Ixdata,1,2719,1)

19、结果记录了每一条记录的偏移量。每个人在自己的电脑上面测试页面id会不一样，但是反应的结果是一样的。本篇文章是关于堆的存储结构。堆是不含聚集索引的表（所以只有非聚集索引的表也是堆）。堆的 sys.partitions 中具有一行，对于堆使用的每个分区，都有 index_id = 0。默认情况下，一个堆有一个分区。当堆有多个分区时，每个分区有一个堆结构，其中包含该特定分区的数据。例如，如果一个堆有四个分区，则有四个堆结构；每个分区有一个堆结构。根据堆中的数据类型，每个堆结构将有一个或多个分配单元来存储和管理特定分区的数据。每个堆中的每个分区至少有一个 IN_ROW_DATA 分

20、配单元。如果堆包含大型对象 (LOB) 列，则该堆的每个分区还将有一个 LOB_DATA 分配单元。如果堆包含超过 8,060 字节行大小限制的可变长度列，则该堆的每个分区还将有一个 ROW_OVERFLOW_DATA 分配单元。有关分配单元的详细信息，sys.system_internals_allocation_units 系统视图中的列 first_iam_page 指向管理特定分区中堆的分配空间的一系列 IAM 页的第一页。SQL Server 使用 IAM 页在堆中移动。堆内的数据页和行没有任何特定的顺序，也不链接在一起。数据页之间唯一的逻辑连接是记录在

21、 IAM 页内的信息。 正文堆结构可以通过扫描 IAM 页对堆进行表扫描或串行读操作来找到容纳该堆的页的扩展盘区。因为 IAM 按扩展盘区在数据文件内存在的顺序表示它们，所以这意味着串行堆扫描连续沿每个文件进行。使用 IAM 页设置扫描顺序还意味着堆中的行一般不按照插入的顺序返回。 页面的组成 一个SQL数据页面=标头+数据行+剩余空间+行偏移表（如果表中存在大数据类型字段）+溢出表（如果存在）行偏移123456789101112131415161718192021222324-测试数据CREATE TABLE Theap(ID INT IDENTITY(1,1)

22、 NOT NULL,NAME NVARCHAR(MAX) NOT NULL,IDATE DATETIME DEFAULT(GETDATE() NOT NULL)GO-插入1000条测试数据DECLARE ID INT=1WHILE(ID<=1000)BEGININSERT INTO Theap(NAME)VALUES(ID)SET ID=ID+1 ENDGOSELECT * FROM Theap -开启跟踪标志DBCC TRACEON(3604,2588)-DBCC TRACEOFF(3604,2588)-获取对象的数据页，结构：数据库、对象、显示DBCC IND(Ixdata

23、,Theap,-1) SELECT * FROM sys.system_internals_allocation_units WHERE container_id=72057594039566336分析114页1DBCC page(Ixdata,1,114,3)整个数据页有四部分组成1.页面在内存中的映射信息（BUFFER:）2. 页头部分（PAGE HEADER）：记录了页号、页类型、记录数，LSN及其他信息，在上一章已经讲过3. 数据部分（DATA）：以16进制格式存储行记录（从第96个字节开始）4. 行偏移部分(OFFSET TABLE)：以倒序的顺序记录了行记录的指针位置，这

24、个使用2的显示方式比较明显看出看看一行记录在页面中是怎样记录的 00000000: 30001000 01000000 76ff7401 64a40000 0.v.t.d00000010: 0300b801 00190031 00.1.1字节：30>00110000 ；右边第一位开始是0位，第4位和第5位是1，由于在2008中null bit map总是存在的，所以只考虑第五位，即存在变长字段。1字节：00；状态位B在SQLServer2005/2008中未启用，所以为002字节：1000;这两个字节是表示定长列的字节数，反过来排0010=1*16=16个字节，表中的定长列ID

25、(4个字节)+IDATE(8个字节)+4个字节（默认加的）=16个字节N个字节：01000000 76ff7401 64a40000；这N个字节是定长字段的内容，总共12个字节2个字节：0300；表中的字段数，由于表中只有3个字段所以用0300表示1个字节：b8>10111000;这个字节表示主要是判断对应的字段是否允许为空1代表允许为空，前三个字段都不允许为空，而且表只有三个字段所以不用看后面。2个字节：01 00；这个字段表示变长列的个数，根据刚才说的方法倒过来00 01=1个字段，表中页只有NAME字段是变长字段。2个字节*变长字段的个数：1900；由于表中只有一个变长字段，所以只

26、有两个字节，表示第一个变长列的终止位置=25N个字节：变长字段的内容，3100转换成字符刚好是1在线16进制转字符： 查询1234567891011SELECT ID      ,NAME      ,IDATE  FROM Ixdata.dbo.Theap  WHERE NAME='1'   SELECT ID      

27、;,NAME      ,IDATE  FROM Ixdata.dbo.Theap  WHERE NAME='900'分析查询可以看出无论你查询的是1还是900,都是扫描一次，逻辑读取4次，因为存在4个页，用ID去查也是一样. 行溢出12345678910111213141516171819202122CREATE TABLE Theapover(ID INT IDENTITY(1,1) NOT NULL,NAME VARCHAR(5000) NOT NULL,NAME

28、1 VARCHAR(5000) NOT NULL,IDATE DATETIME DEFAULT(GETDATE() NOT NULL)GO-插入1000条测试数据DECLARE ID INT=1WHILE(ID<=1000)BEGININSERT INTO Theapover(NAME,NAME1)VALUES(REPLICATE(1,5000),REPLICATE(2,5000)SET ID=ID+1 ENDGOSELECT * FROM TheapoverORDER BY IDGO DBCC IND(Ixdata,Theapover,-1) SELECT * FR

29、OM sys.system_internals_allocation_units WHERE container_id=72057594039828480总共插入了1000条记录，一行占一页再加上两个IAM页刚好2002页，存在两个IAM页，分别是3281和3283页，还有一个比较特殊的页3280页，3280页是溢出数据里面的根页，等一下看一下这页的数据。分析IAM页1DBCC page(Ixdata,1,3283,3)分析溢出页1DBCC page(Ixdata,1,3282,3)注意:不是堆页和溢出页就只能一一对应，由于当前表中堆页容纳不下两条记录所以就导致了堆页和溢出页一样，当堆页可以存

30、多条记录的时候就会出现一个堆页对应多个溢出页。测试查询123456SELECT  ID    ,NAME    ,NAME1    ,IDATEFROM Ixdata.dbo.Theapoverwhere ID=500当我继续往堆表里插入数据直到表超过4G的时候会有新的IAM页生成，而且IAM页之间存在链关系（数据页）。查询发现新生成的3135IAM页种的数据页的行溢出指向的是新生成的511256IAM页的溢出页，这样的话IAM页之间的链关系对查询效率貌似没有什么改

31、善的好处。1. IAM用于查找分配给heap的所有数据页信息，IAM页中记录了所有的页面的页id。2. 对于大多数较小的heap表来说，仅需要一个IAM页就可以管理其页面。3. 若heap表大于4GB或包含LOB数据类型的话，则会包含多个IAM页面。4. 当查询要获取heap表的所有记录时，SQL Server使用IAM页来扫描heap表总结堆表的页是没有规律的不存在页链，所以导致堆表的查询效率很差,当查询一个10万条记录的堆表逻辑读取就需要10万次，如果堆表的数据量很大需要多次进行物理读获取页面的时候对于IO的消耗是非常大的，建议表都应该建聚集索引。概述非聚集索引与聚集索引具有相同的 B 树

32、结构，它们之间的显著差别在于以下两点：· 基础表的数据行不按非聚集键的顺序排序和存储。· 非聚集索引的叶层是由索引页而不是由数据页组成。既可以使用聚集索引来为表或视图定义非聚集索引，也可以根据堆来定义非聚集索引。非聚集索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。非聚集索引行中的行定位器或是指向行的指针，或是行的聚集索引键，如下所述：· 如果表是堆（意味着该表没有聚集索引），则行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID (RID)。· 如果表有聚集索引或索

33、引视图上有聚集索引，则行定位器是行的聚集索引键。如果聚集索引不是唯一的索引，SQL Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。对于索引使用的每个分区，非聚集索引在 index_id >0 的 sys.partitions 中都有对应的一行。默认情况下，一个非聚集索引有单个分区。如果一个非聚集索引有多个分区，则每个分区都有一个包含该特定分区的索引行的 B 树结构。例如，如果

34、一个非聚集索引有四个分区，那么就有四个 B 树结构，每个分区中一个。根据非聚集索引中数据类型的不同，每个非聚集索引结构会有一个或多个分配单元，在其中存储和管理特定分区的数据。每个非聚集索引至少有一个针对每个分区的 IN_ROW_DATA 分配单元（存储索引 B 树页）。如果非聚集索引包含大型对象 (LOB) 列，则还有一个针对每个分区的 LOB_DATA 分配单元。此外，如果非聚集索引包含的可变长度列超过 8,060 字节行大小限制，则还有一个针对每个分区的 ROW_OVERFLOW_DATA 分配单元。有关分配单元的详细信息，请参阅表组织和索引组织。B 树的页集合由 sys.sys

35、tem_internals_allocation_units 系统视图中的 root_page 指针定位。 要很好的理解这篇文章的内容之前需要先阅读我前面写的上中部分的两篇文章：SQL Server 深入解析索引存储(中)SQL Server 深入解析索引存储(上)正文非聚集索引结构 生成测试数据 12345678910111213141516171819202122232425262728CREATE TABLE Torder(ID INT IDENTITY(1,1) NOT NULL,NAME CHAR(100) NOT NULL

36、,pro VARCHAR(8000) NULL,Statu INT NOT NULL,IDATE DATETIME DEFAULT(GETDATE()GO-插入1000条测试数据DECLARE ID INT=1WHILE(ID<=1000)BEGININSERT INTO Torder(NAME,pro,Statu)VALUES('商品'+CONVERT(CHAR(20),ID),REPLICATE(1,8000),LEFT(ID,1)SET ID=ID+1 ENDGO-创建非聚集索引CREATE INDEX IX_Torder ON Torder(NAME,Statu)

37、INCLUDE(IDATE) SELECT DISTINCT , so.object_id,sp.index_id,internals.type_desc,internals.total_pages, internals.used_pages, internals.data_pages,first_iam_page,  first_page, root_pageFROM sys.objects soINNER JOIN sys.partitions sp ON so.object_id = sp.object_idINNER JOIN sys.allocati

38、on_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('Torder') 由于创建的表只有非聚集索引，所以整个表的页存储中有三部分数据：堆页面、溢出页面、索引页面；堆中共有20个数据页和一个IAM页；溢出单元有1001个页面包括一个IAM页；索引中共有20个页其中18个数据页

39、一个ROOT页和一个IAM页.一个堆页对应多个溢出页，因为Pro有8000个字节所以一行占一页，而表的其它字段只有116个字节一个堆页可以存50条记录，所以并不是一个溢出页就唯一对应一个堆页分析页的存储信息12345-开启跟踪标志DBCC TRACEON(3604,2588)-DBCC TRACEOFF(3604,2588)-获取对象的数据页，结构：数据库、对象、显示DBCC IND(Ixdata,Torder,-1)上一章中已经讲过了堆页面和溢出页面，所以现在就讲非聚集索引页看过前面的文章应该一眼就能看出1281页是ROOT页，现在就分析1281页分析非聚集索引根页1DBCC page(Ix

40、data,1,1281,3)现在来分析行定位指针是怎样的：0x6801000001002F00除去开头的16进制标示，剩下总共8个字节，从右往左其中行号2个字节，文件标示ID2个字节，剩下的4个字节就是页号了，所以行号（002f）=47文件页（0001）=1页号（00000168）=360页现在查看360页的信息1DBCC page(Ixdata,1,360,3)47行的记录正好是“商品150”分析非聚集索引索引页 通过对比会发现索引页比根页多出了索引包含列值和键的哈希值,这个里面的keyhashvalue应该是NAME字段的值通过某种方法算出来的，应该不包括statu字段，这里只是

41、猜测通过平时的查询你会产生这样的猜测，当你where条件只有statu的时候是不走索引查找的。测试简单的查询这里的商品150和商品153都是1280页中的记录，1280页是索引页，其中商品150是该页的第一条记录123456789101112131415161718192021222324252627SET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN TRAN  SELECT ID      ,NAME      

42、;,pro      ,Statu      ,IDATE  FROM Ixdata.dbo.Torder  WHERE NAME='商品153' -COMMIT  另开一个窗口SELECTrequest_session_id,gram_name,DB_NAME(c.dbid) AS dbname,resource_type,request_status,request_mode,resou

43、rce_description,OBJECT_NAME(p.object_id) AS objectname,p.index_idFROM sys.dm_tran_locks AS a LEFT JOIN sys.partitions AS pON a.resource_associated_entity_id=p.hobt_idLEFT JOIN sys.sysprocesses AS c ON a.request_session_id=c.spid WHERE c.dbid=DB_ID('Ixdata') AND a.request_session_id=58&#

44、160; -要查询申请锁的数据库ORDER BY request_session_id,resource_type从上面的查询过程可以知道页面总共读取了三次(索引叶一次堆页一次溢出页一次)。1234567891011SET TRANSACTION ISOLATION LEVEL REPEATABLE READGO        BEGIN TRAN   SELECT       ID    

45、60; ,NAME      ,pro      ,Statu      ,IDATE  FROM Ixdata.dbo.Torder  WHERE NAME='商品150'通过对比查询商品150和商品153可以看到如果查找的页面的第一条记录，它需要再读取该索引页的前一个页面，如果该索引页是第一页就无需再读除本身的其他索引页了，文章写到后面反过来思考才

46、知道为什么非聚集索引还需要多查找一个页面了。因为非聚集索引是允许存在重复值所以才需要再往前查找，如果前面一个页查找不到则结束，如果前面一个页还没查完会再往前一个页进行查，当然查询商品153的时候就已经判断了前一条记录的键值是不一样的否则也是要再查询前一个页，这也是非聚集索引的一个特殊情况吧！索引扫描123456789101112131415update Torderset statu=100where id=1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READGO      &#

47、160; BEGIN TRAN   SELECT       ID      ,NAME      ,pro      ,Statu      ,IDATE  FROM Ixdata.dbo.Torder  WHERE Statu

48、=100该查询总共扫描了18个索引页+1个堆页+1个溢出页.创建聚集索引123456789101112ALTER TABLE dbo.Torder ADD CONSTRAINT    PK_Torder PRIMARY KEY CLUSTERED     (    ID    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY SELECT DISTINCT , so.object_id,sp.index_id,internals.type_desc,internals.total_pages, internals.used_pages, internals.data_pages,first_iam_page,  first_page, root_pageFROM sys.objects soINNER JOIN sys.partitions sp ON so.object_id =