深入研究B树索引(五)续

转载 2013年12月04日 14:14:31

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);

<p class="a" style="b

深入研究B树索引(五)

5.     重建B树索引 5.1 如何重建B树索引 重建索引有两种方法:一种是最简单的,删除原索引,然后重建;第二种是使用ALTER INDEX … REBUILD 命令对索引进行重建。第二...
  • wenbingcai
  • wenbingcai
  • 2013年12月04日 14:12
  • 676

深入研究B树索引(四)续

4.2 B树索引的对于删除(DELETE)的管理        上面介绍了有关插入键值时索引的管理机制,那么对于删除键值时会怎么样呢? 在介绍删除索引键值的机制之前,先介绍与索引相关的一个比较重要...
  • wenbingcai
  • wenbingcai
  • 2013年12月04日 14:11
  • 528

深入研究B树索引

深入研究B树索引(一) 上一篇 / 下一篇  2008-05-27 13:03:41 / 个人分类:工作技术 查看( 15908 ) / 评论( 76 ) / 评分( 218 / 103 ) ...
  • haiross
  • haiross
  • 2013年09月27日 13:23
  • 870

[Oracle]深入研究B-树索引

摘要:本文对B树索引的结构、内部管理等方面做了一个全面的介绍。同时深入探讨了一些与B树索引有关的广为流传的说法,比如删除记录对索引的影响,定期重建索引能解决许多性能问题等。 1.B树索引的相关概念  ...
  • xiaoxu0123
  • xiaoxu0123
  • 2010年04月05日 14:56
  • 7614

B树索引+B树索引逻辑结构图+B树索引是怎么搜索数据+创建B树索引

B树索引 1创建索引不指定unique ,btimap 那么表示创建的索引是B树索引. 2B树索引的组织结构类似一颗树,主要数据集中在叶子节点上,叶子节点包含索引列的值和记录行对应的物理地址ro...
  • zhou920786312
  • zhou920786312
  • 2017年05月28日 00:41
  • 419

B树索引和位图索引的区别!

B树索引 主键和唯一性约束字段的B树索引,效率几乎和海量数据没有关系。 键值重复率低的字段比较适合使用B树索引。 位图索引 键值重复率高的字段比较适合使用位图索引。 count、an...
  • zq9017197
  • zq9017197
  • 2011年11月24日 17:07
  • 4523

B+树比B树更适合做文件索引的原因

B+树比B树更适合做文件索引的原因
  • mine_song
  • mine_song
  • 2017年03月18日 10:47
  • 2725

数据库检索 索引之--- B 树

B树索引是一个典型的树结构,始终是平衡的,也就是说 从Root节点到 Leaf 节点的任何一个路径都是等距离的。其包含的组件主要是:             叶子节点(Leaf node):包含...
  • ldds_520
  • ldds_520
  • 2016年07月22日 10:42
  • 2717

MySQL的B树索引

本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题。特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree...
  • Great_Tony
  • Great_Tony
  • 2015年08月04日 18:59
  • 2301

oracle B树索引

摘要:本文对B树索引的结构、内部管理等方面做了一个全面的介绍。同时深入探讨了一些与B树索引有关的广为流传的说法,比如删除记录对索引的影响,定期重建索引能解决许多性能问题等。   1.B树索引的相关...
  • jc_benben
  • jc_benben
  • 2017年03月10日 13:04
  • 489
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深入研究B树索引(五)续
举报原因:
原因补充:

(最多只允许输入30个字)