PostgreSQL中B-Tree索引的物理存储内容是什么？

1.测试数据

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

testdb=# -- 创建表并插入多行数据 testdb=# drop table if isn't t_index; t_index value(16,'4','d');-- 创建索引 alter table t_index 添加约束 pk_t_index 主键(id);DROP TABLEtestdb=# create table t_index (id int,c1 char(8),c2 varchar( 16));CREATE TABLEtestdb=# 插入 t_index 值(2,'1',' a');INSERT 0 1testdb=# 插入 t_index 值(4,'2','b');INSERT 0 1testdb=# insert into t_indexvalues(8,'3','c');INSERT 0 1testdb= #insert into t_indexvalues(16,'4','d');INSERT 0 1testdb=#testdb=# -- 创建索引testdb =# 更改表 t_index 添加 constraint pk_t_index 主键(id);ALTER TABLE testdb=# -- 索引物理文件 testdb=# SELECT pg_relation_filepath('pk_t_index'); pg_relation_filepath ---------- ------------ base/16477/26637(1行)

索引文件原始数据

 [xdb@localhost utf8db]$ hexdump -C 基/16477/2663700000000 01 00 00 00 20 5d 0e db 00 00 00 00 40 00 f0 1f |.... ]......@...|00000010 f0 1f 04 20 00 00 00 00 62 31 0 5 00 03 00 00 00 |......b1......|00000020 01 00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 | ………………|00000030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 f0 bf |…………|00000040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |............|*00001ff0 00 00 00 00 00 00 00 00 00 00 00 00 08 00 00 00 |.. ...........|00002000 01 00 00 00 98 5c 0e db 00 00 00 00 28 00 b0 1f |.....\......(...|00002010 f0 1f 04 20 00 00 00 00 e0 9f 20 00 d0 9f 20 00 |... ...... ... .|00002023 c0 9f 20 00 b0 9f 20 00 b0 9f 20 00 00 00 00 00 |. . ... .....|00002030 00 00 00 00 00 00 00 00 0000 00 00 00 00 00 00 |..... ... | 00 00 00 00 00 00 00 |............|00003fd0 00 00 00 00 02 00 10 00 04 00 00 00 00 00 00 00 |............ ...|00003fe0 00 00 00 00 01 00 10 00 02 00 00 00 00 00 00 00 |............|00003ff0 00 00 00 00 00 00 00 00 00 00 00 00 03 00 00 00 |............|00004000

2. B-Tree索引物理存储

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

testdb=# -- 查看索引页头数据 testdb=# select * from page_header(get_raw_page('pk_t_index',0)); LSN |校验和|旗帜|降低|上 |特别|页面大小 |版本 | prune_xid ------------+----------+--------+---- ---+--------+- ------+----------+----------+-------- ---- 1/DB0E5D20 | 0 | 0 | 64 | 64 8176 | 8176 | 8192 | 4 | 0(1 row)testdb=# -- 查看索引元数据页 testdb=# select * from bt_metap('pk_t_index');魔法|版本 |根 |级别 |快根 |快速水平|最旧的_xact |最后清理编号元组s ----- ---+---------+------+--------+------------+---- ------ -+-------------+------------------------- 340322 | 3 | 1 | 0 | 1 | 。 (get_raw_page('pk_t_index',1)); LSN |校验和|旗帜|降低|上 |特别|页面大小 |版本 | prune_xid------------+---------- +--------+--------+--------+- ------+----------+---- -----+------------ 1/DB0E5C98 | 0 | 0 | 40 | 40 8112 | 8176 | 8192 | 4 | 0(1行)

每个索引条目结构为IndexTupleData+Bitmap+Value，其中IndexTupleData占8个字节，Bitmap占4个字节，Value占4个字节，总共16个字节。数据结构如下：

 /* * 索引元组头结构 * * 所有索引元组都以IndexTupleData 开头。如果设置了 HasNulls 位，* 后面跟着一个 IndexAttributeBitMapDatA。索引属性 * 值如下，从 MAXALIGN 边界开始。 * * 注意，为位图分配的空间不随属性的数量而变化；这是因为我们没有空间来存储标头中 * 属性的数量。考虑到 MAXALIGN 约束，对于 INDEX_MAX_KEYS 的通常值来说，无论如何都无法节省空间。 */ typedef struct IndexTupleData { ItemPointerData t_tid; /* 引用 TID 到堆元组 */ /* -------------------------- * t_info 按以下方式布局： * * 第 15 个（高）位：有空值 * 第 14 位：有 var-width 属性 * 第 13 位：AM 定义的含义 * 12-0 位：元组的大小 * --------------- * / 无符号短 t_info； /* 有关元组的各种信息 */ } IndexTupleData; /*结构体末尾有更多数据*/TypeDef Indextupledata*Indextuple; Typedef 结构 INDEXATRIBUTEBITMAPDATA {Bits8 位 [(Index _Max_keys+8-1)/8];} IndexattributeBitMapdata; TypeDef IndexattributebitMapdata*IndexattributeBi地图； 

>通过bt_page_items函数查看索引条目：

testdb=# select * from bt_page_items('pk_t_index',1);项目偏移 | ctid |项目名称 |空值 |变量 |数据 数据--------- ---+--------+--------+--------+--------+-- ----------------------- 1 1 | (0,1) 16 | f | f | 02 00 00 00 00 00 00 00 2 | (0,2) | 16 | 16 f | f | 04 00 00 00 00 00 00 3 | (0,3) | 16 | 16 f | f | 08 00 00 00 00 00 00 00 4 | (0,4) | 16 | 16 f | f | 10 00 00 00 00 00 00 00(4行)

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

[xdb@localhost utf8db]$ hexdump -C base/ 16477/2663700000000 01 00 00 00 20 5d 0e db 00 00 00 00 40 00 f0 1f |....]......@...|00000010 f0 1f 04 20 00 00 00 00 62 31 0 5 00 03 00 00 00 |. ......b1......|00000020 01 00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 |........................ ...|00000030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 f0 bf |............|00000040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |.. ..........|--以上是元数据的头数据页*00001ff0 00 00 00 00 00 00 00 00 00 00 00 00 08 00 00 00 |............|00002000 01 00 00 00 98 5c 0e db 00 00 00 00 28 00 b0 1f |..... \......(...|00002010 f0 1f 04 20 00 00 00 00 e0 9f 20 00 d0 9f 20 00 |... ... ... ... .| 00002023 c0 9f 20 00 b0 9f 20 00 b0 9f 20 00 00 00 00 00 |.. ... ... .....|00002030 00 00 00 00 00 00 00 00 00 00 00 0000 00 00 00 |. ...........|--以上是索引数据Page 0*00003fb0 00 00 00 00 04 00 10 00 10 00 00 00 00 00 00 00 |.....的头数据.......|00003fc0 00 00 00 00 03 00 10 00 08 00 00 00 00 00 00 00 |............ ....|00003fd0 00 00 00 00 02 00 10 00 04 00 00 00 00 00 00 00 |............|00003fe0 00 00 00 00 01 00 10 00 02 00 00 00 00 00 00 00 |........ ....|00003ff0 00 00 00 00 00 00 00 00 00 00 00 00 03 00 00 00 |.............|00004000--上面是索引号根据索引第0页的数据

ItemPointerData

[xdb@localhost utf8db]$ hexdump -C base/16477/26637 -s 16304 -n 600003fb0 00 0000 00 04 00                                                                                                                                                                                                                               通过本地主机 utf8db]$ hexdump -C base/16477/2 6637 -s 16310 -n 200003fb6 10 00 \x0010，即16，表示元组（索引项）大小为16字节

至此，相信大家对“PostgreSQL中B-Tree索引的物理存储内容有哪些”有了更深入的了解了？你不妨在实践中这样做！这是网站。更多相关内容，可以进入相关频道。询问、关注我们并继续学习！