索引设计是数据库设计的重要组成部分，对数据库的性能起着至关重要的作用，但是索引的设计并不是那么容易，性能也不是那么好所以很容易获取，很多技术人员创建索引不当，导致效果适得其反。可以说“索引是成功的，索引也是失败的”。创建、修改和删除索引属于索引维护部分。作为数据库对象，索引也是通过三种DDL语句进行操作的：CREATE、ALTER和DROP。但不同的是，对于索引来说，这些语句所能提供的功能远远超过其名称所暗示的，允许您创建、组织、删除甚至修改索引的元数据。在创建或修改索引时，可以设置一些参数，这些参数作为索引的一部分存储在系统表中，可以通过sys.indexes系统视图查看。当SQLServer查询或更新数据和维护索引时，需要此数据来帮助更好地完成任务。本文将涵盖这些参数，而不会详细介绍。索引所在的表越大，对其索引的DLL语句的影响就越大。这种影响表现为服务器资源的消耗和查询执行速度的降低。所以通过学习索引在执行DDL或DML语句时的内部执行过程，可以：1.理解为什么要经常维护索引2.执行维护操作的过程尽量不要降低性能3.减少影响维护索引过程对其他查询的影响4.降低索引维护频率创建索引：我们先创建聚集索引，然后再创建非聚集索引。创建聚簇索引的内部过程取决于表的当前状态和在创建聚簇索引期间指定的参数。如果：表已经是聚簇索引：出现错误，一个表不能包含两个聚簇索引，因为不可能同时按不同的物理顺序排列数据。表为空：SQLServer只更新系统表，让自己知道该表是聚集索引结构。没有分配空间。表中有数据，但表上没有非聚集索引：SQLServer更新系统表让自己知道该表是聚集索引结构。SQLServer根据索引键的数据对表中的行进行排序，并按照指定的填充因子将数据填充到页中，然后生成索引的非叶子节点。在此过程中几乎没有外部碎片。表中有数据，表上有非聚集索引：SQLServer释放非聚集索引占用的所有空间，但不删除其元数据。SQLServer更新系统表，让自己知道该表是聚集索引结构。SQLServer创建聚集索引（过程见上文）。非聚集索引是通过刚才没有删除的元数据重建的，没有别的选择。非聚集索引必须完全重建，因为以前非聚集索引的书签指向rowid，现在书签需要存储键值。因此，如果需要在一个表上创建多个索引，先创建聚集索引再创建非聚集索引会更省时。创建非聚集索引：表为空：SQLServer只是更新系统表让自己知道这个表包含一个非聚集索引。无需分配空间。表中有数据：SQLServer更新系统表让自己知道这个表包含了一个非聚集索引。无需分配空间。SQLServer扫描表或任何其他可以包含该索引的非聚集索引。为表中的每一行创建索引条目，按索引键排序，将这些条目按照指定的填充因子填充到页面中，并生成索引的叶子节点。此步骤几乎不产生外部碎片。修改索引：ALTERINDEX语句可以用来做以下四件事：1.禁用索引2.重建索引3.组织索引4.修改索引选项注意；ALTERINDEX语句不能修改索引中索引键的组合，如果要实现，只能先删除索引再建索引，也可以使用带DROP_EXISTING选项的CREATEINDEX语句。禁用索引：禁用索引只需要使用DISABLE关键字，如：ALTERINDEXPK_FragTest_PKColONFragTestDISABLE；GOdisableanindex不会使索引的定义信息从索引表中移除。所有停用的索引都可以在以后重建或删除。停用非聚集索引可以释放非聚集索引所占用的空间，因此当索引停用时，在SQLServer上运行的查询会将其视为索引不存在。关闭聚簇索引，释放聚簇索引的非叶子节点占用的空间。因为叶子节点是表本身，所以叶子节点不会被释放，但是由于没有非叶子节点进行索引，停用的聚簇索引（即表本身）不能再用于查询或更新.停用索引涉及释放磁盘空间，因此此过程需要一些IO操作和写入日志文件。索引停用最重要的目的是节省磁盘空间。如果重建索引时不禁用索引，SQLServer需要维护两个版本的索引，只有在新索引创建成功后才会删除旧索引，造成磁盘空间的浪费。而如果在重建索引之前先删除索引，则可以留出磁盘空间。通常，重建已删除索引所需的空间是不删除重建索引所需空间的五分之一。Rebuildindex：Rebuildindex不仅可以重建索引，还可以改变选项，如：ALTERINDEXPK_FragTest_PKColONFragTestREBUILDWITH(FILLFACTOR=75,SORT_IN_TEMPDB=ON,MAXDOP=3);上面重新指定的重建索引的选项会更新到系统表中，其他未指定的选项保持不变。另外，索引重建后，外部碎片几乎为0。所有页面都填充到fillfactor指定的值。如果在上述参数中还指定了一个填充因子，这个填充因子会在重建索引时立即生效。组织索引：组织索引只有一个目的：消除碎片。整理索引用于消除外部碎片并将页面填充到fillfactor指定的范围。虽然整理索引提供的选项比重建索引小，但是整理索引消耗的资源和对用户查询的影响也比重建索引小。对索引进行碎片整理时要记住四点：1.碎片整理不会增加索引的大小，不需要额外的存储空间，相反，碎片整理会减少索引的大小，释放不需要的页面占用的空间。2.索引在排序过程中可以继续使用。3.collat??ionindex唯一可以修改的选项是LOB_COMPACTION，不能修改collat??ionindex的fillfactor的值。4.组织索引要求索引允许页锁，这是建索引时的默认值。因为索引在碎片整理过程中仍然可用，所以SQLServer需要在其他查询使用索引时锁定索引中的特定页面。并且如果ALLOW_PAGE_LOCKS选项设置为OFF，则无法对索引进行排序。因此，常见的索引整理语句如：或：SQLServer将整理索引分为两个阶段。Phase1：主要是内部碎片整理这个阶段能做的事情非常有限，因为前面说过，碎片整理索引不能添加额外的页面。因此，如果每页平均数据小于填充因子所标识的数据，则可以通过对索引进行排序来减小索引的大小，但如果平均数据大于索引因子的填充值，则索引的大小不能通过对索引进行排序来增加索引的数量。阶段1按逻辑顺序处理索引。一次处理八页。比如从***页到第八页，从第二页到第九页，从第三页到第十页，直到检查完整个索引。对于八页的检查，SQLServer会看这八页的内容在一定的填充因子下是否可以压缩成7页，如果可以，则将八页压缩成七页。页并释放第8页。Phase2：主要处理外部碎片。Phase2主要是根据索引的逻辑顺序整理物理顺序。SQLServer读取逻辑第一页和最后一个物理页，如果它们不是同一页，则交换它们的内容，一次一页，直到索引的最后一页被排序。此过程完成后，索引的外部碎片减少为***。碎片整理完成后，外部和内部碎片都会减少到可接受的水平。虽然与重建索引相比，碎片整理在功能上受到限制，但该过程不需要额外的磁盘空间并且需要非常少的内存消耗。最重要的一点是索引在整理过程中仍然可以使用。因此，处理索引碎片的选项包括：重建、停用和重建以及对索引进行碎片整理。在本系列的第15部分中，我将详细介绍索引的最佳实践。修改索引元数据？有一些索引选项可以在不重建索引或对索引进行碎片整理的情况下进行修改。以下示例语句显示如何修改这些选项：删除索引：删除索引后，释放索引占用的空间，并从系统表中删除索引的元数据。在我们关于唯一索引的第八篇文章中提到，当有主键或唯一约束时，您不能删除相应的索引。值得注意的是，删除聚簇索引并不会删除其表，只是释放非叶子节点。但是相当于表本身的叶子节点不会被删除。这些叶页将存储在堆中，所有非聚集索引将自动重建。因此，如果删除多个索引，先删除非聚集索引，再删除聚集索引。Options：使用CREATEINDEX语句时可以设置的选项分为三类：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：注意：该选项只能在CREATEINDEX中使用。DROP_EXISTING=ON：如果创建过程中已经存在同名索引和索引类型（类型指聚合或非聚合），则删除旧索引，重新创建新索引。如果已经存在同名不同类型的索引，则会报错。如果没有同名索引，则直接按定义新建一个索引。DROP_EXISTING=OFF：如果存在同名索引，则报错。如果没有同名索引，则直接根据索引定义新建一个索引。ONLINE：这个选项可以指定重建索引时其他SPID是否可以访问这个索引。如果创建非聚集索引，则SELECT语句可以访问基础表。此选项仅在企业版、开发人员版和评估版中可用。ALLOW_ROW_LOCKS和ALLOW_PAGE_LOCKS：自动启动SQLServer2005，允许根据该选项控制锁升级，详细如表1所示。这些选项都不影响索引的创建。它们是影响索引创建后使用的选项。如果隔离级别允许行版本控制，则此选项无关紧要。整理索引要求ALLOW_ROW_LOCKS为ON。将两者都设置为OFF，或将其中之一设置为OFF，可以减少重负载下的锁升级。指定此选项对于频繁查询、很少更新的索引很有用。这两个选项需要你对数据库和锁的原理有透彻的了解。MAXDOP：指定创建索引时可以使用多少个CPU核。DATA_COMPRESSION：数据压缩选项。此选项不仅会影响索引创建，还会影响索引使用。数据压缩的主题超出了本文的范围。总结CREATEINDEX语句允许您创建索引和设置选项。ALTERINDEX可以创建、停用、重建、整理碎片和删除索引。ALTERINDEX不能为索引增加或删除列，只能通过CREATEINDEX语句。修整索引需要更少的时间和资源，并且允许在修整过程中继续使用索引。停用非聚集索引会释放它占用的空间，SQLServer无法再使用它。停用聚簇索引会导致非叶节点占用的空间被释放，无法再访问该表。禁用的索引只能重建或删除。重建现有索引比重建禁用索引需要更多空间。许多选项只能在索引重建期间应用。创建或删除聚簇索引会导致重建所有关联的非聚簇索引。当一个表需要多个索引时，先创建聚簇索引，再创建非聚簇索引。删除过程相反。删除聚簇索引不会导致表被删除，而是会导致表中的数据存储在堆中，并重建相关的非聚簇索引。
