（转）深入研究B树索引（五）续

5.3 重建 B 树索引对于查询性能的影响

       最后我们来看一下重建索引对于性能的提高到底会有什么作用。假设我们有一个表，该表具有 1 百万条记录，占用了 100000 个数据块。而在该表上存在一个索引，在重建之前的 pct_used 为 50% ，高度为 3 ，分支节点块数为 40 个，再加一个根节点块，叶子节点数为 10000 个；重建该索引以后， pct_used 为 90% ，高度为 3 ，分支节点块数下降到 20 个，再加一个根节点块，而叶子节点数下降到 5000 个。那么从理论上说：

1）  如果通过索引获取单独 1 条记录来说：

重建之前的成本： 1 个根＋ 1 个分支＋ 1 个叶子＋ 1 个表块＝ 4 个逻辑读

重建之后的成本： 1 个根＋ 1 个分支＋ 1 个叶子＋ 1 个表块＝ 4 个逻辑读

性能提高百分比： 0

2）  如果通过索引获取 100 条记录（占总记录数的 0.01% ）来说，分两种情况：

最差的 clustering_factor （即该值等于表的数据行数）：

重建之前的成本： 1 个根＋ 1 个分支＋ 0.0001*10000 （ 1 个叶子）＋ 100 个表块＝ 103 个逻辑读

重建之后的成本： 1 个根＋ 1 个分支＋ 0.0001*5000 （ 1 个叶子）＋ 100 个表块＝ 102.5 个逻辑读

性能提高百分比： 0.5% （也就是减少了 0.5 个逻辑读）

最好 clustering_factor （即该值等于表的数据块）：

重建之前的成本： 1 个根＋ 1 个分支＋ 0.0001*10000 （ 1 个叶子）＋ 0.0001*100000 （ 10 个表块）＝ 13 个逻辑读

重建之后的成本： 1 个根＋ 1 个分支＋ 0.0001*5000 （ 1 个叶子）＋ 0.0001*100000 （ 10 个表块）＝ 12.5 个逻辑读

性能提高百分比： 3.8% （也就是减少了 0.5 个逻辑读）

3）  如果通过索引获取 10000 条记录（占总记录数的 1% ）来说，分两种情况：

最差的 clustering_factor （即该值等于表的数据行数）：

重建之前的成本： 1 个根＋ 1 个分支＋ 0.01*10000 （ 100 个叶子）＋ 10000 个表块＝ 10102 个逻辑读

重建之后的成本： 1 个根＋ 1 个分支＋ 0.01*5000 （ 50 个叶子）＋ 10000 个表块＝ 10052 个逻辑读

性能提高百分比： 0.5% （也就是减少了 50 个逻辑读）

最好 clustering_factor （即该值等于表的数据块）：

重建之前的成本： 1 个根＋ 1 个分支＋ 0.01*10000 （ 100 个叶子）＋ 0.01*100000 （ 1000 个表块）＝ 1102 个逻辑读

重建之后的成本： 1 个根＋ 1 个分支＋ 0.01*5000 （ 50 个叶子）＋ 0.01*100000 （ 1000 个表块）＝ 1052 个逻辑读

性能提高百分比： 4.5% （也就是减少了 50 个逻辑读）

4）  如果通过索引获取 100000 条记录（占总记录数的 10% ）来说，分两种情况：

最差的 clustering_factor （即该值等于表的数据行数）：

重建之前的成本： 1 个根＋ 1 个分支＋ 0.1*10000 （ 1000 个叶子）＋ 100000 个表块＝ 101002 个逻辑读

重建之后的成本： 1 个根＋ 1 个分支＋ 0.1*5000 （ 500 个叶子）＋ 100000 个表块＝ 100502 个逻辑读

性能提高百分比： 0.5% （也就是减少了 500 个逻辑读）

最好 clustering_factor （即该值等于表的数据块）：

重建之前的成本： 1 个根＋ 1 个分支＋ 0.1*10000 （ 1000 个叶子）＋ 0.1*100000 （ 10000 个表块）＝ 11002 个逻辑读

重建之后的成本： 1 个根＋ 1 个分支＋ 0.1*5000 （ 500 个叶子）＋ 0.1*100000 （ 10000 个表块）＝ 10502 个逻辑读

性能提高百分比： 4.5% （也就是减少了 500 个逻辑读）

5）  对于快速全索引扫描来说，假设每次获取 8 个数据块：

重建之前的成本：（ 1 个根＋ 40 个分支＋ 10000 个叶子） / 8 ＝ 1256 个逻辑读

重建之后的成本：（ 1 个根＋ 40 个分支＋ 5000 个叶子） / 8 ＝ 631 个逻辑读
性能提高百分比： 49.8% （也就是减少了 625 个逻辑读）

       从上面有关性能提高的理论描述可以看出，对于通过索引获取的记录行数不大的情况下，索引碎片对于性能的影响非常小；当通过索引获取较大的记录行数时，索引碎片的增加可能导致对于索引逻辑读的增加，但是索引读与表读的比例保持不变；同时，我们从中可以看到， clustering_factor 对于索引读取的性能有很大的影响，并且对于索引碎片所带来的影响具有很大的作用；最后，看起来，索引碎片似乎对于快速全索引扫描具有最大的影响。

       我们来看两个实际的例子，分别是 clustering_factor 为最好和最差的两个例子。测试环境为 8KB 的数据块，表空间采用 ASSM 的管理 方式。先做一个最好的 clustering_factor 的例子，创建测试表并填充 1 百万条数据。

SQL> create table rebuild_test(id number,name varchar2(10));

SQL> begin

 2    for i in 1..1000000 loop

 3        insert into rebuild_test values(i,to_char(i));

 4            if mod(i,10000)=0 then

 5                commit;

 6            end if;

 7    end loop;

 8 end;

 9 /

 

       该表具有 1 百万条记录，分布在 2328 个数据块中。同时由于我们的数据都是按照顺序递增插入的，所以可以知道，在 id 列上创建的索引都是具有最好的 clustering_factor 值的。我们运行以下查询测试语句，分别返回 1 、 100 、 1000 、 10000 、 50000 、 100000 以及 1000000 条记录。

select * from rebuild_test where id = 10;

select * from rebuild_test where id between 100 and 199;

select * from rebuild_test where id between 1000 and 1999;

select * from rebuild_test where id between 10000 and 19999;

select /*+ index(rebuild_test) */ * from rebuild_test where id between 50000 and 99999;

select /*+ index(rebuild_test) */ * from rebuild_test where id between 100000 and 199999;

select /*+ index(rebuild_test) */ * from rebuild_test where id between 1 and 1000000;

select /*+ index_ffs(rebuild_test) */ id from rebuild_test where id between 1 and 1000000;

 

       在运行这些测试语句前，先创建一个 pctfree 为 50% 的索引，来模拟索引碎片，分析并记录索引信息。

SQL> create index idx_rebuild_test on rebuild_test(id) pctfree 50;

SQL> exec dbms_stats.gather_table_stats(user,'rebuild_test',cascade=>true);

 

然后运行测试语句，记录每条查询语句所需的时间；接下来以 pctfree 为 10% 重建索引，来模拟修复索引碎片，分析并记录索引信息。

SQL> alter index idx_rebuild_test rebuild pctfree 10;

SQL> exec dbms_stats.gather_table_stats(user,'rebuild_test',cascade=>true);

 

接着再次运行这些测试语句，记录每条查询语句所需的时间。下表显示了两个索引信息的对比情况。

pctfree	Height	blocks	br_blks	lf_blks	pct_used	clustering_factor
50%	3	4224	8	4096	49%	2326
10%	3	2304	5	2226	90%	2326

下表显示了不同的索引下，运行测试语句所需的时间对比情况。

记录数	占记录总数的百分比	pctused(50%)	pctused(90 ％ )	性能提高百分比
1 条记录	0.0001%	0.01	0.01	0.00%
100 条记录	0.0100%	0.01	0.01	0.00%
1000 条记录	0.1000%	0.01	0.01	0.00%
10000 条记录	1.0000%	0.02	0.02	0.00%
50000 条记录	5.0000%	0.06	0.06	0.00%
100000 条记录	10.0000%	1.01	1.00	0.99%
1000000 条记录	100.0000%	13.05	11.01	15.63%
1000000 条记录 (FFS)	100.0000%	7.05	7.02	0.43%

       上面是对最好的 clustering_factor 所做的测试，那么对于最差的 clustering_factor 会怎么样呢？我们将 rebuild_test 中的 id 值反过来排列，也就是说，比如对于 id 为 3478 的记录，将 id 改为 8743 。这样的话，就将把原来按顺序排列的 id 值彻底打乱，从而使得 id 上的索引的 clustering_factor 变成最差的。为此，我写了一个函数用来反转 id 的值。

create or replace function get_reverse_value(id in number) return varchar2 is

 ls_id varchar2(10);

 ls_last_item varchar2(10);

 ls_curr_item varchar2(10);

 ls_zero varchar2(10);

 li_len integer;

 lb_stop boolean;

begin

 ls_id := to_char(id);

 li_len := length(ls_id);

 ls_last_item := '';

 ls_zero := '';

 lb_stop := false;

 while li_len>0 loop

       ls_curr_item := substr(ls_id,li_len,1);

       if ls_curr_item = '0' and lb_stop = false then

           ls_zero := ls_zero || ls_curr_item;

       else

           lb_stop := true;

           ls_last_item:=ls_last_item||ls_curr_item;

       end if;

       ls_id := substr(ls_id,1,li_len-1);

       li_len := length(ls_id);

 end loop;

 return(ls_last_item||ls_zero);

end get_reverse_value;

 

       接下来，我们创建我们第二个测试的测试表。并按照与第一个测试案例相同的方式进行测试。注意，对于测试查询来说，要把表名（包括提示里的）改为 rebuild_test_cf 。

SQL> create table rebuild_test_cf as select * from rebuild_test;

SQL> update rebuild_test_cf set name=get_reverse_value(id);