澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

表组织和目录协会,DML和目录内部结构变化

2019-06-15 作者:数据库网络   |   浏览(108)

一.   概述

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

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

表组织

相关有关索引碎片的问题,大家应该是听过不少,也许也很多的朋友已经做了与之相关的工作。那我们今天就来看看这个问题。

《Microsoft Sql server 2008 Internals》读书笔记订阅地址:

二. 堆应用场景

  堆最常用的现象就是使用临时表,一般都很少会主动加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的结合操作,因此在堆表更新行时,即使容不下行也不会页拆分,而是留下转发指针。

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

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

图片 12

 

《Microsoft Sql server 2008 Internals》索引目录:

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

 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

图片 13

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

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

  delete  from ProductWithDeap

       delete from ProductWithclustered

图片 14

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

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

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

1.3 删除行

1.删除堆的数据页

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

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

 图片 15

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

2.删除聚集表中记录

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

图片 16

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

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

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

图片 17

知识普及

《Microsoft Sql server 2008 Internal》读书笔记--目录索引

五.堆的使用建议

  5.1堆需要考虑点

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

       5.2 堆选择理由

              高频率的增删操作。

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

              插入大量数据列到表中。

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

1.4 更新行

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

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

 

 

  ■Heap Modification Internals

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树的每个层次都进行了分析,其中最后一列是内部碎片的情况。

图片 18

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存储机制上,读取一个页和读取一个区的时间几乎是相等的,而页定位很可能意味着要消耗大量时间寻址。因此对于有大量定位动作的读取行为,时间主要消耗在定位上。

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

聚集表、堆和索引

我们都知道,数据库中的每一个表要么是堆表,要么就是包含聚集索引的表,或者我们称之为有序表。如果表是一个堆表,那么在使用非聚集索引查询数据的时候,会使用书签查找去底层的数据表中去检索需要的数据,这个书签查找会通过每一个索引中包含的行标识(RID)去定位每一个底层数据表的数据行。如果表上面有聚集索引,那么在使用非聚集索引查找其他需要数据的时候,就会使用聚集索引键去定位底层的数据行。

我们已经了解SQL Server如何在一个Heap中存储数据。现在我们了解一下SQL Server在一个Heap数据修改时内部实际如何操作。对于一个有聚集索引的表的数据修改,我们在第七章中讨论。一般来说,在一个表中应该有一个聚集索引。某些情况下你也许会觉得Heap是一个更好的选择,但请注意,希望做出彻底测试后再做决定。现在,我们只关注Heap中数据的修改。 

3.重组和重建索引

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

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

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

SQL Server 表使用下列两种方法之一来组织其分区中的数据页:

 

■1、分配结构(Allocation Structure)

  • 聚集表是有聚集索引的表。

    数据行基于聚集索引键按顺序存储。聚集索引按 B 树索引结构实现,B 树索引结构支持基于聚集索引键值对行进行快速检索。索引中每个级别的页(包括叶级别的数据页)链接在一个双向链接的列表中。但是,通过使用键值来执行从一个级别到另一级别的导航。堆是没有聚集索引的表。

  • 数据行不按任何特殊的顺序存储,数据页也没有任何特殊的顺序。数据页不在链接列表内链接。

我们也知道,索引是由索引页组成的,索引中的每一个条目包含在页中。每8个页组成一个块。

正如第三章讨论时,SQL Server为每个对象分配一个或多个IAM页,以跟踪哪些分区在每个文件属于该对象。如果表是一个堆(heap),使用IAMs是SQL Server找到属于表的所有分区唯一的途径。因为表的独立数据页不连接到双向链表,这不像在聚集索引的表中的处理方式。一个索引的每一个级所在的页被连接是因为数据被认为是一个聚集索引的叶级,则SQL Server会保持这个链接。但是,对于Heap,没有这样的链表相互连接的页。SQL Server确定哪些页属于一个表的唯一方式是检查数据表中的IAMs。

索引视图与聚集表具有相同的存储结构。

 

另外一个特殊的分配结构有特定条件下有用,当SQL Server执行数据修改时,即Page Free Space(PFS)结构。PFS结构跟踪每页有多少空闲空间,以便Heap中的Insert操作知道哪些空间可用于新数据的插入,而Update操作知道哪个位置的行可能被移动。前面第三章我们已经提到过,这些页在每个文件的8088页范围内包含一个字节为每个页,这比GAMs和SGAMs和IAMs要稀疏得多,后者每个分区包含一个Bit(位)。下图5-13显示了一个PFS页上字节结构。只有最后3位是用来表示页面全满,其他五个字节中的四个每个都有含义。

当堆或聚集表具有多个分区时,每个分区都有一个堆或 B 树结构,其中包含该指定分区的行组。例如,如果一个聚集表有 4 个分区,那么将有 4 个 B 树,每个分区一个

索引的层级是从底向上的,就是一个树结构,最下面的就是第0层,也是叶节点。索引中的根节点处于整个索引的最上层。

图片 19

下面想看一下 聚集索引结构 ,堆结构和非聚集索引结构

表组织和目录协会,DML和目录内部结构变化。 

这些位的值解释如下:

聚集索引结构:

如果要扫描整个索引,那么就意味着必须要读取页节点中的每一个页(要么是数据页,要么是索引页)。其中,每个页都包含着一个指向它前面的页和一个指向它后面也的指针。之前,我们也提过:如果单看某一层节点,其实就是一个双向链表。

◆Bit 1 该位表示页实际分配与否。例如,一标准分区可分配给一个对象,但在分区内的所有页可能未分配。哪些分配的分区内页已实际使用,SQL Server需要看PFS页的相关字节的这个位。

在 SQL Server 中,索引是按 B 树结构进行组织的。索引 B 树中的每一页称为一个索引节点。B 树的顶端节点称为根节点。索引中的底层节点称为叶节点。根节点与叶节点之间的任何索引级别统称为中间级。在聚集索引中,叶节点包含基础表的数据页。根节点和中间级节点包含存有索引行的索引页。每个索引行包含一个键值和一个指针,该指针指向 B 树上的某一中间级页或叶级索引中的某个数据行。每级索引中的页均被链接在双向链接列表中。

我们应该知道:页(不管是数据页,还是索引页 ,还是其他的类型的页)处于的逻辑顺序和它的物理顺便不一定就是一样的,也就说,在A页中的指针指向了它的下一个页B,也就说A和B页在逻辑上面是一起的,但是它们在物理上面可能不一样,甚至B页和A页在物理上相隔几百个页。

◆Bit 2 表示相关的页是否来自于一个混合分区

对于某个聚集索引,sys.system_internals_allocation_units 中的 root_page 列指向该聚集索引某个特定分区的顶部。SQL Server 将在索引中向下移动以查找与某个聚集索引键对应的行。为了查找键的范围,SQL Server 将在索引中移动以查找该范围的起始键值,然后用向前或向后指针在数据页中进行扫描。为了查找数据页链的首页,SQL Server 将从索引的根节点沿最左边的指针进行扫描。

如果在逻辑上面相连的页在物理存储级别相隔的越近,那么在读取这些页的时候所花的I/O成本也就越小,因为产生磁盘的磁头移动带来的延迟。相反,如果他们的物理存储顺序和逻辑顺序一致,那么SQL Server在读取的时候,就可以一次读取,因为每次会读取一个块(8个页)。

◆Bit 3 表示这个页是否一个IAM页,记住IAM页不会定位到文件中的已知位置

下图显式了聚集索引单个分区中的结构。

好了,普及知识之后,我们就来看看什么是碎片。

◆Bit 4 表示页是否含有克隆记录。我们知道,SQL Server使用一个后台的清理线程移除克隆记录。PFS的这个位有助于SQL Server找到需要被清理的页。

图片 20

 

◆Bit 5-7 看作一个3位值(three-bit value),0-4展示页的充满程度:

 

什么是索引碎片

❏   0: The page is empty.
❏   1: The page is 1–50 percent full.
❏   2: The page is 51–80 percent full.
❏   3: The page is 81–95 percent full.
❏   4: The page is 96–100 percent full.

堆结构:

 

PFS页在每个数据文件的已知位置。一个文件的第二页(Page 1)是PFS页,此后是每8088页一个。

堆是不含聚集索引的表。堆的 sys.partitions.aspx) 中具有一行,对于堆使用的每个分区,都有 index_id = 0。默认情况下,一个堆有一个分区。当堆有多个分区时,每个分区有一个堆结构,其中包含该特定分区的数据。例如,如果一个堆有四个分区,则有四个堆结构;每个分区有一个堆结构。

索引碎片可以分为两类:内部索引碎片和外部索引碎片。下面我们就来具体的看看而这之前的区别以及如何检查。

 

sys.system_internals_allocation_units 系统视图中的列 first_iam_page 指向管理特定分区中堆的分配空间的一系列 IAM 页的第一页。SQL Server 使用 IAM 页在堆中移动。堆内的数据页和行没有任何特定的顺序,也不链接在一起。数据页之间唯一的逻辑连接是记录在 IAM 页内的信息。

 

■2、插入行(Inserting Rows)

可以通过扫描 IAM 页对堆进行表扫描或串行读操作来找到容纳该堆的页的扩展盘区。因为 IAM 按扩展盘区在数据文件内存在的顺序表示它们,所以这意味着串行堆扫描连续沿每个文件进行。使用 IAM 页设置扫描顺序还意味着堆中的行一般不按照插入的顺序返回。

内部索引碎片

当插入一个新行到表时,SQL Server必须确定它要被放的位置。当表没有聚集索引,也就是说,当表是一个Heap时,一个新行可以插入表中任何可用的空间。我已经讨论了IAMs和PFS页如何跟踪文件中哪些分区已经属于一个表,这些分区的哪些页有可用空间。即使没有聚集索引,空间的管理是相当有效。如果没有页空间可用,SQL Server会尝试查找已经属于对象的现有分区的未分配页。如果不存在,则SQL Server必须分配一个全新的分区给这个表。第3章讨论过。

下图说明 SQL Server 数据库引擎 如何使用 IAM 页检索具有单个分区的堆中的数据行。

 

 

图片 21

每一个索引页中都包含一些索引的条目(就类似数据页包含很多的数据行一行),这一点我们在之前讲过了的。但是,很多的时候,不是每个页都包含了最大的条数。例如,一个页的大小8k,也就是4096字节,除去一些页头,页脚等,还剩下8000多字节,如果每个索引条目的大小事100字节,那么这个索引页最大就可以包含80个条目,但是很多的情况下,却没有包含这么多。

■3、删除行(Deleting Rows)

 

 

当你从表中删除一个行时,你必须考虑到数据页和索引页发生什么。请记住,数据实际上是一个聚集索引的叶级,从表中删除一个聚集索引行与从一个非聚集索引删除叶级行方式相同。从Heap中删除行是一个不同的管理方式,这从一个索引的节点页删除。

非聚集索引结构:

也就说,很多的时候,索引页并没有完全的填满,或者这是问题,或许这么我们特意这样的,我们后续会提到。当我们谈到索引碎片的时候,我们往往就是指这些索引页没有完全填满。或者说的更加明白一点就是:我们原本是希望页都被填满的,但是随着数据的增删改,使得索引中的数据没有填满。

❏ 从Heap中删除行

非聚集索引与聚集索引具有相同的 B 树结构,它们之间的显著差别在于以下两点:

 

SQL Server2008在一行被删除时并不自动重新组织页的空间。作为一个性能优化,压缩(compaction)直到一个页需要连续空间以存放一个新插入行时才会发生。下面这个例子,它删除了页的中间一行,然后使用DBCC PAGE 检查该页:

  • 基础表的数据行不按非聚集键的顺序排序和存储。

  • 非聚集索引的叶层是由索引页而不是由数据页组成。

我们可以使用
sys.dm_db_index_physical_stats来查看相关的内部碎片的情况,执行查询如下:

 

既可以使用聚集索引来为表或视图定义非聚集索引,也可以根据堆来定义非聚集索引。非聚集索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。

SELECT IX.name AS 'Name'
     , PS.index_level AS 'Level'
     , PS.page_count AS 'Pages'
     , PS.avg_page_space_used_in_percent AS 'Page Fullness (%)'
  FROM sys.dm_db_index_physical_stats( 
           DB_ID(), 
           OBJECT_ID('Sales.SalesOrderDetail'), 
           DEFAULT, DEFAULT, 'DETAILED') PS
  JOIN sys.indexes IX
    ON IX.OBJECT_ID = PS.OBJECT_ID AND IX.index_id = PS.index_id 
  WHERE IX.name = 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID';
GO
USE testdb2;
GO

CREATE TABLE smallrows
(
    a int identity,
    b char(10)
);
GO

INSERT INTO smallrows 
    VALUES ('row 1'); 
INSERT INTO smallrows 
    VALUES ('row 2');
INSERT INTO smallrows  
    VALUES ('row 3');
INSERT INTO smallrows  
    VALUES ('row 4');
INSERT INTO smallrows  
    VALUES ('row 5');
GO

DBCC IND (testdb2, smallrows, -1);

DBCC TRACEON(3604);
GO
DBCC PAGE(testdb2, 1, 475,1);

非聚集索引行中的行定位器或是指向行的指针,或是行的聚集索引键,如下所述:

 

页输出如下:

  • 如果表是堆(意味着该表没有聚集索引),则行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID (RID)。

  • 如果表有聚集索引或索引视图上有聚集索引,则行定位器是行的聚集索引键。如果聚集索引不是唯一的索引,SQL Server 将添加在内部生成的值(称为唯一值)以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时,才添加该值。SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。

执行结果如图:

图片 22

对于索引使用的每个分区,非聚集索引在 index_id >0 的 sys.partitions.aspx) 中都有对应的一行。默认情况下,一个非聚集索引有单个分区。如果一个非聚集索引有多个分区,则每个分区都有一个包含该特定分区的索引行的 B 树结构。例如,如果一个非聚集索引有四个分区,那么就有四个 B 树结构,每个分区中一个。

图片 23

现在我们删除中间行(where a=3),即第三行

根据非聚集索引中数据类型的不同,每个非聚集索引结构会有一个或多个分配单元,在其中存储和管理特定分区的数据。每个非聚集索引至少有一个针对每个分区的 IN_ROW_DATA 分配单元(存储索引 B 树页)。如果非聚集索引包含大型对象 (LOB) 列,则还有一个针对每个分区的 LOB_DATA 分配单元。此外,如果非聚集索引包含的可变长度列超过 8,060 字节行大小限制,则还有一个针对每个分区的 ROW_OVERFLOW_DATA 分配单元。有关分配单元的详细信息,请参阅表组织和索引组织.aspx)。B 树的页集合由 sys.system_internals_allocation_units 系统视图中的 root_page 指针定位。

我们可以看到每个索引的页面的填充情况。这是一个针对聚集索引的查询。因此,这个索引的叶子层的入口是表中的行。层级为0的叶子层有1234页,每页的平均密度达到99%以上,说明这个表只有非常小的内部碎片。

图片 24

下图说明了单个分区中的非聚集索引结构。

下面,我们再来讲讲外部索引碎片。

注意:在Heap中,页底部的行偏移数组表明,第三行(at slot 2)现在偏移量为0,(也就是说,没有行真正使用slot 2),而使用slot 3的行仍然使用删除前的行偏移。也就是Delete发生时,页上并没有数据被移动。如果你使用DBCC PAGE的printopt 1或3,行不会显示在页。如果使用Printopt 2,则你仍然看到'Row 3'的字节。它们并没有物理地从页中移除,但是行偏移数组中偏移为0显示空间未被使用,因而可以被新行使用。

图片 25

 

除了页的空间未被回收外,Heap中的空页也经常不能被回收。即使从Heap中删除所有的行,SQL Server并不会标记空页(Empty page)为未分配,因而空间可以为其他对象可用。DMV sys.dm_db_Partition_stats仍然显示这部分空间属于Heap表。避免这个问题的方法是在删除被执行时,请求一个表锁。在第10章中会讨论。如果这个问题已经发生,将显示(比它实际拥有的)更多的属于表的空间,你可以生成一个表上的聚集索引经重新组织空间,并删除索引。

 

外部索引碎片

❏ 回收页

 

 

当最后一行从一个数据页被删除,整个页被释放。唯一的例外是,如果表是一个Heap,正如我前面讨论。(如果页是唯一一个表中的,它不被释放。表总是至少包含一个页,即使它是空的。)释放一个数据页会导致指向释放数据页的索引页中的行被删除。如果一个索引行被删除,索引页被释放(这再次,作为一个/插入删除更新策略的一部分而发生),只留下一个索引页的项。该项被移动到邻近页,然后空页被释放。

理解了上面的问题,这个外部索引碎片就好理解了,最简单的说法就是:索引中的索引页的逻辑顺序和物理顺序不一致。我们通过个图对比的来看看。

到目前为止,仅限于单个的行被删除,如果多行被删除,你必须意识到更多的冲突。

图片 26图片 27

 

图片 28 9865.jpg(95.72 K) 

■4、更新行(Updating Rows)

9/9/2012 11:19:53 AM

SQL Server可以用几种不同的方式更新行:自动和无形地为特定操作选择速度最快的更新策略。在确定策略时,SQL Server评估受影响的行数,行如何被访问(通过一个扫描或索引检索,或通过某个索引),以及是否对索引键的变化会发生。更新可能发生在任何地方,只要在原来的行改变一个列值为新值,或由一个删除接着一个插入。此外,更新可以由查询处理器或由存储引擎管理。

 

 

 

❏ 移动行

 

如果行必须移动到表中的新位置会发生什么?在SQL Server 2008中,此情况可能是由不同的原因而不同。在第6章中,我们将浏览索引的结构,并看看在一个表的聚集索引列(或列)的值如何决定该行的位置。因此,如果聚集键值被更改,该行很可能要在表内移动。

在上图中,一个索引包含了16个页。但是这16页不是包含在2个相连的块中的,而是分布在不同的地方,因为它们之前中的一些块被其他的对象占用了。这样就导致了16个页在物理上面不连续,这就是碎片。在读取的时候,就会消耗额外的I/O。

如果它仍然具有相同的行定位器(换句话说,该行的聚集键保持不变),没有非聚集索引必须修改。如果表没有聚集索引(换句话说,如果这是一个堆),一行可能被移动,因为它不再适合原来的页。每当具有可变长度列的行更新为一个新的,更大的空间时发生这种情况,非聚集索引并非仅仅因为一行移动到一个不同的物理位置时而全部都必须更新,SQL Server在行不得不移动时在原位置保留了一个转发指针。

 

我们看一个转发指针的例子,

和之前一样,我们可以使用
sys.dm_db_index_physical_stats来查看外部碎片的情况。但是这里的参数值可能要发生变化了:之前在sys.dm_db_index_physical_stats最后一个参数值是'DETAILED',这里我们的值是LIMITED或者Default。因为外部碎片关注的是索引页之前的连续性问题,不关注每一个页中的数据,此时只是部分的扫描,没有必要全部的扫描。大家可以参看MSDN的去进一步的理解这些参数的含义。

 

 

USE testdb2;
GO
DROP TABLE bigrows;
GO
CREATE TABLE bigrows
(   a int IDENTITY ,
    b varchar(1600),
    c varchar(1600));
GO
INSERT INTO bigrows 
    VALUES (REPLICATE('a', 1600), ''); 
INSERT INTO bigrows 
    VALUES (REPLICATE('b', 1600), '');
INSERT INTO bigrows 
    VALUES (REPLICATE('c', 1600), '');
INSERT INTO bigrows 
    VALUES (REPLICATE('d', 1600), '');
INSERT INTO bigrows 
    VALUES (REPLICATE('e', 1600), '');
GO
UPDATE bigrows 
SET c = REPLICATE('x', 1600)
WHERE a = 3;
GO

DBCC IND (testdb2, bigrows, -1);

--PageFID    PagePID
--1    478
--1    477
--1    50248

DBCC TRACEON(3604);
GO
DBCC PAGE(testdb, 1, 478, 1);
GO 

查询如下:

部分结果如下:

SELECT IX.name AS 'Name'
, PS.index_level AS 'Level'
, PS.page_count AS 'Pages'
, PS.avg_fragmentation_in_percent AS 'External Fragmentation (%)'
, PS.fragment_count AS 'Fragments'
, PS.avg_fragment_size_in_pages AS 'Avg Fragment Size'
FROM sys.dm_db_index_physical_stats(
DB_ID(),
OBJECT_ID('Sales.SalesOrderDetail'),
DEFAULT, DEFAULT, 'LIMITED') PS
JOIN sys.indexes IX
ON IX.OBJECT_ID = PS.OBJECT_ID AND IX.index_id = PS.index_id
WHERE IX.name = 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID';

图片 29

结果如下:

对第一个字节的值4表示这只是一个转发存根。后面三个字节(00c448,十六进制的)是行被移动的页数字。

图片 30

 

 

❏ 管理转发指针

除了使用脚本之外,我们还可以在SQL Server管理器中查看,在某个索引上面右键,属性,如下:

转发指针允许你在一个Heap中修改数据而不用担心非聚集索引发生激烈的变化,如果一个已经转向的行需要再次被移动,原转向指针被更新以指向新的位置。另 外,如果转向行收缩到足够适应原页面,该记录可能移动到原来的位置,如果原页面还有空间。此时,转向指针将消除。

 图片 31

 在SQL Server的未来一个版本中,可能包含一些机制,如在一个Heap中执行物理识别,以除去转向指针。注意转向指针仅存在于Heap中。重新组织表的 Alter Table选项并不影响Heap。可以在一个Heap中整理一个非聚集索引,而不是更新表自身。目前,当一个转向指向被创建时,它永远在那儿不会移动, 除了几个例外:

 

第一个例外是刚才提到的,行被收缩返回到原始位置。

在这里要说明一下,因为原英文版本在理解上面可能会有些困难,为了使得大家更好的理解原文,我们这里特意的加入了一些其他的内容,帮助朋友们进行一个过渡。

第二个例外,是整个数据库发生收缩,当一个文件被 收缩时书签实际上被重排了。收缩进程实际上并不生成转发指针。而那些对于收缩进程移动的页面,所包含的任何转向的行或存根都有有效地“反转向”了。

因为索引碎片分析涉及到了页拆分的一些知识,页拆分发生在某个页上的数据已经填满而没有多余的空间给新增的数据而产生的动作,同时,向已经填满数据的页上面加入新的数据还可能会导致另外一个操作,所以,我们这里也随便的讲一个,使得大家更好的理解。

第三个例外,如果转向行被删除或一个聚集索引被创建而不再是一个Heap。

 

如果要获取一个表中的转向记录的数量,你可以使用 sys.dm_db_index_physical_stats函数。请看MSDN:

我们之前已经提到过,SQL Server在数据库中把任何的信息都是保存在基于8KB的页(不管是何种类型的页,我们这里不考虑大对象的数据页)上面的。如果记录(不管是底层的数据行记录还是索引中的条目等)的大小总和加起来小于8KB,那么SQL Server可能就会在一个页上面存放多条记录。如果大于了8KB,那么肯定就需要更多的页来进行记录的保存,此时SQL Server必须改变每一个页上面的记录。SQL Server主要基于两种方法来实现这个改变:记录转发与页拆分。

 

 

❏ 原地更新

备注:记录-我们这里一个对数据的统称,例如数据页上面的每一条数据是一个记录,索引页上面的一个条目是一个记录。

 在 SQL Server 2008中,原地更新一个行时主要依据规则而不是意外。(updating a row in place is the rule rather than the exception),这意味着行准确在呆在相同的页面的相同位置,仅仅受影响的字节发生改变。此外,日志为每个原地更新操作包含了一个单独的记录,除非 表有一个更新触发器或被标记为用于复制。在这些情况下,更新将在原地发生,但日志不包含索引键列被更新时的Delete Insert操作。

 

在某些情况下当一个行不能被原地更新时,“非原地更新”的代价是最小的,因为非聚集索引的存储方式和转向的使用。事实上,也可以对仍保留在原始页的行进行非 原地更新。如果一个Heap被更新,并且页面有足够的空间,或一个有聚集索引的表被更新(而同时聚集键没有变化的情况下),更新在原地发生。如果聚集键发 生改变但是行不需要移动时,也可以在原地更新。比如,如果在包含连续键值(Able,Becker和Charlie)的lastname列有一个聚集索 引,你可能更新Becker为Baker,此时,行还在原来的位置,即使聚集索引键改变,SQL会执行一个原地更新。另外一方面,如果你更新“Able” 为“Buchner”,更新不能在原地发生。

记录转发

 

 

❏ 非原地更新

当记录的大小已经超过了一个页的容量的时候,第一种存放记录的方式就是“记录转发”。

如果正在更新聚集键时更新不能在原地发生,更新以“Delete Insert”方式实行,在某些情况下,你可能得到一个混合更新:一些行被原地更新,一些不在。如果你更新索引键,SQL建立一个需要实行(Delete inert)的行列表。列表在内存中,在不大的情况下,如果有必要可能会写入Tempdb 中,这个列表按键值和操作(Delete Insert)排序。如果键值被改变的索引是不惟一的,Delete Insert步骤被应用到表。如果是惟一 的,则执行单个的Update操作。

 

注意:查询优化器决定特殊的Update方法是否合适是,这个内部优化,称为split/Sort/Collapse,第八章中讨论。

这个方法只有当底层的数据表是堆的时候才采用。如果某一行的数据记录被修改,使得此时所在的数据页已经无法存放其修改的行所有的数据,SQL Server将会把这条记录移动到一个新的数据页上面去,同时会增加两个指针。第一个指针将会表明这个数据行现在新的位置,通常这个指针称之为“记录转发指针”,而第二个指针将会放在新的数据页上面,指向这个记录原先数据页,这个指针称之为“回指指针”。熟悉数据结构的朋友,其实可以把这个过程想成在一个链表中加入一个节点。

 

 

■小结:

为了使得大家更好的明白上面的讲述,我们还是来看一个例子。在例子中,我们将会带着大家一起来看看记录转发这个过程是如何进行的。如下图:

表是在一般关系数据库的核心,在SQL Server中更是如此。在这一章中,我们研究了各种数据类型的内部存储问题,特别是固定和可变长度数据类型的比较。我们看SQL Server 2008提供用于存储可变长度数据的多个方案,包括太长而不能放在一个单一的数据页的数据,你看到使用可变长度的数据类型不总是好或不总是坏。 SQL Server提供域支持的用户定义的数据类型,它提供了IDENTITY属性,使一列产生自动序列化的数值。你也看到了数据如何被物理存储在数据页,我们查询一些提供来自潜在的或不可访问的系统表的信息的元数据视图。 SQL Server还提供了约束,这提供了有力的方式,以确保您的数据的逻辑完整性。

图片 32图片 33

图片 34 20120906203332.png (30.64 K)

9/9/2012 11:20:20 AM

 

假设图中的页,编号为100,这个页处于一个堆表中。在这个页中包含了4条数据,而且每一条数据大小约2K,加起来就是8KB。如果此时第二条数据被更新了,使得它的数据大小变为了2.5KB,此时这个数据页肯定就无法存放所有的数据,此时SQL Server就会再去分配一个新的页,假设编号为101。那么,第二条数据就会被移到新分配的数据页上面去,而且在原先的页(编号100)上面加上一个指针指向第二条数据的新位置。那么原先存放第二条记录的地方此时就放置了指针。

 

另外,在新的页101中,也有一个指针回指向页100。在图中没有画出来。

 

记录转发的问题在于,它使得一条数据在一个表中存在两个位置:一个位置存放指针,一个位置存放真实的数据。随着记录的不断变多,会增加更多的额外的磁盘空间,特别是读取数据时额外的I/O操作,因为可能存在这样的情况:某些记录通过不断的修改,使得它们不在适合存放在当前页,从而放在新页上,做第一次的记录转发,然后再修改,然后再次进行第二次的记录转发….如下图:

 

图片 35图片 36

图片 37 20120906203429.png (31.27 K)

9/9/2012 11:20:20 AM

 

大家应该可以体会到,此时原本的数据A已经通过多次的转发,而在其他的页上面保留的仅仅只是它转发过程中下一个页的位置,这样,要想找到A数据,那么就要经过多次的指针查找,直到最后。

 

页拆分

 

对于页拆分,相信是很多朋友听的比较多的一个词了。下面,我们就来看看这个话题。页拆分发生在包含有索引的表中,要么有聚集索引,要么有非聚集索引。同时,页拆分不仅仅发生在数据页上,也发生在索引页上。

 

页拆分的过程基本是这样的: 如果一个记录的大小更新(或者增加),使得原来的页不在适应数据的大小,此时SQL Server无法将变化的数据写入,那么它就会把原先页上面的一半的记录移到新的页上面去。之后,SQL Server再次尝试去把数据写入,如果不行,那么再次分页,直到最后可以把数据写入。

 

我们还是通过一个例子来讲解这个问题。我们主要通过一个更新的操作来讲述。还是看到下面的图:

图片 38图片 39

图片 40 20120906203529.png (31.46 K)

9/9/2012 11:20:20 AM

 

在页100上有4条记录,每一个的大小约2KB,此时刚好把一个页占满。如果此时对第二条数据进行修改,使得它的大小变为2.5KB,那么此时就会进行页的拆分。那么原先的4条数据,就会被分为2部分放在不同的页上,同时,SQL Server会在原先的页100上面放置一个指向新页的指针,然后SQL Server再次去更新第二条记录。

 

好,说完了上面两种情况之后,我们就来看看,它们对索引的碎片有什么影响。

 

其实谈到碎片问题,只要是发生在页拆分操作上,特别是当索引的B树结构发生页拆分的时候。

 

下面,我们就要细化这个过程。

 

如果此时,表上已经有了索引,如果在数据表中增加一行数据,那么,这行数据肯定要反应到索引结构中(除非采用了过滤的索引),从而使得索引结构开始发生调整。

如果增加到索引结构中的这个条目可以加入到某个索引页中,换句话说,索引页中的空闲的空间可以容纳新的索引条目的大小,这个过程算是结束。

 

如果空间不足,那么此时,肯定要去分配新的页面,此时还不确定这个新的页面和旧的页面是否在物理空间上面连续,那么这就产生外部索引碎片,同时把原先页中的索引记录分布在两个页上,使得这个两个页有了比之前更多的空闲的空间,这就增加了内部索引碎片。

 

但是内部的碎片,可能会随着索引记录的不断增加而将其空闲的填充而减少。但是外部的碎片只有等到我们维护索引的时候才消失。

 

 

其实,大家可以看出来,不仅仅是索引碎片,底层数据页的碎片也可以采用同样的分析方法。

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:表组织和目录协会,DML和目录内部结构变化

关键词: