溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容主要講解“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
我們可以通過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個字節，數據結構如下：
/*
* Index tuple header structure
*
* All index tuples start with IndexTupleData. If the HasNulls bit is set,
* this is followed by an IndexAttributeBitMapData. The index attribute
* values follow, beginning at a MAXALIGN boundary.
*
* Note that the space allocated for the bitmap does not vary with the number
* of attributes; that is because we don't have room to store the number of
* attributes in the header. Given the MAXALIGN constraint there's no space
* savings to be had anyway, for usual values of INDEX_MAX_KEYS.
*/
typedef struct IndexTupleData
{
ItemPointerData t_tid; /* reference TID to heap tuple */
/* ---------------
* t_info is laid out in the following fashion:
*
* 15th (high) bit: has nulls
* 14th bit: has var-width attributes
* 13th bit: AM-defined meaning
* 12-0 bit: size of tuple
* ---------------
*/
unsigned short t_info; /* various info about tuple */
} IndexTupleData; /* MORE DATA FOLLOWS AT END OF STRUCT */
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=\x0004
t_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索引的物理存儲內容有哪些”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
