来自 新葡亰数据库 2019-11-06 09:27 的文章
当前位置: 澳门新葡亰app > 新葡亰数据库 > 正文

澳门新葡亰app二遍拆分后发现依然无法容下会叁

一.   概述

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

澳门新葡亰app 1

1.2 堆上的非聚集索引

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

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

澳门新葡亰app 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]

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

澳门新葡亰app 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]

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

  澳门新葡亰app 4

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

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

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

澳门新葡亰app 5

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

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

澳门新葡亰app 6

1.1页拆分和行移动现象

1.页拆分

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

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

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

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

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

 澳门新葡亰app 7

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

 澳门新葡亰app 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页)

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

澳门新葡亰app 10

 

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

如下图:forwarded_record_count为0了 

澳门新葡亰app 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

澳门新葡亰app 12

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

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

  delete  from ProductWithDeap

       delete from ProductWithclustered

澳门新葡亰app 13

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

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

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

1.3 删除行

1.删除堆的数据页

堆表数据删除后不释放空间,留下slot但slot不指向页中的位置,也就是像slot 0  0x0这样。这时候如果有新记录要存放就可以“见缝插针”,并将原来没有指向的slot指向这一插入的行。

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

 澳门新葡亰app 14

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

2.删除聚集表中记录

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

澳门新葡亰app 15

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

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

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

五.堆的使用建议

  5.1堆需要考虑点

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

       5.2 堆选择理由

              高频率的增删操作。

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

              插入大量数据列到表中。

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

1.4 更新行

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

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

2.碎片

在SQL Server中,碎片分两种:内部碎片和外部碎片。

1.内部碎片

内部碎片一般还称为页密度或物理碎片,表示页中减去填充因子所占的空间后的空间使用率,也就是页面使用率。SQL Server综合每个B树的层次的页空间使用情况,分别生成一个内部碎片百分比。内部碎片可能由下面几种情况导致:

  • 页拆分:页拆分后由于行移动,导致拆分的页面和新页面中出现空白空间。
  • DELETE操作导致页面还剩部分数据。这里的例外是聚集表由于记录被删除时存在虚影,所以不会释放这些删除行的空间,直到后台进程清理后才出现空白空间。
  • 行的大小使得页面填充不完整。例如,聚集索引叶级页中一个宽5000字节的行存放时一页只能放一行,每页都会浪费3000字节左右的空间。

在读取需要的数据时,内部碎片可能会使系统读取更多的页面,导致IO更大,并且需要更多的内存来存储这些页面。例如,读取聚集键值1-100的记录,如果不出现页拆分,它们可能存储在同一页上,这时只需从磁盘读取1页即可,如果内部碎片多,可能1-50在一页上,51-100在另一页上,这时就需要从磁盘读两页。

内部碎片也有好处,比如插入行时由于空闲空间的存在,可能不会出现页拆分现象。因此,经常需要DML操作的时候有一定的内部碎片是允许且有益的;但是对经常需要读取巨量数据进行分析的场景,对查询的性能要求较大,内部碎片越少越好。

可以通过sys.dm_db_index_physical_stats中的avg_page_space_used_in_percent列检测内部碎片。sys.dm_db_index_physical_stats是一个表值函数,它有5个参数,第一个参数是DatabaseID,第二个参数是ObjectID,第三个参数是IndexID,第四个参数是分区ID号,第五个参数是显示信息的模式。

SELECT  OBJECT_NAME(object_id) AS name,

        index_id,

        index_type_desc AS index_type,  --索引类型

        index_depth,                    --索引B树的深度

        index_level,                    --索引B树的层次位置

        record_count AS rec_cnt,        --对应层次的记录数量

        page_count AS pg_cnt,           --对应层次使用的页的数量

        avg_fragmentation_in_percent AS frag_precent,   --外部碎片百分比

        avg_page_space_used_in_percent AS used_percent  --内部碎片百分比

FROM    sys.dm_db_index_physical_stats(DB_ID('testdb'),OBJECT_ID('dbo.Clu_Test'),NULL,

                                       NULL,'DETAILED')

 

对Clu_test表中的索引进行分析,返回结果看下图,从图可知为Clu_test表中所有B树的每个层次都进行了分析,其中最后一列是内部碎片的情况。

澳门新葡亰app 16

2.外部碎片

外部碎片一般还称为逻辑碎片或扩展碎片,是页拆分时出现页的逻辑顺序和物理顺序不一致导致的。很多地方说碎片默认的就是外部碎片。

那什么是页的逻辑顺序什么是页的物理顺序?页的逻辑顺序是指通过双链表形成的顺序,它能体现B树结构中键值的顺序,因此读取和扫描时按照页的逻辑顺序进行;页的物理顺序是指物理页的页码数值顺序。如果完全按序分配区间和页面,则页面之间不仅在逻辑上连续,在物理页码的数值上也是连续的,比如1-->2-->3。如果页面2出现页拆分,逻辑顺序变成1—>2-->10-->3,这样逻辑顺序和物理顺序将不一致。在页读取或扫描时,会在不连续的页面上不断的进行跳跃定位,很可能会让磁盘臂进行来回移动,从而消耗大量时间。例如从2定位到10进行一次页定位动作,再从10定位回3也要一次定位动作,这需要消耗时间;如果是1-->2-->3-->4这样连续的页就可以快速下一页下一页扫描甚至一次性抓取多个邻近的页到内存中(SQL Server允许一次性读取64个连续的页到内存中,更详细的页读取情况可以查看这篇文章

如果查询请求的记录较少,外部碎片的影响可以忽略,因为读取页时少量的页定位影响不大;但是如果查询要返回大量记录,由于要读取较多页面,大量的外部碎片会导致多次来回页定位,会严重影响查询性能。可以通过sys.dm_db_index_physical_stats中的avg_fragmentation_in_percent列来检测外部碎片。

 

传统的机械硬盘读取数据需要先计算地址后寻道,寻道时会移动磁盘臂,寻道后盘片旋转使数据所在扇区处于磁头下方,最后磁头读取扇区数据。扇区数据的读取动作非常快,整个过程的大部分时间都消耗在寻址上。在SQL Server存储机制上,读取一个页和读取一个区的时间几乎是相等的,而页定位很可能意味着要消耗大量时间寻址。因此对于有大量定位动作的读取行为,时间主要消耗在定位上。

固态硬盘只有得到指令后地址的计算时间,几乎没有寻址时间,不存在定位消耗大量时间的问题,因此外部碎片问题也迎刃而解。

3.重组和重建索引

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

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

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

本文由澳门新葡亰app发布于新葡亰数据库,转载请注明出处:澳门新葡亰app二遍拆分后发现依然无法容下会叁

关键词: