Oracle CBO优化模式中的5种索引访问方法浅析
数据库 / 管理员 发布于 6年前 282
本文主要讨论以下几种索引访问方法：
1.索引唯一扫描(INDEX UNIQUE SCAN)
2.索引范围扫描(INDEX RANGE SCAN)
3.索引全扫描(INDEX FULL SCAN)
4.索引跳跃扫描(INDEX SKIP SCAN)
5.索引快速全扫描(INDEX FAST FULL SCAN)
索引唯一扫描(INDEX UNIQUE SCAN)
通过这种索引访问数据的特点是对于某个特定的值只返回一行数据，通常如果在查询谓语中使用UNIQE和PRIMARY KEY索引的列作为条件的时候会选用这种扫描；访问的高度总是索引的高度加一，除了某些特殊的情况，如另外存储的LOB对象。
复制代码 代码如下:
SQL> set autotrace traceonly explain
SQL> select * from hr.employees where employee_id = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
索引范围扫描(INDEX RANGE SCAN)
谓语中包含将会返回一定范围数据的条件时就会选用索引范围扫描，索引可以是唯一的亦可以是不唯一的；所指定的条件可以是(<,>,LIKE,BETWEEN,=)等运算符，不过使用LIKE的时候，如果使用了通配符%，极有可能就不会使用范围扫描，因为条件过于的宽泛了，下面是一个示例：
复制代码 代码如下:
SQL> select * from hr.employees where DEPARTMENT_ID = 30;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2056577954
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 414 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 414 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 6 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=30)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
7 consistent gets
1 physical reads
0 redo size
1716 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
范围扫描的条件需要准确的分析返回数据的数目，范围越大就越可能执行全表扫描；
复制代码 代码如下:
SQL> select department_id,count(*) from hr.employees group by department_id order by count(*);
DEPARTMENT_ID COUNT(*)
------------- ----------
10 1
40 1
1
70 1
20 2
110 2
90 3
60 5
30 6
100 6
80 34
50 45
12 rows selected.
-- 这里使用数值最多的50来执行范围扫描
SQL> set autotrace traceonly explain
SQL> select * from hr.employees where DEPARTMENT_ID = 50;
45 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45 | 3105 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPARTMENT_ID"=50)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
4733 bytes sent via SQL*Net to client
545 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
45 rows processed
可以看到在获取范围数据较大的时候，优化器还是执行了全表扫描方法。
一种对于索引范围扫描的优化方法是使用升序排列的索引来获得降序排列的数据行，这种情况多发生在查询中包含有索引列上的ORDER BY子句的时候，这样就可避免一次排序操作了，如下：
复制代码 代码如下:
SQL> set autotrace traceonly explain
SQL> select * from hr.employees
2 where department_id in (90, 100)
3 order by department_id desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 3707994525
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 621 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 9 | 621 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN DESCENDING| EMP_DEPARTMENT_IX | 9 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"=90 OR "DEPARTMENT_ID"=100)
上例中，索引条目被相反的顺序读取，避免了排序操作。
索引全扫描(INDEX FULL SCAN)
索引全扫描的操作将会扫描索引结构的每一个叶子块，读取每个条目的的行编号，并取出数据行，既然是访问每一个索引叶子块，那么它相对的全表扫描的优势在哪里呢？实际上在索引块中因为包含的信息列数较少，通常都是索引键和ROWID，所以对于同一个数据块和索引块，包含的索引键的条目数通常都是索引块中居多，因此如果查询字段列表中所有字段都是索引的一部分的时候，就可以完全跳过对表数据的访问了，这种情况索引全扫描的方法会获得更高的效率。
发生索引全扫描的情况有很多，几种典型的场景：
1，查询总缺少谓语，但获取的列可以通过索引直接获得
复制代码 代码如下:
SQL> select email from hr.employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 2196514524
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 856 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | EMP_EMAIL_UK | 107 | 856 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
2，查询谓语中包含一个位于索引中非引导列上的条件(其实也取决于引导列值的基数大小，如果引导列的唯一值较少，也可能出现跳跃扫描的情况)
复制代码 代码如下:
SQL> select first_name, last_name from hr.employees
2 where first_name like 'A%' ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2228653197
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 45 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | EMP_NAME_IX | 3 | 45 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("FIRST_NAME" LIKE 'A%')
filter("FIRST_NAME" LIKE 'A%')
SQL> SET LONG 2000000
SQL> select dbms_metadata.get_ddl('INDEX','EMP_NAME_IX','HR') from dual;
DBMS_METADATA.GET_DDL('INDEX','EMP_NAME_IX','HR')
--------------------------------------------------------------------------------
CREATE INDEX "HR"."EMP_NAME_IX" ON "HR"."EMPLOYEES" ("LAST_NAME", "FIRST_NAME"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE"
-- 可以看到EMP_NAME_IX索引是建立在列(("LAST_NAME", "FIRST_NAME")上的，使用了带非引导列FIRST_NAME的谓语
3，数据通过一个已经排序的索引获得从而省去单独的排序操作
复制代码 代码如下:
SQL> select * from hr.employees order by employee_id ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2186312383
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | EMP_EMP_ID_PK | 107 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
-- 同样可以使用升序索引返回降序数据
SQL> select employee_id from hr.employees order by employee_id desc ;
Execution Plan
----------------------------------------------------------
Plan hash value: 753568220
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 428 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN DESCENDING| EMP_EMP_ID_PK | 107 | 428 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
在上面的例子中可以看出，索引全扫描也可以想范围扫描一样，通过升序索引返回降序数据，而它的优化不止这一种，当我们查询某一列的最大值或最小值而这一列又是索引列的时候，索引全扫描就会获得非常显著的优势，因为这时的优化器并没有对索引的数据进行全部叶子节点的检索，而只是对一个根块，第一个或最后一个叶子块的扫描，这无疑会显著的提高性能！！
复制代码 代码如下:
-- 索引全扫描获得最小值
SQL> select min(department_id) from hr.employees ;
Execution Plan
----------------------------------------------------------
Plan hash value: 613773769
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX | 1 | 3 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
-- 如果同时包含MAX和MIN的求值，优化器并不会主动选择效率较高的索引全扫描方法
SQL> select min(department_id), max(department_id) from hr.employees ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1756381138
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-- 一种替代的优化方案
SQL> select
2 (select min(department_id) from hr.employees) min_id,
3 (select max(department_id) from hr.employees) max_id
4 from dual;
Execution Plan
----------------------------------------------------------
Plan hash value: 2189307159
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX | 1 | 3 | 1 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 3 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX | 1 | 3 | 1 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
索引跳跃扫描(INDEX SKIP SCAN)
这种扫描方式也是一种特例，因为在早期的版本中，优化器会因为使用了非引导列而拒绝使用索引。跳跃扫描的前提有着对应的情景，当谓语中包含索引中非引导列上的条件，并且引导列的唯一值较小的时候，就有极有可能使用索引跳跃扫描方法；同索引全扫描，范围扫描一样，它也可以升序或降序的访问索引；不同的是跳跃扫描会根据引导列的唯一值数目将复合索引分成多个较小的逻辑子索引，引导列的唯一值数目越小，分割的子索引数目也就越少，就越可能达到相对全表扫描较高的运算效率。
复制代码 代码如下:
-- 创建测试表，以dba_objects表为例
SQL> create table test as select * from dba_objects;
Table created.
-- 创建一个复合索引，这里选取了一个唯一值较少的owner列作为引导列
SQL> create index i_test on test(owner,object_id,object_type) ;
Index created.
-- 分析表收集统计信息
SQL> exec dbms_stats.gather_table_stats('SYS','TEST');
PL/SQL procedure successfully completed.
-- 先看一下引导列的唯一值的比较
SQL> select count(*),count(distinct owner) from test;
COUNT(*) COUNT(DISTINCTOWNER)
---------- --------------------
72482 29
-- 使用非引导列的条件查询来访问触发SKIP SCAN
SQL> select * from test where object_id = 46;
Execution Plan
----------------------------------------------------------
Plan hash value: 1001786056
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 31 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 97 | 31 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | I_TEST | 1 | | 30 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=46)
filter("OBJECT_ID"=46)
Statistics
----------------------------------------------------------
101 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
1610 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
-- 来看看这条语句全扫描的效率
SQL> select /*+ full(test) */ * from test where object_id = 46;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 282 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 97 | 282 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=46)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1037 consistent gets
0 physical reads
0 redo size
1607 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
分析上面的查询可以看出，我们使用的索引中引导列有29个唯一值，也就是说在执行索引跳跃扫描的时候，分割成了29个逻辑子索引来查询，只产生了38次逻辑读；而相对全表扫描的1037次逻辑读，性能提升非常明显！
索引快速全扫描(INDEX FAST FULL SCAN)
这种访问方法在获取数据上和全表扫描相同，都是通过无序的多块读取来进行的，因此也就无法使用它来避免排序代价了；索引快速全扫描通常发生在查询列都在索引中并且索引中一列有非空约束时，当然这个条件也容易发生索引全扫描，它的存在多可用来代替全表扫描，比较数据获取不需要访问表上的数据块。
复制代码 代码如下:
-- 依旧使用上面创建的test表
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP &nbs
- 相关文章
- 分库分表的目的、优缺点及具体实现方式介绍(0个评论)
- DevDB - 在 VS 代码中直接访问数据库(0个评论)
- 在ubuntu系统中实现mysql数据存储目录迁移流程步骤(0个评论)
- 在mysql中使用存储过程批量新增测试数据流程步骤(0个评论)
- php+mysql数据库批量根据条件快速更新、连表更新sql实现(0个评论)
- 近期文章
- 在go语言中使用api.geonames.org接口实现根据国际邮政编码获取地址信息功能(1个评论)
- 在go语言中使用github.com/signintech/gopdf实现生成pdf分页文件功能(0个评论)
- gmail发邮件报错:534 5.7.9 Application-specific password required...解决方案(0个评论)
- 欧盟关于强迫劳动的规定的官方举报渠道及官方举报网站(0个评论)
- 在go语言中使用github.com/signintech/gopdf实现生成pdf文件功能(0个评论)
- Laravel从Accel获得5700万美元A轮融资(0个评论)
- 在go + gin中gorm实现指定搜索/区间搜索分页列表功能接口实例(0个评论)
- 在go语言中实现IP/CIDR的ip和netmask互转及IP段形式互转及ip是否存在IP/CIDR(0个评论)
- PHP 8.4 Alpha 1现已发布！(0个评论)
- Laravel 11.15版本发布 - Eloquent Builder中添加的泛型(0个评论)
- 近期评论
-
122 在
学历：一种延缓就业设计，生活需求下的权衡之选中评论 工作几年后，报名考研了，到现在还没认真学习备考，迷茫中。作为一名北漂互联网打工人.. -
123 在
Clash for Windows作者删库跑路了，github已404中评论 按理说只要你在国内，所有的流量进出都在监控范围内，不管你怎么隐藏也没用，想搞你分.. -
原梓番博客 在
在Laravel框架中使用模型Model分表最简单的方法中评论 好久好久都没看友情链接申请了，今天刚看，已经添加。.. -
博主 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 @1111老铁这个不行了，可以看看近期评论的其他文章.. -
1111 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 网站不能打开，博主百忙中能否发个APP下载链接，佛跳墙或极光..
Copyright·© 2019 侯体宗版权所有·
粤ICP备20027696号
