这篇文章主要介绍“PostgreSQL数据页Page中的行数据分析”，在日常操作中，相信很多人在PostgreSQL数据页Page中的行数据分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL数据页Page中的行数据分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

详见上一节，数据文件中的内容如下：

[xdb@localhostutf8db]$hexdump-C$PGDATA/base/16477/24801000000000100000088202a12000000002800601f|.....*.....(.`.|000000100020042000000000d89f4e00b09f4e00|........N...N.|00000020889f4e00609f4e000000000000000000|..N.`.N.........|0000003000000000000000000000000000000000|................|*00001f60e51b1800000000000000000000000000|................|00001f7004000300020818000400000013342020|.............4|00001f802020202020056400e41b180000000000|.d.........|00001f9000000000000000000300030002081800|................|00001fa003000000133320202020202020056300|.....3.c.|00001fb0e31b1800000000000000000000000000|................|00001fc002000300020818000200000013322020|.............2|00001fd02020202020056200e21b180000000000|.b.........|00001fe000000000000000000100030002081800|................|00001ff001000000133120202020202020056100|.....1.a.|00002000二、Items（Tuples）

每个Tuple包括两部分，第一部分是Tuple头部信息，第二部分是实际的数据。

1、HeapTupleHeader

相关数据结构如下：

//---------------------src/include/storage/off.h/**OffsetNumber:**thisisa1-basedindexintothelinp(ItemIdData)arrayinthe*headerofeachdiskpage.*/typedefuint16OffsetNumber;//---------------------src/include/storage/block.h/**BlockId:**thisisastoragetypeforBlockNumber.inotherwords,thistype*isusedforon-diskstructures(e.g.,inHeapTupleData)whereas*BlockNumberisthetypeonwhichcalculationsareperformed(e.g.,*inaccessmethodcode).**theredoesn'tappeartobeanyreasontohaveseparatetypesexcept*forthefactthatBlockIdscanbeSHORTALIGN'd(andthereforeany*structuresthatcontainsthem,suchasItemPointerData,canalsobe*SHORTALIGN'd).thisisanimportantconsiderationforreducingthe*spacerequirementsofthelinepointer(ItemIdData)arrayoneach*pageandtheheaderofeachheaporindextuple,soitdoesn'tseem*wisetochangethiswithoutgoodreason.*/typedefstructBlockIdData{uint16bi_hi;uint16bi_lo;}BlockIdData;typedefBlockIdData*BlockId;/*blockidentifier*///---------------------src/include/storage/itemptr.h/**ItemPointer:**Thisisapointertoanitemwithinadiskpageofaknownfile*(forexample,across-linkfromanindextoitsparenttable).*blkidtellsuswhichblock,posidtellsuswhichentryinthelinp*(ItemIdData)arraywewant.**Note:becausethereisanitempointerineachtupleheaderandindex*tupleheaderondisk,it'sveryimportantnottowastespacewith*structurepaddingbytes.Thestructisdesignedtobesixbyteslong*(itcontainsthreeint16fields)butafewcompilerswillpaditto*eightbytesunlesscoerced.Weapplyappropriatepersuasionwhere*possible.Ifyourcompilercan'tbemadetoplayalong,you'llwaste*lotsofspace.*/typedefstructItemPointerData{BlockIdDataip_blkid;OffsetNumberip_posid;}//---------------------src/include/access/htup_details.htypedefstructHeapTupleFields{TransactionIdt_xmin;/*insertingxactID*/TransactionIdt_xmax;/*deletingorlockingxactID*/union{CommandIdt_cid;/*insertingordeletingcommandID,orboth*/TransactionIdt_xvac;/*old-styleVACUUMFULLxactID*/}t_field3;}HeapTupleFields;typedefstructDatumTupleFields{int32datum_len_;/*varlenaheader(donottouchdirectly!)*/int32datum_typmod;/*-1,oridentifierofarecordtype*/Oiddatum_typeid;/*compositetypeOID,orRECORDOID*//**datum_typeidcannotbeadomainovercomposite,onlyplaincomposite,*evenifthedatumismeantasavalueofadomain-over-compositetype.*ThisisinlinewiththegeneralprinciplethatCoerceToDomaindoesnot*changethephysicalrepresentationofthebasetypevalue.**Note:fieldorderingischosenwiththoughtthatOidmightsomeday*widento64bits.*/}DatumTupleFields;structHeapTupleHeaderData{union{HeapTupleFieldst_heap;DatumTupleFieldst_datum;}t_choice;ItemPointerDatat_ctid;/*currentTIDofthisornewertuple(ora*speculativeinsertiontoken)*//*FieldsbelowheremustmatchMinimalTupleData!*/#defineFIELDNO_HEAPTUPLEHEADERDATA_INFOMASK22uint16t_infomask2;/*numberofattributes+variousflags*/#defineFIELDNO_HEAPTUPLEHEADERDATA_INFOMASK3uint16t_infomask;/*variousflagbits,seebelow*/#defineFIELDNO_HEAPTUPLEHEADERDATA_HOFF4uint8t_hoff;/*sizeofheaderincl.bitmap,padding*//*^-23bytes-^*/#defineFIELDNO_HEAPTUPLEHEADERDATA_BITS5bits8t_bits[FLEXIBLE_ARRAY_MEMBER];/*bitmapofNULLs*//*MOREDATAFOLLOWSATENDOFSTRUCT*/};

结构体展开，详见下表：

FieldTypeLengthOffsetDescriptiont_xminTransactionId4bytes0insertXIDstampt_xmaxTransactionId4bytes4deleteXIDstampt_cidCommandId4bytes8insertand/ordeleteCIDstamp(overlayswitht_xvac)t_xvacTransactionId4bytes8XIDforVACUUMoperationmovingarowversiont_ctidItemPointerData6bytes12currentTIDofthisornewerrowversiont_infomask2uint162bytes18numberofattributes,plusvariousflagbitst_infomaskuint162bytes20variousflagbitst_hoffuint81byte22offsettouserdata//注意：t_cid和t_xvac为联合体，共用存储空间

从上一节我们已经得出第1个Tuple的偏移为8152，下面使用hexdump对其中的数据逐个解析：
t_xmin

[xdb@localhost~]$hexdump-C$PGDATA/base/16477/24801-s8152-n400001fd8e21b1800|....|00001fdc[xdb@localhost~]$echo$((0x00181be2))1580002

t_xmax

[xdb@localhost~]$hexdump-C$PGDATA/base/16477/24801-s8156-n400001fdc00000000|....|00001fe0

t_cid/t_xvac

[xdb@localhost~]$hexdump-C$PGDATA/base/16477/24801-s8160-n400001fe000000000|....|00001fe4

t_ctid

[xdb@localhost~]$hexdump-C$PGDATA/base/16477/24801-s8164-n600001fe4000000000100|......|00001fea//ip_blkid=\x0000，即blockid=0//ip_posid=\x0001，即posid=1，第1个tuple

t_infomask2

[xdb@localhost~]$hexdump-C$PGDATA/base/16477/24801-s8170-n200001fea0300|..|00001fec//t_infomask2=\x0003，3代表什么意思？我们看看t_infomask2的说明/**informationstoredint_infomask2:*/#defineHEAP_NATTS_MASK0x07FF/*11bitsfornumberofattributes*//*bits0x1800areavailable*/#defineHEAP_KEYS_UPDATED0x2000/*tuplewasupdatedandkeycols*modified,ortupledeleted*/#defineHEAP_HOT_UPDATED0x4000/*tuplewasHOT-updated*/#defineHEAP_ONLY_TUPLE0x8000/*thisisheap-onlytuple*/#defineHEAP2_XACT_MASK0xE000/*visibility-relatedbits*///根把十六进制值转换为二进制显示11111111111#defineHEAP_NATTS_MASK0x07FF10000000000000#defineHEAP_KEYS_UPDATED0x2000100000000000000#defineHEAP_HOT_UPDATED0x40001000000000000000#defineHEAP_ONLY_TUPLE0x80001110000000000000#defineHEAP2_XACT_MASK0xE0001111111111111110#defineSpecTokenOffsetNumber0xfffe//前（低）11位为属性的个数，3意味着有3个属性（字段）

t_infomask

[xdb@localhost~]$hexdump-C$PGDATA/base/16477/24801-s8172-n200001fec0208|..|00001fee[xdb@localhost~]$echo$((0x0802))2050[xdb@localhost~]$echo"obase=2;2050"|bc100000000010//t_infomask=\x0802，十进制值为2050，二进制值为100000000010//t_infomask说明1#defineHEAP_HASNULL0x0001/*hasnullattribute(s)*/10#defineHEAP_HASVARWIDTH0x0002/*hasvariable-widthattribute(s)*/100#defineHEAP_HASEXTERNAL0x0004/*hasexternalstoredattribute(s)*/1000#defineHEAP_HASOID0x0008/*hasanobject-idfield*/10000#defineHEAP_XMAX_KEYSHR_LOCK0x0010/*xmaxisakey-sharedlocker*/100000#defineHEAP_COMBOCID0x0020/*t_cidisacombocid*/1000000#defineHEAP_XMAX_EXCL_LOCK0x0040/*xmaxisexclusivelocker*/10000000#defineHEAP_XMAX_LOCK_ONLY0x0080/*xmax,ifvalid,isonlyalocker*//*xmaxisasharedlocker*/#defineHEAP_XMAX_SHR_LOCK(HEAP_XMAX_EXCL_LOCK|HEAP_XMAX_KEYSHR_LOCK)#defineHEAP_LOCK_MASK(HEAP_XMAX_SHR_LOCK|HEAP_XMAX_EXCL_LOCK|\HEAP_XMAX_KEYSHR_LOCK)100000000#defineHEAP_XMIN_COMMITTED0x0100/*t_xmincommitted*/1000000000#defineHEAP_XMIN_INVALID0x0200/*t_xmininvalid/aborted*/#defineHEAP_XMIN_FROZEN(HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID)10000000000#defineHEAP_XMAX_COMMITTED0x0400/*t_xmaxcommitted*/100000000000#defineHEAP_XMAX_INVALID0x0800/*t_xmaxinvalid/aborted*/1000000000000#defineHEAP_XMAX_IS_MULTI0x1000/*t_xmaxisaMultiXactId*/10000000000000#defineHEAP_UPDATED0x2000/*thisisUPDATEdversionofrow*/100000000000000#defineHEAP_MOVED_OFF0x4000/*movedtoanotherplacebypre-9.0*VACUUMFULL;keptforbinary*upgradesupport*/1000000000000000#defineHEAP_MOVED_IN0x8000/*movedfromanotherplacebypre-9.0*VACUUMFULL;keptforbinary*upgradesupport*/#defineHEAP_MOVED(HEAP_MOVED_OFF|HEAP_MOVED_IN)1111111111110000#defineHEAP_XACT_MASK0xFFF0/*visibility-relatedbits*///\x0802，二进制100000000010表示第2位和第12位为1，//意味着存在可变长属性（HEAP_HASVARWIDTH），XMAX无效（HEAP_XMAX_INVALID）

t_hoff

[xdb@localhost~]$hexdump-C$PGDATA/base/16477/24801-s8174-n100001fee18|.|00001fef[xdb@localhost~]$echo$((0x18))24//用户数据开始偏移为24，即8152+24

2、Tuple

说完了Tuple的头部数据，接下来我们看看实际的数据存储。上一节我们得到Tuple总的长度是39，计算得到数据大小为39-24=15。

[xdb@localhost~]$hexdump-C$PGDATA/base/16477/24801-s8176-n1500001ff0010000001331202020202020200561|.....1.a|00001fff回顾我们的表结构：createtablet_page(idint,c1char(8),c2varchar(16));第1个字段为int，第2个字段为定长字符，第3个字段为变长字符。相应的数据：id=\x00000001，数字1c1=\x133120202020202020，字符串，无需高低位变换，第1个字节\x13为标志位，后面是字符'1'+7个空格c2=\x0561，字符串，第1个字节\x05为标志位，后面是字符'a'

到此，关于“PostgreSQL数据页Page中的行数据分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！