一、索引分类
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合并,快速的返回按任意列搜索请求的数据。