你的位置:首页 > 数据库

[数据库]SQL Server 深入解析索引存储(中)


标签:SQL SERVER/MSSQL SERVER/数据库/DBA/索引体系结构/堆

概述

     本篇文章是关于堆的存储结构。堆是不含聚集索引的表(所以只有非聚集索引的表也是堆)。堆的 sys.partitions 中具有一行,对于堆使用的每个分区,都有 index_id = 0。默认情况下,一个堆有一个分区。当堆有多个分区时,每个分区有一个堆结构,其中包含该特定分区的数据。例如,如果一个堆有四个分区,则有四个堆结构;每个分区有一个堆结构。根据堆中的数据类型,每个堆结构将有一个或多个分配单元来存储和管理特定分区的数据。每个堆中的每个分区至少有一个 IN_ROW_DATA 分配单元。如果堆包含大型对象 (LOB) 列,则该堆的每个分区还将有一个 LOB_DATA 分配单元。如果堆包含超过 8,060 字节行大小限制的可变长度列,则该堆的每个分区还将有一个 ROW_OVERFLOW_DATA 分配单元。有关分配单元的详细信息,

sys.system_internals_allocation_units 系统视图中的列 first_iam_page 指向管理特定分区中堆的分配空间的一系列 IAM 页的第一页。SQL Server 使用 IAM 页在堆中移动。堆内的数据页和行没有任何特定的顺序,也不链接在一起。数据页之间唯一的逻辑连接是记录在 IAM 页内的信息。

 

正文

堆结构

可以通过扫描 IAM 页对堆进行表扫描或串行读操作来找到容纳该堆的页的扩展盘区。因为 IAM 按扩展盘区在数据文件内存在的顺序表示它们,所以这意味着串行堆扫描连续沿每个文件进行。使用 IAM 页设置扫描顺序还意味着堆中的行一般不按照插入的顺序返回。

 

 页面的组成

 

一个SQL数据页面=标头+数据行+剩余空间+行偏移表(如果表中存在大数据类型字段)+溢出表(如果存在) 

行偏移

---测试数据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(@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,Theap,-1)SELECT * FROM sys.system_internals_allocation_units WHERE container_id=72057594039566336

分析114页

DBCC page(Ixdata,1,114,3)

整个数据页有四部分组成

1.页面在内存中的映射信息(BUFFER:)

2. 页头部分(PAGE HEADER):记录了页号、页类型、记录数,LSN及其他信息,在上一章已经讲过

3. 数据部分(DATA):以16进制格式存储行记录(从第96个字节开始)

4. 行偏移部分(OFFSET TABLE):以倒序的顺序记录了行记录的指针位置,这个使用2的显示方式比较明显看出

看看一行记录在页面中是怎样记录的

 

 

 

00000000: 30001000 01000000 76ff7401 64a40000 †0.......v.t.d...
00000010: 0300b801 00190031 00†††††††††††††††††.......1.

1字节:30>00110000 ;右边第一位开始是0位,第4位和第5位是1,由于在2008中null bit map总是存在的,所以只考虑第五位,即存在变长字段。 

1字节:00;状态位B在SQLServer2005/2008中未启用,所以为00

2字节:1000;这两个字节是表示定长列的字节数,反过来排0010=1*16=16个字节,表中的定长列ID(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;由于表中只有一个变长字段,所以只有两个字节,表示第一个变长列的终止位置=25

N个字节:变长字段的内容,3100转换成字符刚好是‘1’

在线16进制转字符: http://www.bejson.com/tools/0x/

 

查询

SELECT [ID]   ,[NAME]   ,[IDATE] FROM [Ixdata].[dbo].[Theap] WHERE NAME='1'   SELECT [ID]   ,[NAME]   ,[IDATE] FROM [Ixdata].[dbo].[Theap] WHERE NAME='900'

分析查询可以看出无论你查询的是'1'还是'900',都是扫描一次,逻辑读取4次,因为存在4个页,用ID去查也是一样.

 

行溢出

CREATE 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(@ID<=1000)BEGININSERT INTO Theapover(NAME,NAME1)VALUES(REPLICATE(1,5000),REPLICATE(2,5000))SET @ID=@ID+1 ENDGOSELECT * FROM TheapoverORDER BY IDGODBCC IND(Ixdata,Theapover,-1)SELECT * FROM sys.system_internals_allocation_units WHERE container_id=72057594039828480

 

总共插入了1000条记录,一行占一页再加上两个IAM页刚好2002页,

存在两个IAM页,分别是3281和3283页,还有一个比较特殊的页3280页,3280页是溢出数据里面的根页,等一下看一下这页的数据。

 

分析IAM页

DBCC page(Ixdata,1,3283,3)

分析溢出页

DBCC page(Ixdata,1,3282,3)

 

测试查询

 SELECT [ID]   ,[NAME]   ,[NAME1]   ,[IDATE] FROM [Ixdata].[dbo].[Theapover] where ID=500

当我继续往堆表里插入数据直到表超过4G的时候会有新的IAM页生成,而且IAM页之间存在链关系(数据页)。

查询发现新生成的3135IAM页种的数据页的行溢出指向的是新生成的511256IAM页的溢出页,这样的话IAM页之间的链关系对查询效率貌似没有什么改善的好处。

1. IAM用于查找分配给heap的所有数据页信息,IAM页中记录了所有的页面的页id。

2. 对于大多数较小的heap表来说,仅需要一个IAM页就可以管理其页面。

3. 若heap表大于4GB或包含LOB数据类型的话,则会包含多个IAM页面。

4. 当查询要获取heap表的所有记录时,SQL Server使用IAM页来扫描heap表


总结

  堆表的页是没有规律的不存在页链,所以导致堆表的查询效率很差,当查询一个10万条记录的堆表逻辑读取就需要10万次,如果堆表的数据量很大需要多次进行物理读获取页面的时候对于IO的消耗是非常大的,建议表都应该建聚集索引。

 

如果文章对大家有帮助,希望大家能给个推荐,谢谢!!!

 


备注:

    作者:pursuer.chen

    博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接,否则保留追究责任的权利。

《欢迎交流讨论》