ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引分析

索引分析

原创 Linux操作系统 作者:cc59 时间:2006-09-21 00:00:00 0 删除 编辑


索引分析

索引为什么会突然变得慢呢?
是什么引起的呢?
我们来看测试结果。
SQL> create table test01 (col1 number,col2 number);


SQL> declare
2 i number:=1;
3 j number:=10000010;
4 begin
5 for i in 1..10000 loop
6 insert into test01 values(j,j);
7 j:=j+10;
8 end loop;
9 commit;
10 end;
11 /

PL/SQL 过程已成功完成。

SQL> create unique index i_col on test01(col1);

索引已创建。

SQL> create unique index i_col2 on test01(col2);

索引已创建。

我们使用col1来查询10050000这条数据
select *
from
test01 where col1=10050000


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 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 28

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TEST01 (cr=3 pr=0 pw=0 time=50 us)
1 INDEX UNIQUE SCAN I_COL1 (cr=2 pr=0 pw=0 time=26 us)(object id 10231)


再使用col2来查询:

select *
from
test01 where col2=10050000


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.04 0 4 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TEST01 (cr=4 pr=0 pw=0 time=35 us)
1 INDEX RANGE SCAN I_COL2 (cr=3 pr=0 pw=0 time=30 us)(object id 10232)


再分别查询前5000条数据:
select *
from
test01 where col1<=10050000


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 335 0.00 0.00 0 365 0 5000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 337 0.01 0.00 0 365 0 5000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28

Rows Row Source Operation
------- ---------------------------------------------------
5000 TABLE ACCESS FULL TEST01 (cr=365 pr=0 pw=0 time=15085 us)


select *
from
test01 where col2<=10050000


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 335 0.01 0.00 0 365 0 5000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 337 0.01 0.00 0 365 0 5000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28

Rows Row Source Operation
------- ---------------------------------------------------
5000 TABLE ACCESS FULL TEST01 (cr=365 pr=0 pw=0 time=15085 us)

我们来把这个索引dump出来。


branch: 0x1400a34 20974132 (0: nrow: 23, level: 1)<--1
leaf: 0x1400a35 20974133 (-1: nrow: 451 rrow: 451)
leaf: 0x1400a36 20974134 (0: nrow: 451 rrow: 451)
leaf: 0x1400a37 20974135 (1: nrow: 451 rrow: 451)
leaf: 0x1400a38 20974136 (2: nrow: 451 rrow: 451)
leaf: 0x1400a39 20974137 (3: nrow: 451 rrow: 451)
leaf: 0x1400a3a 20974138 (4: nrow: 451 rrow: 451)
leaf: 0x1400a3b 20974139 (5: nrow: 451 rrow: 451)
leaf: 0x1400a3c 20974140 (6: nrow: 451 rrow: 451)
leaf: 0x1400a3d 20974141 (7: nrow: 451 rrow: 451)
leaf: 0x1400a3e 20974142 (8: nrow: 451 rrow: 451)
leaf: 0x1400a3f 20974143 (9: nrow: 451 rrow: 451)
leaf: 0x1400a40 20974144 (10: nrow: 451 rrow: 451)<--2
leaf: 0x1400a42 20974146 (11: nrow: 451 rrow: 451)
leaf: 0x1400a43 20974147 (12: nrow: 451 rrow: 451)
leaf: 0x1400a44 20974148 (13: nrow: 451 rrow: 451)
leaf: 0x1400a45 20974149 (14: nrow: 451 rrow: 451)
leaf: 0x1400a46 20974150 (15: nrow: 451 rrow: 451)
leaf: 0x1400a47 20974151 (16: nrow: 451 rrow: 451)
leaf: 0x1400a48 20974152 (17: nrow: 451 rrow: 451)
leaf: 0x1400a49 20974153 (18: nrow: 451 rrow: 451)
leaf: 0x1400a4a 20974154 (19: nrow: 451 rrow: 451)
leaf: 0x1400a4b 20974155 (20: nrow: 451 rrow: 451)
leaf: 0x1400a4c 20974156 (21<--leaf no: nrow: 78 rrow: 78<--键的数量)
----- end tree dump
EXEC #6:c=31250,e=545662,p=0,cr=25,cu=24,mis=0,r=0,dep=0,og=1,tim=19542133867


(1)读取枝数据块,求出存放了第5000笔的值10050000的DBA。
(2)读取存放了第5000笔的值10050000的叶数据块的号码10。
(3)其后的数据块都符合条件式(EMPNO01 <= 10050000),所以会读取叶资料块号码10 ~ -1的数据块。

来看看我们尝试再增加一些数据:
SQL> declare
2 i number:=1;
3 j number:=10100010;
4 begin
5 for i in 1..90000 loop
6 insert into test01 values(j,j);
7 j:=j+10;
8 end loop;
9 commit;
10 end;
11 /

PL/SQL 过程已成功完成。

SQL> declare
2 i number:=1;
3 j number:=10000001;
4 begin
5 for i in 1..100000 loop
6 insert into test01 values(j,j);
7 j:=j+10;
8 end loop;
9 commit;
10 end;
11 /

PL/SQL 过程已成功完成。

依次增加2~9

1 declare
2 i number:=1;
3 j number:=10000002;
4 begin
5 for i in 1..100000 loop
6 insert into test01 values(j,j);
7 j:=j+10;
8 end loop;
9 commit;
10* end;

1 declare
2 i number:=1;
3 j number:=10000003;
4 begin
5 for i in 1..100000 loop
6 insert into test01 values(j,j);
7 j:=j+10;
8 end loop;
9 commit;
10* end;
.......一直到10000009

加完后看看index_stats的内容:

SQL> SELECT HEIGHT,BLOCKS,LF_ROWS,LF_BLKS,BR_ROWS,BR_BLKS FROM INDEX_STATS ;

HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS
---------- ---------- ---------- ---------- ---------- ----------
2 256 42463 229 228 1

再来看dump结果:

branch: 0x1401252 20976210 (-1: nrow: 369, level: 1)
leaf: 0x1400a35 20974133 (-1: nrow: 423 rrow: 423)
leaf: 0x1401c06 20978694 (0: nrow: 428 rrow: 428)
leaf: 0x1401243 20976195 (1: nrow: 256 rrow: 256)
leaf: 0x1402d3c 20983100 (2: nrow: 274 rrow: 274)
leaf: 0x140187b 20977787 (3: nrow: 310 rrow: 310)
leaf: 0x140267f 20981375 (4: nrow: 308 rrow: 308)
leaf: 0x1400d7a 20974970 (5: nrow: 317 rrow: 317)
leaf: 0x1401be0 20978656 (6: nrow: 316 rrow: 316)
.............
leaf: 0x1400ea7 20975271 (366: nrow: 312 rrow: 312)
leaf: 0x140273a 20981562 (367: nrow: 308 rrow: 308)
branch: 0x1402760 20981600 (0: nrow: 346, level: 1)
leaf: 0x14013db 20976603 (-1: nrow: 317 rrow: 317)
leaf: 0x1401f36 20979510 (0: nrow: 333 rrow: 333)
leaf: 0x1400a4f 20974159 (1: nrow: 317 rrow: 317)
.........
leaf: 0x140195d 20978013 (343: nrow: 310 rrow: 310)
leaf: 0x14028af 20981935 (344: nrow: 308 rrow: 308)
branch: 0x1401986 20978054 (1: nrow: 368, level: 1)
leaf: 0x1400eaa 20975274 (-1: nrow: 317 rrow: 317)
leaf: 0x1402040 20979776 (0: nrow: 316 rrow: 316)
......
leaf: 0x1401983 20978051 (365: nrow: 310 rrow: 310)
leaf: 0x14028e6 20981990 (366: nrow: 308 rrow: 308)
branch: 0x14028ed 20981997 (2: nrow: 416, level: 1)
leaf: 0x1400ef3 20975347 (-1: nrow: 317 rrow: 317)
leaf: 0x1402137 20980023 (0: nrow: 316 rrow: 316)
......
leaf: 0x140154d 20976973 (413: nrow: 319 rrow: 319)
leaf: 0x1402159 20980057 (414: nrow: 348 rrow: 348)
branch: 0x1401256 20976214 (3: nrow: 400, level: 1)
leaf: 0x1400f99 20975513 (-1: nrow: 317 rrow: 317)
leaf: 0x140229f 20980383 (0: nrow: 315 rrow: 315)
......
leaf: 0x1401af9 20978425 (397: nrow: 310 rrow: 310)
leaf: 0x1402b2f 20982575 (398: nrow: 308 rrow: 308)
branch: 0x14029f6 20982262 (4: nrow: 416, level: 1)
leaf: 0x1400ffa 20975610 (-1: nrow: 317 rrow: 317)
leaf: 0x14022f0 20980464 (0: nrow: 316 rrow: 316)
......
leaf: 0x1401b00 20978432 (413: nrow: 310 rrow: 310)
leaf: 0x1402b5b 20982619 (414: nrow: 308 rrow: 308)
branch: 0x1401aed 20978413 (5: nrow: 412, level: 1)
leaf: 0x1401120 20975904 (-1: nrow: 317 rrow: 317)
leaf: 0x1402450 20980816 (0: nrow: 316 rrow: 316)
.....
leaf: 0x1401877 20977783 (437: nrow: 319 rrow: 319)
leaf: 0x140267b 20981371 (438: nrow: 318 rrow: 318)
leaf: 0x1401393 20976531 (439: nrow: 304 rrow: 304)
leaf: 0x1402d34 20983092 (440: nrow: 308 rrow: 308)
leaf: 0x1401c02 20978690 (441: nrow: 310 rrow: 310)
leaf: 0x1402d38 20983096 (442: nrow: 308 rrow: 308)
leaf: 0x1400d76 20974966 (443: nrow: 401 rrow: 401)
----- end tree dump

比较数据增加前后就知道, 每一个叶数据块所存的索引键变多了。

扩张前的1万笔是由小到大插入,每一个叶数据块存放了78个索引键。扩张后的100万笔不是由小到大、而是跳着插入(最后是0 → 1 → 2... → 8 →9的顺序),所以发生多次叶分割,导致每个叶数据块里存放的索引键变少,大约是308~333。

我们删除990000条数据。
SQL> ANALYZE INDEX i_col1 VALIDATE STRUCTURE ;

索引已分析

SQL> select height,blocks,lf_rows,lf_blks,br_rows,br_blks from index_stats;

HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS
---------- ---------- ---------- ---------- ---------- ----------
3 3328 88806 3173 3172 9


再DUMP出来看:


branch: 0x1401252 20976210 (-1: nrow: 370, level: 1)
leaf: 0x1400a35 20974133 (-1: nrow: 126 rrow: 0)
leaf: 0x1401c06 20978694 (0: nrow: 129 rrow: 0)

leaf: 0x1402d38 20983096 (442: nrow: 124 rrow: 31)
leaf: 0x1400d76 20974966 (443: nrow: 160 rrow: 40)
----- end tree dump
只有索引键值变小了。

如果在查询时使用INDEX比全表扫描还要慢。那是因为在删除数据时相应的索引并没有被删除。
因为在扫描过程中空的数据块也会被扫描到,因此在这种情况下,应当适当的作索引的重建工作。
当然了,关于索引空数据块是否会被再次引用这里不作描述。可以参考ITPUB上的文章。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/104152/viewspace-139964/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-21

  • 博文量
    132
  • 访问量
    286455