ORACLE数据库索引的设计与维护研究

2012-09-17曹素丽杨延广

微型电脑应用 2012年11期

曹素丽，杨延广

0 引言

索引是数据库提高数据查询速度的一项关键技术。在ORACLE中，对一个含有900万条记录的数据表执行一个返回8行数据的查询，经测试发现，如果使用索引，查询的时间不足0.01秒，而不用索引时间则长达39秒，可见使用索引时查询效率会明显提高。但是，实际应用中，由于索引运用不当或者维护不及时而导致系统性能下降的情形却也是屡见不鲜的，因此，不管是数据库设计阶段还是系统运行阶段，都应该高度重视索引的使用。

1 索引概念与类型

索引，INDEX，是一个单独的、物理的数据结构，在这个数据结构中包括表中一列或几列的值以及该行数据存储的确切地址。这样，当用户需要查询数据时，不再直接扫描数据表，而是先从索引中找到所要的行，进而根据其对应的存储地址直接定位到该行数据，这样就减少了I/O次数和查询时间。这就是查询效率提高的原因，这种提高是通过预先建立索引的时间开销和增加的索引存储空间开销而获得的。

依据索引存储结构的不同，ORACLE将索引分为B-树（B-Tree）索引和位图（Bitmap）索引两大类，其中前者是多数数据库管理系统都支持的常用索引类型，其存储结构,如图1所示：

图1 B-Tree索引组成

图1表示的是对表中某字段索引相对应的存储结构，字段值为1到100，共100条记录行。图中可以看出，索引结构分根块（root）、分支块（branch）、叶块（leaf）三部分：根块属于索引顶级块，包含了指向下一级节点（分支块或叶块）的信息，分支块包含了指向下一级节点（分支块或叶块）的信息，叶块包含了索引入口数据，索引入口包含索引列值和列值对应数据行的ROWID（对于数据库的每一个数据行来说都有一个数据库范围内唯一的ROWID值，它代表了行存储的物理地址），叶块之间以双向链表的形式相互连接。注意，根块与分枝块中，每条记录是按照索引列值顺序排列的（默认为升序排列，也可以在创建索引时指定为降序排列），但是只存储所指向下一级节点中的索引块中所包含的最小键值，如根块“1”就是所指向的分支块“1，11，21,31,41”中的最小值，根块“51”就是所指向的分支块“51，61，71,81,91”中的最小值，再如分支块中的“11”就是指向下一级即叶块“（11，ROWID），（12，ROWID），......，（20，ROWID）”中的最小键值。

与 B-树索引不同，位图索引是以位值来表示索引数据的，一般用在DSS决策支持系统，适合重复值很多、不同值相对固定的列上。位图索引基于列中每个不同的值会建立一个位图，如，客户表(tb_customers)有一个性别字段(cgender)，该列仅有两种列值“男”、“女”，那么该性别列上的位图索引结构，如图2所示：

图2 表与基于只有2种列值的列建立位图索引

图2表明，对性别列建立位图索引，因为该列只有两个不同值，所以只建立2个位图，当行数据匹配于位图值时，相应位值为1，否则为0。

在ORACLE中，B-树索引属于默认常规索引，多用于OLTP联机事务处理系统；位图索引适用于OLAP联机分析处理系统和 DSS决策支持系统，应用于低基数（列拥有不重复值的个数很小）超级大数据量查询服务。建立它们的命令分别如下，位图索引建立时需要指明BITMAP保留字。

位图索引占用的磁盘空间比 B-树索引小得多，一般仅仅是在相同列上B-树索引所用空间的1/30～1/20之间。如，假定客户表有5万条数据记录，如果基于性别列建立B-树索引，则每个索引入口都会包含 2个字节的索引列值和 6个字节的受限ROWID，那么整个索引占用约100K的空间大小；如果该列上建立位图索引，则会建立2个位图，约占16K的空间大小。显然位图索引存储空间小很多，比 B-树索引能显著减少I/O次数，性能更好。

下面将就如何合理使用索引、提高效率进行分析。

2 索引的设计及有用性测试

2.1 合理设计索引

索引对于一个系统的性能来说是至关重要的，如果包含了WHERE子句，那么不管是SELECT语句还是UPDATE、DELETE语句，索引通常能使操作速度大大加快。尽管一个表上允许建立多个索引，但是索引并不是越多越好，因为索引在建立之后由系统自动维护，表上的DML操作会引发系统对相应索引的自动更新，例如，在每次向表中插入一行数据时，系统必须在该表的每一个索引中插入一个新键，而当表中数据被删除时，系统也要将每一个索引中的相应键值予以删除，经验性的认为，一般每增加一个索引，就会使DML语句的性能下降2/3倍。所以，数据库的索引策略需要设计人员、数据库管理人员共同参与制定，选择合适的索引类型，掌握一个平衡点，下面是一般的设计原则。

OLTP系统中应尽量减少索引数目，而对于查询密集的数据仓库系统则可以创建足够多的索引。

大表上建立索引，小表上一般不需要建立索引，因为一次磁盘读取就可以完成小表的全表扫描，而没必要首先读取索引，小表上建立索引所占用的资源开销和维护开销可能得不偿失，所以在小表上建立索引没有意义。

应该在 WHERE条件或在连接条件经常引用的列上创建索引，可使效率得到较大提高；

高基数列上适合建立 B-树索引，因为如果一个列中包含的不同值很少时，则为了找到每个不同的键值而检索的表的部分会过多。

低基数列上适合建立位图索引，位图索引更节省空间。在主键列和唯一约束列上系统会自动创建索引。

注意，经常被更新，或者一个表虽然很大，但是如果多数查询返回结果都超过表中总行数的4%，那么一般认为也是不宜建立索引的。

2.2 检测并清除无用索引

根据业务需求与索引设计指导原则，在设计了索引以后，有时候还需要对该索引到底能否被用上进行一下检测，毕竟系统是由团队多人开发的，前期设计的索引未必都会使用，如果发现一个索引一次没用过，应该将其删除，因为这个索引对于业务运行根本不会提供任何帮助。那么如何检测一个索引有用没用呢？以索引 ind_cname 为例，检测步骤如下：

（1）发布如下命令，以启用对指定索引的监控

（2）执行被监控索引对应的SQL查询语句，如：

（3）监控结果保存在v$object_usage 中， USED列的值若为YES表明索引被用，NO表明索引未用。例如，对于如下查询示例：

示例显示了2个索引的使用情况，各列的含义依次为：索引名、索引相关联的表、是否正处于监控、监控期间内是否使用了索引、监控的开始时间、监控的结束时间。最后一列值显示为空说明还未结束监控，需要执行第（4）步结束。

（4）发布如下命令，以停止对指定索引的监控

通过上述例子发现，对于查询条件“UPPER(cname)='JOHN'”，所建索引 ind_cname根本用不上，如果这类条件的查询是经常性的，那么就应该使用 DROP INDEX命令删除此索引，然后建立有用的索引——函数索引，建立命令用 CREATE INDEX ind_cname ON tb_customers (upper(cname))而不是用 CREATE INDEX ind_cname ON tb_customers (cname)。

需要提醒，启动监控后的过程中如果从未发布SQL语句，那么USED列会永远等于NO，所以监控周期可能需要比较长。

3 运行阶段的索引维护

随着应用系统的日益运行，原本有用的索引可能也会出现各种各样的问题，如索引出现严重碎片现象，索引变得不可用等，从而影响索引作用的正常发挥，导致系统性能的下降。所以对索引要经常监控，有问题时进行及时维护，接下来重点谈谈索引碎片与不可用方面的维护方法。

3.1 碎片监控与处理

（1）导致索引出现碎片的原因

索引键值的持续增加（比如 sequence生成的序列号的键值），同时对索引键值按照顺序连续删除，这会导致索引碎片的发生；不合理的较高的PCTFREE，这将导致索引块的可用空间减少；经常删除或更新的键值，以后几乎不再被插入。

（2）如何判断索引出现碎片

第一步，运行如下命令对索引进行分析：

ANALYZE INDEX 索引名 VALIDATE STRUCTURE;

注意，VALIDATE STRUCTURE有 ONLINE与OFFLINE二种模式，默认为OFFLINE：当以OFFLINE的模式分析索引时，会对数据表加一个表级共享锁，会对目前表的一些实时DML操作产生一定的影响；而以ONLINE模式分析时，则不会加任何锁，但在第二步的查询中不会看到任何信息。

第二步，由于分析结果已经放于index_stats，所以查询此表，如果发现 pct_used列的值低于 50%，则说明存在碎片。通常可以使用如下命令进行查询：

SELECT pct_used FROM index_stats WHERE name='索引名';

通过实践证实，刚建立的索引其pct_used值很高，以后随着表数据的增加、删除或更新，pct_used值会慢慢下降，当低到一定程度时就意味着需要重建索引了。

（3）消除碎片

通过先删除再重建索引，或是用带有REBUILD保留字命令的方法都可以消除碎片。

带有REBUILD保留字命令语法如下：

ALTER INDEX 索引名 REBUILD [ONLINE];

REBUILD实际上是重新建立一个新的索引，然后再删除原来的索引，所以在重新生成索引的过程中需要额外一倍的存储空间。其中 ONLINE是可选项，省略它则在重建索引的过程中锁定DML操作，加上它则重建期间允许表上的DML，但是速度会慢下来，不过与先删除再建的方法相比，这种REBUILD方法的速度总是要快。

对两种方法的一个简单比较，如表1所示：

表1 消除碎片的两种方法比较

适合场合在应用不需要用到该索引时候，或没有足够的空间支持REBUILD ONLINE方式尽可能采用

3.2 不可用索引检测与处理

（1）导致索引不可用的原因

索引变得不可用有多种原因，其中最为常见的情形就是执行ALTER TABLE ......REMOVE命令消除表中碎片时，因为表进行了重组，数据行的ROWID发生了改变，而索引中所记录的ROWID却没有改变，导致索引不可用。

（2）不可用索引的检测

在低版本的ORACLE数据库中，执行SQL语句时，如果会话试图使用不可用的索引那么会立即返回一条错误消息，同时语句执行失败，所以用户非常容易检测到不可用的索引。但是在10G及更高的版本中ORACLE改变了这种方式，如果SQL语句试图使用不可用的索引，那么这条语句会重新使用不需要该索引的执行计划，这样一来SQL语句的执行总是会成功，不过代价可能是性能显著降低。如果希望数据库实现旧版本中不可用索引导致返回错误信息的功能，则可以改变初始化参数skip_unusable_indexes的值，从默认的true改为false即可。

尽管如此，维护人员通常还是会查询dba_indexes来检测出变得不可用的索引，及早处理，而不是等用到了才发现存在问题。检测命令如下：