轻松掌握Oracle索引（二）-伙伴云

网友投稿 739 2022-05-29

4 数据的访问路径

条条大路通罗马，你可以选择最近的路线，也可以选择一条较远的路线，甚至你也可以绕地球一周。执行SQL其实是同样的道理，完成同样的查询有很多方式，有的方式可能需要访问10个块，而有的却可能需要访问10万个块，执行时间可以相差数千倍。

如果你把数据库当成个黑盒子，不去搞懂它的话，真的可能干出“绕地球一周”的傻事。也许你觉得不可能有这么大的差距，同一个语句要做的事应该是一样多的啊。这就是SQL和普通编程语言的差异了：SQL不是面向过程的语言，而是面向结果的。你实际上只告诉了数据库要什么数据，而没有说明具体的执行步骤，优化器会选择它认为最优化的方式来执行，这就是所谓“执行计划”。可惜优化器不是总能做出正确的选择的，如果没有创建合适的索引，优化器更是无能为力了。

执行计划有三个方面需要考虑：访问路径、连接方式和连接顺序。访问路径就是通过什么路径去访问数据，包括全表扫描和各种索引扫描。对于单表查询不用考虑连接方式和连接顺序，暂时我们也不讨论多表连接的情况，打好基础最重要。

本章会介绍各种索引扫描方式，如何选择索引。学习了这部分，对于单表查询，相信就能做出正确的选择了。

4.1 全表扫描（TABLE ACCESS FULL）

4.1.1 什么是全表扫描

在数据库中，对无索引的表进行查询一般称为全表扫描。全表扫描是数据库服务器用来搜寻表的每一条记录的过程，直到所有符合给定条件的记录返回为止。全表扫描是最常见的访问路径，所以在介绍索引扫描前先介绍下全表扫描。

4.1.2 全表扫描实例

下面是个全表扫描的例子。

SQL> set linesize 1000 --为了展示的好看些

SQL> set autotrace on --使用autotrace的命令

SQL> select /*+full(a)*/ count(*) from t_userserviceinfo a;

COUNT(*)

----------

2170606

--这部分是执行计划

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 9370 (1)| 00:01:53 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| T_USERSERVICEINFO | 2170K| 9370 (1)| 00:01:53 |

--------------------------------------------------------------------------------

--这部分是性能统计数据

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

53602 consistent gets --说明需要读53602个数据块

53598 physical reads --其中53598是物理读

0 redo size

529 bytes sent via SQL*Net to client

520 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

4.1.3 AUTOTRACE工具

AUTOTRACE是Oracle自带的工具，它不仅可以展示执行计划，还可以告诉我们SQL执行过程中访问了多少数据块。

我们可以看到这次查询读取了53602个数据块，其中53598是物理读，“1 rows processed”表示最后返回了一条数据。

如果把该语句再执行一次，你可以看到consistent gets几乎没变化，而physical reads很可能变成了0，这是因为数据已经在缓存中了，不产生物理读了。

如果把该表的所有数据全部delete再执行相同的查询，你会发现consistent gets几乎没变化，这是因为delete不会降低表的高水位，即使数据块是空块，全表扫描时也需要读取该数据块。

4.1.4 全表扫描执行计划

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 9370 (1)| 00:01:53 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | TABLE ACCESS FULL| T_USERSERVICEINFO | 2170K| 9370 (1)| 00:01:53 |

--------------------------------------------------------------------------------

上面是最简单的一个执行计划，它只有三步。它的执行顺序是从内到外的，即2->1->0。TABLE ACCESS FULL表示这一步是对表T_USERSERVICEINFO做全表扫描；SORT AGGREGATE表示对上一步结果执行count，注意这里的SORT不表示排序，而是集函数的意思；SELECT STATEMENT表示这是一个查询语句。

4.1.5 全表扫描提示

/*+full(a)*/是全表扫描的提示，表示告诉优化器对a表使用全表扫描的访问路径，其中a是表的别名。

提示和注释很像，但是它有固定的格式并且要放在合适的位置。提示不一定能生效，当提示错误时优化器会忽略提示而不会报错。Oracle不建议使用提示来固定执行计划，因为如果指定的提示不合适，还不如让优化器来选择呢。当你确定要选择全表扫描时可以使用提示固定执行计划。

重要知识点

执行计划有三个方面需要考虑：访问路径、连接方式和连接顺序。访问路径就是通过什么路径去访问数据，包括全表扫描和各种索引扫描。

AUTOTRACE是Oracle自带的工具，它不仅可以展示执行计划，还可以告诉我们SQL执行过程中访问了多少数据块。

/*+full(a)*/是全表扫描的提示，表示告诉优化器对a表使用全表扫描的访问路径，其中a是表的别名。

4.2 索引唯一扫描（INDEX UNIQUE SCAN）

4.2.1 什么是索引唯一扫描

当根据主键或唯一索引查询数据时，可以使用索引唯一扫描。这种查询的代价非常小，因为它最多从索引树中查出一条数据。索引唯一扫描在访问索引时最多访问的数据块数就是索引树的高度，而索引数一般不会超过4层，所以它的查询速度很快。

4.2.2 只访问索引而不访问表

当索引中包含了要查询的所有数据时，则只需要访问索引而不用再访问表，下面是一个索引唯一扫描的例子，其中，PK_T_USERSERVICEINFO是创建在列phonenumber和servicetype上的主键。

从统计结果可以看到，实际只需要读取3个数据块（3 consistent gets），还记得前面这个索引树的高度吗，正好就是3，说明这次查询只需要从索引根节点访问到叶子节点。

从执行计划看，首先执行索引唯一扫描（INDEX UNIQUE SCAN），然后执行count操作（SORT AGGREGATE），并没有访问表的操作。

为什么这个操作不要访问表？

因为要查询count（*），而唯一索引的列是不允许为空的，所以在索引中count和在表中count是一样的。

SQL> select /*+index(a PK_T_USERSERVICEINFO)*/count(*) from t_userserviceinfo a where phonenumber = '674977819' and servicetype = 1;

COUNT(*)

----------

Execution Plan

----------------------------------------------------------

Plan hash value: 2484190785

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

轻松掌握Oracle索引（二）

|* 2 | INDEX UNIQUE SCAN| PK_T_USERSERVICEINFO | 1 | 13 | 2 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("PHONENUMBER"='674977819' AND "SERVICETYPE"=1)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

526 bytes sent via SQL*Net to client

520 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

4.2.3 需要访问表

下面再来看一个需要访问表的例子，将上面的SQL中的count(*)修改为另一个列paykind。我们可以执行计划发生了变化：由于不需要count，所以没有了SORT AGGREGATE，取而代之的是TABLE ACCESS BY INDEX ROWID，这一步的含义是根据索引中的rowid访问表数据。

看下最后的统计信息，这次访问了4个数据块（4 consistent gets），刚好比前一次多了一个。因为索引中只查到了一条数据，所以只需要访问一个数据块。

为什么这次需要访问表呢？

因为这次要查询的结果列是paykind，这个列在使用的索引中不存在，只有到表里才能查到它的信息。

SQL> select /*+index(a PK_T_USERSERVICEINFO)*/paykind from t_userserviceinfo a where phonenumber = '674977819' and servicetype = 1;

PAYKIND

----------

Execution Plan

----------------------------------------------------------

Plan hash value: 2896004350

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 16 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T_USERSERVICEINFO | 1 | 16 | 3 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_T_USERSERVICEINFO | 1 | | 2 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("PHONENUMBER"='674977819' AND "SERVICETYPE"=1)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

4 consistent gets

1 physical reads

0 redo size

525 bytes sent via SQL*Net to client

520 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

4.2.4 索引扫描提示

/*+index(a PK_T_USERSERVICEINFO)*/这个提示表示强制指定某个索引，如果可以的话，优化器会使用该索引，不过不一定是索引唯一扫描。PK_T_USERSERVICEINFO是要使用的索引名，a是表的别名。

4.2.5 与全表扫描的对比

索引唯一扫描一般比全表扫描的效率高的多，因为索引唯一扫描只需要访问几个块，而全表扫描可能需要访问上万个块。

当表非常小时，两者的查询效率就差不多了，此时也就不用关注性能差异了。

重要知识点

当根据主键或唯一索引查询数据时，可以使用索引唯一扫描。这种查询的代价非常小，因为它最多从索引树中查出一条数据。

索引唯一扫描在访问索引时最多访问的数据块数就是索引树的高度。

当索引中包含了要查询的所有数据时，那么就只需要访问索引而不用再访问表。

当表数据量很大时，索引唯一扫描一般比全表扫描的效率高的多。

4.3 索引范围扫描（INDEX RANGE SCAN）

4.3.1 什么是索引范围扫描

索引范围扫描跟索引唯一扫描类似，从根节点一直扫描到叶子节点，每一层（不包括叶子节点）扫描一个索引块。它们的区别是，索引唯一扫描在叶子块里最多只能找到一条目标数据，而索引范围扫描可能找到N条目标数据（N>=0）。由此可见，索引唯一扫描实际是索引范围扫描的一个特例。

当目标数据很多时，就可能占据多个叶子块，例如查询条件为userid=1000，可能查出很多条数据，假设占用了三个索引块，如下图所示（图中省略了rowid）。

我们可以确定，这三个索引块是相邻的叶子块。这里说的相邻是使用指针关联的，而不一定是物理位置的连续。对于这个查询需要且只需要访问三个叶子块：首先从索引树定位到最左侧第一个含有目标数据的叶子块，然后使用指针找到另外两个叶子块。

为什么不再访问第四个叶子块呢？因为第三个叶子块中已经包含了比1000大的数据，那么后面就肯定不会再有1000了，记住叶子块中的键值是有序的。

同理可以推出，当目标数据存在于100个叶子块，那么这100个叶子块都需要访问，但是注意，只有第一次是从根节点下来的，后面的访问都是根据叶子块的指针来访问的。理解这一点很重要，很多初学者会以为每查一条数据都要从根节点开始扫描的。

4.3.2 使用场景分析

什么情况下会使用索引范围扫描？

查询列上有唯一索引就一定是索引唯一扫描吗？

查询列上有非唯一索引就一定是索引范围扫描吗？

查询条件使用了查询列就一定能用索引范围扫描吗？

大部分的索引扫描都是索引范围扫描，我们主要分析下哪些场景不能使用索引范围扫描：

（1）查询条件中没有使用索引列

如果只告诉你汉字的拼音却要你使用部首的索引，显然是办不到的。

（2）使用全模糊查询

例如username like ’%abc%’，这个查询就不能使用username的索引范围扫描（注意不是说一定不能使用这个索引，只是不能使用这种扫描类型）。

就像查字典，如果要查CH开头的字，可以去索引里检索，但如果要查中间包含AB的字就不好使用索引了。

（3）没有使用组合索引的前导列（一般是索引的第一个列）

假设userid和servicetype上有组合索引，查询条件userid=1可以用该索引做索引范围扫描，但servicetype=1就不可以。这个原因和第一点是类似的。其实在索引中对组合索引并没有很多特殊处理，它只保存键值和rowid，对于组合索引来说，键值就是多个字段的值的拼接。

你可以把一个字符串列的索引想象成一个组合索引，每一位是一个列。你也可以把一个单列索引认为是只有一个列的组合索引，这样说来，单列索引只是组合索引的一个特例。

（4）查询空值

当键值为空时，索引是不包含该数据的，所以查询username is null就不能使用username的索引。有的人会有一个错误的认识：索引的列必须是非空的。显然，这种理解是错误的，索引列允许为空。

虽然username列可以为空，但是username=’abc’是可以使用该索引的，因为null不是要查的数据，索引不包含username为空的数据对查询没有影响。

（5）使用不等于或not in

查询条件userid<>123不能使用索引范围扫描，不过修改为userid>123 or userid <123却可以，当然一般这样的查询效率也比较低。

（6）查询列上有表达式或函数

例如username || ‘’ = ‘abc’，此时username的索引就无法使用索引范围扫描了，这也是常用的屏蔽索引的方法。

归纳下，索引范围扫描的使用场景如下表所示，其中username列有非唯一索引，phonenumber列上有唯一索引，userid和servicetype上有主键（组合索引）。

场景

查询条件举例

说明

可以使用索引范围扫描场景

对非唯一索引的等值查询

username =’abc’

对非唯一索引的范围查询

username >’abc’

对非唯一索引的模糊查询

username like ’abc%’

必须是前匹配，如果是全模糊，则无法使用索引范围扫描

对唯一索引的范围查询

phonenumber>’123’

对唯一索引的模糊查询

phonenumber like ’123%’

必须是前匹配

对唯一索引使用前导列查询

userid=123

userid是组合索引的第一个列

不可以使用索引范围扫描场景

非前匹配的模糊查询

username like ’%abc’

username like ’%abc%’

查询列上有表达式

username || ‘’ = ’abc’

userid +0=123

to_number(phonenumber)= 123

查询列上有隐式转换

phonenumber=123

等价于to_number(phonenumber)= 123

查询空值

phonenumber is null

使用组合索引但未使用前导列

servicetype=1

userid和servicetype上有索引

使用不等于

userid<>123

4.3.3 索引范围扫描性能分析概述

索引范围扫描的性能好不好？这要怎么衡量呢？还记得前面说过，简单的说，可以算下查询需要访问的数据块（包括表和索引的块），如果很多就不好，反之则好。

如果索引范围扫描不好如何处理呢？你可以有以下选择：

换个索引

对于userid=1 and status=1，一般userid的索引要好于status的索引，因为status的重复数据多，而userid唯一性好。

使用全表扫描

如果要你把字典里所有的字都看一遍（包括内容），显然没必要一个个通过索引来查找了，直接按顺序看内容更快。

使用快速全索引扫描

如果要你把字典里所有的字都看一遍，不需要看内容，那是不是只要把索引翻一遍即可，毕竟索引只有几十页，而内容有几千页。这种扫描类型后面还会讨论。

4.3.4 选择哪个索引好

对于一个特定的SQL，走哪个索引访问的数据块少，就应该选择哪个索引。要选择好索引，就需要了解数据的大概分布。

其实，我们也不需要精确的计算实际要访问多少个块，一般而言，多扫描几个块对性能的影响也很小。我们需要关注的是差异很大的索引，如果一个索引只需要访问10个块，另一个需要访问1万个块，当然要选择前者。

索引范围扫描要访问的块包括索引块（主要是叶子块）和表块（根据叶子块的rowid访问）。下面详细分析下索引范围扫描过程中需要访问的几种数据块。

（1）根节点和枝节点

前面讨论过，索引的层高都比较小，并且每层只需要扫描一个块，所以这部分的开销是很小的，一般都是个位数。而且，由于这部分索引块经常被访问到，一般都是在内存中，基本上都是逻辑读，所以这部分的开销几乎可以忽略。

（2）叶子节点

这部分的开销可大可小，对性能影响巨大。下面分几种场景详细讨论下：

根据索引条件只查到一条数据

例如，查询status=1，如果只查到一条数据，那么就只需要扫描一个叶子块，此时这部分的开销最小。

根据索引条件查出很多数据

例如，查询status=1，如果符合条件的数据有100万，那么就可能需要访问上万个数据块，那么这部分的开销就很大。

有rownum的条件

例如，查询status=1 and rownum<=100，此时符合status=1条件的数据即使有100万，依然可以走status的索引，因为只要查询到100条status=1的数据后就中止了，不会继续扫描后面的叶子块了。这一点比较容易迷惑，需要重点理解。

访问了很多索引块，实际目标数据很少

索引列的查询条件就是为了准确定位到目标数据。假如userid, servicetype上有一个组合索引，当查询userid=1 and servicetype=1的数据时，根据这个索引可以准确定位到第一个目标叶子块。但是当查询userid>1 and servicetype=1时，servicetype=1条件就起不到定位数据作用，而仅仅是过滤数据的作用。

下面看一个例子：

假设有100万的数据满足userid>1，其中只有一条满足servicetype=1，那么通过这个组合索引实际只查出来一条数据，那么是不是只需要访问一个叶子块呢？当然不是，根据查询条件只能定位到第一个满足userid>1的叶子块，后面所有满足userid>1的数据都可能出现servicetype=1的数据，也可能一条都没有，所以实际上还是访问了几万个叶子块。

如果反过来，查询条件是userid=1 and servicetype>1，数据中有100万满足userid=1，其中只有1条满足servicetype>1。此时使用这个索引可以精确定位到目标数据的第一个叶子块，往后扫描时，只要出现一条不满足userid=1 and servicetype>1的数据就可以中止了，后面不会再有目标数据了。

为什么会有这样大的差异呢？因为组合索引的键值是先按照第一个列排序的，当第一个列相同时才按照第二个列排序。这个例子可能不太好理解，但是很重要。

（3）表数据块

如果要访问的数据都在索引里，此时不需要访问表。实际应用中，这种场景并不多，大部分的SQL是需要访问表的。

例如userid=1 and username=’abc’，假设走userid的索引并且查出了30条满足userid=1的数据，那么就需要根据rowid访问表数据才能判断username=’abc’的条件是否成立。此时userid=1是访问谓词，它用于查找数据位置，而username=’abc’就是过滤谓词，它不能定位数据的位置，只用于从查到的数据中过滤掉不要的数据。

如果这30条数据分布在两个数据块，那么就只要再扫描两个数据块；如果分布在30个数据块，那么就需要扫描30个数据块。索引中相邻的数据如果在数据块中也是相邻的，这样做索引范围扫描是比较理想的。可惜实际数据却不一定是这样的，因为索引中的数据是有序的，而表中的数据是无序。

再看下面这个例子：

假如有一个查询是userid>=0 and phonenumber>= '0'，假如这两个列都有索引并且查询条件都是命中所有数据，那么哪个索引好呢？

此时查询的开销就主要体现在要访问多少的表块了，因为从索引中查出来的数据是一样多的，访问的索引块也相当。

如果userid是一个自增长的列，而phonenumber表示用户号码，你会发现对于这个查询，userid索引比phonenumber索引效率高不少。因为userid是自增长的，相邻的数据往往录入表的时间也相近，出现在同一个表块的可能性比较大；而phonenumber相邻的数据在表中就是随机分布的。

其实索引有个 “聚集因子”的属性就描述了索引这方面的情况，优化器会利用这个属性估算索引扫描的开销从而选择不同的执行计划。

由于表的数据块很多，这种访问出现物理读的可能性比较大，所以这部分开销往往是查询的最大开销。

4.3.5 全表扫描还是索引范围扫描

对于大部分的实时查询，一般都不会使用全表扫描，但有时全表扫描可能比索引范围扫描的效率高的多。

举个例子，假设表A有200万数据，占用了5万个数据块，列username有索引，占用了5000个块。现在有一个查询username= 'abc' and status=1，假设所有数据都满足username= 'abc'，下面看看两种查询方式的开销分别有多少。

（1）全表扫描

5万个块都需要被访问，假设都是物理读。假如多块读可以将IO的开销降低到1/5，那么实际发生了1万次的物理读。

总开销=1万次物理IO

（2）索引范围扫描

如果是第一次扫描的话，索引块也是物理读，并且只能是单块读，那么索引扫描需要5000次物理读。根据rowid到表每次也只能访问一个块，如果每个叶子块的数据都不在同一个数据块里，那么最多可能发生200万次的IO；如果每个叶子块的数据都只在10个块里，那最少要产生5万次的IO。不管怎样，对表的5万次物理IO是不可避免的。

那么，计算下产生的总开销：

假设1次物理IO=10次逻辑IO

最好情况=索引IO+表IO==5000物理IO+50000物理IO =5.5万次物理IO

最坏情况=索引IO+表IO==5000物理IO+50000物理IO+1950000逻辑IO =20.5万次物理IO

从上面的计算结果可以看到，当索引命中的数据过多时，索引范围扫描比全表扫描的开销可能高很多倍，一般查询超过5%的数据就不建议使用索引范围扫描。

相对于索引范围扫描来说，全表扫描的成本是衡定不变的。无论你的查询是想访问一笔数据还是访问整个表的所有数据，全表扫描都会把整张表读出来。而索引范围扫描就不同，因为有了索引这个有序的数据结构做支持，可以只访问需要的索引块和需要的数据块，而不是访问整张表，它可能很好，也可能极差。

重要知识点

索引唯一扫描实际是索引范围扫描的一个特例。

索引范围扫描的主要开销是扫描索引叶子块和表块，尤其是后者。

掌握哪些场景可以使用索引范围扫描，哪些不可以。

rownum条件对查询的影响很大。索引的聚集因子也会影响查询效率。

当索引命中的数据过多时，索引范围扫描比全表扫描的开销可能高很多倍，一般查询超过5%的数据就不建议使用索引范围扫描。

4.4 索引全扫描（INDEX FULL SCAN）

4.4.1 什么是索引全扫描

索引全扫描一般会扫描所有叶子节点。它从第一个叶子节点开始扫描，通过叶子节点的链接依次访问每一个叶子节点。

索引全扫描有以下几个特点：

由于叶子节点是有序的，所以扫描结果也是有序的。

由于叶子块之间是指针关联的，所以对叶子块只能使用单块读。

如果需要访问表，由于每一条数据都需要使用rowid访问，它的效率比全表扫描低的多。

前面说过，索引范围扫描可能只访问一个叶子块也可能访问很多叶子块。那么，最坏情况下，索引范围扫描也可能需要访问所有叶子块，此时它的效果和索引全扫描是一样的，所以可以认为索引全扫描是索引范围扫描的一种极端情况。

4.4.2 索引全扫描实例

（1）索引列不允许为空

索引pk_t_userinfo所在列是非空列，不使用索引列查询，但使用提示指定使用该索引，此时执行计划显示为全索引扫描。全索引扫描的提示和索引范围扫描是一样的。

由于需要count（*），而索引列是非空列，所以只在访问索引，不需要访问表。

（2）索引列允许为空

索引ix_userinfo_username所在列username允许为空，直接指定该索引，从执行计划看，仍然是全表扫描。原因是该索引不包含所有数据，所以不能使用全索引扫描。

如果增加条件username is not null，此时可以使用索引全扫描了，由于这个查询需要访问所有列，所以需要访问表（TABLE ACCESS BY INDEX ROWID）。

4.4.3 索引全扫描的应用场景

索引全扫描是索引范围扫描的最差情况，那么是否应该避免出现索引全扫描呢？

的确，一般情况下不应该使用索引全扫描，它甚至比全表扫描还差很多，一般是没有合适的索引或者提示指定错了索引导致使用了索引全扫描。

但是，尺有所长，寸有所短，它有一个重要特性：查询结果是有序的。当需要对一个大数据量的表排序，然后返回TOP N时，此时可以使用索引全扫描来避免排序，因为排序需要把所有数据读到内存中，非常消耗性能。

下面看一个例子：

对表t_userinfo按照phonenumber排序后再取前100条数据。虽然SQL中有排序，但执行计划中并没有SORT，这就是因为使用了全索引扫描的效果。

注意：这里的rownum<=100很重要，它表示扫描到100条目标数据就可以返回了，而不用扫描所有数据。如果需要扫描所有数据，那么全索引扫描即使不排序也很差。

对上面的查询，如果增加一个条件：username = 'abc'，虽然执行计划没有变化，但是它的性能可能差很多，因为这个条件是个过滤谓词，如果符合这个条件的数据很少，那么就可能扫描很多数据才能查到100条目标数据。所以执行计划相同，性能不一定一样，你必须了解数据的分布情况，才能做出正确的判断。

重要知识点

索引全扫描是索引范围扫描的最差情况，一般要避免使用。

当需要对一个大数据量的表排序，然后返回TOP N时，此时可以使用索引全扫描来避免排序，但一定不能扫描很多数据。

索引快速全扫描（INDEX FAST FULL SCAN）

4.5.1 什么是索引快速全扫描

索引快速全扫描和索引全扫描极为类似，它也适用于所有类型的B+树索引（包括唯一性索引和非唯一性索引）。和索引全扫描一样，索引快速全扫描也需要扫描目标索引所有叶子块的所有索引行。

索引快速全扫描与索引全扫描相比有如下三点区别。

（1）索引快速全扫描只适用于CBO。

（2）索引快速全扫描可以使用多块读，也可以并行执行。

（3）索引快速全扫描的执行结果不一定是有序的。

因为索引快速全扫描时Oracle是根据索引行在磁盘上的物理存储顺序来扫描，而不是根据索引树的指针顺序来扫描的，所以返回结果是无序的。

4.5.2 索引快速全扫描实例

下面是使用索引快速全扫描的例子，它的提示是index_ffs，与索引范围扫描不同。

4.5.3 索引快速全扫描的应用场景

索引快速全扫描的应用条件：

（1）只需要访问索引，不需要访问表。

（2）要访问的数据在索引中都存在，对于count（*）就不能从一个允许为空的索引中获得结果。

索引快速全扫描的使用场景：在只需要访问索引就能获得结果的情况下，替代全表扫描使用。它的性能比全表扫描好，因为索引的块比表少的多；它的性能一般也优于索引全扫描，因为它支持多块读，而索引全扫描不支持；它的性能不一定有索引范围扫描好，更没有索引唯一扫描好，因为它毕竟要访问所有索引块。

所以，索引快速全扫描只是在某些场景下替代全表扫描使用。

重要知识点

索引快速全扫描支持多块读，结果是无序的。

索引快速全扫描的使用场景：在只需要访问索引就能获得结果的情况下，替代全表扫描使用。

4.6 索引跳跃扫描（INDEX SKIP SCAN）

4.6.1 什么是索引跳跃扫描

索引跳跃式扫描（INDEX SKIP SCAN）适用于所有类型的B+树索引（包括唯一性索引和非唯一性索引），它使那些在where条件中没有对目标索引的前导列指定查询条件但同时又对该索引的非前导列指定了查询条件的目标SQL依然可以用上该索引，这就像是在扫描该索引时跳过了它的前导列，直接从该索引的非前导列开始扫描一样（实际的执行过程并非如此），这也是索引跳跃式扫描中"跳跃"（SKIP）一词的含义。

4.6.2 索引跳跃扫描实例

（1）首先构造一个测试表

数据量是100万数据，该表有两列，第一列只有0、1、2三个取值，在这两个列上创建组合索引。

create table t_test nologging as select mod(rownum, 3) usertype, rownum userid from dual connect by level <= 1000000;

create index ix_test on t_test(usertype, userid) tablespace ringidx;

（2）使用userid列查询，由于没有使用索引的第一个列，所以不能使用索引范围扫描，但是可以使用索引跳跃扫描，提示index_ss指定索引跳跃扫描。

（3）它实际上跟下面这个查询的效率是相当的

（4）但是，它比下面这个查询的效率高的多，虽然结果也是一样的，想想为什么？

4.6.3 索引跳跃扫描的应用场景

索引跳跃扫描一般是不应该出现的，往往是索引设计错误或者SQL错误导致的错误。不过，在某些场景下也可以使用，从前面的例子可以看到，索引跳跃扫描也可能优于索引范围扫描。

索引跳跃的应用场景：索引前导列（一般就是第一列）的取值很少的情况。

重要知识点

索引跳跃扫描一般是不应该出现的，往往是索引设计错误或者SQL错误导致的错误。

索引跳跃扫描也可能优于索引范围扫描。

索引跳跃的应用场景：索引前导列（一般就是第一列）的取值很少的情况。

4.7 数据访问路径总结

数据访问路径

主要特点

适用场景

全表扫描

1。扫描表的所有数据块，包括空块

2。支持多块读

1。需要访问超过5%的数据总量

2。一般是后台任务，前台业务一般要避免对大表做全表扫描

索引唯一扫描

1。一次扫描的块非常少

2。它是索引范围扫描的特例

3。索引扫描最多查出一条数据

1。根据主键或唯一索引精确查询

2。优先使用的方式

索引范围扫描

1。最常用的索引扫描方式

2。性能最好跟索引唯一扫描一样，最差跟索引全扫描一样

3。当需要在索引中扫描大量数据并且需要访问表时性能很差

4。索引返回结果是有序的

1。适合在索引中命中少量数据的场景，不超过5%的数据总量

索引全扫描

1。索引范围扫描的特例

2。索引返回结果是有序的

1。一般不建议使用

2。在排序后查TOP N的场景下可以使用

索引快速全扫描

1。索引返回结果是无序的

2。性能优于全表扫描

3。支持多块读，优于全索引扫描

1。只需要访问索引，不需要访问表

2。作为全表扫描的替代方式使用

3。CBO可以使用，RBO不可以

索引跳跃扫描

1。组合索引查询时，查询条件没有使用前导列

2。等价于遍历前导列的所有取值分别做索引范围扫描

1。一般不建议使用

2。前导列的取值很少时可以使用

3。CBO可以使用，RBO不可以

5 索引设计的一般原则

分区表不要创建全局索引

分区表一般创建本地索引（使用local关键字）。如果创建了全局索引，当删除分区时会导致索引失效。

不要创建无用的索引

索引会降低DML语句的性能，所以一般不要创建无用的索引。

不要创建同样功能的索引

例如下面两个索引，如果在userid, playlistid上创建了索引，就没有必要在userid上创建一个索引。

create index ix_ums_playcontentlist_userid on t_ums_userplaycontentlist (userid)

tablespace ringidx

create index ix_ums_playcontentlist_id on t_ums_userplaycontentlist (userid, playlistid)

tablespace ringidx

索引的key不宜过长

如果索引key过长，会导致索引树高度很大，索引查询效率会降低。对于组合索引，索引的列不宜过多。不要把长字符串列作为索引列，例如描述字段。

对于组合索引，要把高选择度的列放在前面

如下例所示，useraccount选择度远高于accounttype，所以应该把useraccount作为索引的首列，这样当查询条件中有useraccount而没有accounttype时仍能高效的使用索引。

create index ix_ums_usrordlib_account_type on t_ums_userorderlib (useraccount,accounttype)

tablespace ringidx

当需要对大数据量排序时，可以通过创建索引来避免排序

场景1：分页查询需要查询歌曲（总数100万），没有查询条件，查询结果需要按照musicname排序，实际绝大部分的查询是前几页。

解决方案：在musicname创建索引，通过索引全扫描来避免排序，只要不是查询的数据非常靠后，效率就很高。

场景2：查询某表中status为1的数据并且要求结果按照inserttime排序，处理完后status会更新为2。

解决方案：在status和inserttime上创建组合索引，查询条件是status=1，由于索引的有序性，查询出来的数据自然是按照inserttime排序的。

一般不在选择率很低的列上创建索引

一般不在状态，用户类型这种取值很少的列上创建索引。但是，有些场景却可以创建这种索引。例如，当需要查询的那部分的取值在数据中的比例很低时或者使用rownum限制了每次只查询出一小批数据。总的原则是，只要一次查询的数据在数据中的比例非常小，那么就适合使用索引。这里之所以说“一次查询”，是因为符合条件的数据可能很多，但是可能使用rownum限制了每次只查100条。

如果where语句中不得不对查询列采用函数查询，如upper函数，需要建立相应函数索引

如果查询条件在列上使用了函数，那么直接在列上创建的索引是无法使用的，必须创建对应的函数索引。如下例所示。

create index ix_auditionauthlog_tonename on t_ums_auditionauthlog(upper(tonename)) local

tablespace ringidx

函数索引跟普通索引的区别是键值不是列的值，而是应用了函数后的值，如果这个函数不改变列的值的话，那么普通索引也可以看作是函数索引的一个特例。

索引和表一般要创建的不同的表空间中

可以提高IO的性能。

6 总结

索引是提高数据查询最有效的方法，也是比较难掌握的技术。好的索引可能使效率提高上万倍，而无效的索引可能浪费了数据库空间，甚至大大降低查询性能。

索引有很多种类型，最常用的是单列索引、复合索引、函数索引以及分区索引。每种索引都有不同的使用场合，只有深刻理解了各种索引的特性，才能在面对纷繁复杂的应用时游刃有余。

如果希望掌握索引的用法还必须能看懂执行计划，了解Oracle优化器的原理。因为只要是稍微复杂一点的查询语句，就会涉及到多表之间的连接，就很难靠调整索引解决所有问题。

本文不求面面俱到，仅仅是把平时遇到的问题和积累的经验总结出来，希望能给初学者一点帮助。

Oracle SQL

使用Word数学公式编辑器MathType轻松快速在Word中输入公式（mathtype编辑完的数学公式怎么放到Word中）

739 2022-05-29

轻松 掌握 Oracle索引（二）

一文掌握Excel动态合并工作表技巧（excel合并工作表数据）

用Word 2013轻松制作练字帖的方法（用word做组织结构图）

使用Word数学公式编辑器MathType轻松快速在Word中输入公式（mathtype编辑完的数学公式怎么放到Word中）

推荐文章

企业生产管理是什么，企业生产管理软件

进盘点进销存软件排行榜前十名

进销存系统哪个简单好用？进销存系统优点

工厂生产管理（工厂生产管理流程及制度）

生产管理软件，机械制造业生产管理，制造业生产过程管理软件

进销存软件和ERP有什么区别？进销存与erp软件理解

进销存如何进行库存管理

如何利用excel制作销售订单管理系统？

数据库订单管理系统有哪些功能？数据库订单管理系统怎么设计？

什么是数据库管理系统？

最近发表

热评文章

零代码开发是什么？2022低代码平台排行榜

智能进销存库存管理系统（智慧进销存）

在线文档哪家强？8款在线文档编辑软件推荐

WPS2016怎么绘制简单的价格表?

连锁餐饮管理系统的功能有哪些？餐饮服务系统的构成及工

Excel项目进度表模板，简化您的项目进度管理

友情链接