一文讲透Oracle索引执行类型（index unique scan,index range scan,index full scan,index fast full scan,index skip

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了一文讲透Oracle索引执行类型（index unique scan,index range scan,index full scan,index fast full scan,index skip，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5418字，纯文字阅读大概需要8分钟。

内容图文

直入主题，因为看不懂Oracle的执行计划/解释计划（explain plan），我摸着石头过河，终于整理出一点关于oracle各种索引执行类型的小心得，希望能帮到面向百度编程各位！

 

index unique scan -- 索引唯一扫描

条件：

1. 列的值唯一

2. 用 '=' 来查询

 

index range scan -- 索引局部扫描

条件：

1. 列的值唯一

2. 范围查询(>, <, and...)

or

1. 列的值不唯一

2. 所有查询(=, >, <, and...)

 

index full scan -- 索引全局扫描

条件：

1. 必须是组合索引

2. 引导列不在where条件中

PS: 当进行index full scan的时候 oracle定位到索引的root block，然后到branch block（如果有的话），再定位到第一个leaf block, 然后根据leaf block的双向链表顺序读取。它所读取的块都是有顺序的，也是经过排序的。

 

index fast full scan -- 索引快速全局扫描，不带order by 情况下常发生

如果select 语句后面中的列都被包含在组合索引中，而且where后面没有出现组合索引的引导列，并且需要检索出大部分数据，那么这个时候可能执行index fast full scan

条件：

1. 必须是组合索引

2. 引导列不在where条件中

PS: index fast full scan则不同，它是从段头开始，读取包含位图块，root block, 所有的branch block, leaf block，读取的顺序完全由物理存储位置决定，并采取多块读，每次读取db_file_multiblock_read_count个。

 

所以：
1. 索引全扫描要排序，索引快速全扫描不用排序（索引全扫描会按照叶子块排序返回，而索引快速全扫描则是按照索引段内存储块顺序返回）。
2. 索引全扫描不得读取索引段头，而索引快速全扫描要读取索引段头

 

index skip scan -- 索引跳跃扫描，where 条件列是非索引的前导列情况下常发生

当查询可以通过组合索引得到结果，而且返回结果很少，并且where条件中没有包含索引引导列的时候，可能执行index skip scan

条件：

1. 必须是组合索引

2. 引导列不在where条件中

 

by index Rowid 列名 --Rowid 扫描是最快的访问数据方式

用主键查询或者回表查询时会出现

 

 

下列是我遇到的问题：

 

原SQL:

select accountid, validRecord from bi_TINbatchimportrecord 
where batchid = 'HK#ACCOUNT-BATCH#20190501#96732a40-aebe-4354-b83d-3c6f781c6df1'
group by accountid, validrecord ;

原索引：

INDEX1 (BATCHID, ACCOUNTID, IMPORTTYPE, BUSINESSKEY)

INDEX2 (ACCOUNTID, BATCHID, VALIDRECORD, VALIDBATCHID)

 

问题 : 以为会用INDEX1，但用的是INDEX2

 

=================== 目前 INDEX2 ===================

用到INDEX2作为索引，以INDEX FAST FULL SCAN/INDEX SKIP SCAN的方式(where没有引导列，且索引是覆盖索引，根据索引具体COST来决定用哪种方式)

-- INDEX FAST FULL SCAN:
INDEX2索引树全树无序扫描，采取多块读的方式

-- INDEX SKIP SCAN：

select accountid, validRecord from bi_TINbatchimportrecord
where batchid = 'batchid'
----- Oracle优化 ----->
select accountid, validRecord from bi_TINbatchimportrecord
where accountid = 'acctid1' and batchid = 'batchid'
union
select accountid, validRecord from bi_TINbatchimportrecord
where accountid = 'acctid2' and batchid = 'batchid'
union
......

所以，accountid越集中，union次数越少，效率越高

 

=================== 强制 INDEX1 ===================

** 根据我当时浅薄的认知，复合索引应该依照最左原则，哪个复合索引的引导项是batchid，就应该用哪个

若强制使用INDEX1，用到BATCHID作为索引，先以RANGE SCAN的方式查到（ID, BATCHID, ACCOUNTID, IMPORTTYPE, BUSINESSKEY）,找不到需要的validRecord，
再进行回表查询，效率取决于回表查的数据量

所以，索引不是覆盖索引的前提下，通过查出来 BATCHID='xxx' 的记录数越多，需要回表查询的记录越多，数据库是按大小分成数据片的，每次IO取一片，意味着回表记录越多，IO消耗越大

 

解决方案：

 

新增索引：

INDEX3 ( BATCHID, ACCOUNTID, VALIDRECORD)

=================== DB引擎自动选择 INDEX3 作为索引 ===================

因为：

1. 其是覆盖索引，复合索引的列涵盖了所有搜索列和条件列

2. 符合复合索引的最左原则

 

所以：

以INDEX RANGE SCAN执行

 

=================== 两组实验数据 ===================

env -> SIT HK HF

----- 第一组 -----
BATCHID2 = 'HK#ACCOUNT-BATCH#20180905#3df3e64a-1a42-4a73-9804-ba32813d2ea7' -> 326

INDEX1 COST = 10(索引查询IO) + 305(回表IO) + 1(无序Group by的CPU占用) = 316
INDEX2 COST = 4319(无引导列且覆盖索引查询IO) + 1(无序Group by的CPU占用) = 4320
INDEX3 COST = 8(有引导列且覆盖索引查询IO) + 0(有序Group by的CPU占用) = 8
no index COST = 67259(全表扫描IO) + 1(无序Group by的CPU占用) = 67260

----- 第二组 -----
BATCHID1 = 'HK#ACCOUNT-BATCH#20190501#96732a40-aebe-4354-b83d-3c6f781c6df1' -> 231566

INDEX1 COST = 3510(索引查询IO) + 213667(回表IO) + 1418(无序Group by的CPU占用) = 218595
INDEX2 COST = 4319(无引导列且覆盖索引查询IO) + 1342(无序Group by的CPU占用) = 5661
INDEX3 COST = 2870(有引导列且覆盖索引查询IO) + 0(有序Group by的CPU占用) = 2870
no index COST = 67260(全表扫描IO) + 1341(无序Group by的CPU占用) = 68610

 

=================== COST ===================
INDEX1 cost：索引查询IO + 回表IO + 条件查询CPU消耗

INDEX2 cost: 无引导列且覆盖索引查询IO + 条件查询CPU消耗

INDEX3 cost: 有引导列且覆盖索引查询IO + 条件查询CPU消耗

no index cost：全表扫描IO + 条件查询CPU消耗

=================== 两组实验数据 ===================

 

----- 第一组 -----
BATCHID2 = 'HK#ACCOUNT-BATCH#20180905#3df3e64a-1a42-4a73-9804-ba32813d2ea7' -> 326

INDEX1 COST = 10(索引查询IO) + 305(回表IO) + 1(无序Group by的CPU占用) = 316
INDEX2 COST = 4319(无引导列且覆盖索引查询IO) + 1(无序Group by的CPU占用) = 4320
INDEX3 COST = 8(有引导列且覆盖索引查询IO) + 0(有序Group by的CPU占用) = 8
no index COST = 67259(全表扫描IO) + 1(无序Group by的CPU占用) = 67260

----- 第二组 -----
BATCHID1 = 'HK#ACCOUNT-BATCH#20190501#96732a40-aebe-4354-b83d-3c6f781c6df1' -> 231566

INDEX1 COST = 3510(索引查询IO) + 213667(回表IO) + 1418(无序Group by的CPU占用) = 218595
INDEX2 COST = 4319(无引导列且覆盖索引查询IO) + 1342(无序Group by的CPU占用) = 5661
INDEX3 COST = 2870(有引导列且覆盖索引查询IO) + 0(有序Group by的CPU占用) = 2870
no index COST = 67260(全表扫描IO) + 1341(无序Group by的CPU占用) = 68610

内容总结

以上是互联网集市为您收集整理的一文讲透Oracle索引执行类型（index unique scan,index range scan,index full scan,index fast full scan,index skip全部内容，希望文章能够帮你解决一文讲透Oracle索引执行类型（index unique scan,index range scan,index full scan,index fast full scan,index skip所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---