Oracle SQL优化②——访问路径

前言

访问路径指的就是通过哪种扫描方式获取数据，比如全表扫描、索引扫描或者直接通过ROWID获取数据。想要完成SQL优化，就必须深入理解各种访问路径。本文章详细介绍常见的访问路径。

一.常见访问路径

1.TABLE ACCESS FULL

表示全表扫描，一般情况下是多块读，HINT: FULL(表名/别名)。等待事件为 db file scattered read。如果是并行全表扫描，等待事件为 direct path read。在Oracle11g中有个新特征，在对一个大表进行全表扫描的时候，会将表直接读入PGA,绕过buffercache，这个时候全表扫描的等待事件也是 direct path read。一般情况下，我们都会禁用该新特征。等待事件 direct path read在开启了异步I/O(disk_asynch_io)的情况下统计是不准确的。

alter system set "_serial_direct_read"=false;

全表扫描究竟是怎么扫描数据的呢？回忆一下Oracle的逻辑存储结构，Oracle最小的存储单位是块(block)，物理上连续的块组成了区(extent)，区又组成了段(segment)。对于非分区表，如果表中没有clob/blob字段，那么一个表就是一个段。全表扫描，其实就是扫描表中所有格式化过的区。因为区里面的数据块在物理上是连续的，所以全表扫描可以多块读。全表扫描不能跨区扫描，因为区与区之间的块物理上不一定是连续的。对于分区表，如果表中没有clob/blob字段，一个分区就是一个段，分区表扫描方式与非分区表扫描方式是一样的。
对一个非分区表进行并行扫描，其实就是同时扫描表中多个不同区，因为区与区之间的块物理上不连续，所以我们不需要担心扫描到相同数据块。
对一个分区表进行并行扫描，有两种方式。如果需要扫描多个分区，那么是以分区为粒度进行并行扫描的，这时如果分区数据不均衡，会严重影响并行扫描速度；如果只需要扫描单个分区，这时是以区为粒度进行并行扫描的。
如果表中有clob字段，clob会单独存放在一个段中，当全表扫描需要访问clob字段时，这时性能会严重下降，因此尽量避免在Oracle中使用clob。我们可以考虑将clob字段拆分为多个varchar2(4000）字段，或者将clob存放在nosql数据库中，例如 mongodb。

一般的操作系统，一次I/O最多只支持读取或者写入1MB数据。数据块为8KB的时候，一次I/O最多能读取128个块。数据块为16KB的时候，一次I/O最多能读取64个块，数据块为32KB的时候，一次I/O最多能读取32个块。
如果表中有部分块已经缓存在buffer cache中，在进行全表扫描的时候，扫描到已经被缓存的块所在区时，就会引起I/O中断。如果一个表不同的区有大量块缓存在buffer cache中，这个时候，全表扫描性能会严重下降，因为有大量的I/O中断，导致每次I/O不能扫描1MB数据。

如果表正在发生大事务，在进行全表扫描的时候，还会从undo读取部分数据。从undo读取数据是单块读，这种情况下全表扫描效率非常低下。因此，我们建议使用批量游标的方式处理大事务。使用批量游标处理大事务还可以减少对undo的使用，防止事务失败回滚太慢。

2.TABLE ACCESS BY USER ROWID

表示直接用ROWID获取数据，单块读
该访问路径是Oracle所有访问路径中性能是最好的

3.TABLE ACCESS BY ROWID RANGE

ROWID范围扫描，多块读。因为同一个块里面的ROWID是连续的，同一个EXTENT里面的ROWID也是连续的，所以可以多块读。

4.TABLE ACCESS BY INDEX ROWID

表示回表，单块读

5.INDEX UNIQUE SCAN

索引唯一扫描，单块读
对唯一索引或者对主键列进行等值查找，就会走索引唯一扫描。因为对唯一索引或者对主键列进行等值查找，CBO能确保最多只返回1行数据，所以这时可以走索引唯一扫描。
在所有Oracle访问路径中，性能仅次于TABLE ACCESS BY USER ROWID

6.INDEX RANGE SCAN

索引范围扫描，单块读，返回的数据是有序的（默认升序）。HINT:INDEX(表名/别名索引名)。对唯一索引或者主键进行范围查找，对非唯一索引进行等值查找，范围查找，就会发生 INDEX RANGE SCAN。等待事件为 db file sequential read。
因为索引IDXID是非唯一索引，对非唯一索引进行等值查找并不能确保只返回一行数据，有可能返回多行数据，所以执行计划会进行索引范围扫描。
索引范围扫描默认是从索引中最左边的叶子块开始，然后往右边的叶子块扫描（从小到大)，当检查到不匹配数据的时候，就停止扫描。
在检查执行计划的时候我们要注意索引范围扫描返回多少行数据，如果返回少量数据，不会出现性能问题。如果返回大量数据，在没有回表的情况下也还好。如果返回大量数据同时还有回表，这时我们应该考虑通过创建组合索引消除回表或者使用全表扫描来代替它。

7.INDEX SKIP SCAN

索引跳跃扫描，单块读。返回的数据是有序的（默认升序）。HINT:INDEX_SS(表名/别名索引名)。
INDEX SKIP SCAN中有个SKIP关键字，也就是说它是跳着扫描的。那么想要跳跃扫描，必须是组合索引，如果是单列索引怎么跳？另外，组合索引的引导列不能出现在where条件中，如果引导列出现在where 条件中，它为什么还跳跃扫描呢，直接INDEX RANGE SCAN不就可以了？再有，要引导列基数很低，如果引导列基数很高，那么它“跳”的次数就多了，性能就差了。
当执行计划中出现了 INDEX SKIP SCAN，我们可以直接在过滤列上面建立索引，使用INDEX RANGE SCAN代替INDEX SKIP SCAN。

8.INDEX FULL SCAN

索引全扫描，单块读，返回的数据是有序的（默认升序）。HINT:INDEX(表名/别名索引名)。索引全扫描会扫描索引中所有的叶子块（从左往右扫描），如果索引很大，会产生严重性能问题（因为是单块读）。等待事件为db file sequential read。
它通常发生在下面3种情况。
分页语句。
SQL语句有order by选项，order by的列都包含在索引中，并且order by后列顺序必须和索引列顺序一致。order by的第一个列不能有过滤条件，如果有过滤条件就会走索引范围扫描(INDEX RANGE SCAN)。同时表的数据量不能太大（数据量太大会走TABLE ACCESS FULL+ SORT ORDER BY)。
在进行SORT MERGE JOIN的时候，如果表数据量比较小，让连接列走INDEX FUL SCAN可以避免排序。
当看到执行计划中有INDEX FULL SCAN,我们首先要检查INDEX FULL SCAN是否有回表
如果INDEX FULL SCAN没有回表，我们要检查索引段大小，如果索引段太大(GB级别)，应该使用INDEX FAST FULL SCAN代替INDEX FULL SCAN,因为INDEX FAST FULL SCAN是多块读，INDEX FULL SCAN是单块读，即使使用了INDEX FAST FULL SCAN会产生额外的排序操作，也要用INDEX FAST FULL SCAN 代替INDEX FULL SCAN。
如果INDEX FULL SCAN有回表，大多数情况下，这种执行计划是错误的，因为INDEX FULL SCAN 是单块读，回表也是单块读。这时应该走全表扫描，因为全表扫描是多块读。如果分页语句走了INDEX FULL SCAN然后回表，这时应该没有太大问题

9.INDEX FAST FULL SCAN

索引快速全扫描，多块读。HINT：INDEX_FFS（表名/别名 索引名)。当需要从表中查询出大量数据但是只需要获取表中部分列的数据的，我们可以利用索引快速全扫描代替全表扫描来提升性能。索引快速全扫描的扫描方式与全表扫描的扫描方式是一样，都是按区扫描，所以它可以多块读，而且可以并行扫描。等待事件为db file scattered read，如果是并行扫描，等待事件为 direct path read。
现有如下SQL

select owner,object_name from test;

该SQL没有过滤条件，默认情况下会走全表扫描。但是因为Oracle是行存储数据库，全表扫描的时候会扫描表中所有的列，而上面查询只访问表中两个列，全表扫描会多扫描额外13个列，所以我们可以创建一个组合索引，使用索引快速全扫描代替全表扫描。

create index idx_ownername on test(owner,object_name,0);

以上SQL能否走 INDEX RANGE SCAN 呢？INDEX RANGE SCAN是单块读，SQL会返回表中大量数据，“几乎”会扫描索引中所有的叶子块。INDEX FAST FULL SCAN是多块读，会扫描索引中所有的块（根块、所有的分支块、所有的叶子块)。虽然INDEX RANGE SCAN与INDEX FAST FULL SCAN相比扫描的块少（逻辑读少），但是INDEX RANGE SCAN是单块读，耗费的I/O次数比INDEX FAST FULL SCAN的I/O次数多，所以INDEX FAST FULL SCAN 性能更好。
在做SQL优化的时候，我们不要只看逻辑读来判断一个SQL性能的好坏，物理I/O次数比逻辑读更为重要。有时候逻辑读高的执行计划性能反而比逻辑读低的执行计划性能更好，因为逻辑读高的执行计划物理I/O次数比逻辑读低的执行计划物理I/O次数低。
在 Oracle 数据库中，INDEX FAST FULL SCAN是用来代替TABLE ACCESS FULL的。因为Oracle是行存储数据库，TABLE ACCESS FULL会扫描表中所有的列，而INDEX FAST FULL SCAN只需要扫描表中部分列，INDEX FAST FULL SCAN就是由Oracle 是行存储这个“缺陷”而产生的。

10.INDEX FULL SCAN (MIN/MAX)

索引最小/最大值扫描，单块读，该访问路径发生在select max(column) from table;或者select min(column) from table;语句中。
只会访问“索引高度”个索引块，其性能与INDEX UNIQUE SCAN一样，仅次于TABLE ACCESS BY USER ROWID

11.MAT_VIEW REWRITE ACCESS FULL

物化视图全表扫描，多块读。物化视图本质上也是一个表，所以其扫描方式与全表扫描一样。如果开启了查询重写功能，而且SQK查询能够直接从物化视图中获得结果，就会走该访问路径。

二.单块读与多块读

单块读与多块读这两个概念对于掌握SQL优化非常重要，更准确地说是单块读的物理I/O次数和多块读的物理I/O次数对于掌握SQL优化非常重要。
从磁盘1次读取1个块到buffer cache就叫单块读，从磁盘1次读取多个块到buffer cache就叫多块读。如果数据块都已经缓存在buffer cache中，那就不需要物理I/O了，没有物理I/O也就不存在单块读与多块读。
绝大多数的平台，一次I/O最多只能读取或者写入1MB数据，Oracle的块大小默认是8K，那么一次I/O最多只能写入128个块到磁盘，最多只能读取128个块到buffer cache。在判断哪个访问路径性能好的时候，通常是估算每个访问路径的I/0次数，谁的I/O次数少，谁的性能就好。在估算I/O次数的时候，我们只需要算个大概就可以了，没必要很精确。

三.为什么有时候索引扫描比全表扫描更慢

假设一个表有100万行数据，表的段大小为1GB。如果对表进行全表扫描，最理想的情况下，每次I/O都读取1MB数据(128个块)，将1GB的表从磁盘读入buffer cache需要1024次I/O。在实际情况中，表的段前16个extent,每个extent都只有8个块，每次I/O只能读取8个块，而不是128个块，表中有部分块会被缓存在buffer cache中，会引起I/O中断，那么将1GB的表从磁盘读入buffer cache可能需要耗费1500次物理I/O。
从表中查询5万行数据，走索引。假设一个索引叶子块能存储100行数据，那么5万行数据需要扫描500个叶子块（单块读），也就是需要500次物理I/O,然后有5万条数据需要回表，假设索引的集群因子很小（接近表的块数），假设每个数据块存储50行数据，那么回表需要耗费1000次物理I/O(单块读)，也就是说从表中查询5万行数据，如果走索引，一共需要耗费大概1500次物理I/O。如果索引的集群因子较大（接近表的总行数），那么回表要耗费更多的物理I/O,可能是3000次，而不是1000次。
根据上述理论我们知道，走索引返回的数据越多，需要耗费的I/O次数也就越多，因此返回大量数据应该走全表扫描或者是INDEX FAST FULL SCAN,返回少量数据才走索引扫描。根据上述理论，我们一般建议返回表中总行数5%以内的数据，走索引扫描，超过5%走全表扫描。请注意，5%只是一个参考值，适用于绝大多数场景，如有特殊情况，具体问题具体分析。