Oracle中索引的使用

港控/mmm° 2022-07-14 07:49 244阅读 0赞

**索引**  
什么是索引？  
一种用于提高查询效率的数据库对象。  
通过快速定位数据的方法，减少I/O操作。  
索引信息与表独立存放。  
Oracle数据库自动使用和维护索引。  
**索引分类**  
唯一性索引  
非唯一索引  
**创建索引的两种方式**  
自动创建，在在定义主键或唯一键约束时系统会自动在相应的字段上创建唯一性索引。  
手动创建，用户可以在其他列上创建非唯一索引，以加速查询。  
**索引的优缺点  
建立索引的优点**  
1.大大加快数据的检索速度;  
2.创建唯一性索引，保证数据库表中每一行数据的唯一性;  
3.加速表和表之间的连接;  
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。  
**索引的缺点**  
1.索引需要占物理空间。  
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的  
维护速度。  
**创建索引的原则  
创建索引一般有以下两个目的**：维护被索引列的唯一性和提供快速访问表中数据的策略。  
\--在select操作占大部分的表上创建索引  
\--在where子句中出现最频繁的列上创建索引；  
\--在选择性高的列上创建索引（补充索引选择性，最高是1，eg：primarykey）  
\--复合索引的主列应该是最有选择性的和where限定条件最常用的列，并以此类推第二列……。  
\--小于5M的表，最好不要使用索引来查询，表越小，越适合用全表扫描。  
\--查询结果是所有数据行的5%以下时，使用index查询效果最好；  
\--where条件中经常用到表的多列时，使用复合索引效果会好于几个单列索引。因为当sql语句所查询的列，  
全部都出现在复合索引中时，此时由于Oracle只需要查询索引块即可获得所有数据，当然比使用多个单列索引要快得多；  
\--索引利于select，但对经常insert，delte尤其update的表，会降低效率。

eg：试比较下面两条SQL语句(emp表的deptno列上建有uniqueindex)：

语句A：SELECT dname,deptno FROM dept WHERE deptno NOT IN
    (SELECT deptno FROM emp);
    语句B：SELECT dname,deptno FROM dept WHERE deptno NOT EXISTS
    (SELECT deptno FROM emp WHERE dept.deptno=emp.deptno);

这两条查询语句实现的结果是相同的，但是执行语句A的时候，ORACLE会对整个emp表进行扫描，  
没有使用建立在emp表上的deptno索引，执行语句B的时候，由于在子查询中使用了联合查询，  
ORACLE只是对emp表进行的部分数据扫描，并利用了deptno列的索引，所以语句B的效率要比语句A的效率高。  
**创建索引**

create index abc on student(sid,sname);
    create index abc1 on student(sname,sid);

这两种索引方式是不一样的

索引 abc 对 Select * from student where sid=1;  这样的查询语句更有效
    索引 abc1 对 Select * from student where sname="rose";  这样的查询语句更有效 因此建立索引的时候，

字段的组合顺序是非常重要的。一般情况下，需要经常访问的字段 放在组合字段的前面  
索引的存储  
索引和表是独立存在的。在为索引指定表空间的时候，不要将被索引的表和索引指向同  
一个表空间，这样可以避免产生 IO 冲突。使Oracle能够并行访问存放在不同硬盘中的索引数据和表数据,更好的提高查询速度。  
**删除索引**  
drop index PK\_EMP;  
**索引类型**  
B 树索引（B-Tree Index）  
创建索引的默认类型，结构是一颗树，采用的是平衡 B 树算法：  
右子树节点的键值大于等于父节点的键值  
左子树节点的键值小于等于父节点的键值 比如有数据:100,101,102,103,104,105,106  
![SouthEast][]  
位图索引(BitMap Index)  
如果表中的某些字段取值范围比较小，比如职员性别、分数列  ABC  级等。只有两个值。   
这样的字段如果建 B 树索引没有意义，不能提高检索速度。这时我们推荐用位图索引  
Create BitMap Index student on(sex);  
**管理索引**  
1）先插入数据后创建索引 向表中插入大量数据之前最好不要先创建索引，因为如果先建立索引。  
那么在插入每行 数据的时候都要更改索引。这样会大大降低插入数据的速度。  
2）设置合理的索引列顺序  
3）限制每个表索引的数量  
4）删除不必要的索引  
5）为每个索引指定表空间  
6）经常做 insert，delete 尤其是 update 的表最好定期 exp/imp 表数据，整理数据，  
降低碎片（缺点：要停应用，以保持数据一致性，不实用）；有索引的最好定期rebuild 索引  
（rebuild期间只允许表的 select 操作，可在数据库较空闲时间提交），以降低索引碎片，提高效率。  
**索引问题**  
1：针对一个表的查询语句能否会用到两个索引?  
2：如果能用到，那么其实现原理是怎样的？  
3：效率如何？其代价如何，比如额外开销等。  
======================================  
回答：  
1.一个表的查询语句可以同时用到两个索引。如下图：  
  
![SouthEast 1][]  
2.索引是以独立于表存在的一种数据库对象，它是对基表的一种排序（默认是 B 树索引就是二叉树的排序方式），比如：  
t 表(x,y,z) ,在 x,y,z 上分别都建立了索引(index1,index2,index3)，那在查询 select \* from t where x=1 and y=2;的时候，  
会分别用到 index1,index2。原理是先到 index1 索引表中查到符合 x=1 条件的记录，然后到 index2 索引表中查到 y=2 条件的记录。  
3.这样的查询效率，肯定是大于没有索引情况的全表扫描（table access full)，但是有两个问题。  
问题一：建立索引将占用额外的数据库空间，更重要的是增删改操作的时候，索引的排序也必须改变， 加大的维护的成本  
问题二：如果经常查询 x=?和 y=?，那推荐使用组合 index(x,y)，这种情况下组合索引的效率是远高于两个单独的索引的。  
  
  
同时在用组合索引的时候，大家一定要注意一个细节：建立组合索引  index(x,y,z)的时候，那在查询条件 中出现 x,xy,xyz,yzx 都是可以用到该组合索引，但是 y,yz,z 是不能用到该索引的，而是会全表扫描。 **原因：组合索引中的，第一列索引必须存在才会使索引生效。**  
  
  
关于sql优化，请移步 [ Oracle中编写Sql语句注意事项][Oracle_Sql]

[SouthEast]: /images/20220714/bb9bb859eb2f4625a7c88262634d8040.png
[SouthEast 1]: /images/20220714/968284b34ce144ae8c8e9c9b37eb6de5.png
[Oracle_Sql]: http://blog.csdn.net/smile_from_2015/article/details/53335063