本篇内容主要讲解“PostgreSQL中B-Tree索引的物理存储内容有哪些”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL中B-Tree索引的物理存储内容有哪些”吧!
一、测试数据
创建数据表，插入数据并创建索引。
testdb=# -- 创建一张表，插入几行数据
testdb=# drop table if exists t_index;
t_index values(16,'4','d');
-- 创建索引
alter table t_index add constraint pk_t_index primary key(id);DROP TABLE
testdb=# create table t_index (id int,c1 char(8),c2 varchar(16));
CREATE TABLE
testdb=# insert into t_index values(2,'1','a');
INSERT 0 1
testdb=# insert into t_index values(4,'2','b');
INSERT 0 1
testdb=# insert into t_index values(8,'3','c');
INSERT 0 1
testdb=# insert into t_index values(16,'4','d');
INSERT 0 1
testdb=#
testdb=# -- 创建索引
testdb=# alter table t_index add constraint pk_t_index primary key(id);
ALTER TABLE
testdb=# -- 索引物理文件
testdb=# SELECT pg_relation_filepath('pk_t_index');
pg_relation_filepath
----------------------
base/16477/26637
(1 row)索引文件raw data
[xdb@localhost utf8db]$ hexdump -C base/16477/26637
00000000 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 05 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 |... ...... ... .|
00002020 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 00 00 00 00 00 |................|
*
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二、B-Tree索引物理存储
我们可以通过pageinspect插件查看索引的存储结构。
Page 0是索引元数据页：
testdb=# -- 查看索引页头数据
testdb=# select * from page_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
(1 row)
testdb=# -- 查看索引元数据页
testdb=# select * from bt_metap('pk_t_index');
magic | version | root | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples
--------+---------+------+-------+----------+-----------+-------------+-------------------------
340322 | 3 | 1 | 0 | 1 | 0 | 0 | -1
(1 row)root=1提示root页在第1页，通过page_header查看页头数据：
testdb=# select * from page_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
(1 row)每个索引entries结构为IndexTupleData+Bitmap+Value，其中IndexTupleData占8个字节，Bitmap占4个字节，Value占4字节，合计占用16个字节，数据结构如下：
typedef struct IndexTupleData
{
ItemPointerData t_tid;
unsigned short t_info;
} IndexTupleData;
typedef IndexTupleData *IndexTuple;
typedef struct IndexAttributeBitMapData
{
bits8 bits[(INDEX_MAX_KEYS + 8 - 1) / 8];
} IndexAttributeBitMapData;
typedef IndexAttributeBitMapData * IndexAttributeBitMap;通过bt_page_items函数查看索引entries：
testdb=# select * from bt_page_items('pk_t_index',1);
itemoffset | ctid | itemlen | nulls | vars | data
------------+-------+---------+-------+------+-------------------------
1 | (0,1) | 16 | f | f | 02 00 00 00 00 00 00 00
2 | (0,2) | 16 | f | f | 04 00 00 00 00 00 00 00
3 | (0,3) | 16 | f | f | 08 00 00 00 00 00 00 00
4 | (0,4) | 16 | f | f | 10 00 00 00 00 00 00 00
(4 rows)相应的物理索引文件内容：
[xdb@localhost utf8db]$ hexdump -C base/16477/26637
00000000 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 05 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 |... ...... ... .|
00002020 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 00 00 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
-- 以上为索引数据Page 0的索引数据ItemPointerData
[xdb@localhost utf8db]$ hexdump -C base/16477/26637 -s 16304 -n 6
00003fb0 00 00 00 00 04 00 |......|
00003fb6
-- blockid=\x0000，offset=\x0004t_info
[xdb@localhost utf8db]$ hexdump -C base/16477/26637 -s 16310 -n 2
00003fb6 10 00 |..|
00003fb8
t_info=\x0010，即16，表示tuple（索引项）大小为16个字节到此，相信大家对“PostgreSQL中B-Tree索引的物理存储内容有哪些”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
