ORACLE 索引优化技术

一.索引概述

Oracle作为关系型数据库，用户查找数据与行的物理位置无关，表中的每一行均用一个ROWID来标识，当Oracle数据库中存储海量的记录时，就意味着有大量的ROWID标识，那么Oracle如何能快速找到指定的ROWID呢？这时就需要索引对象了。

如果一个表中存有海量的数据记录，当对该表进行指定条件的查询时，常规的查询方法会将所有的记录都读取出来，一一与查询条件进行比较，最后返回满足条件的记录，这样操作会导致时间和I/O开销都非常大，我们可以通过建立索引来减少开销，有了索引之后，只需要在索引中找到符合查询条件的索引值字段，就可以通过保存在索引中的ROWID快速找到表中对应的记录。（将表看作一本书，索引就是目录，没有目录你就要一页一页翻，有了目录，根据目录中标记的页码（ROWID）去指定页中寻找你想要的内容即可，懂了吗？😁）

Oracle不仅要在数据字典中保存索引的定义，还需要在表空间中为它分配实际的存储空间。创建索引时，Oracle会自动在默认表空间或指定表空间中创建一个索引段。（因为Oracle能并行读取不同硬盘的数据，所以将索引和表放在不同硬盘的不同表空间中能够提高查询的速度，避免产生I/O冲突）

建立和规划索引时，必须选择合适的表和列，如果选择的不合适，可能会出现抱着木柴救火的绝美画面，不仅无法提高查询速度，反而会极大降低DML操作的速度，我总结出建立索引需要注意的几点：

索引应该建立在where语句频繁引用的表的列上。

索引应该建立在经常需要基于某列或某几列排序的表的列上。

一个表中的索引不是越多越好。索引主要用于加快查询速度，但索引越多会导致DML的速度越慢，所以索引的个数需要你好好权衡一下。

指定索引块空间的使用参数。基于表建立索引时，Oracle 会将相应表的列数据添加到索引块中。为索引块添加数据时，Oracle会按照PCTFREE参数在索引块中预留部分空间，该预留空间是为将来的INSERT操作准备的。如果将来在表上执行大量INSERT操作，那么应该在建立索引时设置较大的PCTFREE。

将表和索引部署到相同的表空间中，可以简化表空间的管理。将表和索引部署到不同的表空间中，可以提高访问性能。

当在大表上建立索引时，使用NOLOGGING选项可以最小化重做记录。使用NOLOGGING选项可以节省重做日志空间、降低索引建立时间、提高索引并行建立的性能。

最好不要在小表上建立索引，杀鸡何须宰牛刀？

多表连接时应在连接列上建立索引。

二.建立索引

Oracle创建索引的流程是首先对将要建立索引的字段进行排序，然后将排序后的字段值和对应记录ROWID存储在索引段中。Oracle索引也分几个类型，按照索引数据的存储方式可以分为B树索引、位图索引、反向健索引和基f函数的案引:按照索引列的唯性又可以分为唯有和非唯索引:按照索引列的个数又可以分为单列索引和复合索引，下面我就分类讲解一下各个索引。

1.B树索引

B树索引是Oracle默认的索引类型，它以B树结构组织并存储索引数据，不懂什么是B树的可以百度一下，你就知道。B树索引中的数据是以升序方式排序的，B树索引由根块、分支块和叶块组成，其中主要数据都集中在叶子节点上。

B树索引结构图

Oracle采用这种方式的索引，可以确保无论索引条目在何处，只需要花费相同的I/O即可获取它，所以它被称为B（Balanced）树索引。如果在Where子句中要经常引用某列或某几列，应该基于这些列建立B树索引。代码如下：

create index tiger_index on tigerlearn(tiger)

pctfree 25 --可以默认不写

tablespace Tiger_TS;

1

2

3

为tigerlearn表的tiger列创建索引tiger_index，子句pctfree指定为将来insert操作所预留的空闲空间，子句tablespace用于指定索引段所在的表空间。假设表已经包含了大量数据，那么在建立索引时应该仔细规划pctfree的值，以便为以后的insert操作预留空间。

2.位图索引

在B树索引中，通过在索引中保存排序的索引列的值以及记录物理地址ROWID来实现快速查找，但对于一些特殊的表，B树索引效率较低，比如一个表中有一列，数据只有正确、错误两个取值，如果建立B树索引，使用该索引对表进行检索时，将返回近一半的数据，效率非常低下，这种情况我们定义一个变量“基数”，比如一个表有10000行数据，一列只有100个取值，基数为1%。Oracle推荐，当一个列的基数小于1%时，适合建立位图索引。代码如下：

create bitmap index tiger_index_bitmap on tigerlearn(tiger)

tablespace Tiger_TS;

1

2

初始化参数CREATE_BITMAP_AREA_SIZE用于指定建立位图索引时分配的位图区大小，默认值为8MB，该参数值越大建立位图索引的速度就越快。修改该参数的代码如下：

alter system set create_bitmap_are_size = 8388608

scope = spfile

1

2

3.反向键索引

Oracle会自动为表的主键创建B树索引，通常主键会是一个递增的序列编号，如果使用默认的B树索引，当数据量巨大时会导致索引数据分布不均，叶子节点可能会偏向某一个方向，这时就需要另一个索引机制，反向键索引，它可以将添加的数据随机分散到索引中，它在顺序递增列上建立索引时非常有用。反向键索引的工作原理非常简单，在存储结构方面它与常规的B树索引相同。然而，如果用户使用序列在表中输入记录，则反向键索引首先指向每个列键值的字节，然后在反向后的新数据上进行索引。例如，如果用户输入的索引列为2011,则反向转换后为1102; 9527 反向转换后为7259。需要注意的是，刚才提及的两个序列编号是递增的，但是当进行反向键索引时却是非递增的。这意味着，如果将其添加到子叶节点中，则可能会在任意的子叶节点中进行。这样就使得新数据在值的范围上的分布通常比原来的有序数更均匀。代码如下：

create bitmap index tiger_index_reverse on tigerlearn(tiger) reverse

tablespace Tiger_TS;

1

2

4.基于函数的索引

基于函数的索引是常规的B树索引，但它存储的数据是由表中的数据应用函数后所得到的，而不是直接存储表中的数据本身。下面是基于lower函数建立的索引。代码如下：

create index tiger_index_fun on tigerlearn(lower(tiger));

1

创建这个函数索引后，如果在查询条件中包含相同的lower函数，则会提高查询的执行效率，如果没有创建函数索引，而是创建了普通的B树索引，在查询中使用lower函数会执行全表进行搜索，因为lower数据不在表中也不在索引中。

三.索引的其他操作

1.修改索引

对一个表建立索引后，对表的DML操作不断增加，索引中会产生越来越多的存储碎片，会拉低索引的工作效率，可以采用两种方式来清除碎片，重建索引或合并索引，重建索引就是简单的新建一个索引，然后把原来的索引删除。

合并索引代码：

alter index tiger_index coalesce deallocate unused;

1

重建索引可重新指定索引对象的表空间，代码如下：

alter index tiger_index rebulid

tablespace emp;--可以不写，默认原来的表空间

1

2

2.删除索引

删除索引的方式与索引的创建方式有关，如果使用create index语句显式地创建该索引，则可以用drop index语句删除该索引。

drop index tiger_index

1

如果索引是在定义约束时由Oracle自动创建的，则必须禁用或删除该约束本身。

3.显示索引信息

通过查询数据字典视图dba_indexes,可以显示数据库中的所有索引;

通过查询数据字典视图all_indexes,可以显示当前用户可访问地所有索引;

通过查询数据字典视图user_indexes,可以显示当前用户地索引信息。

通过查询数据字典视图dba_ind_columns,可以显示所有索引地表列信息；

通过查询数据字典视图all_ind_columns,可以显示当前用户可访问地所有索引地表列信息；

通过查询数据字典视图user_ind_columns,可以显示当前用户索引地表列信息。

建立索引时，Oracle会为索引分配相应地索引字段，索引数据被存储在索引段中，段名与索引名完全相同。

通过查询索引段视图user_segments，可以显示当前用户所拥有的段分配的信息。

通过查询数据字典视图dba_ind_expressions,可以显示数据库中所有函数索引所对应的函数或表达式。

通过查询数据字典user_ind_expression,可以显示当前用户的所有函数索引所对应的函数或表达式。

e.g.

select index_name,index_type

from dba_indexes

where owner = 'tiger'