一、索引应该是开发设计时就应该考虑的，而不是系统上线后才补充的。尤其是开发时了解应用的前提下，知道数据的流程，创建适当的索引，这对性能影响是至关重要的。

[@more@]

二、索引的种类：

1、 B*tree索引：同二叉树结构相似，是主要的索引类型。包括以下几种类型

（1、）索引组织表：以索引结构作为存储方式的表。

（2、）B*tree聚簇索引：聚簇中用于存储聚簇列的索引。

（3、）反向键索引：索引列值以反向的形式存储，以减少快速插入时对同一个索引块的争用。

（4、）降序索引：同一般的升序索引不同，其索引值以降序形式存储。

2、位图索引：一个单独的索引条目用位图指向不同的行。适用于低基数列且DML操作较少的表（只读），OLTP较少用，适用于DSS系统。

3、函数索引：B*tree或位图索引用于存储索引列函数计算后的值。

4、应用程序域索引：自我创建和存储（ORACLE内部或外部）的一种索引。可以自己决定索引的选择性和成本，以使优化器是否决定运用。

5、 interMedia Text索引：ORACLE特定的一种用于大文本关键字检索的索引。

三、B*tree索引：

1、非唯一索引：非唯一索引存储时事实上是唯一的，是因为ORACLE先会按列值进行排序，接下按ROWID排序进行存储，由于不同行的ROWID不同，因此索引存储时将会是唯一值。（相等列不同行对应的索引也是不同行的）

2、索引层次：索引高度一般情况下是自平衡的，也就是所有叶节点处于同一层次，且绝大数情况是为2-3层（数据可能是百千万以上，意味着只要两至三个IO就可以访问到相应的索引）。DELETE和UPDATE会影响到TREE的平衡，因为DELETE将把索引标为删除，但是不会释放空间，只能等相同值插入时才可以重用。UPDATE事实是上一个DELETE和INSERT的组合。因此如果表有大量的DML操作后，应该重建索引。（Oracle will try to keep every block in the index from about three quarters to completely full although, again, DELETEs and UPDATEs can skew this as well.）

3、索引压缩：索引为分前缀和后缀，前缀可能重复，后缀为唯一的。可以对相同的前缀进行压缩以节省空间，消除冗余数据。虽然压缩索引可以带来空间的节省，但是将带来管理上的复杂，如更改维护等，增加CPU开销，因此要综合考虑好了再应用。

4、反向键索引：RAC环境较适用，由于RAC下可能多个实例更改同一个块（’Pinging’），导致块争用。反向存储索引列的字节存储减少争用（带来空间利用率不高）。反向键索引由于存储位置物理上的分离导致多数情况下无法范围扫描。但是如果有复合反向索引的话，将可以范围扫描，因为前缀列是反向存储后还是相等的，如果前缀列一样的话，物理存储位置将是连续的。

5、降序索引：8i引入，之前虽然可以用DESC关键字来创建索引，但是物理存储将会忽略。降序索引主要是对索引列按降序进行排序存储。

6、 B*tree索引适用范围：

（1、）通过索引访问表中小部分数据时。

（2、）当索引能够提供大多数的查询需求信息时，如对索引列进行统计等。

（3、）关于使用索引百分比的一个例子：

我们经常在各种文档上看到说如果查询数据的百分比为表总数的20%以下时用索引会有比较好的效果，下面我们通过一个实例说明为什么这种说法是错的。

假如有一张表有100,000条记录，用户要访问其中的20,000条，也就是20%，符合我们通常意义上所说的比例。假如这个表每行占用80个字节，按一个块为8KB来算，那么每个块差不多存储100行，整个表占用1000个数据块。当我们通过索引访问表中数据（无序）的时候，由于表中数据是无序的以堆的形式存在的，那么访问20,000行将需要20,000个TABLE ACCESS (BY INDEX ROWID)操作，也就是说需要20,000个I/O我们才会访问到我们需要的数据，这还不包括读取索引的IO。而整个表一共才1000个数据块！

(4 、)CBO与索引的使用：

USER_INDEXES里面的CLUSTERING_FACTOR列的值用以表示索引的数据和相应表的数据是否有很好的对应关系。如果该值接近于表的块数，那么说明表中的数据分布是均匀有规则的，这种情况下索引叶块的索引条目通常指向表中同一个数据块的行数据；如果接近于表的行数的话，那么说明表中的数据分布是很凌乱的，同一叶块的索引条目指向的行数据可能分散在表中不同的数据块上，通过索引访问大范围数据的话需要重复读取相同数据块，导致索引效率不高。

7、 B*tree索引总结：B*tree索引是ORACLE中最通用的一种索引类型。多数情况下索引对查询有着积极的影响，但是也应该避免由于使用索引导致的性能下降。索引创建的时机，索引列的选择这些都是很重要的技巧，必须在开发阶段就应该决定好索引的创建。

四、位图索引：

位图索引从ORACLE7.3开始引入，主要为数据仓库环境而设计的，不适用于OLTP系统，尤其是有很多并发会话执行DML的系统。位图索引存储也是按树形结构存储的。不同于B*tree索引，位图索引的同一索引条目是存储有着相同列值的不同行的指针的位图组成。位图索引存储的值只为1(true),0(false or null)。当我们要通过位图索引对应的行时，ORACLE会先应用内部函数将对应行的比特位进行反转运算得到行的ROWID，再通过ROWID访问行数据。位图索引比较适用于低基数列，且基数越低效果越好。还有就是一些诸如查询参考许多列或者产生COUNT聚集的的查询也将从位图索引中受益。位图索引在集中读取的环境中有较高的效率，但是在有很多DML操作的情况下便不适合了。这是因为位图索引产生更改时会锁定整个索引条目对应的位图，而不是单行的比特位，严重影响了并发性。

五、函数索引：

函数索引是从Oracle 8i开始引入的，具有索引计算列的功能，允许应用程序通过执行自己的函数和操作符有效地扩展应用。适用于较少DML的表，对于查询较多时尤其有效。

要使用函数索引必须遵从下列前提条件：

（1、）使用CBO

（2、）拥有QUERY REWRITE或GLOBAL QUERY REWRITE权限

（3、）使用SUBSTR函数只能返回VARCHAR2或RAW数据类型的值

（4、）QUERY_REWRITE_ENABLE和QUERY_REWRITE_INTERGRITY两个会话和系统参数必须为TRUE和TRUSTED。前者表示允许优化器使用函数索引重写查询，后者表示“信任”开发人员的确定性代码。

六、索引FAQ：

1、索引和NULL：常规的B*tree索引不会存储全为空的值，聚簇索引和位图索引会存储全为空的值。

2、外键与索引：如果子表外键不存在索引的话，那么任何对主表主键的更新都将会导致锁定整个子表，额外锁定了许多行，严重抑制了并行性。一般情况下外键最好使用索引，以下情况除外：（1、）没有从父表中删除。（2、）不更新父表主键的值。（3、）不连接父表和子表。

3、为何没用到索引：

（1、）谓词没有使用索引的主要边界。如表T在（x,y）上有索引，如果有查询select * from t where y=5将不会使用索引，因为谓词中没有包括列X，而如果改成select x,y from t where y=5就会使用索引。

（2、）使用如select count(*) from t由于索引列存在全为空值，这样将不会用到索引。

（3、）对索引列使用函数：select * from t where function(indexed_column)=value;

（4、）隐式数据转换。如索引列是字符列，但列只包括数字数据，使用数字来查询的话将导致索引失效。应该避免这种情况，总是拿苹果和苹果比较，香蕉和香蕉比较。

（5、）当表中数据发生大范围更改时，必须及时收集信息，以使CBO做出正确的判断，决定索引的使用与否。

（6、）索引中空间是重用的。只要出现的行重用，索引块上的空间就可以重用。当一个索引块全为空时，将返回到freelist中，空间可以重用。

（7、）误解：创建索引时，必须将差异数最多的列当做前导列。事实上这是错误的，列的放置应该是肯定查询的需求而定的。

ITPub博客

Expert 101 Oracle——索引

最新文章