SQL Server 深入解析索引存储(中)

原文: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=1
WHILE(@ID<=1000)
BEGIN
INSERT INTO Theap(NAME)VALUES((@ID))
SET @ID=@ID+1
END
GO
SELECT * 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=1
WHILE(@ID<=1000)
BEGIN
INSERT INTO Theapover(NAME,NAME1)VALUES(REPLICATE(1,5000),REPLICATE(2,5000))
SET @ID=@ID+1
END
GO
SELECT * FROM Theapover
ORDER BY ID
GO

DBCC 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的消耗是非常大的，建议表都应该建聚集索引。

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