【译】索引进阶（十四）：创建，修改，删除索引

此文为翻译，由于笔者语言及技术水平有限，疏漏在所难免，欢迎建议指正。

原文链接：传送门。

Written by David Durant 2012-02-17.

Translated by QXM,

在第十节我们研究了索引的内部结构，在十一节我们研究了平衡树结构的潜在的边界效应：碎片。掌握了索引结构的这些知识，我们能够研究当一个索引被DDL语句或者DML语句影响时会发生些什么。本节我们将研究三个DDL动词的影响：CREATE, ALTER, 以及DROP。下一节我们将研究DML动词：INSERT, UPDATE, DELETE。

创建，修改，删除索引属于索引维护的主题。CREATE, ALTER, DROP是索引维护的动词仅仅是因为SQL SERVER团队希望所有的对象都使用相同的DDL动词。如同你将在本节看到的，当这几个动词与索引一起工作时，它们做的比它们的名字所体现的内容更多：除了修改索引元数据外，它们还使你能够创建，重建，重新组织，禁用，以及删除索引。

当你创建或者修改索引的时候，你可以选择指定一个选项，这些选项成为了索引元数据的一部分并存储在系统表中，它可以通过sys.indexes这个系统视图暴露给你。当查询或者更新数据以及在索引维护中，它们被SQL SERVER所使用。注意在本节中我们提到了几次这些选项的时候并没有对其进行深入的分析，而在本节结束的时候，我们会对所有这些选项做一个讨论。

你的表越大，在索引上执行DDL语句的影响便越大。这种影响不仅体现在对服务器资源的消耗上，而且体现在减慢此时（执行索引DDL语句时）执行的其他查询上。通过了解在DDL和DML操作中索引内部发生了什么， 你可以：

• 理解了规律性进行索引维护的必要性
• 最大化你的维护操作的性能
• 最小化你的维护操作对于其他查询的影响
• 减少这种维护的频率

创建一个索引

我们以创建一个聚集索引开始，创建非聚集索引会在后续进行介绍。

当你创建聚集索引时候会发生什么取决于表的当前状态以及创建索引时指定的选项。

如果：

表已经是一个聚集索引：

一个错误会抛出，一个表不能具有两个聚集索引，因为那将导致表的列同时处于两个不同的顺序，而那是不可能的。

表是空表：

SQL SERVER更新系统表以体现聚集索引的存在，在这个时候不分配任何存储空间。

表具有数据行，并没有非聚集索引：

SQL SERVER更新系统表以体现聚集索引的存在。

SQL SERVER将数据行排序为索引键序列，并把数据行放置在具有你指定的填充因子的页中，并在其之上产生索引的非叶子层级，此时外部 碎片几乎为0。

表具有数据行，并且并具有非聚集索引：

SQL SERVER释放所有被非聚集索引占据的空间，但保留其元数据。

SQL SERVER更新系统表以体现聚集索引的存在。

SQL SERVER建立聚集索引（见之前的情形）。

非聚集索引根据元数据进行重建，这别无选择，非聚集索引必须被完全重建，各个条目的书签部分是一个行标识（RID），而现在它将更新为聚集索引键值。因此各个条目和原来相比将具有一个不同的大小。

因此，当你在一个表上创建多个索引的时候，为了节省时间以及工作量，应该首先创建聚集聚集索引，然后再创建非聚集索引。

创建非聚集索引

如果：

表是空的：

SQL SERVER更新系统表以体现非聚集索引的存在，在这个时候并不分配任何存储空间。

表具有数据行：

SQL SERVER更新系统表以体现非聚集索引的存在。

SQL SERVER对表或者其他包含必要列的非聚集索引进行扫描，为每行数据建立条目，并将条目排序为索引键顺序，将这些数据行放置在具有你指定的填充因子的页中，生成索引的非叶子层级。外部碎片此时几乎为0。

更新索引

ALTER INDEX 允许你做如下四件事情：

禁用索引

重建索引

重新组织索引

更新索引选项设置

注意：ALTER IDNEX 不允许你做的一件事情是更改组成索引键的列集合。这仅仅能够通过删除索引然后创建一个包含希望列的同名索引来实现，或者在执行CREATE INDEX 时候指定 DROP_EXISTING 选项。

禁用索引

为了禁用一个索引，可以简单的使用DISABLE 关键字，如下：

ALTER INDEX PK_FragTest_PKCol 
         ON FragTest
         DISABLE;
GO

禁用一个索引并不会从系统表中移除索引的定义，所有禁用的索引可以在以后被重建或者删除。

禁用一个非聚集索引会释放被索引占据的磁盘空间，当一个非聚集索引被禁用的时候，所有依赖于此索引的查询行为都不会存在。

禁用一个聚集索引是释放被索引的非叶子层级占据的存储空间，而使得表的数据行处于不可及的状态，当一个聚集索引被禁用的时候，这个表不能进行查询及更新。

因为禁用索引是通过释放磁盘空间来完成的，这个过程需要少量的处理器时间及日志文件IO。

禁用索引的最主要原因是重建索引时节省磁盘空间。如果索引没有被禁用，那么重建进程会保留索引的原始版本直到新版本被建立，导致了两个版本的索引同时消耗磁盘空间。通过首先删除索引，整个索引占据的磁盘空间在重建过程中便节省了下来，重建一个已删除索引大概需要重建一个未删除索引的1/5的磁盘空间。

重建索引

重建索引在重新建立索引的同时允许你更新选项配置，例如：

ALTER INDEX PK_FragTest_PKCol 
         ON FragTest
         REBUILD 
      WITH ( FILLFACTOR = 75
           , SORT_IN_TEMPDB = ON
           , MAXDOP = 3 );

仅仅是在ALTER INDEX语句中指定的选项会被更新到索引的元数据中，所有其他的会保持不变。

因此，在索引被重建之后，外部碎片会接近于0，页的覆盖率会处于或者接近于你在ALTER INDEX语句中指定的填充因子。如果在ALTER INDEX语句中没有指定填充因子，从索引元数据中获取到的填充因子将会被使用。

重新组织索引

重新组织索引具有一个目的：碎片。它被用来减少外部碎片，并且很可能使得页覆盖率接近于在索引元数据中指定的填充因子的值。如同你所见到的，重新组织索引提供了比重建索引更少的选项。但是重新组织索引也需要更少的资源并对于其他用户的影响更少。

关于重新组织索引需要记住四件事情：

重新组织索引不会增加索引的大小，没有新的区会被分给这个索引。

重新组织索引或许会减少索引的大小，释放一些不再需要的页。

当正在被重新组织时，其他进程可以使用这个索引。

当重新组织索引时候唯一可以指定的选项是LOB_COMPACTION，因此，当重新组织一个索引时不能指定一个新的填充因子。

重新组织一个索引需要页级锁被允许，其是默认值。因为在它被重新组织的时候其他进程可以使用这个索引，那么SQL SERVER在索引重新组织时将各个页而不是整个索引锁起来是至关重要的。

如果索引的 ALLOW_PAGE_LOCKS 选项被设置为OFF，那么它将不会被重新组织。

因此，一个典型的重新组织语句会看起来是这样的：

ALTER INDEX PK_FragTest_PKCol 
         ON FragTest
         REORGANIZE; 

或者是：

ALTER INDEX PK_FragTest_PKCol 
         ON FragTest
         REORGANIZE
      WITH ( LOB_COMPACTION = OFF );

SQL SERVER重新组织索引分为两个阶段：

阶段1：定位于内部碎片：

阶段1受限于它所能做的是因为重新组织进程禁止增加索引的大小，它能够释放页，但不能够添加页。因此，一个平均覆盖率小于索引元数据中指定的填充因子的索引当被重新组织时可能会变小。但是平均覆盖大于填充因子的索引并不会因为重新组织而变得大一些。

阶段1 处理索引的逻辑顺序，一次处理8个页，从1~8页开始，然后是2~9页，接下来是3~10页。直到全部的页被检查一遍。对于每一组8个页来说，SQL SERVER会检测是否这8个页能够在不超过填充因子的情况下被压缩为7个页，如果可以，那么它便会将这些条目压缩进7个页中，而释放掉第8个页。

阶段2：定位于外部碎片：

第二阶段将索引的物理顺序与它的逻辑顺序进行对齐，一次处理一个页。它读取索引逻辑上的第一页以及物理上的第一页，并且判断，如果它们不是同一个页，便交换它们的内容。它对两个顺序的每一个成功也重复这个过程，当它完成最后一页的交换，两个顺序会变得一致并且具有最小的外部碎片数。

其最终结果是一个具有最小外部碎片的索引，并且很有可能也会减少内部碎片。

虽然与索引重建相比，在功能上有所限制，然而在达到这些益处的时候，我们没有消耗额外的磁盘空间，只有一小部分的内存消耗，并且索引仍然可以被其他应用所使用。

因此当索引碎片是个问题的时候，是重建，禁用后重建，还是重新组织？这选择权在于你。

在第15节（原文第15节）---最佳模式与实践---我们提供了一些建议。

更改索引的元数据

一些索引的选项设置可以不用重建索引或者重新组织索引来进行更新：

ALLOW_ROW_LOCKS 
ALLOW_PAGE_LOCKS
IGNORE_DUP_KEY
STATISTICS_NORECOMPUTE.

下面的SQL 语句是更改索引而仅仅更新其选项设置的例子：

ALTER INDEX PK_FragTest_PKCol 
         ON FragTest
      SET ( ALLOW_ROW_lOCKS = ON
          , ALLOW_PAGE_lOCKS = ON
          , STATISTICS_NORECOMPUTE = OFF );
GO

删除索引

当你删除一个索引的时候，分配的磁盘空间被释放，对用户来说，磁盘空间可以被其他对象所使用，并且所以的元数据从系统表中被删除。

如同在第8节提到的那样--唯一索引， 你不能删除一个正在支持一个主键或者一个唯一性约束的索引。

很重要的一点是删除一个聚集索引并不会删除其底层的依赖表，分配的非叶子层级空间被释放，但是作为表数据行的叶子层级仍旧保留了下来。聚集索引变成了一个堆结构，这意味着表中的任何非聚集索引会被自动重建。

因此，当你删除若干个索引时候，如果其中的一个是聚集索引，那么节省是时间和工作量的做法是先删除非聚集索引，然后再删除聚集索引。

选项

CREATE INDEX的选项可被分为3个类别：

1. 影响索引创建，但后续不影响索引使用的那些选项，大部分选项都属于这一类别。
2. 影响后续索引使用，但是不影响索引创建的那些选项，ALLOW_ROW_LOCKS 以及ALLOW_PAGE_LOCKS 属于这个类别。
3. 同时影响索引创建和 post-creation活动的选项， DATA_COMPRESSION 是这样的一个例子。

这些选项列举如下，除非特别说明，它们仅仅影响索引创建。

FILLFACTOR：

允许你指定期望的页覆盖率。仅仅影响叶子节点。默认值是0，它并不是指将页置为空，相应的，它指的是：”每页留下足够插入一行的空闲空间”。

PAD_INDEX：

指定是否FILLFACTOR将被应用于非叶子层级（除了叶子层级之外）。

SORT_IN_TEMPDB：

指定用来创建索引的排序是在表的数据库中还是在tempdb中进行。

IGNORE_DUP_KEY：

见第八章--唯一性索引。

STATISTICS_NORECOMPUTE：

将在14节（原文第14节）介绍--索引统计

DROP_EXISTING：

注意：DROP_EXISTING仅仅和 CREATE INDEX一起是有效的。

DROP_EXISTING = ON：

如果一个同名同类型的索引存在，那个索引会被删除，然后一个同名并且具有指定定义的索引将被创建。删除旧的索引意味着在新索引的创建开始之前，被旧的索引占据的存储空间都会被释放，并且旧索引的元数据都被移除。如果你希望新的索引具有和旧的索引一样的选项，例如FILLFACTOR，你必须在CREATE INDEX 语句中指定这些选项值。

如果一个同名但不同类型的索引存在，那么便会抛出一个错误。

如果没有一个同名索引存在，那么具有指定定义的索引便会被建立。

DROP_EXISTING = OFF：

如果一个同名索引存在，那么会抛出一个错误。

如果没有一个同名索引存在，那么具有指定定义的索引便会被建立。

ONLINE：

指定在索引创建过程中，索引的表对于其他的连接是否是可访问的。如果一个非聚集索引被创建，不管这个设置是啥，SELECT 语句都能够访问这个表，此选项仅在SQL Server Enterprise， Developer 和Evaluation版本可用。

ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS：

在SQL SERVER 2005添加进来，提供数据库所有者对于表锁的控制，其影响索引的锁，如下表所示：

Settings	Allow Row Locks	Allow Page Locks	Allow Index Lock
Both ON	Yes	Yes	Yes
ROW off – PAGE on	No	Yes	Yes
ROW on – PAGE off	Yes	No	Yes
Both OFF	No	No	Yes

这些选项不会影响索引的创建，它们是创建后（post-creation） 选项，并且如果行版本被用作数据库的并发模式，它们具有有限的影响。

重新组织一个索引需要页级别锁被允许。

关掉一个或者两个选项能够在重的查询负担时候减少锁放大数量。很可能从这个选择受益的索引是那些大量查询但很少更新的索引。

如果你是一个经验丰富的数据库所有者并理解SQL SERVER的锁机制，并对于提出的设置有一个可靠的测试，那么可以指定这些选项。

MAXDOP：

用来控制在索引创建过程中可使用的处理器的最大数量。

DATA_COMPRESSION：

指定哪种类型的数据压缩会被应用于此索引中，同时影响索引创建及创建后的活动。数据压缩超出了本阶级系列的主题。

结论

CREATE INDEX使得你能够创建索引并指定一些选项。

ALTER INDEX 被用来创建，禁用，重建，重新组织，及删除一个索引，并更改其选项设置。

ALTER INDEX不能被用来从索引添加或者移除列， CREATE INDEX必须与DROP_EXISTING 一起使用来完成此更改。

比起索引重建来，索引重新组织需要更少的时间，资源，并且是可执行访问的，但是在减少碎片上并没有重建索引更有效果。

禁用一个非聚集索引会释放掉索引的分区并使得其对于查询来说不可见，禁用一个聚集索引使得它释放掉索引的非叶子节点并使得表变得不可访问，一个禁用的索引仅可被重建或者删除。重建一个已删除的索引比重建一个未删除的索引需要更少的磁盘空间。

仅仅通过重建许多选项设置可被重新重置。

创建或者删除聚集索引导致表的所有非聚集索引被重建。

当一个表具有多个索引时，首先创建聚集索引，然后创建非聚集索引，当删除这些索引的时候，先删除非聚集索引，然后删除聚集索引。

删除一个聚集索引实，并不删除表，表会变回一个堆，并且所有的非聚集索引被自动重建。

【完结】