图片 15

DML和索引内部结构变化

Posted by

一.   概述

  这一节来详细介绍堆组织,通过讲解堆的结构,堆与非聚集索引的关系,堆的应用场景,堆与聚集索引的存储空间占用,堆的页拆分现象,最后堆的使用建议
,这几个维度来描述堆组织。在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

1.2 堆上的非聚集索引

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

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

图片 2

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

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

二. 堆应用场景

  堆最常用的现象就是使用临时表,一般都很少会主动加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]

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

图片 3

--创建临时表聚集
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]

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

  图片 4

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

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

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

图片 5

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

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

图片 6

1.1页拆分和行移动现象

1.页拆分

页拆分也称为页分裂。当有序的页面容不下新记录时就会出现页拆分现象。页拆分时SQL
Server会尽量将旧页的一半记录复制到新页,其中的动作是先在旧页delete需要移动的行再在新页insert移动的行,新插入的行会根据键值大小来决定插在旧页中还是新页中。

INSERT和UPDATE都可能会导致页拆分。当页拆分后还是不能容下某记录时,会出现二次拆分,二次拆分后发现还是不能容下会三次拆分,直到能容下这部分记录。假如父页原有10行,插入一个7900字节的,第一次拆分差不多移动5行左右到新页,发现在新页还是容不下新行,又拆分移动2行到另一个新页,还是发现不能和新行并存,接着拆分2次,最后发现,新行只能独立成页才最后一次拆分页来存放新行,这时就有不少页只利用了很少一点空间。

页拆分后的页之间通过双链表连接,即形成上下页的关系。页拆分会记录日志,并且在拆分完成后,页拆分的专属系统内部事务会单独被提交,因此即使INSERT语句回滚了,拆分的页也不会回滚。也因此,频繁页拆分是一个消耗大量资源的动作。

页面容不下新记录时并不一定会页拆分,只有有序的页面会页拆分。如果是堆表的数据页,插入或更新记录都是“见缝插针”型的页填充,不会出现页拆分现象。如果新记录插入的位置是B树中某个层次的中间一个页面(如叶级层次的中间某页),当该页容不下新记录时,则一定会进行页拆分。如果新记录是插在最后一页(例如,具有IDENTITY属性的列为聚集键,向其中插入新记录时总是会插入在表尾),并且该页容不下新记录,则有两种情况:一是进行页拆分,所有的索引页(包括聚集的和非聚集的)和聚集索引叶级的第一页都是这种情况;二是直接分配新页存放新记录,不进行页拆分,聚集索引的叶级部分除了第一页的所有页都是这种情况。

下面的图中演示了向聚集表尾插入数据的页拆分过程。随着数据不断插入到聚集表的尾部,叶级的第一页首先拆分,这时会分配第二个叶级页和一个根页,并将接近一半的记录移动到第二个叶级页中,以后将尽量完全填充叶级页。这也是聚集索引的一个作用,表尾数据的插入不会导致大量的页拆分,并且保证了叶级页的空间使用率。当第一个根页无法容纳新记录时,将分配一个新的中间页和一个新的根页,旧的根页则变成中间页,并且以后将一直分裂,页面的空间使用率也不高。

 图片 7

需要引起注意的是,每当B树结构中出现一个新的层次页时,为这个新的层次分配的页码总是会挤在中间。例如,下面的图中所展示的情况,新分配的根页页码为257,挤在叶级第一页和第二页的页码中间。

 图片 8

2.行移动

行移动的现象只在更新行和页拆分的时候出现。行移动可能在本页移动,也可能在页间移动。

页拆分时的行移动很容易理解,拆分时尽量将旧页的大概一半记录移动到新页,这是页间的行移动。

那更新行时的行移动是怎么进行的呢?更新行时可能是在本页移动,可能是页间移动。不管在页内移动还是页间移动,移动后如何找到记录是问题的关键,这和记录是否有序、如何定位记录有关。

对于有序的记录(所有的索引页和聚集索引的叶级页中的记录),通过顺序就可以找到移动后的位置。如果更新行时,行记录只需在本页移动,则只需重排下该页的slot,空间位置上不会真的移动这一行。例如,某聚集表的数据页中记录了聚集键值为1(slot0)、3(slot1)、5(slot2)、7(slot3)、9(slot4)的记录,如果将3更新为6,则该记录可以继续留在本页,只需重排下slot,重排后记录对应为1(slot0)、5(slot1)、6(slot2)、7(slot3)和9(slot4)。如果将3修改为4呢?那么除了修改键值外不做任何其它改变。如果更新行时,行记录需要移动到其它页上,这时先在旧页执行DELETE再在新页执行INSERT,当然,这里也会重排相关页内的slot。

对于无序的记录,也就是堆表的数据页,如果记录在页间移动,则会在原记录处留下转发指针(forwarding
pointer),用于定位移动后的位置。如果该记录需要二次移动,则会更新原记录处的转发指针指到最新的位置,而不会在中间的位置添加转发指针,即转发指针不可能指向另一个转发指针。转发指针的作用是用于定位,如果堆中有非聚集索引,只需让非聚集索引的叶级行定位器RID指向转发指针的位置,通过转发指针就能定位新位置。

转发指针只在堆中出现,当转发指针数量多时,它对性能的影响非常大,可能出现多十倍甚至百倍的逻辑读。数据库收缩或文件收缩会收缩转发指针;当再次更新转发后的行记录使得原位置又可以容纳该行,则该行会复位并删除转发指针。

堆中行的更新不会出现页内移动,因为只要本页空间够容下更新后的记录,该记录直接在本页上扩展空间即可。因此,除非物理移动了数据文件的位置,堆中非聚集索引行定位器RID将不会因为行的更新而受到影响。

三.堆上的页拆分

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

图片 9

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

图片 10

 

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

如下图:forwarded_record_count为0了 

图片 11

1.2 插入行

堆中插入行,是“见缝插针”型。此时会寻找空间足够大的“缝”来插入这根“针”,如果有空“缝”但空间不够放这一行记录,则不会在这里插入;如果在已分配的页中没有“缝”可以存放记录,就新分配一个页来存放。由于总会找到合适的空间,因此不会出现页拆分现象。注意:更新行是DELETE和INSERT的结合操作,因此在堆表更新行时,即使容不下行也不会页拆分,而是留下转发指针。

聚集表中插入行的位置是固定了的,页中容不下新记录时可能会出现页拆分,也可能不会页拆分,具体的情况在刚才的页拆分段落的上下文中说明了。

在非聚集索引的索引页上插入记录且容纳不下时会出现页拆分。

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

 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

图片 12

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

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

  delete  from ProductWithDeap

       delete from ProductWithclustered

图片 13

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

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

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

1.3 删除行

1.删除堆的数据页

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

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

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

 图片 14

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

2.删除聚集表中记录

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

图片 15

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

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

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

相关文章

Leave a Reply

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