您好,登錄后才能下訂單哦!
本篇內(nèi)容介紹了“PostgreSQL的Page中頁(yè)頭和行數(shù)據(jù)指針分析”的有關(guān)知識(shí),在實(shí)際案例的操作過程中,不少人都會(huì)遇到這樣的困境,接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧!希望大家仔細(xì)閱讀,能夠?qū)W有所成!
-- 創(chuàng)建一張表,插入幾行數(shù)據(jù)
drop table if exists t_page;
create table t_page (id int,c1 char(8),c2 varchar(16));
insert into t_page values(1,'1','a');
insert into t_page values(2,'2','b');
insert into t_page values(3,'3','c');
insert into t_page values(4,'4','d');
-- 獲取該表對(duì)應(yīng)的數(shù)據(jù)文件
testdb=# select pg_relation_filepath('t_page');
pg_relation_filepath
----------------------
base/16477/24801
(1 row)
-- Dump數(shù)據(jù)文件中的數(shù)據(jù)
[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
上一節(jié)提到過PageHeaderData,其數(shù)據(jù)結(jié)構(gòu)如下:
typedef struct PageHeaderData
{
/* XXX LSN is member of *any* block, not only page-organized ones */
PageXLogRecPtr pd_lsn; /* LSN: next byte after last byte of xlog
* record for last change to this page */
uint16 pd_checksum; /* checksum */
uint16 pd_flags; /* flag bits, see below */
LocationIndex pd_lower; /* offset to start of free space */
LocationIndex pd_upper; /* offset to end of free space */
LocationIndex pd_special; /* offset to start of special space */
uint16 pd_pagesize_version;
TransactionId pd_prune_xid; /* oldest prunable XID, or zero if none */
ItemIdData pd_linp[1]; /* beginning of line pointer array */
} PageHeaderData;
下面根據(jù)數(shù)據(jù)文件中的數(shù)據(jù)使用hexdump查看并逐個(gè)進(jìn)行解析。
[xdb@localhost utf8db]$ hexdump -C $PGDATA/base/16477/24801 -s 0 -n 8
00000000 01 00 00 00 88 20 2a 12 |..... *.|
00000008
數(shù)據(jù)文件的8個(gè)Bytes存儲(chǔ)的是LSN,其中最開始的4個(gè)Bytes是TimelineID,在這里是\x0000 0001(即數(shù)字1),后面的4個(gè)Bytes是\x122a2088,組合起來(lái)LSN為1/122A2088
注意:
A、0000000&0000008是hexdump工具的輸出,不是數(shù)據(jù)內(nèi)容
B、X86使用小端模式,閱讀字節(jié)碼時(shí)注意高低位變換
[xdb@localhost utf8db]$ hexdump -C $PGDATA/base/16477/24801 -s 8 -n 2
00000008 00 00 |..|
0000000a
checksum為\x0000
[xdb@localhost utf8db]$ hexdump -C $PGDATA/base/16477/24801 -s 10 -n 2
0000000a 00 00 |..|
0000000c
flags為\x0000
[xdb@localhost utf8db]$ hexdump -C $PGDATA/base/16477/24801 -s 12 -n 2
0000000c 28 00 |(.|
0000000e
lower為\x0028,十進(jìn)制值為40
[xdb@localhost utf8db]$ hexdump -C $PGDATA/base/16477/24801 -s 14 -n 2
0000000e 60 1f |`.|
00000010
[xdb@localhost utf8db]$ echo $((0x1f60))
8032
upper為\x1f60,十進(jìn)制為8032
[xdb@localhost utf8db]$ hexdump -C $PGDATA/base/16477/24801 -s 16 -n 2
00000010 00 20 |. |
00000012
Special Space為\x2000,十進(jìn)制值為8192
[xdb@localhost utf8db]$ hexdump -C $PGDATA/base/16477/24801 -s 18 -n 2
00000012 04 20 |. |
00000014
pagesize_version為\x2004,十進(jìn)制為8196(即版本4)
[xdb@localhost utf8db]$ hexdump -C $PGDATA/base/16477/24801 -s 20 -n 4
00000014 00 00 00 00 |....|
00000018
prune_xid為\x0000,即0
PageHeaderData之后是ItemId數(shù)組,每個(gè)元素占用的空間為4Bytes,數(shù)據(jù)結(jié)構(gòu):
typedef struct ItemIdData
{
unsigned lp_off:15,/* offset to tuple (from start of page) */
lp_flags:2,/* state of item pointer, see below */
lp_len:15;/* byte length of tuple */
} ItemIdData;
typedef ItemIdData* ItemId;
[xdb@localhost utf8db]$ hexdump -C $PGDATA/base/16477/24801 -s 24 -n 2
00000018 d8 9f |..|
0000001a
取低15位
[xdb@localhost utf8db]$ echo $((0x9fd8 & ~$((1<<15))))
8152
表示第1個(gè)Item(tuple)從8152開始
[xdb@localhost utf8db]$ hexdump -C $PGDATA/base/16477/24801 -s 26 -n 2
0000001a 4e 00 |N.|
0000001c
取高15位
[xdb@localhost utf8db]$ echo $((0x004e >> 1))
39
表示第1個(gè)Item(tuple)的大小為39
取第17-16位,01,即1
“PostgreSQL的Page中頁(yè)頭和行數(shù)據(jù)指針分析”的內(nèi)容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站,小編將為大家輸出更多高質(zhì)量的實(shí)用文章!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。