数据库索引概论及详解

记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中.

使用索引，在一般情况下，将能明显提高查询的性能，但系统为维护索引，也必将增加许多额外的开销。所以，何时应建立索引，查询时是否使用索引，对系统性能的影响将是非常大的。在这里，我想对这个问题谈一下自己的认识。

首先，在下列情况下，不适合建立索引：

1、表的规模不大，在这种情况下，直接查找表的开销比搜索索引

再定位的开销要小。

2、表被频繁更新，在这种情况下，维护索引的开销要大于使用索

引所带来的性能提高。

3、表上已经建立了许多索引。

4、用户的查询方式经常发生变化。

上述这些情况都是比较直观的，但是，即使建立了索引，在具体查

询时，系统也未必会使用该索引。

不管是何种数据库系统，其查询优化过程由两个层次构成：代数优

化（或称基于规则的优化）和物理优化（或称基于代价的优化）（部分

数据库系统可能不含物理优化过程）。

代数优化是使用一组预定义的规则来对查询进行优化，在这种优化

方式下，如果表上建有索引，系统将使用该索引。

物理优化是在代数优化的基础上，根据物理统计信息，来估计各种

执行方案的执行代价，从中选取一种最优（代价最小）的执行方案。在

这种优化方式下，如果表上建有索引，是否使用索引，将取决于查询的

“选中度”（selectivity）。

什么是选中度？举个例子，假设表中有一名为“年龄”的字段，有

一查询需要查出该表中所有“年龄”不超过50岁的记录，如果表中有70%

的记录满足这一条件，则称该查询的选中度为70。

当选中度超过某一预先给定的值P（P的大小取决于系统的具体实现）

时，遍历整个表的开销比搜索索引再定位的开销要小，此时系统将不使

用索引。

通过统计字段的值分布，可以估计查询的选中度，如果它大于P，系

统将不使用索引，直接遍历表。这是一种非常重要的统计信息，它还可

用于估计连接操作结果集的大小。

当然，当查询比较固定时，用户也可以根据自己对应用的理解预先估

计选中度，如果太大，则不应建立索引。

不过，最理想的方式是，系统能根据查询的特点和统计信息，自主选

择是否建立和使用索引，即索引对用户应是透明的。目前关于这方面的研

究也正在进行之中，比较成功的原型系统是Microsoft公司的Phoenix系统

（部分技术已用于SQL-SERVER的最新版本中），如果需要了解这一系统更

详细的信息，可以访问/db/phoenix 最后，我再来谈谈索引的种类，B+树和HASH表是最常用的两种索引，

前者适用于大型的表，后者则适用于较小的表。

ORACLE8i实现了一种所谓的“位图”索引，这种索引比较适合于字段

的取值范围较小，且分布比较平均的表。

例如，假设表中有一布尔类型的字段，它的取值包括“真”、“假”、

“未知”三种，我们可以为这三个值分别建立一个大小相同的位图，位图

中的一个位与表中的一条记录一一对应。以“真”值位图为例，假设某记

录的该字段取值为“真”，则它在位图中的对应位为“1”，否则为“0”。

这样，假设现在需要查询该表中所有该字段取值为“真”的记录，则只要

根据该位图，找到所有为“1”的位，将它们对应的记录取出即可。

在专用数据库中，往往也使用其它类型的索引（如R树）。

创建索引不是为了在sql语句中用的，而是可以大大提高系统的性能。

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

1．合理使用索引

索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。

现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处，

其使用原则如下：

●在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接

的字段则由优化器自动生成索引。

●在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。

●在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。

●如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。

●使用系统工具。如Informix数据库有一个tbcheck工具，可以在可疑的索引上进行检查。在一些数据库服务器上，索引可能失效或者因为频繁操作而使得读取效率降低，如果一个使用索引的查询不明不白地慢下来，可以试着用tbcheck工具检查索引的完整性，必要时进行修复。另外，当数据库表更新大量数据后，删除并重建索引可以提高查询速度。

2．避免或简化排序

应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。以下是一些影响因素：

●索引中不包括一个或几个待排序的列；

●group by或order by子句中列的次序与索引的次序不一样；

●排序的列来自不同的表。

为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表（尽管有时可能影响表的规范化，但相对于效率的提高是值得的）。如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。

3．消除对大型表行数据的顺序存取

在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表（学号、姓名、年龄……）和选课表（学号、课程号、成绩）。如果两个表要做连接，就要在“学号”这个连接字段上建立索引。

还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取。下面的查询将强迫对orders 表执行顺序操作：

SELECT ＊FROM orders WHERE (customer_num=104 AND or der_num> 1001) OR order_num=1008