本节书摘来自华章出版社《高并发Oracle数据库系统的架构与设计》一书中的第2章,第2.1节,作者 侯松,更多章节内容可以访问云栖社区“华章计算机”公众号查看
2.1 索引扫描识别
如果把我们的数据库比喻成一座图书馆,那表作为数据的载体,则是一本一本的图书,而索引则是图书的目录。目录不仅让图书阅读和查找变得方便,更是图书成败的关键。
也许有人会说,我翻阅的是一本杂志,内容本就不多,我甚至不需要目录。是的,Oracle数据库也考虑到了这一点,对于数据量很小的表,我们可以不建索引,在查询时可以进行全表扫描(FULL TABLE SCAN),这种方式对于小表来说更适合。但是,如果我们手上是一本大字典呢?你甚至一个人都搬不动它,当然你也不必像看杂志一样每页都去翻阅,只需要查询到真正需要的内容即可。这个时候我们就需要目录了,甚至是多样类别的目录,比如:拼音目录、部首目录等,这样我们可以根据不同的需求选择不同的目录。同样,Oracle数据库也为不同的查询者提供了不同类别的索引,最常用的也是默认的索引就是接下来要说的B树索引。
B树索引的扫描就像目录的翻阅,高效的扫描方式才能带来快速的信息获取,本节将给读者介绍B树索引的几种常见扫描方式。
2.1.1 B树索引
在正式开始之前,我们先来介绍一下什么是B树索引。顾名思义,B树索引是一种树形结构的数据库对象,它由根节点、分支节点、叶节点三部分组成。如图2-1所示,根节点存储着指向分支节点的指针,分支节点则存储着指向叶节点的指针,索引的条目最终是存储在各个叶节点上的。根节点和分支节点一方面是作为索引条目快捷的数据路由,另一方面也是通过算法将索引条目分布均匀。
我们说过索引就像表的目录,那目录条目会有哪些内容呢,其中我们最关心的是什么呢?毋庸置疑,我们最关心的必是对应章节的开始页码。在B树索引的叶节点索引条目中也包含了这个页码——ROWID,它指明了对应数据实际存储的物理位置,也是我们进行索引扫描的目的。
2.1.2 全表扫描
说到索引扫描,不得不提的就是全表扫描(FULL TABLE SCAN)了,因为在一定程度上,引进索引扫描就是为了取代全表扫描。
全表扫描(FULL TABLE SCAN)就是在数据查询过程中,对整张表的全部低于高水位标记(High Water Mark,HWM)的数据块(Data Block)进行读取。如图2-2所示,可以说单次查询需要读取全表的数据,对于小表来说,这是无可厚非的,甚至可能是最优的方式。但如果是一张数据量较大的表,这将导致很多非必要的数据块读取,造成过多的I/O开销。
从另一方面来讲,判断一次索引扫描是否高效的标准就是将其与全表扫描进行比较,如果较之成本更低,那么索引扫描可以被视为高效的,反之则是需要优化的。
通过一个例子来简单对比一下吧。从返回结果来看,表alex_t00有10万行记录,不算一个小表了,执行计划的成本开销(COST)中,全表扫描COST=84,而索引扫描COST=44,全表扫描的执行效率是非常低的。两种扫描的效率对比如下所示:
SQL> select /*+full(alex_t00)*/ count(*) from alex_t00;
COUNT(*)
----------
100000
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 84 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| ALEX_T00 | 100K| 84 (2)| 00:00:02 |
-----------------------------------------------------------------------
SQL> select count(*) from alex_t00;
COUNT(*)
----------
100000
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| PK_ALEX_T00 | 100K| 44 (3)| 00:00:01 |
-----------------------------------------------------------------------------
全表扫描,对于小表来说是最优选择,对于没有合适的索引的大表来说,也是不错的选择。
2.1.3 ROWID扫描
我们已经了解到ROWID其实就是索引的“页码”,它是Oracle提供的伪列,一般说来每一行数据都对应一个固定且唯一的ROWID,在这一行数据存入数据库的时候就确定了。ROWID扫描查询示例如下所示:
SQL> select rowid from alex_t00 where id=1;
ROWID
------------------
AAA3YkAAEAAAAvlAAA
SQL> explain plan for select * from alex_t00
2 where rowid='AAA3YkAAEAAAAvlAAA';
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 1 |
| 1 | TABLE ACCESS BY USER ROWID| ALEX_T00 | 1 | 32 | 1 |
------------------------------------------------------------------------
从上面这个查询例子可以看到,ROWID是基于64位编码的18个字符显示,它记录了数据对象的编号、文件编号、块编号、行编号,即数据行存储的物理位置,如表2-1所示:
利用ROWID来查询记录,其实就是根据数据行实际存储的位置来获取数据。通过ROWID查询记录是查询速度最快的查询方法,比任何索引扫描方式都要快速。为什么这么说呢?我们说索引扫描实质上可以分解成两个动作:
索引结构扫描,获取待返回数据行的ROWID;
根据获取的ROWID扫描表,获取对应数据行,并返回。
ROWID的扫描方式其实就是索引扫描的第二个动作,换而言之,索引扫描的目标就是通过ROWID扫描的方式从表中获取查询数据行。
通过dbms_rowid这个包,可以直接得到具体的ROWID所包含的信息:
SQL> select dbms_rowid.rowid_object(rowid) object_id,
2 dbms_rowid.rowid_relative_fno(rowid) file_id,
3 dbms_rowid.rowid_block_number(rowid) block_id,
4 dbms_rowid.rowid_row_number(rowid) num
5 from alex_t00
6 where id = 1;
OBJECT_ID FILE_ID BLOCK_ID NUM
---------- ---------- ---------- -------------
226852 4 3045 0
ROWID扫描方式是查询取数最快的方式,索引检索的本质也是转换为ROWID扫描取数。
2.1.4 索引唯一扫描
从上面的介绍,我们可以了解到索引扫描的过程其实是扫描索引结构获取ROWID的过程。索引唯一扫描(INDEX UNIQUE SCAN)只能发生在唯一键索引(主键索引实质即为唯一键索引)上,通过唯一索引查找数值往往返回单个ROWID,如图2-3所示,从索引的根(root)节点到枝(branch)节点,再到叶(leaf)节点上存储着一个对应的ROWID,即对应的查询结果也只返回一行,这种存取方法称为“索引唯一扫描”。如果该唯一索引是由多个列组成的组合索引,则至少要有组合索引的前导列参与到该查询中,同样SQL语句只返回一行记录,这也属于索引唯一扫描。
下面通过一些实例来了解一下该扫描方式的特点。在正式开始之前,我们需要做一点准备工作:
步骤1 创建一下相关的表和主键索引:
SQL> create table alex_t01 (
2 id number,
3 a number,
4 b number,
5 c number,
6 name varchar2(100)
7 );
SQL> alter table alex_t01 add constraint pk_alex_t01
2 primary key (id) using index;
步骤2 初始化数据,顺序地插入10万行数据:
SQL> declare
2 begin
3 for i in 1 .. 100000 loop
4 insert into alex_t01
5 values
6 (i, mod(i, 2), mod(i, 20000), mod(i, 20000), 'alex');
7 end loop;
8 commit;
9 end;
10 /
步骤3 最重要的是收集一下表和主键索引的统计信息和直方图信息(默认开启直方图收集),在缺失统计信息和直方图的情况下,CBO优化器可能无法正确地计算SQL语句的执行成本,直接导致执行计划跑偏,影响性能:
SQL> exec dbms_stats.gather_table_stats('alex','alex_t01')
SQL> exec dbms_stats.gather_index_stats('alex','pk_alex_t01')
准备工作完成后,可以实际执行一下查询SQL语句,进行如下所示的验证。
SQL> select id, name from alex_t01 where id=400;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ALEX_T01 | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_ALEX_T01 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
我们看到查询筛选条件为id=400,是一个等值查询,返回唯一数据行,执行计划走的是索引唯一扫描方式。
如果这里不是等值查询呢?那么,执行计划将无法按索引唯一扫描方式。换而言之,有且仅当唯一键索引列上发生等值查询时,才会触发索引唯一扫描,返回单行数据。这种索引扫描方式也是最高效的索引扫描方式,常见于主键索引的应用场景。
我们也可以通过给SQL语句添加HINT关键字的方式,改变执行计划,强制SQL语句走索引扫描,如下所示:
SQL> select /*+ index(alex_t01 pk_alex_t01) */ id, name
2 from alex_t01 where id=400;
索引唯一扫描是最高效的索引扫描方式,其只对唯一键索引上的等值查询有效。
2.1.5 索引范围扫描
在索引的使用过程中,更多的情况是返回多个数据行。当使用一个索引存取多行数据时,这种索引扫描方式称为“索引范围扫描”(INDEX RANGE SCAN)。与索引唯一扫描不同,索引范围扫描可以发生在唯一键索引上,也可以发生在非唯一键索引上。
哪些情况会发生索引范围扫描呢?
在唯一索引列上使用了范围操作符(如:>、<、<>、>=、<=、between,即不等值查询);
对非唯一索引列上进行的查询。
先来看看第一种情况,在主键索引列上进行非等值查询,筛选条件为id<4,返回了3行数据,此时的执行计划走的不是索引唯一扫描了,而是索引范围扫描,如下例所示:
SQL> select id, name from alex_t01 where id<4;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ALEX_T01 | 1 | 10 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_ALEX_T01 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
再来测试一下第二种情况,为表alex_t01追加一个单列索引和一个组合索引,并收集相关统计信息和直方图:
SQL> create index idx_alex_t01_id_ab on alex_t01 (a, b);
SQL> create index idx_alex_t01_id_c on alex_t01 (c);
SQL> exec dbms_stats.gather_index_stats('alex','idx_alex_t01_id_ab')
SQL> exec dbms_stats.gather_index_stats('alex','idx_alex_t01_id_c')
在非唯一键索引idx_alex_t01_id_c的索引列c上进行查询,其执行计划走的是索引范围扫描。而在普通索引上的查询,不论是否等值查询,也不论返回的数据行数是多少,其执行计划均为索引范围扫描。索引范围扫描示例如下所示:
SQL> select * from alex_t01 where c=100;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 105 | 6| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ALEX_T01 | 5 | 105 | 6| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ALEX_T01_ID_C | 5 | | 1| 00:00:01 |
-------------------------------------------------------------------------------------------
我们也可以通过给SQL语句添加HINT关键字的方式,改变执行计划,强制SQL语句走索引范围扫描,如下所示:
SQL> select /*+ index_rs(alex_t01 pk_alex_t01) */ id, name
2 from alex_t01 where id<4;
当发生索引范围扫描的时候,对索引列有一个自动排序操作,默认情况下是正序(ASC)输出返回的结果集的,也就是INDEX RANGE SCAN ASC。对于本例来说,以下两句SQL语句是等效的:
SQL> select * from alex_t01 where c=100;
SQL> select * from alex_t01 where c=100 order by c;
如果在SQL语句中要求反序排序输出结果集呢?索引排序具体内容将在接下来的章节展开。
索引范围扫描是最常见的一种索引扫描方式,在做优化时,需要尽可能使用的一种方式。
2.1.6 索引全扫描
对于表来说,有全表扫描,同样对于索引来说,也是存在索引全扫描的。索引全扫描(INDEX FULL SCAN)与全表扫描是非常类似的,如图2-5所示,它将先扫描索引全部节点和条目,再选择对应数据进行排序输出。索引全扫描只在CBO模式下才有效。CBO根据统计数值得知进行索引全扫描比进行全表扫描更有效时,才进行索引全扫描,而且此时查询出的数据都必须从索引中可以直接得到。
一般来说哪些情况会使用到索引全扫描呢?
表和表进行排序合并联立(Sort-Merge Join)查询的时候,排序的列必须是存在于索引中的;
查询中有order by和group by子句的时候,子句中所有的列是必须存在于索引中的。
下面是一个简单索引全扫描的例子:
SQL> select * from alex_t01 order by id;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 2050K| 560 (2)| 00:00:07 |
| 1 | TABLE ACCESS BY INDEX ROWID| ALEX_T01 | 100K| 2050K| 560 (2)| 00:00:07 |
| 2 | INDEX FULL SCAN | PK_ALEX_T01 | 100K| | 191 (2)| 00:00:03 |
-------------------------------------------------------------------------------------------
我们也可以通过给SQL语句添加HINT关键字的方式,改变执行计划,强制SQL语句走索引全扫描,如下所示:
SQL> select /*+ index_fs(alex_t01 pk_alex_t01) */ *
2 from alex_t01 order by id;
与全表扫描相比,索引全扫描的优势在哪里呢?
全表扫描过程是不进行排序的,必须将数据全部取出后再进行排序输出,其扫描目标表HWM下所有数据块,包括没有必要的空块。
因为索引结构本身就是一个有序的结构,索引全扫描在遍历索引的同时就已经完成了排序操作,在输出结果的时候是不需要再排序的,再者其通过ROWID获取行数据,避免了空块的读取。
索引全扫描过程是单块读取,其不支持多块并行的读取,输出结果是有序排列的。
2.1.7 索引快速全扫描
索引快速全扫描(INDEX FAST FULL SCAN)是扫描索引中的所有数据块,与INDEX FULL SCAN很类似,最显著的区别就是它不对查询出的数据进行排序,即数据不是以排序顺序被返回。在这种存取方法中,可以使用多块读功能,也可以使用并行读,以便获得最大吞吐量并缩短执行时间。
看一看下面的例子,复合索引idx_alex_t01_id_ab的索引列为(a,b),查询的返回列a,b都包含在索引列上,这个时候的取数操作直接就能在索引上完成了,不需要再根据ROWID去表中取数了,而且没有排序的需求。这时执行计划走的就是INDEX FAST FULL SCAN的操作了。
SQL> select a, b from alex_t01 where b>600;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 97038 | 758K| 83 (4)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_ALEX_T01_ID_AB | 97038 | 758K| 83 (4)| 00:00:01 |
-------------------------------------------------------------------------------------------
当我们取count(*)的时候,同样是不关心顺序的,也不需要排序操作,该查询只需要统计索引叶节点上的索引条目数量就可返回结果了,INDEX FAST FULL SCAN是一个非常好的选择。在下面的执行计划示例中,我们可以看到,SORT AGGREGATE操作是没有意义的,因为排序行数只有1行。
SQL> select count(*) from alex_t01;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 44 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FAST FULL SCAN| PK_ALEX_T01 | 100K| 488K| 44 (3)| 00:00:01 |
-------------------------------------------------------------------------------------
我们也可以通过给SQL语句添加HINT关键字的方式,改变执行计划,强制SQL语句走索引快速全扫描,如下所示:
SQL> select /*+ index_ffs(alex_t01 idx_alex_t01_id_ab) */ a, b
2 from alex_t01 where b>600;
再来对比一下索引全扫描和索引快速全扫描,如表2-2所示:
2.1.8 索引跳跃扫描
索引跳跃扫描(INDEX SKIP SCAN)是Oracle 9i引进的一个新特性,其发生在复合索引上,如果SQL语句中WHERE子句只包含索引中的部分列,且这些列不是索引的第一列,就可能发生INDEX SKIP SCAN。如果在查询时,第一列没有被指定,就跳过它。
INDEX SKIP SCAN除了需要CBO,并且对表进行过分析外,还需要保证第一列的distinct值非常小。Oracle会对复合索引进行逻辑划分,分为多个子索引,可以理解为索引从逻辑上被划分为第一列distinct值的数量的子索引,每次对一个子索引进行扫描。
下面通过一个例子来分析一下,在表alex_t01上,有一个复合索引idx_alex_t01_id_ab,索引列为(a,b),查询一下该表A列的distinct值的数量为2,即只有“0”和“1”两个键值,是满足了先决条件的。
SQL> select distinct a from alex_t01;
A
-----------
1
0
再进行一次INDEX SKIP SCAN类型的查询,示例如下所示:
SQL> select a, b, name from alex_t01 where b=600;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 65 | 8 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ALEX_T01 | 5 | 65 | 8 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_ALEX_T01_ID_AB | 5 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
可以看到COST开销是非常小的。如图2-6所示,此时,我们可以理解成复合索引idx_alex_t01_id_ab(a,b)逻辑上被拆分成两个独立子索引idx_alex_t01_id_ab_(b)和idx_alex_t01_id_ab_(b),where子句中b=600的查询将分别对这两个子索引进行扫描。
这时,如果a列的distinct值很多,那么复合索引idx_alex_t01_id_ab拆分逻辑子索引的动作本身就有不小的开销,查询过程再逐个扫描子索引也会增加开销,相比之下,CBO优化器可能会更倾向于选择全表扫描。
我们也可以通过给SQL语句添加HINT关键字的方式,改变执行计划,强制SQL语句走索引跳跃扫描,如下所示:
SQL> select /*+ index_ss(alex_t01 idx_alex_t01_id_ab) */ a, b, name
2 from alex_t01 where b=600;
但是,换一个角度来思考,我们会在设计索引的时候设计一个前导列区分度极低的复合索引吗?一般情况下,我们是不会这么做的。这又意味着什么呢?这意味着在执行计划中,如果看到INDEX SKIP SCAN,其COST开销将会非常大的,反而成了我们需要优化的对象。
在复合索引设计中,尽可能选择区分度较大的列作为前导列。如果为了使用INDEX SKIP SCAN这个索引扫描方式而选择区分度极低的列作为前导列,就是本末倒置了。
2.1.9 索引组合扫描
如果一个查询语句中,WHERE子句包含两个筛选条件,这两个条件都有其单独的索引,我们是不是可以同时使用两个索引呢?答案是肯定的。我们可以通过两个独立的索引分别扫描,再组合起来。在Oracle早期的版本中,我们可以通过and_equal方式来实现。从Oracle 10g开始,and_equal方式已经被废弃,由index_combine方式取而代之。
索引组合(INDEX COMBINE)最早是出现在位图索引上的,从Oracle 9i开始,默认可以使用在B树索引上,这个特性是由隐藏参数_b_tree_bitmap_plans来控制的。Oracle将B树索引中获得的ROWID信息通过BITMAP CONVERSION FROM ROWIDS的步骤转换成位图进行匹配,完成后通过BITMAP CONVERSION TO ROWIDS再转换出ROWID获得数据或者回表获得数据。
通过一个例子来看一下吧。在开始之前,我们需要修改一下表alex_t01上的索引,我们需要删除掉组合索引idx_alex_t01_id_ab,为b列创建一个单列索引idx_alex_t01_id_b,并重新收集统计信息。SQL语句如下:
SQL> drop index idx_alex_t01_id_ab;
SQL> create index idx_alex_t01_id_b on alex_t01 (b);
SQL> exec dbms_stats.gather_index_stats('alex','idx_alex_t01_id_b')
此时,b列和c列都有了其独立的单列索引,且此两列区分度都较高。我们再来做一次基于b列和c列的组合查询试试:
SQL> select * from alex_t01 where b=600 and c=600;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID | ALEX_T01 | 1 | 21 | 2 (0)|
| 2 | BITMAP CONVERSION TO ROWIDS | | | | |
| 3 | BITMAP AND | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 5 | INDEX RANGE SCAN | IDX_ALEX_T01_ID_B | 5 | | 1 (0)|
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | |
|* 7 | INDEX RANGE SCAN | IDX_ALEX_T01_ID_C | 5 | | 1 (0)|
-------------------------------------------------------------------------------------------
如果你因为看到BITMAP CONVERSION的字样而感到担忧的话,那大可不必,这部分的COST基本可以忽略,这是一个典型的index_combine例子。
我们要是强制查询只走其中一个索引呢,情况会如何呢?看一个示例:
SQL> select /*+index(alex_t01,idx_alex_t01_id_b)*/ *
2 from alex_t01 where b=600 and c=600;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 6 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| ALEX_T01 | 1 | 21 | 6 (0)|
|* 2 | INDEX RANGE SCAN | IDX_ALEX_T01_ID_B | 5 | | 1 (0)|
--------------------------------------------------------------------------------------
SQL> select /*+index(alex_t01,idx_alex_t01_id_c)*/ *
2 from alex_t01 where b=600 and c=600;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 6 (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| ALEX_T01 | 1 | 21 | 6 (0)|
|* 2 | INDEX RANGE SCAN | IDX_ALEX_T01_ID_C | 5 | | 1 (0)|
--------------------------------------------------------------------------------------
从上例可以看到,不论是走b列的索引还是走c列的索引,其COST开销都不如index_combine方式更优。
换而言之,如果我们知道索引组合扫描的方式会更优,也可以通过给SQL语句添加HINT关键字的方式,改变执行计划,强制SQL语句走索引组合扫描,示例如下所示:
SQL> select /*+ index_combine(alex_t01 idx_alex_t01_id_b
2 idx_alex_t01_id_c) */ * from alex_t01 where b=600 and c=600;
2.1.10 索引联立扫描
2.1.9节说到,若一个查询语句中,WHERE子句包含两个都有单独的索引筛选条件,则我们可以用index_combine扫描的方式来进行优化,但是index_combine仍然是需要有回表取数的操作。如果我们查询返回的列都包含在该两个索引中,我们就可以不用回表取数了,直接通过两个索引的HASH JOIN来完成就可以了。这个时候需要用另一个索引相关的HINT关键字index_join。
通过下面的例子来看一下,CBO优化器更倾向于COST更低的index_combine扫描,强制执行计划走index_join扫描,COST较index_combine扫描要高一些,但是相对单一索引的使用来说,却是有优势的。
SQL> select /*+ index_join(alex_t01 idx_alex_t01_id_b
2 idx_alex_t01_id_c) */ b, c from alex_t01 where b=600 and c=600;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 3 (34)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 1 | 10 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_ALEX_T01_ID_B | 1 | 10 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| IDX_ALEX_T01_ID_C | 1 | 10 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
严格意义上讲,index_combine和index_join都不能算是一种独立的索引扫描方式,它们是对现有五种索引扫描方式的优化和补充,使其获得更好的性能优势。
INDEX COMBINE和INDEX JOIN扫描方式各自有其适用场景,合理的使用索引组合和索引联立会带来性能的大幅提升。
如果索引结构设计比较合理,则能在索引扫描过程中完成取数的操作,尽量在索引扫描中完成,避免回表取数的开销,这个技巧叫做索引覆盖应用(INDEX COVERING),它覆盖了查询的所有字段(select、 where、 order by、group by),用来提高查询的效率。
纵观各种索引扫描方式的介绍和分析,每种扫描方式都有其特点和适用场景,不能单纯地说哪种扫描方式更优。在优化的工作中,更不能简单地用某种扫描方式去替代另一种扫描方式,我们需要分析清楚具体的应用场景,根据业务需求选择合适的索引扫描方式。
如果统计信息和直方图收集得准确的话,CBO优化器会提供准确的COST开销估算,可以作为索引扫描方式选择的参考。在实际优化的工作中,我们往往不能获得足够准确的统计信息和直方图信息,就需要通过比较不同索引扫描方式下,SQL语句执行的响应时间来判断。