DML和索引内部结构变化,索引阐述系列三

一.   概述

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

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

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

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

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

1.1 堆组织结构

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

www.350.vip 1

1.2 堆上的非聚集索引

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

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

www.350.vip 2

3.重组和重建索引

重组索引可以将索引的叶级进行重新排列并整理。重组索引使用的是原有的叶级页,重组完成后如果有空页则会释放空页。因为索引重组没有涉及创建索引的过程,因此重组语句中不能指定填充因子,只能默认使用创建索引时指定的填充因子进行重组。重组时会根据内部算法(冒泡排序算法)合理的移动行到合理的位置,尽可能的填充页面空间,并使页的逻辑顺序和物理顺序尽量保持一致,这样可以减少内部碎片和外部碎片。

和重组索引相比,重建索引更彻底。重建索引会为索引B树(不只是叶级)重新分配一套页面,并释放旧页。重建索引实现的是删除旧碎片(其实是释放旧的页),但是并不能保证重建后完全无碎片。

例如,新分配的页面之间本身就不连续,或者分配页面的时候正好有其它进程(例如多个CPU并行重建索引时)抢占了中间的页面导致两个进程的页面有交错区域。实际上B树结构中拆分出新层次的页(如第一个中间页或者新的根页)时,都会为新层次的页分配一个中间的页码,如某聚集索引重建最初只有一个页码为208的叶级页,出现第二个叶级页的同时会分配一个根页,根页页码为209,第二个叶级页页码为210,这样根页页码就挤在了叶级页的中间,这也是外部碎片只能无限趋于0但不可能完全被删除的原因之一。

五.堆的使用建议

  5.1堆需要考虑点

            过多的产生forwarded records 来维护堆表,产生额外的io操作。

       5.2 堆选择理由

              高频率的增删操作。

              键值经常改变,特别在索引上的位置改变。

              插入大量数据列到表中。

              主键值并不自增或者唯一。

1.修改数据对索引结构的影响

合适的索引对查询性能和效率的提升是巨大的,但是万事有利有弊,拥有索引的表在增、删、改记录时需要去维护索引。如何让增、删、改更快速更高效?这就需要了解数据修改时对索引结构会产生什么影响。

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

 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

www.350.vip 3

存储方式 使用页面数量 使用区数量
堆组织  517  69
聚集索引  518  66

4.2 删除数据后,对空间的释放情况

  delete  from ProductWithDeap

       delete from ProductWithclustered

www.350.vip 4

存储方式 剩余空间数量 剩余区数量
堆组织  50  11
聚集索引  1  1

  使用delete后我们发现,建立堆组织的空间不会马上释放掉,聚集索引能很好的释放空间,但也存在1页未释放,如果完全释放使用truncate
table。

     
总结:当我们考虑表是用堆组织还是用聚集索引时,通过上面的演示我们知道,聚集索引的叶子层就是数据本身,并不会因为建立聚集索引而消耗过多的空间(注意非聚集索引会占用空间,不管是建立在堆组织上还是聚集索引上),而且能够更好的管理数据和空间的释放。除非特殊情况(后面有选择堆的理由)

1.4 更新行

更新行可能出现行移动和页拆分。行移动又可能是本页移动和页间移动,这种情况是非在位更新;还可能是原地更新,即不会出现任何移动,这种情况称为在位更新。

更新行的具体内部变化已经在刚才的页拆分和行移动段落里分情况讨论了,这里就不赘述了。

三.堆上的页拆分

   堆上的页拆分叫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页)

www.350.vip 5

  下面将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是指页拆分次数(是指向另一个数据位置的指针的记录数,在更新过程中,如果在原始位置存储的空间不足,将会出现此状态)
如下图:

www.350.vip 6

 

  总结:通过sys.dm_db_index_physical_stats
我们可以查询到碎片信息,page count的页数越多,内存消耗就越多。
要整理碎片可以重建聚集索引。若要减少堆的区碎片,请对表创建聚集索引,然后删除该索引。更多碎片信息查看

如下图:forwarded_record_count为0了 

www.350.vip 7

1.3 删除行

1.删除堆的数据页

堆表数据删除后不释放空间,留下slot但slot不指向页中的位置,也就是像slot

0x0这样
。这时候如果有新记录要存放就可以“见缝插针”,并将原来没有指向的slot指向这一插入的行。

下面的图中展示的是某个堆的页中记录被删除后的偏移信息,删除的是原来slot
0到slot 6的记录。

 www.350.vip 8

如果想要释放堆中的空间,可以使用TRUNCATE删除整个表中数据;或者在DELETE时加上WITH(TABLOCK)选项(如DELETE
FROM WITH(TABLOCK) table_name WHERE…)来按页释放空间;也可以先在堆中建立聚集索引,然后删除数据再删除聚集索引。

2.删除聚集表中记录

聚集索引的叶级和聚集表中非聚集索引的叶级记录被删除后会在原位置留下虚影记录(ghost_record),它们不是真正的被删除,只是在记录上做了虚影标记。该标记可以从页的标头信息查看,看下图,图中只整理了某页与虚影记录相关的信息。虚影记录由后台进程定时清理,清理后空间被释放。

www.350.vip 9

因为叶级还有虚影,所以非叶级仍然需要指向它们,因此聚集索引的非叶级和聚集表中非聚集索引的非叶级记录都不会被删除,而且它们不是虚影,而是原原本本的原记录。直到后台进程清除虚影后,叶级页被释放,指针也被释放,当非叶级页上没有数据了也直接删除并释放空间。

3.删除堆中非聚集索引的叶级和非叶级记录

因为堆中非聚集索引的行定位器指向堆中行位置,因此删除堆中行的同时会释放指针并删除叶级页中对应的记录,如果删除的记录足够多,还会删除非叶级的记录。不过删除非聚集索引的叶级和非叶级会直接释放空间,而不是和删除堆的数据页一样仍然占用空间。

发表评论

电子邮件地址不会被公开。 必填项已用*标注