《Microsoft Sql server 2008 Internals》读书笔记--第六章Indexes:Internals and Management(7)
《Microsoft Sql server 2008 Internals》读书笔记--目录索引
前几篇文章主要介绍了聚集索引和非聚集索引的物 理存储结构,及几类特殊的索引:1、计算列索引和索引视图(Indexes On computered Columns and Indexed View);2、全文索引(Full-Text Indexes);3、空间索引(Spatial Indexes);4、XML索引(XML Indexes)
下面这几篇主要是关于数据修改的内部结构。
■ 数据修改内部结构(Data Modification Internals)
在粗略学习了SQL Server的数据和索引的一些知识后,让我们来看看当数据被修改的时候,SQL Server 内部究竟发生了什么。我们已经了解,聚集索引如何对你的数据定义一个逻辑序列,而Heap为什么只不过是一个无序页(page)的集合。我们也了解到非聚集索引是与Data分开存储的结构,它的数据是表实际数据的一份copy(按照索引定义所定义的那样)。一个优先的规则是表应该先有一个聚集索引。推荐 SQL Server最佳实践:
http://technet.microsoft.com/zh-cn/library/cc917672%28en-us%29.aspx
注 意:当表进行Insert,Update,Delete操作时,一个等效的操作也发生在这个表的非聚集索引上。这个机制也适用于聚集索引。 即:任何对于表(无论是Heap或聚集索引的)的修改,非聚集索引会依此重做同样的操作。
SQL server 2008中,这个机制的例外是筛选索引(Filtered Indexes),因为筛选谓词意味着筛选的非聚集索引可能不会匹配实际的数据,当表中的数据行被修改的时候。当数据行修改时,筛选索引会评估是否需要对 筛选索引作同样的操作。
■Inerting Rows
当插入一行row到table时,SQL Server必须决定这行数据放于何处,同时插入相应的行到每一个非聚集索引。每一个操作遵循下列模式:基于表是否有一聚集索引,修改适当的数据页页 (Data page),并插入相应的行到每一个非聚集索引的叶级。
1、Heap
一个新行总是被插入到表的可用空间,在前面第三章中,我们了解到IAMs保持对(属于表的文件的)扩展分区(Extent)的跟踪,在第五章中,你已经注意到PFS页 (Pages)显示了这个页这个扩展分区内是否有可用空间(space)。如果没有空间可用的页,SQL server会(从存在的已经属于这介对象的扩展分区)试图寻找未分配的页(unallocated pages),如果不存在,SQL Server必须重新分配一个新的扩展分区(Extent)给这个表,第三章已经讨论了GAMs和SGAMs,被用于查找已经分配给这个对象的可用的扩展分区。因 此,为做一个Insert所做的分配要比使用PFS和IAM更为有效一些。因为即将插入的行的位置是未定义的,决定一个行位于何处时,Heap比一个聚集索引的表要低效的多。
2、有聚集索引的表
插入一个行到一个有聚集索引的表时,索引行被插入到非聚集索引 的索引行,这行(无论是数据行或索引行),总有一个特别的位置, 这个位置依赖于索引键对应于新行的值。当一个Insert发生时,(无论是直接插入或数据Update时)引起行移动或索引键列变化。当一个行*移动到一个新页时,"Update"对应的内部操作其实是"Delete"+"Insert",新行会被插入到索引键对应的位置,SQL Server通过页分隔熔接(splices)一个新页,如果当前叶级(即聚集索引的数据页或非聚集索引的的索引页)没有房间(room)。由于索引显示 了一个对应于索引的叶级行的特殊的排序,每一个新行对应于它所属的位置。如果没有房间可用于它所属的页的新行,那么一个新的页必须被重新分配,并链接到B 树。尽可能的,这个新页被分配到(与它相链接的其他页的)相同的范围。 在通常情况下,这个范围是满的,那么。一个新的范围(64k)被重新分配到这个对象。正如第三章所述,SQL Server使用GAM页查找可用的Extent范围。(这段有些拗口,呵呵。老外的句子太长了。 邀月[email protected]注)
■Splitting Pages
在SQL Server找到一个新页时,原始页必须被分隔,行的一半(对应于页的Slot数组的前一半)留在原始页,另一半被移到新页(尽可能平均)。在某些情况 下,即便在已经分隔后,SQL Server发现没有足够的房间存放新行,因为可变长度字段,可能非常的大。作为分隔的一部分,SQL Server必须(为每一个新页)增加一个相应的入口(Entry)到级上方的父页。如果只是单个分隔被需要,一个行被增加。然而,如果一个新行进行一 个分隔后仍然不能适应(存储需要),那么多个新页或新的条件会被添加到父页。举例,如果一个页有32行,假定SQL Server试图插入一个8000字节的新行,第一次分隔页,8000字节的行不适应,第二次分隔后仍然不适应。最后, SQL Server意识到新行不能被已有的一个页存放,于是重新分配一个新的页以存放新行。
一个索引树总是从根向下追溯,因此,在一个 Insert操作期间,它被向下分隔,这意味着评一个Insert对应的索引在被查找时,索引会被保护而不能用于Update,这个保护机制是一个闩锁(latch),你可以把它年看成一个锁。当一个页被读取或写到磁盘时,一个闩锁需要被用来保护页内容的物理完整性,一个父节点被加锁保护直到子节点的分隔动作完成后,没 有进一步操作,父节点被安全的释放。
在父节点的锁被释放之前,SQl Server决定这个页是否能容纳另外两行,不能,分隔页。只要页被增加一行到索引的对象搜索时,这个锁就会启用。目的是确保父页总是有对应于这行(或另 外一个子页分隔的行)的房间。分隔的类型取决于要被分隔的页的类型:索引的一个根页,一个中间索引页、或一个叶级页。并且,当分隔发生的时候,它自成事务独立执行。换句话说,即便Insert事务失败回滚,这个分隔操作不会回滚。
1、Splitting the root page of an index
如果一个索引的根页(root page)由于一个新的索引行被插入而需要被分隔,那么两个新页被分配给索引。根页的所有行被分隔在两个责,新插入的行被插入到新其中一个页的合适位置, 原始的根页仍然是根,只不过现在有两行在其中,分别指向每一个新分配的页。保持原始根页就意味着避免了一个对(包含了一个指向索引根页的指针的)系统目录上的索引元数据的Update操作。一个根页的分隔在索引中创建了一个新级(level)。由于索引通常仅仅有很少的级深度且可扩展,这种类型的分隔不经常发生。
2、Splitting an Intermediate Index Page
一 个中间索引页的分隔仅仅完成了分配这个页的索引键的中点,分配一个新页,复制原始索引页的后半部分到新页,新行被插入到分隔后新增加的页。再说一句,这个分隔也不常见,尽管中间页比根页更常见。
3、Splitting the leaf-Level Page
一 个叶级页(leaf-Page)分隔是常见的操作。甚至可能是你惟一的操作。作为一名开发人员或DBA,应当关注这个。这个分隔聚集索引数据页的机制与非 聚集索引的叶级级索引页是相同的,
数据页分隔仅仅当Insert 活动引起,并表中存在一个聚集索引时发生。尽管只能被 Insert操作引起,但这个Insert可能是一个Update语句的结果。如果行不能被在同一个位置或至少在同一个页Update,那么将引起 Delete+Insert操作。新行的插入引起页被分隔。
分 隔一个叶级(数据或索引)上一个复杂的操作。很像一个中间索引页的分隔:分配一个页的索引键的中点,分配一个新页,复制源页一半的数据到新页。它需要索引 管理器决定将要分配新行在哪个页,并处理不能容纳在任何一个旧页或新页的大数据。 当一个数据页被分隔的时候,聚集索引键值没有变化,因此非聚集索引不受影响。
我们通过一个例子来看看页被分隔的时 候发生了什么。
我们新建一个表,并插入一些数据:
use TestDb go --DROP TABLE bigrows; --go CREATE TABLE bigrows ( a int PRIMARY KEY, b varchar(1600) ); go /* Insert five rows into the table */ INSERT INTO bigrows VALUES (5, REPLICATE('a', 1600)); INSERT INTO bigrows VALUES (10, replicate('b', 1600)); INSERT INTO bigrows VALUES (15, replicate('c', 1600)); INSERT INTO bigrows VALUES (20, replicate('d', 1600)); INSERT INTO bigrows VALUES (25, replicate('e', 1600)); go
再用我们前面用过的一个表,来存放一些页数据
IF OBJECTPROPERTY(object_id('sp_tablepages'), 'IsUserTable') IS NOT NULL
DROP TABLE sp_tablepages;
go
CREATE TABLE sp_tablepages
(
PageFID tinyint,
PagePID int,
IAMFID tinyint,
IAMPID int,
ObjectID int,
IndexID tinyint,
PartitionNumber tinyint,
PartitionID bigint,
iam_chain_type varchar(30),
PageType tinyint,
IndexLevel tinyint,
NextPageFID tinyint,
NextPagePID int,
PrevPageFID tinyint,
PrevPagePID int,
CONSTRAINT sp_tablepages_PK
PRIMARY KEY (PageFID, PagePID)
);
go
TRUNCATE TABLE sp_tablepages;
INSERT INTO sp_tablepages
EXEC ('DBCC IND ( Testdb, bigrows, -1)' );
go
查询叶级:
SELECT PageFID, PagePID
FROM sp_tablepages
WHERE PageType = 1;
go
查看slot array:
现
在我们插入一行或多行数据,再看Slot Array
新页总是包含原始页的后半部分的行,但是新行插入到哪个页取决于它的索引键。本例中,聚集
索引的键值是22,将被插入到后半页,因此,当页分隔发生的时候,页260上的前三行保留在页260,原始页。我们来看上图。
Slot 1
(with value 22)开始偏移3,326。Slot 2 (with value
25)开始偏移1,711。行的聚集键的顺序按照Slot序号,而并没有按照物理顺序,如果一个表有一个聚集索引键,Slot1对应的行总是比Slot2
对应的行的键值小,而比Slot0的大。仅仅Slot
Number发生重排,而不是数据。用一个小数量偏移时量的重排来替代整个页内容,这是一个优化。一个索引中的行总是准确按照物理排序是一个美好的愿望。
实际上,SQL
server能存储行到页的任何位置,只要Slot Array提供对应的正确的逻辑排序即可。
页分隔是一个昂贵的操作。涉及到多个页的Update(包括页被分隔、新页创建、将要用到的下一个分隔页的序号、父页),这些都要被记录。因此,在你的生产系统中,尽可能最小地使用页分隔,特别是在峰值时段。保持最小使用页分隔的方法通常是选择一个好的聚集键(最好按顺序而不是随机如GUID),特别是一个可变宽度列的Update会引起页的分隔。 最好在创建索引时用FileFactor选项,以保留一些*空间在页上。你可以空闲的时段,使用理想的 FileFactor,周期性地重建或重新组织索引。这样,你的特殊空间可以在峰值时段使用,从而节省了页分隔的开销。后面将会继续讨论。
这 一节我们主要了解Inserting Rows 时SQL Server的内部存储机制,下一节我们来看看Deleting Rows