唯一索引与非唯一索引结构
作者 ：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/06/unique_nounique_structure.html
链接：http://www.dbaroad.me/archives/2009/06/unique_nounique_structure.html
主要就是观察下，唯一索引与非唯一索引中，Branch block与Leaf block分别记录了些什么，有何不同之处。
唯一索引：
SQL> create table test_uni (ID number); Table created. SQL> insert into test_uni 2 select rownum from dba_objects 3 where rownum<10001; 10000 rows created. SQL> commit; Commit complete. SQL> create unique index ind_uni on test_uni (ID); Index created. SQL> select object_id from dba_objects 2 where object_name='IND_UNI'; OBJECT_ID ---------- 94258 SQL> alter session set events 'immediate trace name treedump level 94258'; Session altered. |
dump出来的索引结构：
----- begin tree dump branch: 0x411b52 4266834 (0: nrow: 20, level: 1) leaf: 0x411b53 4266835 (-1: nrow: 519 rrow: 519) leaf: 0x411b54 4266836 (0: nrow: 512 rrow: 512) leaf: 0x411b55 4266837 (1: nrow: 512 rrow: 512) leaf: 0x411b56 4266838 (2: nrow: 512 rrow: 512) leaf: 0x411b57 4266839 (3: nrow: 512 rrow: 512) leaf: 0x411b58 4266840 (4: nrow: 512 rrow: 512) leaf: 0x411b59 4266841 (5: nrow: 512 rrow: 512) leaf: 0x411b5a 4266842 (6: nrow: 512 rrow: 512) leaf: 0x411b5b 4266843 (7: nrow: 512 rrow: 512) leaf: 0x411b5c 4266844 (8: nrow: 512 rrow: 512) leaf: 0x411b5d 4266845 (9: nrow: 512 rrow: 512) leaf: 0x411b5e 4266846 (10: nrow: 512 rrow: 512) leaf: 0x411b5f 4266847 (11: nrow: 512 rrow: 512) leaf: 0x411b60 4266848 (12: nrow: 512 rrow: 512) leaf: 0x411b61 4266849 (13: nrow: 512 rrow: 512) leaf: 0x411b62 4266850 (14: nrow: 512 rrow: 512) leaf: 0x411b63 4266851 (15: nrow: 512 rrow: 512) leaf: 0x411b64 4266852 (16: nrow: 512 rrow: 512) leaf: 0x411b65 4266853 (17: nrow: 512 rrow: 512) leaf: 0x411b66 4266854 (18: nrow: 265 rrow: 265) ----- end tree dump |
Dump Branch block的结构：
SQL> select 2 dbms_utility.data_block_address_file(4266834) file_id#, 3 dbms_utility.data_block_address_block(4266834) block_id# 4 from dual; FILE_ID# BLOCK_ID# ---------- ---------- 1 72530 SQL> alter system dump datafile 1 block 72530; System altered. |
Branch block dump ================= header address 9223372041152863300=0x80000001002f9c44 kdxcolev 1 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 0 kdxconro 19 kdxcofbo 66=0x42 kdxcofeo 7889=0x1ed1 kdxcoavs 7823 kdxbrlmc 4266835=0x411b53 kdxbrsno 0 kdxbrbksz 8040 kdxbr2urrc 0 row#0[8032] dba: 4266836=0x411b54 col 0; len 3; (3): c2 06 15 (该leaf block中索引键的最小值） row#1[8024] dba: 4266837=0x411b55 col 0; len 3; (3): c2 0b 21 row#2[8016] dba: 4266838=0x411b56 col 0; len 3; (3): c2 10 2d row#3[8008] dba: 4266839=0x411b57 col 0; len 3; (3): c2 15 39 row#4[8000] dba: 4266840=0x411b58 col 0; len 3; (3): c2 1a 45 row#5[7992] dba: 4266841=0x411b59 col 0; len 3; (3): c2 1f 51 row#6[7984] dba: 4266842=0x411b5a col 0; len 3; (3): c2 24 5d row#7[7976] dba: 4266843=0x411b5b col 0; len 3; (3): c2 2a 05 row#8[7968] dba: 4266844=0x411b5c col 0; len 3; (3): c2 2f 11 row#9[7960] dba: 4266845=0x411b5d col 0; len 3; (3): c2 34 1d row#10[7952] dba: 4266846=0x411b5e col 0; len 3; (3): c2 39 29 row#11[7944] dba: 4266847=0x411b5f col 0; len 3; (3): c2 3e 35 row#12[7936] dba: 4266848=0x411b60 col 0; len 3; (3): c2 43 41 row#13[7928] dba: 4266849=0x411b61 col 0; len 3; (3): c2 48 4d row#14[7920] dba: 4266850=0x411b62 col 0; len 3; (3): c2 4d 59 row#15[7913] dba: 4266851=0x411b63 col 0; len 2; (2): c2 53 row#16[7905] dba: 4266852=0x411b64 col 0; len 3; (3): c2 58 0d row#17[7897] dba: 4266853=0x411b65 col 0; len 3; (3): c2 5d 19 row#18[7889] dba: 4266854=0x411b66 col 0; len 3; (3): c2 62 25 ----- end of branch block dump -----
Branch block中记录的是每个叶子节点中索引键的最小值以及叶子节点的地址，如：
row#0[8032] dba: 4266836=0x411b54 col 0; len 3; (3): c2 06 15 最小值：c2 06 15，转化为10进制为： SQL> select uf_dec('06 15') from dual; UF_DEC('0615') -------------- 520 dba进行转化为： SQL> select 2 dbms_utility.data_block_address_file(4266836) file_id#, 3 dbms_utility.data_block_address_block(4266836) block_id# 4 from dual; FILE_ID# BLOCK_ID# ---------- ---------- 1 72532 |
我们再来dump Leaf block看看：
SQL> alter system dump datafile 1 block 72532; System altered. Leaf block dump =============== header address 9223372041152863324=0x80000001002f9c5c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 0 kdxconro 512 kdxcofbo 1060=0x424 kdxcofeo 1877=0x755 kdxcoavs 817 kdxlespl 0 kdxlende 0 kdxlenxt 4266837=0x411b55 kdxleprv 4266835=0x411b53 kdxledsz 6 kdxlebksz 8016 row#0[8004] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3a 02 07 col 0; len 3; (3): c2 06 15 (该leaf block中索引键的最小值，在Branch block中有记录） row#1[7992] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3a 02 08 col 0; len 3; (3): c2 06 16 row#2[7980] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3a 02 09 col 0; len 3; (3): c2 06 17 row#3[7968] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3a 02 0a col 0; len 3; (3): c2 06 18 row#4[7956] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3a 02 0b col 0; len 3; (3): c2 06 19 row#5[7944] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3a 02 0c col 0; len 3; (3): c2 06 1a row#6[7932] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3a 02 0d col 0; len 3; (3): c2 06 1b row#7[7920] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3a 02 0e col 0; len 3; (3): c2 06 1c row#8[7908] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3a 02 0f col 0; len 3; (3): c2 06 1d ................................. ................................. 省略部份内容 ................................. ................................. row#503[1973] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3b 01 6a col 0; len 3; (3): c2 0b 18 row#504[1961] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3b 01 6b col 0; len 3; (3): c2 0b 19 row#505[1949] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3b 01 6c col 0; len 3; (3): c2 0b 1a row#506[1937] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3b 01 6d col 0; len 3; (3): c2 0b 1b row#507[1925] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3b 01 6e col 0; len 3; (3): c2 0b 1c row#508[1913] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3b 01 6f col 0; len 3; (3): c2 0b 1d row#509[1901] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3b 01 70 col 0; len 3; (3): c2 0b 1e row#510[1889] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3b 01 71 col 0; len 3; (3): c2 0b 1f row#511[1877] flag: ------, lock: 0, len=12, data:(6): 00 41 1b 3b 01 72 col 0; len 3; (3): c2 0b 20 ----- end of leaf block dump ----- |
可以看出叶子节点中，记录的主要就是索引键的值，并按升序排列。
非唯一索引：
SQL> create table test_nouni (ID number); Table created. SQL> insert into test_nouni 2 select rownum from dba_objects 3 where rownum<10001; 10000 rows created. SQL> commit; Commit complete. SQL> begin 2 for i in 1..1000 loop 3 insert into test_nouni values(1000); 4 end loop; 5 commit; 6 end; 7 / PL/SQL procedure successfully completed. SQL> create index ind_id_nouni on test_nouni(ID); Index created. SQL> select object_id from dba_objects 2 where object_name='IND_ID_NOUNI'; OBJECT_ID ---------- 94261 SQL> alter session set events 'immediate trace name treedump level 94261'; Session altered. |
dump出来的索引结构：
----- begin tree dump branch: 0x411b82 4266882 (0: nrow: 23, level: 1) leaf: 0x411b83 4266883 (-1: nrow: 484 rrow: 484) leaf: 0x411b84 4266884 (0: nrow: 477 rrow: 477) leaf: 0x411b85 4266885 (1: nrow: 509 rrow: 509) leaf: 0x411b86 4266886 (2: nrow: 511 rrow: 511) leaf: 0x411b87 4266887 (3: nrow: 479 rrow: 479) leaf: 0x411b88 4266888 (4: nrow: 477 rrow: 477) leaf: 0x411109 4264201 (5: nrow: 477 rrow: 477) leaf: 0x41110a 4264202 (6: nrow: 477 rrow: 477) leaf: 0x41110b 4264203 (7: nrow: 477 rrow: 477) leaf: 0x41110c 4264204 (8: nrow: 477 rrow: 477) leaf: 0x41110d 4264205 (9: nrow: 477 rrow: 477) leaf: 0x41110e 4264206 (10: nrow: 477 rrow: 477) leaf: 0x41110f 4264207 (11: nrow: 477 rrow: 477) leaf: 0x411110 4264208 (12: nrow: 477 rrow: 477) leaf: 0x411111 4264209 (13: nrow: 477 rrow: 477) leaf: 0x411112 4264210 (14: nrow: 477 rrow: 477) leaf: 0x411113 4264211 (15: nrow: 477 rrow: 477) leaf: 0x411114 4264212 (16: nrow: 477 rrow: 477) leaf: 0x411115 4264213 (17: nrow: 477 rrow: 477) leaf: 0x411116 4264214 (18: nrow: 477 rrow: 477) leaf: 0x411117 4264215 (19: nrow: 477 rrow: 477) leaf: 0x411118 4264216 (20: nrow: 477 rrow: 477) leaf: 0x411119 4264217 (21: nrow: 431 rrow: 431) ----- end tree dump |
Dump Branch block的结构：
SQL> select 2 dbms_utility.data_block_address_file(4266882) file_id#, 3 dbms_utility.data_block_address_block(4266882) block_id# 4 from dual; FILE_ID# BLOCK_ID# ---------- ---------- 1 72578 SQL> alter system dump datafile 1 block 72578; System altered. |
Branch block dump ================= header address 9223376402689370180=0x800003f880052844 kdxcolev 1 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 22 kdxcofbo 72=0x48 kdxcofeo 7833=0x1e99 kdxcoavs 7761 kdxbrlmc 4266883=0x411b83 kdxbrsno 0 kdxbrbksz 8040 kdxbr2urrc 0 row#0[8031] dba: 4266884=0x411b84 col 0; len 3; (3): c2 05 56 col 1; TERM row#1[8022] dba: 4266885=0x411b85 col 0; len 3; (3): c2 0a 3f (该leaf block中索引键的最小值） col 1; TERM row#2[8008] dba: 4266886=0x411b86 col 0; len 2; (2): c2 0b (该leaf block中索引键的最小值） col 1; len 6; (6): 00 41 1b 79 02 3a（最小值对应的rowid） row#3[7994] dba: 4266887=0x411b87 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 7a 01 a5 row#4[7985] dba: 4266888=0x411b88 col 0; len 3; (3): c2 0f 3e col 1; TERM row#5[7976] dba: 4264201=0x411109 col 0; len 3; (3): c2 14 27 col 1; TERM row#6[7967] dba: 4264202=0x41110a col 0; len 3; (3): c2 19 10 col 1; TERM row#7[7958] dba: 4264203=0x41110b col 0; len 3; (3): c2 1d 5d col 1; TERM row#8[7949] dba: 4264204=0x41110c col 0; len 3; (3): c2 22 46 col 1; TERM row#9[7940] dba: 4264205=0x41110d col 0; len 3; (3): c2 27 2f col 1; TERM row#10[7931] dba: 4264206=0x41110e col 0; len 3; (3): c2 2c 18 col 1; TERM row#11[7923] dba: 4264207=0x41110f col 0; len 2; (2): c2 31 col 1; TERM row#12[7914] dba: 4264208=0x411110 col 0; len 3; (3): c2 35 4e col 1; TERM row#13[7905] dba: 4264209=0x411111 col 0; len 3; (3): c2 3a 37 col 1; TERM row#14[7896] dba: 4264210=0x411112 col 0; len 3; (3): c2 3f 20 col 1; TERM row#15[7887] dba: 4264211=0x411113 col 0; len 3; (3): c2 44 09 col 1; TERM row#16[7878] dba: 4264212=0x411114 col 0; len 3; (3): c2 48 56 col 1; TERM row#17[7869] dba: 4264213=0x411115 col 0; len 3; (3): c2 4d 3f col 1; TERM row#18[7860] dba: 4264214=0x411116 col 0; len 3; (3): c2 52 28 col 1; TERM row#19[7851] dba: 4264215=0x411117 col 0; len 3; (3): c2 57 11 col 1; TERM row#20[7842] dba: 4264216=0x411118 col 0; len 3; (3): c2 5b 5e col 1; TERM row#21[7833] dba: 4264217=0x411119 col 0; len 3; (3): c2 60 47 col 1; TERM ----- end of branch block dump -----
从Branch block中我们可以看出，除了记录每个叶子节点中索引键的最小值以及叶子节点的地址外，还有可能记录了，该最小值对应的rowid。当然也有一些记录的是TERM，这是因为除了值：c2 0b（对应1000），其它的值，根据索引键就能决定是在哪个叶子节点中了。
再来看看，Leaf block中是如何记录的：
row#1[8022] dba: 4266885=0x411b85 col 0; len 3; (3): c2 0a 3f col 1; TERM Leaf block dump =============== header address 9223372041152863324=0x80000001002f9c5c kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 509 kdxcofbo 1054=0x41e kdxcofeo 1870=0x74e kdxcoavs 816 kdxlespl 0 kdxlende 0 kdxlenxt 4266886=0x411b86 kdxleprv 4266884=0x411b84 kdxledsz 0 kdxlebksz 8016 row#0[8003] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 3f (该leaf block中索引键的最小值，在Branch block中有记录） col 1; len 6; (6): 00 41 1b 6b 01 2d row#1[7990] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 40 col 1; len 6; (6): 00 41 1b 6b 01 2e row#2[7977] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 41 col 1; len 6; (6): 00 41 1b 6b 01 2f row#3[7964] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 42 col 1; len 6; (6): 00 41 1b 6b 01 30 row#4[7951] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 43 col 1; len 6; (6): 00 41 1b 6b 01 31 row#5[7938] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 44 col 1; len 6; (6): 00 41 1b 6b 01 32 row#6[7925] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 45 col 1; len 6; (6): 00 41 1b 6b 01 33 row#7[7912] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 46 col 1; len 6; (6): 00 41 1b 6b 01 34 row#8[7899] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 47 col 1; len 6; (6): 00 41 1b 6b 01 35 row#9[7886] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 48 col 1; len 6; (6): 00 41 1b 6b 01 36 row#10[7873] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 49 col 1; len 6; (6): 00 41 1b 6b 01 37 row#11[7860] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 4a col 1; len 6; (6): 00 41 1b 6b 01 38 row#12[7847] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 4b col 1; len 6; (6): 00 41 1b 6b 01 39 row#13[7834] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 4c col 1; len 6; (6): 00 41 1b 6b 01 3a row#14[7821] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 4d col 1; len 6; (6): 00 41 1b 6b 01 3b row#15[7808] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 4e col 1; len 6; (6): 00 41 1b 6b 01 3c row#16[7795] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 4f col 1; len 6; (6): 00 41 1b 6b 01 3d row#17[7782] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 50 col 1; len 6; (6): 00 41 1b 6b 01 3e row#18[7769] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 51 col 1; len 6; (6): 00 41 1b 6b 01 3f row#19[7756] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 52 col 1; len 6; (6): 00 41 1b 6b 01 40 row#20[7743] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 53 col 1; len 6; (6): 00 41 1b 6b 01 41 row#21[7730] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 54 col 1; len 6; (6): 00 41 1b 6b 01 42 row#22[7717] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 55 col 1; len 6; (6): 00 41 1b 6b 01 43 row#23[7704] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 56 col 1; len 6; (6): 00 41 1b 6b 01 44 row#24[7691] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 57 col 1; len 6; (6): 00 41 1b 6b 01 45 row#25[7678] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 58 col 1; len 6; (6): 00 41 1b 6b 01 46 row#26[7665] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 59 col 1; len 6; (6): 00 41 1b 6b 01 47 row#27[7652] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 5a col 1; len 6; (6): 00 41 1b 6b 01 48 row#28[7639] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 5b col 1; len 6; (6): 00 41 1b 6b 01 49 row#29[7626] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 5c col 1; len 6; (6): 00 41 1b 6b 01 4a row#30[7613] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 5d col 1; len 6; (6): 00 41 1b 6b 01 4b row#31[7600] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 5e col 1; len 6; (6): 00 41 1b 6b 01 4c row#32[7587] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 5f col 1; len 6; (6): 00 41 1b 6b 01 4d row#33[7574] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 60 col 1; len 6; (6): 00 41 1b 6b 01 4e row#34[7561] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 61 col 1; len 6; (6): 00 41 1b 6b 01 4f row#35[7548] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 62 col 1; len 6; (6): 00 41 1b 6b 01 50 row#36[7535] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 63 col 1; len 6; (6): 00 41 1b 6b 01 51 row#37[7522] flag: ------, lock: 0, len=13 col 0; len 3; (3): c2 0a 64 col 1; len 6; (6): 00 41 1b 6b 01 52 row#38[7510] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 6b 01 53 row#39[7498] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 79 00 64 row#40[7486] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 79 00 65 row#41[7474] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 79 00 66 row#42[7462] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 79 00 67 row#43[7450] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 79 00 68 row#44[7438] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 79 00 69 row#45[7426] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 79 00 6a row#46[7414] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 79 00 6b row#47[7402] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 79 00 6c row#48[7390] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 79 00 6d ................................. ................................. 省略部份内容 ................................. ................................. row#504[1918] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 79 02 35 row#505[1906] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 79 02 36 row#506[1894] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 79 02 37 row#507[1882] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 79 02 38 row#508[1870] flag: ------, lock: 0, len=12 col 0; len 2; (2): c2 0b col 1; len 6; (6): 00 41 1b 79 02 39 ----- end of leaf block dump ----- |
从上面的DUMP可以看出，叶子节点中，如果索引键值不同，是按索引键升序的，如果索引键值相同（都为c2 0b），按rowid升序
总结：
对于唯一索引，Branch block中记录的是每个叶子节点中索引键的最小值以及叶子节点的地址；Leaf block中，记录的主要就是索引键的值，并按升序排列。
对于非唯一索引，Branch block中除了记录每个叶子节点中索引键的最小值以及叶子节点的地址外，还有可能记录了，该最小值对应的rowid。当然也有一些记录的是TERM；Leaf block中，如果索引键值不同，按索引键升序，如果索引键值相同，按rowid升序。
— The End —
关键字: 基础知识
我觉得这里还有必要提到unique index是把rowid存在行头，且kdxledsz指明了rowid的长度。而non unique index是把rowid作为另外一个column来存，且kdxledsz的值是0。
Reply
OoNiceDream Reply:
06月 9th, 2009 at 15:47
嗯，多谢指正
Reply