展示页 - 分片的表和索引
在数据库的微观世界中，页结构是支撑庞大数据帝国的基石。GBase 8s国产数据库系统，其页结构的优化直接关系到数据存储的效率和查询性能。本文将带您深入探索GBase 8s页结构的奥秘，从物理地址到逻辑地址，从数据页到索引页，逐一解析，探索分片的表和索引的内部世界。
1、准备元数据
drop database if exists test2;
create database test2 with log;
create table t1(c1 int, c2 char(100), c3 varchar(100)) fragment by round robin partition p1 in datadbs1, partition p2 in datadbs2;
-- This index is a partition index
create index i1 on t1(c1) fragment by expression partition ip1 mod(c1, 2) = 0 in datadbs1, partition ip2 mod(c1, 2) = 1 in datadbs2 ;2、准备数据
生成数据的脚本
[root@centos7_2 ix9111]# cat generate_data.py
#!/usr/bin/python
def main():
fp = open("data.unl", "w")
for i in range(0,1000):
fp.write("%d|%08dxxxxxxxxxxxxxxxxxxx|%08dxxxxxxxxxxxxxxxxxxxxxxxxxx\n" % (i, i, i))
fp.close()
if __name__ == '__main__':
main()生成数据，并加载数据
./ generate_data.py
dbaccess test2 -
load from 'data.unl' insert into t1;3、基于物理地址寻找页
- 获取物理地址：
下图中，黄色部分就是数据和索引的存储位置
[root@gbasehdr202 ids]# oncheck -pt test2:t1 TBLspace Report for test2:root.t1 Table fragment partition p1 in DBspace datadbs1 Physical Address 4:7 Extents Logical Page Physical Page Size Physical Pages 0 4:201 32 32 32 4:237 32 32 Table fragment partition p2 in DBspace datadbs2
Physical Address 5:5 Creation date 07/19/2024 11:42:45 TBLspace Flags 902 Row Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 206 Number of special columns 1 Number of keys 0 Number of extents 2 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 8 Next extent size 64 Number of pages allocated 64 Number of pages used 40 Number of data pages 39 Number of rows 500 Partition partnum 5242882 Partition lockid 4194308
Extents Logical Page Physical Page Size Physical Pages 0 5:57 32 32 32 5:93 32 32
Index i1 fragment partition ip1 in DBspace datadbs1
Physical Address 4:8 Creation date 07/19/2024 11:42:51 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 206 Number of special columns 0 Number of keys 1 Number of extents 2 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 4 Next extent size 8 Number of pages allocated 8 Number of pages used 7 Number of data pages 0 Number of rows 0 Partition partnum 4194309 Partition lockid 4194308
Extents Logical Page Physical Page Size Physical Pages 0 4:197 4 4 4 4:233 4 4
Index i1 fragment partition ip2 in DBspace datadbs2
Physical Address 5:6 Creation date 07/19/2024 11:42:51 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 206 Number of special columns 0 Number of keys 1 Number of extents 2 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 4 Next extent size 8 Number of pages allocated 8 Number of pages used 7 Number of data pages 0 Number of rows 0 Partition partnum 5242883 Partition lockid 4194308
Extents Logical Page Physical Page Size Physical Pages 0 5:53 4 4 4 5:89 4 4 |
3.1 展示数据部分
oncheck -pP 4 202 (201是bitmap页，需要跳过）
[root@gbasehdr202 ids]# oncheck -pP 4 202
addr stamp chksum nslots flag type frptr frcnt next prev
4:202 379016 c843 13 1 DATA 1844 148 0 0
slot ptr len flag
1 24 140 0
2 164 140 0
3 304 140 0
4 444 140 0
5 584 140 0
6 724 140 0
7 864 140 0
8 1004 140 0
9 1144 140 0
10 1284 140 0
11 1424 140 0
12 1564 140 0
13 1704 140 0
slot 1:
0: 0 0 0 0 30 30 30 30 30 30 30 30 78 78 78 78 ....00000000xxxx
16: 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 20 xxxxxxxxxxxxxxx
32: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
48: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
64: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
80: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
96: 20 20 20 20 20 20 20 20 0 22 30 30 30 30 30 30 ."000000
112: 30 30 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00xxxxxxxxxxxxxx
128: 78 78 78 78 78 78 78 78 78 78 78 78 xxxxxxxxxxxx....
略展示另一个分片的数据
oncheck -pP 5 58 （57是bitmap页，需要跳过）
[root@gbasehdr202 ids]# oncheck -pP 5 58
addr stamp chksum nslots flag type frptr frcnt next prev
5:58 379019 c8b1 13 1 DATA 1844 148 0 0
slot ptr len flag
1 24 140 0
2 164 140 0
3 304 140 0
4 444 140 0
5 584 140 0
6 724 140 0
7 864 140 0
8 1004 140 0
9 1144 140 0
10 1284 140 0
11 1424 140 0
12 1564 140 0
13 1704 140 0
slot 1:
0: 0 0 0 1 30 30 30 30 30 30 30 31 78 78 78 78 ....00000001xxxx
16: 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 20 xxxxxxxxxxxxxxx
32: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
48: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
64: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
80: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
96: 20 20 20 20 20 20 20 20 0 22 30 30 30 30 30 30 ."000000
112: 30 31 78 78 78 78 78 78 78 78 78 78 78 78 78 78 01xxxxxxxxxxxxxx
128: 78 78 78 78 78 78 78 78 78 78 78 78 xxxxxxxxxxxx....
略3.2 展示索引部分
oncheck -pP 4 198 （197页需要跳过）
[root@gbasehdr202 ids]# oncheck -pP 4 198
addr stamp chksum nslots flag type frptr frcnt next prev
4:198 381318 d141 5 70 BTREE 60 1964 0 0
slot ptr len flag
1 24 8 0
2 36 8 0
3 44 8 0
4 52 8 0
5 32 4 0
slot 1:
0: 80 0 0 e8 0 0 0 3 ...h............
slot 2:
0: 80 0 1 d2 0 0 0 2 ...R............
slot 3:
0: 80 0 2 bc 0 0 0 4 ...<............
slot 4:
0: 80 0 3 a6 0 0 0 5 ...&............
slot 5:
0: 0 0 0 6 ................展示另一个分片
oncheck -pP 5 54 (53需要跳过)
[root@gbasehdr202 ids]# oncheck -pP 5 54
addr stamp chksum nslots flag type frptr frcnt next prev
5:54 381326 d1b8 5 70 BTREE 60 1964 0 0
slot ptr len flag
1 24 8 0
2 36 8 0
3 44 8 0
4 52 8 0
5 32 4 0
slot 1:
0: 80 0 0 e9 0 0 0 3 ...i............
slot 2:
0: 80 0 1 d3 0 0 0 2 ...S............
slot 3:
0: 80 0 2 bd 0 0 0 4 ...=............
slot 4:
0: 80 0 3 a7 0 0 0 5 ...'............
slot 5:
0: 0 0 0 6 ................4、基于逻辑地址1
获取 partnum，其中黄色部分就是每个分片的partnum
[root@gbasehdr202 ids]# oncheck -pt test2:t1
TBLspace Report for test2:root.t1
Table fragment partition p1 in DBspace datadbs1
Physical Address 4:7 Creation date 07/19/2024 11:42:45 TBLspace Flags 902 Row Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 206 Number of special columns 1 Number of keys 0 Number of extents 2 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 8 Next extent size 64 Number of pages allocated 64 Number of pages used 40 Number of data pages 39 Number of rows 500 Partition partnum 4194308 Partition lockid 4194308
Extents Logical Page Physical Page Size Physical Pages 0 4:201 32 32 32 4:237 32 32
Table fragment partition p2 in DBspace datadbs2
Physical Address 5:5 Creation date 07/19/2024 11:42:45 TBLspace Flags 902 Row Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 206 Number of special columns 1 Number of keys 0 Number of extents 2 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 8 Next extent size 64 Number of pages allocated 64 Number of pages used 40 Number of data pages 39 Number of rows 500 Partition partnum 5242882 Partition lockid 4194308
Extents Logical Page Physical Page Size Physical Pages 0 5:57 32 32 32 5:93 32 32
Index i1 fragment partition ip1 in DBspace datadbs1
Physical Address 4:8 Creation date 07/19/2024 11:42:51 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 206 Number of special columns 0 Number of keys 1 Number of extents 2 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 4 Next extent size 8 Number of pages allocated 8 Number of pages used 7 Number of data pages 0 Number of rows 0 Partition partnum 4194309 Partition lockid 4194308
Extents Logical Page Physical Page Size Physical Pages 0 4:197 4 4 4 4:233 4 4
Index i1 fragment partition ip2 in DBspace datadbs2
Physical Address 5:6 Creation date 07/19/2024 11:42:51 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 206 Number of special columns 0 Number of keys 1 Number of extents 2 Current serial value 1 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 4 Next extent size 8 Number of pages allocated 8 Number of pages used 7 Number of data pages 0 Number of rows 0 Partition partnum 5242883 Partition lockid 4194308
Extents Logical Page Physical Page Size Physical Pages 0 5:53 4 4 4 5:89 4 4 |
4.1 展示数据部分
[root@gbasehdr202 ids]# oncheck -pp 4194308 1
addr stamp chksum nslots flag type frptr frcnt next prev
4:202 379016 c843 13 1 DATA 1844 148 0 0
slot ptr len flag
1 24 140 0
2 164 140 0
3 304 140 0
4 444 140 0
5 584 140 0
6 724 140 0
7 864 140 0
8 1004 140 0
9 1144 140 0
10 1284 140 0
11 1424 140 0
12 1564 140 0
13 1704 140 0
slot 1:
0: 0 0 0 0 30 30 30 30 30 30 30 30 78 78 78 78 ....00000000xxxx
16: 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 20 xxxxxxxxxxxxxxx
32: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
48: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
64: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
80: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
96: 20 20 20 20 20 20 20 20 0 22 30 30 30 30 30 30 ."000000
112: 30 30 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00xxxxxxxxxxxxxx
128: 78 78 78 78 78 78 78 78 78 78 78 78 xxxxxxxxxxxx....
略展示第二个分片的：
[root@gbasehdr202 ids]# oncheck -pp 5242882 1
addr stamp chksum nslots flag type frptr frcnt next prev
5:58 379019 c8b1 13 1 DATA 1844 148 0 0
slot ptr len flag
1 24 140 0
2 164 140 0
3 304 140 0
4 444 140 0
5 584 140 0
6 724 140 0
7 864 140 0
8 1004 140 0
9 1144 140 0
10 1284 140 0
11 1424 140 0
12 1564 140 0
13 1704 140 0
slot 1:
0: 0 0 0 1 30 30 30 30 30 30 30 31 78 78 78 78 ....00000001xxxx
16: 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 20 xxxxxxxxxxxxxxx
32: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
48: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
64: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
80: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
96: 20 20 20 20 20 20 20 20 0 22 30 30 30 30 30 30 ."000000
112: 30 31 78 78 78 78 78 78 78 78 78 78 78 78 78 78 01xxxxxxxxxxxxxx
128: 78 78 78 78 78 78 78 78 78 78 78 78 xxxxxxxxxxxx....
略4.2 展示索引部分
[root@gbasehdr202 ids]# oncheck -pp 4194309 1
addr stamp chksum nslots flag type frptr frcnt next prev
4:198 381318 d141 5 70 BTREE 60 1964 0 0
slot ptr len flag
1 24 8 0
2 36 8 0
3 44 8 0
4 52 8 0
5 32 4 0
slot 1:
0: 80 0 0 e8 0 0 0 3 ...h............
slot 2:
0: 80 0 1 d2 0 0 0 2 ...R............
slot 3:
0: 80 0 2 bc 0 0 0 4 ...<............
slot 4:
0: 80 0 3 a6 0 0 0 5 ...&............
slot 5:
0: 0 0 0 6 ................展示另一个分片的：
[root@gbasehdr202 ids]# oncheck -pp 5242883 1
addr stamp chksum nslots flag type frptr frcnt next prev
5:54 381326 d1b8 5 70 BTREE 60 1964 0 0
slot ptr len flag
1 24 8 0
2 36 8 0
3 44 8 0
4 52 8 0
5 32 4 0
slot 1:
0: 80 0 0 e9 0 0 0 3 ...i............
slot 2:
0: 80 0 1 d3 0 0 0 2 ...S............
slot 3:
0: 80 0 2 bd 0 0 0 4 ...=............
slot 4:
0: 80 0 3 a7 0 0 0 5 ...'............
slot 5:
0: 0 0 0 6 ................5、基于逻辑地址2
获取数据部分的partnum
[root@centos7_2 test]# dbaccess test2 -
Database selected.
> select f.fragtype,f.partn from sysfragments f, systables t where f.tabid=t.tabid and t.tabname='t1';
fragtype partn
T 4194391
T 5242884
I 4194392
I 5242885
4 row(s) retrieved.fragtype是 T的是数据部分，是I的是索引部分。
获取了partnum后，展示页的方式同上一节。
6、大页
6.1 大页介绍
- AIX和windows默认页大小是4K，其他是2K。
- root dbspace总是用默认页大小。
- 创建页的时候，最大16K，而且是默认页大小的整数倍。
配置了一种新的页大小，需要在onconfig中针对这种页大小配置一个BUFFERPOOL。
6.2 展示大页
大页的页偏移比较容易让人困惑，大页的页偏移不是指偏移了多少个大页，而是偏移了多少个默认页。
公式是：
- pg_offset = (chunk_pgsize / system_pgsize) * page_num
比如：在linux环境下，页大小是8K，第3个页的页偏移是：
- pg_offset = (8K / 2K) * 3 = 12
因此，展示这个大页的命令是：
- oncheck -pP chunk_number 12
通过本文的深入解析我们不仅揭开了GBase 8s页结构的面纱，更为数据库管理员和开发者提供了实用的操作指南。理解并掌握页结构的内部构造，将有助于您在数据库性能优化的道路上更进一步。我们希望本系列文章能成为您探索数据库深层世界的得力助手，为您的数据库管理之旅增添一份信心与力量。
