Oracle-SQL性能优化（3）-访问路径

访问路径就是数据库检索数据的方式。
访问路径主要分为：

• 全表扫描 Full Table Scan
• rowid扫描 Index Unique Scan
• 索引唯一扫描 Index Unique Scan
• 索引范围扫描 Index Range Scan
• 索引跳跃扫描 Index Skip Scan
• 全索引扫描 Index Full Scan
• 快速全索引扫描 Fase Full Index Scan
• 集群访问 Cluster Access
• 哈希访问 Hash Access
• 抽样表扫描 Smple Table Scan

1.全表扫描 Full Table Scan

关键字：所有块、多块读

• 全表扫描是指扫描高水位以下的所有块，多块之间顺序读取，检查数据块中的每一行是否满足该语句的wher子句。
• 每次读取的块数受到数据库参数：DB_FILE_MULTIBLOCK_READ_COUNT的影响。
• 等待事件为db file scattered read 和 direct path read（11g引入，磁盘直接读到pga）

1.1.为什么返回大量数据的时候，全表扫描的方式更快 ?

全表扫描是多块读，索引范围扫描是单块读。 假设操作系统一次I/O操作最多读取1MB的数据，数据库默认数据块大小为8KB，那么DB_FILE_MULTIBLOCK_READ_COUNT的值应为128.访问一张大小为10M的表，全表扫描需要进行的I/O次数为（101024）/1288=10次。而索引范围扫描需要进行的I/O次数为10*（1024/8）=1280次，这还没有加上回表所产生的I/O次数。

1.2.什么情况下优化器会选择全表扫描的方式 ？

• 缺乏必要的索引或者索引列存在转换。UPPER(last_name)
• 检索大量数据。返回表绝大部分数据。
• 小表。
• 较高的并行度。表上存在较高的并行度，会使得优化器偏向于全表扫描。
• 使用了HINT强制使用全表。

2.ROWID扫描 Rowid Scan

关键字：先获取ROWID
通过ROWID定位数据文件和数据块，可以获得数据行所在的确切位置。对于检索单行数据，单块读的方式最为有效。如果是通过ROWID访问表，那么Oracle首先需要获得被检索记录的ROWID，可以在where条件中得到，但一般是通过扫描索引的方式来获得，然后基于rowid定位被检索的每条记录。并不是每个索引扫描都伴随着ROWID的访问，如果索引中包含了被访问的所有字段，则不再需要通过ROWID来访问表。

3.索引唯一扫描 Index Unique Scan

关键字：有索引、只返回一行
如果语句中包含了UNIQUE或者PRIMARY KEY约束，则需要对约束列进行等值查询，以确保查询结果只返回一行数据，Oracle数据库将执行索引唯一扫描。

SQL> select object_name,object_id from scott.jason a where a.object_id=41;
Execution Plan
----------------------------------------------------------
Plan hash value: 1931230502
---------------------------------------------------------------------------
| Id  | Operation		    | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		   |	 1 |	30 |	 2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| JASON	   |	 1 |	30 |	 2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN	    | PK_OBJECT_ID |	 1 |	   |	 1   (0)| 00:00:01 |
---------------------------------------------------------------------------
复制

4.索引范围扫描 Index Range Scan

关键字：返回多行、索引、单块读
索引范围扫描是最常见的访问方式，默认按照ROWID升序排列。除了UNIQUE或PRIMARY KEY约束列进行等值查询之外，其他大部分查询方式均为索引范围扫描。
等待事件为 db file sequential read (数据文件顺序读)，是以单块读的方式进行读取。

SQL> select * from scott.jason a where a.object_name='JASON';
Execution Plan
----------------------------------------------------------
Plan hash value: 2176269147
---------------------------------------------------------------------------
| Id  | Operation		    | Name	      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		      |     2 |   196 |     4	(0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| JASON	      |     2 |   196 |     4	(0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | IDX_OBJECT_NAME |     2 |       |     3	(0)| 00:00:01 |
---------------------------------------------------------------------------

--hint
HINT INDEX(table_alias index_name)
复制

当索引列或符合索引前导列上出现如下条件时，优化器将会选择索引范围扫描

• col1 = :b1
• col1 < :b1
• col1 > :b1
• col1 like ‘ASD%’
  当进行索引范围扫描时，我们首先会检查筛选列的选择性。此外，我们可以利用索引默认排序的特性，对检索数据进行排序，从而降低额外的排序开销。
  当然了，我们可以利用hint提示。

5.索引跳跃扫描 Index Skip Scan

关键字：适用于前导列基数低的场景
谓词条件中未指定复合索引的前导列，数据库仍然会使用符合索引。 等待事件为 db file sequential read

SQL> create index customers_gender_email on sh.customers(cust_gender,cust_email);
SQL> select * from sh.customers where cust_email='Abbed@company.com';
Execution Plan
----------------------------------------------------------
Plan hash value: 1287876719
---------------------------------------------------------------------------
| Id  | Operation		    | Name		     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |			     |	  33 |	5973 |	  10   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS 	     |	  33 |	5973 |	  10   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN	    | CUSTOMERS_GENDER_EMAIL |	  33 |	     |	   4   (0)| 00:00:01 |
---------------------------------------------------------------------------

--hint
HINT INDEX_SS(table_alias index_name)
复制

使用索引跳跃扫描的前提：

• 复合索引
• 前导列不在where条件中
• 前导列基数很低

6.全索引扫描 Index Full Scan

关键字：有序、单块
全索引扫描返回的是有序数据，因为索引列默认对数据进行排序。等待事件为db file sequential read.
使用全索引扫描的三种情况如下：

6.1.SQL语句中存在Order by子句

• ORDER BY子句中的列必须都在索引中
• ORDER BY子句中的列顺序必须与索引列的顺序相匹配。

SQL> select * from scott.jason order by object_id;
86981 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2328341681
---------------------------------------------------------------------------
| Id  | Operation		    | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		   | 86981 |  8324K|  1642   (1)| 00:00:20 |
|   1 |  TABLE ACCESS BY INDEX ROWID| JASON	   | 86981 |  8324K|  1642   (1)| 00:00:20 |
|   2 |   INDEX FULL SCAN	    | PK_OBJECT_ID | 86981 |	   |   297   (1)| 00:00:04 |
---------------------------------------------------------------------------
复制

6.2.查询采用排序合并连接的方式

查询采用排序合并连接的方式，当查询满足以下要求时，数据库可以执行索引全扫描，而不是全表扫描，然后再执行排序操作。

• 查询中引用的所有列都必须在索引中。
• 查询中引用列的顺序必须与索引列的顺序相匹配。

SQL> select e.ename,d.dname from scott.dept d,scott.emp e where e.deptno >= d.deptno;
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
---------------------------------------------------------------------------
| Id  | Operation		     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |    28 |   616 |     6	(17)| 00:00:01 |
|   1 |  MERGE JOIN		     |	       |    28 |   616 |     6	(17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2	 (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN	     | PK_DEPT |     4 |       |     1	 (0)| 00:00:01 |
|*  4 |   SORT JOIN		     |	       |    14 |   126 |     4	(25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL	     | EMP     |    14 |   126 |     3	 (0)| 00:00:01 |
---------------------------------------------------------------------------
复制

6.3.查询中存在group by子句

查询中存在GROUP BY子句，而索引中存在你GROUP BY子句中的列。在索引和GROUP BY 子句中，列的顺序不必相同。

SQL> select j.object_id,count(1) from scott.jason j group by j.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 3955111463
---------------------------------------------------------------------------
| Id  | Operation	     | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |		    | 86981 |	424K|	297   (1)| 00:00:04 |
|   1 |  SORT GROUP BY NOSORT|		    | 86981 |	424K|	297   (1)| 00:00:04 |
|   2 |   INDEX FULL SCAN    | PK_OBJECT_ID | 86981 |	424K|	297   (1)| 00:00:04 |
---------------------------------------------------------------------------
复制

如果执行计划中出现了全索引扫描，那么我们需要关注是否存在TABLE ACCESS BY INDEX ROWID回表操作，INDEX FULL SCAN是单块读，回表也是单块读，将严重影响性能，此时更应该进行全表扫描，因为全表扫描是多块读。

7.索引快速全扫描 FAST FULL INDEX SCAN

索引列包含了SQL查询需要的所有数据，而不需要回表访问，且索引包含的列中至少有一列具有NOT NULL约束。等待事件为db file scattered read,以多块读的方式读取。

SQL> select object_id from scott.jason;
Execution Plan
----------------------------------------------------------
Plan hash value: 3528708852

---------------------------------------------------------------------------
| Id  | Operation	     | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |		    | 86981 |	424K|	 82   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| PK_OBJECT_ID | 86981 |	424K|	 82   (0)| 00:00:01 |
---------------------------------------------------------------------------
复制

当SQL查询需要返回大量数据时，通常情况下，我们可以利用索引快速全扫描特性，创建组合索引来避免回表访问。

SQL> select owner from scott.jason where object_id > 1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 1258222954
---------------------------------------------------------------------------
| Id  | Operation	  | Name  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	  | 86006 |   923K|   342   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| JASON | 86006 |   923K|   342   (1)| 00:00:05 |
---------------------------------------------------------------------------
SQL> create index idx_owner_objectid on scott.jason(owner,object_id);
SQL> select owner from scott.jason where object_id > 1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3138678440
---------------------------------------------------------------------------
| Id  | Operation	     | Name		  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |			  | 86006 |   923K|    73   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IDX_OWNER_OBJECTID | 86006 |   923K|    73   (0)| 00:00:01 |
---------------------------------------------------------------------------

--hint
HINT INDEX_FFS(table_alias index_name)
复制

注：本文参考于：《DBA攻坚指南》