1. 基础知识
B*树的构造类似于二叉树,能根据键提供一行或一个行集的快速访问,通常只需很少的读操作就能找到正确的行。
不过,”B*树“中的”B“不代表二叉(binary),而代表平衡(balanced)。B*树索引并不是一颗二叉树。在一个数字列上建立的索引类似于下图:叶子节点之上的内部块称为分支块(branch block)。这些节点用于在结构中实现导航。
最底层的块称为叶子节点(leaf node)或叶子块(leaf block),其中分别包含各个索引键以及一个rowid(指向所索引的行)。
索引的叶子节点实际上构成了一个双向链表,这样有利于执行索引区间扫描(index range scan)以满足类似"where x between 20 and 30"的查询。
所有叶子块都在树的同一层上,从索引的根块到叶子块的遍历都会访问同样数目的块。这一层也称为索引的高度(height)。大多数B*树索引的高度都是2或者3。
另一个术语是BLEVEL,这是指分支层数,与HEIGHT相差1(BLEVEL不把叶子块层算在内)。
通过sql 查询索引信息
analyze index IDX_TEST1 validate structure;
selectHEIGHT,LF_ROWS,LF_BLKS,BR_BLKS,DISTINCT_KEYS from index_stats;
树高对性能影响不大,使用索引进行范围扫描时,只有最开始才从根开始,经过分支块,然后扫描叶块,到叶块后进行连续扫描知道结束为止。
2. B* 索引
1) 创建索引时,尽量使用小的pctfree值,设置为0也行。因为索引列update概率小。
2) 灵活运用压缩方法,尤其在组合索引中。
create index ord_custmor_idex on order(customerid,saleid) compress 1;
3) 插入新行,对索引影响很大,需要拆分索引叶块,分配一个新的索引叶块。
3. 何时采用B* 树索引
有两种适合使用B*树索引的方法,
1)仅通过索引回答一个查询
索引包含了足够的信息来回答整个查询,根本不用去访问表。在这种情况下,索引则用作一个“较瘦“版本的表。
2)通过读索引来访问表中的少部分行
此时应当只访问问表中很少的一部分行(只占一个很小的百分比)。如果需要通过索引访问大量的行,那么与全表扫描相比,通过索引反而要花更多时间。
这是因为,一方面,增加了读取索引本身的数据块的I/O,另一方面,索引按索引键的顺序存储,会按键的有序顺序进行访问,索引指向的块则随机地存储在堆中,因此通过索引访问表时,会执行大量分散、随机的I/O。
当Oracle对索引结构执行区间扫描时,如果它发现索引中的下一行和前一行在同一个数据库块上,就不会再执行另一个I/O从缓冲区缓存中获得表块,它已经有表块的一个句柄,只需直接使用就可以了。不过,如果下一行不在同一个块上,就会释放当前的这个块,而执行另一个I/O从缓冲区缓存获取要处理的下一个块。因此,数据在磁盘上如何物理存储,也会极大影响到通过索引访问数据的开销。
下面创建表格COLOCATED,按照主键X递增(1->1000)的顺序插入,因此在磁盘上一般也是按照主键顺序存放(并不能保证一定如此,若要保证这一点,需要使用IOT)。
另外创建表格DISORGANIZED,把COLOCATED表格中的数据全部插入此表,但是按照随机数列Y的顺序插入,这样对于主键X来说,表格中的数据是分散的。
- tony@ORA11GR2> create table colocated as
- 2 select rownum x, dbms_random.random y
- 3 from dual connect by rownum <= 1000;
- Table created.
- tony@ORA11GR2> alter table colocated
- 2 add constraint colocated_pk primary key(x);
- Table altered.
- tony@ORA11GR2> create table disorganized as
- 2 select x,y from colocated order by y;
- Table created.
- tony@ORA11GR2> alter table disorganized
- 2 add constraint disorganized_pk primary key(x);
- Table altered.
- tony@ORA11GR2> exec dbms_stats.gather_table_stats(user, 'COLOCATED', cascade=>true);
- PL/SQL procedure successfully completed.
- tony@ORA11GR2> exec dbms_stats.gather_table_stats(user, 'DISORGANIZED', cascade=>true);
- PL/SQL procedure successfully completed.
先来看看访问表格中的行的比例对查询计划的影响。
- tony@ORA11GR2> select * from colocated where x between 100 and 400;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1550765370
- -----------------------------------------------------------------------------------
- | Id | Operation |Name |Rows |Bytes|Cost (%CPU)|Time |
- -----------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 302| 3322| 3 (0)|00:00:01|
- | 1 | TABLE ACCESS BY INDEX ROWID|COLOCATED | 302| 3322| 3 (0)|00:00:01|
- |* 2 | INDEX RANGE SCAN |COLOCATED_PK| 302| | 2 (0)|00:00:01|
- -----------------------------------------------------------------------------------
- tony@ORA11GR2> select * from colocated where x between 100 and 500;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 4293073267
- -------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 402 | 4422 | 3 (0)| 00:00:01 |
- |* 1 | TABLE ACCESS FULL| COLOCATED | 402 | 4422 | 3 (0)| 00:00:01 |
- -------------------------------------------------------------------------------
可以看到访问30%的行的时候,Oracle选择的查询计划是INDEX RANGE SCAN,访问40%的行的时候,查询计划变成了TABLE ACCESS FULL。
- tony@ORA11GR2> select * from disorganized where x between 100 and 200;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2727546897
- ----------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 102 | 1122 | 3 (0)| 00:00:01 |
- |* 1 | TABLE ACCESS FULL| DISORGANIZED | 102 | 1122 | 3 (0)| 00:00:01 |
- ----------------------------------------------------------------------------------
但是对于DISORGANIZED表格,即使只访问10%的表格,Oracle选择的查询计划也是全表扫描。
为什么会有这个差别?先来了解聚簇因子的概念。
聚簇因子(CLUSTERING_FACTOR)
USER_INDEXES视图中有一列叫做聚簇因子,它的含义是指示表中行的有序程度:
· 如果这个值与块数接近,则说明表相当有序,得到了很好的组织,在这种情况下,同一个叶子块中的索引条目可能指向同一个数据块上的行。
· 如果这个值与行数接近,表的次序可能就是非常随机的。在这种情况下,同一个叶子块上的索引条目不太可能指向同一个数据块上的行。
- tony@ORA11GR2> select idx.index_name, tbl.num_rows, tbl.blocks, idx.clustering_factor
- from user_indexes idx, user_tables tbl
- where idx.table_name=tbl.table_name
- and idx.index_name in ('COLOCATED_PK', 'DISORGANIZED_PK');
- INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
- ---------------------------------------- ---------- ---------- -----------------
- COLOCATED_PK 1000 6 3
- DISORGANIZED_PK 1000 6 587
可以看到DISORGANIZED表的聚簇因子相当大,因此Oracle选择全表扫描的计划。
可以把聚簇因子看作是通过索引读取整个表时对表执行的逻辑I/O次数。
(COLOCATED表的聚簇因子小于表中的块数。这是因为使用了ASSM管理的表空间,COLOCATED表中在HWM之下有一些未格式化的块,其中未包含数据,而且ASSM 本身也使用了一些块来管理空间,索引区间扫描中不会读取这些块。)
下面通过TKPROF查看,对于上面的2张表,通过索引读取整个表各需要执行多少次I/O。
- select count(y)
- from
- (select /*+ index(disorganized, disorganized_pk) */ * from disorganized)
- call count cpu elapsed disk query current rows
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
- Parse 1 0.00 0.00 0 0 0 0
- Execute 1 0.00 0.00 0 0 0 0
- Fetch 2 0.00 0.00 0 590 0 1
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
- total 4 0.00 0.00 0 590 0 1
- Misses in library cache during parse: 1
- Optimizer mode: ALL_ROWS
- Parsing user id: 85
- Rows Row Source Operation
- ------- ---------------------------------------------------
- 1 SORT AGGREGATE (cr=590 pr=0 pw=0 time=0 us)
- 1000 TABLE ACCESS BY INDEX ROWID DISORGANIZED (cr=590 pr=0 pw=0 time=11738 us cost=590 size=8000 card=1000)
- 1000 INDEX FULL SCAN DISORGANIZED_PK (cr=3 pr=0 pw=0 time=2247 us cost=3 size=0 card=1000)(object id 73839)
- ********************************************************************************
- SQL ID: 3tv5g4y1p2tft
- Plan Hash: 3483305348
- select count(y)
- from
- (select /*+ index(colocated, colocated_pk) */ * from colocated)
- call count cpu elapsed disk query current rows
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
- Parse 1 0.01 0.00 0 0 0 0
- Execute 1 0.00 0.00 0 0 0 0
- Fetch 2 0.00 0.00 0 6 0 1
- ------- ------ -------- ---------- ---------- ---------- ---------- ----------
- total 4 0.01 0.00 0 6 0 1
- Misses in library cache during parse: 1
- Optimizer mode: ALL_ROWS
- Parsing user id: 85
- Rows Row Source Operation
- ------- ---------------------------------------------------
- 1 SORT AGGREGATE (cr=6 pr=0 pw=0 time=0 us)
- 1000 TABLE ACCESS BY INDEX ROWID COLOCATED (cr=6 pr=0 pw=0 time=5994 us cost=6 size=8000 card=1000)
- 1000 INDEX FULL SCAN COLOCATED_PK (cr=3 pr=0 pw=0 time=1998 us cost=3 size=0 card=1000)(object id 73837)
可以看到,
对于DISORGANIZED表,总共执行590(cr=590)次一致读,如果减去索引扫描的3次,访问表格的一致读次数为587;
对于COLOCATED表,总共执行6(cr=6)次一致读,如果减去索引扫描的3次,访问表格的一致读次数为3;
和聚簇因子相等。
总结一下,索引并不一定总是合适的访问方法。影响优化器是否采用索引因素很多,包括数据的物理存储布局。
如果完全适用索引就可以回答查询,那么访问大量的行就是有意义的,
如果使用索引来访问表(即查询计划中的TABLE ACCESS BY INDEX ROWID),那么只适合访问少量的行。