第七章 索引

    7.6.4  查看索引

    在SQL Server Management Studio中直接查看的方法我就不说了，直接看下如何使用系统内置存储过程查看吧。比如我们查看stu_test数据库中的ZONE表下面的索引，如图7-14所示。
   
    从图7-14中可以看出什么？三列分别是：索引名字、索引描述和索引基于的列。

    还有种看得更加详细的办法，那就是使用SELECT从系统信息中查询，如图7-15所示。

图7-14  查看表中的索引信息

图7-15  使用SELECT查看索引信息

    7.6.5  修改索引

    接着说修改。其实可修改的并不多，通过禁用、重新生成或重新组织索引，或通过设置索引的相关选项修改现有的表索引或视图索引。

    重新生成索引：重新生成索引将会删除并重新创建索引。这将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间，然后对连续页中的索引行重新排序。如果指定ALL，将删除表中的所有索引，然后在单个事务中重新生成。FOREIGN KEY约束不必预先删除。重新生成具有128个区或更多区的索引时，数据库引擎延迟实际的页释放与其关联的锁，直到事务提交。

    例如重新生成前面创建索引章节的实例在Stu_test数据库中的Zone表创建的那个索引USER_ID_INDEX，代码如下：

    小天：我要重新设置索引的选项，也都要重新生成吗？我刚才把REBUILD删除，想直接设置选项都不行。

    老田：不用，看下面这个实例为索引USER_ID_INDEX设置了几个选项：

    小天：快看我这里，如图7-16所示。

    老田：这是因为禁用了基于主键创建的索引，所以它会同时禁用索引和上面的所有约束。详细解释请继续看下面的“删除索引”小节。

    最后还有一个修改索引名字，代码如下：

    执行后会出现警告，如图7-17所示。

    想想为什么会出现这个提示？

图7-16  禁用了基于主键的索引

图7-17  修改索引名字

    7.6.6  删除索引

    删除索引直接使用DROP INDEX即可。

    小天：我有个问题，从前就想问，我们总删除东西，但是却从来没有考虑过万一要删除的对象不存在咋办？

    老田：还从前，晕倒。这个问题确实很严重，其实很简单，就是一个EXISTS关键字就可以判断了。例如要删除前面章节中创建的USER_ID_INDEX这个索引，代码如下。

    使用的时候可以带的选项包括MAXDOP、ONLINE和MOVE TO三个，下面分别进行解释。

    MAXDOP = max_degree_of_parallelism：在索引操作期间覆盖“最大并行度”配置选项。空间索引或XML索引不允许使用MAXDOP。max_degree_of_parallelism可以是：

    1：取消生成并行计划。

    >1：将并行索引操作中使用的最大处理器数量限制为指定数量。

    0（默认值）：根据当前系统工作负荷使用实际的处理器数量或更少数量的处理器。

    ONLINE = ON | OFF：指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。默认值为OFF。只能在删除聚集索引时指定ONLINE选项。

    ON：不保留长期表锁。这样便允许继续对基础表进行查询或更新。

    OFF：应用表锁，该表在索引操作期间不可用。

    MOVE TO { partition_scheme_name ( column_name ) | filegroup_name | "default" }：指定一个位置，以移动当前处于聚集索引叶级别的数据行。数据将以堆的形式移动到这一新位置。可以将分区方案或文件组指定为新位置，但该分区方案或文件组必须已存在。MOVE TO对索引视图或非聚集索引无效。如果未指定分区方案或文件组，则生成的表将位于为聚集索引定义的同一分区方案或文件组中。如果使用MOVE TO删除了聚集索引，则将重新生成所有对基表的非聚集索引，但这些索引会保留在其原始文件组或分区方案中。如果基表移动到其他文件组或分区方案中，这些非聚集索引不会通过移动来与基表（堆）的新位置一致。因此，即使非聚集索引以前与聚集索引对齐，它们也可能不再与堆对齐。

    删除索引需要注意以下事项。

    *删除非聚集索引时，将从元数据中删除索引定义，并从数据库文件中删除索引数据页（B树）。删除聚集索引时，将从元数据中删除索引定义，并且存储于聚集索引叶级别的数据行将存储到生成的未排序表（堆）中。将重新获得以前由索引占有的所有空间。此后可将该空间用于任何数据库对象。

    *如果索引所在的文件组脱机或设置为只读，则不能删除该索引。

    *删除索引视图的聚集索引时，将自动删除同一视图的所有非聚集索引和自动创建的统计信息。手动创建的统计信息不会被删除。

    *保留了语法table_or_view_name.index_name，以便向后兼容。XML索引或空间索引无法使用向后兼容的语法删除。

    *删除带有128个或更多区数的索引时，数据库引擎将延迟实际页释放及其关联的锁，直到提交事务为止。

    *有时，删除并重新创建索引以重新组织或重新生成索引，例如在大容量加载之后应用新的填充因子值或重新组织数据。若要执行该操作，使用ALTER INDEX更为有效，尤其是对于聚集索引而言。ALTER INDEX REBUILD具有优化功能，可避免重新生成非聚集索引所造成的开销。

    小天：我这里错了，删除不了，效果如图7-18所示。

图7-18  删除主键间接生成的索引

    老田：下面不是都说清楚了嘛，因为此索引正用于PRIMARY KEY强制执行。如果你一定要删除，就得先删除主键约束。不过删除主键约束的时候可能又会遇到另外一个问题，就是这个主键还有外键约束，那么就得先删除外键约束，例如看到如下错误提示。关于删除约束请参考本书第5章“约束”部分。

    小天：明白了，我刚才删除主键的时候就遇到了。另外还有一个问题，如果一个数据库中有很多索引，事实上这也确实可能，有办法批量删除吗？

    老田：很简单，语法如下：

    小天：哎，这个删除索引页太不好了，如果我要删除索引就得删除约束，假设某表的主键一共有100个外键约束，我就得删除100次，删除还好说，问题是删除完了还得重新建，这太麻烦了。

    老田：其实也不用都删除啊，可以禁用。如下示例通过禁用PRIMARY KEY索引来禁用PRIMARY KEY约束。对基础表的FOREIGN KEY约束自动被禁用，并显示警告消息，如图7-19所示。

    小天：不错，不错，禁用一个则相关的都一起禁用了。但是，如何启用呢？

    老田：下面的例子就是，如图7-20所示。

图7-19  禁用约束

图7-20  启用约束

    本章小结

    本章对数据的存储结构，索引的结果做了比较深入的解释。但因为本章内容太过抽象，所以可能会让初学者感觉很头疼、很晕。事实上大可不必如此，如果本章你实在无法一下都理解了，那么请尽量熟悉我们提到的那些优缺点和注意事项，然后熟悉创建、维护等的语法，在以后的每次建表练习中都尽量将此知识点加入进去，多练习，很多知识点自然就清晰明白了。下面对本章的内容再做一次提炼。

    数据库中的索引与书籍中的索引类似。在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是一个表中所包含的值的列表，其中注明了表中包含各个值的行所在的存储位置。可以为表中的单个列建立索引，也可以为一组列建立索引；索引采用B树结构。索引包含一个条目，该条目有来自表中每一行的一个或多个列（搜索关键字）。B 树按搜索关键字排序，可以在搜索关键字的任何子词条集合上进行高效搜索。例如，对于一个A、B、C列上的索引，可以在A以及A、B和A、B、C上对其进行高效搜索。

    SQL Server中管理的最小单位是页，占用8KB的空间。数据的存放是没有逻辑顺序的，所以，当数据堆满一些，数据库就将其存放到另外一页。

    索引可以提高系统的性能它有以下优点。

    （1）唯一索引可以保证行的唯一。

    （2）加快检索速度。

    （3）加速表之间的连接，实现数据的参照完整性。

    （4）使用ORDER BY/GROUP BY可以加快分组和排序的速度。

    （5）使用索引进行查询的过程中可以使用优化隐藏器以提高系统性能。

    但是索引的创建和维护耗费时间；索引需要占用物理空间；当表进行INSERT、UPDATE、DELETE操作时；需要对INDEX进行维护，降低了数据库的维护速度。

    创建索引的原则如下。

    （1）在经常进行搜索的列上创建索引。

    （2）在作为主键的列上创建索引。

    （3）在一些外键上创建索引。

    （4）在经常需要排序的列上创建索引。

    （5）在需要根据范围进行搜索的列上创建索引。

    （6）在用于WHERE子句的列上创建索引。

    不需要创建索引的情况如下。

    （1）在查询中很少用到或参考的列。

    （2）值很少的列。

    （3）定义为text、image、bit的列。

    （4）需要UPDATE的性能要求大于对SELECT的性能要求。

    索引类型如下。

    （1）聚集索引：数据库表的物理顺序和索引的顺序相同。

    ① 树状结构，叶子节点为数据页。

    ② 每个表只有一个。

    ③ 表的物理顺序和索引行的物理顺序相同。

    ④ 使用UNIQUE。

    ⑤ 平均大小为表的5%。

    ⑥ 创建索引时临时使用当前数据库的磁盘空间，聚簇所需的临时空间是表的1.2倍。

    （2）非聚集索引：与聚集索引的结构类似，但也有不同。

    ① 默认情况下为非聚集索引。

    ② 一个表最多为249个非聚集索引。

    ③ 索引页只包含索引的关键字，不包含实际数据。