sybase索引物理存储结构分析

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

---- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；

---- 转载务必注明原始出处 : http://blog.csdn.net/andkylee

---- 关键字： 索引 存储结构 分析  非聚簇 index storage non-clustered

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

 

迄今已分析出来了sybase中索引（indid>1）的物理存储结构。

索引结构是B-Tree类型的。最顶部叫做根（root），最底层称为叶子（leaf）。一个表可能建有好几个非聚簇索引，这时indid依次为2，3，。。。递增。

对于一个索引，比如indid=2的那个。索引树状结构是分层次的，在sybase数据存储中用level表示，根部级别最高，叶子的级别最低。叶子（leaf）的级别level为0，往上索引层level为1，再往上位2，。。。最后到达顶部root级别为（N-1，N为所有的层次数）。

 

不管APL还是DOL表，索引的每层（level）上的页面都是前后链接起来的，这一点有点像APL表中的数据页面上的前、后页链（data page link）。

以下简要演示分析索引结构的过程。

1.

设定成在终端显示dbcc结果信息。

dbcc traceon(3604) go

2.

查看syspartitions表的信息

1> select *from sysobjects 2> where name='PartitionTestTable' 3> go name id uid type userstat sysstat indexdel schemacnt sysstat2 crdate expdate deltrig instrig updtrig seltrig ckfirst cache audflags objspare versionts loginame identburnmax spacestate erlchgts ----------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- ----------- ----------- ---- -------- ------- -------- --------- ----------- -------------------------- -------------------------- ----------- ----------- ----------- ----------- ----------- ------ ----------- ----------- -------------------------- ------------------------------ ----------------------------------------- ---------- ------------------ PartitionTestTable 1223672376 1 U 0 99 2 0 73728 Feb 24 2010 4:43PM Feb 24 2010 4:43PM 0 0 0 0 0 0 0 0 NULL NULL NULL NULL NULL (1 row affected) 1> select * from syspartitions 2> where id = 1223672376 3> go name indid id partitionid segment status datoampage indoampage firstpage rootpage data_partitionid crdate cdataptnname ----------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------- ------ ----------- ----------- ------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------------- --------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------ zhang 0 1223672376 1239672433 1 0 61288 0 61289 61997 0 Feb 24 2010 4:43PM NULL liu 0 1223672376 1255672490 1 0 61296 0 61297 62124 0 Feb 24 2010 4:43PM NULL wang 0 1223672376 1271672547 1 0 61304 0 61305 62260 0 Feb 24 2010 4:43PM NULL li 0 1223672376 1287672604 1 0 61312 0 61313 62385 0 Feb 24 2010 4:43PM NULL idx_PartitionTestTable_id_1431673117 2 1223672376 1431673117 1 2 0 62465 67256 66248 0 Mar 1 2010 11:19AM NULL idx_PartitionTestTable_name_1479673288 3 1223672376 1479673288 1 2 0 62721 69816 69386 0 Mar 1 2010 6:03PM NULL (6 rows affected) 1>

表PartitionTestTable是在其上的id列建了4个分区的分区表，它有2个索引。idx_PartitionTestTable_id对id列索引，idx_PartitionTestTable_name对name列索引。

我们就分析idx_PartitionTestTable_id这个索引吧。通过syspartitions表我们可以得到四个比较有用的datoampage，indoampage， firstpage ，rootpage。分别表示数据对象分配页的页号，索引对象分配页的页号，

索引叶子层上的第一页，索引根部的页号。（堆表信息中的firstpage，rootpage意思有些不同。分别表示：数据页的第一个、最后一页。）

有：datoampage=0,indoampage=62465,firstpage= 67256,rootpage=66248.

对于indoampage索引对象分配页，可以这么查看。（PartitionTestTable的objid为： 1223672376）

dbcc listoam(4,1223672376,2)

3.可以看出索引idx_PartitionTestTable_id在14个对象分配页allocation page上的分配情况如下：

1> dbcc listoam(4,1223672376,2) 2> go ----------------------------------------------------------------------------- Partition id: 1431673117 indid: 2 prevpg: 62465 nextpg: 62465 OAM pg cnt: 1 Entry cnt: 14 Row count information is not maintained for index pages. Used pgs: 716 Unused pgs: 11 Attribute entries: 10 OAM status bits set: (0x8000 (PG_OAMPG), 0x0008 (PG_OAMATTRIB), 0x0004 (PG_OAMSORT)) LAST SCANNED OAM PAGE: 0 ALLOCATION HINTS : 62465 0 0 0 0 0 0 0 0 0 0 0 0 0 0 OAM pg # 1: 62465 has the following 14 entries (allocpg:used/unused): [ 0] 62464: 9/ 6 63232: 24/ 0 63744: 8/ 0 64256: 0/ 0 [ 4] 64768: 16/ 0 65024: 16/ 0 65280: 16/ 0 65536: 16/ 0 [ 8] 65792:152/ 0 66048:240/ 0 66304: 96/ 0 66560: 48/ 0 [ 12] 66816: 40/ 0 67072: 35/ 5 There are 1 entries with zero used/unused values. ---- End of OAM chain for partition 1431673117 ---- DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

（此处，暂时不解释以上结果中的情况！）

 

4.现在回到索引上，先从根部分析。rootpage=66248。

查看66248页上的16进制数据。

dbcc page(4,66248,1)

1> dbcc page(4,66248,1) 2> go Page not found in Cache: default data cache. Page read from disk. BUFFER: Buffer header for buffer 0x28288000 (Mass head) page=0x28287000 bdnew=0x00000000 bdold=0x00000000 bhash=0x00000000 bmass_next=0x00000000 bmass_prev=0x00000000 bdbid=4 bvirtpg= [ 0x28288070 vpgdevno=5 vpvpn=132496 vdisk=0x21DE1CB4 ] bmass_head=0x28288000 bmass_tail=0x28288000 bcache_desc=0x2828B3F0 (cache name='default data cache') bpool_desc=0x00000000 bdbtable=0x00000000 Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0 bmass_size=4096 (4K pool) bunref_cnt=0 bmass_stat=0x0800 (0x00000800 (MASS_NOTHASHED)) bbuf_stat=0x0 (0x00000000) Buffer blpageno=66248 bpg_size=4k Mass blpageno=66248 (Buffer slot #: 0) bxls_pin=0x00000000 bxls_next=0x00000000 bspid=0 bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x00000000 Latch and the wait queue: Latch (address: 0x28288020) latchmode: 0x0 (FREE_LATCH) latchowner: 0 latchnoofowners: 0 latchwaitq: 0x00000000 latchwaitqt: 0x00000000 Latch wait queue: PAGE HEADER: Page header for page 0x28287000 pageno=66248 nextpg=0 prevpg=0 ptnid=1431673117 timestamp=0000 0040a817 lastrowoff=62 level=2 indid=2 freeoff=77 minlen=15 page status bits: 0x80 (0x0080 (PG_FIXED)) DATA: Offset 32 - row length=15 # varlen cols=0 Child page ID=67258 28287020 ( 0): 00be8301 00aff000 004100ba 060100 .........A..... Row-Offset table for variable-length columns: [<varcol number>, <offset from start of the row>, <varcol length>] Offset 47 - row length=15 # varlen cols=0 Child page ID=65823 2828702F ( 0): 00be8301 00aff000 0041001f 010100 .........A..... Row-Offset table for variable-length columns: [<varcol number>, <offset from start of the row>, <varcol length>] Offset 62 - row length=15 # varlen cols=0 Child page ID=66990 2828703E ( 0): 007b0703 00f0f200 009d00ae 050100 .{............. Row-Offset table for variable-length columns: [<varcol number>, <offset from start of the row>, <varcol length>] OFFSET TABLE: DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role. 1>

 

此时，可以看出索引根部的级别level为2。 也就是说索引还有中间层level=1和叶子层level=0.

看第二行数据，

Offset 47 - row length=15 # varlen cols=0 Child page ID=65823
2828702F (     0):  00be8301 00aff000 0041001f 010100    .........A.....
Row-Offset table for variable-length columns:
        [<varcol number>, <offset from start of the row>, <varcol length>]

 

它的子页面号是：65823。

再来查看65823的页面数据。

1> dbcc page(4,65823,1) 2> go Page not found in Cache: default data cache. Page read from disk. BUFFER: Buffer header for buffer 0x28288000 (Mass head) page=0x28287000 bdnew=0x00000000 bdold=0x00000000 bhash=0x00000000 bmass_next=0x00000000 bmass_prev=0x00000000 bdbid=4 bvirtpg= [ 0x28288070 vpgdevno=5 vpvpn=131646 vdisk=0x21DE1CB4 ] bmass_head=0x28288000 bmass_tail=0x28288000 bcache_desc=0x2828B3F0 (cache name='default data cache') bpool_desc=0x00000000 bdbtable=0x00000000 Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0 bmass_size=4096 (4K pool) bunref_cnt=0 bmass_stat=0x0800 (0x00000800 (MASS_NOTHASHED)) bbuf_stat=0x0 (0x00000000) Buffer blpageno=65823 bpg_size=4k Mass blpageno=65823 (Buffer slot #: 0) bxls_pin=0x00000000 bxls_next=0x00000000 bspid=0 bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x00000000 Latch and the wait queue: Latch (address: 0x28288020) latchmode: 0x0 (FREE_LATCH) latchowner: 0 latchnoofowners: 0 latchwaitq: 0x00000000 latchwaitqt: 0x00000000 Latch wait queue: PAGE HEADER: Page header for page 0x28287000 pageno=65823 nextpg=66990 prevpg=67258 ptnid=1431673117 timestamp=0000 0040a817 lastrowoff=4052 level=1 indid=2 freeoff=4067 minlen=15 page status bits: 0x80 (0x0080 (PG_FIXED)) DATA: Offset 32 - row length=15 # varlen cols=0 Child page ID=65822 28287020 ( 0): 00be8301 00aff000 0041001e 010100 .........A..... Row-Offset table for variable-length columns: [<varcol number>, <offset from start of the row>, <varcol length>] Offset 47 - row length=15 # varlen cols=0 Child page ID=66249 2828702F ( 0): 002f8501 0039f100 003300c9 020100 ./...9...3..... Row-Offset table for variable-length columns: [<varcol number>, <offset from start of the row>, <varcol length>] Offset 62 - row length=15 # varlen cols=0 Child page ID=66250 2828703E ( 0): 00a08601 007bf100 00a700ca 020100 .....{......... Row-Offset table for variable-length columns: [<varcol number>, <offset from start of the row>, <varcol length>] Offset 77 - row length=15 # varlen cols=0 Child page ID=66251 2828704D ( 0): 00118801 00f3f000 00c800cb 020100 ............... Row-Offset table for variable-length columns: [<varcol number>, <offset from start of the row>, <varcol length>] Offset 92 - row length=15 # varlen cols=0 Child page ID=66252 2828705C ( 0): 00828901 00b0f000 00d100cc 020100 ............... Row-Offset table for variable-length columns: [<varcol number>, <offset from start of the row>, <varcol length>] Offset 107 - row length=15 # varlen cols=0 Child page ID=66253 2828706B ( 0): 00f38a01 003af100 00b500cd 020100 .....:......... Row-Offset table for variable-length columns: [<varcol number>, <offset from start of the row>, <varcol length>]

 

 

此页面65823是索引的中间层level=1.看第一行的数据。

Offset 32 - row length=15 # varlen cols=0 Child page ID=65822
28287020 (     0):  00be8301 00aff000 0041001e 010100    .........A.....
Row-Offset table for variable-length columns:
        [<varcol number>, <offset from start of the row>, <varcol length>]

 

页面：66248上的第二行就是指向该行。

 

继续查看它的子页面上的数据，65822。

 

1> dbcc page(4,65822,1) 2> go Page not found in Cache: default data cache. Page read from disk. BUFFER: Buffer header for buffer 0x28288000 (Mass head) page=0x28287000 bdnew=0x00000000 bdold=0x00000000 bhash=0x00000000 bmass_next=0x00000000 bmass_prev=0x00000000 bdbid=4 bvirtpg= [ 0x28288070 vpgdevno=5 vpvpn=131644 vdisk=0x21DE1CB4 ] bmass_head=0x28288000 bmass_tail=0x28288000 bcache_desc=0x2828B3F0 (cache name='default data cache') bpool_desc=0x00000000 bdbtable=0x00000000 Mass bkeep=0 Mass bpawaited=0 Mass btripsleft=0 Mass btripsleft_orig=0 bmass_size=4096 (4K pool) bunref_cnt=0 bmass_stat=0x0800 (0x00000800 (MASS_NOTHASHED)) bbuf_stat=0x0 (0x00000000) Buffer blpageno=65822 bpg_size=4k Mass blpageno=65822 (Buffer slot #: 0) bxls_pin=0x00000000 bxls_next=0x00000000 bspid=0 bxls_flushseq=0 bxls_pinseq=0 bcurrxdes=0x00000000 Latch and the wait queue: Latch (address: 0x28288020) latchmode: 0x0 (FREE_LATCH) latchowner: 0 latchnoofowners: 0 latchwaitq: 0x00000000 latchwaitqt: 0x00000000 Latch wait queue: PAGE HEADER: Page header for page 0x28287000 pageno=65822 nextpg=66249 prevpg=65821 ptnid=1431673117 timestamp=0000 0040a817 lastrowoff=4080 level=0 indid=2 freeoff=4091 minlen=11 page status bits: 0x82 (0x0080 (PG_FIXED), 0x0002 (PG_LEAF)) DATA: Offset 32 - row length=11 # varlen cols=0 Data page RID=(61615, 65) 28287020 ( 0): 00be8301 00aff000 004100 .........A. Row-Offset table for variable-length columns: [<varcol number>, <offset from start of the row>, <varcol length>] Offset 43 - row length=11 # varlen cols=0 Data page RID=(61752, 198) 2828702B ( 0): 00bf8301 0038f100 00c600 .....8..... Row-Offset table for variable-length columns: [<varcol number>, <offset from start of the row>, <varcol length>] Offset 54 - row length=11 # varlen cols=0 Data page RID=(61818, 239) 28287036 ( 0): 00c08301 007af100 00ef00 .....z..... Row-Offset table for variable-length columns: [<varcol number>, <offset from start of the row>, <varcol length>]

 

还是分析页面上的第一行。索引叶子层直接指向了数据页面，某一页面上的某一行。

 

Offset 32 - row length=11 # varlen cols=0 Data page RID=(61615, 65)
28287020 (     0):  00be8301 00aff000 004100             .........A.
Row-Offset table for variable-length columns:
        [<varcol number>, <offset from start of the row>, <varcol length>]

 

继续往下，dbcc page(4,61615,1)找到第65行数据。

 

Offset 1072 - row ID=65 row length=16 # varlen cols=1
28287430 (     0):  0141be83 0100 1000 7a68616e 67020d08  .A......zhang...
28287440 (    16):
Row-Offset table for variable-length columns:
        [<varcol number>, <offset from start of the row>, <varcol length>]
        [1, 8, 5]

可以看出00be8301 00 aff000 004100中的be8301 00 和0141be83 01001000 7a68616e 67020d08中的

be83 0100 是一致的。也就是说索引中间层level=1是指向叶子层上的第一页。并且包含索引键的数据99262。

 

从16进制数据中分析出来，页61615第65行的数据为：id=99262,name='zhang'。

 

 

暂时就分析这么多。可能有些地方说的不太明白。见谅！