SQL Server索引进阶第十二篇：索引的创建，修改和删除

 索引设计是数据库设计中比较重要的一个环节，对数据库的性能其中至关重要的作用，但是索引的设计却又不是那么容易的事情，性能也不是那么轻易就获取到的，很多的技术人员因为不恰当的创建索引，最后使得其效果适得其反，可以说“成也索引，败也索引”。

    本系列文章来自Stairway to SQL Server Indexes，然后经过我们团队的理解和整理发布在agilesharp，希望对广大的技术朋友在如何使用索引上有所帮助

系列文章索目录：

SQL Server索引进阶第一篇：索引介绍
SQL Server索引进阶第二篇：深入非聚集索引
SQL Server索引进阶第三篇：聚集索引
SQL Server索引进阶第四篇：页和区

SQL Server索引进阶第五篇：索引包含列
SQL Server索引进阶第六篇：书签
SQL Server索引进阶第七篇：过滤的索引

SQL Server索引进阶第八篇：唯一索引
SQL Server索引进阶第九篇：解读执行计划
SQL Server索引进阶第十篇：索引的内部结构
SQL Server索引进阶第十一篇：索引碎片分析与解决（上）
SQL Server索引进阶第十一篇：索引碎片分析与解决（中）-碎片发生原理深度剖析
SQL Server索引进阶第十二篇：索引的创建，修改和删除
SQL Server索引进阶第十三篇：Insert,Update,Delete语句
SQL Server索引进阶第十四篇：索引统计
SQL Server索引进阶第十五篇：索引的最佳实践

    创建、修改和删除索引是属于索引维护部分中的内容，作为数据库对象，索引同样也用CREATE, ALTER和 DROP这三个DDL语句进行操作。但不同的是，对于索引来说这几个语句所能提供的功能要远远超过其名字所示，允许你创建、整理、删除甚至修改索引的metadata。

    当你创建或是修改索引时，你可以设置一些参数，这些参数作为索引的一部分存储在系统表中，你可以通过sys.indexes系统视图进行查看。当SQL Server查询或更新数据以及维护索引时需要这些数据来帮助更好的完整任务。本篇文章将会讲到这些参数，但不会深入细节。

    索引所在的表越大时，对其索引的DLL语句影响也就越大。这个影响表现在对于服务器资源的消耗和降低查询执行速度。所以通过学习当执行DDL或DML语句时，索引内部的执行过程，你可以:

• 理解为什么经常需要维护索引
• 执行维护操作的过程尽量不降低性能
• 减少维护索引过程对于其它查询的影响
• 减少索引维护的频率
  

创建索引

    我们首先创建聚集索引，然后创建非聚集索引。
    创建聚集索引的内部过程取决于当前表的状态以及创建聚集索引过程中指定的参数。
假如:

表已经是聚集索引了:

发生错误，一个表中不能含有两个聚集索引，因为同一时间使得数据按照不同的物理顺序排列是不可能的。

表是空的:

SQL Server仅仅更新系统表来让自己知道这个表是聚集索引结构.不需要分配空间。

表中有数据，但表上没有非聚集索引:

SQL Server更新系统表来让自己知道这个表是聚集索引结构.
SQL Server将表中的行按照索引键的数据进行排序，根据指定的填充因子将数据填充进页，然后生成索引的非叶子节点。这个过程几乎不存在外部碎片。

表中有数据，表上存在非聚集索引:

SQL Server释放由非聚集索引占用的所有空间，但不删除其metadata。
SQL Server更新系统表来让自己知道这个表是聚集索引结构。
SQL Server创建聚集索引（过程看上面）。
非聚集索引通过刚才没有删除的metadata进行重建,没有其它选择。非聚集索引必须完全重建，因为之前非聚集索引的书签指向的是rowid，但现在书签需要存储键值。
因此，如果你需要在表上创建多个索引，先建立聚集索引，然后再建立非聚集索引，这样更加节省时间。

创建非聚集索引

如果:
表是空的：

SQL Server仅仅更新系统表来让自己知道这个表上含有非聚集索引.不需要分配空间。

表中存在数据：

SQL Server更新系统表来让自己知道这个表上含有非聚集索引.不需要分配空间。
SQL Server扫描表，或是其它可以包含这个索引的非聚集索引。为表中的每一行创建索引条目，按照索引键排序，根据指定的填充因子将这些条目填充进页，然后生成索引的叶子节点。这个步骤几乎不会产生外部碎片。

修改索引

ALTER INDEX语句可以被用来做如下四件事:

• 停用索引
• 重建索引
• 整理索引
• 修改索引选项
  

注意;ALTER INDEX语句不能修改索引中的索引键的组合，如果想要实现这点只能通过删除索引再建立索引，也可以通过CREATE INDEX语句配上DROP_EXISTING选项。

停用索引

    停用索引只需要使用DISABLE关键字，比如:
  ALTER INDEX PK_FragTest_PKCol ON FragTest DISABLE; GO


    停用一个索引并不会使得索引的定义信息从索引表中被移除。所有被停用的索引都可以之后执行重建或删除操作。
    停用一个非聚集索引可以将非聚集索引所占用的空间释放出来，因此当索引被停用之后，SQL Server上运行的查询就会当作这个索引不存在。
    对于停用聚集索引来说，则是释放掉聚集索引非叶子节点所占用的空间。因为叶子节点就是表本身，所以不会释放叶子节点，但由于没有非叶子节点进行索引，所以被停用的聚集索引（也就是表本身）不能再用于查询或更新。

    停用索引涉及到释放磁盘空间，因此这个过程需要一些IO操作以及写入日志文件。

    存在索引停用的最重要的目的是为了节省磁盘空间。假如重建索引的时候不停用索引，则SQL Server需要维护两个版本的索引，新建的索引成功后才会删除老索引，因此造成磁盘空间的浪费。而重建索引之前首先删除索引的话，就能剩下磁盘空间了。通常来说，重建一个已经删除的索引需要的空间是重建没有删除索引的五分之一。

重建索引

重建索引不仅可以重建索引，还可以改变选项，比如:

1. ALTER INDEX PK_FragTest_PKCol
2. ON FragTest
3. REBUILD
4. WITH ( FILLFACTOR = 75
5. , SORT_IN_TEMPDB = ON
6. , MAXDOP = 3 )

复制代码

上面重建索引重新指定的选项会更新到系统表中，其它没有指定的选项保持不变。
此外，索引重建之后，外部碎片几乎为0.所有页内都填充到填充因子所指定的值。如果上面参数你还指定了填充因子，这个填充因子在重建索引时立刻生效。

整理索引

    整理索引的目的只有一个:消除碎片。整理索引被用于消除外部碎片，并将页中填满到填充因子所指定的程度。虽然整理索引所能提供的选项要小于重建索引，但同时整理索引消耗的资源以及对用户查询的影响也是小于重建索引的。
    整理索引时要记住的四件事:

• 整理索引不会增加索引的大小，也不需要额外的存储空间，相反，整理索引会减少索引的大小，并释放不需要的页所占的空间。
• 索引在整理的过程中可以继续使用
• 整理索引唯一能修改的选项是LOB_COMPACTION,整理索引不能修改填充因子的值。
• 整理索引需要索引允许页锁，这是建立索引时的默认值。因为整理索引的过程中，索引依然可用，SQL Server需要在其它查询使用索引时对索引中的特定页进行加锁。而如果ALLOW_PAGE_LOCKS选项设置成了OFF，则无法整理索引。
  

因此，常见的整理索引的语句比如:

1. ALTER INDEX PK_FragTest_PKCol
2. ON FragTest
3. REORGANIZE ;

复制代码

或是:

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

复制代码

SQL Server将整理索引分为两个阶段执行。

阶段一：主要整理内部碎片

    这个阶段所能做的是非常有限的，因为正如前面提到的，整理索引不能增加额外的页。因此如果每页平均的数据小于填充因子标识的数据，则可以通过整理索引减少索引大小，但平均数据如果大于索引因子的填充值的话，则不能通过整理索引增长索引的大小。

    阶段1按照逻辑顺序处理索引。一次处理八个页。比如从第一页到第八页，从第二页到第九页，从第三页到第十页，直到整个索引被检查完。对于一次八个页的检查来说，SQL Server会看这八个页中的内容是否可以在特定填充因子的情况下压缩到7个页中，如果可以，则将这八个页压缩到七个页中并释放第8个页。

阶段二:主要处理外部碎片

    阶段二主要按照索引的逻辑顺序来整理物理顺序。SQL Server读取逻辑上的第一页和物理上的第一页，如果它们不是同一个页，则交换其内容，每次一页，直到整理完索引的最后一页。这个过程完成后，则索引的外部碎片被降到了最低。

    整理索引完成后，外部碎片和内部碎片都会降到可以接受的程度。
    整理索引和重建索引相比起来虽然功能有限，但这个过程不需要额外的磁盘空间，并只需要非常少量的内存消耗。最重要的一点是在整理的过程中索引依然可以使用。

    所以对于处理索引碎片的选择包括了：重建，停用和重建，整理索引。在本系列的第15篇中我将会详细讲述关于索引的最佳实践。

修改索引的Metadata

有一些索引选项可以在不用重建或是整理索引的情况下进行修改。

1. ALLOW_ROW_LOCKS
2.     ALLOW_PAGE_LOCKS
3.     IGNORE_DUP_KEY
4.     STATISTICS_NORECOMPUTE.

复制代码

下面的示例语句显示了如何修改这些选项:

1. ALTER INDEX PK_FragTest_PKCol
2. ON FragTest
3. SET ( ALLOW_ROW_LOCKS = ON
4. , ALLOW_PAGE_LOCKS = ON
5. , STATISTICS_NORECOMPUTE = OFF ) ;
6. GO

复制代码

删除索引

    删除索引后，索引所占用的空间被释放，并且从系统表中删除索引的metadata。
    我们第八篇关于唯一索引的部分提到过，你不能在有主键或唯一约束的情况下删除对应的索引。

    值得注意的是，删除聚集索引并不会删除其表，仅仅释放非叶子节点。但等同于表本身的叶子节点并不会被删除，这些叶子页将会按堆存放，同时所有的非聚集索引也会被自动重建。
    因此，如果删除多个索引时，首先要删除非聚集索引，然后再删除聚集索引。

选项

在使用CREATE INDEX语句时可以设置的选项分为三类:
1.影响索引创建，但并影响索引使用的选项，大多数选项都属于这一类。
2.影响索引的使用，但不影响索引的创建的选项。ALLOW_ROW_LOCKS和ALLOW_PAGE_LOCKS 选项都属于这一类。
3.既影响索引创建又影响索引使用的选项，比如说DATA_COMPRESSION选项。

下面就是这些选项的说明，除非特别注明了，否则都属于上面提到的第一类选项。

FILLFACTOR:

指定页面的填充因子，仅仅影响叶子节点，默认值是0，也就是每一页允许完全填满。

PAD_INDEX:

指定填充因子是否可以存在于非叶子节点。

SORT_IN_TEMPDB：

指定创建索引过程的排序操作实在数据库空间操作还是TempDB上操作。

IGNORE_DUP_KEY:

在第8篇关于唯一索引的文章说已经说过了。

STATISTICS_NORECOMPUTE:

在第14篇索引统计中会详细说明。

DROP_EXISTING:

注意：这个选项仅仅可以在CREATE INDEX中使用。

DROP_EXISTING = ON:

如果创建过程中已经存在了同名的索引和索引类型（类型指的是聚集或是非聚集），则删除掉旧的索引并重新创建新的索引。
如果已经有了同名索引，但类型不同，则会报错。
如果没有同名索引，则根据定义直接创建新的索引。

DROP_EXISTING = OFF:

    如果存在同名索引，则报错。
    如果不存在同名索引，则根据索引定义直接创建新的索引。

ONLINE:

     这个选项可以指定当重建索引的时候其它SPID是否可以访问这个索引。如果创建的是非聚集索引，则SELECT语句都可以访问底层表。这个选项只能在企业版，开发版和评估版中使用。

ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS:

自动SQL Server 2005开始，允许根据这个选项来控制锁升级，详细如表1所示。

设置	允许行锁	允许页锁	允许索引锁
两个选项都是ON	是	是	是
ROW off – PAGE on	否	是	否
ROW on – PAGE off	是	否	是
两个选项都是OFF	否	否	是

表1.索引锁

    这两个选项都不会影响索引的创建，它们都是创建之后影响索引使用的选项，如果隔离等级允许行版本控制，则这个选项无关紧要。
    整理索引需要ALLOW_ROW_LOCKS 设置为ON。
    两个都设置为OFF，或是设置其中一个为OFF，使得在大量负载的情况下减少锁升级。指定这个选项对于大量查询，很少更新的索引非常有用。
    这两个选项需要你对数据库的原理和锁的原理有比较透彻的了解。

MAXDOP:

指定创建索引的时候可以使用几个CPU内核。

DATA_COMPRESSION:

数据压缩选项。这个选项不仅影响索引的创建，还会影响索引的使用。有关数据压缩的话题已经超出了本篇文章的范围。

总结

    CREATE INDEX语句允许你创建索引并设置选项。
    ALTER INDEX可以创建，停用，重建，整理和删除索引。
    ALTER INDEX不能为索引添加或删除列，只有通过CREATE INDEX语句。

    整理索引所需的时间和资源更少，并且在整理的过程中允许继续使用索引。
    停用非聚集索引使得其占用的空间被释放，并且不能够在被SQL Server使用。停用聚集索引使得非叶子节点所占的空间被释放并且表不能继续被访问。被停用的索引只能执行重建或删除操作。重建一个已经存在的索引所需的空间要大于重建被停用的索引。

    很多选项只能在重建索引的过程中应用。
    创建或删除聚集索引导致其相关联的所有非聚集索引重建。
    当一个表需要多个索引时，要先创建聚集索引，再创建非聚集索引。而删除的过程则相反。
    删除聚集索引并不会导致删除表，而是使得表中数据按堆存放以及相关的非聚集索引被重建。