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

SQLServer索引进阶第⼗⼆篇：索引的创建，修改和删除
索引设计是数据库设计中⽐较重要的⼀个环节，对数据库的性能其中⾄关重要的作⽤，但是索引的设计却⼜不是那么容易的事情，性能也不是那么轻易就获取到的，很多的技术⼈员因为不恰当的创建索引，最后使得其效果适得其反，可以说“成也索引，败也索引”。

本系列⽂章来⾃，然后经过我们团队的理解和整理发布在，希望对⼴⼤的技术朋友在如何使⽤索引上有所帮助
系列⽂章索⽬录：
创建、修改和删除索引是属于索引维护部分中的内容，作为数据库对象，索引同样也⽤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使⽤。

停⽤聚集索引使得⾮叶⼦节点所占的空间被释放并且表不能继续被访问。

被停⽤的索引只能执⾏重建或删除操作。

重建⼀个已经存在的索引所需的空间要⼤于重建被停⽤的索引。

很多选项只能在重建索引的过程中应⽤。

创建或删除聚集索引导致其相关联的所有⾮聚集索引重建。

当⼀个表需要多个索引时，要先创建聚集索引，再创建⾮聚集索引。

⽽删除的过程则相反。

删除聚集索引并不会导致删除表，⽽是使得表中数据按堆存放以及相关的⾮聚集索引被重建。