11、PostgreSQL索引

一、索引分类

btree、 hash、gin、gist、sp-gist、brin、bloom、 rum、zombodb、bitmap

二、Btree结构

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来表示。

安装扩展准备数据

--使用pageinspect扩展工具查看结构，数据准备
create extension pageinspect;
--主键索引使用的是btree索引，索引名字 tb_order_pkey
create table tb_order(id int primary key, order_no varchar(255)); 
insert into tb_order select generate_series(1,100), md5(random()::varchar);
--analyze 统计数据库表数据，统计结果存储到pg_statistic系统表中
--vacuum 用于清理死亡元组占用的存储空间
vacuum analyze tb_order;

三、btree索引演变过程

1、 btree索引一层结构

（1）、查看meta块

select * from bt_metap('tb_order_pkey');--查看meta块

此时level为0即高度为1，root块为1

 （2）、根据root 的 page id =1查看 root page的stats

select * from bt_page_stats('tb_order_pkey', 1);--查看page的统计状态信息

live_items:存活的索引行

dead_items：死亡的索引行

avg_item_size:平均索引行大小

page_size:块大小，详细看最后说明

free_size：块空余大小

btpo_prev：块左边

btpo_next：块右边

btpo：当前块层次，0代表处于0层

btpo_flags：当前块类型，3代表：他既是leaf又是root，即2+1

• meta page
• root page:表示为btpo flags=2
• branch page :表示为btpo flags=0
• leaf page:表示为btpo flags=1

（3）、查看指定索引块内容

select * from bt_page_items('tb_order_pkey', 1);--查看指定索引块内容

 索引：ctid+索引列的值

（4）、通过索引ctid访问数据

select * from tb_order where ctid='(0,1)'; --通过索引ctid访问数据

2、 btree索引二层结构

包括 meta page, root page, leaf page

--使用pageinspect扩展工具查看结构，数据准备
create extension pageinspect;
--主键索引使用的是btree索引，索引名字 tb_order_pkey
create table tb_order2(id int primary key, order_no varchar(255)); 
insert into tb_order2 select generate_series(1,10000), md5(random()::varchar);
--analyze 统计数据库表数据，统计结果存储到pg_statistic系统表中
--vacuum 用于清理死亡元组占用的存储空间
vacuum analyze tb_order2;

 （1）、查看meta块

Vacuum用于清理死亡元组占用的存储空间，默认删除或因更新过期（为了MVVC）的元组不会被物理删除。因此需要周期性的进行Vacuum，尤其是频繁更新的表
Analyze命令用于统计数据库表数据，统计结果存储到pg_statistic系统表中。数据库进行基于成本的优化（CBO）时通过统计数据优化SQL语句的解释计划。

select * from bt_metap('tb_order2_pkey');

此时level为1即高度为2，root块id为3

 （2）、根据root 的 page id =3查看 root page的stats

select * from bt_page_stats('tb_order2_pkey', 3);--根据root 的 page id =3查看stats

live_items:存活的页块

dead_items：死亡的页块

avg_item_size:平均索引行大小

page_size:块大小，详细看最后说明

free_size：块空余大小

btpo_prev：块左边

btpo_next：块右边

btpo：当前块层次，1代表处于2层，表示下面还有一层

btpo_flags：当前块类型，3代表：他既是leaf又是root，即2+1

• meta page
• root page:表示为btpo flags=2
• branch page :表示为btpo flags=0
• leaf page:表示为btpo flags=1

（3）、查看指定索引块内容

select * from bt_page_items('tb_order2_pkey', 3);

总共28个页块，

 （4）、查看每个的页块统计

select * from bt_page_stats('tb_order2_pkey', 1);

btpo flags=1表示为  leaf page

btpo_prev：0

btpo_next：2

  （4）、查看每个的页块内容

select * from bt_page_items('tb_order2_pkey', 1);

（5）、通过ctid查看数据

select * from tb_order2 where ctid='(3,1)'; 

 

3、 btree索引三层结构

包括 meta page, root page, leaf page，branch page

四、Hash索引

1、Hash索引结构

·哈希索引项只存储每个索引项的哈希代码，而不是实际的数据值

2、应用场景

• hash索引存储的是被索引字段VALUE的哈希值，只支持等值查询。
• hash索引特别适用于字段VALUE非常长（不适合b-tree索引，因为b-tree一个PAGE至少要存储3个索引行，所以不支持特别长的VALUE）的场景，例如很长的字符串，并且用户只需要等值搜索，建议使用hash index。

create table article(id int, name text);

insert into article select generate_series(1,100),repeat(md5(random()::text),10000);

create index idx_article_name on article using btree (name );

创建hash索引九可以

create index idx_article_hash_name  on article using hash (name );

五、gin索引

1、gin索引结构

GIN是Generalized lnverted Index的缩写。就是所谓的倒排索引，它处理的数据类型的值不是原来的，而是由元素构成。我们称之为复合类型。

存储被索引字段的VALUE或VALUE的元素，以及行号的list或tree。

col_val:(tid_list or tid_tree),col_val_elements:(tid_list or tid_tree)比如
('hank','15:3 21:4')中，表示hank在15:3和21:4这两个位置出现过
 

2、应用场景

当需要搜索多值类型内的VALUE时，适合多值类型，例如数组、全文检索、
TOKEN。（根据不同的类型，支持相交、包含、大于、在左边、在右边等搜索)
当用户的数据比较稀疏时，如果要搜索某个VALUE的值，可以适应btree_gin支持
普通btree支持的类型。(支持btree的操作符)
当用户需要按任意列进行搜索时，gin支持多列展开单独建立索引域，同时支持
内部多域索引的bitmapAnd, bitmapor合并，快速的返回按任意列搜索请求的数据。