Oracle之SQL优化-索引的基本原理（二）

Oracle之SQL优化-索引的基本原理（二）。

1、为什么使用索引？

(1)、原因

索引中只有一列，io小，所以较快；

索引中此列是排序的，二叉查找，提高查询速度。

(2)、原因分析

索引是对数据库表中一列或多列的值进行排序的一种结构。

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

当表中有大量记录时，若要对表进行查询有2中搜索方式：

第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，

这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；

第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）

快速找到表中对应的记录。

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

2、什么情况下适合建立索引：

(1)表的主键、外键必须有索引。

(2)经常与其它表进行连接的表，在连接字段上应该建立索引。

(3)经常出现在WHERE子句中的字段，特别是大表的字段，应该建立索引。

(4)索引应该建在选择性高的字段上。

(5)索引应该建在小字段上，对于大的文本字段甚至超长字段，不适合建索引。

(6)复合索引的建立需要进行仔细分析。

(7)正确选择复合索引中的主列字段，一般是选择性较好的字段。

(8)如果单字段查询很少甚至没有，那么可以建立复合索引；否则考虑单字段索引。

(9)如果复合索引中包含的字段经常单独出现在WHERE子句中，那么分解为多个单字段索引。

(10)如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段。

(11)如果既有单字段索引，又有这几个字段上的复合索引，那么一般可以删除复合索引。

(12)频繁进行DML操作(insert、update、delete)的表，不要建立太多的索引。

(13)删除无用的索引，避免对执行计划造成负面影响。

3、索引创建的策略

(1)导入数据后再创建索引。

(2)不需要为很小的表创建索引。

(3)对于取值范围很小的字段（比如性别字段）应当建立位图索引。

(4)限制表中的索引的数目。

(5)为索引设置合适的PCTFREE值。

(6)存储索引的表空间最好单独设定。

4、如何对索引进行操作：

(1)、索引存储位置——数据库文件：

数据库文件中存储着用户数据（表、索引等）、数据字典、存储过程、函数和数据包的代码、

用来排序的临时数据以及回滚段数据等。

(2)、查看索引：

ORACLE数据字典视图的种类分别为：USER,ALL 和 DBA。

　　USER_*:有关用户所拥有的对象信息，即用户自己创建的对象信息；

　　ALL_*:有关用户可以访问的对象的信息，即用户自己创建的对象的信息加上

　　 其他用户创建的对象但该用户有权访问的信息；

　　DBA_*:有关整个数据库中对象的信息

注：如果数据库中的索引数据很多，尽量避免查询ALL_INDEXES、DBA_INDEXES。

系统视图存放的是索引名称，对应的表和列等：

all_ind_columns/dba_ind_columns/user_ind_columns

  select * from user_indexes where table='表名' ;
  select * from user_ind_columns where index_name=upper('&index_name');

(3)清理索引碎片

a.合并索引(只是简单的将B树叶结点中的存储碎片合并在一起，并不会改变索引的物理组织结构）

    alter index emp_pk coalesce;

b.重建索引(不仅能够消除存储碎片,还可以改变索引的全部存储参数设置，并且可以将索引移动到

其它的表空间中,重建索引实际上就是再指定的表空间中重新建立一个新的索引,然后删除原来的索引)

    alter index emp_pk rebuild;

(4)删除索引

   drop index emp_ename; 

如果索引中包含损坏的数据块，或者包含过多的存储碎片，需要首先删除这个索引，然后再重建它。

如果索引是在创建约束时由oracle自动产生的,可以通过禁用约束或删除约束的方法来删除对应的索引。

在删除一个表时,oracle会自动删除所有与该表相关的索引。

5、常用到的一些索引操作

(1)查询一张表里面索引

 select * from user_indexes where table_name=upper('tableName'); 

(2)查询被索引字段

 select * from user_ind_columns where index_name=('indexName');

(3)给某一字段创建索引

 create index index_name on table_name(col_name);

(4)查看用户下的索引

  select  * from user_indexes-          -----查看当前用户下的所有索引
  select  * from user_indexes where table_name='A';      -----查看当前用户下表A的索引
  drop index index_name去掉索引 
  select index_name,index_type,status,blevel from user_indexes where table_name = '？';  
           -----查看某一个表的所有索引
  select table_name, index_name, column_name, column_position from user_ind_columns 
  where  table_name='？';    ----查看索引的构成

(5)建索引

 Create unique clustered index 索引名on 表名(字段1)  --单索引
 Create index 索引名 on 表名(字段1,字段2)  -------复合索引