Oracle索引简要介绍

    Oracle为我们提供了强大的索引功能，如果能好好利用，可使性能有显著的提升。Oracle索引有BTREE索引、位图索引、函数索引几种，BTREE是我们平时用得最多的一种，下面对学习索引过程中一些常用知识点进行简要的记录（Oracle对索引的利用主要围绕三大特点：本身有序、高度较低、存储列值）。

    一、BTREE索引的创建过程：

    当我们为表的某个或某些字段创建索引时，Oracle会将这些字段的值连同这些记录的rowid取出来（有序取出，如对主键创建索引，会按照主键从小到大的顺序取出，所以索引本身就是有序的），存放到单独的块中，如果一个块不能存放下这些数据，会分布到多个块，多个块需要一个上级块来管理（也就是存储这些叶子块的信息），当上级块达到两个时，便会出现第三层级块来管理这些上级块。一般索引的高度不会太高，所以数据量悬殊很大时，利用索引来检索数据也不会有很大的性能差距。叶子块也就是最先建立的最底层的块存储了索引列具体值和能具体定位到数据块所在位置的rowid。

    二、Oracle索引本身有序特点的利用：

    索引访问的几种方式中包括INDEX FASTFULL SCAN和INDEX FULL SCAN，前者和后者的区别是前者支持同时访问多个索引数据块，提高读取效率，但是不能保证读取的数据有序，而后者一次只能读取一个数据块，读取出的数据是有序的。如DISTINCT语句本身需要对数据进行排序，如果使用INDEX FAST FULLSCAN的话，可以提高读取效率，但是不能避免排序操作，如果使用INDEX FULL SCAN，可避免排序操作。所以究竟在执行过程中使用哪种索引的读取方式是由ORACLE的基于代价的执行计划决定的。

    MAX/MIN操作所选择的列如果建立索引，会大大提升性能，利用有序性，可以直接最大最小值。但是当同时查询最大值和最小值时就要注意，如果使用select min(id),max(id) from tablename，即使有索引也会变成全索引扫描（即INDEX FAST FULL SCAN而不是INDEX FULLSCAN(MIN/MAX)），因为这时Oracle无法用INDEXFULL SCAN(MIN/MAX)这个算法同时在索引的最左边和最右边读取。这时可以这样写：selectmax,min from (select max(id) max from tablename) a，(selectmin(id) from tablename) b;

三、组合索引：

    为了避免索引回表读，可考虑建立联合索引，但是要注意平衡，因为联合列太多时，会导致索引过大，占用块过多，虽然减少了回表读，却可能在查询时要遍历更多的块。并且在更新时由于要维护索引的有序性，造成很大的开销。

    组合索引是先根据位置在前的一列进行排序，再根据后边的列进行二次排序。在等值查询情况下，组合索引的列无论哪一列在前性能都一样，但对非等值查询来说是有一定影响的，当一列是范围查询，一列是等值查询时，等值查询列在前范围查询列在后，索引更高效。

    当表中只有联合索引时，单列查询如果不与联合索引的第一列一致，那么单列查询不会走索引，只有一致时才会走索引。

四、位图索引：用比特位来表示列值，适用于重复度很高的列，并且更新很少的表，这时候检索效率最高。否则当列值重复读低时，需要更多的比特位来表示，占用更多的数据块，造成读取效率低；当表更新较多时，表更新时会将比特位相同值的列都锁住，造成瓶颈。

五、函数索引：对列的函数运算建立索引，如create index ** ontablename(upper(columnname))，这样在对列进行upper运算时便会走索引，否则BTREE索引不起作用。

六、使用索引的注意事项：

    使用索引时需要特别注意的是仅使用于查询结果较少的情况，如果查询结果是表的大部分数据，那么使用索引可能会降低性能，这是因为如上所说的索引本身有高度，如果高度为2，查询一条记录最少需要2次IO操作，如果读取所有记录，则IO操作会翻倍。此外如果索引的值中不包含查询结果，则需要回表读，产生大量多余的逻辑读操作，反而不如直接全表扫描来得快。

    索引不能存储空记录，在使用count(*)操作时，如果数据列可以为空的话，则不会走索引，除非是把数据列改成不能为空或者使用count(*) … where * is not null，SUM/AVG操作同样如此。

    Oracle不走索引的情景：