Sybase索引物理存储结构分析
迄今已分析出来了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结果信息。
1 2 | dbcc traceon(3604) go |
2.
查看syspartitions表的信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 | 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | 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'。
暂时就分析这么多。可能有些地方说的不太明白。见谅！
————————————————————————————————
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：索引 存储结构 分析 非聚簇 index storage non-clustered
————————————————————————————————