索引的优点和缺点 2

索引的类型
根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种类型。一种是数据表的物理顺序与索引顺序相同的聚簇索引，另一种是数据表的物理顺序与索引顺序不相同的非聚簇索引。

聚簇索引的体系结构
索 引的结构类似于树状结构，树的顶部称为叶级，树的其它部分称为非叶级，树的根部在非叶级中。同样，在聚簇索引中，聚簇索引的叶级和非叶级构成了一个树状 结构，索引的最低级是叶级。在聚簇索引中，表中的数据所在的数据页是叶级，在叶级之上的索引页是非叶级，索引数据所在的索引页是非叶级。在聚簇索引中，数 据值的顺序总是按照升序排列。

应该在表中经常搜索的列或者按照顺序访问的列上创建聚簇索引。当创建聚簇索引时，应该考虑这些因素：每 一个表只能有一个聚簇索引，因为表中数据的物理顺序 只能有一个；表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这是因为聚簇索引改变了表中行的物理顺序，数据行按 照一定的顺序排列，并且自动维护这个顺序；关键值的唯一性要么使用UNIQUE关键字明确维护，要么由一个内部的唯一标识符明确维护，这些唯一性标识符是 系统自己使用的，用户不能访问；聚簇索引的平均大小大约是数据表的百分之五，但是，实际的聚簇索引的大小常常根据索引列的大小变化而变化；在索引的创建过 程中，SQL Server临时使用当前数据库的磁盘空间，当创建聚簇索引时，需要1.2倍的表空间的大小，因此，一定要保证有足够的空间来创建聚簇索引。

当 系统访问表中的数据时，首先确定在相应的列上是否存在有索引和该索引是否对要检索的数据有意义。如果索引存在并且该索引非常有意义，那么系统使用该索引 访问表中的记录。系统从索引开始浏览到数据，索引浏览则从树状索引的根部开始。从根部开始，搜索值与每一个关键值相比较，确定搜索值是否大于或者等于关键 值。这一步重复进行，直到碰上一个比搜索值大的关键值，或者该搜索值大于或者等于索引页上所有的关键值为止。

非聚簇索引的体系结构
非聚簇索引的结构也是树状结构，与聚簇索引的结构非常类似，但是也有明显的不同。
在非聚簇索引中，叶级仅包含关键值，而没有包含数据行。非聚簇索引表示行的逻辑顺序。 非聚簇索引有两种体系结构：一种体系结构是在没有聚簇索引的表上创建非聚簇索引，另一种体系结构是在有聚簇索引的表上创建非聚簇索引。

如 果一个数据表中没有聚簇索引，那么这个数据表也称为数据堆。当非聚簇索引在数据堆的顶部创建时，系统使用索引页中的行标识符指向数据页中的记录。行标识 符存储了数据所在位置的信息。数据堆是通过使用索引分配图（IAM）页来维护的。IAM页包含了数据堆所在簇的存储信息。在系统表sysindexes 中，有一个指针指向了与数据堆相关的第一个IAM页。系统使用IAM页在数据堆中浏览和寻找可以插入新的记录行的空间。这些数据页和在这些数据页中的记录 没有任何的顺序并且也没有链接在一起。在这些数据页之间的唯一的连接是IAM中记录的顺序。当在数据堆上创建了非聚簇索引时，叶级中包含了指向数据页的行 标识符。行标识符指定记录行的逻辑顺序，由文件ID、页号和行ID组成。这些行的标识符维持唯一性。非聚簇索引的叶级页的顺序不同于表中数据的物理顺序。 这些关键值在叶级中以升序维持。

当非聚簇索引创建在有聚簇索引的表上的时候，系统使用索引页中的指向聚簇索引的聚簇键。聚簇键存储了 数据的位置信息。如果某一个表有聚簇索引，那么非聚簇 索引的叶级包含了映射到聚簇键的聚簇键值，而不是映射到物理的行标识符。当系统访问有非聚簇索引的表中数据时，并且这种非聚簇索引创建在聚簇索引上，那么 它首先从非聚簇索引来找到指向聚簇索引的指针，然后通过使用聚簇索引来找到数据。
当需要以多种方式检索数据时，非聚簇索引是非常有用的。当创建非聚簇索引时，要考虑这些情况：在缺省情况下，所创建的索引是非聚簇索引；在每一个表上面，可以创建不多于249个非聚簇索引，而聚簇索引最多只能有一个。
系统如何访问表中的数据
一 般地，系统访问数据库中的数据，可以使用两种方法：表扫描和索引查找。第一种方法是表扫描，就是指系统将指针放置在该表的表头数据所在的数据页上，然后 按照数据页的排列顺序，一页一页地从前向后扫描该表数据所占有的全部数据页，直至扫描完表中的全部记录。在扫描时，如果找到符合查询条件的记录，那么就将 这条记录挑选出来。最后，将全部挑选出来符合查询语句条件的记录显示出来。第二种方法是使用索引查找。索引是一种树状结构，其中存储了关键字和指向包含关 键字所在记录的数据页的指针。当使用索引查找时，系统沿着索引的树状结构，根据索引中关键字和指针，找到符合查询条件的的记录。最后，将全部查找到的符合 查询语句条件的记录显示出来。
在SQL Server中，当访问数据库中的数据时，由SQL Server确定该表中是否有索引存在。如果没有索引，那么SQL Server使用表扫描的方法访问数据库中的数据。查询处理器根据分布的统计信息生成该查询语句的优化执行规划，以提高访问数据的效率为目标，确定是使用 表扫描还是使用索引。
索引的选项
在创建索引时，可以指定一些选项，通过使用这些选项，可以优化索引的性能。这些选项包括FILLFACTOR选项、PAD_INDEX选项和SORTED_DATA_REORG选项。
使 用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选项。
索引的维护
为了维护系统性能，索引在创建之后，由于频繁地对数据进行增加、删除、修改等操作使得索引页发生碎块，因此，必须对索引进行维护。
使 用DBCC SHOWCONTIG语句，可以显示表的数据和索引的碎块信息。当执行DBCC SHOWCONTIG语句时，SQL Server浏览叶级上的整个索引页，来确定表或者指定的索引是否严重碎块。DBCC SHOWCONTIG语句还能确定数据页和索引页是否已经满了。当对表进行大量的修改或者增加大量的数据之后，或者表的查询非常慢时，应该在这些表上执行 DBCC SHOWCONTIG语句。当执行DBCC SHOWCONTIG语句时，应该考虑这些因素：当执行DBCC SHOWCONTIG语句时，SQL Server要求指定表的ID号或者索引的ID号，表的ID号或者索引的ID号可以从系统表sysindexes中得到；应该确定多长时间使用一次 DBCC SHOWCONTIG语句，这个时间长度要根据表的活动情况来定，每天、每周或者每月都可以。
使用DBCC DBREINDEX语句重建表的一个或者多个索引。当希望重建索引和当表上有主键约束或者唯一性键约束时，执行DBCC DBREINDEX语句。除此之外，执行DBCC DBREINDEX语句还可以重新组织叶级索引页的存储空间、删除碎块和重新计算索引统计。当使用执行DBCC DBREINDEX语句时，应该考虑这些因素：根据指定的填充度，系统重新填充每一个叶级页；使用DBCC DBREINDEX语句重建主键约束或者唯一性键约束的索引；使用SORTED_DATA_REORG选项可以更快地创建聚簇索引，如果没有排列关键值， 那么不能使用DBCC DBREINDEX语句；DBCC DBREINDEX语句不支持系统表。另外，还可以使用数据库维护规划向导自动地进行重建索引的进程。
统计信息是存储在SQL Server中的列数据的样本。这些数据一般地用于索引列，但是还可以为非索引列创建统计。SQL Server维护某一个索引关键值的分布统计信息，并且使用这些统计信息来确定在查询进程中哪一个索引是有用的。查询的优化依赖于这些统计信息的分布准确 度。查询优化器使用这些数据样本来决定是使用表扫描还是使用索引。当表中数据发生变化时，SQL Server周期性地自动修改统计信息。索引统计被自动地修改，索引中的关键值显著变化。统计信息修改的频率由索引中的数据量和数据改变量确定。例如，如 果表中有10000行数据，1000行数据修改了，那么统计信息可能需要修改。然而，如果只有50行记录修改了，那么仍然保持当前的统计信息。除了系统自 动修改之外，用户还可以通过执行UPDATE STATISTICS语句或者sp_updatestats系统存储过程来手工修改统计信息。使用UPDATE STATISTICS语句既可以修改表中的全部索引，也可以修改指定的索引。
使用SHOWPLAN和STATISTICS IO语句可以分析索引和查询性能。使用这些语句可以更好地调整查询和索引。SHOWPLAN语句显示在连接表中使用的查询优化器的每一步以及表明使用哪一 个索引访问数据。使用SHOWPLAN语句可以查看指定查询的查询规划。当使用SHOWPLAN语句时，应该考虑这些因素。SET SHOWPLAN_ALL语句返回的输出结果比SET SHOWPLAN_TEXT语句返回的输出结果详细。然而，应用程序必须能够处理SET SHOWPLAN_ALL语句返回的输出结果。SHOWPLAN语句生成的信息只能针对一个会话。如果重新连接SQL Server，那么必须重新执行SHOWPLAN语句。STATISTICS IO语句表明输入输出的数量，这些输入输出用来返回结果集和显示指定查询的逻辑的和物理的I/O的信息。可以使用这些信息来确定是否应该重写查询语句或者 重新设计索引。使用STATISTICS IO语句可以查看用来处理指定查询的I/O信息。
就象SHOWPLAN语句一样，优化器隐藏也用来 调整查询性能。优化器隐藏可以对查询性能提供较小的改进，并且如果索引策略发生了改变，那么这种优化器隐 藏就毫无用处了。因此，限制使用优化器隐藏，这是因为优化器隐藏更有效率和更有柔性。当使用优化器隐藏时，考虑这些规则：指定索引名称、当 index_id为0时为使用表扫描、当index_id为1时为使用聚簇索引；优化器隐藏覆盖查询优化器，如果数据或者环境发生了变化，那么必须修改优 化器隐藏。