- 数据库高效优化：架构、规范与SQL技巧
- 马立和 高振娇 韩锋
- 2561字
- 2020-08-27 05:47:11
7.3 索引
索引可以说是Oracle数据库中除了表以外最重要的对象了。通过添加索引来提高查询性能，也是最为常见的一种优化手段。甚至很多非DBA人员认为，数据库优化就是加索引。这种观点虽然有些偏颇，但也说明了索引对于优化的重要意义。
Oracle数据库支持多种索引。下面针对几种常用的索引分别加以介绍。
1.B树索引
B树索引是Oracle数据库的默认索引，也是最为常见的一种索引。通常我们所说的索引都是特指B树索引（见图7-1）。那为什么使用B树索引可以调高访问速度呢？这就要从索引结构来说明了。
图7-1 B树索引
整个索引结构就是一个平衡树（Balance Tree），这也就是称为B树索引的原因。在整个树结构中，包含有3种节点，分别是根节点（Root）、分支节点（Branch）、叶子节点（Leaf）。有的简单索引只有根节点和叶子节点。在根节点或分支节点中，存在一组键值范围，当通过条件访问到这些节点时，根据键值范围路由到不同的分支节点或叶子节点。例如上面示例中，如果输入的条件是'AA'，那么首先查询根节点，在这个节点中有一组键值BC，它代表将键值范围分为3个区间，分别是X<B、B<X<C、C<X。因为输入的条件是'AA'，故属于第一个区间，相关数据会在对应的第一个分支节点上。在第一个分支节点（L1-1）应用同样的方法，可知数据在第一个叶子节点（L0-1）。对于叶子节点来说，保存的每组记录中，每条记录包含两部分信息：一是索引键值，二是对应的行地址（ROWID）。通过行地址，就可以很快定位到数据块中的记录了。
下面通过一个示例说明为什么通过索引访问会很快。
SQL> create table t1 as select * from dba_objects;
//表已创建
SQL> insert into t1 select * from t1;
//已创建 18870 行
SQL> /
//已创建 37740 行
SQL> /
//已创建 75480 行
SQL> /
//已创建 150960 行
SQL> /
//已创建 301920 行
SQL> commit;
//提交完成
SQL> select * from t1 where object_id=20;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 89 | 18423 | 2194 (1)| 00:00:27 |
|* 1 | TABLE ACCESS FULL| T1 | 89 | 18423 | 2194 (1)| 00:00:27 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
11251 consistent gets
0 physical reads
SQL> create index idx_object_id on t1(object_id);
//索引已创建
SQL> select * from t1 where object_id=20;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
| | 89 | 18423 | 2188 (1)| 00:00:27
| 1 | TABLE ACCESS BY INDEX ROWID
| T1 | 89 | 18423 | 2188 (1)| 00:00:27
|* 2 | INDEX RANGE SCAN
| IDX_OBJECT_ID | 2580 | | 3 (0)| 00:00:01
--------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
38 consistent gets
0 physical reads
/*
从前后执行对比来看，前者走了全表扫描，后者走了索引扫描。直观地对比统计信息的“consistent gets”一栏，前者需要1万多次一致性读，后者只要数十次一致性读，两者差异巨大。自然，执行时间也差异巨大
*/
2.位图索引
位图索引是另外一种较为常见的索引，虽然说是较为常见，但也仅限于个别场景，主要适用于分析型数据库中。其原理与B树索引完全不同。在Oracle的优化器中，个别场景下可以将两类索引相互转换。这个在后面的章节会有详细说明。
下面首先来看看位图索引的结构，示例如表7-1所示。
表7-1 位图索引结构
在上面的显示中，10.0.3=>文件号＋块号＋行号。从表7-1可见，位图索引是在指定的地址范围，若对应记录是某个键值，则对应值设置为1，否则设置为0。从上面结构可见，如果位图索引的不同值很少，则空间占用很少。换句话说，其存储密度很高。
下面通过一个示例说明位图索引的用法。
create table t1 as select * from dba_objects where rownum<=50000;
//表已创建
update t1 set status='NOVALID' where object_id=20;
//更新3条
update t1 set status=NULL where object_id=21;
//更新1条
commit;
//提交完成
alter table t1 add constraint pk_t1 primary key(object_id);
//索引已创建
create bitmap index idx_status on t1(status);
//索引已创建
select count(*) from t1;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT | | 50000 | 2 (0)| 00:00:01 |
| 3 | BITMAP INDEX FAST FULL SCAN
| IDX_STATUS | | | |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
5 consistent gets
/*
默认使用位图索引，并且走的是位图索引快速全扫描。即使位图索引字段有空值，由于位图索引保存空值，因此也没有问题。此外，这也要看位图索引字段值的基数，如果基数较低，则该位图索引较小；如果基数很大，则位图索引会很大。在基数很大的情况下，COUNT(*)会选择B树索引，而不会走位图索引扫描
*/
select /*+ index(t1 pk_t1) */ count(*) from t1;
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 106 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_T1 | 50000 | 106 (1)| 00:00:02 |
------------------------------------------------------------------
Statistics
----------------------------------------------------------
105 consistent gets
//强制使用主键索引(B树索引)，可看到一致性读大大增加。这也间接说明了位图索引的高密度存储特点
3.其他索引
上面我们谈到了最为常见的两种索引类型，下面再看看其他索引类型。从本质上来讲，它们还是B树索引或者位图索引。
（1）函数索引
函数索引就是将一个函数计算的结果存储在列中，而不是存储列数据本身，可以把基于函数的索引看成是一个虚拟列上的索引。总之，所谓函数索引也只不过是基于已加工的逻辑列所创建的索引而已。
SQL> create table t1 as select * from dba_objects;
//表已创建
SQL> create index idx_object_name on t1(object_name);
//索引已创建
SQL> select * from t1 where upper(object_name)='EMP';
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 621 | 74 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 3 | 621 | 74 (0)| 00:00:01 |
--------------------------------------------------------------------------
//虽然在object_name字段上建立了索引，但是由于使用了upper()函数，导致无法利用该索引
SQL> create index idx_object_name_upper on t1(upper(object_name));
//索引已创建
SQL> select * from t1 where upper(object_name)='EMP';
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 179 | 48867 | 35 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID
| T1 | 179 | 48867 | 35 (0)|
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_NAME_UPPER | 72 | | 1 (0)|
---------------------------------------------------------------------------------
//创建了单独的函数索引，此时的查询就可以利用索引
（2）虚拟列索引
这里要先谈一下虚拟列。虚拟列是在11g中新引入的一个技术，从字面上看，创建的列不是真正的物理保存，只是一个定义。而基于虚拟列创建的索引，就是虚拟列索引。在某种程度上，虚拟列索引和上面谈到的函数索引有些类似。
（3）虚拟索引
在11g中，Oracle可以通过NOSEGMENT子句命令创建一个永远不会使用且不会为其分配任何盘区的索引。如果想要创建一个很大的索引，但并不想给它分配空间，则要先确定优化器是否会选择使用该索引。如果确定了这个索引是有用的，可以删除该索引，然后使用不包含NOSEGMENT的语句重建它。
（4）不可见索引
不可见索引不是一种特殊的索引类型，而是使索引对优化器“不可见”，导致没有查询会使用它。这对于评估索引使用效果非常有帮助，特别是对某些第三方应用，无法修改代码，这个特性十分有用。下面通过一个示例说明。
SQL> create table t1 as select * from dba_objects;
//表已创建
SQL> create index idx_id on t1(object_id);
//索引已创建
SQL> select * from t1 where object_id=20;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 621 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID
| T1 | 3 | 621 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID | 73 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
SQL> alter index idx_id invisible;
//索引已更改
SQL> select * from t1 where object_id=20;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 621 | 74 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 3 | 621 | 74 (0)| 00:00:01 |
--------------------------------------------------------------------------
//将索引设置为不可见后，优化器将不考虑这个索引，因此选用了全表扫描方式
（5）压缩索引
Oracle中的索引键允许压缩存储索引键中前面重复的部分，并且是每个叶块而不是每个叶块中的每行存储重复的值。压缩和非压缩索引在使用上差别不大，但压缩索引能节省大量空间。利用压缩索引，块缓冲区缓存比以前能存放更多的索引条目，缓存命中率可能会上升，物理I/O应该会下降，但是要多占用一些CPU时间来处理索引，还会增加块竞争的可能性。
下面通过一个示例说明。
SQL> create table t as select * from all_objects; //表已创建 SQL> create table idx_stats as select ' ' what,a.* from index_stats a where 1=0; //表已创建 SQL> create index t_idx_0 on t(owner,object_type,object_name); //索引已创建 SQL> analyze index t_idx_0 validate structure; //索引已分析 SQL> insert into idx_stats select 'compress_0',a.* from index_stats a where a.name='T_IDX_0'; 已创建 1 行 SQL> drop index t_idx_0; //索引已删除 SQL> create index t_idx_1 on t(owner,object_type,object_name) compress 1; //索引已创建 SQL> analyze index t_idx_1 validate structure; //索引已分析 SQL> insert into idx_stats select 'compress_1',a.* from index_stats a where a.name='T_IDX_1'; //已创建 1 行 SQL> drop index t_idx_1; //索引已删除 SQL> create index t_idx_2 on t(owner,object_type,object_name) compress 2; //索引已创建 SQL> analyze index t_idx_2 validate structure; //索引已分析 SQL> insert into idx_stats select 'compress_2',a.* from index_stats a where a.name='T_IDX_2'; //已创建 1 行 SQL> select what,height,lf_blks,br_blks,btree_space,opt_cmpr_count,opt_cmpr_pctsave from idx_stats; WHAT HEIGHT LF_BLKS BR_BLKS BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE ---------- ------- -------- -------- ----------- -------------- ---------------- compress_0 2 109 1 880032 2 29 compress_1 2 94 1 759656 2 18 compress_2 2 76 1 615728 2 0 /* 从输出可见，对于不压缩、压缩一个字段(compress=1)、压缩两个字段(compress=2)，对应索引的叶子节点明显减少 */
（6）复合索引
当某个索引包含多个已索引列时，这个索引就称为复合索引。如果查询条件中包含多个列，往往可以应用到复合索引。下面通过一个示例说明。
SQL> create table t1 as select * from dba_objects;
//表已创建
SQL> create index idx_1 on t1(owner,object_id);
//索引已创建
SQL> select * from t1 where owner='SYS' and object_id=20;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 414 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID
| T1 | 2 | 414 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_1 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
//此时利用了复合索引
（7）反转索引
反转索引是一种特殊的B树索引。它将索引列中列值的每个字节的位置反转。例如“12345”，反转之后是“54321”。其最大特点就是对于原来相连比较紧密的值，强制使其分散到相距比较远的位置上。这样可以使数据更均匀地分布。但由于反转索引的特点，导致只有精准匹配查找才能使用反转索引。下面通过一个示例说明。
create table t1 as select rownum id from dba_objects; create index t1_idx on t1(id); alter index idx_ t1_idx rebuild reverse;