浅谈Oracle索引

最新推荐文章于 2023-05-13 16:41:38 发布

justic1981

最新推荐文章于 2023-05-13 16:41:38 发布

阅读量593

点赞数

文章标签： oracle list parallel 数据库 object sql

本文链接：https://blog.csdn.net/justic1981/article/details/5159296

版权

前言：

以前总觉得索引很神秘，总是避开和它打交道（找别人帮忙）。最近有任务研究索引，我知道我逃不掉了哈哈。看了网上N个大虾的帖子，我总结了下，加入了点自己的东西。希望能给接触索引的同志们一些帮助。也希望大虾们光临指导。

因为参考的大虾太多了，我就不一一列举了，希望被我引用的大虾们不会雷我就好 --JUST

-----------------------------正文---------------------------------------------

一、为什么使用索引

1. 使用索引来消除一些不必要（如表中没有索引，排序合并连接）的排序操作；

2. 通过增加索引来避免不合法的全表扫描（一般来说，在有序表中查询返回的记录数少于表记录数的 40 ％，或者在无序表中返回的记录数少于表中记录数的 7 ％）；

3. 对于数据列的唯一值较少的字段，建立位图索引，以提高性能；

4. 创建基于函数的索引来避免全表扫描；

5. 改善多个表的联结的性能

简单来说，使用索引提高查询速度

二、创建索引索引准则

1) 适合

l 列的可选择性

比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。如果该列的”唯一键的数量/ 表中的行数”的比值越接近1 ，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询

l 如果需要经常地检索大表中的少于 15% 的行，就创建索引。这个百分比的变化很大，取决于表扫描的相关速度和索引键是如何将行数据分簇的。表扫描越快，百分比越低；行数据的簇越多，百分比越高。

l 为了改善多个表的联结的性能，索引列常用于联结。

l 列中有许多空值，但经常查询所有具有值的行，此时使用如下语法：

WHERE COL_X > -9.99 * power(10,125)

上面的语法比下面的好

WHERE COL_X IS NOT NULL

这是因为前一句使用了 COL_X 列上的索引假设 COL_X 是一个数值列

2) 不适合

l 小表不需要索引。如果查询所花的时间太长了，可能这个表已经由小变大了。

l 列中有许多空值，但又不查询非空值。

l LONG 和 LONG RAW 列不能被索引。

l 单个索引项的大小不能比数据块中可用空间的一半多太多（减去某些杂项开销）。

三、建什么样的索引

1. B- 树索引 —— 默认的和最常用的

CREATE INDEX ic_emp ON employee ;

2. 位图索引 —— 紧凑的特别适用于具有少量值集的列

CREATE BITMAP INDEX partno_ix ON lineitem ( partno )

3. 基于函数的索引 —— 包含函数 / 表达式的预先计算的值

CREATE INDEX emp_i ON emp (UPPER( ename ));

四、索引的设置

1) PARALLEL/ NOPARALLEL

PARALLEL 选项：当创建索引时， Oracle 首先会进行全表扫描来收集符号键和 ROWID 对，而 PARALLEL 选项答应多处理器并行扫描表，这样就会加速索引的创建过程。一般推荐的 PARALLEL 值为 CPU 数减 1 。

下面的 sql 创建一个索引，这个索引使用 10 个并发执行服务， 5 个扫描 SCOTT.EMP 另外 5 个使用 EMP_IDX 索引：

CREATE INDEX emp_idx

ON scott . emp ( ename )

PARALLEL 5 ;

2) LOGGING/NOLOGGING

NOLOGGING 选项因为不写日志，所以大大提高了性能，比不使用 NOLOGGING 选项大约会节省 70% 的时间。使用 NOLOGGING 创建索引的唯一风险就是假如数据库需要做前滚恢复，你将不得不重建索引。

create index emp_idx on scott . emp ( ename ) NOLOGGING

3) COMPRESS

COMPRESS 选项用于在创建非唯一性索引时压缩重复值。对于连接索引（即索引包含多列）来说， COMPRESS 选项会使索引的大小减少一半以上。 COMPRESS 选项也可以设置连接索引的前置长度。

oracle 在做 compress 时会为每个唯一的值增加 4bytes 的开销，所以不应对唯一列进行压缩。

下面语句压缩 ename 列， COMPRESS 后的数字代表从后往前压缩列的个数。 Bitmap 索引不需要压缩。时间换空间。

CREATE INDEX emp_idx2 ON emp (job, ename ) COMPRESS 1 ;

4) MONITORING USAGE

监控索引是否被使用。只能在创建索引后使用 alter index 加到索引上。

开启监视：

alter index emp_idx2 monitoring usage

可用查询视图 V$OBJECT_USAGE 来查询正在被监视的索引，以便查看是否该索引已经被使用。该视图包含一个 USED 列，其值是 YES 或 NO 取决于该索引在被监视期间是否已经被使用。该视图还包含该监视阶段的开始和结束时间。还包含一个 MONITORING 列（ YES/NO ），以标识是否现在激活了使用监视。

每次指定 MONITORING USAGE 时， V$OBJECT_USAGE 视图都被针对指定的索引进行重新设置，以前的使用信息被清除或重新设置，新的开始时间被记录下来。当指定 NOMONITORING USAGE 又不执行进一步的监视时，则该监视阶段的结束时间被记录下来，在下一次发布 ALTER INDEX MONITORING USAGE 语句之前，该视图的信息保持不变。

关闭监视：

alter index emp_idx2 nomonitoring usage

这种方式可以看到在监控期间内索引是否被使用，但不能知道索引被使用次数和被谁使用。可以参照视图 v$object_usage 来建立自定义视图获得想要的信息。

5) TABLESPACE

当数据库管理员为表或者字段建立索引的时候，可以选择是否指定具体的表空间。若没有具体指定的话，则数据库会自动在用户的默认表空间创建索引段。

最好能够把表与索引保存在不同的表空间中。确切的说是确保数据表空间和索引表空间置与不同的硬盘控制卡控制的硬盘上。因为 Oracle 数据库能够并行读取不同磁盘中的数据，从而达减少输入输入冲突的目的。而现在把索引与其对应的表存放在不同硬盘上的不同表空间中，就可以提高查询的效率。

注：对于磁盘阵列来说，即使把索引放到与表相同的表空间索引和表的物理存储也可能存在于不同的磁盘上。但为了方便管理一般还是会放到不同的表空间。

create index emp_idx on scott . emp ( ename ) TABLESPACE ts1

五、索引的使用

1) 一般使用

l 单列单索引

定义：对某一列建立索引

使用条件：该列出现在 Where 字句中

注意：该列应单独出现在连接符左侧。如： where colname=??

l 多列单索引

定义：对几个列建立索引

使用条件：定义索引时使用的第一列一定要出现在 Where 字句中

如索引定义如下：

create index multindex on multiindexusage ( first , second )

使用索引：

select * from multiindexusage where first = 1

不使用索引：

select * from multiindexusage where second = 1

l 多索引同时使用

对一个表建立多个索引，如果索引是同级别的，查询时按单索引规则使用索引。可能使用一个，也可能使用多个。

比如表 test 有两列 aa 和 bb ，分别对 aa 和 bb 建立索引 idx_aa 和 idx_bb ，在执行查询

Select * from test where aa = 1 and bb = 1 时， idx_aa 和 idx_bb 都会被使用。

如果这时再建立一个 aa 和 bb 的联合索引 idx_ab 时，上面查询会使用单一索引 idx_ab 。

如果索引不是同级别的，比如同时又一个唯一索引和一个普通索引加在表上，查询会选择使用唯一索引而不使用普通索引。

2) 强制使用

使用提示可以强制优化器使用指定索引。

提示使用索引：

Select /*+index(test)*/ * from test where aa = 1 and bb = 1

提示使用指定索引：（当一个表有多个索引的时候）：

Select /*+index(test idx_bb)*/ * from test where aa = 1 and bb = 1

3) 强制不使用

有时我们想选择使用同级索引中的某一个，或是我们想选择低级别索引而不是用高级别索引，又或语句的执行计划中有不良索引时，可以人为地屏蔽该索引（通过添加计算或函数），方法：

l 数值型：在索引字段上加0 ，例如
　　 select * from emp where emp_no + 0 = v_emp_no ;

l 字符型：在索引字段上加 ‘’ ，例如
　　 select * from tg_cdr01 where msisdn|| '' = v_msisdn ;

这是一种相当直接的提高查询效率的办法 . 但是你必须谨慎考虑这种策略 , 一般来说 , 只有在你希望单独优化几个 SQL 时才能采用它 .

4) 收集信息

在创建和重建索引后，需要手动执行收集索引统计信息语句，将索引的最新信息反馈给系统，这样可以使优化器更好的了解和使用索引。

analyze index IndexName compute statistics

注：现有 ERP 系统每周重建一次索引，重新收集一次索引信息。每周收集两次表信息。

六、索引的监控

建立索引后并不表明执行效率就一定提高了，相反，有时候过多的索引反而降低执行效率。我们在建立索引后要监控索引是否被使用。索引的监控包括两个部分：监控无效索引和重建低效索引。

1) 查找无效索引

使用 monitoring usage 功能在一段时间内对索引进行监控，可以得到这段时间的无效索引。如果无效索引占总索引比例较大可以考虑清除无效索引提高效率。

2) 查找低效索引

使用下面语句查找低效索引。

1. 分析索引结构：

analyze index IndexName validate structure

有两种模式， online 和 offline 。使用默认模式 offline 分析时，会将统计信息放入视图 index_stats 中，同时会对表加一个 4 级别的锁（表共享），对执行系统可能造成一定影响。

2. 分析是否需要重建：

SELECT name,

del_lf_rows ,

lf_rows ,

ROUND ( ( del_lf_rows / ( lf_rows + 0.0000000001 )) * 100 ) frag_pct

FROM index_stats

一般如果该索引的 frag_pct>10% ，那么就需要重建索引。

注：一般不会一个一个分析索引是否需要重建，都使用定期重建。

3) 察看索引在 sql 中使用

使用 Oracle 中的 Execution Plan 功能察看当前 sql 执行情况。

七、索引的重建

当我们创建索引时， oracle 会为索引创建索引树，表和索引树通过 rowid( 伪列 ) 来定位数据。当表里的数据发生更新时， oracle 会自动维护索引树。但是在索引树中没有更新操作，只有删除和插入操作。

例如在某表 id 列上创建索引，某表 id 列上有值 “101” ，当我将 “101” 更新为 “110” 时， oracle 同时会来更新索引树，但是 oracle 先将索引树中的 “101” 标示为删除（实际并未删除，只是标示一下），然后再将 “110” 写到索引树中。

如果表更新比较频繁，那么在索引中删除标示会越来越多，这时索引的查询效率必然降低，所以我们应该定期重建索引。来消除索引中这些删除标记。

ALTER INDEX INDEXNAME REBUILD