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

5.     重建 B 树索引

5.1 如何重建 B 树索引

重建索引有两种方法：一种是最简单的，删除原索引，然后重建；第二种是使用 ALTER INDEX … REBUILD

命令对索引进行重建。第二种方式是从 oracle 7.3.3 版本开始引入的，从而使得用户在重建索引时不必删除原索引再重新 CREATE INDEX 了。 ALTER INDEX … REBUILD 相对 CREATE INDEX 有以下好处：

1）  它使用原索引的叶子节点作为新索引的数据来源。我们知道，原索引的叶子节点的数据块通常都要比表里的数据块要少很多，因此进行的 I/O 就会减少；同时，由于原索引的叶子节点里的索引条目已经排序了，因此在重建索引的过程中，所做的排序工作也要少的多。

2）  自从 oracle 8.1.6 以来， ALTER INDEX … REBUILD 命令可以添加 ONLINE 短语。这使得在重建索引的过程中，用户可以继续对原来的索引进行修改，也就是说可以继续对表进行 DML 操作。

而同时， ALTER INDEX … REBUILD 与 CREATE INDEX 也有很多相同之处：

1）  它们都可以通过添加 PARALLEL 提示进行并行处理。

2）  它们都可以通过添加 NOLOGGING 短语，使得重建索引的过程中产生最少的重做条目（ redo entry ）。

3）  自从 oracle 8.1.5 以来，它们都可以田间 COMPUTE STATISTICS 短语，从而在重建索引的过程中，就生成 CBO 所需要的统计信息，这样就避免了索引创建完毕以后再次运行 analyze 或 dbms_stats 来收集统计信息。

当我们重建索引以后，在物理上所能获得的好处就是能够减少索引所占的空间大小（特别是能够减少叶子

节点的数量）。而索引大小减小以后，又能带来以下若干好处：

1）  CBO 对于索引的使用可能会产生一个较小的成本值，从而在执行计划中选择使用索引。

2）  使用索引扫描的查询扫描的物理索引块会减少，从而提高效率。

3）  由于需要缓存的索引块减少了，从而让出了内存以供其他组件使用。

尽管重建索引具有一定的好处，但是盲目的认为重建索引能够解决很多问题也是不正确的。比如我见过一

个生产系统，每隔一个月就要重建所有的索引（而且我相信，很多生产系统可能都会这么做），其中包括一些 100GB 的大表。为了完成重建所有的索引，往往需要把这些工作分散到多个晚上进行。事实上，这是一个 7 × 24 的系统，仅重建索引一项任务就消耗了非常多的系统资源。但是每隔一段时间就重建索引有意义吗？这里就有一些关于重建索引的很流行的说法，主要包括：

1）  如果索引的层级超过 X （ X 通常是 3 ）级以后需要通过重建索引来降低其级别。

2）  如果经常删除索引键值，则需要定时重建索引来收回这些被删除的空间。

3）  如果索引的 clustering_factor 很高，则需要重建索引来降低该值。

4）  定期重建索引能够提高性能。

对于第一点来说，我们在前面已经知道， B 树索引是一棵在高度上平衡的树，所以重建索引基本不可能降

低其级别，除非是极特殊的情况导致该索引有非常大量的碎片，导致 B 树索引“虚高”，那么这实际又来到第二点上（因为碎片通常都是由于删除引起的）。实际上，对于第一和第二点，我们应该通过运行 ALTER INDEX … REBUILD 命令以后检查 indest_stats.pct_used 字段来判断是否有必要重建索引。

 

5.2 重建 B 树索引对于 clustering_factor 的影响

 

而对于 clustering_factor 来说，它是用来比较索引的顺序程度与表的杂乱排序程度的一个度量。 Oracle 在计算某个 clustering_factor 时，会对每个索引键值查找对应到表的数据，在查找的过程中，会跟踪从一个表的数据块跳转到另外一个数据块的次数（当然，它不可能真的这么做，源代码里只是简单的扫描索引，从而获得 ROWID ，然后从这些 ROWID 获得表的数据块的地址）。每一次跳转时，有个计数器就会增加，最终该计数器的值就是 clustering_factor 。下图四描述了这个原理。

                                                                             图四              

       在上图四中，我们有一个表，该表有 4 个数据块，以及 20 条记录。在列 N1 上有一个索引，上图中的每个小黑点就表示一个索引条目。列 N1 的值如图所示。而 N1 的索引的叶子节点包含的值为： A 、 B 、 C 、 D 、 E 、 F 。如果 oracle 开始扫描索引的底部，叶子节点包含的第一个 N1 值为 A ，那么根据该值可以知道对应的 ROWID 位于第一个数据块的第三行里，所以我们的计数器增加 1 。同时， A 值还对应第二个数据块的第四行，由于跳转到了不同的数据块上，所以计数器再加 1 。同样的，在处理 B 时，可以知道对应第一个数据块的第二行，由于我们从第二个数据块跳转到了第一个数据块，所以计数器再加 1 。同时， B 值还对应了第一个数据块的第五行，由于我们这里没有发生跳转，所以计数器不用加 1 。

在上面的图里，在表的每一行的下面都放了一个数字，它用来显示计数器跳转到该行时对应的值。当我们处理完索引的最后一个值时，我们在数据块上一共跳转了十次，所以该索引的 clustering_factor 为 10 。

注意第二个数据块， clustering_factor 为 8 出现了 4 次。因为在索引里 N1 为 E 所对应的 4 个索引条目都指向了同一个数据块。从而使得 clustering_factor 不再增长。同样的现象出现在第三个数据块中，它包含三条记录，它们的值都是 C ，对应的 clustering_factor 都是 6 。

从 clustering_factor 的计算方法上可以看出，我们可以知道它的最小值就等于表所含有的数据块的数量；而最大值就是表所含有的记录的总行数。很明显， clustering_factor 越小越好，越小说明通过索引查找表里的数据行时需要访问的表的数据块越少。

我们来看一个例子，来说明重建索引对于减小 clustering_factor 没有用处。首先我们创建一个测试表：

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

SQL> create
 
index
 idx_clustfact_test 
on
 clustfact_test(id);

 

然后，我们插入十万条记录。

SQL> 
begin

 2           for
 i 
in
 1..100000 loop

 3                   insert
 
into
 clustfact_test 
values
(mod(i,200),to_char(i));

 4           end
 loop;

 5           commit
;

 6 end
;

 7 /

 

因为使用了 mod 的关系，最终数据在表里排列的形式为：

0,1,2,3,4,5,…,197,198,199,0,1,2,3,…, 197,198,199,0,1,2,3,…, 197,198,199,0,1,2,3,…

       接下来，我们分析表。

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

 

       这个时候，我们来看看该索引的 clustering_factor 。

SQL> 
select
 num_rows, blocks 
from
 user_tables 
where
 table_name = 
'CLUSTFACT_TEST'
;

 NUM_ROWS    BLOCKS

---------- ----------

   100000       202

SQL> select
 num_rows, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key,

 2 clustering_factor from
 user_indexes 
where
 index_name = 
'IDX_CLUSTFACT_TEST'
;

 NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR

---------- ------------- ----------------------- ----------------------- -----------------

   100000          200                      1                    198            39613

 

       从上面的 avg_data_blocks_per_key 的值为 198 可以知道，每个键值平均分布在 198 个数据块里，而整个表也就 202 个数据块。这也就是说，要获取某个键值的所有记录，几乎每次都需要访问所有的数据块。从这里已经可以猜测到 clustering_factor 会非常大。事实上，该值近 4 万，也说明该索引并不会很有效。

       我们来看看下面这句 SQL 语句的执行计划。

SQL> 
select
 
count
(
name
) 
from
 clufac_test 
where
 id = 100;

Execution Plan

----------------------------------------------------------

  0     SELECT
 STATEMENT ptimizer=CHOOSE (Cost=32 Card=1 Bytes=9)

  1   0  SORT (AGGREGATE)

  2   1    TABLE
 ACCESS (
FULL
) 
OF
 
'CLUFAC_TEST'
 (Cost=32 Card=500 Bytes=4500)

Statistics

----------------------------------------------------------

         0 recursive calls

         0 db block gets

       205 consistent gets

……

 

       很明显， CBO 弃用了索引，而使用了全表扫描。这实际上已经说明由于索引的 clustering_factor 过高，导致通过索引获取数据时跳转的数据块过多，成本过高，因此直接使用全表扫描的成本会更低。

       这时我们来重建索引看看会对 clustering_factor 产生什么影响。从下面的测试中可以看到，没有任何影响。

SQL> 
alter
 
index
 idx_clustfact_test rebuild;

SQL> select
 num_rows, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key,

 2 clustering_factor from
 user_indexes 
where
 index_name = 
'IDX_CLUSTFACT_TEST'
;

 NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR

---------- ------------- ----------------------- ----------------------- -----------------

   100000          200                      1                    198            39613

 

       那么当我们将表里的数据按照 id 的顺序（也就是索引的排列顺序）重建时，该 SQL 语句会如何执行？

SQL> 
create
 
table
 clustfact_test_temp 
as
 
select
 * 
from
 clustfact_test 
order
 
by
 id;

SQL> truncate
 
table
 clustfact_test;

SQL> insert
 
into
 clustfact_test 
select
 * 
from
 clustfact_test_temp;

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

SQL> select
 num_rows, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key,

 2 clustering_factor from
 user_indexes 
where
 index_name = 
'IDX_CLUSTFACT_TEST'
;

 NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR

---------- ------------- ----------------------- ----------------------- -----------------

   100000          200                      1                      1              198

 

       很明显的，这时的索引里每个键值只分布在 1 个数据块里，同时 clustering_factor 也已经降低到了 198 。这时再次执行相同的查询语句时， CBO 将会选择索引，同时可以看到 consistent gets 也从 205 降到了 5 。

SQL> 
select
 
count
(
name
) 
from
 clustfact_test 
where
 id = 100;

Execution Plan

----------------------------------------------------------

  0     SELECT
 STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=9)

  1   0  SORT (AGGREGATE)

  2   1    TABLE
 ACCESS (
BY
 
INDEX
 ROWID) 
OF
 
'CLUSTFACT_TEST'
 (Cost=2 Card=500 Bytes=4500)

  3   2      INDEX
 (RANGE SCAN) 
OF
 
'IDX_CLUSTFACT_TEST'
 (NON-
UNIQUE
) (Cost=1 Card=500)

Statistics

----------------------------------------------------------

         0 recursive calls

         0 db block gets

         5 consistent gets

……

 

       所以我们可以得出结论，如果仅仅是为了降低索引的 clustering_factor 而重建索引没有任何意义。降低 clustering_factor 的关键在于重建表里的数据。只有将表里的数据按照索引列排序以后，才能切实有效的降低 clustering_factor 。但是如果某个表存在多个索引的时候，需要仔细决定应该选择哪一个索引列来重建表。