专栏首页乐沙弥的世界INDEX FULL SCAN vs INDEX FAST FULL SCAN

INDEX FULL SCAN vs INDEX FAST FULL SCAN

     INDEX FULL SCAN 与 INDEX FAST FULL SCAN两个长相差不多,乃是一母同胞,因此既有其共性,也有其个性。两者来说其共性是不用扫描表而是通过索引就可以直接返回所需要的所有数据。这对提高查询性能而言,无疑是一个难得的数据访问方式之一,因为索引中存储的数据通常是远小于原始表的数据。下面具体来看看两者之间的异同。

一、何时INDEX FULL SCAN 或 INDEX FAST FULL SCAN    1、select 与where子句中出现的所有列必须存在索引    2、查询返回的数据行总数占据整个索引10%以上的比率。取决于db_file_multiblock_read_count值与并行度的值    3、满足像统计行数这样的一些特定的标准,如count(*)这样的操作。count(*)操作几乎总是使用INDEX FAST FULL SCAN    4、对于索引列上order by之类的操作几乎总是使用INDEX FULL SCAN

   注: 参数db_file_multiblock_read_count会在index fast full scan 像full table scan一样生效,因为整个索引都被访问,Oracle 此时允许出现多块读(multiblock_read)。db_file_multiblock_read_count与paralle仅仅对index fast full scan情形。其次paralle在新版Oracle中是否支持待证实。一旦上述几个条件满足,基于成本的优化器根据表和索引的统计信息来调用 index full scan 或者index fast full scan。对于index fast full scan可以通过使用提示index_ffs来实现。 二、何谓INDEX FULL SCAN 与 INDEX FAST FULL SCAN  

-->创建演示表t
scott@CNMMBO> create table t as select * from dba_objects where 1=2;

-->为表t填充数据
scott@CNMMBO> insert into t select * from dba_objects where object_id is not null;

scott@CNMMBO> commit;

--为表t的object_id列添加索引
scott@CNMMBO> create index i_t_object_id on t(object_id);

-->收集表t上的统计信息
scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);

scott@CNMMBO> set autot trace exp;

scott@CNMMBO> select object_id from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50422 |   246K|   166   (1)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T    | 50422 |   246K|   166   (1)| 00:00:02 |
--------------------------------------------------------------------------

--从上面的执行计划中可知,此时走了全表扫描。
--由于我们需要查询的列为object_id,因此理论上只需要读取索引就应该可以返回所有数据,而此时为什么是全表扫描呢?
--这是因为NULL值与索引的特性所决定的。即null值不会被存储到B树索引。因此应该为表 t 的列 object_id 添加 not null 约束。
有关null值与索引请参考
    NULL 值与索引(一) http://blog.csdn.net/robinson_0612/article/details/7437561
   NULL 值与索引(二) http://blog.csdn.net/robinson_0612/article/details/7438397

--为列object_id添加 not null约束
scott@CNMMBO> alter table t modify(object_id not null);

--添加约束后的执行计划
scott@CNMMBO> select object_id from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2036340805

--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               | 50422 |   246K|    26   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| I_T_OBJECT_ID | 50422 |   246K|    26   (0)| 00:00:01 |
--------------------------------------------------------------------------------------  

--从上面的执行计划可知,此时走的是索引快速全扫描,整个cost比全表扫描呈数量级下降

INDEX FAST FULL SCAN
       类似于full table scan,使用该方式当在高速缓存中没有找到所需的索引块时,则根据db_file_multiblock_read_count的值进行多块读操
    作。对于索引的分支结构只是简单的获取,然后扫描所有的叶结点。其结果是导致索引结构没有访问,获取的数据没有根据索引键的顺序排序。
    INDEX FAST FULL SCAN使用multiblock_read,故产生db file scattered reads 事件。
		
--对于上面的情形能否使用索引全扫描方式来实现呢?答案是肯定的,需要增加一个提示
scott@CNMMBO> select /*+ index(t i_t_object_id) */ object_id from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 431110666

----------------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               | 50422 |   246K|   113   (1)| 00:00:02 |
|   1 |  INDEX FULL SCAN | I_T_OBJECT_ID | 50422 |   246K|   113   (1)| 00:00:02 |
----------------------------------------------------------------------------------

INDEX FULL SCAN
       与INDEX FAST FULL SCAN所不同的是,INDEX FULL SCAN会完全按照索引存储的顺序依次访问整个索引树。当访问到叶结点之后,按照双向
    链表方式读取相连节点的值。换言之,对于索引上所有的数据是按照有序的方式来读取的。如果索引块没有在高速缓存中被找到时,则需要从数
    据文件中单块进行读取。对于需要读取大量数据的全索引扫描而言,这将使其变得低效。INDEX FULL SCAN使用single read,故产生
    db file sequential reads事件。新版的Oracle支持db file parallel reads方式。 

三、INDEX FULL SCAN 与 INDEX FAST FULL SCAN 两者的差异

--还是使用上面的查询,我们为原来的语句增加order by子句
scott@CNMMBO> set autot trace ;
scott@CNMMBO> select object_id from t order by object_id ;

50422 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 431110666

----------------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               | 50422 |   246K|   113   (1)| 00:00:02 |
|   1 |  INDEX FULL SCAN | I_T_OBJECT_ID | 50422 |   246K|   113   (1)| 00:00:02 |
----------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3467  consistent gets                   
          0  physical reads
          0  redo size
     918087  bytes sent via SQL*Net to client
      37463  bytes received via SQL*Net from client
       3363  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50422  rows processed
      
--当增加order by子句之后,我们发现此时未添加任何提示的情形下,CBO优化器选择了INDEX FULL SCAN方式进行扫描      
--观察上面的统计信息可知,consistent gets 为3467,sorts (memory)为0

--下面通过提示来使得走INDEX FAST FULL SCAN扫描方式
scott@CNMMBO> select /*+ index_ffs(t i_t_object_id) */ object_id from t order by object_id;

50422 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2527678987

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               | 50422 |   246K|       |   185   (3)| 00:00:03 |
|   1 |  SORT ORDER BY        |               | 50422 |   246K|  1208K|   185   (3)| 00:00:03 |
|   2 |   INDEX FAST FULL SCAN| I_T_OBJECT_ID | 50422 |   246K|       |    26   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        118  consistent gets
          0  physical reads
          0  redo size
     918087  bytes sent via SQL*Net to client
      37463  bytes received via SQL*Net from client
       3363  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      50422  rows processed

--执行计划中提示得以生效,即按照INDEX FAST FULL SCAN方式扫描
--注意执行计划中的第2步为SORT ORDER BY操作,而对于INDEX FULL SCAN操作则没有这样一部。此执行计划中多出一列TempSpc,值为1208K
--此时的consistent gets为118,较3467呈数量级下降,其次可以看到sorts (memory)的值为1,而上一步的sorts (memory)的值为0。

--下面使用提示使其按全表扫描方式来观察其统计信息
scott@CNMMBO> select /*+ full(t) */ object_id from t order by object_id;

50422 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 961378228

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 50422 |   246K|       |   324   (2)| 00:00:04 |
|   1 |  SORT ORDER BY     |      | 50422 |   246K|  1208K|   324   (2)| 00:00:04 |
|   2 |   TABLE ACCESS FULL| T    | 50422 |   246K|       |   166   (1)| 00:00:02 |
-----------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        726  consistent gets
          0  physical reads
          0  redo size
     918087  bytes sent via SQL*Net to client
      37463  bytes received via SQL*Net from client
       3363  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      50422  rows processed

--执行计划中按全表方式读取数据
--全表扫描方式等同于第二步的INDEX FAST FULL SCAN,在执行计划中的第二步多出了SORT ORDER BY操作。以及列TempSpc,值为1208K
--此次全表扫描的consistent gets为726,高于INDEX FAST FULL SCAN的consistent gets,低于INDEX FULL SCAN的consistent gets

--下面是使用降序的情形
scott@CNMMBO> set autot trace exp;
scott@CNMMBO> select object_id from t order by 1 desc;

Execution Plan
----------------------------------------------------------
Plan hash value: 2808014233

--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               | 50422 |   246K|   113   (1)| 00:00:02 |
|   1 |  INDEX FULL SCAN DESCENDING| I_T_OBJECT_ID | 50422 |   246K|   113   (1)| 00:00:02 |
--------------------------------------------------------------------------------------------

--从上面的执行计划中可以看出,只要是涉及到排序操作,Oracle会毫不犹豫地选择INDEX FULL SCAN,因为INDEX FULL SCAN方式扫描一定是
--按创建索引是的方式来排序的。当order by使用降序时,可以看到操作1种相应的为降序操作INDEX FULL SCAN DESCENDING

四、聚合操作count(*)时的INDEX FULL SCAN 与 INDEX FAST FULL SCAN

scott@CNMMBO> set autot trace exp;
scott@CNMMBO> select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 3095383276

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    26   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| I_T_OBJECT_ID | 50422 |    26   (0)| 00:00:01 |
-------------------------------------------------------------------------------

scott@CNMMBO> select /*+ index(t i_t_object_id) */ count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 3079973526

--------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |     1 |   113   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE  |               |     1 |            |          |
|   2 |   INDEX FULL SCAN| I_T_OBJECT_ID | 50422 |   113   (1)| 00:00:02 |
--------------------------------------------------------------------------
--> Author : Robinson Cheng  --> Blog : http://blog.csdn.net/robinson_0612
使用count及count(*)的注意事项
	如果是基于可以为 null 值列进行count,则该查询优化器会选择包含该列的任意索引
	如果是基于not null值列进行count,或count(*),则至少包含一个非null列且最小的索引会被原则,因为null值不会被B树索引存储。

--附寻找INDEX FULL SCAN的sql语句
SELECT p.sql_id,sql_text
FROM   v$sqlarea t, v$sql_plan p
WHERE  t.hash_value = p.hash_value AND p.operation = 'INDEX' AND p.options = 'FULL SCAN'
and p.object_owner not in('SYS','SYSTEM');

五、位图索引时的INDEX FULL SCAN 与 INDEX FAST FULL SCAN       在使用位图索引时,index full SCAN 与 index fast full与使用B树索引有相同的表现。需要注意的是位图索引存储null值,在很大程度 上对索引进行压缩。其次位图索引不支持降序扫描方式。此处不再演示位图索引的index full scan 与 index fast full。

六、总结    1、当select和where中出现的列都存在索引是发生index full scan与index fast full scan的前提    2、查询返回的数据行总数占据整个索引10%以上的比率    3、基于上述前提count(*)操作几乎总是选择index fast full scan,而索引列上的order by子句几乎总是选择index full scan    4、index fast full scan使用多块读的方式读取索引块,产生db file scattered reads 事件,读取时高效,但为无序读取    5、index full scan使用单块读方式有序读取索引块,产生db file sequential reads事件,当采用该方式读取大量索引全扫描,效率低下    6、绝大多数情况下,index fast full scan性能优于index full scan,但前者在有order by时,一定会存在对读取的块重新排序的过程    7、index fast full scan通过牺牲内存与临时表空间换取性能,因此在内存不足或饱和状态应进行合理权衡

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL实例恢复

    版权声明:本文为博主原创文章,欢迎扩散,扩散请务必注明出处。 https://blog.csdn.net/robinson_0612/art...

    Leshami
  • SQL*Plus break与compute的简单用法

       在SQL*Plus提示符下输出求和报表,我们可以借助break与compute两个命令来实现。这个两个命令简单易用,可满足日常需求,其实质也相当于在编写S...

    Leshami
  • dbms_xplan之display_cursor函数的使用

            DBMS_XPLAN包中display_cursor函数不同于display函数,display_curso...

    Leshami
  • SQL Tuning 基础概述10 - 体会索引的常见执行计划

    在《SQL Tuning 基础概述05 - Oracle 索引类型及介绍》的1.5小节,提到了几种"索引的常见执行计划":

    Alfred Zhao
  • SQL优化

    普通索引: 即针对数据库表创建索引; 唯一索引: 与普通索引类似,不同的就是:MySQL数据库索引列的值必须唯一,但允许有空值; 主键索引: 它是一种特殊的...

    葆宁
  • 数据库的索引和锁到底是怎么回事

    所以说,如果我们写 select*fromuserwhereusername='Java3y'这样没有进行任何优化的sql语句,默认会这样做:

    黄泽杰
  • gulp + gulp-better-rollup + rollup 构建 ES6 开发环境

    关于 Gulp 就不过多啰嗦了。常用的 js 模块打包工具主要有 webpack、rollup 和 browserify 三个,Gulp 构建 ES6 开发环境...

    用户6167509
  • 每天一道剑指offer-牛客网二进制中1的个数

    乔戈里
  • 【技术分享】如何使用腾讯智能钛机器学习平台玩转超大数据集比赛

    本文作者为腾讯智能钛机器学习平台的资深用户,TI平台陪着他一起参加过多次国内外的算法比赛,大多都取得了不错的成绩。本次邀请他分享如何使用平台去参加超大数据集比赛...

    腾讯智能钛AI开发者
  • Java面经——数据库

    所有java面经系列已同步到我的github,欢迎访问https://github.com/tzfun/Java-Interview-experience,记得...

    beifengtz

扫码关注云+社区

领取腾讯云代金券