3.3  创建、修改和删除索引

索引的创建、修改和删除与其他对象（例如表）几乎一样。分别来看这些操作，从CREATE开始。

能够以两种方式创建索引：

l    通过显式的CREATE INDEX命令创建；

l    在创建约束时作为隐含对象创建。

每一种方式在能做什么和不能做什么上都有自己特异之处，因此，接下来分别讨论它们。

3.3.1  CREATE INDEX语句

CREATE INDEX语句完成的事情与它听上去一样——它在指定的表或视图上基于声明的列创建索引。

创建索引的语法如下，我将介绍几个迄今为止尚未讨论过的项目。

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]

INDEX <索引名> ON <表或视图名>(<列名> [ASC|DESC] [,...n])

INCLUDE (<列名> [, ...n])

[WITH

[PAD_INDEX = { ON | OFF }]

[[,] FILLFACTOR = <填充因子>]

[[,] IGNORE_DUP_KEY = { ON | OFF }]

[[,] DROP_EXISTING = { ON | OFF }]

[[,] STATISTICS_NORECOMPUTE = { ON | OFF }]

[[,] SORT_IN_TEMPDB = { ON | OFF }]

[[,] ONLINE = { ON | OFF }

[[,] ALLOW_ROW_LOCKS = { ON | OFF }

[[,] ALLOW_PAGE_LOCKS = { ON | OFF }

[[,] MAXDOP = <最大并行度>

]

[ON {<文件组> | <分区方案名> | DEFAULT }]

这里的许多选项上有遗留的语法，因而可以使用这些语法来支持以前版本的SQL Server。尽管如此，不赞成使用这样的语法，并且，它们将在某个时候被删除——我强烈建议尽可能地使用较新的语法。

在创建XML索引上有类似的但有很大不同的语法。该语法将在本节的后面单独讲述。

不严格地说，这里的语句遵循你已经多次见过（并将见到更多）的同样的CREATE <object type> <object name>语法。主要的障碍是几个没有在别的地方见过的参数。

就像将在后面一章的视图中看到的那样，必须向CREATE语句中加入另外的子句，以处理索引其实不是一个孤立的对象的事实。索引必须伴随表或视图出现，因而需要声明索引所“在”（ON）的表。

在ON <table or view name>(<COLUMN NAME>)子句后，所有的事物都是可选的。可以混搭这些选项。这里的许多选项很少用到，但是，某些选项（如FILLFACTOR）能够对系统的性能和行为产生重大的影响，因此，下面来一一讨论它们。

1．ASC/DESC

这两个参数允许为索引选择升序和降序排序顺序。默认值是ASC，正如你所猜想的，默认是升序。

这里可能会有点疑惑，为什么升序与降序对索引有关系呢——如果SQL Server需要相反的排序顺序，只需反向查看索引就可以了。然而，事情不会总是那样简单。如果只涉及一列，或者如果所有的列总是以同样的方式排序，那么，反序查看索引是可以的——但是，如果想在索引中混合升序、降序的排序方式呢？即是说，如果需要一列按升序排序，而另一列按降序排序，这又将如何呢？由于索引列是存储在一起的，因此对一列反向查看索引也将倒转另一列的顺序。如果显式地说明一个列为升序，而另一列为降序，则会直接在物理数据中倒转第二列——忽然间不必改变访问数据的方式了。

下面是一个简短的例子，设想一个生成报表的场景。想要把雇员列表按照雇佣日期进行排序，日期最近的排在前面（降序），但是，还想要按照姓氏进行排序（升序）。在以前的形式下，SQL Server必须进行两个操作——一个用于第一列另一个用于第二列。通过允许控制数据的物理排序顺序，在组合列上获得了灵活性。

一般来说，将不理会这里（再次说明，记住向后兼容性）。可能的一些例外情况是：

l    需要在多个列上混合升序和降序；

l    向后兼容性不是问题。

2．INCLUDE

该参数是SQL Server 2005中新加入的很令人满意的参数。它的目的是为覆盖查询提供更好的支持。当使用的索引包含查询要用到的所有数据时，认为该查询是“覆盖的”。如果需要的全部数据都在索引中，那么就没必要进入到实际的数据页中——只要到达索引的叶级，就得到了所有需要数据，因而能够停止在这里（省下了大量的I/O操作）。

当包含（INCLUDE）列而不是把它们放在ON列表中时，SQL Server只把它们添加到索引的叶级。由于索引叶级的每一行都对应一个数据行，因此，本质上这里所做的是把更多的原始数据包含在索引的叶级。思考一下，你可能会猜到INCLUDE实际上只应用在非聚集索引上（聚集索引已经是叶级的数据了，因而这样做没什么意义）。

这样做有什么关系呢？当本书进一步深入讨论时，SQL Server一旦获得了其实际所需就停止操作。因此，如果在遍历索引时，没继续访问实际的数据行就得到了所有需要的数据，那么将无需前往数据行（这样做有什么意义？）。通过把特定的列包含进索引中，可以利用那个特定索引在叶级“覆盖”查询，从而节省了与使用索引指针到达数据页有关的I/O。

要小心，不要滥用该参数！当INCLUDE列时，将增加索引页在叶级的大小。这意味着该页中的行数将减少，因而要看到同样数目的行可能需要更多的I/O。结果可能是，加快了一个查询也许减慢了其他的查询。套用一部上个世纪八十年代的老电影，“Balance Danielson—balance!”要考虑对系统各部分的影响，而不要只考虑某个时候正在使用的特定的查询。

3．WITH

WITH是一个很容易的参数，它只是用来告诉SQL Server你将要提供一个或多个后面的选项。

4．PAD_INDEX

该参数在语法列表中第一个出现——但是，在理解了PAD_INDEX的作用后，会觉得这样有点古怪。简言之，该参数决定首次创建索引时，索引的非叶级页将有多满（以百分数表示）。在PAD_INDEX中不声明百分比，因为它将使用随后在FILLFACTOR选项中指定的百分比。如果没有FILLFACTOR，设置PAD_INDEX = ON是毫无意义的（这就是为什么先出现该参数会有些古怪）。

5．FILLFACTOR

当SQL Server首次创建索引时，默认情况下，将尽可能满地填充页，仅留下两条记录的空间。可以将FILLFACTOR设置为1到100之间的任何值。这里的数字是一个百分比，它表明一旦索引构造完成，页将有多满。然而，要记住，当页拆分时，数据在两个页之间50-50地分布——除了定期重新创建索引（你应该做的事情——设置维护计划，将在第24章中涉及这方面的内容）外，不能在进行中控制填充的百分比。

当需要调整页的信息密度时会使用FILLFACTOR。按下面的方式来考虑：

l    如果是OLTP系统，则希望FILLFACTOR较低；

l    如果是OLAP或其他非常稳定的（以变化的术语来描述——几乎没有添加和删除）系统，则希望FILLFACTOR尽可能高；

l    如果系统中事务比例中等，且有许多基于它的报表类型的查询，那么可能希望FILLFACTOR中等（不太低，也不太高）。

如果不提供一个值，则SQL Server将在每页最少有一行的情况下，把页填充到差两行满（例如，如果行是8000字符宽，则每页只能放入一行——因此无法达到差两行满）。

6．IGNORE_DUP_KEY

IGNORE_DUP_KEY选项差不多算是一种回避系统的方法。简言之，它使UNIQUE约束与其应有的行为方式有些不同。

通常，唯一约束或唯一索引不允许有任何种类的重复——如果事务试图基于一个定义为唯一的列创建重复值，则事务将被回滚和拒绝。然而，当设置了IGNORE_DUP_KEY选项后，将得到某种混合的行为。虽然仍然会收到错误消息，但是，错误只是警告级别的错误——记录依然没有被插入。

从IGNORE_DUP_KEY的角度看，最后一行的“记录依然没有被插入”是一个重要的概念。没有为事务发出回滚（错误是一个警告错误而非重大错误），但重复行将被拒绝。

为什么要这样做？这是一种不打扰试图插入重复值的事务来存储唯一值的方法。对于任何正在插入将重复的值的过程；或许，关于要插入的行是一个重复行这一点完全无关紧要（没有源自它的逻辑错误）。替代地，该过程的态度更可能是，“嗯，只要我知道那里已经有一个这样的行存在，这就够了——我并不在乎行是否是试图插入的特定行。”

7．DROP_EXISTING

如果指定DROP_EXISTING选项，任何具有讨论中的索引名的现有索引将被删除，以便创建新的索引。当该选项与聚集索引一起使用时，与简单地删除并重新创建现有索引相比，该选项有效得多。为了适应不同的行位置，显式的删除和创建将导致所有的非聚集索引重建两次。然而，如果重建与现有索引完全匹配的索引，SQL Server将会知道它无需触及非聚集索引。如果使用DROP_EXISTING修改了索引的结构，只会重建NCI一次而非两次。此外，不能简单地删除和重建由约束创建的索引，例如，要实现一个确定的填充因子。DROP_EXISTING将用于完成这一使命。

8．STATISTICS_NORECOMPUTE

默认情况下，SQL Server尝试自动化表和索引上统计信息的更新过程。通过选择STATISTICS_NORECOMPUTE选项，表明将由你负责统计信息的更新。要关闭该选项，需要运行UPDATE STATISTICS命令，但不使用NORECOMPUTE选项。

我强烈建议不要使用该选项。为何？查询优化器将使用索引上的统计信息来确定索引对于给定的查询有多大的用处。由于表中的数据大量地增多和减少，并且，由于列中特定的值发生改变，索引上的统计信息在持续地变化着。结合这两个事实，应该能看出，不更新统计信息意味着查询优化器将基于过时的信息运行查询。保持自动更新统计信息功能启用意味着统计信息将规律地更新（多长时间更新一次取决于对表进行更新的频率和类型）。相反，关闭统计信息自动更新意味着要么你的信息会过时，要么你必须设定计划以便手工运行UPDATE STATISTICS命令。

9．SORT_IN_TEMPDB

只有当存储tempdb的驱动器与包含新索引的数据库所在的驱动器在物理上隔离时，该选项才有意义。这在很大程度上是一种管理功能，因此，在简单概述了它是什么以及为什么只有当tempdb在单独的物理设备上时它才有意义后，我不会在这一主题做过多停留。

当SQL Server建立索引时，必须执行很多读取以处理各种各样的索引构造步骤：

(1) 通读所有数据，构造与实际数据的每一行相对应的叶行。正像实际的数据和最终的索引一样，这些会进入到用于临时存储的页中。这些中间的页不是最终的索引页，而是每当排序缓存填满时用于临时存储的地方。

(2) 在中间页中独立运行，以便把它们融入最终的索引叶级页中。

(3) 在构建叶级页的时候，构造了非叶级页。

如果不使用SORT_IN_TEMPDB选项，那么中间页将被写到用于存储数据库的同一个物理文件中。这意味着对实际数据的读操作不得不与构建过程的写操作竞争。二者导致磁盘磁头从一个（读与写）需要的地方移动至另一个不同的地方。结果是磁头持续地来回移动——这会花费时间。

另一方面，如果使用了SORT_IN_TEMPDB，那么中间页将写入到tempdb中，而非数据库自己的文件中。如果它们在不同的物理驱动器上，就说明构建索引的读和写操作之间没有竞争。然而，要记住，只有当tempdb所在的物理驱动器在数据库文件所在的驱动器不同时，该选项才有作用；否则，只是名义上发生了改变，而实际上I/O争用依然是个问题。

如果要使用SORT_IN_TEMPDB，请确保tempdb中有足够的空间来支持大型的文件。

10．ONLINE

如果将该选项设置为ON，将强制表对于一般性的访问保持可用，且不创建任何阻止用户使用索引和（或）表的锁。默认情况下，完全的索引操作将夺取它所需的锁（最终得到表锁），以便完全而有效地访问表。不过，这样做的副作用是把用户阻挡在外了。（是的，它有点自相矛盾。为了让数据库更好用，可能要创建索引，而在创建索引的过程中，实际上却让表不可用了。）

这样一来你可能会想：“如果随时随地构建索引，那用户就不会受影响了，这似乎是个不错的主意吧。”这是糟糕的点子。要记住，任何像这样的索引构建都很可能是I/O非常密集的操作，因而会或多或少地影响到用户。再加上构建索引时为了确保不会妨碍到任何用户，还需要许多额外的开销。如果构建索引时让SQL Server在表上有自由的支配权，则索引的构建将快很多。并且，由于构建索引而影响系统的总时间将少很多。

11．ALLOW ROW/PAGE LOCKS

这是比ONLINE长期的指令，也是一个非常、非常高级的话题。鉴于本书的目的以及眼下对于锁的介绍，这里只做相当简单的解释。

迄今为止，本书的讲述中不断使用的一个术语是锁。如之前讲过的，这是某种避免在数据完整性上有冲突的占位符。这里看到的ALLOW设置是关于索引是否允许那些类型锁的设置指令。该选项属于极致性能调整的内容。

12．MAXDOP

该选项为索引的构建覆盖系统关于最大并行度的设置。并行并非本书中要谈论的内容，因此这里只稍作讲述。

简言之，并行度是指一个数据库操作（这里是索引构建操作）能够使用多少个进程。有一个称为最大并行度的系统设置，允许你设置每个操作能够使用多少个处理器。索引创建中的MAXDOP选项允许设置任何你认为合适的并行度，可以比系统设置的最大并行度更高，也可以更低。

13．ON

在SQL Server中，可以选择不把索引与数据存放在一起，通过使用ON选项单独存储索引。从以下两个观点看这很不错：

l    索引所需的空间能够分散在其他驱动器上；

l    用于索引操作的I/O不会加重物理数据检索的负担。

关于该选项还有更多可讲述的东西，但这涉及非常高级的内容。它十分依赖数据和使用，因而我们认为它超出了本书的范畴。

3.3.2  创建XML索引

XML索引是SQL Server 2005中新增内容，我得承认，对于微软努力实现了它，我有些吃惊。我早就知道有许多这样的团队，并且，我对他们也非常有信心。但是，对像XML这样非结构化的东西进行索引，这是许多人力图解决的问题，可是几乎没有人真正成功完成过。SQL Server团队努力实现了这一点，真是太杰出了。赞誉就先到这里，接下来开始详细讲述关于XML索引的内容。

既然本书迄今为止尚未真正讨论过XML，这又是一个“先有鸡还是先有蛋？”的问题。然而，我认为这更近似于索引的主题而非XML的主题。事实上，除了IGNORE_DUP_KEY和ONLINE选项外，XML创建语法支持所有在前面的CREATE语句中看到过的选项。

那么，先很快了解一点背景知识：与迄今为止讨论过的关系数据不同，XML往往是十分非结构化的数据。它利用标记来识别数据，并能够与模式相关联，模式用于向基于XML的数据提供类型和验证信息。XML的非结构化特性需要“导航”的概念或“路径”信息，以便在XML文档中找到数据“结点”。另一方面，索引试图提供到数据的非常详细的结构和顺序——这多少有些对立。

在SQL Server中，能够在XML类型的列上创建索引。要完成这一创建索引的任务，主要的要求是：

l    包含被索引的XML列的表上必须有聚集索引；

l    必须先在XML数据列上创建“主”XML索引，然后才能创建“辅助”XML索引（马上会有更多的介绍）；

l    只能在XML类型的列上创建XML索引（并且，XML索引是能够在那种数据类型的列上创建的唯一的索引类型）；

l    XML列必须是基表的一部分——不能在视图上创建这种索引。

1．主XML索引

在XML列上创建的第一个索引必须声明为“主”索引。创建主索引时，SQL Server会创建新的聚集索引，该索引把基表的聚集索引与来自任何你指定的XML结点的数据结合在一起。

2．辅助XML索引

这里没什么特别的——与非聚集索引指向聚集索引的聚集键很类似，辅助XML索引以几乎相同的方式指向主XML索引。一旦创建了主XML索引，就能够在该XML列上创建248个更多的XML索引。

3.3.3  随约束隐含创建的索引

我称这样的索引为“意外的索引”。这并不是说索引不应该在那里——当需要约束时，约束创建了索引，索引无疑是在那里的。只是我见过很多这样的情形：系统中唯一的索引是以这种方式产生的索引。通常，这说明系统的管理员和（或）设计者几乎忘记了索引的概念。

另一方面，你还会在这上面发现一种奇特的倾向——管理员或设计者知道如何创建索引，但却不是真正了解如何说明系统中已经有了什么索引，以及这些索引的作用是什么。这种状况的典型代表是重复的索引。只要索引具有不同的名字，SQL Server就会欣然为你创建。

当向表中加入下列两种约束之一时，会创建隐含的索引：

l    PRIMARY KEY约束；

l    UNIQUE约束（也称备用键）。

到目前为止，已经学习了大量的CREATE语法，因此就不再赘述——然而要注意，当作为约束的隐含索引来创建索引时，除{CLUSTERED|NONCLUSTERED}和FILLFACTOR外，所有的选项都不允许使用。

3.3.4  ALTER INDEX

ALTER INDEX命令多少有些欺骗性。截至目前为止，ALTER命令总是与修改对象的定义有关。例如，ALTER（修改）表以添加或禁用约束和列。ALTER INDEX是不同的——该命令全部与维护有关，而与结构则全然不相干。如果需要修改索引的组成，只能要么DROP（删除）然后CREATE（创建）索引，要么以DROP_EXISTING=ON选项CREATE（创建）并使用索引。

正如在本章前面看到的，SQL Server提供了一个选项，用以控制叶级页能够填充到多满，并且，如果选择使用，还有另一个选项用来处理非叶级页。不幸的是，它们是主动先行的选项——它们只应用一次，然后，在需要时，必须通过重新创建索引并重新应用这些选项来再度应用它们。

在随后关于维护的一节中，将了解到更多关于在哪里以及为什么要使用这一命令的内容，但是现在，只需无条件相信你将使用像ALTER INDEX这样的维护命令作为常规的维护程序的一部分。

ALTER INDEX的语法看起来像下面这样：

ALTER INDEX { <索引名> | ALL }

  ON <表或视图名>

  { REBUILD

  [ [ WITH (

    [ PAD_INDEX = { ON | OFF } ]

  | [[,] FILLFACTOR = <填充因子>

  | [[,] SORT_IN_TEMPDB = { ON | OFF } ]

  | [[,] IGNORE_DUP_KEY = { ON | OFF } ]

  | [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ]

  | [[,] ONLINE = { ON | OFF } ]

  | [[,] ALLOW_ROW_LOCKS = { ON | OFF } ]

  | [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ]

  | [[,] MAXDOP = <最大并行度>

      ) ]

    | [ PARTITION = <分区号>

      [ WITH ( <分区重建选项>

        [ ,...n ] ) ] ] ]

| DISABLE

| REORGANIZE

  [ PARTITION = <分区号> ]

  [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]

| SET ([ ALLOW_ROW_LOCKS= { ON | OFF } ]

  | [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ]

  | [[,] IGNORE_DUP_KEY = { ON | OFF } ]

  | [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ]

    )

} [ ; ]

一些选项与CREATE INDEX命令相同，因而这里将跳过对这些选项的重新定义。除此之外，相当多的ALTER特定选项十分精细，且与处理碎片之类的事情有关（很快将涉及碎片和维护），或者更加面向DBA并且通常是即兴使用的，以处理非常特殊的问题。尽管如此，这里的核心内容应该是常规维护计划的部分。

从查看最先的几个参数开始讲述，然后谈及更大的维护计划所需要的选项。

1．索引名称

如果想维护一个特定的索引则可以指定该索引，或者使用ALL表明想要维护与指定的表相关联的所有索引。

2．表名或视图名

很像它听上去那样——想要在其上进行维护的特定对象（表或视图）的名字。注意，必须是一个特定的表（可以给它提供一个列表，然后说，“请处理所有这些！”）。

3．REBUILD

这是用来调整索引的“工业强度”的方法。如果使用该选项运行ALTER INDEX，将完全丢弃旧的索引并从头重新生成新的索引。最后的结果是，得到了真正最优化的索引，索引中所有叶级和非叶级的页都按照定义的那样进行了重新构建（或者使用默认值，或者使用开关修改填充因子等）。如果讨论中的索引是聚集索引，那么也会重新组织物理数据。

默认情况下，页将被构建成差两条记录填充满。正如在CREATE TABLE语法中那样，可能把FILLFACTOR设置为0到100之间的任何值。该值是在数据库完成重新组织后页被填满的程度，以百分比表示。要记住，尽管在页拆分时，数据将按50-50分布在两个页上；但是，除了定期地重建索引外，无法在进行中控制填充的百分比。

在该选项上要小心。一旦开始REBUILD，在完成索引重建之前，实质上，正在使用的索引就没有了。所有依赖该索引的查询可能会变得异乎寻常地慢（可能要以数量级计）。对于这类事情，首先，需要在离线系统上测试，以了解整个过程将花费多少时间。然后，计划在非高峰时段运行（最好有人监控，以确保当高峰时段来临时，它已经回复联机状态）。

它运行时可能有许多副作用，因此，以我之拙见，它应当是数据库管理员的领域。

4．DISABLE

该选项所做的事情如同它声称的那样，只是方式多少有点激烈。如果命令只是为了让索引离线，直至你决定了进一步要做什么，则它是不错选择，但本质上它把索引标记为了不可用。一旦禁用了某个索引，在能够重新激活之前，必须重建索引（不是重新组织，而是重建）。

你极少会自己使用该选项（你更可能做的是删除索引）——它很有可能在SQL Server升级的过程中或者其他一些古怪的情况下用到。

5．REORGANIZE

从开发者的角度看，该选项真太棒了！！！有了REORGANIZE，就仿佛找到了非常恰到好处的方法。当重新组织索引时，得到了比彻底重建索引略逊一筹的完全优化，可是这种方法可以联机进行（用户仍然能使用索引）。

如果仔细的话，上面的讲述可能会让你产生一个问题，“前面说‘略逊一筹’到底是指什么？”其实是这样的，REORGANIZE只对索引的叶级起作用，对于非叶级不予理会。这意味着没有完全获得彻底的优化。但是，对于大部分的索引而言，那不是碎片真正开销的所在之处（尽管可能会发生，并且，各人遭遇的事情也可能不尽相同）。

由于其对用户的影响非常小，通常会想要把该工具作为常规维护计划的一部分来使用。当我们在后面讨论碎片时，将更深入地考查它。

3.3.5  DROP INDEX

该语句恢复了前面的DROP语句大部分的简洁。关于它唯一真正麻烦的是，由于索引不是一个孤立的对象（本质上，它包含在表的定义当中），删除索引时，必须不仅要指定索引，而且要指定索引所属的表。其语法如下所示：

DROP INDEX <表名>.<索引名>

正如你所料，这里其实没有太多可讲的内容。如果需要，可以使用四部分命名（如果包含索引的话，我想应该是五部分了）。