oracle11g基于SQL的优化之索引优化篇

Oracle11g 基于SQL语句性能优化通过索引对SQL进行优化

主讲人：***

所在部门：运维部

一、概述

本文所介绍的索引案例是在使用的是Oracle11g 11.2.0.4 数据库运行的。索引是使用最为普遍的一种优化SQL的方法，不同索引均有各自的优缺点。实际优化中需要综合考虑各种环境因素对运行慢的SQL进行优化。常见环境因素有：数据库表及索引的统计信息、列的柱状图，优化器的模式，表上是否有触发器，表上是否创建了物化视图日志，SQL语句是否使用提示符，当前会话的等待事件等。

Oracle数据库中索引可分为B-TREE索引、BitMap索引、全文索引三大类。按索引列的数量不同可分为，单列索引，多列索引。按列值是否唯一可分为唯一索引和非唯一性索引。

二、B-TREE索引

B-TREE索引常常用在OLTP数据库中，为了提高查询性,但同时一个表中索引数据多时会影响DML语句的性能，所以需要全面考虑增加索引后利弊。

2.1索引分类

主键索引、唯一键索引、非唯一键索引、多列组合索引。当表在创建主键时系统会自动为主键列或列的组合上创建唯一索引，主键索引性能最好。其它索引性能好坏取决于单列或多列的数据选择性，如果索引访问的数据小，性能相对较高，因为访问索引和表的块较少因而性能好。

2.2扫描方式

索引唯一扫描、索引范围扫描，全索引扫描，快速全索引扫描，索引跳跃扫描。

2.3上机实践

2.3.1 索引唯一扫描例子：

unique.txt

注意：由于唯一索引的列中可为空值。如果查询条件中有如下写法，则无法走索引扫描。因为b-tree索引中不存储空值。

(1)select * from tab where col is null

(2)select * from tab where col is not null

(3)select count(0) from tab;

其中(3)中的语句是否走索引取决于唯一索引的列上是否为非空，如果是非空，则会走“INDEX FAST FULL SCAN”快速索引扫描（采用并行索引扫描方式进行取读索引块，效率非常高）。

2.3.2 索引范围扫描例子

在非唯一性索引上的扫描通常都采用索引范围的扫描方式进行。

scan.txt scan2.txt

2.3.3 全索引扫描例子

全索引扫描指的是查询语句的所有列均在索引列中，同时需要访问全表的数据时使用。

indexfull.txt

2.3.4 快速全索引扫描例子

fast_fullscan.txt

2.3.5 索引跳跃扫描例子

skip.txt

2.4索引利弊

优点：当访问表中少量数据时可以提高查询的性能。

缺点：增加索引会降低DML语句的性能，尤其中表上索引多的时候尤为严重。

三、BitMap索引

位图索引常常用在在读为主的表中，准确地说是以读为主且创建位图索引的列上的唯一值较少的情况。位图索引用于提高单位查询速度或多表关联的查询速度，一般多用在报表统计中或数据仓库中。在DML操作的表中如果增加位图索引，不但不会提高查询性能，返而会因为位图索引锁的范围大而阻塞其它程序并发执行，使得其它运行运行变慢。

3.1索引分类

单列位图索引，多列位图索引。

3.2扫描方式

3.3上机实践

单位位图索引例子

多例位图索引例子

3.4索引利弊

优点：在只读表上的查询性能比B-TREE索引要高。因为B-TREE索引中记录的是位的位信息，占用空间小，因而查询性能高。

缺点：若在有写操作的表上创建位图索引，位图索引

四、全文域索引

三、总结