PostgreSQL btree索引原理和应用场景

秒速五厘米 2022-08-30 11:21 370阅读 0赞

## PG数据库众多开放特性概述 ##

*  开放的数据类型接口，使得PG支持超级丰富的数据类型，除了传统数据库支持的类型，  
    还支持GIS，JSON，RANGE，IP，ISBN，图像特征值，化学，DNA等等扩展的类型，用  
    户还可以根据实际业务扩展更多的类型。
 *  开放的操作符接口，使得PG不仅仅支持常见的类型操作符，还支持扩展的操作符，例  
    如 距离符，逻辑并、交、差符号，图像相似符号，几何计算符号等等扩展的符号，  
    用户还可以根据实际业务扩展更多的操作符。
 *  开放的外部数据源接口，使得PG支持丰富的外部数据源，例如可以通过FDW读写  
    MySQL, redis, mongo, oracle, sqlserver, hive, www, hbase, ldap, 等等只要你能想到的数据  
    源都可以通过FDW接口读写。
 *  开放的语言接口，使得PG支持几乎地球上所有的编程语言作为数据库的函数、存储过  
    程语言，例如plpython , plperl , pljava , plR , plCUDA , plshell等等。用户可以通过  
    language handler扩展PG的语言支持。
 *  开放的索引接口，使得PG支持非常丰富的索引方法，例如btree , hash , gin , gist , sp-gist ,  
    brin , bloom , rum , zombodb , bitmap (greenplum extend)，用户可以根据不同的数据类型，  
    以及查询的场景，选择不同的索引。
 *  PG内部还支持BitmapAnd, BitmapOr的优化方法，可以合并多个索引的扫描操作，从而  
    提升多个索引数据访问的效率。

## btree索引原理和应用场景 ##

PostgreSQL索引结构：

*  meta page和root page是一定有的，meta page需要一个页来存储，表示指向root  
    page的page id。
 *  随着记录数的增加，一个root page可能存不下所有的heap item，就会有leaf page，  
    甚至branch page，甚至多层的branch page。
 *  一共有几层branch 和 leaf，可以用btree page元数据的 level 来表示。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzbWFydGtpbGxlcg_size_16_color_FFFFFF_t_70]

### 索引工具介绍 ###

如何访问索引结构  
1、create extension pageinspect  
2、查看meta块 select \* from bt\_metap(‘tab1\_pkey’);  
3、查看root page的stats select \* from bt\_page\_stats(‘tab1\_pkey’,1);  
4、查看root（leaf）页里面的内容： select \* from bt\_page\_items(‘tab1\_pkey’,1);  
5、根据ctid来访问表： select \* from tab1 where ctid=’(0,1)’;

### 第一种情况 ###

1、环境准备

postgres=# create extension pageinspect; 
    postgres=# create table tab1(id int primary key, info text); 
    CREATE TABLE 
    postgres=# insert into tab1 select generate_series(1,100), md5(random()::text); 
    INSERT 0 100 
    postgres=# vacuum analyze tab1; 
    VACUUM

第一种情况：  
有1层（0）结构，包括meta page, root page  
2、查看meta块  
![在这里插入图片描述][20210719222157512.png]  
此时level 0，root块为1。  
3、根据root page id = 1，查看root page的stats  
`ndx=# select * from bt_page_stats('tab1_pkey',1);`  
![在这里插入图片描述][20210719222234238.png]  
此时：btpo=0，说明处于第0层。  
btpo\_flags=3，说明它既是leaf又是root页。即：root\_page（2）+leaf\_page（1）=3  
注：  
meta page  
root page ：表示为btpo\_flags=2  
branch page ：表示为btpo\_flags=0  
leaf page ：表示为btpo\_flags=1  
4、查看root（leaf）页里面的内容  
`indx=# select * from bt_page_items('tab1_pkey',1);`  
![在这里插入图片描述][20210719222333277.png]  
此时ctid就是指向表的行id，类似于oracle的rowid，PG中为tid。data就是索引列的值，16进制。

5、根据ctid来访问表  
`indx=# select * from tab1 where ctid='(0,1)';`  
![在这里插入图片描述][20210719222419209.png]  
6、查看表的数据来验证  
`indx=# select * from tab1 limit 2;`  
![在这里插入图片描述][20210719222453508.png]

### 第二种情况 ###

有2层（0，1）结构，包括meta page, root page, leaf page.

create table t_btree(id int, info text); 
    insert into t_btree select generate_series(1,10000), md5(random()::text) ; 
    create index idx_t_btree_1 on t_btree using btree (id);

1、查看meta数据  
indx=\# select \* from bt\_metap(‘idx\_t\_btree\_1’);  
![在这里插入图片描述][20210719222604914.png]  
root块在第3块  
2、根据root page id 查看root page的stats  
indx=\# select \* from bt\_page\_stats(‘idx\_t\_btree\_1’,3);  
![在这里插入图片描述][20210719222633139.png]  
3、查看root page存储的 leaf page items (指向leaf page)  
indx=\# select \* from bt\_page\_items(‘idx\_t\_btree\_1’,3);  
![在这里插入图片描述][20210719222701198.png]  
一共28个叶块。data存储的是这个leaf page存储的最小值。  
4、查看第一个叶块统计  
indx=\# select \* from bt\_page\_stats(‘idx\_t\_btree\_1’,1);  
![在这里插入图片描述][20210719222731808.png]  
btpo=0，说明是最底层，btpo\_flags=1，即叶块。  
5、查看其它叶块统计，当查询到第30块时，显示超出块的范围  
indx=\# select \* from bt\_page\_stats(‘idx\_t\_btree\_1’,29);  
![在这里插入图片描述][20210719222809557.png]  
6、查看第一个叶块的内容  
indx=\# select \* from bt\_page\_items(‘idx\_t\_btree\_1’,1);  
![在这里插入图片描述][20210719222833234.png]  
7、根据CTID查看表中的行数据  
select \* from t\_btree where ctid=’(0,1)’;  
![在这里插入图片描述][20210719222855535.png]

### 第三种 ###

记录数超过1层结构的索引所能够存储的记录数时，会分裂为2层结构，除了meta page和root  
page，还可能包含1层branch page以及1层leaf page。  
1、建表

create table tab2 (id int primary key, info text); 
    postgres=# insert into tab2 select trunc(random()*10000000), md5(random()::text) from 
    generate_series(1,1000000) on conflict on constraint tab2_pkey do nothing; 
    INSERT 0 951379 
    postgres=# vacuum analyze tab2;

2、查看meta page，可以看到root page id = 412, 索引的level=2，即包括1级 branch 和 1级 leaf。  
postgres=\# select \* from bt\_metap(‘tab2\_pkey’);  
![在这里插入图片描述][20210719222958806.png]  
3、根据root page id 查看root page的stats  
indx=\# select \* from bt\_page\_stats(‘tab2\_pkey’, 412);  
![在这里插入图片描述][20210719223022913.png]  
btpo = 2 当前在第二层，另外还表示下层是1。  
btpo\_flags = 2 说明是root page  
4、查看root page存储的 branch page items (指向branch page)  
postgres=\# select \* from bt\_page\_items(‘tab2\_pkey’, 412);  
![在这里插入图片描述][20210719223053421.png]  
5、根据branch page id查看stats  
indx=\# select \* from bt\_page\_stats(‘tab2\_pkey’, 3);  
![在这里插入图片描述][20210719223115140.png]  
6、查看branch page存储的 leaf page ctid (指向leaf page)  
indx=\# indx=\# select \* from bt\_page\_items(‘tab2\_pkey’, 3);  
![在这里插入图片描述][20210719223143532.png]  
6、查看branch page存储的 leaf page ctid (指向leaf page)  
indx=\# indx=\# select \* from bt\_page\_items(‘tab2\_pkey’, 3);  
![在这里插入图片描述][20210719223208346.png]  
只要不是最右边的页，第一条都代表右页的起始item。  
第二条才是当前页的起始ctid  
注意所有branch page的起始item对应的data都是空的。  
也就是说它不存储当前branch page包含的所有leaf pages的索引字段内容的最小值。  
7、根据ctid 查看leaf page的统计  
indx=\# select \* from bt\_page\_stats(‘tab2\_pkey’, 1);  
![在这里插入图片描述][20210719223245313.png]  
btpo = 0 当前在第0层，即最底层，这里存储的是heap ctid  
btpo\_flags = 1 说明是leaf page  
第0层叶块，第1层枝块，第2层root块。  
8、查看leaf页的指向表的ctid  
indx=\# select \* from bt\_page\_items(‘tab2\_pkey’, 1);  
![在这里插入图片描述][20210719223324160.png]  
9、通过ctid查看表的数据  
indx=\# select \* from tab2 where ctid=’(1748,1)’;  
![在这里插入图片描述][20210719223407744.png]

## 索引案例应用 ##

利用查看索引数据块的变化，去证明Autovacuum是否会维护索引。  
1、环境搭建

create table tbl_test (id int, info text, c_time timestamp);
    insert into tbl_test select generate_series(1,100000),md5(random()::text),clock_timestamp();
    create index tbl_test_id_ind on tbl_test (id);

2、索引信息  
–查看索引元数据  
select \* from bt\_metap(‘tbl\_test\_id\_ind’);  
–查看索引root根统计  
select \* from bt\_page\_stats(‘tbl\_test\_id\_ind’,3);  
–查看索引叶块内容（此时没有发生数据更新）  
indx=\# select \* from bt\_page\_items(‘tbl\_test\_id\_ind’,1);  
![在这里插入图片描述][20210719223522229.png]  
3、更新表数据，导致autovacuum触发  
update tbl\_test set info=md5(random()::text) where id < 20060;  
4、查看索引叶块的内容变化（autovacuum前）  
indx=\# select \* from bt\_page\_items(‘tbl\_test\_id\_ind’,1);  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzbWFydGtpbGxlcg_size_16_color_FFFFFF_t_70 1]  
5、查看索引叶块的内容变化（autovacuum后）  
indx=\# select \* from bt\_page\_items(‘tbl\_test\_id\_ind’,1);  
![在这里插入图片描述][20210719223625484.png]  
观察后发现索引块的信息更新了，原来的索引行被删除。说明autovacuum会自动维护索引信息。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzbWFydGtpbGxlcg_size_16_color_FFFFFF_t_70]: /images/20220829/d8fa778029c545a197d9793ed6a24be2.png
[20210719222157512.png]: /images/20220829/a8bf51555a1148fea2ccd45ee8e86ebc.png
[20210719222234238.png]: /images/20220829/271f2e6549b64afb95bda08806a60aff.png
[20210719222333277.png]: /images/20220829/31f5d700df144f2fa51189f3a8c497e7.png
[20210719222419209.png]: /images/20220829/28117c048d1549c7ae5f813f2ac41f7b.png
[20210719222453508.png]: /images/20220829/96b401a8fbfb4da6a5ddd33645a281b9.png
[20210719222604914.png]: /images/20220829/916f1309852c47828b077335d41410cb.png
[20210719222633139.png]: /images/20220829/16a83f44414148b29f743f63fe203e7a.png
[20210719222701198.png]: /images/20220829/632cf92b0c144941a5cb149f448dbc87.png
[20210719222731808.png]: /images/20220829/09e0b244f3934db98302c735f21f2d2f.png
[20210719222809557.png]: /images/20220829/d839f116c8084866bb0adce54cf4a95f.png
[20210719222833234.png]: /images/20220829/380586f812194aa8ab90b891dd2a970b.png
[20210719222855535.png]: /images/20220829/97d1e4ed949e404bb8e2e4aac5050492.png
[20210719222958806.png]: /images/20220829/d0c155007d86456fb70f38a7de419bb9.png
[20210719223022913.png]: /images/20220829/e7c9d763e12c4e5fb91700ae5b070dd0.png
[20210719223053421.png]: /images/20220829/bfa1f16bc6e24d3a85b42fa4e5b59057.png
[20210719223115140.png]: /images/20220829/801e79238a924f33ad455e653ca3c333.png
[20210719223143532.png]: /images/20220829/8de50c02da9944a1bebc190408777061.png
[20210719223208346.png]: /images/20220829/ed402563544c402fb7d3f7c0696ca0cf.png
[20210719223245313.png]: /images/20220829/9f5093c782704a068c3369fb5b1e8147.png
[20210719223324160.png]: /images/20220829/33f268363cb749caa3e9aa813551c2b6.png
[20210719223407744.png]: /images/20220829/75d738134eb248f59449bbfddaddc6be.png
[20210719223522229.png]: /images/20220829/086a7e0733f44b429dc3cbbc60fca106.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzbWFydGtpbGxlcg_size_16_color_FFFFFF_t_70 1]: /images/20220829/db357bec899640e4ba72515e77e09943.png
[20210719223625484.png]: /images/20220829/9ff71d8502234d43af5543e612b89eea.png