你的位置:首页 > 数据库

[数据库]Oracle数据库对象_索引


查询是在表上进行的最频繁的访问。

在查询数据时,很少有用户愿意查询表中的所有数据,除非要对整个表进行处理。
一般情况下用户总是查询表中的一部分数据。

在SELECT语句中,通常需要通过WHERE子句指定查询条件,以获得满足该条件的所有数据。
如果能够在很小的范围内查询需要的数据,而不是在全表范围内查询,那么将减少很多不必要的磁盘1/0 ,查询的速度无疑会大大加快。
提供这种快速查询的方法就是索引。

索引的基本概念

索引是一种建立在表上的数据库对象,它主要用于加快对表的查询操作。

合理使用索引可以大大减少磁盘访问的次数,从而大大提高数据库的性能。
使用索引的主要目的是加快查询速度,另外,索引也可以作为唯一性约束。

如果在表的一个列上建立了唯一性索引,那么系统将自动在这个列上建立唯一性约束,这样可以保证插入这个列的数据是唯一的。

索引究竟是怎样加快查询速度的呢?

原来,索引是建立在表中的某个列或几个列上的,这样的列称为索引列。
在创建索引时,数据库服务器将对索引列的数据进行排序,并将排序的结果存储在索引所占用的存储空间中。
在查询数据时,数据库服务器首先在索引中查询,然后再到表中查询。
因为索引中的数据事先进行了排序,所以只需要很少的查找次数就可以找到需要的数据。

在索引中,不仅存储了索引列上的数据,而且还存储了一个ROWID 的值。
ROWID是表中的一个伪列,是数据库服务器自动添加的,表中的每一行数据都有一个ROWID值,它代表这一行的标识,即一行数据在存储空间的物理位置。
在访问表中的数据时,都要根据这个伪列的值找到数据的实际存储位置,然后再进行访问。
由于索引列上的数据已经进行了排序,在索引中很快就能找到这行数据,然后根据ROWID就能直接到表中找到这行数据了。

需要注意的是,表是独立于索引的,无论对在表上建立了多少索引,无论索引对表中的数据进行什么样的排序,表中的数据都不会有任何变化。

在查询一行数据时,首先在索引中查询该行的行标识,然后根据这个行标识找到表中的数据。
因为索引中的数据是经过排序的,所以采用了折半查找法查找数据,以达到快速查找的目的。

利用折半查找法在索引中查找数据的过程类似于遍历一棵二叉树,首先与根节点比较,如果与查找的数据相同,则一次访问就完成查询。
如果要查找的数据小于根节点,则在根节点的左子树中查找,否则在右子树中查找,这样查找的范围将缩小一半。
按照这种方法,每次将查找范围缩小一半,然后在剩下的节点中继续查找,直到找到所需的数据。

按照索引列的值是否允许重复,索引可以分为唯一性索引和非唯一性索引,其中唯一性索引可以保证索引列的值是唯一的。
按照索引列中列的数目,索引可以分为单列索引和复合索引。
按照索引列的数据的组织方式,索引可以分为B+树索引、位图索引、反向索引和基于函数的索引,这里仅介绍B+树索引的用法。

合理地使用索引固然可以大大提高数据库的查询性能,但是不合理的索引反而会降低数据库的性能,尤其是在进行DML操作时。
在创建索引时,表中的数据将被排序,如果对表进行了DML操作,表中的数据发生了变化,这时索引中的数据也将被重新排序,如果在表上建立了多个索引,那么每个索引中的数据都要被重新进行排序。

这种排序的开销是很大的,尤其是表非常大时。

索引是关系型数据库系统用来提高性能的有效方法之一,索引的使用可以减少磁盘访问的次数,从而大大提高了系统的性能。
但是在设计索引时必须全面考虑在表上所进行的操作,如果在表上进行的主要操作是查询操作,那么可以考虑在表上建立索引,如果在表上要进行频繁
的DML操作,那么索引反而会引起更多的系统开销。

一般来说,创建索引要遵循以下原则:
·如果每次查询仅选择表中的少量行,应该建立索引。

·如果在表上需要进行频繁的DML操作,不要建立索引。
·尽量不要在有很多重复值的列上建立索引。
·不要在太小的表上建立索引。

在一个小表中查询数据时,速度可能已经足够快,如果建立索引,对查询速度不仅没有多大帮助,反而需要一定的系统开销。

索引的创建、修改和删除

索引可以自动创建,也可以手工创建。如果在表的一个列或几个列上建立了主键约束或者
唯一性约束,那么数据库服务器将自动在这些列上建立唯一性索引,这时索引的名字与约束的
名字相同。
手工创建索引需要执行SQL命令,创建索引的命令是CREATE INDEX 。一个用户可以在自
己的模式中创建索引,只要这个用户具有CREATE INDEX这个系统权限。如果希望在其他用户
的模式中创建索引,那么需要具有CREATE ANY INDEX这个系统权限。
CREATE INDEX 命令的语法格式为:

CREATE INDEX 索引名 ON 表名(列1,列2 ...);

在这个索引中,索引列只有一个,这样的索引称为单列索引。

如果要建立复合索引,则要指定多个列。

例如:

CREATE INDEX ind_de_dn ON test(deptno, dname);

复合索引主要用于多个条件的查询语句中。

在默认情况下,创建的索引是非唯一的,也就是说,在表中的索引列上允许存在重复值。
如果要创建唯一性索引,那么需要使用关键字UNIQUE 。

例如:

CREATE UNIQUE INDEX ind_de ON test(deptno);

一般情况下,在指定索引中的列时,要遵循以下原则:

在WHERE子句中经常使用的列上创建索引。
尽量不要在具有大量重复值的列上创建索引。
具有唯一值的列是建立索引的最佳选择,但是究竟是否在这个列上建立索引,还要看是否对这个列经常进行查询。
如果WHERE子句中的条件涉及多个列,可以考虑在这些列上创建一个复合索引。

正如前面所说,合理设计的索引将提高系统的性能,而不合理的索引反而会降低系统性能。
所以,在数据库的运行过程中,要经常利用SQL Trace检查索引是否被使用,检查索引是否像期望的那样提高了数据库的性能。
如果一个索引并设有被频繁地使用,或者一个索引对数据库性能的提高只有微小的帮助甚至设有帮助,这时可以考虑删除这个索引。

索引信息的查询

与索引有关的数据字典有两个: user_indexesuser_ind_columns
例如,要查询索引的类型、所基于的表、是否唯一性索引,以反状态、等信息,可以执行以下查询语句:

SELECT index_type, table_name, status FROM user_indexes WHERE index_name='IND_DE';

下面的查询语句用来获得索引所基于的表和表上的列:

SELECT table_name, column_name FROM user_ind_columns WHERE index_name='IND_DE';

数据字典视图user_ind_columns各列的定义和意义如下

名称         意义

INDEX_NAME    Index name 

TABLE_NAME    Table or cluster name 

COLUMN_NAME   Column name or attribute of object column

COLUMN_POSITION Position of column or attribute within index

COLUMN_LENGTH  Maximum length of the column or attribute, in bytes

CHAR_LENGTH      Maximum length of the column or attribute, in characters

DESCEND      DESC if this column is sorted descending on disk, otherwise ASC

注:cluster 簇表;

attribute 属性

数据字典视图user_indexes常用各列的定义和意义如下:

名称          意义

INDEX_NAME      索引名称 

INDEX_TYPE     索引类型

TABLE_OWNER     对象属主

TABLE_NAME         对象名称     

TABLE_TYPE      对象类型

STATUS       状态

注:数据字典视图user_indexes上列有很多。