sql语句大全之sql server 索引阐述系列三表的堆组

当前位置:

首页 > 数据库 > T-SQL >

sql语句大全之sql server 索引阐述系列三表的堆组

一. 概述

　　这一节来详细介绍堆组织，通过讲解堆的结构，堆与非聚集索引的关系，堆的应用场景，堆与聚集索引的存储空间占用，堆的页拆分现象，最后堆的使用建议，这几个维度来描述堆组织。在sqlserver里，表有二种组织方式，在表上没有创建聚集索引时，表就是堆组织, 有聚集索引就是B树组织。无论哪种组织方式，都可以在表上建多个非聚集索引。表的组织方式也称为HOBT。

　　之所以称为堆，是因为它的数据不按任何顺序进行组织，而是按分区组对数据进行组织。在一个堆中。用于保存数据之间的关系的唯一结构是索引分配映射(IAM , index allocation map)的位图页，上一章节中有说过页文件类型。

　　IAM位图页有指向数据页的指针，如果一个IAM不足以覆盖所有页，将维护一个IAM页的链，在查询数据时，先使用IAM页来遍历分配单元的数据。

　　堆结构在数据插入没有更改时是有存储顺序的，但一改动如修改删除，结构就会发生变化，因为没有特定的顺序来维护数据，所以在新增表中的行时，可以保存到任何数据页上。

　　Sql server内部使用文件页(PFS, Page Free Space)可用空间页，PFS位图来跟踪数据页中的可用空间, 以便可以快速找到有足够空间能容纳新行的页面，如果没有则分配一个新数据页面。

1.1 堆组织结构

　　在堆组织中对于一个select查询，首先查询IAM页，然后根据IAM页提供的信息，遍历每个区，把区内符合条件下的数据页返回，在堆中查询从上到下依次是Heap-->IAM-->区-->数据页。如下图所示：

1.2 堆上的非聚集索引

　　非聚集索引也可以结构化为一颗B树，与聚集索引类似，唯一区别就是非聚集索引的叶子层只包含索引键列和指向数据行的指针（行定位符）。如果是在堆上建立非聚集索引，则指针指向堆结构中的数据行

　　在堆中非聚集索引都有一个相对应的partition, 在这个partition下都有一个连接指向Root page根，在叶子层有会一个连接(文件号，页号，行号)指向真正的数据，真正的数据还是以堆结构存放的。在堆上建立的非聚集索引查询从上到下依次是Heap-->Root根-->root index中间层-->叶节点(文件号，页号，行号)-->数据页。如下图所示：

二. 堆应用场景

　　堆最常用的现象就是使用临时表，一般都很少会主动加clustered primary关键词，很多时间临时对象的应用也没有必要使用聚集索引。但如果临时表在会话里需要使用多次条件查询，排序等操作，聚集索引则少一部分开销。下面演示下：　　

--创建临时表堆
CREATE TABLE #tempWithHeap([SID] INT, model VARCHAR(50))
--插入数据
INSERT INTO #tempWithHeap
SELECT [sid],model FROM dbo.Product WHERE UpByMemberID=3000
--查询
SELECT Product.* FROM Product 
JOIN #tempWithHeap 
ON #tempWithHeap.[SID] = dbo.Product.[SID]

　　下图在执行计划里能看到临时表是表扫描方式

--创建临时表聚集
CREATE TABLE #tempWithCLUSTERED([SID] INT PRIMARY KEY CLUSTERED, model VARCHAR(50))
--插入
INSERT INTO #tempWithCLUSTERED
SELECT [sid],model FROM dbo.Product WHERE UpByMemberID=3000
--查询
SELECT Product.* FROM Product 
JOIN #tempWithCLUSTERED 
ON #tempWithCLUSTERED.[SID] = dbo.Product.[SID]

　　下图在执行计划里能看到临时表是聚集索引扫描方式

　　下面来演示堆和索引在排序下不同的执行计划

--临时表堆上排序
SELECT Product.SID FROM Product JOIN #tempWithHeap
ON #tempWithHeap.SID=Product.SID
ORDER BY #tempWithHeap.SID

　　在下图执行计划中排序显示开销15%

--临时表聚集索引上排序
SELECT Product.SID FROM Product JOIN #tempWithCLUSTERED
ON #tempWithCLUSTERED.SID=Product.SID
ORDER BY #tempWithCLUSTERED.SID

　　在下图执行计划中排序开销没有

三.堆上的页拆分

　　堆上的页拆分叫Forwarded records，是指更新数据后，原有页面空间大小已经无法存放该数据，sql server 会把这个数据移到堆中的新数据页里，并在新旧页中分别添加一个指针，标识这个数据在新旧页中的位置，从旧页指向新页的指针叫Forwarded records pointer 存放于旧页中, 从新页指向旧页的指针叫作back pointer 存放于新页中。

　　下面来演示下页拆分现象

--这里定义一个堆表，使用变长字段2500
CREATE TABLE HeapForwardedRecords
(
    ID  INT IDENTITY(1,1),
    DATA VARCHAR(2500)  
)
--插入数据，这里data字段插入2000，插入24条
INSERT INTO HeapForwardedRecords(data)
SELECT TOP 24 REPLICATE('X',2000) FROM sys.objects

--查看碎片信息
select OBJECT_NAME(object_id),object_id,
index_type_desc,page_count,record_count,
forwarded_record_count
from sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('HeapForwardedRecords')  ,null,null,'Detailed')

　　下图显示：共6页，24条数据，页拆分0条。（一行数据2000字节，一页存储4行， 24行共6页）

　　下面将data字段存储的2000字节，修改为2500字节，每页4行更新二行，原来一页存储4行(4*2000<8060)，现更新后就是(2*2000 +2*2500)>8060字节，原页就只能存储三行，这时堆上的页就会拆分。

--更新数据,12行受影响
UPDATE HeapForwardedRecords SET DATA=REPLICATE('X',2500)
WHERE ID%2=0

　　再次查看碎片信息，发现原来6页存储变为了9页， forwarded_record_count是指页拆分次数(是指向另一个数据位置的指针的记录数，在更新过程中，如果在原始位置存储的空间不足，将会出现此状态) 如下图：

　　总结：通过sys.dm_db_index_physical_stats 我们可以查询到碎片信息，page count的页数越多，内存消耗就越多。要整理碎片可以重建聚集索引。若要减少堆的区碎片，请对表创建聚集索引，然后删除该索引。更多碎片信息查看 https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms188917(v=sql.105)

如下图：forwarded_record_count为0了

四.堆存储结构对空间使用的影响　

4.1 等量数据的存储方式，使用DBCC SHOWCONFIG来查看

　　下面演示表结构相同情况下在堆组织和聚集索引组织二种方式，存储等量数据，来查看空间的占用。

--堆表
CREATE TABLE [dbo].[ProductWithDeap](
    [SID] [int] IDENTITY(1,1) NOT NULL,
    [Model] [nvarchar](100) NULL,
    [Brand] [nvarchar](100) NULL,
    [UpdateTime] [datetime] NULL,
    [UpByMemberID] [int] NULL,
    [UpByMemberName] [nvarchar](200) NULL)
  ON [PRIMARY]
--插入表堆数据(60703 行)
INSERT INTO  ProductWithDeap(Model,Brand,UpdateTime,UpByMemberID,UpByMemberName) 
SELECT Model,Brand,UpdateTime,UpByMemberID,UpByMemberName FROM dbo.Product 
WHERE  UpByMemberID=3000

--聚集索引
CREATE TABLE [dbo].[ProductWithClustered](
    [SID] [int] IDENTITY(1,1) NOT NULL,
    [Model] [nvarchar](100) NOT NULL,
    [Brand] [nvarchar](100) NULL,
    [UpdateTime] [datetime] NULL,
    [UpByMemberID] [int] NULL,
    [UpByMemberName] [nvarchar](200) NULL,
 CONSTRAINT [PK_ProductWithClustered] PRIMARY KEY CLUSTERED 
(
    [SID]  ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
--插入表聚集数据(60703 行)
INSERT INTO  ProductWithClustered(Model,Brand,UpdateTime,UpByMemberID,UpByMemberName) 
SELECT Model,Brand,UpdateTime,UpByMemberID,UpByMemberName FROM dbo.Product 
WHERE  UpByMemberID=3000