重温Oracle(5)

 

第十六章 Index 索引

 

索引

Select * from user_indexes;  --查询当前登录用户的现有索引

Select * from user_ind_columns; --查询索引的详细信息，有索引建立在哪些字段上等。

 

什么是索引(Index)

一种用于提升查询效率的数据库对象；

通过快速定位数据的方法，减少磁盘I/O操作；

索引信息与表独立存放；

Oracle数据库自动使用和维护索引。

 

索引的分类

唯一性索引

非唯一性索引

 

创建索引的两种方式

自动创建 - 在定义主键或唯一键约束时系统会自动在相应的字段上创建唯一性索引

手动创建 - 用户可以再其他列上创建非唯一性索引，以加速查询。

 

索引的优缺点

优点：

大大加快数据的检索速度；

创建唯一性索引，保证数据库表中每一行数据的唯一性；

加速表和表之间的连接；

在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：

索引需要占用物理空间；

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

 

创建索引的原则

创建索引一般有以下两个目的：维护被索引列的唯一性和提供快速访问表中数据的策略。

--在select操作占大部分的表上创建索引；

--在where子句中出现最频繁的列上创建索引；

--在选择性高的列上创建索引(补充索引选择性，最高位1，eg:primary key)

--复合索引的主列应该是最有选择性的和where限定条件最常用到的列，以此类推第二列…

--小于5M的表，最好不要使用索引来查询，表越小，越适合全表扫描。

 

使用索引的原则

--查询结果是所有数据行的5%以下时，使用index查询效果最好；

--where条件中经常用到表的多列时，使用复合索引效果会好于几个单列索引，因为当sql语句所查询的列，全部都出现在复合索引中时，此时由于Oracle只需要查询索引块即可获得所有数据，当然比使用多个单列索引要快得多；

--索引利于Select，但对经常insert，delete，尤其是update的表，会降低效率。

Eg：试比较下面两条SQL语句（emp表的deptno列上建有ununique index）:

语句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的效率高。

--where子句中的这个字段，必须是复合索引的第一个字段；

Eg：一个索引是按f1,f2,f3的次序简历的，若where子句是f2=:var2，则因为f2不是索引的第1个字段，无法使用该索引。

--where子句中的这个字段，不应该参与任何形式的计算：任何对列的操作都将导致表扫描，它包括数据库函数，计算表达式等等，查询时要尽可能将操作移至等号右边。

--应尽量数据各种操作符对Oracle是否使用索引的影响：一下这些操作会显式(explicitly，明确的)地阻止Oracle使用索引：is null; is not null; not in ; !=; like; numeric_col+0; date_col+0; char_col||’ ‘; to_char; to_number; to_date等。

 

Eg：select jobid from mytabs where isReq=’0’ and to_date(updatedate) >= to_Date(‘2012-11-27’,’YYYY-MM-DD’); --updatedate列的索引不会生效。

创建索引

Create index idxName on tblName(cols…); --在tblName上以cols（可多列）创建idxName索引。

 

索引的存储

索引和表都是独立村爱的。在为索引指定表空间的时候，不要将索引的表和索引指向同一个表空间，这样可以避免产生IO冲突。使Oracle能够并行访问存放在不同硬盘中的索引数据和表数据，更好的提高查询速度。

 

删除索引

Drop index pk_dept1;

 

索引类型

B数索引（B Tree Index）

创建索引的默认类型，结构是一棵树，采用的是平衡B树算法：

右子树节点的键值大于等于父节点的键值；

坐子树节点的键值小于等于父节点的键值。

 

位图索引(BitMap Index)

如果表中的某些字段取值范围比较小，比如职员性别、分数列ABC级。只有2个值。这样的字段如果建立B树索引没有意义，不能提高检索速度。这时我们推荐用位图索引。

Create BitMap Index pk_sex on student(sex);

 

管理索引

<!--[if !supportLists]-->1，  <!--[endif]-->先插入数据后创建索引；

向表中插入大量数据之前最好不要先创建索引，因为如果先创建索引，那么在插入每行数据的时候都要更改索引，这样会大大降低插入数据的速度。

<!--[if !supportLists]-->2，  <!--[endif]-->设置合理的索引列顺序；

<!--[if !supportLists]-->3，  <!--[endif]-->限制每个表索引的数量；

<!--[if !supportLists]-->4，  <!--[endif]-->删除不必要的索引；

<!--[if !supportLists]-->5，  <!--[endif]-->为每个索引指定表空间；

<!--[if !supportLists]-->6，  <!--[endif]-->经常做insert、delete尤其是update的表最好定期exp/imp表数据，整理数据，降低碎片（缺点：要停应用，以保持数据一致性，不实用）；有索引的最好定期rebuild索引（rebuild期间只允许表的select操作，可在数据库较空闲时间提交），以降低索引碎片，提高效率。

 

索引问题

<!--[if !supportLists]-->1，  <!--[endif]-->针对一个表的查询语句能否会用到两个索引？

<!--[if !supportLists]-->2，  <!--[endif]-->如果能用到，那么其实现原理是怎样的？

<!--[if !supportLists]-->3，  <!--[endif]-->效率如何？其代价如何，比如额外开销等。

 

1，  一个表的查询语句可以同时用到两个索引（原书有附结果图）。

<!--[if !supportLists]-->2，  <!--[endif]-->索引时可以独立于表存在的一种数据库对象，它是对基表的一种排序（默认是B树索引，就是二叉树的排序方式），比如：t表(x,y,z)，在X,Y,Z上分别建立了索引（idx1,idx2,idx3），那在查询select * from t where x=1 and y=2;的时候，会分别用到idx1,idx2。

原理：先到idx1索引表中查询符合x=1条件的记录，然后到idx2索引表中查询符合y=2条件的记录。

<!--[if !supportLists]-->3，  <!--[endif]-->这样的查询效率，肯定是大于没有索引情况下的全表扫描（table access full），但是有两个问题：

问题一：建立索引将占用额外的数据库空间，更重要的是增删改操作的时候，索引的排序也必须改变，加大了维护的成本；

问题二：如果经常查询x=?和y=?，那推荐使用组合index(x,y)，这样情况下组合所以你的效率也远高于两个单独的索引。

 

同时在用组合索引的时候，一定要注意一个细节：建立组合索引index(x,y,z)的时候，那在查询条件中出现x,xy,xyz,yzx都是可以用到该组合索引，但是y,yz,z是不能涌动啊该索引的。关于这段话的原文如下：

A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement:

CREATE INDEX comp_ind ON table1(x,y,z);

x,xy,and xyz combinations of columns are leading portions of the index

yz,y,and z combinations of columns are not leading portions of the index

 

 

 张文海