Oracle之索引

2022-03-21 00:29:20

索引是关系型数据库的一个重要的优化手段，可以极大地提高数据的查询效率。Oracle作为关系型数据库也不能免俗。

注：虽然索引专注于查询效率，但是索引也存在一定弊端。索引会在数据表文件之外专门建立一份字段的映射文件，它会随数据的增加而增加。另外，一张表不是越多索引越好，索引越多维护起来也会很麻烦，对一些数据表结构的操作如交换分区等也很让人头疼。

还有索引字段不能出现null值，出现null值，查询时不会经过索引的。

Oracle中的索引有如下几种：

Btree索引、位图索引、函数索引、反向索引、降序索引、interMedia全文索引等


索引

Btree索引

基础(默认)索引，最常见的索引。类似于二叉树结构(非二叉树)，通过rowid快速定位记录。Btree索引很适合于字段内容重复率相当低的情形。当查询目标数据占全表总数据的一小部分时，Btree提供的效率时高于全表检索的；但是当查询目标占全表总数据的10%，Btree索引的效率达到瓶颈。

Btree的结构中每个节点存放索引列值范围和子节点索引键值位置，最终叶子结点才是存放索引值。所以可以知道一点：Btree索引的高度是一次查询的最大查询次数，远低于全表检索。

对于范围查询，不需要分两次从根结点开始查，可以先定位第一个条件范围节点再在这个节点横向定位第二个条件范围。

位图索引

使用位图管理数据记录的关系。与Btree索引相反，适合于字段重复率高的情形，最好内容枚举。比如性别，只有男女两个选项。

位图索引基于位图，位图是一种键值形式(类似二维数组)，横向表示数据行，纵向表示有限的值选项，每行根据值圈定对应值选项为1(true)，其他选项为0(false)。这种形式的位图存放在Btree结构的叶子结点，查询相当快速便捷。而且，位图是以一种压缩格式保存，还不会占用太大空间。

Oracle之索引

而且面对查询索引列值，Oracle内部也会将位图中的信息转换为rowid获取值。

另外位图索引不应该用于频繁修改的字段，因为位图索引不支持行级锁定，所以当更改某条记录的索引字段值时，其他同等值的字段都将被锁定，除非commit，否则其他需要更改的用户操作就不能执行。

函数索引

对函数建立索引，当以该函数作为筛选条件时可以提高查询效率。因为函数具有计算的能力并且容易使用，可以不修改程序逻辑(逻辑可以编入到自定义函数中)就提高查询效率。

但是使用函数索引需要满足条件：

  1. 基于成本优化器(cost)，否则将被忽略。
  2. 必须要有query rewrite 和 global query rewrite 权限
  3. 设置系统参数：QUERY_REWRITE=TRUE; QUERY_REWRITE_INTEGRITY=TRUSTED。可以在init.ora文件中修改；也可以通过alter system/session set xxx=xxx来更改。

举例：

create index test.ind_fun on test.testindex(upper(a));
insert into testindex values(‘a‘,2);
commit;

select /*+ RULE*/* from test/testindex where upper(a) = ‘A‘; //临时使用规则优化器会发现查询未使用函数索引
A　　     B
-- ----------
a　　     2
Execution Plan
----------------------------------------------------------
   0　　SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF ‘TESTINDEX‘
--------------------------------------------------------------------

select * from test.testindex where upper(a) = ‘A‘;  //使用默认成本优化器会发现函数索引生效
A　　     B
-- ----------
a　　     2
Execution Plan
----------------------------------------------------------
   0　　SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF ‘TESTINDEX‘ (Cost=2 Card=
　　  1 Bytes=5)
   2    1     INDEX (RANGE SCAN) OF ‘IND_FUN‘ (NON-UNIQUE) (Cost=1 Car
　　    d=1)
--------------------------------------------------------------------

反向索引

将Btree索引中的字节反转，可以均匀分配索引条目，适用于并行服务器，可以有效减少索引叶的竞争。

想象一下，如果Btree索引列恰好有一组值递增的记录，则它们按范围分大概率会分到一棵子树下，这样当多个查询或修改同时操作对应的数据，可能会对这棵子树进行争抢。反向索引会将索引码反转将数据打散均匀到不同位置，减少争抢的可能性。

下面可以看到索引码反转后相邻值的索引码相差甚远

select ‘number‘,dump(1,16) from dual
union all select ‘number‘,dump(2,16) from dual
union all select ‘number‘,dump(3,16) from dual;

Oracle之索引

select ‘number‘,dump(reverse(‘1‘),16) from dual
union all select ‘number‘,dump(reverse(‘2‘),16) from dual
union all select ‘number‘,dump(reverse(‘3‘),16) from dual;
//
number Typ=2 Len=2: 2,c1
number Typ=2 Len=2: 3,c1
number Typ=2 Len=2: 4,c1

但是反向索引不能建立在已经建立其他索引的字段上，因为不会生效。这也是必然的，毕竟它是与Btree相反的。

降序索引

面向逆序查询的索引。一般面对逆序查询，数据查出来之后会经过一个排序的过程，如果使用降序索引，会跳过排序。

举例：

select * from test where a between 1 and 100 order by a desc,b asc;
Execution Plan
----------------------------------------------------------
   0    SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)
   1  0  SORT(ORDER BY)(Cost=2 Card=100 Bytes=400)
   2  1 INDEX (RANGE SCAN) OF ‘IND_BT‘ (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)
---------------------------------------------------------------

create index test.ind_desc on test.testrev(a desc,b asc);
commit;

select * from test where a between 1 and 100 order by a desc,b asc;
Execution Plan
----------------------------------------------------------
   0    SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=400)
1  0 INDEX (RANGE SCAN) OF ‘IND_DESC‘ (NON-UNIQUE) (Cost=2 Card=100 Bytes=400)
----------------------------------------------------------------

注：安装Oracle时要保证compatible参数为8.1.0及以上，否则创建索引时desc关键字会被忽略。

索引扫描

基于成本的优化器(cost)会根据统计数值的方式推断当索引扫描比全表扫描更有效时就采用索引扫描。且查询结果完全由索引得到。

Oracle中有4种索引扫描方式，主要有where的筛选条件选择

在实际执行语句前，可以通过执行计划(explain plan)查看该语句是否使用了索引扫描以及使用了哪种扫描和花费时间等。

远程登录工具(如plsql developer)一般会自带执行计划按钮，在命令行窗口需要自行开启。

索引唯一扫描(index unique scan)

一般预期返回一条记录的筛选条件会触发唯一扫描，包括一个字段(单列索引)和多个字段(组合索引)。

唯一扫描也是比较常用的一种索引扫描。

create index index_xxx on tableName(name,class,school);

select * from tableName where name=‘xxx‘ and class=‘xxx‘ and school=‘xxx‘;

注：组合索引想使用索引扫描必须保证条件包含左边字段。如组合索引(col1,col2,col3)，则只有条件 where col1=xxx where col1=xx and col2=xx where col1=xx and col2=xxx and col3=xx 这三种情况才能索引扫描生效。

对个单个字段，如果出现unique或primary key等保证唯一性的约束，也是可以使用系统的唯一扫描。

索引范围扫描(index range scan)

面向组合索引，但是预期结果是多行记录。比较典型的有通过 < ,> ,<> , between and等筛选条件。

另外在所有非唯一索引上也都会使用范围扫描。

索引全扫描(index full scan)

对应全表扫描，实际上就是从左到右挨个扫描索引树的每个叶子索引，出来的结果是有序的。

原理是从根结点先定位至索引树最左叶子(树遍历不难),然后由节点的双向链表依次向右扫描其他叶子。

但是这种扫描方式是基于成本优化器(CBO)，因为需要根据统计值比较决定是否使用全扫描还是全表扫描。

索引快速扫描(index fast full scan)

与全扫描类似，只是这种扫描是并行扫描索引块，目的是大吞吐量和短时间。因此不会照顾到查询结果是否有序。

全表扫描 vs 索引扫描

全表扫描就是一条一条访问每条记录，虽然Oracle采取一次读入多个数据块方式优化，但是对于大数据量来说效率仍然低下。

索引扫描是采用基于rowid方式访问数据，直接接触物理内存地址，效率很高。Oracle实现了数据内容与物理地址的联系，而索引就是实现快速访问rowid。

对于索引扫描范围唯一扫描和其他扫描，首先会通过唯一扫描刷掉一批，剩下再通过其他索引扫描。

PS：索引操作

创建索引

create index index_name on tableName(col/function/...);

空值不能被索引

一张表不必建立多个索引，否则适得必反

修改索引

alter index index_name rebuild storage(initial 1m next 512k);//重构存储
alter index index_name rebuild reverse;//(https://www.imooc.com/article/279505)
alter index index_name coalesce;//重构合并索引无用空间

oracle中修改索引的概念是重构索引以保证适应索引存储参数的增长和数据的增加和清除无用的空间

rebuild相当于truncate，经历了一个删除重新建立的过程。

删除索引

drop index index_name;

查看索引

Oracle中的系统表user_indexes和user_ind_columns存有当前用户下的表的索引信息

select * from user_indexes/user_ind_columns where table_name=‘表名大写‘

Oracle之索引

上一篇：Photoshop 神秘的图片中隐藏的图片


下一篇：photoshop CS3调整图片大小的技巧