《高并发Oracle数据库系统的架构与设计》一2.1 索引扫描识别

简介:

本节书摘来自华章出版社《高并发Oracle数据库系统的架构与设计》一书中的第2章,第2.1节,作者 侯松,更多章节内容可以访问云栖社区“华章计算机”公众号查看

2.1 索引扫描识别

如果把我们的数据库比喻成一座图书馆,那表作为数据的载体,则是一本一本的图书,而索引则是图书的目录。目录不仅让图书阅读和查找变得方便,更是图书成败的关键。
也许有人会说,我翻阅的是一本杂志,内容本就不多,我甚至不需要目录。是的,Oracle数据库也考虑到了这一点,对于数据量很小的表,我们可以不建索引,在查询时可以进行全表扫描(FULL TABLE SCAN),这种方式对于小表来说更适合。但是,如果我们手上是一本大字典呢?你甚至一个人都搬不动它,当然你也不必像看杂志一样每页都去翻阅,只需要查询到真正需要的内容即可。这个时候我们就需要目录了,甚至是多样类别的目录,比如:拼音目录、部首目录等,这样我们可以根据不同的需求选择不同的目录。同样,Oracle数据库也为不同的查询者提供了不同类别的索引,最常用的也是默认的索引就是接下来要说的B树索引。
B树索引的扫描就像目录的翻阅,高效的扫描方式才能带来快速的信息获取,本节将给读者介绍B树索引的几种常见扫描方式。

2.1.1 B树索引

在正式开始之前,我们先来介绍一下什么是B树索引。顾名思义,B树索引是一种树形结构的数据库对象,它由根节点、分支节点、叶节点三部分组成。如图2-1所示,根节点存储着指向分支节点的指针,分支节点则存储着指向叶节点的指针,索引的条目最终是存储在各个叶节点上的。根节点和分支节点一方面是作为索引条目快捷的数据路由,另一方面也是通过算法将索引条目分布均匀。

image

我们说过索引就像表的目录,那目录条目会有哪些内容呢,其中我们最关心的是什么呢?毋庸置疑,我们最关心的必是对应章节的开始页码。在B树索引的叶节点索引条目中也包含了这个页码——ROWID,它指明了对应数据实际存储的物理位置,也是我们进行索引扫描的目的。

2.1.2 全表扫描

说到索引扫描,不得不提的就是全表扫描(FULL TABLE SCAN)了,因为在一定程度上,引进索引扫描就是为了取代全表扫描。
全表扫描(FULL TABLE SCAN)就是在数据查询过程中,对整张表的全部低于高水位标记(High Water Mark,HWM)的数据块(Data Block)进行读取。如图2-2所示,可以说单次查询需要读取全表的数据,对于小表来说,这是无可厚非的,甚至可能是最优的方式。但如果是一张数据量较大的表,这将导致很多非必要的数据块读取,造成过多的I/O开销。
从另一方面来讲,判断一次索引扫描是否高效的标准就是将其与全表扫描进行比较,如果较之成本更低,那么索引扫描可以被视为高效的,反之则是需要优化的。
image

通过一个例子来简单对比一下吧。从返回结果来看,表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所示:
image

利用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语句只返回一行记录,这也属于索引唯一扫描。
image

下面通过一些实例来了解一下该扫描方式的特点。在正式开始之前,我们需要做一点准备工作:
步骤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,即不等值查询);
对非唯一索引列上进行的查询。

image

先来看看第一种情况,在主键索引列上进行非等值查询,筛选条件为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子句的时候,子句中所有的列是必须存在于索引中的。

image

下面是一个简单索引全扫描的例子:

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所示:
image

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;

image

但是,换一个角度来思考,我们会在设计索引的时候设计一个前导列区分度极低的复合索引吗?一般情况下,我们是不会这么做的。这又意味着什么呢?这意味着在执行计划中,如果看到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语句执行的响应时间来判断。

相关文章
|
1月前
|
数据库 索引
深入探索数据库索引技术:回表与索引下推解析
【10月更文挑战第15天】在数据库查询优化的领域中,回表和索引下推是两个核心概念,它们对于提高查询性能至关重要。本文将详细解释这两个术语,并探讨它们在数据库操作中的作用和影响。
50 3
|
1月前
|
数据库 索引
深入理解数据库索引技术:回表与索引下推详解
【10月更文挑战第23天】 在数据库查询性能优化中,索引的使用是提升查询效率的关键。然而,并非所有的索引都能直接加速查询。本文将深入探讨两个重要的数据库索引技术:回表和索引下推,解释它们的概念、工作原理以及对性能的影响。
59 3
|
1月前
|
缓存 关系型数据库 MySQL
高并发架构系列:数据库主从同步的 3 种方案
本文详解高并发场景下数据库主从同步的三种解决方案:数据主从同步、数据库半同步复制、数据库中间件同步和缓存记录写key同步,旨在帮助解决数据一致性问题。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
高并发架构系列:数据库主从同步的 3 种方案
|
26天前
|
存储 Oracle NoSQL
【赵渝强老师】Oracle的体系架构
Oracle数据库的核心在于其体系架构,主要包括数据库与实例、存储结构、进程结构和内存结构。数据库由物理文件组成,实例则是内存和进程的组合。存储结构分为逻辑和物理两部分,进程结构涉及多个后台进程如SMON、PMON、DBWn等,内存结构则包含SGA和PGA。掌握这些知识有助于更好地管理和优化Oracle数据库。
|
24天前
|
数据库 索引
数据库索引
数据库索引 1、索引:建立在表一列或多列的辅助对象,目的是加快访问表的数据。 2、索引的优点: (1)、创建唯一性索引,可以确保数据的唯一性; (2)、大大加快数据检索速度; (3)、加速表与表之间的连接; (4)、在查询过程中,使用优化隐藏器,提高系统性能。 3、索引的缺点: (1)、创建和维护索引需要耗费时间,随数据量增加而增加; (2)、索引占用物理空间; (3)、对表的数据进行增删改时,索引需要动态维护,降低了数据的维护速度。
33 2
|
27天前
|
缓存 负载均衡 网络协议
高并发架构的CDN知识介绍
本文详细介绍了网络请求过程,特别是大型网站架构中DNS和CDN的作用。通过一张常用架构图,文章解释了从客户端请求到服务器响应的全过程,包括DNS解析、负载均衡、CDN加速等关键环节,帮助读者深入了解高并发架构的设计原理和优化方法。
72 1
|
2月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
69 3
Mysql(4)—数据库索引
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
191 1
|
29天前
|
存储 关系型数据库 数据库
Postgres数据库BRIN索引介绍
BRIN索引是PostgreSQL提供的一种高效、轻量级的索引类型,特别适用于大规模、顺序数据的范围查询。通过存储数据块的摘要信息,BRIN索引在降低存储和维护成本的同时,提供了良好的查询性能。然而,其适用场景有限,不适合随机数据分布或频繁更新的场景。在选择索引类型时,需根据数据特性和查询需求进行权衡。希望本文对你理解和使用PostgreSQL的BRIN索引有所帮助。
35 0
|
2月前
|
缓存 NoSQL Java
秒杀圣经:10Wqps高并发秒杀,16大架构杀招,帮你秒变架构师 (1)
高并发下,如何设计秒杀系统?这是一个高频面试题。40岁老架构师尼恩的读者交流群中,近期有小伙伴在面试Shopee时遇到了这个问题,未能很好地回答,导致面试失败。为此,尼恩进行了系统化、体系化的梳理,帮助大家提升“技术肌肉”,让面试官刮目相看。秒杀系统设计涉及16个架构要点,涵盖业务架构、流量架构、异步架构、分层架构、缓存架构、库存扣减、MQ异步处理、限流、熔断、降级、存储架构等多个方面。掌握这些要点,可以有效应对高并发场景下的秒杀系统设计挑战。
秒杀圣经:10Wqps高并发秒杀,16大架构杀招,帮你秒变架构师 (1)

推荐镜像

更多