DBA成长随笔---oracle11g,性能优化之索引

目录

索引

索引的类型

B-tree索引

位图（bitmap）索引

---

索引

 目的：提高访问的效率

索引的类型

B-tree索引   B树索引

Bitmap索引 位图索引

Text index    全文索引

B-tree索引

当索引的主键没有重复的时候；那么查询一条数据时，比如 where id=100; 不管主表是1000条还是10000条数据；他们的效率几乎都是一样的。

高效的场景

索引有很高的selectivity或者结果集很小的时候。

低效的场景

索引字段有着很低的selectivity或者结果集很大的时候。

 select /*+ index(t idx_t)*/ * from t where object_id >10; -- 强行优化器通过索引方式访问 反而 效率更低

 select * from t where object_id >10; -- Oracle 优化器默认通过全表扫描的方式，效率更高一些； 因为返回的结果集太大与返回全表数据差不多；经过索引访问反而增加了一致性读的次数。

B-tree索引特点

基本适合所有数据库

没有明显的缺点

位图（bitmap）索引

似乎这个机制是对B-tree 索引的互补；理论上字段重复数据越多，其效率越高。

通过位的状态存储每条记录：

使用场景

OLAP

重复率很高的键值

不适用场景

OLTP

DML频繁操作

案例分析

---位图索引与B树索引在字段重复率较高环境进行对比

create table t4 as select * from dba_objects;

create table t5 as select * from t4;

create index idx_t4 on t4(owner);   -- B-tree

create bitmap index idx_t5 on t5(owner);-- 位图索引

exec dbms_stats.gather_table_stats(user,'t4',cascade => true);

exec dbms_stats.gather_table_stats(user,'t5',cascade => true);

select /*+ index(t4 idx_t4) */* from t4 where owner='SYS';

 select * from t5 where owner='SYS';

 由此可见在选择性比较低的时候，位图索引效率相对较高一点。

位图索引的位运算效率展现

 select /*+ index(t4 idx_t4) */count(*) from t4 where owner='SYS' or owner='TEST';

select count(*) from t5 where owner='SYS' or owner ='TEST';

位图索引的锁定

在位图索引里，一个dml操作影响的不仅仅是一行而是一整个位图段；位图段是指键值影响的所有的记录；如果更新的是同一个键值，位图索引将锁定。

示例：

Session 1:

 Session 2:

B-tree 索引不会引起阻塞

Session 1:

 Session 2:

Session 1:

 Session 2:

 总结：相同的键值会进行锁定；

进一步分析

 总结：位图索引修改数据，会影响所有受影响的键值关联的记录锁定。

全文索引

示例

create index idx_t7 on t7(object_name) indextype is ctxsys.context; --创建全文索引

使用场景

b-tree,bitmap 无法发挥作用的场景

Like ‘%string%’

缺点 ：bug多，占用磁盘空间大，维护成本高