我是李香兰小朋友

索引

2015/02/10 Oracle

索引

索引分为B树索引和位图索引，我们主要介绍B树索引，oracle索引结构使用的是B+树的数据结构，根据存储引擎可以分为InnoDB（聚族）表分布和MyISAM(非聚族)表分布如下图：

聚族索引：

1. 当根据主键查询数据的时候，直接跟主键索引查询到复合条件的叶子节点（保存了整行数据，不用再在数据库里面查询数据再返回数据）
2. 当根据其他值查询数据的时候，先查询辅助建索引，查询到复合条件的叶子节点（保存了主键的信息），再根据主键索引查询数据。

非聚族索引：

1. 当根据主键查询数据的时候，查询主键索引，查到复合条件的叶子节点（保存了数据在数据库的位置），根据这个位置再在数据库里面查询，返回数据
2. 当根据其他值查询数据的时候，查询辅助索引，查到复合条件的叶子节点（保存了数据在数据库的位置），根据这个位置再在数据库里面查询，返回数据

索引的说明和目的

索引的说明

索引是与表相关的一个可选结构，在逻辑上和物理上都独立于表的数据，索引能优化查询，不能优化DML操作，oracle自动维护索引，频繁的DML操作反而会引起大量的索引维护
如果sql语句仅访问被索引的列，那么数据库只需从索引中读取数据，而不用读取表，如果该语句同时还要访问除索引列之外的列，那么，数据库会使用rowid来查找表中的行，通常，为检索表数据，数据库以交替方式先读取索引块，然后读取相应的表块

索引的目的是：主要是减少IO，这是本质，这样才能体现索引的效率

1. 大表，返回的行数<5%
2. 经常使用where子句查询的列
3. 离散度高的列
4. 更新键值代价低
5. 逻辑AND，OR效率高
6. 查看索引建在那表那列
   select *from user_indexes;
   select * from user_ind_columns;

索引的使用

  1. 唯一索引（唯一索引指键值不重复）

    create unique index empno_idx on emp(empno)

  2. 一般索引（键值可以重复）

    create index empno_idx on emp(empno)

  3. 复合索引（绑定两个或者多个列）

    create index empno_idx on emp(empno,job)

  4. 反向键索引

    create index empno_idx on emp(empno) reverse

  5. 函数索引

  6. 压缩索引

    create index empno_idx on emp(empno) compress

  7. 升序降序索引

    create index empno_idx on emp(empno desc,job asc) drop index empno_idx

索引的问题

查看执行计划：set autotrace tracenoly explain;

索引碎片问题：

由于对基表做DML操作，导致索引表块的自动更改操作，尤其是基表delete操作会引起index表的index_entries的逻辑删除，注意只有当一个索引块中的全部index_entry都被删除了，才会把这个索引块删除，索引对基表的delete，insert操作都会产生索引碎片问题

在oracle文档里面并没有给出索引碎片的量化标准，oracle建议通过segment Advisor解决表和索引的碎片问题，如果你想自行解决，可以通过查看index_stats视图，当以下情形之一发生时，说明积累的碎片应该整理了。

  1. HEIGHT>=4
  2. PCT_USED<50%
  3. DEL_LF_ROWS/LF_ROWS>0.2

解决索引碎片问题

分析索引：

analyz index 索引名 validate structure;
select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats;
delete t where rownum<7000000;
alter index 索引名 rebuild[online][tablespace name];

alter index rebuild online实质上是扫描表而不是扫描现有的索引块来实现索引的重建
alter index rebuild 只扫描现有的索引块来实现索引的重建。

Search

    Table of Contents