1.index 需要储存空间和 I/O 操作。
2.index 的目的是加快 select 的速度的。
3.insert,update,delete 数据 oracle 会同时对索引进行相应的调整,因此会增加一定的消耗。
4. 使用 index 一定能加快 select 速度吗?不是的,数据少和巨大时 index 会影响 select 的速度,因此如果查询速度可以满足,就不要建 index 。
5 . Index 对 null 无效。
分类:
一、从物理角度
1. partitioned or nonpartitioned : 分区或不分区索引。分区索引用于分区表。
2.B-tree( 平衡树 ) : normal or reverse key 正常和倒序索引。
oracle 默认索引方式,平衡树形索引,在叶子节点上有双向链表,加快索引定位速度, oracle 有一定的优化,可以根据链表直接定位记录,而不走树,综合使用提高速度。见图 1 和图 2 。
图 1
图 2
3.bitmap( 位图 ) :用二进制的 0 、 1 来构建索引,在进行 or 操作时非常快 , 但要注意 bitmap 对于并发操作时,改一条会锁了很多记录,因为所有的记录在一个索引条目上,所以修改或增加时会一起锁定, 见图 3.
图 3
区别和使用场景
B-tree 索引 |
Bitmap 索引 |
Suitable for high-cardinality columns (记录对应的列重复的值较少,如主键,姓名等 )。 |
Suitable for low-cardinality columns( 用在记录相同的值较多的列上,如果性别只有两种值:男和女 ) 。 |
Updates on keys relatively inexpensive (在做 updated 时, b-tree 只消耗很少的资源)。 |
Updates to key columns very expensive (在做 updated 时, bitmap 的消耗是昂贵的)。 |
Inefficient for queries using OR predicates ( where 子句中 or 条件较多时速度较慢) |
Effcient for queries using OR predicates ( where 子句中 or 条件较多时速度非常快) |
Useful for OLTP (记录频繁的 insert 和 update, 查询相对较少的系统)。 |
Useful for data warehousing ( OLIP )数据仓库,查询系统等较少做数据修改的系统。 |
二、逻辑角度:
1.single column or concatenated 单索引和组合索引。
2.unique or nonunique: 唯一索引和非唯一索引。
3.function-based: 基于函数的索引,把一些 where 条件作为函数。
4.domain : 数据库以外的索引,如文件等。
三、创建 index 时的注意事项:
1.balance query and DML needs: 索引的目的是为了提高查询速度,但它会加重 DML 的负担。
2.place in separate tablespace: 索引和表应该放在不同的表空间,如果把索引和表放在同一个空间,会引起竞争,因为在读取一个表时,记录和索引是同时读取,修改也同步进行的。
3.use uniform extent sizes:Multipes of five blocks or minimum extent size for tablespace. 索引空间是 extent 是大小应该是 5 blocks 的倍数,因为 oracle 是一次读出 5 个 blocks ,如果你的 extends 是 6 ,就会造成 2 次 I/O 操作。
4. consider nologging for large indexes : 在创建索引时可以关闭索引对应的 redo 日志,提高速度,因为索引和数据不同,如果索引创建时出意外,数据还在,就再创建一次好了。
5.INITRANS should generally be higher on indexes than on the corresponding tables : INITRANS 参数比对应的表的值大些,因为索引也是已表记录的方式保存的,但索引大大小于表的记录,所以一个 block 中存储的索引记录就大大多于表在一个 block 中的记录,加大 INITRANS 可以增加在一个 block 中的事务的并发数,就提高了效率。
6.rebuilding indexes: 如果删除一条记录,对应的索引仅仅是做了逻辑删除,只有一个 block 中的全部索引都被标识为逻辑删除, orcle 才会真正的回收 block, 这时这个 block 才能被再次利用,在表的记录做 update 时, index 是先做了逻辑删除,然后再为该记录新建一个索引的,所以表在频繁的增删改后,就会造成 index 对应的 block 不完整,和系统碎片的情况是一致的,造成空间浪费,加大 index 的 I/O ,影响性能。而 rebuilding indexes 就可以回收原来的,重新构建一个高效的索引,但重构时会锁表。
语法: alter index index_name rebuild;
7 . coalescing indexes: 整理索引碎片,效率高,不锁表。
语法: Alter index index_name coalesce;
四.管理索引
1. 分析索引:
1) select * from user_objects where object_type= 'INDEX'
2 ) analyze index PK_T_TICKET validate structure;
3 ) select * from index_stats;
HEIGHT ( b-tree 的高度) |
BLOCKS (索引有多少块) |
NAME (索引名) |
LF_ROWS (记录数) |
DEL_LF_ROWS (删除记录数) |
2 |
256 |
PK_T_TICKET |
82775 |
792 |
当 DEL_LF_ROWS/ LF_ROWS>15% 时应进行 索引重建或 索引碎片整理。
2.drop 索引:当屁量导入大量数据时,索引会影响导入速度。可以现在drop 掉,导入后再重建索引。
3. 监控索引:
1 )设置监控那个索引 alter index pk_t_ticket monitoring usage;
2 )查看该索引用没有使用select * from v$object_usage
3 )select count(1) from pk_t_ticket;
4) 查看该索引用没有使用select * from v$object_usage
5) 关闭监控 alter index pk_t_ticket nomonitoring usage ;
监控一个月就大概可以知道那些是无用的索引了。
6 ) 查询索引的详细信息 : select * from all_ind_columns where index_name='PK_T_TICKET' . 那个表的那个列上有索引及详细信息。
参考:
oracle index学习总结 http://dolphin-ygj.javaeye.com/blog/543906