本篇内容主要讲解“PostgreSQL中B-Tree索引的物理存储内容有哪些”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL中B-Tree索引的物理存储内容有哪些”吧!

创建数据表，插入数据并创建索引。

testdb=#--创建一张表，插入几行数据testdb=#droptableifexistst_index;t_indexvalues(16,'4','d');--创建索引altertablet_indexaddconstraintpk_t_indexprimarykey(id);DROPTABLEtestdb=#createtablet_index(idint,c1char(8),c2varchar(16));CREATETABLEtestdb=#insertintot_indexvalues(2,'1','a');INSERT01testdb=#insertintot_indexvalues(4,'2','b');INSERT01testdb=#insertintot_indexvalues(8,'3','c');INSERT01testdb=#insertintot_indexvalues(16,'4','d');INSERT01testdb=#testdb=#--创建索引testdb=#altertablet_indexaddconstraintpk_t_indexprimarykey(id);ALTERTABLEtestdb=#--索引物理文件testdb=#SELECTpg_relation_filepath('pk_t_index');pg_relation_filepath----------------------base/16477/26637(1row)

索引文件raw data

[xdb@localhostutf8db]$hexdump-Cbase/16477/266370000000001000000205d0edb000000004000f01f|....]......@...|00000010f01f0420000000006231050003000000|.......b1......|0000002001000000000000000100000000000000|................|000000300000000000000000000000000000f0bf|................|0000004000000000000000000000000000000000|................|*00001ff000000000000000000000000008000000|................|0000200001000000985c0edb000000002800b01f|.....\......(...|00002010f01f042000000000e09f2000d09f2000|.............|00002020c09f2000b09f2000b09f200000000000|.............|0000203000000000000000000000000000000000|................|*00003fb000000000040010001000000000000000|................|00003fc000000000030010000800000000000000|................|00003fd000000000020010000400000000000000|................|00003fe000000000010010000200000000000000|................|00003ff000000000000000000000000003000000|................|00004000二、B-Tree索引物理存储

我们可以通过pageinspect插件查看索引的存储结构。
Page 0是索引元数据页：

testdb=#--查看索引页头数据testdb=#select*frompage_header(get_raw_page('pk_t_index',0));lsn|checksum|flags|lower|upper|special|pagesize|version|prune_xid------------+----------+-------+-------+-------+---------+----------+---------+-----------1/DB0E5D20|0|0|64|8176|8176|8192|4|0(1row)testdb=#--查看索引元数据页testdb=#select*frombt_metap('pk_t_index');magic|version|root|level|fastroot|fastlevel|oldest_xact|last_cleanup_num_tuples--------+---------+------+-------+----------+-----------+-------------+-------------------------340322|3|1|0|1|0|0|-1(1row)

root=1提示root页在第1页，通过page_header查看页头数据：

testdb=#select*frompage_header(get_raw_page('pk_t_index',1));lsn|checksum|flags|lower|upper|special|pagesize|version|prune_xid------------+----------+-------+-------+-------+---------+----------+---------+-----------1/DB0E5C98|0|0|40|8112|8176|8192|4|0(1row)

每个索引entries结构为IndexTupleData+Bitmap+Value，其中IndexTupleData占8个字节，Bitmap占4个字节，Value占4字节，合计占用16个字节，数据结构如下：

/**Indextupleheaderstructure**AllindextuplesstartwithIndexTupleData.IftheHasNullsbitisset,*thisisfollowedbyanIndexAttributeBitMapData.Theindexattribute*valuesfollow,beginningataMAXALIGNboundary.**Notethatthespaceallocatedforthebitmapdoesnotvarywiththenumber*ofattributes;thatisbecausewedon'thaveroomtostorethenumberof*attributesintheheader.GiventheMAXALIGNconstraintthere'snospace*savingstobehadanyway,forusualvaluesofINDEX_MAX_KEYS.*/typedefstructIndexTupleData{ItemPointerDatat_tid;/*referenceTIDtoheaptuple*//*---------------*t_infoislaidoutinthefollowingfashion:**15th(high)bit:hasnulls*14thbit:hasvar-widthattributes*13thbit:AM-definedmeaning*12-0bit:sizeoftuple*---------------*/unsignedshortt_info;/*variousinfoabouttuple*/}IndexTupleData;/*MOREDATAFOLLOWSATENDOFSTRUCT*/typedefIndexTupleData*IndexTuple;typedefstructIndexAttributeBitMapData{bits8bits[(INDEX_MAX_KEYS+8-1)/8];}IndexAttributeBitMapData;typedefIndexAttributeBitMapData*IndexAttributeBitMap;

通过bt_page_items函数查看索引entries：

testdb=#select*frombt_page_items('pk_t_index',1);itemoffset|ctid|itemlen|nulls|vars|data------------+-------+---------+-------+------+-------------------------1|(0,1)|16|f|f|02000000000000002|(0,2)|16|f|f|04000000000000003|(0,3)|16|f|f|08000000000000004|(0,4)|16|f|f|1000000000000000(4rows)

相应的物理索引文件内容：

[xdb@localhostutf8db]$hexdump-Cbase/16477/266370000000001000000205d0edb000000004000f01f|....]......@...|00000010f01f0420000000006231050003000000|.......b1......|0000002001000000000000000100000000000000|................|000000300000000000000000000000000000f0bf|................|0000004000000000000000000000000000000000|................|--以上为元数据页的头部数据*00001ff000000000000000000000000008000000|................|0000200001000000985c0edb000000002800b01f|.....\......(...|00002010f01f042000000000e09f2000d09f2000|.............|00002020c09f2000b09f2000b09f200000000000|.............|0000203000000000000000000000000000000000|................|--以上为索引数据Page0的头部数据*00003fb000000000040010001000000000000000|................|00003fc000000000030010000800000000000000|................|00003fd000000000020010000400000000000000|................|00003fe000000000010010000200000000000000|................|00003ff000000000000000000000000003000000|................|00004000--以上为索引数据Page0的索引数据

ItemPointerData

[xdb@localhostutf8db]$hexdump-Cbase/16477/26637-s16304-n600003fb0000000000400|......|00003fb6--blockid=\x0000，offset=\x0004

t_info

[xdb@localhostutf8db]$hexdump-Cbase/16477/26637-s16310-n200003fb61000|..|00003fb8t_info=\x0010，即16，表示tuple（索引项）大小为16个字节

到此，相信大家对“PostgreSQL中B-Tree索引的物理存储内容有哪些”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！