存档

文章标签 ‘物理存储结构’,文章数:9

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
————————————————————————————————

阅读全文…

标签: ,