Oracle索引

阅读量：4457 次

发布时间：2019-06-08

本文共 7633 字，大约阅读时间需要 25 分钟。

### --索引介绍

索引对于Oracle学习来说，非常重要，在数据量巨大的状况下，使用恰到好处的索引，将会使得数据查询时间大大减少，于2017/12/25暂时对Oracle中的索引进行一个大致的了解。

索引的创建语法

索引的特点

索引的不足

比较适合建立索引的列的特点

不适合建立索引的列的特点

限制索引（建立了索引，但是无法使用）

查询索引

组合索引

Oracle rowid

选择性

群集因子

二元高度

快速全表扫描

跳跃式扫描

主键、外键和索引的区别？

	主键	外键	索引
定义：	唯一标识一条记录，不能有重复的，不允许为空	表的外键是另一表的主键, 外键可以有重复的, 可以是空值	该字段没有重复值，但可以有一个空值
作用：	用来保证数据完整性	用来和其他表建立联系用的	是提高查询排序的速度
个数：	主键只能有一个	一个表可以有多个外键	一个表可以有多个惟一索引

索引的创建语法

create or replace unique|bitmap index 
     
      .
      
        on 
       
        .
        
          (
         
          |
          
            asc|desc , 
           
            |
            
              asc|desc ...) tablespace 
             
               storage 
              
                logging|nologging compute statistics nocompress|compress
               
                 nosort|reverse partition|global partition

unique|bitmap : unique表示唯一值索引，bitmap表示位图索引，为空则默认为B-tree索引

column_name|expression asc|desc , ... :可以单列索引，也可以多列进行联合索引，当为

tablespace : 制定存放索引的表空间（当表和索引在不同的表空间的时候，效率更高）

storage : 可以设置表空间的存储参数

logging|nologging : 是否对索引产生redolog（对于大表来说，可以设置为nologging从而来减少空间占用，提高效率）

compute statistics : 设置为创建索引时，收集统计信息

nocompress|compressnn : 是否使用“键压缩”（使用键压缩可以删除一个键列中出现的重复值）

nosort|reverse : nosort表示与表中相同的顺序进行创建索引，reverse表示使用与表中相反的顺序进行创建索引

partition|nopartition|global partition : 可以在分区表上和未分区表上对创建的索引进行分区

索引的特点

大大加快检索数据的速度

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性

加速表与表之间的连接

查询语句汇总含有分组或者排序的语句时，速度更快

查询的过程中，使用索引，使用优化隐藏器，从而提高系统的性能

索引的不足

创建和维护索引，比较耗费时间，随着数据量的增大而增大

创建索引，占一定的物理空间（聚簇索引，占用空间会更大）

在对表进行增删改的时候，索引相应的也需要进行动态的更新

比较适合建立索引的列的特点

经常需要搜索的列上

主键，一般建立唯一性索引，保持数据的唯一性

外键，提高表与表之间连接的速度

需要排序的列上

where子句后边经常出现的字段

经常需要根据范围进行搜索的列上，比如日期

不适合建立索引的列的特点

很少进行搜索的列上

列取值比较少的列上

blob类型的列上

修改频率比较高的列上

限制索引（建立了索引，但是无法使用）

使用不等于<> 、 != ，（不等于操作符一定会进行全表扫描）

使用is null 、 is not null （只要索引中出现一个null，那么这个索引就报废了。所以在建立索引的时候，一定要将准备建立索引的列设置为not null）

使用函数（where子句中含有trunc()、add_months()之类）的时候，sql优化器会自动忽略掉索引

where子句中，进行了数据类型不匹配的比较，比如(where row_num = '1')的时候，生气了优化器会限制索引的使用

查询索引

dba_indexes

user_indexes

uesr_ind_columns

组合索引

索引中，包含不止一个列。

在Oracle9i之前，需要先使用前导索引，才能使用组合索引。

Oracle rowid

实体表中，每一行都有rowid,通过每一行的rowid,Oracle提供了访问单行数据的能力。

选择性

user_indexes中distinct_keys,选择性越高，那么索引返回的值就越少。

群集因子

user_indexes中的clustering factor 越接近 leaf block的值的话，说明表中的数据越有序。

二元高度

dba_indexes 的 Blevel列查看对应索引的二元高度，二元高度随着表的大小以及被索引的列中，值的范围的狭窄程度而变化。重建索引可以降低二元高度。

快速全表扫描

允许Oracle执行一个全局索引的扫描操作，快速全表扫描可以快速读取B-tree索引上的所有树叶块。

跳跃式扫描

create index TT_index on TT(teamid,areacode); select /*+ index(tt TT_index )*/ count(areacode) from tt;

Oracle 提供了大量索引选项。知道在给定条件下使用哪个选项对于一个程序的性能来说非常重要。一个错误的选择可能会引发死锁，并导致数据库性能急剧下降或进程终止。而如果做出正确的选择，则可以合理使用资源，使那些已经运行了几个小时甚至几天的进程在几分钟得以完成，极大的提高数据操作语句的运行效率。

　导读^{【2017-12-26】【22:35:36】}：

- B 树索引(默认类型)
- 位图索引
- HASH 索引
- 索引组织表索引
- 反转键(reverse key)索引
- 基于函数的索引
- 分区索引(本地和全局索引)
- 位图连接索引

一、B 树索引

　　B 树索引在 Oracle 中是一个通用索引。在创建索引时它就是默认的索引类型。B 树索引可以是一个列的(简单)索引，也可以是组合/复合(多个列)的索引。B 树索引最多可以包括 32 列。索引列的值都存储在索引中。因此，可以建立一个组合(复合)索引，这些索引可以直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了 I/O 量。

特点：

适合与大量的增、删、改(OLTP);

不能用包含 OR 操作符的查询;

适合高基数的列(唯一值多);

典型的树状结构;

每个结点都是数据块; 大多都是物理上一层、两层或三层不定，逻辑上三层;

叶子块数据是排序的，从左向右递增;

在分支块和根块中放的是索引的范围;

- 叶子节点块（leaf block）：直接指向表里的数据行。

2 - 分支节点块（branch block）：指向分支节点或者是叶子节点。

3 - 根节点块（root node）：也属于分支节点，只是最顶端的分支节点。

上面三个专业名词都很形象的表现了B树索引的特点，如下图

由树转成模型：

如上图这棵树共40根叶毛，我要找第27根叶毛，如果没有索引，就得直接从第一根叶毛开始找，有了B树索引，就可以很简单的找出来，以下是利用B树索引查找方法

1.先从树根判断，27在21后面，找到21对应的树枝地址是树枝2，

2.进入树枝2，再对比27还是在21后面，找到21对应的树叶地址是树枝3，

3.进入树叶3，对比27在26后面，找到26对应的叶毛地址，那么27就很快被找出来了。

索引其实更像一本书的目录大纲，大纲的标题就是节点块，页数就是节点块的地址。

二、位图索引

　　位图索引非常适合于决策支持系统(Decision Support System，DSS)和数据仓库，它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。尽管位图索引最多可达 30 个列，但通常它们都只用于少量的列。对于有较低基数的列需要使用位图索引，比如性别。在一个查询中合并多个位图索引后，可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。较大尺寸的块也会提高对位图索引的存储和读取性能。

特点：

适合与决策支持系统;

做 UPDATE 代价非常高;

非常适合 OR 操作符的查询;

基数比较少的时候才能建位图索引;

位图（BitMap）索引

　　前段时间听同事分享，偶尔讲起Oracle数据库的位图索引，顿时大感兴趣。说来惭愧，在这之前对位图索引一无所知，因此趁此机会写篇博文介绍下位图索引。

1. 案例

　　有张表名为table的表，由三列组成，分别是姓名、性别和婚姻状况，其中性别只有男和女两项，婚姻状况由已婚、未婚、离婚这三项，该表共有100w个记录。现在有这样的查询： select * from table where Gender=‘男’ and Marital=“未婚”;

姓名(Name)	性别(Gender)	婚姻状况(Marital)
张三	男	已婚
李四	女	已婚
王五	男	未婚
赵六	女	离婚
孙七	女	未婚
...	...	...

1）不使用索引

　　不使用索引时，数据库只能一行行扫描所有记录，然后判断该记录是否满足查询条件。

2）B树索引

　　对于性别，可取值的范围只有'男','女'，并且男和女可能各站该表的50%的数据，这时添加B树索引还是需要取出一半的数据，因此完全没有必要。相反，如果某个字段的取值范围很广，几乎没有重复，比如身份证号，此时使用B树索引较为合适。事实上，当取出的行数据占用表中大部分的数据时，即使添加了B树索引，数据库如oracle、mysql也不会使用B树索引，很有可能还是一行行全部扫描。

2. 位图索引出马

如果用户查询的列的基数非常的小，即只有的几个固定值，如性别、婚姻状况、行政区等等。要为这些基数值比较小的列建索引，就需要建立位图索引。

对于性别这个列，位图索引形成两个向量，男向量为10100...，向量的每一位表示该行是否是男，如果是则位1，否为0，同理，女向量位01011。

RowId	1	2	3	4	5	...
男	1	0	1	0	0
女	0	1	0	1	1

　　对于婚姻状况这一列，位图索引生成三个向量，已婚为11000...，未婚为00100...，离婚为00010...。

RowId	1	2	3	4	5	...
已婚	1	1	0	0	0
未婚	0	0	1	0	1
离婚	0	0	0	1	0

　　当我们使用查询语句“select * from table where Gender=‘男’ and Marital=“未婚”;”的时候首先取出男向量10100...，然后取出未婚向量00100...，将两个向量做and操作，这时生成新向量00100...，可以发现第三位为1，表示该表的第三行数据就是我们需要查询的结果。

RowId	1	2	3	4	5
男	1	0	1	0	0
and
未婚	0	0	1	0	1
结果	0	0	1	0	0

3.位图索引的适用条件

　　上面讲了，位图索引适合只有几个固定值的列，如性别、婚姻状况、行政区等等，而身份证号这种类型不适合用位图索引。

　　此外，位图索引适合静态数据，而不适合索引频繁更新的列。举个例子，有这样一个字段busy，记录各个机器的繁忙与否，当机器忙碌时，busy为1，当机器不忙碌时，busy为0。

　　这个时候有人会说使用位图索引，因为busy只有两个值。好，我们使用位图索引索引busy字段！假设用户A使用update更新某个机器的busy值，比如update table set table.busy=1 where rowid=100;，但还没有commit，而用户B也使用update更新另一个机器的busy值，update table set table.busy=1 where rowid=12; 这个时候用户B怎么也更新不了，需要等待用户A commit。

　　原因：用户A更新了某个机器的busy值为1，会导致所有busy为1的机器的位图向量发生改变，因此数据库会将busy＝1的所有行锁定，只有commit之后才解锁。

三、HASH 索引

　　使用 HASH 索引必须要使用 HASH 集群。建立一个集群或 HASH 集群的同时，也就定义了一个集群键。这个键告诉 Oracle 如何在集群上存储表。在存储数据时，所有与这个集群键相关的行都被存储在一个数据库块上。 HASH 索引在有限制条件(需要指定一个确定的值而不是一个值范围)的情况下非常有用。

特点：

HASH 索引可能是访问数据库中数据的最快方法，但它也有自身的缺点，低估了集群键的不同值的数字可能会造成集群的冲突；

如果不能为集群的未来增长分配好附加的空间，HASH 集群可能就不是最好的选择；

如果应用程序经常在集群表上进行全表扫描，HASH 集群可能也不是最好的选择；

通常，HASH 对于一些包含有序值的静态数据非常有效；

Hash索引

索引列会被存储在匹配到的hash bucket里面的表里，这个表里会有实际的数据行指针，再根据实际的数据行指针查找对应的数据行。

概括来说，要查找一行数据或者处理一个where子句，SQL Server引擎需要做下面几件事

1、根据where条件里面的参数生成合适的哈希函数

2、索引列进行匹配，匹配到对应hash bucket，找到对应hash bucket意味着也找到了对应的数据行指针（row pointer）

3、读取数据

哈希索引比起B树索引简单，因为它不需要遍历B树，所以访问速度会更快

Hash索引的缺点：

1、因为Hash索引比较的是经过Hash计算的值，所以只能进行等式比较，不能用于范围查询

2、由于哈希值是按照顺序排列的，但是哈希值映射的真正数据在哈希表中就不一定按照顺序排列，所以无法利用Hash索引来加速任何排序操作

3、不能用部分索引键来搜索，因为组合索引在计算哈希值的时候是一起计算的。

4、当哈希值大量重复且数据量非常大时，其检索效率并没有Btree索引高的。

四、索引组织表

　　索引组织表会把表的存储结构改成 B 树结构，以表的主键进行排序。这种特殊的表和其他类型的表一样，可以在表上执行所有的 DML 和 DDL 语句。由于表的特殊结构，ROWID 并没有被关联到表的行上。

　　对于一些涉及精确匹配和范围搜索的语句，索引组织表提供了一种基于键的快速数据访问机制。基于主键值的 UPDATE 和 DELETE 语句的性能也同样得以提高，这是因为行在物理上有序。由于键列的值在表和索引中都没有重复，存储所需要的空间也随之减少。

　　如果不会频繁地根据主键列查询数据，则需要在索引组织表中的其他列上创建二级索引。不会频繁根据主键查询表的应用程序不会了解到使用索引组织表的全部优点。对于总是通过对主键的精确匹配或范围扫描进行访问的表，就需要考虑使用索引组织表，可以在索引组织表上建立二级索引

五、反转键索引

　　当载入一些有序数据时，索引肯定会碰到与 I/O 相关的一些瓶颈。在数据载入期间，某部分索引和磁盘肯定会比其他部分使用频繁得多。为了解决这个问题，可以把索引表空间存放在能够把文件物理分割在多个磁盘上的磁盘体系结构上。

　　为了解决这个问题，Oracle 还提供了一种反转键索引的方法。如果数据以反转键索引存储，这些数据的值就会与原先存储的数值相反。这样，数据 1234、1235 和 1236 就被存储成 4321、5321 和 6321。结果就是索引会为每次新插入的行更新不同的索引块。

技巧:

如果您的磁盘容量有限，同时还要执行大量的有序载入，就可以使用反转键索引。

不可以将反转键索引与位图索引或索引组织表结合使用。因为不能对位图索引和索引组织表进行反转键处理。

六、基于函数的索引

　　可以在表中创建基于函数的索引。如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。例如，下面的查询就不能使用 JOB 列上的索引，除非它是基于函数的索引:

　　 1 select * from emp where UPPER(job) = 'MGR';

　　下面的查询使用 JOB 列上的索引，但是它将不会返回 JOB 列具有 Mgr 或 mgr 值的行：

　　1 select * from emp where job = 'MGR';

　　可以创建这样的索引，允许索引访问支持基于函数的列或数据。可以对列表达式 UPPER(job)创建索引，而不是直接在 JOB 列上建立索引，如:

　　 1 create index EMP$UPPER_JOB on emp(UPPER(job));

尽管基于函数的索引非常有用，但在建立它们之前必须先考虑下面一些问题:

能限制在这个列上使用的函数吗?如果能，能限制所有在这个列上执行的所有函数吗？

是否有足够应付额外索引的存储空间?

在每列上增加的索引数量会对针对该表执行的 DML 语句的性能带来何种影响?

　　基于函数的索引非常有用，但在实现时必须小心。在表上创建的索引越多，INSERT、UPDATE 和 DELETE 语句的执行就会花费越多的时间。注意:对于优化器所使用的基于函数的索引来说，必须把初始参数 QUERY_REWRITE _ ENABLED 设定为 TRUE。

　　示例:

1 select count(*) from sample where ratio(balance,limit) >.5;2 time: 20.1 minutes3 4 create index ratio_idx1 on sample (ratio(balance, limit)); 5 6 select count(*) from sample where ratio(balance,limit) >.5;7 time: 7 seconds