近来生产环境上经常发现某个查询超时，或是耗CPU很高。究其原因还是索引建的不好或是sql盲目复用（本来可以使用count，实际则复用了分页查询的sql）。下面详细介绍下索引的基本原理以及建索引时需要考虑的事项，另外也会介绍下如何分析执行计划。
1.B*树索引的起源
oracle中最常用的索引就是B*树索引，当然oracle对其他索引也支持，包括位图索引、应用域索引（自定义索引）等，这里仅介绍B*树索引，其他索引有兴趣可以查询相关的资料。
一般的树每个节点只有1个元素，每个节点可以有多个孩子，对于二叉树每个节点最多有两个节点。这样当元素非常多的时候树的度就会很大，相应的查找某个元素的时间会很长。如果树的所有节点都可以放在内存中，查询效果很好，但当元素太多时（如几百万、几千万）时，内存无法容纳全部的树节点，需要将绝大部分的树节点放在硬盘上，如果采用二叉树来查找则需要频繁的读取硬盘来比较，具体读取次数和树的度数有关。
为了解决这个问题，就提出了B树的概念。B树又称为多路查找树，它是二叉树的一个延伸。它打破了每个节点只能存储1个元素的限制，每个节点可以保存大于2个节点，其中节点最大的孩子数目成为B树的阶。2-3树是3阶B树，2-3-4树是4阶B树。在实际使用的B树中，B树的阶数和硬盘存储的页大小（数据库块大小）相当。B树可以支持大量数据的查询，比如一棵B树的阶为1001（即1个节点包括1000个关键字），高度为2，则它可以存储10亿个节点，我们只要让根节点持久保存在内存中，那么寻找一个关键字之多需要两次硬盘读取。（一次读索引，一次读数据）。
详细的B树结构介绍可以参考《大话数据结构》8.9节，或在网上搜索相关内容。B树也有一些缺点，例如他的非叶子节点也保存客户的key值，这些key值不会在叶子节点上出现，这样在进行范围扫描时会有一些问题，需要频繁往返叶子节点和分支节点。为了避免这个问题，提出了B+树的概念，B+树中叶子节点保存完整的key值，分支节点仅用于检索，不用于数据获取。另外为了支持范围扫描，在每个叶子中保存下一个叶子节点的指针，可以快速的访问下一个key值。B*树在B+树的基础上增加了”分支节点也保存下一个兄弟节点的指针”同时B*树定义了非叶子节点关键字个数至少是(2/3)*M（M为最大保存key个数），这样可以提高存储效率。兄弟节点可以提高树分裂的效率。B*树分配新节点效率比B+树低，空间使用率高（目前Oracle使用B*树索引）。
2.索引键的数据模型
根据前面的描述，B*树中完整key值保存在叶子节点。叶子节点中保存了索引的键及实际数据的指针，这里的键是指索引列，指针是指ROWID。索引的建是指建立索引的列，可以是多列。例如在(X,Y)上建立索引，可以认为索引的完整存储为（X,Y,ROWID)。其中X,Y默认都是按照升序排列的，如果应用有特殊的查询需求，需要建立降序索引，来对个别字段在索引中降序排列。
下面说下非唯一索引和唯一索引的区别：首先，在一个非惟一索引中，Oracle会把 rowid作为一个额外的列（有一个长度字节）追加到键上，使得键惟一。例如，如果有一个CREATE INDEX I ON T(X,Y)索引，从概念上讲，它就是CREATE UNIQUE INDEX I ON T(X,Y,ROWID)。在一个惟一索引中，根据你定义的惟一性，Oracle不会再向索引键增加rowid。在非惟一索引中，你会发现，数据会首先按索引键值排序（依索引键的顺序） 。然后按rowid升序排序。而在惟一索引中，数据只按索引键排序。另外，两者在效率上也有差别，如果是唯一索引，当查到特定的值后则不会继续向后扫描看有没有其他值。但对于非唯一索引则会进行这个扫描。索引唯一索引性能要好于非唯一索引。当然了索引是不是非唯一的不是性能决定的，而是业务需求决定的。
下面简单说下ROWID，ROWID在oracle中显示时由18位base64编码表示，例如：OOOOOOFFFBBBBBBRRR
OOOOOO：data object number, 对应dba_objects.data_object_id
FFF：file#, 对应v$datafile.file#
BBBBBB：block#
RRR：row#
在物理存储时采用10字节的二进制来表示。通过rowid可以唯一定位一条记录。对于非分区表rowid一般不可变，只有当执行某些特殊指令比如flashback table或是 alter table shrink时会改变rowid，正常的update不会更新rowid，即使某个字段update后数据块放不下，数据转移到其他数据块保存，rowid也不会改变（原来数据存放位置会放置指向新位置的指针，这样查询时相当于两次操作，会影响效率）。顺便一说，对于clob、blob字段oracle在存储时会保存到单独的段中，查询时也会进行多次查询，具体可以参考（小于4000字节的lob数据可以使用in row模式来避免这种情况)。rowid另一个会变化的场景是分区表，当分区字段发生修改后产生分区变动，这是会发生行移动，rowid也会变化，行移动代价很高。如果某个表没开行移动，在更新分区字段产品分区变动会直接报错。
3.局部索引与全局索引(全局分区索引与非分区索引)
索引和表一样也可以分区。如果分区是随表对索引完成的分区，即索引分区和表分区一致，称为局部索引。如果按照区间或是散列来自行分区，和表分区不一致，称为全局分区索引（当然也可以不分区）。全局索引保存了所有分区信息的索引。全局分区索引目前oracle仅支持前缀分区，非前缀分区建立时会报错。分区很大程度上是为了管理方便，对性能尤其是查询性能没有提升，甚至会减慢。分区在OLTP系统中更是如此，在OLTP系统应该慎用分区。
在使用局部索引查询时需尽量保证能够分区消除，否则查询会很慢，相当于查询了多个索引来搜索数据。局部索引有一个好处是当表某个分区被删除时，索引也会一并删除，无需重建索引。但是对于全局索引，当删除分区时，索引需要重建，重建时索引是失效的，会影响数据库的性能。局部索引又分为局部前缀索引、局部非前缀索引，这两种差别仅在于是否使用分区键作为索引的前缀（局部非前缀索引的非前缀列也可以包含分区键。由于分区不一定都是散列，也可能是范围等，分区键放在索引中也是有必要的）。如果一个查询将索引作为查询的第一步，局部前缀和非前缀索引没什么区别，具体使用哪种要根据实际业务场景来定，如果对于局部索引没有分区消除，则相当于顺序扫描了多个局部索引，性能也会变差。局部索引只能保证分区内部的唯一性，不能保证全局的唯一性，如果要保证全局的唯一性，分区键必须在约束本身中，例如使用timestamp来分区，就不能对ID列通过局部索引实现唯一性，只能通过全局索引，如果要通过局部索引的话约束中必须加上timestamp。在表分区失效时局部索引和全局索引具有同样的可用性高度，oracle会返回可用的且正确的结果。
分区主要是为了增加系统的可用性和可维护性。删除表分区时会导致全局索引失效，必须重建索引，否则索引不可用。对于这种情况有两种方法，1.跳过索引，这样将不实用这些失效的索引，会影响性能。2.让查询时接收到一个错误。3.更新分区时使用update global indexes来实时更新索引，但这会导致变更时间的增长，且会产生大量的redo和undo信息。而局部索引可以通过滑动窗口技术来实现瞬时切换，不影响可用性。
一般OLTP系统分区可能不会提高性能，甚至还会使性能下降；在数据仓库上建立分区一般会提升查询的效率。建立分区主要出于提高可用性、减少管理负担(索引重建方便、数据删除导入）、改善语句性能（并行DML)。单分区全局索引（不分区）和分区全局索引在查询上没什么差别，增加分区只是为了可用性和可管理性。但分区对于局部索引或是分区索引使用不当，会带来数倍的查询消耗，因为要查询多个分区的这个索引。这些都和具体的查询语句、索引建立方式、表数据维护方式有关。
4.执行计划初步解析
Oracle访问数据的存取方法有下面几种：
- 全表扫描（Full Table Scans， FTS）
- 通过ROWID的表存取（Table Access by ROWID或rowid lookup）
- 索引扫描（Index Scan或index lookup）有4种类型的索引扫描：
- 索引唯一扫描（index unique scan）
- 索引范围扫描（index range scan）
在非唯一索引上都是使用索引范围扫描。使用index rang scan的3种情况：
- 在唯一索引列上使用了range操作符（> < <> >= <= between）
- 在组合索引上，只使用部分列进行查询，导致查询出多行
- 对非唯一索引列上进行的任何查询。
- 索引全扫描（index full scan） oracle CBO通过分析决定走索引全扫描比表全扫描更快时，通常查询需要排序等时使用。
- 索引快速扫描（index fast full scan） 查询结果列全在索引中的，且查询条件未在索引前几列时。
多表联查时可能的连接方式：
下面简单介绍一下（详细参考 ），连接方式有3种：
- Nested Loops
对于外表返回的每一行都在内表中检索找到它的匹配行。外表为驱动表，内表查询时一定要有索引，驱动表的记录集比较小（<10000)。这样可以响应时间最快。
- Hash Join
散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。
- Sort Merge Join
操作步骤为：是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配，因为merge join需要做更多的排序，所以消耗的资源更多。通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。
其他执行计划谓词：
- SORT 排序，比较耗资源
- FILTER 过滤，如not in、min函数等容易产生。（对于查询条件前几列通过索引匹配后，对于其他在索引中的列会进行FILTER过滤；另外从表里取出数据后一般也会进行过滤）
- VIEW视图，大都由内联视图产生
- PARTITION VIEW 分区视图。
执行计划结果参数说明：
Statistics(统计信息参数)
-————————
0 recursive calls（系统或用户自动默认执行的调用）
8 db block gets(当前读的块数,即通过update/delete/select for update读时执行的当前读块数)
6 consistent gets(一致性读的块数，oracle默认所有查询都是一致性读)
0 physical reads(物理读:从磁盘读到数据块数量)
0 redo size (重做数:执行SQL的过程中，产生的重做日志的大小)
551 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory) (在内存中发生的排序)
0 sorts (disk) (在硬盘中发生的排序)
执行计划查看方法 ：
-
仅查看执行计划，不实际执行sql脚本，支持:形式参数
EXPLAIN PLAN FOR
SELECT * FROM SCOTT.EMP; --要解析的SQL脚本
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-
实际执行sql语句
set autotrace on/traceonly --（traceonly不显示执行结果）
set timing on
-- 执行需要查看执行计划的SQL语句
-
查看历史执行计划
- DBA_HIST_SQLTEXT、DBA_HIST_SQL_PLAN、DBA_HIST_SQLSTAT、DBA_HIST_SNAPSHOT
- DBMS_XPLAN包
select * from table(dbms_xplan.display);select * from table(dbms_xplan.display_cursor(null, null, 'advanced'));select * from table(dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number, 'advanced'));select * from table(dbms_xplan.display_awr('sql_id'));
参考资料：