概述
索引分为B树索引和位图索引。我们主要研究B树索引,B树索引如下图(图片源自网络):![](https://i-blog.csdnimg.cn/blog_migrate/f91db05eb838819e4691df1a3b2088f8.png)
索引是与表相关的一个可选结构,在逻辑上和物理上都独立于表数据,索引能优化查询,不能优化DML,oracle自动维护索引,频繁的DML操作反而会引起大量的索引维护。
如果sql语句仅仅访问被索引的列,那么数据库只需从索引中读取数据,而不会读取表;如果该语句还要访问未被索引的列,那么数据库会使用rowid来查找表中的行,通常,为检索表数据,数据库以交换方式先读取索引块,然后读取对应的表。
索引的目的是减少IO,
- 大表,返回的行数<5%
- 经常使用where子句查询的列
- 离散度高的列
- 更新键值代价低
- 逻辑AND、OR效率高
- 查看索引建在哪表哪列
select * from user_indexes;
select * from user_ind_columns;
索引的使用
- 唯一索引
create unique index empno_idx on emp(empno); - 一般索引
create index empno_idx on emp(empno); - 组合索引
create index job_deptno_idx on emp(job,deptno); - 函数索引:查询时必须用到这个函数才会使用到。
create index fun_idx on emp(lower(ename)); - 。。。
索引问题
查看执行计划:set autotrace traceonly explain;
索引碎片问题:由于基表做DML操作,导致索引表块的自动更改操作,尤其是基表的delete操作会引起index表的index_entries的逻辑删除,注意只有当一个索引块中的全部index_entry都被删除了,才会把这个索引块删除,索引对基表的delete、insert操作都会产生索引碎片问题。
在oracle文档中并没有清晰的给出索引碎片的量化标准,oracle建议通过segment advisor(片段顾问)解决表和索引的碎片问题(后面的课程会提及),如果你想自行解决,可以通过查看 index_stats视图,当以下三种情形之一发生时,说明积累的碎片应该整理了(经验之谈)
- height >= 4 (概述中图示索引树的高度是3)
- pct_used < 50%
- del_lf_rows/lf_row > 0.2
实操:
先建表,建索引
![](https://i-blog.csdnimg.cn/blog_migrate/e121dec70161de164974893ffb2cb007.png)
然后插入1000000条数据
![](https://i-blog.csdnimg.cn/blog_migrate/cd0f5e12212032ac1afd4fa48a862eae.png)
分析索引:analyse index t_idx validate structure;
查看分析结果:select name,height,pct_used,del_lf_rows/lf_rows from index_stats;
![](https://i-blog.csdnimg.cn/blog_migrate/bdf61913d1ca68882278747bb7a66991.png)
然后我们来破坏一下索引,重新查看一下分析结果
![](https://i-blog.csdnimg.cn/blog_migrate/77725143556034a7923857226c333b04.png)
可以看到最后一个指标变了。说明产生一些碎片了。那么需要进行整理:
![](https://i-blog.csdnimg.cn/blog_migrate/99cafdf26ab6730cf6776cb26331e0e1.png)
![](https://i-blog.csdnimg.cn/blog_migrate/5a5acb2afb10fe65741c241ba7249516.png)
可见最后一个指标正常了(低于0.2)。