PG索引类型[通俗易懂]

PG索引类型[通俗易懂]PG索引类型索引类型CREATEINDEX在一个指定表或者物化视图的指定列上创建一个索引,索引主要用来提高数据库的效率(尽管不合理的使用将导致较慢的效率)btree选择性越好(唯一值个数接近记录数)的列,越适合b-tree。当被索引列存储相关性越接近1或-1时,数据存储越有序,范围查询扫描的HEAPPAGE越少。 支持多列索引,默认最多32列,编译可改。(通过调整pg…

大家好,又见面了,我是你们的朋友全栈君。

PG索引类型

  • 索引类型

CREATE INDEX 在一个指定表或者物化视图的指定列上创建一个索引,索引主要用来提高数据库的效率(尽管不合理的使用将导致较慢的效率)

  1. btree

选择性越好(唯一值个数接近记录数)的列,越适合b-tree。

当被索引列存储相关性越接近1或-1时,数据存储越有序,范围查询扫描的HEAP PAGE越少。

 

支持多列索引,默认最多32列,编译可改。(通过调整pg_config_manual.h可以做到更大,但是还有另一个限制,indextuple不能超过约1/4的数据块(索引页)大小,也就是说复合索引列很多的情况下,可能会触发这个限制)

支持唯一索引。

索引策略 : <,<=,=,>=,>

 

PostgreSQL B-Tree是一种变种(high-concurrency B-tree management algorithm),算法详情请参考src/backend/access/nbtree/README

可以使用pageinspect插件,内窥B-Tree的结构。

 

https://github.com/digoal/blog/blob/master/201605/20160528_01.md

 

组合索引

虽然b-tree多列索引支持任意列的组合查询,但是最有效的查询还是包含驱动列条件的查询。

对于b-tree的多列索引来说,一个查询要扫描索引的哪些部分呢?

从驱动列开始算,按索引列的顺序算到非驱动列的第一个不相等条件为止(没有任何条件也算)。

(WHERE a = 5 AND b >= 42 AND c < 77),从a=5, b=42开始的所有索引条目,都会被扫描。

其他例子

(WHERE b >= 42 AND c < 77),所有索引条目,都会被扫描。只要不包含驱动列,则扫描所有索引条目。

(WHERE a = 5 AND c < 77),a=5的所有索引条目,都会被扫描。

(WHERE a >= 5 AND b=1 and c < 77),从a=5开始的所有索引条目,都会被扫描。

 

  1. hash

只有等值查询,并且被索引的列长度很长,可能超过数据库block的1/3时,建议使用hash索引。 PG 10 hash索引会产生WAL,确保了可靠性,同时支持流复制。PG 10 以前的版本,不建议使用hash indexcrash后需要rebuild,不支持流复制。

 

不支持多列索引。

不支持唯一索引。

索引策略 :=

 

原理src/backend/access/hash/README

 

应用场景

hash索引存储的是被索引字段VALUE的哈希值,只支持等值查询。

hash索引特别适用于字段VALUE非常长(不适合b-tree索引,因为b-tree一个PAGE至少要存储3个ENTRY,所以不支持特别长的VALUE)的场景,例如很长的字符串,并且用户只需要等值搜索,建议使用hash index。

PG10之后的版本hash index会写wal日志了,同时支持流复制。

  1. gist

GISTPG的一种通用索引接口,适合各种数据类型,特别适合异构的类型,例如几何类型,空间类型,范围类型等。

 

支持多列索引,默认最多32列,编译可改。

不支持唯一索引。

索引策略 :两维R-tree策略

            严格地在…左边,

            不扩展到…右边,

            重叠,

            不延伸到…左边,

            严格地在…右边,

            相同,

            包含,

            包含于,

            不扩展到…上面,

            严格地在…下面,

            严格地在…上面,

            不扩展到…下面

独有参数 BUFFERING

            建立索引时决定是否缓存建立的方法.使用OFF 关闭这个功能,ON 打开这个功         能. 使用AUTO 它初始化时是关闭的,但是当索引的达到effective_cache_size将          会打开.默认使用AUTO.

 

GiST是一个通用的索引接口,可以使用GiST实现b-tree, r-tree等索引结构。

不同的类型,支持的索引检索也各不一样。例如:

1、几何类型,支持位置搜索(包含、相交、在上下左右等),按距离排序。

2、范围类型,支持位置搜索(包含、相交、在左右等)。

3、IP类型,支持位置搜索(包含、相交、在左右等)。

4、空间类型(PostGIS),支持位置搜索(包含、相交、在上下左右等),按距离排序。

5、标量类型,支持按距离排序。

 

 

Generalized Search Tree,或者叫归纳树,用于解决一些b-tree, gin难以解决的数据减少问题,例如,范围是否相交,是否包含,地理位置中的点面相交,或者按点搜索附近的点,当然,它能实现的功能还不仅于此。

 

http://ata2-img.cn-hangzhou.img-pub.aliyun-inc.com/b343c2ca8162869c26fb2bed052c3664.jpg

http://ata2-img.cn-hangzhou.img-pub.aliyun-inc.com/dfd3c02e5ddc1c050d67294b395da169.jpg

GiST的灵魂是聚集,所以首先是聚集的动作,聚集后,在单个组内包含的KEY+HEAP行号会放到单个INDEX PAGE中。

聚集的范围作为一级结构,存储在GiST的entry 中,便于检索。

既然灵魂是聚集,那么GiST的性能就和他的聚集算法息息相关,PostgreSQL把这个接口留给了用户,用户在自定义数据类型时,如果要自己实现对应的GIST索引,那么就好好考虑这个类型聚集怎么做吧。

PostgreSQL内置的range, geometry等类型的GIST已经帮你做好了,你只需要做新增的类型,比如你新增了一个存储人体结构的类型,存储图片的类型,或者存储X光片的类型,怎么快速检索它们,那就是你要实现的GIST索引聚集部分了。

 

  1. spgist

不支持多列索引。

不支持唯一索引。

索引策略 : 点策略

            严格在左边,

            严格在右边,

            相同,

            包含,

            严格在下面,

            严格在上面

 

Space-Partitioned GIST

可以理解为GiST的扩展,有以下特点

1. nodes无交叉,(GiST是有交叉的,只是做了聚集,但是nodes(不同的index page)包含的内容是有交叉的)。

2. 索引深度是可变的

 

应用场景

1、几何类型,支持位置搜索(包含、相交、在上下左右等),按距离排序。

2、范围类型,支持位置搜索(包含、相交、在左右等)。

3IP类型,支持位置搜索(包含、相交、在左右等)。

 

  1. gin

btree相反,选择性越差,采用GIN索引效率越高。

 

支持多列索引,默认最多32列,编译可改。

不支持唯一索引。

索引策略 : 数组策略

            重叠,

            包含,

            包含于,

            相等

独有参数 FASTUPDATE

            快速更新技术.它是一个布尔类型参数:ON使能快速更新,OFF关闭这个功能

 

GIN的意思是通用倒排索引(Generalized Inverted Index)。 GIN被设计用于这样一种情况:被索引项是组合值,而被索引处理的查询需要搜索出现在这些组合值中的元素值。比如,项目可能是文档,查询可以是搜索包含多个特定单词的文档。

更新效率较差,比较适合静态数据。

 

gin索引,是将列(比如数组,全文检索类型)中的值拿出来,再存储到树形结构中(类似B+TREE,值+行号s),对于高频值,为了减少树的深度,行号s会存储在另外的页中。

由于GIN存储的是元素索引,所以当一条记录被插入或更新时,可能涉及到很多个元素,对GIN索引来说,就会涉及到很多ITEM的变更。

为了提升插入,更新,删除的性能,PostgreSQL支持类似MySQL的索引组织表类似的buffer ,先写入BUFFER,然后再合并到树里去。

而相比MySQL索引组织表更优一些的地方是,查询不会堵塞合并,也不会堵塞写入。因为查询时不需要等待BUFFER中的数据合并到树中,而是直接查询BUFFER(如果BUFFER非常大,可能查询速度会受到一定的影响)。

用户可通过参数来控制BUFFER的大小,GIN会在BUFFER增长到一定程度后自动进行合并。或者等VACUUM来合并。

所以一个完整的GIN索引长这样

http://ata2-img.cn-hangzhou.img-pub.aliyun-inc.com/c38b3d919fa5c12d19665d58bd5723be.jpg

使用注意

1. 为了提高更新速度,使用了FASTER UPDATE技术,当BUFFER很大时(可自己设置),查询速度可能会较慢。所以权衡插入和查询,建议设置合理的BUFFER大小。

2. 仅支持bitmap查询,也就是说取到所有的行号之后,排序,然后再去检索,好处显然是可以减少随机的HEAP PAGE扫描,但是坏处是,当涉及的行非常多(比如每行都包含了某个元素)很大时,排序耗费资源较多,耗时较长,从执行到获得第一条的时间较长,如果用户使用了LIMIT,也要等排序结束。

 

应用场景

1、当需要搜索多值类型内的VALUE时,适合多值类型,例如数组、全文检索、TOKEN。(根据不同的类型,支持相交、包含、大于、在左边、在右边等搜索)

2、当用户的数据比较稀疏时,如果要搜索某个VALUE的值,可以适应btree_gin支持普通btree支持的类型。(支持btree的操作符)

3、当用户需要按任意列进行搜索时,gin支持多列展开单独建立索引域,同时支持内部多域索引的bitmapAnd, bitmapOr合并,快速的返回按任意列搜索请求的数据。

 

gin多列索引支持任意列的组合查询。并且任意查询条件的查询效率都是一样的。

使用btree_gin插件,可以对任意标量数据类型创建GIN索引。

 

注意,目前gin还不支持sort,所以如果你有大数据量的ORDER BY limit 小数据输出需求,建议还是使用b-tree。

 

  1. BRIN

当数据与堆存储线性相关性很好时,可以采用BRIN索引。

 

支持多列索引,默认最多32列,编译可改。

对插入性能影响小于Btree索引。

brin多列索引支持任意列的组合查询。并且任意查询条件的查询效率都是一样的。

如果有brin组合查询的必要(比如多个与ctid线性相关的列的范围查询,无所谓线性的方向),任何时候都建议使用BRIN的multi column index,除非想针对不同的列使用不同的pages_per_range(比如有些列10个块的范围和另外一些列100个块的范围覆盖差不多,那么建议它们使用不同的pages_per_range)

 

BRIN 索引是块级索引,有别于B-TREE等索引,BRIN记录并不是以行号为单位记录索引明细,而是记录每个数据块或者每段连续的数据块的统计信息。因此BRIN索引空间占用特别的小,对数据写入、更新、删除的影响也很小。

BRIN属于LOSSLY索引,当被索引列的值与物理存储相关性很强时,BRIN索引的效果非常的好。

例如时序数据,在时间或序列字段创建BRIN索引,进行等值、范围查询时效果很棒。

 

 

  1. BLOOM

bloom filter是一个有损过滤器,使用有限的比特位存储一些唯一值集合所产生的bits。

通过这些bits可以满足这样的场景需求,给定一个值,判断这个值是否属于这个集合。

由于bloom filter是有损过滤器,并且真的不一定为真,但是假的一定为假。

目前已实现的场景是,支持=查询,但是这个=会包含一些假的值,所以需要recheck。

 

CREATE EXTENSION bloom;

CREATE INDEX score_idx ON score USING bloom(s1,s2,s3,s4);

SELECT * FROM score WHERE s1 = 86 AND s4 = 70;

 

对于Bloom索引的大小和精度有一个取舍。选择大的索引会花更长的时间去扫描,但是选择较少的列数以供后续检测。选择小的索引会使扫描变快但是选择的用于检测的行数较多。

使用Bloom索引的优势是与利用过滤器选择性的查询位数紧密相关的。如果选择性太低,例如只有一列用于查询,或者有太少的位数被分配到索引列中,数据库可能会选择回归到扫描整个表。

这个效果对于不同类型的硬盘(例如SSD和机械硬盘)来说差别很大。使用bloom索引的优势在SSD上被增强了,随机访问惩罚在SSD上会降低。

 

 

  1. btree_gist

对gist索引的补充扩展,支持标量类型建立btree_gist索引和排序

CREATE EXTENSION btree_gist;

  1. btree_gin

对GIN的补充扩展,支持标量数据

CREATE EXTENSION btree_gin;

直接使用原先的gin索引的定义方法即可定义标量数据的GIN索引。

create index idx_tbl_tmp_lpj_gin_c2 on tbl_tmp_lpj  using gin(c2);

  1. rum(第三方索引插件)

https://github.com/postgrespro/rum

 

RUM 参考了GIN的实现,并改进了GIN在全文检索时的一些弱点,不过建立索引和数据变更的时间比GIN长。

rum检索支持近似度排行,通过相似度分值表示文本和检索条件的相似度。

 

 

 

  1. zombodb(第三方索引插件)

zombodb是PostgreSQL与ElasticSearch结合的一个索引接口,可以直接读写ES。

https://github.com/zombodb/zombodb

 

应用场景

ES结合,实现SQL接口的搜索引擎,实现数据的透明搜索。

 

  1. 用户自定义索引

难度较大,暂不考虑。

 

下面是其他的一些第三方索引

 

bitmap索引是Greenplum的索引接口,类似GIN倒排,只是bitmapKEY是列的值,VALUEBIT(每个BIT对应一行),而不是行号listtree

 

varbitx是阿里云RDS的扩展包,丰富bit类型的函数接口,实际上并不是索引接口,但是在PostgreSQL中使用varbitx可以代替bitmap索引,达到同样的效果。

 

  • 验证性能
  1. hash索引和b-tree索引在等值检索时的性能差异

 

数据量

索引类型

平均耗时ms

28W

btree

76.5

28W

hash

56.6

99W

btree

51

99W

hash

43.5

 

 

结论等值检索上hash索引效率高于btree索引,不过hash索引因为不记录wal日志不建议使用。

PG10的hash索引会写wal日志。

 

  • 使用场景
  1. 全文搜索

CREATE INDEX name ON table USING gist(column);

创建以GiST(通用搜索树)为基础的索引,column可以是tsvector or tsquery 类型。

CREATE INDEX name ON table USING gin(column);

创建以GIN(倒排索引)为基础的索引,column必须是tsvector类型。

 

 

GiST索引是有损耗的,这意味着该索引可能会产生错误的匹配,并且有必要检查实际的表行消除这种错误匹配(PostgreSQL需要时自动执行)。

GIN索引并没有损耗标准查询,但它们的性能取决于对数独特的单词数。(然而,GIN索引只存储tsvector值的字(词),而不是它们的权重标签。因此,当使用涉及权重的查询时,需要复查一个表行。)

 

在选择要使用的索引类型时,GiST或者GIN考虑这些性能上的差异:

  • GIN索引查找比GiST快约三倍
  • GIN索引建立比GIST需要大约三倍的时间。
  • GIN索引更新比GiST索引速度慢,但如果快速更新支持无效,则慢了大约10倍
  • GIN索引比GiST索引大两到三倍

一般来说,GIN索引对静态数据是最好的,因为查找速度很快。对于动态数据, GiST索引更新比较快。具体而言,GiST索引非常适合动态数据,并且如果独特的字(词)在100,000以下,则比较快,而GIN索引将处理100,000+词汇,但是更新比较慢。

 

  1. 模糊查询

模糊查询,是一个需求量很大,同时也是一个对数据库来说非常难缠的需求。

对于前模糊(like ‘%xxx’),可以使用倒排B-TREE索引解决,对于后模糊(like ‘xxx%’),可以使用B-TREE索引解决。

B-TREE索引通常支持的查询包括 > , < , = , <= , >= 以及排序。 目前大多数数据库都支持B-TREE索引方法。

但是对于前后模糊(like ‘%xxxx%’),对于以及前后模糊的正则表达式(~ ‘.ab?cd[e-f]{1,10}-0.‘),则很多数据库无从下手,无法优化,只能全表扫描,对每条记录进行单独的处理。

PostgreSQL数据库的开放性使得这一切成为了可能,在数据库中进行前后模糊,正则表达查询的索引检索成为可能。

原因是PostgreSQL开放的索引接口,数据类型。(比如支持GIN, GIST, RUM, 自定义索引方法,你可以认为这是PG的独门绝技之一,目前还没有其他数据库支持这一特性 2016-12-31)。

前后模糊:

gin, gist, rum

正则表达:

gin, gist

 

  • 执行计划改变插件pg_hint_plan

https://github.com/ossc-db/pg_hint_plan/releases

 

安装
# export PATH= /home/postgres/pgsql/bin:$PATH

# gmake clean 

# gmake 

# gmake install 

 

vi $PGDATA/postgresql.conf  
shared_preload_libraries = 'pg_hint_plan'  
pg_hint_plan.enable_hint = on  
pg_hint_plan.debug_print = on  
pg_hint_plan.message_level = log 

 

postgres=# create extension pg_hint_plan; 

 

 

 

  • 表达式索引

表达式索引也是PostgreSQL特有的特性,例如用户的数据需要转换后查询,例如某些设备上传的地理坐标的坐标系不符合国标,需要转换为国内的空间坐标来查询。

那么可以针对这类字段,创建表达式索引,将转换过程放到表达式中,查询时也使用表达式进行查询。

create index idx_t_express_1 on t_express using gist ( ( ST_Transform(pos, 26986) ) );    
    
select * from t_express order by ST_Transform(pos, 26986) <-> ST_Transform(ST_GeomFromText('POINT(108.50000000001 22.8)', 4326), 26986) limit 10; 

 

  • 函数索引

必须是IMMUTABLE的函数,STABLE和VOLATILE无法创建索引

  • 部分索引

应对常用组合筛选条件或是只对热数据加索引

create index idx_test_id on test(id) where active;    
    
select * from test where active and id=?;  -- 可以使用部分索引    

 

  • 多列索引

多列索引每个列的operator class必须和实际查询匹配,在创建索引时可以指定。

b-tree, gin都支持任意组合查询。

但是b-tree推荐使用包含驱动列的查询条件,如果查询条件未包含驱动列,则需要扫描整个复合索引。

而gin则通吃,可以输入任意组合列作为查询条件,并且效率一致。

例如

index on (a,b,c)

b-tree 对于包含驱动列a查询条件的SQL,效率可能比较好,不包括a查询条件的SQL,即使走索引,也要扫描整个索引的所有条目。

而gin 则无论任何查询条件,效果都一样。

仅仅当多列组合查询时,gin效率可能比不上b-tree的带驱动列的查询(因为b-tree索引不需要bitmapAnd,而gin需要内部bitmapAnd)。

 

 

  • PG的数据扫描方法

PostgreSQL中数据的扫描方法很多,常见的有:

1、全表顺序扫描(seqscan)

2、索引+回表扫描(index scan)

3、索引扫描(index only scan)

4、bitmap扫描(bitmap index + block sorted heap scan)

那么对于同一张表,返回同样的记录数,不同的索引,效率有什么差别呢?

回答是和数据的存储线性相关性有关。(PostgreSQL的bitmap scan就是用来解这个问题的)

除了考虑数据存储的离散性,索引页本身的组织也是离散的

 

  • 读取数据页面内容的插件pageinspect

PG自带,可用于读取索引页信息,用于调试,仅超级用户使用。

详见官方文档。

 

  • 参考文档

https://yq.aliyun.com/articles/68244#1

https://github.com/digoal/blog/blob/master/201809/20180903_03.md

https://github.com/digoal/blog/blob/master/201602/20160203_01.md?spm=a2c4e.11153940.blogcont68244.70.29035625nnzqPA&file=20160203_01.md

https://github.com/ossc-db/pg_hint_plan/releases

https://github.com/digoal/blog/blob/master/201804/20180402_01.md

https://github.com/digoal/blog/blob/master/201706/20170627_01.md

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/139707.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)
blank

相关推荐

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号