PostgreSQL数据页Page中的行数据分析
短信预约 -IT技能 免费直播动态提醒
这篇文章主要介绍“PostgreSQL数据页Page中的行数据分析”，在日常操作中，相信很多人在PostgreSQL数据页Page中的行数据分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL数据页Page中的行数据分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
一、测试数据
详见上一节，数据文件中的内容如下：
[xdb@localhost utf8db]$ hexdump -C $PGDATA/base/16477/24801
00000000 01 00 00 00 88 20 2a 12 00 00 00 00 28 00 60 1f |..... *.....(.`.|
00000010 00 20 04 20 00 00 00 00 d8 9f 4e 00 b0 9f 4e 00 |. . ......N...N.|
00000020 88 9f 4e 00 60 9f 4e 00 00 00 00 00 00 00 00 00 |..N.`.N.........|
00000030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
*
00001f60 e5 1b 18 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
00001f70 04 00 03 00 02 08 18 00 04 00 00 00 13 34 20 20 |.............4 |
00001f80 20 20 20 20 20 05 64 00 e4 1b 18 00 00 00 00 00 | .d.........|
00001f90 00 00 00 00 00 00 00 00 03 00 03 00 02 08 18 00 |................|
00001fa0 03 00 00 00 13 33 20 20 20 20 20 20 20 05 63 00 |.....3 .c.|
00001fb0 e3 1b 18 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
00001fc0 02 00 03 00 02 08 18 00 02 00 00 00 13 32 20 20 |.............2 |
00001fd0 20 20 20 20 20 05 62 00 e2 1b 18 00 00 00 00 00 | .b.........|
00001fe0 00 00 00 00 00 00 00 00 01 00 03 00 02 08 18 00 |................|
00001ff0 01 00 00 00 13 31 20 20 20 20 20 20 20 05 61 00 |.....1 .a.|
00002000二、Items（Tuples）
每个Tuple包括两部分，第一部分是Tuple头部信息，第二部分是实际的数据。
相关数据结构如下：
//--------------------- class="lazy" data-src/include/storage/off.h
typedef uint16 OffsetNumber;
//--------------------- class="lazy" data-src/include/storage/block.h
typedef struct BlockIdData
{
uint16 bi_hi;
uint16 bi_lo;
} BlockIdData;
typedef BlockIdData *BlockId;
//--------------------- class="lazy" data-src/include/storage/itemptr.h
typedef struct ItemPointerData
{
BlockIdData ip_blkid;
OffsetNumber ip_posid;
}
//--------------------- class="lazy" data-src/include/access/htup_details.h
typedef struct HeapTupleFields
{
TransactionId t_xmin;
TransactionId t_xmax;
union
{
CommandId t_cid;
TransactionId t_xvac;
} t_field3;
} HeapTupleFields;
typedef struct DatumTupleFields
{
int32 datum_len_;
int32 datum_typmod;
Oid datum_typeid;
} DatumTupleFields;
struct HeapTupleHeaderData
{
union
{
HeapTupleFields t_heap;
DatumTupleFields t_datum;
} t_choice;
ItemPointerData t_ctid;
#define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK2 2
uint16 t_infomask2;
#define FIELDNO_HEAPTUPLEHEADERDATA_INFOMASK 3
uint16 t_infomask;
#define FIELDNO_HEAPTUPLEHEADERDATA_HOFF 4
uint8 t_hoff;
#define FIELDNO_HEAPTUPLEHEADERDATA_BITS 5
bits8 t_bits[FLEXIBLE_ARRAY_MEMBER];
};结构体展开，详见下表：
Field Type Length Offset Description
t_xmin TransactionId 4 bytes 0 insert XID stamp
t_xmax TransactionId 4 bytes 4 delete XID stamp
t_cid CommandId 4 bytes 8 insert and/or delete CID stamp (overlays with t_xvac)
t_xvac TransactionId 4 bytes 8 XID for VACUUM operation moving a row version
t_ctid ItemPointerData 6 bytes 12 current TID of this or newer row version
t_infomask2 uint16 2 bytes 18 number of attributes, plus various flag bits
t_infomask uint16 2 bytes 20 various flag bits
t_hoff uint8 1 byte 22 offset to user data
//注意：t_cid和t_xvac为联合体，共用存储空间从上一节我们已经得出第1个Tuple的偏移为8152，下面使用hexdump对其中的数据逐个解析：
t_xmin
[xdb@localhost ~]$ hexdump -C $PGDATA/base/16477/24801 -s 8152 -n 4
00001fd8 e2 1b 18 00 |....|
00001fdc
[xdb@localhost ~]$ echo $((0x00181be2))
1580002t_xmax
[xdb@localhost ~]$ hexdump -C $PGDATA/base/16477/24801 -s 8156 -n 4
00001fdc 00 00 00 00 |....|
00001fe0t_cid/t_xvac
[xdb@localhost ~]$ hexdump -C $PGDATA/base/16477/24801 -s 8160 -n 4
00001fe0 00 00 00 00 |....|
00001fe4t_ctid
[xdb@localhost ~]$ hexdump -C $PGDATA/base/16477/24801 -s 8164 -n 6
00001fe4 00 00 00 00 01 00 |......|
00001fea
//ip_blkid=\x0000，即blockid=0
//ip_posid=\x0001，即posid=1，第1个tuplet_infomask2
[xdb@localhost ~]$ hexdump -C $PGDATA/base/16477/24801 -s 8170 -n 2
00001fea 03 00 |..|
00001fec
//t_infomask2=\x0003，3代表什么意思？我们看看t_infomask2的说明
#define HEAP_NATTS_MASK 0x07FF
#define HEAP_KEYS_UPDATED 0x2000
#define HEAP_HOT_UPDATED 0x4000
#define HEAP_ONLY_TUPLE 0x8000
#define HEAP2_XACT_MASK 0xE000
//根把十六进制值转换为二进制显示
11111111111 #define HEAP_NATTS_MASK 0x07FF
10000000000000 #define HEAP_KEYS_UPDATED 0x2000
100000000000000 #define HEAP_HOT_UPDATED 0x4000
1000000000000000 #define HEAP_ONLY_TUPLE 0x8000
1110000000000000 #define HEAP2_XACT_MASK 0xE000
1111111111111110 #define SpecTokenOffsetNumber 0xfffe
//前（低）11位为属性的个数，3意味着有3个属性（字段）t_infomask
[xdb@localhost ~]$ hexdump -C $PGDATA/base/16477/24801 -s 8172 -n 2
00001fec 02 08 |..|
00001fee
[xdb@localhost ~]$ echo $((0x0802))
2050
[xdb@localhost ~]$ echo "obase=2;2050"|bc
100000000010
//t_infomask=\x0802，十进制值为2050，二进制值为100000000010
//t_infomask说明
1 #define HEAP_HASNULL 0x0001
10 #define HEAP_HASVARWIDTH 0x0002
100 #define HEAP_HASEXTERNAL 0x0004
1000 #define HEAP_HASOID 0x0008
10000 #define HEAP_XMAX_KEYSHR_LOCK 0x0010
100000 #define HEAP_COMBOCID 0x0020
1000000 #define HEAP_XMAX_EXCL_LOCK 0x0040
10000000 #define HEAP_XMAX_LOCK_ONLY 0x0080
#define HEAP_XMAX_SHR_LOCK (HEAP_XMAX_EXCL_LOCK | HEAP_XMAX_KEYSHR_LOCK)
#define HEAP_LOCK_MASK (HEAP_XMAX_SHR_LOCK | HEAP_XMAX_EXCL_LOCK | \
HEAP_XMAX_KEYSHR_LOCK)
100000000 #define HEAP_XMIN_COMMITTED 0x0100
1000000000 #define HEAP_XMIN_INVALID 0x0200
#define HEAP_XMIN_FROZEN (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID)
10000000000 #define HEAP_XMAX_COMMITTED 0x0400
100000000000 #define HEAP_XMAX_INVALID 0x0800
1000000000000 #define HEAP_XMAX_IS_MULTI 0x1000
10000000000000 #define HEAP_UPDATED 0x2000
100000000000000 #define HEAP_MOVED_OFF 0x4000
1000000000000000 #define HEAP_MOVED_IN 0x8000
#define HEAP_MOVED (HEAP_MOVED_OFF | HEAP_MOVED_IN)
1111111111110000 #define HEAP_XACT_MASK 0xFFF0
//\x0802，二进制100000000010表示第2位和第12位为1，
//意味着存在可变长属性（HEAP_HASVARWIDTH），XMAX无效（HEAP_XMAX_INVALID）t_hoff
[xdb@localhost ~]$ hexdump -C $PGDATA/base/16477/24801 -s 8174 -n 1
00001fee 18 |.|
00001fef
[xdb@localhost ~]$ echo $((0x18))
24
//用户数据开始偏移为24，即8152+24说完了Tuple的头部数据，接下来我们看看实际的数据存储。上一节我们得到Tuple总的长度是39，计算得到数据大小为39-24=15。
[xdb@localhost ~]$ hexdump -C $PGDATA/base/16477/24801 -s 8176 -n 15
00001ff0 01 00 00 00 13 31 20 20 20 20 20 20 20 05 61 |.....1 .a|
00001fff
回顾我们的表结构：
create table t_page (id int,c1 char(8),c2 varchar(16));
第1个字段为int，第2个字段为定长字符，第3个字段为变长字符。
相应的数据：
id=\x00000001，数字1
c1=\x133120202020202020，字符串，无需高低位变换，第1个字节\x13为标志位，后面是字符'1'+7个空格
c2=\x0561，字符串，第1个字节\x05为标志位，后面是字符'a'到此，关于“PostgreSQL数据页Page中的行数据分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/52.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
