《Microsoft Sql server 2008 Internals》读书笔记--第六章Indexes:Internals and Management(7)

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

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

 

 前几篇文章主要介绍了聚集索引和非聚集索引的物 理存储结构，及几类特殊的索引：１、计算列索引和索引视图（Indexes On computered Columns and Indexed View）；２、全文索引(Full-Text Indexes)；３、空间索引(Spatial Indexes)；４、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)，并插入相应的行到每一个非聚集索引的叶级。

 １、Ｈeap

 一个新行总是被插入到表的可用空间，在前面第三章中，我们了解到IAMs保持对（属于表的文件的）扩展分区(Extent)的跟踪，在第五章中，你已经注意到PFS页 （Pages)显示了这个页这个扩展分区内是否有可用空间(space)。如果没有空间可用的页，SQL server会（从存在的已经属于这介对象的扩展分区）试图寻找未分配的页(unallocated pages),如果不存在，SQL Server必须重新分配一个新的扩展分区(Extent)给这个表，第三章已经讨论了GAMs和SGAMs，被用于查找已经分配给这个对象的可用的扩展分区。因 此，为做一个Insert所做的分配要比使用PFS和IAM更为有效一些。因为即将插入的行的位置是未定义的，决定一个行位于何处时，Heap比一个聚集索引的表要低效的多。

２、有聚集索引的表

 插入一个行到一个有聚集索引的表时，索引行被插入到非聚集索引 的索引行，这行（无论是数据行或索引行），总有一个特别的位置， 这个位置依赖于索引键对应于新行的值。当一个Insert发生时，（无论是直接插入或数据Update时）引起行移动或索引键列变化。当一个行被迫移动到一个新页时，"Update"对应的内部操作其实是"Delete"+"Insert"，新行会被插入到索引键对应的位置，SQL Server通过页分隔熔接（splices）一个新页，如果当前叶级（即聚集索引的数据页或非聚集索引的的索引页）没有房间(room)。由于索引显示 了一个对应于索引的叶级行的特殊的排序，每一个新行对应于它所属的位置。如果没有房间可用于它所属的页的新行，那么一个新的页必须被重新分配，并链接到Ｂ 树。尽可能的，这个新页被分配到（与它相链接的其他页的）相同的范围。 在通常情况下，这个范围是满的，那么。一个新的范围(64k)被重新分配到这个对象。正如第三章所述，SQL Server使用GAM页查找可用的Extent范围。（这段有些拗口，呵呵。老外的句子太长了。 邀月3w@live.cn注）

   ■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)。由于索引通常仅仅有很少的级深度且可扩展，这种类型的分隔不经常发生。

２、Splitting an Intermediate Index Page

一 个中间索引页的分隔仅仅完成了分配这个页的索引键的中点，分配一个新页，复制原始索引页的后半部分到新页，新行被插入到分隔后新增加的页。再说一句，这个分隔也不常见，尽管中间页比根页更常见。

３、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

 

邀月注：本文版权由邀月和优快云共同所有，转载请注明出处。
助人等于自助!   3w@live.cn