Oracle 数据库索引

曾经终败给现在 2021-06-10 20:41 560阅读 0赞

## 前言 ##

索引可以加快数据库查询速度，减少数据库I/O，提高数据库性能。建立有效的索引，对于系统来说是非常重要的。一般来说，我们面临的数据搜索场景主要包括两类：表的每条记录都具有一个特征字段，与其他记录进行区分，一次查询即能定位到较少记录；表字段选择性差，基数（Cardinality）较小，一次查询可能会命中大量记录。

## B-tree索引 ##

B-tree（多路搜索树）类型索引是比较常见的。

### 适用场合： ###

*  选择性高的字段，如：时间戳字段、sequence字段、物理主键、业务主键。
 *  若字段的选择性很差，则考虑只有当该字段的值分布非常倾斜（这些字段的值相比其他值出现的几率小得多）的情况下才建索引。可以事先对表字段的取值进行一个统计分析，判断各种取值的情况的记录数分别是多少，从而判断是否值得为该字段建立索引。
 *  如果多个字段组合起来，能改善选择性，则可考虑建立复合索引。

### 以下情况不适合建b-tree索引 ###

*  基数小、选择性差的字段，如：员工表的 性别字段，不适合建立索引。
 *  经常会发生修改的字段，不要建立索引。因为这时候数据库不光要修改表的数据，还要更新索引。
 *  大字段，如：备注性质的文本信息，不适合建立索引。只能在小字段上建索引。
 *  小表，即数据量较少（一般<1000）的表，不适合建索引。在这种情况下，数据库采用全表扫描甚至可能比索引更快。

### 实务要点： ###

#### 注意平衡更新速度与查询速度 ####

通常来说，表的索引越多，其查询的速度也就越快。但是，表的更新速度则会降低。这主要是因为表的更新（如往表中插入一条记录）速度，反而随着索引的增加而增加。这主要是因为，在更新记录的同时需要更新相关的索引信息。为此，到底在表中创建多少索引合适，就需要在这个更新速度与查询速度之间取得一个均衡点。

#### 注意索引的定期清理 ####

数据库管理员，需要隔一段时间，如一年，对数据库的索引进行优化。该去掉的去掉，该调整的调整，以提高数据库的性能。

#### 关于复合索引 ####

*  复合索引的建立需要进行仔细分析，尽量考虑用单字段索引代替；
 *  正确选择复合索引中的主列字段，一般是选择性较好的字段，即确保在WHERE子句中使用到的字段是复合索引的领导字段；
 *  复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
 *  如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
 *  如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
 *  如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
 *  过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

### 注意OLAP与OLTP的差异 ###

对于一些数据仓库或者决策型数据库系统，其主要用来进行查询。相关的记录往往是在数据库初始化的时候导入。此时，设置的索引多一点，可以提高数据库的查询性能。同时因为记录不怎么更新，所以索引比较多的情况下，也不会影响到更新的速度。即使在起初的时候需要导入大量的数据，此时也可以先将索引禁用掉。等到数据导入完毕后，再启用索引。可以通过这种方式来减少索引对数据更新的影响。相反，如果那些表中经常需要更新记录，如一些事务型的应用系统，数据更新操作是家常便饭的事情。此时如果在一张表中建立过多的索引，则会影响到更新的速度。

## 位图索引 ##

位图索引适用于静态数据，即OLAP，数据仓库环境，不适于OLTP环境。如果一个列经常发生更新，则不适合。

### 主要特性 ###

#### 提高低基数字段查询性能 ####

如果对于基数较小的字段进行查询，因为无法精确命中少数记录，Oracle很可能会进行全表扫描，从而导致查询性能受到影响。而这正是位图索引可以解决的问题。

#### 并发性较差，不适合OLTP ####

位图索引往往涉及多个字段，当位图索引发生更新时，锁的最小粒度是一个位图段，可能牵涉到整张表的大部分记录；这与B-tree索引的更新时只锁定单条记录是不同的。因此，位图索引存在并发性问题。这也决定了位图索引更适合于不经常变化的静态数据。

### 原理 ###

对表的某几个字段的所有记录，纵横转换后，可表示为多维数组。因这些数值的基数都比较小，其中一个字段的所有值可视为一个向量。  
例如下图，人员信息表的性别字段，组成两个向量，男：101000……女：01011……  
![性别向量组][20151217182457201]  
婚姻状况字段，组成三个向量，分别为：已婚：11000……未婚：00101……离婚：00010……  
![婚姻状况向量组][20151217182555064]  
在查询“未婚男性”时，实际上是对男向量与未婚向量进行与运算。  
![未婚男性向量计算过程][20151217183423234]  
根据结果向量中为1的字段即可定位到相应的查询结果记录集。

## 索引相关DDL语句 ##

### 创建索引 ###

--创建一般索引
    create index IDX_TABLENAME_01 on emp(ename); --采用默认表空间
    create index IDX_TABLENAME_02 on emp(ename) tablespace idx_tbs; --指定表空间
    --创建基于函数的索引
    create index IDX_TABLENAME_03 on emp(UPPER(ename)) tablespace idx_tbs;
    --创建唯一索引
    create unique index IDX_TABLENAME_05 on emp(ename) tablespace idx_tbs;
    --创建位图索引
    create bitmap index IDX_TABLENAME_06 on emp(ename) tablespace idx_tbs;
    --创建主键索引
    alter table emp add constraint PK_TABLENAME_1 primary key (col_name) using index tablespace idx_tbs;

### 删除及重建索引 ###

drop index idx_name;
    alter index idx_name rebuild nologging;
    
    --分区索引删除及重建
    drop index idx_name partition partition_name;
    alter index idx_name rebuild partition partition_name nologging;

### 索引分区 ###

--全局索引，通过global字句指定索引的范围值：
    create index IDX_TABLENAME_X1 on emp(part_date) global partition by range(part_date)( partition p01_idx vlaues less than ('0106') partition p01_idx vlaues less than ('0111') ... partition p01_idx vlaues less than ('0401') );
    --局部索引，只适用于分区表，且依赖于分区表的分区键来建立。
    create index IDX_PARTY_TAB_1 on PARTY_TAB(part_dec) local( partition idx_1 tablespace tbs1, partition idx_2 tablespace tbs2 );

参考：[全局分区索引与局部分区索引][Link 1]

[20151217182457201]: /images/20210522/1b39caa0862a4704885c441d2b5ba427.png
[20151217182555064]: /images/20210522/5cc846c19cdd47e4acb8b3ce52f61204.png
[20151217183423234]: /images/20210522/333efff5bd464b10897ff2a3b2c648d6.png
[Link 1]: http://www.2cto.com/database/201401/275258.html