PostgreSQL Page頁結構解析（5）- B-Tree索引儲存結構#1
本文簡單介紹了在PG資料庫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個位元組，資料結構如下：
/*
* 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個位元組
三、小結
小結一下，主要有以下幾點：
1、資料儲存：索引資料頁頭和與普通資料表頁頭一樣的結構，佔用24個位元組，ItemIds佔用4個位元組；
2、索引entries：結構為IndexTupleData+Bitmap+Value；
3、內容檢視：可透過pageinspect外掛，推薦透過hexdump物理檔案檢視，有助於理解資料結構和資料的底層儲存格式
更詳細的B-Tree物理/邏輯結構在下一節解析。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2374919/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL Page頁結構解析（6）- B-Tree索引儲存結構#2SQL索引
- PostgreSQL Page頁結構解析（7）- B-Tree索引儲存結構#3SQL索引
- PostgreSQL Page頁結構解析（1）-基礎SQL
- PostgreSQL儲存引擎之page結構SQL儲存引擎
- PostgreSQL Page頁結構解析（3）- 行資料SQL
- PostgreSQL Page頁結構解析（2）- 頁頭和行資料指標SQL指標
- PostgreSQL Page頁結構解析（4）- 執行DML時表佔用空間解析SQL
- PostgreSQL儲存引擎之heap tuple結構SQL儲存引擎
- 儲存結構
- PostgreSQL 資料庫學習 - 1.資料庫體系結構之儲存結構SQL資料庫
- MyRocks儲存引擎資料結構解析儲存引擎資料結構
- 如何從零學習PostgreSQL Page結構SQL
- VSAN儲存結構解析+儲存資料恢復案例資料恢復
- 《MySQL 基礎篇》十一：索引的儲存結構MySql索引
- 理解SQL Server 2008索引的儲存結構YDSQLServer索引
- JanusGraph -- 儲存結構
- CentOS 儲存結構CentOS
- 資料的儲存結構淺析LSM-Tree和B-tree
- MySQL索引及優化（1）儲存引擎和底層資料結構MySql索引優化儲存引擎資料結構
- PostgreSQL 資料頁Page解析（1）- 基礎SQL
- 資料結構知識點--儲存結構與邏輯結構資料結構
- 【資料結構——圖和圖的儲存結構】資料結構
- 深度解析C#中LinkedList<T>的儲存結構C#
- redis 儲存結構原理 2Redis
- php圖的儲存結構PHP
- HBase 資料儲存結構
- InnoDB記錄儲存結構
- 【PHP資料結構】圖的概念和儲存結構PHP資料結構
- 【RocketMQ】RocketMQ儲存結構設計MQ
- etcd MVCC 儲存結構及流程MVC
- 圖（Graph）——圖的儲存結構
- 【部落格383】etcd儲存結構
- 儲存器的層次結構
- innodb表空間儲存結構
- hadoop異構儲存+lucene索引Hadoop索引
- MySQL 索引結構MySql索引
- PostgreSQL的B-tree索引SQL索引
- PostgreSQL：程式結構SQL