索引的作用及其使用

1、摘要：在设计我们系统的数据库时，通常需要考虑数据库的索引形式。本文作者详细介绍了 数据库索引的作用和它的使用。1 索引的作用及其使用2 索引的作用及其使用为什么要创建索引呢？这是因为，创建索引可以人人提高系统的性能。第一， 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。第三，可以加速表和表z间的连接，特别是在实现数据的参考完整性方而特别有意义。笫四，在使川分组和排序子句进行数据检索时，同样町以显著减少查询屮分组和排序 的时间。第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，捉高系统的性能。也许会有人要问：增加索

2、引有如此多的优点，为什么不对表中的每一个列创建一个索 引呢？这种想法固然有英合理性，然而也有其片面性。虽然，索引有许多优点，但是，为表 中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方血。第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。笫二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的 物理空间，如果要建立聚簇索引，那么需要的空间就会更大。第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就 降低了数据的维护速度。索引是建立在数据库表中的某些列的上血。因此，在创建索引的时候，应该仔细考虑在 哪些列上

3、可以创建索引，在哪些列上不能创建索引。一般來说，应该在这些列上创建索引，例如：在经常需要搜索的列上，可以加快搜索的 速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接 的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列 上创建索引，因为索引己经排序，其指定的范围是连续的;在经常需要排序的列上创建索引， 因为索引己经排序，这样杳询可以利用索引的排序，加快排序杳询时间；在经常使用在 where子句小的列上面创建索引，加快条件的判断速度。同样，对于冇些列不应该创建索引。一般來说，不应该创建索引的的这些列具冇下列特第一，对于那些在杳询屮很少使

4、用或者参考的列不应该创建索引。这是因为，既然这 些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引， 反ifu降低了系统的维护速度和增大了空间需求。笫二， 对丁那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的 取值很少，例如人事表的性別列，在查询的结果中，结果集的数据行占了表中数据行的很大 比例，即需耍在农中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。第三， 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因 为，这些列的数据量要么相当大，要么取值很少。第四，当修改性能远远大于检索性能吋， 不应该创建索引。这是

5、因为，修改性能和检索性能是互相才盾的。当增加索引时，会捉髙检 索性能，但是会降低修改性能。肖减少索引时，会提高修改性能，降低检索性能。因此，当 修改性能远远人于检索性能时，不应该创建索引。创建索引的方法创建索引有多种方法，这些方法包括肓接创建索引的方法和间接创建索引的方法。直接创建索引，例如使用create index语句或者使用创建索引向导，间接创建索 引，例如在表中定义主键约束或者唯一性键约束时，同时也创建了索弓i。虽然，这两种方法 都可以创建索引，但是，它们创建索引的具体内容是冇区别的。使用create index语句或者使用创建索引向导来创建索引，这是最基本的索引创 建方式，并j1这种

6、方法最具有柔性，可以定制创建出符合口己需要的索引。在使用这种方式 创建索引时，可以使用许多选项，例如指定数据页的充满度、进行排序、整理统计信息等， 这样可以优化索引。使用这种方法，可以指定索引的类型、唯一性和复合性，也就是说，既 可以创建聚簇索引，也可以创建非聚簇索引，既可以在一个列上创建索引，也可以在两个或 者两个以上的列上创建索引。通过定义主键约束或者唯性键约束，也可以间接创建索引。主键约束是一种保持数据 完整性的逻辑，它限制农屮的记录有相同的主键记录。在创建主键约束时，系统口动创建了 一个唯一性的聚簇索引。虽然，在逻辑上，主键约束是一种重要的结构，但是，在物理结构 上，与主键约束相对应的

7、结构是唯-性的聚簇索引。换句话说，在物理实现上，不存在主键 约束，而只存在唯一性的聚簇索引。同样，在创建唯一性键约束吋，也同吋创建了索引，这 种索引则是唯一性的非聚簇索引。因此，当便用约束创建索引吋，索引的类型和特征基本上 都己经确定了，由用户定制的余地比较小。当在表上定义主键或者唯-性键约束时，如來表中已经有了使用create index语 句创建的标准索引时，那么主键约束或者唯性键约束创建的索引覆盖以询创建的标准索引。 也就是说，主键约束或者唯一性键约束创建的索引的优先级高于使用create index语 句创建的索引。索引有两个特征，即唯一性索引和复合索引。唯一性索引保证在索引列中的全部

8、数据是唯一的，不会包含冗余数据。如果表中已经 有 个主键约束或者唯性键约束，那么当创建农或者修改表吋，sqlserver门动创建 个唯一性索引。然而，如果必须保证唯一性，那么应该创建主键约束或者唯一性键约束，而 不是创建一个唯一性索引。当创建唯一性索引时，应该认真考虑这些规则：当在表小创建主 键约束或者唯一性键约束时，sql server 口动创建一个唯一性索引；如果表中已经包含有 数据，那么当创建索引吋，sqlserver检查表中已有数据的冗余性；每当使用插入语句插 入数据或者使用修改语句修改数据吋，sqlserver检查数据的冗余性：如果有冗余值，那 么sql server取消该语句的执行

9、，并且返回一个错谋消息；确保表屮的每一行数据都有 一个唯一值，这样可以确保每一个实体都可以唯一确认；只能在可以保证实体完整性的列上 创建唯一性索引，例如，不能在人事表中的姓名列上创建唯一性索引，因为人们可以有相同 的姓名。复合索引就是一个索引创建在两个列或者多个列上。在搜索时，当两个或者多个列作为 个关键值时，最好在这些列上创建复合索引。当创建复合索引吋，应该考虑这些规则：最 多可以把16个列介并成一个单独的复合索引，构成复合索引的列的总长度不能超过900 字节，也就是说复合列的长度不能太长；在复合索引屮，所有的列必须来自同一个表屮，不 能跨农建立复合列；在复合索引中，列的排列顺序是非常重要的

10、，因此要认真排列列的顺序， 原则上，应该首先定义最唯一的列，例如在(col1 , col2)上的索引与在(col2, col1 ) 上的索引是不相同的，因为两个索引的列的顺序不同；为了使杏询优化器使用复合索引，杏 询语句屮的where 了句必须参考复合索引屮第一个列；当表屮有多个关键列时，复合索 引是非常有用的；使用复合索引可以捉高查询性能，减少在一个表中所创建的索引数量。索引的类型根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种类型。一种是数 据表的物理顺序与索引顺序相同的聚簇索引，另一种是数据表的物理顺序与索引顺序不相同 的非聚簇索引。聚簇索引的体系结构：索引的结构类似于树状结

11、构，树的顶部称为叶级，树的其它部分 称为非叶级，树的根部在非叶级中。同样，在聚簇索引屮，聚簇索引的叶级和非叶级构成了 一个树状结构，索引的最低级是叶级。在聚簇索引屮，表中的数据所在的数据页是叶级，在 叶级之上的索引页是非叶级，索引数据所在的索引页是非叶级。在聚簇索引屮，数据值的顺 序总是按照升序排列。应该在表屮经常搜索的列或者按照顺序访问的列上创建聚簇索引。当创建聚簇索引时, 应该考虑这些因素：每一个表只能有一个聚簇索引，因为表中数据的物理顺序只能有一个; 表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引zmj创建聚簇索 引，这是因为聚簇索引改变了表屮行的物理顺序，数据行按照

12、一定的顺序排列，并且自动维 护这个顺序；关键值的唯一性要么使用unique关键字明确维护，要么由一个内部的唯一 标识符明确维护，这些唯一性标识符是系统口己使用的，用户不能访问；聚簇索引的平均大 小大约是数据表的百分z五，但是，实际的聚簇索引的大小常常根据索引列的大小变化而变 化；在索引的创建过程屮，sqlserver临时使用当前数据库的磁盘空间，当创建聚簇索引 时，需要1.2倍的表空间的大小，因此，一定耍保证有足够的空间来创建聚簇索引。当系统访问农中的数据吋，首先确定在相应的列上是否存在有索引和该索引是否对耍检 索的数据有意义。如果索引存在并且该索引非常有意义，那么系统使用该索引访问农小的记

13、录。系统从索引开始浏览到数据，索引浏览则从树状索引的根部开始。从根部开始，搜索值 与每一个关键值相比较，确定搜索值是否大于或者等于关键值。这一步重复进行，直到碰上 一个比搜索值大的关键值，或者该搜索值大于或者等于索引页上所有的关键值为止。非聚簇索引的体系结构:非聚簇索引的结构也是树状结构，与聚簇索引的结构非常 类似，但是也有明显的不同。在非聚簇索引中，叶级仅包含关键值，而没有包含数据行。 非聚簇索引表示行的逻辑顺序。 非聚簇索引有两种体系结构：一种体系结构是在没有聚 簇索引的表上创建非聚簇索引，另一种体系结构是在有聚簇索引的表上创建非聚簇索引。如果一个数据表屮没有聚簇索引，那么这个数据表也称为

14、数据堆。当非聚簇索引在数据 堆的顶部创建时，系统使用索引页屮的行标识符指向数据页屮的记录。行标识符存储了数据 所在位置的信息。数据堆是通过使用索引分配图(iam)页来维护的。iam页包含了数据 堆所在簇的存储信息。在系统表sysindexes屮，有一个指针指向了与数据堆相关的第一个 iam页。系统使用i am页在数据堆中浏览和寻找可以插入新的记录行的空间。这些数据页 和在这些数据页中的记录没有任何的顺序并且也没有链接在一起。在这些数据页之间的唯一 的连接是iam中记录的顺序。当在数据堆上创建了非聚簇索引时，叶级中包含了指向数据页的行标识符。行标识符指 定记录行的逻辑顺序，由文件id、页号和行i

15、d组成。这些行的标识符维持唯一性。非聚 簇索引的叶级页的顺序不同于表中数据的物理顺序。这些关键值在叶级中以升序维持。当 非聚簇索引创建在有聚簇索引的表上的时候，系统使用索引贝中的指向聚簇索引的聚簇键。 聚簇键存储了数据的位置信息。如果某一个表有聚簇索引，那么非聚簇索引的叶级包含了映 射到聚簇键的聚簇键值，而不是映射到物理的行标识符。为系统访问有非聚簇索引的表中数 据时，并且这种非聚簇索引创建在聚簇索引上，那么它首先从罪聚簇索引来找到指向聚簇索 引的指针，然后通过使用聚簇索引来找到数据。当需要以多种方式检索数据吋，非聚簇索引是非常有用的。当创建非聚簇索引吋，要考 虑这些情况：在缺省情况下，所创建

16、的索引是非聚簇索引；在每一个表上面，可以创建不多 于249个非聚簇索引，而聚簇索引最多只能有一个。系统如何访问表小的数据-般地，系统访问数据库中的数据，可以使用两种方法：表扫描和索引査找。第-种 方法是表扫描，就是指系统将指针放置在该表的表头数据所在的数据页上，然示按照数据页 的排列顺序，-页一页地从前向后扫描该表数据所占有的全部数据页，直至扫描完表中的全 部记录。在扫描吋，如果找到符合査询条件的记录，那么就将这条记录挑选出来。最后，将 全部挑选出来符合杏询语句条件的记录显示岀来。第二种方法是使用索引杏找。索引是一种 树状结构，其中存储了关键字和指向包含关键字所在记录的数据页的指针。当使用索引

17、查找 吋，系统沿着索引的树状结构，根据索引中关键字和指针，找到符合査询条件的的记录。最 后，将全部查找到的符合查询语句条件的记录显示出来。在sql server中,当访问数据库中的数据时,由sql server确定该表中是否有索引 存在。如果没有索引，那么sql server使用表扫描的方法访问数据库中的数据。查询处 理器根据分布的统计信息生成该查询语句的优化执行规划，以提高访问数据的效率为id标, 确定是使用表扫描还是使用索引。索引的选项在创建索引时，可以指定一些选项，通过使用这些选项，可以优化索引的性能。这些选 项包括 fl llfactor 选项、pad_i ndex 选项和 sorte

18、d_data_reorg 选项。使用flllfactor选项，可以优化插入语句和修改语句的性能。当某个索引页变满吋， sql server必须花费时间分解该页，以便为新的记录行腾出空间。使用fl llfactor选 项，就是在叶级索引页上分配一定百分比的自山空间，以便减少页的分解时间。当在有数据 的表中创建索引吋，可以使用flllfactor选项指定侮一个叶级索引节点的填充的百分比。 缺省值是0,该数值等价于1 00。在创建索引的时候，内部索引节点总是留有了一定的空间， 这个空间足够容纳一个或者两个表中的记录。在没有数据的表屮，当创建索引的时候，不要 使用该选项，因为这吋该选项是没有实际意义的

19、。另外，该选项的数值在创建吋指定以后, 不能动态地得到维护，因此，只应该在有数据的表中创建索引时才使用。pad_index选项将fl llfactor选项的数值同样也用于内部的索引节点，使内部的 索引节点的填充度与叶级索引的节点中的填充度相同。如果没有指定fl llfactor选项, 那么单独指定pad_index选项是没有实际意义的，这是因为pad_index选项的取值是 由fillfactor选项的取值确定的。当创建聚簇索引吋，sorted_data_reorg选项清除排序，因此町以减少建立聚簇 索引所需耍的吋间。当在一个已经变成碎块的表上创建或者重建聚簇索引吋，使用 sorted_dat

20、a_reorg选项可以压缩数据页。当重新需耍在索引上应用填充度时，也使 用该选项。当使用sorted_data_reorg选项吋，应该考虑这些因素：sql server 确认每一个关键值是否比前一个关键值高，如果都不高，那么不能创建索引；sqlserver 要求1.2倍的农空间来物理地重新组织数据；便用sorted_data_reorg选项，通过 清除排序进程而加快索引创建进程；从表中物理地拷贝数据；当某一个行被删除吋，其所占 的空间可以重新利用；创建全部非聚簇索引；如果希望把叶级页填充到一定的百分比，可以 同时使用 fl llfactor 选项和 sorted_data_reorg 选项。索

21、引的维护为了维护系统性能，索引在创建之后，由于频繁地对数据进行增加、删除、修改等操作 使得索引页发生碎块，因此，必须对索引进行维护。使用dbcc showcontig语句，可以显示表的数据和索弓i的碎块信息。当执行 dbcc showcontig语句时，sql server浏览叶级上的整个索引页，来确定表或者指 定的索引是否严重碎块。dbcc showcontig语句还能确定数据页和索引页是否已经满 to当对表进行大量的修改或者增加大量的数据z后，或者表的查询非常慢时，应该在这些 表上执行dbcc showcontig语句。当执行dbcc showcontig语旬时，丿遏该考虑 这些因素：当执

22、行dbcc showcontig语句时，sql server要求指定表的id号或者 索引的id号，表的id号或者索引的id号可以从系统表sysindexes中得到；应该确定多 长时间使用一次dbcc showcontig语句，这个时间长度要根据表的活动悄况来定，每 天、每周或者每月都可以。使用dbccdbreindex语句重建表的一个或者多个索引。当希望重建索引和当表上 有主键约束或者唯一性键约束吋，执行dbccdbreindex语句。除此z外，执行dbcc dbreindex语句还可以重新组织叶级索引页的存储空间、删除碎块和重新计算索引统计。 当使用执行dbcc dbreindex语句吋，应

23、该考虑这些因素：根据指定的填充度，系统重 新填充每一个叶级贝；使用dbccdbreindex语句重建主键约束或者唯一性键约束的索 引；使用sorted_data_reorg选项可以更快地创建聚簇索引，如果没有排列关键值， 那么不能使用dbcc dbreindex语句；dbcc dbreindex语句不支持系统表。另外， 还可以使用数据库维护规划向导口动地进行重建索引的进程。统计信息是存储在sql server +的列数据的样木。这些数据一般地用于索引列，但 是述町以为非索引列创建统计。sqlserver维护某一个索引关键值的分布统计信息，并且 使用这些统计信息来确定在查询进程中哪一个索引是有用

24、的。查询的优化依赖于这些统计信 息的分布准确度。杳询优化器使用这些数据样木来决定是使用表扫描还是使用索引。当表屮 数据发生变化时，sql server周期性地自动修改统计信息。索引统计被白动地修改，索引 中的关键值显著变化。统计信息修改的频率由索引中的数据量和数据改变量确定。例如，如 果表中有10000行数据,1000行数据修改了，那么统计信息可能需要修改。然而,如果 只有50行记录修改了，那么仍然保持当前的统计信息。除了系统自动修改之外，用户还可 以通过执行update stati sti cs语句或者sp_updatestats系统存储过程来手工修改 统计信息。使用update stati

25、stics语句既对以修改表中的全部索弓i，也町以修改指定 的索引。使用showplan和stati sti cs io语句可以分析索引利查询性能。使用这些语句 可以更好地调整查询和索引oshowplan语句显示在连接表屮便用的查询优化器的每一步 以及表明使用哪一个索引访问数据。使用showplan语句可以查看指定查询的查询规划。 当使用showplan语句吋，应该考虑这些因素。set showplan_all语句返回的输 出结果比setshowplan_text语句返回的输出结果详细。然而，应用程序必须能够处 理set showplan_all语句返回的输出结果。showplan语句生成的信息

26、只能针对 一个会话。如果重新连接sql server,那么必须重新执行showplan语句ostati stics io语句农明输入输出的数屋，这些输入输出用来返回结果集和显示指定查询的逻辑的和物 理的i/o的信息。可以使用这些信息来确定是否应该重写查询语句或者重新设计索引。便 用statistics io语句可以查看用来处理指定查询的i/o信息。就象showplan语句一样，优化器隐藏也用来调整杏询性能。优化器隐藏可以对杏 询性能捉供较小的改进，并且如來索引策略发牛了改变，那么这种优化器隐藏就毫无用处了。 因此，限制使用优化器隐藏，这是因为优化器隐藏更有效率和更有柔性。当使用优化器隐藏 时，

27、考虑这些规则：指定索引名称、当indexd为0时为使用表扫描、当indexd为1 吋为使用聚簇索引；优化器隐藏覆盖查询优化器，如果数据或者环境发*了变化，那么必须 修改优化器隐藏。索引调整向导索引调整向导是一种工具，可以分析一系列数据库的杳询语旬，提供使用一系列数据库 索引的建议，优化整个杳询语句的性能。对于查询语句，需要指定下列内容：查询语句，这是将要优化的工作最包含了这些表的数据库，在这些表屮，可以创建索引，提高杏询性能在分析屮使用的表在分析中，考虑的约束条件，例如索引可以使用的最大磁盘空间这里指的工作量，可以来口两个方面：使用sql server捕捉的轨迹和包含了 sql 语句的文件。索

28、引调整向导总是基于一个已经定义好的工作量。如果一个工作量不能反映正 常的操作，那么它建议使用的索引不是实际的工作量上性能最好的索引。索引调整向导调用 查询分析器，便用所有可能的组合评定在这个工作量中每一个查询语句的性能。然后，建议 在整个工作屋上可以提高整个查询语句的性能的索引。如果没有供索引调整向导来分析的工 作虽:，那么可以使用图解器立即创建它。一旦决定跟踪一条正常数据库活动的描述样本，向 导能够分析这种工作量和推荐能够提高数据库工作性能的索引配置。索引调整向导对工作量进行分析之示，可以查看到一系列的报告，还可以使该向导立即 创建所建议的最佳索引，或者使这项工作成为一种可以调度的作业，或者

29、生成一个包含创建 这些索引的sql语句的文件。索引调整向导允许为sql server数据库选择和创建-种理想的索引组合和统计，而 不要求对数据库结构、工作最或者sql server内部达到专家的理解程度。总之，索引调 整向导能够作到以卜儿个方面的工作：通过使用杏询优化器来分析工作聚屮的杏询任务，向有人量工作址的数据库推荐一种最 佳的索引混合方式分析按照建议作出改变z后的效果，包括索引的用法、表间查询的分布和大量工作中查 询的工作效果为少录杳询任务推荐调整数据库的方法通过设定高级选项如磁盘空间约束、最大的查询语句数量和每个索引的最多列的数量等, 允许定制推荐方式图解器图解器能够实时抓取在服务器屮

30、运行的连续图片，可以选取希望监测的项目和事件， 包括transact-sql语句和批命令、对彖的用法、锁定、安全事件和错误。图解器能够过 滤这些事件，仅仅显示用户关心的问题。可以使用同一台服务器或者其他服务器重复已经记 录的跟踪事件，重新执行那些已经作了记录的命令。通过集中处理这些事件，就能够很容易 监测和调试sqlserver中出现的问题。通过对特定事件的研究，监测和调试sqlserver 问题变得简单多了。杏询处理器查询处理器是-种可以完成许多工作的多用途的工具。在查询处理器屮，可以交互式地 输入和执行各种transact-sql语句，并且在一个窗口中可以同吋查看transact-sql语 句和其结果集；可以在杏询处理器屮同时执行多个transact-sql语句，也可以执行脚木 文件中的部分语句；提供了一种图形化分析查询语句执行规划的方法，可以报告由查询处理 器选择的数据检索方法，并j1fj以根据查询规划调整查询语句的执行，提出执行可以提高性 能的优化索引建议，这种建议只是针对一条杳询语句的索引建议，只能提高这一条杏询语句 的杏询性能。系统为每一个索引创建一个分布页，统计信息就是指存储在分布页上的某一个表屮的一 个或者多个索引的关键值