ORACLE 索引

相信大家都知道20/80规则，比如财富论中80%的人掌握世上20%的人的财富，20%的人掌握世上80%的人的财富，80%的人用脖子以下赚钱20%的人用脖子以上赚钱。在ORACLE性能优化方法论中也有这个规则：

（1）数据库体系结构设计和应用设计对系统性能的影响能占到80%，而硬件配置、参数调整、系统软件Bug等系统方面因素只占到20%。

（2）80%的性能问题是由20%的应用导致的。如少量大表的全表扫描导致性能瓶颈。

（3）80%的性能问题可以由20%的优化技术所解决，如简单的索引、执行路径优化等，能解决大部分性能问题。

那么本文就总结下ORACL优化方法论中20%的优化技术————索引。

索引技术由来已久，我们小学用的新华字典，图书馆里面找书，搜索引擎都用到了索引。据说Google存储了上百亿的网页，不知你是否想过为什么Google会在零点零几秒找到成千上万甚至上亿的搜索结果。如果是扫描所有文本，计算机扫描的速度再快也不可能做到这一点，这里面就用到了索引。

1.什么是索引。

记得小学时如果有一个汉字我们不知道他的读音，那么我们会通过字典中的笔划索引表很快找到这个字以及它对应的页码，然后翻到具体页码看这个汉字的详细信息，但是如果我们没有笔划索引表，那么我们只能一页一页翻字典直到在某页找到它。很显然前一种方式比后一种快。ORACLE中表一行一行的数据就相当于字典中所有的汉字，如何能快速的取出数据，ORACLE也用到了索引，在索引表中存储了每行数据的关键码以及数据的具体位置。

在ORACLE中每行数据都有一个ROWID,ROWID是一串文本，记录了本行数据在磁盘上的具体位置，如同字典中的页码记录了每个汉字的位置。索引表中就存储了ROWID，根据索引值找到rowid，然后再去磁盘取数据相对来说就快很多了。

2.ORACLE有哪些索引。

根据原理分类：B树索引（默认的索引）、位图索引；

应用的角度分类：

列值是否重复：惟一索引、非惟一索引（默认的索引）；

定义的列的个数：单列索引和复合索引；

根据索引表达式的不同：函数索引

下面对常用的索引B树索引和复合索引作简单介绍。

3.B树索引

B树索引，有地方写成B-树，全称就是Balanced树，即平衡树。B树类似于平衡二叉树，不过支持多结点。B树索引可以是一个列的(简单)索引，也可以是组合/复合(多个列)的索引

在下图的例子中，B树索引位于某表的人名列上。这个索引的二元高度为3； Oracle会穿过两个树枝块(branch block)，到达包含有ROWID的树叶块。在每个树枝块中，树枝行包含链中下一个块的ID号。树叶块包含了索引值、ROWID，以及指向前一个和后一个树叶块的指针。Oracle可以从两个方向遍历这个二叉树。B树索引保存了在索引列上有值的每个数据行的ROWID值。Oracle不会对索引列上包含NULL值的行进行索引。如果索引是多个列的组合索引，而其中列上包含NULL值，这一行就会处于包含NULL值的索引列中，且将被处理为空(视为NULL)。

B树特点：
适合大量的增、删、改（OLTP），因为只需要修改部分树结点，而不需要重新修改整个树结构；
不能用包含OR操作符的查询；
适合高基数的列（唯一值多）
典型的树状结构；
每个结点都是数据块；
大多都是物理上一层、两层或三层不定，逻辑上三层；
叶子块数据是排序的，从左向右递增；
在分支块和根块中放的是索引的范围；

针对B树单字段索引的设计建议有如下规范：

（1）分析SQL语句中的约束条件字段

（2）如果约束条件字段不固定，建议创建针对单字段的普遍B树索引

（3）选择可选择性最高（即字段值多）的字段建立索引。这里与位图索引正好相反。

（4）如果是多表连接SQL语句，注意被驱动表的连接字段是否需要创建索引

（5）通过多种SQL分析工具，分析执行计划并以量化形式评估效果。

4.复合索引

复合索引即在多列上建立索引。语法如：

CREATE INDEX ID_TEST ONTABLENAME(COLUMN1,COLUMN2,…);

这里COLUMN1,也就是复合索引的第一列，称为领导列。复合索引有两个重要的原则需把握：前缀性和可选择性。

（1）前缀性即如果查询条件中没有带上领导列，则通常情况下索引是不会起效的。

（2）可选择性：复合索引是按建立的字段顺序排序的，Oracle建议按字段可选择性高低进行排列，即字段值多的排在前面。因为字段值多，可选择性越强，定位的记录越少，查询效率就越高。

如果查询条件字段比较固定，则优先考虑创建针对多字段的复合索引。

5.索引也是双刃剑。

（1）索引表是为了针对数据表提高查询效率建立的，也需要占用物理空间

（2）对表中数据增加、删除和修改的时候，需要维护索引表，这样降低了原本的数据维护速度。

（3）如果查询出的结果大于整表数据的5%-10%以上时，使用索引可能比全表扫描性能更低。