《Microsoft Sql server 2008 Internal》读书笔记--第六章Indexes:Internals and Management(10)-CSDN博客

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

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

　　前一节主要介绍了索引的更新机制和索引的转向指针，以及更新的位置和修改单个行（或单索引对应的若干行）。如果你修改单个操作（Insert,update,Delete）或使用（BCP、BLUK Insert）命令的多行，而表有多个索引时，你得关注另外一些主题。SQL Server 2008提供了两种维护表索引的策略：表级修改和索引级修改。查询优化器会根据预计的执行成本决定使用相应的策略。

■表级vs索引级数据修改
　　表级修改有时也叫row-at-a-time,索引级修改有时也叫index-at-a-time。在表级修改时，行被修改时每个行所有的索引被保留。如果更新流没有被以任何形式排序，SQL Server会大量的随机索引访问，每个更新行每个索引一次访问。如果更新流已经排序（只能有一个排序），因此非随机索引访问最多只能在一个索引时发生。

　　索引级修改时，SQL Server收集所有要被修改的行，并为每个索引排序这些行，换句话说，排序操作和索引操作是对应的。对每个索引，更新被合并到索引（的更新），每个索引页最多会被访问一次，即便多个更新属于一个单个索引叶级页。

　　很显然，如果更新是小的（即少量的瘦的行），表和索引是可度量的，查询优化器通常会考虑表级修改作为最佳选择。绝大多数OLTP操作都是表级修改。另一方面，如果更新相对较大，表级修改需要大量的随机I/O操作，可能会在每个索引的每个叶级页读写多次。在此情况下，索引级修改提供了更佳的性能。两种策略都会需要大量的日志记录。另外，你可以通过查询执行计划来决定使用表级或索引级策略。

■管理索引结构

　　SQL Server自动维护你的索引。当你增加新行时，系统会自动插入这些行在一个（有聚集索引的）表的正确位置，或增加新的叶级行到你的非聚集索引以指向新的数据行。当你移去这些行时，SQL Server自动从非聚集索引索引中删除相关的叶级行。

■Dropping Indexes

　　使用Create Index命令创建的索引和支持约束的索引这两种方式会影响删除索引的方式。Drop Index命令允许你删除仅仅使用Create Index命令创建的索引。为了删除支持约束的索引，你必须使用Alter table命令先删除约束。而且，包括删除主键、惟一的约束和引用这些它们的外键约束，如果不想麻烦，你可以使用以下方法：

１使用ALTER Index命令。更多参数，请看MSDN:

http://msdn.microsoft.com/zh-cn/library/ms188388.aspx

http://technet.microsoft.com/zh-cn/library/ms188388%28SQL.90%29.aspx

２使用Create Index命令带DROP_Indexing选项，不过此时重建命令不再适合于支持约束的索引，即只能drop了。

■Alter Index

SQL Server 2005提供了Alter Index命令，该命令允许你使用单个命令来调用以前版本所需的多个风格迥异的命令集（包括sp_Indexoption,Update Statistics,DBCC DBREINDEX和DBCC INDEXDEFRAG等）才能完成的功能。具体功能可以参看MSDN:（http://msdn.microsoft.com/zh-cn/library/ms188388.aspx）

１、Rebuiding an Index（重新生成索引）

　　重新生成索引代替了“DBCC DBREINDEX”命令，可以看作是CREATE INDEX命令加了DROP_EXISTING选项。然而，这个选项也允许索引被移动或被分区。

２、禁用索引

　　禁用一个索引会使它完全不可用，它也不能被用来为任何操作来查找行。同时也意味着它不会随着数据的修改而更新（，从而导致过时）。你可以用一个命令禁用一个或全部索引，记住，没有Enable选项来恢复你的禁用。道理很简单，因为索引被禁用后不再被维护，也就失去了意义，必须被完全rebuilt。重新启用索引（无论是脱机还是联机，）其实质是ALTER INDEX加了一个REBUILD选项。禁用索引主要是在SQL Server升级或打补丁时用到，但也有几个有趣的应用场景。第一，你可以在故障排除时想临时忽略索引时。第二，加载数据前不使用非聚集索引时，但是请注意不要禁用聚集索引。如果你禁用某表的聚集索引，这个表的数据将不可用，因为聚集索引的叶级是数据。禁用聚集索引就等于禁用了表。当然，如果你的数据是按照聚集索引排序（即便不是递增的聚集键），这样所有新的数据将会加在表的尾部，此时，禁用所有的非聚集索引有助于改善加载性能。一旦所有的数据被加载完成，你不需要提供完整的索引定义就可以rebuild非聚集索引。索引被禁用时，所有的元数据被保存。

３、更改索引选项

　　Create Index操作的大多数选项可以用于Alter Index命令。比如ALL_ROW_LOCKS,ALLOW_PAGE_LOCKS,INGORE_DUP_KEY,FILLFACTOR,PAD_INDEX,STATITICS_NORECOMPUTE,MAX_DOP和SORT_IN_TEMPDB等。

４、(Reorganizing an index)重组索引

参照MSDN的解释，重组索引就是使用最少系统资源重新组织索引。通过对叶级页以物理方式重新排序，使之与叶节点的从左到右的逻辑顺序相匹配，进而对表和视图中的聚集索引和非聚集索引的叶级进行碎片整理。重新组织还会压缩索引页。压缩基于现有的填充因子值。

■（Detecting Fragmentation）探测碎片

　　正如我们前面所看到的例子那样，sys.dm_db_index_physical_stats为每个索引的每一Level返回一行。然而，表被分区时，其实分区是被看作一个表，因此，DMV实际是为每个索引的每个分区的每个Level返回一行。对于仅有in-Row数据（即没有row-overflow或LOB页）和仅有一个默认分区的小索引，我们可能只得到两到三行返回值（每个索引Level一个）。但是如果有多个分区和额外的row-overflow及LOB页分配单元时，我们可能看到更多行。例如，一个包含row-Overflow数据，为row-overfow分配单元建有11 个分区，有两个Level深度的表，它的聚集索引有33行（2LevelsX11分区+11个为row_Overflow分配单元的分区），这个结果由sys.dm_db_index_ physical_stats（http://msdn.microsoft.com/zh-cn/library/ms188917.aspx）整理报告返回。

　　这里我们再提一下返回结果的几个列：

(1)Forwarded_record_count

Forwarded记录（前面讨论过）可能仅仅在一个Heap，并且当包含可变长度列的行因大小改变而不能在原位置时发生。如果表有大量的Forwarded记录，扫描一个表可能效率非常低下。

(2)Ghost_Record_Count 和version_ghost_record_count

　　Ghost记录是那些物理上还在一个页，而逻辑上已经被移走的行。在SQL Server中后台线程清理Ghost记录，但是在清理之前，新记录不能被插入在它们所占的位置。因此，如果有大量的Shost记录，表就有了内部整理的缺陷（即该表将占去更多的页，花更多时间来扫描）而不利（在相关页没有空间来插入新记录以避免额外的整理）。一个Ghost记录的子集用Version_ghost_record_count来度量。这个值　对应被孤立的快照隔离事务保留的记录数量。它们直到相关的事务被提交或回滚时才被清除。

■（Removing Fragmentation）移除碎片

　　如果碎片变得非常严重，会影响到查询性能，有几个选项可以移除它。你可能想要知道多严重才是“严重”，首先，碎片不总是一件坏事。最大的性能代价来自于你的程序需要对数据执行一个已拟定的扫描时，整理数据被唤醒。如果，你的应用仅仅一两行数据，那么表或索引数据是否按逻辑顺序或物理连续就不那么重要，甚至它在磁盘上的位置完全随机（也影响不大）。如果你有一个好的索引来找到你感兴趣的行，SQL Server能高效地找到一个或几行，即便它们刚好被物理定位。

　　如果你正在做一个索引的排序扫描（比如在一个有聚集索引的表上进行表扫描，或一个非聚集索引上的叶级扫描），最常被推荐的是：如果avg_fragmentation_in_percent值位于5-20，则应该重新组织索引以移去碎片。重新组织一个索引会让叶级页返回原始的定义填充因子，并重新排列叶级页经纠正逻辑核算碎片，使用索引原始占用的相同页。没有新页被分配，因而这个操作比rebuilding索引更有效利用　空间。

如果avg_fragmentation_in_percent值大于30，你应该考虑完全重建索引。这将移去所有的碎片，但并不确保完全削除碎片（也就是我们通常说的，手术过了但不一定成功）。如果数据库的自由空间是自身的碎片，你或许没有足够的连续空间来移除所有的区块之间的缝隙。此外，其他正在进行的工作需要重新分配新区块，而此时你的索引正在重建，那么对两个进程的已分配的区块可能因为交错而终止。

　　整理被用设计用来从索引的叶级移去逻辑碎片同时保持索引联机并尽可能可用。当整理一个索引时，SQL Server在B树上得到一个意向独占锁。锁定的独占页刚好是被操作的单个页。SQL Server 2008中使用ALTER INDEX命令来初始化整理。

常用的移除碎片的格式如下：

ALTER INDEX { index_name | ALL } ON REORGANIZE [ PARTITION = partition_number ] [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
　　相比而言，REORGANIZE选项的功能比SQL Server 2000的INDEXDEFRAG强了很多，它支持分区索引。如前所述，每个索引被一个特定的填充因子（fillfactor）所创建，初始的填充因子值以索引元数据存储，因此，整理碎片被请求时，SQL Server可以inspect该值。在整理期间，SQL Server试图在一个叶级页重建（reestablish）初始填充因子（如果它比当前值大）。整理被设想来使数据更紧凑，可能每页放更多的行，每页增加更充满的百分比。SQL Server可能终止并在整理后从索引移去页。如果当前填充因子大于初始值，SQL Server不能通过移动行来消减页的饱满度。压缩算法（按逻辑顺序）检查邻近的页，以确定是否有空间来从后页移动行到前页。自SQL Server 2005起，这个进程通过查看８个逻辑连续页的小滑块甚至更有效。它决定周围连续的８个页是否有足够的行以允许一个单页被清空或移走，并在合适时移走行。

　　SQL Server 2005也新增了选项来紧缩(compact)LOB页，默认选项是ON。重组指定的聚集索引会在紧缩叶级页之前紧缩所有包含在聚集索引中的ＬＯＢ列。重组非聚集索引会紧缩所有非键列的LOB列。

　　在SQL Server 2000中，唯一的紧缩表中LOB的方法是unload+reload　LOB数据。SQL Server 2005起，LOB紧缩操作会找到低密度的区块（使用低于75%）。它从低密度的整齐区块移去页，并从已经分配到ＬＯＢ分配单元的其他整齐区块的可用空间来存放这些数据。该功能允许磁盘空间更好的被利用（特别是低密度LOB区块）。无论是在紧缩阶段或其他后续阶段，都没有新区块（extent)被分配。

　　重组操作的第二阶段其实是移动数据到in-row分配单元的新页，目的是使数据的逻辑顺序与物理顺序一致。索引保持联机因为在一个操作中同时只有两个页被处理（类似于堆排序或平滑排序。）下面的例子是一个重组的实际进程。

　　假如一个datetime列索引，周一的数据逻辑排列在周二前，周二在周三前，依次类推。如果，周一的数据在88页，周二的数据在50页，周三在100页，周四在77页。物理与逻辑丝毫不匹配，出现逻辑碎片。当整理一个索引时，SQL Server判断第一个物理页属于页级（本例的页50），第一个叶级的逻辑页（页88，拥有周一的数据），通过使用一个额外的新页作为临时存储区域来交换两页的数据。交换过后，第一个逻辑页的数据是周一数据，在页50，最小序号的物理页。在两两交换后，所有的锁和闩被释放，最后一个被移动页的键值　被存储。算法的下一次迭代使用存储的键值来找到下一个逻辑页－－周四的数据，页88。下一个物理页是77，周二的数据。因此，下一个交换是周二的数据位于77页，周四的数据位于88页。直到无需交换为止。注意：混合区块上的页不会整理。

　　你需要了解一些使用REORGANIZE选项的限制。肯定，如果索引被禁用，则不能被整理碎片。而且，移去碎片的过程需要工作在独立的页，你可能会在重组一个（ALLOW_PAGE_LOCKS为OFF的）索引时，抛出一个错误。如果一个并发的联机索引被建立在相同的索引或另外一个重组相同索引的进程时重组不会发生。

　　你可以使用sys.dm_exec_requests（DMV，http://msdn.microsoft.com/zh-cn/library/ms177648.aspx）视图的percent_Complete列来观察每个索引的过程。这是一个百分比。

■（Rebuilding an Index）重新生成索引

　　你有几种方式可以完全重建索引。可以使用一个简单的DROP INDEX加CREATE INDEX的组合，但这最不可取。特别是，这种方法重建一个聚集索引时，所有的非聚集索引在drop聚集索引时会全部重建。非聚集索引这么处理是必需的，因为它要改变叶级的行定位从聚集键值到行IDs。于是，当你重建聚集索引时，所有非聚集索引必须被重建。而且，如果索引支一个Primary KEY或Unique约束时，你不能使用drop index命令，除非你首先删除所有的外键。不推荐这样做。

　　更好的一种方式是使用ALTER INDEX命令或使用带DROP_EXISTING子句的CREATE INDEX。下例是重建Production .TransactionHistory表上的索引PK_TransactionHistory_TransactionID

ALTER INDEX PK_TransactionHistory_TransactionID ON Production.TransactionHistory REBUILD; CREATE UNIQUE CLUSTERED INDEX PK_TransactionHistory_TransactionID ON Production.TransactionHistory (TransactionDate, TransactionID) WITH DROP_EXISTING;

　　尽管Create方法需要知道索引架构，它实际上更强大，提供了更多的自定义选项。你可以改变标记索引的列，改变uniqueness属性，或者改变一个非聚集索引为聚集索引，只要表上不存在一个聚集索引。你也可以在重建时指定一个新的文件组或分区架构。注意，如果你确实更改了聚集索引属性，所有的聚集索引必须被重建，但是仅仅一次。（第一个方法是两次）

　　当使用ALTER INDEX命令重建索引时，非聚集索引不需要重建，因为此时并没有更改索引的定义。然而，你可以指定取代所有的索引名，并请求所有索引被重建。使用ALTER INDEX的另一个优势是你可以仅仅指定一个分区来重建（通过sys.dm_db_index_physical_stats查看）

#联机索引生成（Online Index Building）

　　重建索引的任何方法的默认行为是SQL Server启用一个该索引的独占锁，因此，索引重建时完全不可用。如果是聚集索引，整表不可用。非聚集的则是一个表的共享锁（不能修改，但可以Select），此时查询的性能可能大打折扣。

　　SQL Server 2005新增了一个选项以联机重建一个或多个索引---“ONLINE”。在Alter Index和Create Index中可用，也可以加DROP_EXISTING或不加。一个基本语法示例如下：

ALTER INDEX PK_TransactionHistory_TransactionID ON Production.TransactionHistory REBUILD WITH (ONLINE = ON);

　　联机重建同时维护两个索引的副本：源和目标（后者是新的）。目标被用于等待索引重建时的任何变化。所有的读都是从源读出，修改被应用于源。SQL Server行级版本被使用，因此任何人从索引中提取信息时能读到连续的数据。

下图展示了这个过程的三个阶段：

邀月工作室