本文介绍了Oracle数据库中index full scan和index fast full scan两种索引扫描方式的工作原理和适用场景。index full scan按照B+树结构随机访问单个索引块，适合数据量小的情况；而index fast full scan按extent顺序扫描，适用于全表统计操作，具有高吞吐量。同时，文章分享了Oracle BTREE索引创建的经验，强调索引列的选择和顺序对于查询性能的影响。
index full scan/index fast full scan以及B+树索引
了解index full scan/index fast full scan的读取方式与顺序
在很早之前，有网友的问题：
- 为何index full scan时不读取索引的segment header block？
A：网友的自答：从网上查到david dai说segment header block_id+1=index root block_id - 为何index fast full scan时，要读取索引的segment header block？
A：由于index fast full scan是按照extent的顺序对索引进行扫描(类似于全表扫描方式)，为了得到HWM，因此要先读取段头块来取得高水位线，以便判断扫描到哪里可以完成。
这两个access path方式，适用于B+Tree 索引和 IOT。
index full scan工作示意图(引自Oracle文档中图片):
index full scan一般会出现在表中数据相当少，需要访问全表或大部分表数据，且有排序操作时。总得来说因为是随机单数据块访问为主，因此大数据量情况下，性能极差。
index fast full scan一般出现在访问全表或大部分表数据，且是统计类操作，如sum/count/avg等聚合函数时，或者要访问的数据列都在一个索引中时。另外，当要求排序时，在index fast full scan取得无序的数据后需要额外进行排序步骤。由于是连续多块读，因此在大数据量下，吞吐量表现非常好。
注：在在线交易环境中，目前广泛使用B+树索引，因此建议基于数据库进行开发的，需要熟悉B+树索引的使用。
Oracle BTREE索引创建一般性经验(主要是前3条)
create [unique] index <index_name> on <table_name>(列1,列2, ...);
- 索引列一般顺序：一般先放where条件中“=”的列，然后有in相关列时，如in的值少，就跟着放，然后是范围条件的列。
- “=”条件的字段列里，哪个列的唯一性好就放前面。当其中有一个列是主键或唯一键时，或者非常接近唯一时，索引里只要有这个列就可以保证有效率定位数据，可以不用多个列的索引，在不降低效率的情况下减少索引的存放空间。
- 最后根据其他类似SQL综合考虑，尽量做到类似SQL能共用索引，以避免索引过多。也就是说可以一定程度调整第2条中描述的列顺序。
- 当一个SQL中的select list列以及where条件中的列都很少（不超过5个），这个SQL要求效率最优化，那么索引里可以顺带存放所有相关的列。这样可以直接从索引中获取所有数据，不再需要先从索引中定位到rowid，然后再回表中取得数据，减少了一个环节。
- where条件中有or，且or两边不是同一个列时（比如select * from tab where a=? or b=?），一般写为union形式，以便能对or两边不同条件使用不同索引。
- 当有order by col_name desc 时，col_name这个列尽量在需要使用的索引列中，一方面可以避免排序环节，另一方面避免SQL引擎去选择其他索引的INDEX FULL SCAN DESCENDING的数据访问路径。
- 在Oracle中无意义的自增ID列，并无过多作用。但在MySQL中由于数据同步需要及数据存放的组织形式的原因，从实际出发要求有主键，且最好是与业务意义无关的主键，避免对主键值的修改。关于MySQL的相关规范可以参考阿里巴巴开源的相关开发规范，比如嵩山版。这个规范是已被实践证明或理论预见性的总结，具有很高的参考借鉴价值。
以下步骤与信息供参考:
Testing on Oracle 11.2.0.1 on oel 5u6 32bit
Create table test (id number ,name varchar2(10));
Create index idx_test on test(id);
declare
i number;
begin
for i in 1..100000 loop
insert into test values(i,'aaaaa');
end loop;
commit;
end;
/
SQL> select segment_name,extent_id,file_id,block_id,blocks from dba_extents where segment_name='IDX_TEST' order by extent_id,block_id;
SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
-------------- ---------- ---------- ---------- ----------
IDX_TEST 0 4 176 8
IDX_TEST 1 4 184 8
IDX_TEST 2 4 208 8
IDX_TEST 3 4 224 8
IDX_TEST 4 4 240 8
IDX_TEST 5 4 256 8
IDX_TEST 6 4 272 8
IDX_TEST 7 4 288 8
IDX_TEST 8 4 304 8
IDX_TEST 9 4 320 8
IDX_TEST 10 4 336 8
IDX_TEST 11 4 352 8
IDX_TEST 12 4 376 8
IDX_TEST 13 4 392 8
IDX_TEST 14 4 408 8
IDX_TEST 15 4 416 8
IDX_TEST 16 4 640 128
//alter session set events 'immediate trace name treedump level index_object_id';
alter session set events 'immediate trace name treedump level 73514';
----- begin tree dump
branch: 0x10000b3 16777395 (0: nrow: 199, level: 1) 4,179(file_id,block_id)
leaf: 0x10000b5 16777397 (-1: nrow: 540 rrow: 540) 4,181
leaf: 0x10000b6 16777398 (0: nrow: 533 rrow: 533) 4,182
leaf: 0x10000b7 16777399 (1: nrow: 533 rrow: 533) 4,183
leaf: 0x10000b4 16777396 (2: nrow: 533 rrow: 533) 4,180
leaf: 0x10000b9 16777401 (3: nrow: 533 rrow: 533) 4,185
(略过)
leaf: 0x1000298 16777880 (193: nrow: 500 rrow: 500) 4,664
leaf: 0x100029c 16777884 (194: nrow: 500 rrow: 500) 4,668
leaf: 0x10002a0 16777888 (195: nrow: 500 rrow: 500) 4,672
leaf: 0x10002a4 16777892 (196: nrow: 500 rrow: 500) 4,676
leaf: 0x10002a8 16777896 (197: nrow: 374 rrow: 374) 4,680
index full scan运行并10046 trace记录，可见是按照index tree来进行访问的，以单个索引块访问为主
SQL> oradebug event 10046 trace name context forever,level 8;
Statement processed.
SQL> set autot on
SQL> select /*+ hard parse */ sum(id) from test.test;
SUM(ID)
----------
5000050000
Execution Plan
----------------------------------------------------------
Plan hash value: 1190062564
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN| IDX_TEST | 1 | 13 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
766 recursive calls
0 db block gets
320 consistent gets
250 physical reads
0 redo size
423 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> oradebug tracefile_name
/oracle/diag/rdbms/ora11g/ora11ga/trace/ora11ga_ora_29636.trc
SQL>
PARSING IN CURSOR #1 len=47 dep=0 uid=0 oct=3 lid=0 tim=1326249156378744 hv=3759626900 ad='4a9daa28' sqlid='00w7n53h1fqnn'
select /*+ hard parse */ sum(id) from test.test
END OF STMT
PARSE #1:c=45994,e=171263,p=10,cr=120,cu=0,mis=1,r=0,dep=0,og=1,plh=1190062564,tim=1326249156378740
EXEC #1:c=0,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1190062564,tim=1326249156379176
WAIT #1: nam='SQL*Net message to client' ela= 17 driver id=1650815232 #bytes=1 p3=0 obj#=528 tim=1326249156379254
WAIT #1: nam='Disk file operations I/O' ela= 20 FileOperation=2 fileno=4 filetype=2 obj#=73514 tim=1326249156379972
WAIT #1: nam='db file sequential read' ela= 15730 file#=4 block#=179 blocks=1 obj#=73514 tim=1326249156395888
WAIT #1: nam='db file scattered read' ela= 11995 file#=4 block#=180 blocks=4 obj#=73514 tim=1326249156410061
WAIT #1: nam='db file sequential read' ela= 1932 file#=4 block#=185 blocks=1 obj#=73514 tim=1326249156420016
WAIT #1: nam='db file scattered read' ela= 2071 file#=4 block#=186 blocks=6 obj#=73514 tim=1326249156422601
(略...)
WAIT #1: nam='db file sequential read' ela= 1166 file#=4 block#=656 blocks=1 obj#=73514 tim=1326249156691743
WAIT #1: nam='db file scattered read' ela= 1828 file#=4 block#=657 blocks=7 obj#=73514 tim=1326249156694126
WAIT #1: nam='db file sequential read' ela= 1867 file#=4 block#=664 blocks=1 obj#=73514 tim=1326249156697179
WAIT #1: nam='db file scattered read' ela= 1286 file#=4 block#=665 blocks=7 obj#=73514 tim=1326249156698744
WAIT #1: nam='db file scattered read' ela= 686 file#=4 block#=672 blocks=3 obj#=73514 tim=1326249156700118
WAIT #1: nam='db file scattered read' ela= 2971 file#=4 block#=680 blocks=3 obj#=73514 tim=1326249156703653
FETCH #1:c=83987,e=325135,p=240,cr=200,cu=0,mis=0,r=1,dep=0,og=1,plh=1190062564,tim=1326249156704441
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=200 pr=240 pw=0 time=0 us)'
STAT #1 id=2 cnt=100000 pid=1 pos=1 obj=73514 op='INDEX FULL SCAN IDX_TEST (cr=200 pr=240 pw=0 time=840145 us cost=0 size=13
card=1)'
WAIT #1: nam='SQL*Net message from client' ela= 104348 driver id=1650815232 #bytes=1 p3=0 obj#=73514 tim=1326249156809521
index fast full scan的10046 trace，可见是以dba_extents的顺序进行全索引扫描的，以多块连续块为主
WAIT #1: nam='Disk file operations I/O' ela= 32 FileOperation=2 fileno=4 filetype=2 obj#=73514 tim=1326255179693530
WAIT #1: nam='db file sequential read' ela= 17150 file#=4 block#=178 blocks=1 obj#=73514 tim=1326255179710842
WAIT #1: nam='db file scattered read' ela= 908 file#=4 block#=179 blocks=5 obj#=73514 tim=1326255179713085
WAIT #1: nam='db file scattered read' ela= 658 file#=4 block#=184 blocks=8 obj#=73514 tim=1326255179714982
WAIT #1: nam='db file scattered read' ela= 961 file#=4 block#=209 blocks=7 obj#=73514 tim=1326255179717812
WAIT #1: nam='db file scattered read' ela= 12179 file#=4 block#=224 blocks=8 obj#=73514 tim=1326255179737346
WAIT #1: nam='db file scattered read' ela= 2237 file#=4 block#=241 blocks=7 obj#=73514 tim=1326255179743513
WAIT #1: nam='db file scattered read' ela= 17146 file#=4 block#=256 blocks=8 obj#=73514 tim=1326255179763194
WAIT #1: nam='db file scattered read' ela= 627 file#=4 block#=273 blocks=7 obj#=73514 tim=1326255179765217
WAIT #1: nam='db file scattered read' ela= 979 file#=4 block#=288 blocks=8 obj#=73514 tim=1326255179767858
WAIT #1: nam='db file scattered read' ela= 1139 file#=4 block#=305 blocks=7 obj#=73514 tim=1326255179770925
WAIT #1: nam='db file scattered read' ela= 8763 file#=4 block#=320 blocks=8 obj#=73514 tim=1326255179781078
WAIT #1: nam='db file scattered read' ela= 951 file#=4 block#=337 blocks=7 obj#=73514 tim=1326255179783339
WAIT #1: nam='db file scattered read' ela= 952 file#=4 block#=352 blocks=8 obj#=73514 tim=1326255179785800
WAIT #1: nam='db file scattered read' ela= 1288 file#=4 block#=377 blocks=7 obj#=73514 tim=1326255179789395
WAIT #1: nam='db file scattered read' ela= 6932 file#=4 block#=392 blocks=8 obj#=73514 tim=1326255179798934
WAIT #1: nam='db file scattered read' ela= 2100 file#=4 block#=409 blocks=7 obj#=73514 tim=1326255179803417
WAIT #1: nam='db file scattered read' ela= 716 file#=4 block#=416 blocks=8 obj#=73514 tim=1326255179805893
WAIT #1: nam='db file scattered read' ela= 34588 file#=4 block#=642 blocks=126 obj#=73514 tim=1326255179843946
FETCH #1:c=63989,e=177100,p=245,cr=251,cu=0,mis=0,r=1,dep=0,og=1,plh=3508397080,tim=1326255179870095
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
