在这篇文章里，给大家简单介绍一下本人对Oracle索引的理解，如有不妥的地方，请不吝指教。

本文只讲最最平常最最简单的索引，就是以create index ix on tx(a,b,c);形式创建的索引，而不讲位图索引、反向键索引、倒序索引、基于函数的索引等等。其实呢，只要是基于B树的索引，不管是在Oracle, Mysql，还是其它数据库中，原理应当都是一样的。

索引最重要的一个性质应该就是有序，索引中的每一项，是从左到右，从小到大，以严格的顺序排列好的。

下面的讨论都以上面的索引ix(a,b,c)为例。

把这棵索引的叶子节点画到纸上，大概是这样的：
a1 a2 a3 ...... an
b1 b2 b3 ...... bn
c1 c2 c3 ...... cn

上面这个3×n的矩阵，每一列代表了一条记录，同时这一列记录，也对应了表里的唯一一条记录。当然，在Oracle里，对于non-unique索引，需要补上rowid，才是真正唯一的。上面的索引相当于create unique index ix on tx(a,b,c,rowid); 我们把这个细节忽略掉。

把每一列看作一个向量,vi = (ai, bi, ci)，
有序的含义就是：
vi < vj iff i ＜ j;

vi < vj这么定义：
(ai ＜ aj) or (ai = aj and bi ＜ bj) or (ai = aj and bi = bj and ci ＜ cj)

从这个基本性质，我们可以得到一些其它性质(为了打字方便，ai+k表示a(i+k),而不是a(i)＋k)：
1) 如果ai, ai+1, ……, ai+k 都是相等的，那么，
bi ＜= bi+1 ＜= …… ＜= bi+k
2) 如果ai, ai+1, ……, ai+k是相等的，而且bi,bi+1, ……, bi+k也是相等的，那么
ci ＜= ci+1 ＜= …… ＜ ci+k

但是从 ai, ai+1, ……, ai+k相等，我们得不到
ci ＜= ci+1 ＜= …… ＜= ci+k这个结论。

索引相关的很多问题，都和上面提到的这几个性质有关系。

下面来看几个常见的查询:
q1) select * from tx where a = :va and b = :vb;
q2) select * from tx where b = :vb and c = :vc;
q3) select * from tx where a = :va and c = :vc;
q4) select * from tx where a = :va order by b;
q5) select * from tx where a = :va order by b, c;
q6) select * from tx where a = :va order by c;
q7) select * from tx where a = :va order by b, c desc;
q8) select * from tx where a = :va order by b desc, c desc;
q9) select * from tx where a = :va and b <= :vb1
qa) select * from tx where a = :va and b >= :vb
qb) select * from tx where a = :va and c >= :vc
qc) select * from tx where a = :va and b >= :vb order by c

大家可以考虑一下这些查询各自会以怎样的方式执行，不同查询之间有什么区别？

同样，为什么在索引字段上作了函数运算之后，索引不可用?

考虑下面这个语句:

select * from tx where f(a) = :vfa;

首先，在字段 a上作了函数运算之后，排序的规则是否仍旧一样？ a < b 与 f(a) < f(b)是否等价？
其次，就算f(a)和a的排序规则一样，但是索引块中存的a, 但是你传给它的是经过了函数运算的值:vfa, 只有oracle知道函数f的反函数inv_f，并在vfa上做inv_f(:vfa)计算之后，才能通过索引的B树结果进行查找。

当然，现实中f可能不是显示的，而是隐式的，如传入参数和字段类型不匹配的情况下，Oracle可能在字段上作函数运算。从语句上可能看不出索引字段上被做了函数运算，但Oracle内部已经在字段上运用了函数。这样也会导致索引不可用，这种情况下用hint强制使用索引也是没用的。

通过dbms_xplan.display_cursor可以或许可以查看到这种隐式类型转换。

通过v$sql_bind_metadata应当可以查看到每个绑定变量的类型，

通过v$sql_bind_capture这个视图甚至可以看到每个绑定变量具体的值，不要把bind_capture和bind peek搞混哦，而且这里bind_cature也不会每绑定一次变量就capture一次，不然对执行量非常高，绑定频繁的语句，capture以同样频率进行的话，开销可能还是有点大的。

上面讲到了索引的有序性，下面来讲讲索引另外一个有趣的性质，其实，我们完全可以把索引看作一张表，这张表包含和主表一样多的记录（如果不考虑null)，只不过每条记录只有主表的部分字段，开个玩笑，我们是不是可以把索引叫做有序视图呢？或者精确一点，有序物化视图:)。

那么，我在执行一些查询的时候，如果所有字段都包含在索引中，是不是只要访问索引就可以了呢？

这些字段可以出现在select列表中，where条件中，order by字段中，也可以出现在两个表连接时的连接条件中。

那么，根据业务的需求，我们是不是可以设计或调整索引以减少对主表的访问呢？或者，是不是可以适当的调整应用的设计或实现来满足索引呢？

同时，考虑到索引的有序性，是不是可以利用索引来避免排序呢？

当然，我们不能忽略null的存在。如果一条记录在索引中的所有字段上都是null的，那么oracle是不会索引这条记录的。比如如果记录ri的ai, bi, ci字段都是null的，索引中是找不到这条记录的。这会有什么问题呢？首先表中的记录和索引中的记录从数量上来说就不一样了。

考虑一下Oracle会怎样执行下面这个查询:

select count(*) from tx;

这个呢，hint起作用了吗？

select /*+ parallel(tx, 4) */ count(*) from tx;

大家可以测试一下，怎样把count(*)这个操作并行化，从这里或许可以得到一些Oracle怎么处理hint的提示。

最后，讲一下Oracle CBO计算索引访问成本的公式：

cost =
blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)

这个公式相信很多地方可以找到（我是从cost base oracle fundamentals这本书里copy出来的)，简单说一下我自己对这个公式的理解：
blevel是索引树的高度，
leaf_blocks是索引的页子节点的个数，
effective index selectivity (eis)怎么算呢？
还是举几个例子，
1. where a = :va and b = :vb c = :vc
这里eis是 (selectivity a) * (selectivity b) * (selectivity c)
2. where a = :va and c = :vc
这里eis是 selectivity a
3. where b = :vb and c = :vc
这里eis是 1
4. where a = :va and b >= :vb and c = :vc
这里eis是 （selectivity a) * (selectivity range b)

就是说按索引字段的顺序，第一个不在where条件中出现的字段，或者第一个做了范围运算的字段，之后出现的字段的selectivity是不能乘到effective index selectivity里去的。

简单的说，ceiling(leaf_blocks * effective index selectivity)表示的是Oracle需要访问的索引叶子节点的个数。

clustering_factor表示的是按索引的顺序，从头走到尾，需要访问多少次数据块。这里需要考虑到Oracle的一个优化：如果连续n条记录在同一个表块中，那么oracle认为只需要访问一次数据块。
那么clustering_factor的值的范围就很容易确定了：cf >= table blocks and cf <= rows in index

effective table selectivity，这个计算就容易了，把索引中所有字段的selectivity乘起来就可以了。

如果查询中还有其它条件， 比如 d = :vd and e = :ve ....，但是d,e这些字段又不在索引中，那么在这些列上的过滤条件，需要回表后把这些值取出来才能判断，所以d,e这些列的selectivity是不能乘到effective table selectivity里去的。

ceiling(clustering_factor * effective table selectivity)表示需要回表的次数。

所以上面索引访问的cost就是走某个索引，需要访问的数据块的个数。

当然，前面的讨论忽略了index skip scan这种情况，因为本人对index skip scan也不是很明白。

什么情况下会走skip scan?

select * from tx where a = :va and c = :vc 是不是会在c这个字段上也作一个skip scan呢?

同时也没有考虑in list iterate，这些情况需要进一步研究。

建议继续学习：

1. 由浅入深探究mysql索引结构原理、性能分析与优化    (阅读：14945)
2. 浅谈MySQL索引背后的数据结构及算法    (阅读：9861)
3. 由浅入深理解索引的实现(2)    (阅读：6315)
4. HBase二级索引与Join    (阅读：5764)
5. 如何建立合适的索引？    (阅读：5334)
6. InnODB和MyISAM索引统计集合    (阅读：5161)
7. Innodb 表和索引结构    (阅读：4775)
8. mysql查询中利用索引的机制    (阅读：4686)
9. MySQL索引背后的数据结构及算法原理    (阅读：4402)
10. mysql索引浅析    (阅读：4062)