Oracle之索引、执行计划

---

前言

对于关系型数据库（Oracle、MySQL）来说，索引是一种可以提高sql执行效率的一种方式，合理的创建索引可以大大提升查询速度，在工作中我们会经常遇到一些慢sql，对于这种sql我们就需要用到执行计划去分析这个sql为什么这么慢，接下来我们一起去探索索引的魅力。

---

一、索引是什么？

• 索引是用于加速数据存取的数据对象，合理的使用索引可以大大降低I/O次数，从而提高数据访问性能。索引形式类似于一棵树，树的节点存储的是每条记录的物理地址，也就是伪列（ROWID），普通索引会先根据索引树找到物理地址，然后根据物理地址找到所在记录。唯一索引创建后无法插入重复值，因为创建唯一索引的同时会创建一个唯一约束。其实主键就是一个唯一索引。当然索引也有缺点，建立索引，系统需要占用大约表的1.2倍的硬盘和内存空间来保存索引；更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。实践表明，不恰当的索引不但无济于事，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操做时比没有索引花费更多的系统时间。

二、索引创建原则

• 在大表上创建索引才有意义
• 在where子句或者连接条件上经常引用的列上建立索引
• 索引的层次不要超过4层

在一些场景建立索引是不合适的，比如很少或从不引用的字段；逻辑型字段，如男或女（是或否）等。综上所述。提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，这是考研DBA是否优秀的很重要的指标。

三、索引类型

• 单列索引：是基于单个列所建立的索引，比如，create index 索引名 on 表名(列名)
• 复合索引：是基于两列或多列的索引，在同一张表上可以有多个索引，但要求列的组合不同，比如，create index emp_idx1 on emp(ename, job) ; create index emp_idx1 on emp(job, ename) ;

四、Oracle执行计划

1.执行计划是什么？

• 执行计划是一条语句在Oracle中的执行过程或访问路径的描述。

2.执行计划常用字段解释

• Object owner：表对象所属用户
• Object name：当前查询操作的是哪个对象
• IO cost：IO的代价值
• Cardinality：Oracle估计的当前操作的返回结果集行数
• Bytes：执行该步骤后返回的字节数
• COST：Oracle估计的该步骤的执行成本，用于说明SQL执行的代价，理论上越小越好（该值可能与实际有出入）
• Time：Oracle估计的当前操作所需要的时间

3.怎么查看执行计划

• 在PL/SQL Developer中一种是Tools->Explain Plan；另一种就是按F5快捷键

4.表访问的几种方式（常见）

• TABLE ACCESS FULL（全表扫描）：Oracle会读取表中的所有行，并检查每一行是否满足SQL语句中的where限制条件；数据量太大的表不建议使用全表扫描，除非本身需要取的数据比较多，占到表数据总量的5%~10%或以上。
• TABLE ACCESS BY INDEX ROWID（通过ROWID的表存取），ROWID是由Oracle自动加在表每行最后一列的伪列，既然是伪列，就说明表中并不会物理存储ROWID的值，你可以像使用其他列一样使用它，只是不能对该列值进行增、删、改操作。一旦一行数据插入后，则其对应的ROWID在该行的生命周期内是唯一的，即使发生迁移，该行的ROWID值也是不可变的。TABLE ACCESS BY INDEX ROWID 行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID可以快速定位到目标数据上，这也是Oracle中存取单行数据最快的方法；
• TABLE ACCESS BY INDEX SCAN（索引扫描），在索引块中，既存储每个索引的键值，也存储具有该键值的ROWID。索引扫描其实分为两步：扫描索引对应的ROWID；通过ROWID定位到具体的行读取数据

5.五种索引扫描

• INDEX UNIQUE SCAN（索引唯一扫描）：针对唯一索引（UNIQUE INDEX）的扫描，每次至多返回一条记录；表中某字段存在UNIQUE、PRIMARY KEY约束时，Oracle常实现唯一性扫描。
• INDEX RANGE SCAN（索引范围扫描）：使用一个索引存取多行数据（一般指普通列索引，非唯一索引）；发生索引范围扫描的三种情况：
• 1.在唯一索引列上使用了范围操作符（如：> < <> >= <= between）
• 2.在组合索引上，只使用了部分列进行查询（查询时必须包含前导列，否则会走全表扫描）
• 3.对非唯一索引列上进行的任何查询
• INDEX FULL SCAN（索引全扫描）：进行全索引扫描时，查询出的数据都必须从索引中可以直接得到
• INDEX FAST FULL SCAN（索引快速扫描）：扫描索引中的所有数据块，与INDEX FULL SCAN类似，但是一个显著的区别是它不对查询的数据进行排序（即数据不是以排序顺序被返回）
• INDEX SKIP SCAN（索引跳跃扫描）：表有一个复合索引，且在查询时有除了前导列（索引中第一列）外的其他列作为条件

另外需要注意的是，索引列使用> <>不会走索引因为扫描的数据范围太大了无上限，会走全表扫描，而索引列使用= <会走索引范围扫描这个数据范围是有限的。

总结

关于索引以及sql执行计划的分析就梳理到这儿了，后面如果有新内容会继续更新。