第七章 索引

    创建索引有多种方法，这些方法包括直接创建索引的方法和间接创建索引的方法。

    *直接创建索引，例如使用CREATE INDEX语句或者使用创建索引向导。

    *间接创建索引，例如在表中定义主键约束或者唯一性键约束时，同时也创建了索引。

    虽然这两种方法都可以创建索引，但是它们创建索引的具体内容是有区别的。使用CREATE INDEX语句或者使用创建索引向导来创建索引，这是最基本的索引创建方式，并且这种方法最具有扩展性，可以定制创建出符合自己需要的索引。在使用这种方式创建索引时，可以使用许多选项，例如指定数据页的填充因子、进行排序、整理统计信息等，这样可以优化索引。使用这种方法，可以指定索引的类型、唯一性和复合性。也就是说，既可以创建聚集索引，也可以创建非聚集索引；既可以在一个列上创建索引，也可以在多个列上创建索引。

    通过定义主键约束或者唯一性键约束，也可以间接创建索引。主键约束是一种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。在创建主键约束时，系统自动创建一个唯一性的聚集索引。虽然在逻辑上，主键约束是一种重要的结构，但是在物理结构上，与主键约束相对应的结构是唯一性的聚集索引。换句话说，在物理实现上，不存在主键约束，而只存在唯一性的聚集索引。同样，在创建唯一性键约束时，也同时创建了索引，这种索引则是唯一性的非聚集索引。因此，当使用约束创建索引时，索引的类型和特征基本上都已经确定了，由用户定制的余地比较小。

    当在表上定义主键或者唯一性键约束时，如果表中已经有使用CREATE INDEX语句创建的标准索引时，那么主键约束或者唯一性键约束创建的索引覆盖以前创建的标准索引。也就是说，主键约束或者唯一性键约束创建的索引的优先级高于使用CREATE INDEX语句创建的索引。

    小天：我明白你的意思了，就是说，如果我们对索引没有什么要求的话，按照以前你教的创建表的方式直接创建就OK了，索引页都有了。如果还有更高要求的话，咱们就得自己使用Transact-SQL语句或者向导创建了，对吧？

    7.5.1  使用向导创建索引

    老田：可以这样理解，我们先说说如何用向导创建吧。打开“对象资源管理器”，找到“指定的SQL Server实例”，打开“数据库”目录下面指定的数据库，打开“表”，再打开“要创建索引的表”，在“索引”目录上单击鼠标右键，选择“新建索引”命令，如图7-6所示。

图7-6  使用向导创建索引

    小天：咦，我看到一个标志为聚集索引的，这个应该就是对这个表创建主键的时候间接创建的那个吧。

    老田：不错，选择“新建索引”命令后打开向导，如图7-7所示。

图7-7  新建索引向导

    在图7-7中，之所以弹出提示框，是因为我故意在“索引类型”下拉列表框中选择了“聚集”选项。这个自己看提示就明白。接下来分别解释其中的选项都分别是什么意思。

    表名：显示创建索引的表或视图的名称。此字段是只读的。若要选择不同的表，请关闭“索引属性”页，选择适当的表，然后再次打开“索引属性”页。不能对索引视图指定空间索引，仅可为具有主键的表定义空间索引。表中最大主键列数为15。复合主键列的每行大小限制最多895个字节。

    索引名称：显示索引的名称。对于现有索引，此字段是只读的。在创建新的索引时，需键入索引的名称。

    索引类型：从该下拉列表框中选择索引类型，有“聚集”、“非聚集”、“主XML”或“空间”四种选项。因为每个表只允许创建一个聚集索引。所以如果聚集索引已经存在，则会显示一条提示消息，询问您是否删除现有的聚集索引并创建新的聚集索引。

    索引类型之间是可以转换的，如下。

    *聚集到聚集。

    *非聚集到非聚集。

    *非聚集到聚集。

    不允许以下转换。

    *聚集到非聚集。

    *非 XML索引到XML索引，反之亦然。

    *非空间索引到空间索引，反之亦然。

    唯一：选中此复选框可使该索引成为唯一索引。不允许两行具有相同的索引值。默认情况下，此复选框处于未选中状态。如果两行具有相同的值，在修改现有索引时，索引创建将会失败。对于允许NULL的列，唯一索引允许为NULL值。如果在“索引类型”下拉列表框中选择“空间”选项，则“唯一”复选框呈灰色。

    索引键列：向“索引键列”列表中添加所需的列。如果添加多列，则必须以所需的顺序列出这些列。索引中的列顺序对索引的性能具有很大影响。单个组合索引不能超过16列。只能对包含空间数据类型（空间列）的单个列定义空间索引。

    *名称：显示组成索引键的列的名称。

    *排列顺序：指定所选索引列的排序方向，“升序”或“降序”。 如果“索引类型”下拉列表框中选择“主 XML”或“空间”，则表中将不显示此列。

    *数据类型：显示数据类型信息。如果表列为计算列，则“数据类型”显示“计算列”。

    *大小：显示存储列数据类型所需的最大字节数。对于空间列或XML列，显示零（0）。

    *标识：显示组成索引键的列是否为标识列。

    *允许NULL：显示组成索引键的列是否允许在表或视图列中存储NULL值。

    *添加：向索引键添加列。从单击“添加”按钮时出现的“从<表名>中选择列”对话框中选择表列。对于空间索引，在选择一列后，该按钮将呈灰色。

    *删除：从组成索引键的列中删除所选列。

    *上移：在索引键列表中向上移动所选列。有关索引中列顺序的详细信息，请参阅常规索引设计指南。

    *下移：在索引键列表中向下移动所选列。

    小天：我针对前一章学习用的那个学生管理系统中的学生成绩表创建了两个索引，先创建一个聚集索引，接着又创建了一个不唯一、非聚集的索引，感觉挺好玩的。而且我还利用向导生成了相应的SQL语句，不过我不明白意思，还是你继续讲吧。

    7.5.2  使用CREATE INDEX语句创建索引

    其实也没有什么，我们先看下语法，如下：

    咱们将上面语法中的关键字分别作个解释。

    UNIQUE：为表或视图创建唯一索引。唯一索引不允许两行具有相同的索引键值。视图的聚集索引必须唯一。无论IGNORE_DUP_KEY是否设置为ON，数据库引擎都不允许为已包含重复值的列创建唯一索引。否则，数据库引擎会显示错误消息。必须先删除重复值，然后才能为一列或多列创建唯一索引。唯一索引中使用的列应设置为NOT NULL，因为在创建唯一索引时，会将多个NULL值视为重复值。

    CLUSTERED：创建索引时，键值的逻辑顺序决定表中对应行的物理顺序。聚集索引的底层（或称叶级别）包含该表的实际数据行。一个表或视图只允许同时拥有一个聚集索引。在创建任何非聚集索引之前创建聚集索引，创建聚集索引时会重新生成表中现有的非聚集索引。如果没有指定CLUSTERED，则创建非聚集索引。

    NONCLUSTERED ：创建一个指定表的逻辑排序的索引。对于非聚集索引，数据行的物理排序独立于索引排序。无论是使用PRIMARY KEY和UNIQUE约束隐式创建索引，还是使用CREATE INDEX显式创建索引。每个表都最多可包含999个非聚集索引。对于索引视图，只能为已定义唯一聚集索引的视图创建非聚集索引，默认值为NONCLUSTERED。

    索引名：索引的名称。索引的名称在表或视图中必须唯一，但在数据库中不必唯一。

    列：索引所基于的一列或多列。指定两个或多个列名，可为指定列的组合值创建组合索引。在table_or_view_name后的括号中，按排序优先级列出组合索引中所包括的列。一个组合索引键中最多可组合16列。组合索引键中的所有列必须在同一个表或视图中。组合索引值允许最大为900字节。不能将大型对象（LOB）数据类型ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、xml或image的列指定为索引的键列。另外，即使CREATE INDEX语句中并未引用ntext、text 或image列，视图定义中也不能包含这些列。

    [ ASC | DESC ]：确定特定索引列的升序或降序排序方向，默认值为ASC。

    说一百不如做一次，下例对前一章（高级检索）使用过的Stu_test 数据库中的Zone表创建一个索引，SQL代码如下：

    可以使用CREATE TABLE或ALTER TABLE创建或修改表时创建索引。另外创建索引的时候记得去参考下前面章节中的注意事项。

    小天：我按照你的做法一模一样地写，执行就出错了，如图7-8所示。你是不是知道本来要出错，所以你都没有执行，就忽悠我哦。

图7-8  创建索引出错

    老田：不想说你了，注意看你的代码真的和我写的一模一样？我们上面过说，默认是创建非聚集索引，但是你增加了关键字CLUSTERED，这就指明是创建聚集索引了。创建聚集索引也没有关系，但是你不应该在ZONE的ID列上创建，因为这个列本来是主键，所以上面本来就有一个聚集索引。何况一张表只能创建一个聚集索引呢？看书不认真，前面这些注意事项都说过的。

    7.5.3  索引的选项

    在创建索引时，可以指定一些选项，通过使用这些选项，可以优化索引的性能。这些选项包括FILLFACTOR选项、PAD_INDEX选项和SORTED_DATA_REORG选项。

    小提示：这些选项我只是解释下，具体如何写建议你使用向导创建的同时参考下面的解释，接着自动生成相应的SQL语句，最后对所生成的SQL语句进行注释，这是学习MSSQL最快捷的一条路，我以前可是屡试不爽。大家都是自学，我相信你一样能够做得更好。

    使用FILLFACTOR选项可以优化插入语句和修改语句的性能。当某个索引页变满时，SQL Server必须花费时间分解该页，以便为新的记录行腾出空间。使用FILLFACTOR选项，就是在叶级索引页上分配一定百分比的自由空间，以便减少页的分解时间。当在有数据的表中创建索引时，可以使用FILLFACTOR选项指定每一个叶级索引节点填充的百分比。默认值是0，该数值等价于100。在创建索引的时候，内部索引节点总是留有一定的空间，这个空间足够容纳一个或者两个表中的记录。在没有数据的表中，当创建索引的时候，不要使用该选项，因为这时该选项是没有实际意义的。另外，该选项的数值在创建时指定以后，不能动态地得到维护，因此，只在有数据的表中创建索引时才使用。

    PAD_INDEX选项将FILLFACTOR选项的数值同样用于内部的索引节点，使内部的索引节点的填充度与叶级索引节点中的填充度相同。如果没有指定FILLFACTOR选项，那么单独指定PAD_INDEX选项是没有实际意义的，这是因为PAD_INDEX选项的取值是由FILLFACTOR选项的取值确定的。

    当创建聚集索引时，SORTED_DATA_REORG选项清除排序，因此可以减少建立聚集索引所需要的时间。当在一个已经变成碎块的表上创建或者重建聚集索引时，使用SORTED_DATA_REORG选项可以压缩数据页。当重新需要在索引上应用填充度时，也可以使用该选项。当使用SORTED_DATA_REORG选项时，应该考虑以下一些因素。

    *SQL Server确认每一个关键值是否比前一个关键值高，如果都不高，那么不能创建索引。

    *SQL Server要求1.2倍的表空间来物理地重新组织数据。

    *使用SORTED_DATA_REORG选项，通过清除排序进程而加快索引创建进程。

    *从表中物理地拷贝数据，当某一个行被删除时，其所占的空间可以重新利用。

    *创建全部非聚集索引。

    *如果希望把叶级页填充到一定的百分比，可以同时使用FILLFACTOR选项和SORTED_DATA_REORG选项。