引言

Postgresql 存在许多特定的索引查询类型,和大部分的Btree为基础架构的关系型数据库一样,在创建索引缺省的时候会把btree作为默认值。

本文简单介绍Postgresql的索引类型,虽然大部分业务常见常见可以用btree搞定,但是某些情况下其他特殊的索引可以有事半功倍的效果。

官方文档

PostgreSQL: Documentation: 14: 11.2. Index Types

pgsql索引类型

  • B-tree
  • Hash
  • GiST
  • GIN
  • BRIN

B-tree 索引

B-tree 适合处理那些能够按顺序存储的数据之上的等于(或范围)查询。范围查询包含下面的内容:

<
<=
=
>=
>

在进行上面这些操作符的运算时候,Postgresql 优化器会优先选择 Btree 索引,除了上面操作符以外还有BETWEEN 和 IN 也可以使用索引, 另外索引列上的IS NULLIS NOT NULL条件也可以用于B树索引。

但是实际尝试发现IS NULLIS NOT NULL条件的查询很多时候是走不了索引了。

根据官方的定义,仅当模式是一个常量,并且锚定在字符串开头的时候,优化器才会把 B-tree 索引用于模式匹配操作符 LIKE 和 ~。这个说法比较云里雾里,实际上是暗示开发者尽量避开正则或者模糊查询操作。

模式匹配

比如:col LIKE 'foo%' 或 col ~ '^foo',这些操作可以认为是可以动用索引的,但是注意col LIKE '%bar'这样的操作就不可以使用正则,因为几乎所有数据库都不支持后缀索引,这和Btree本身的数据结构有关。

这种时候如果要存储可能需要考虑逆序存储+索引的方式来触发扫描索引操作。

B-tree 索引可以用于 ILIKE 和 ~* ,但是和前文说的一样,仅当模式以非字母字符(不受大小写影响的字符)开头才可以使用索引。

B树索引可以用来检索排序后的数据,这并不总是比简单的扫描和排序快,这一点需要注意。

注意:
$:表示匹配字串的结尾。
^:表示匹配输入字符串的开始位置。
 等效于 LIKE, 而 \~\~ 对应 ILIKE,! 和 !\~\~ 操作符 分别代表 NOT LIKE 和 NOT ILIKE
操作符描述例子
~匹配正则表达式,大小写相关'thomas' ~ '.*thomas.*'
~*匹配正则表达式,大小写无关'thomas' ~* '.*Thomas.*'
!~不匹配正则表达式,大小写相关'thomas' !~ '.*Thomas.*'
!~*不匹配正则表达式,大小写无关'thomas' !~* '.*vadim.*'

Hash索引

Hash 索引只能处理简单的等于比较查询操作,并且注意加入哈希索引必然是在唯一值的列,否则索引容易失效。

注意官方并不推荐使用哈希索引。测试表明,PostgreSQL 的 Hash 索引的性能不比 B-tree 索引强,而 Hash 索引的尺寸和制作时间更差。hash索引因为不记录WAL日志,所以我们可能需要用 REINDEX 重建 Hash 索引,这会耗费大量系统开销。

哈希索引的创建SQL如下:

CREATE INDEX index_name 
ON table_name USING HASH (indexed_column);

GiST 索引(Generalized Search Tree - 通用的搜索树)

GiST 的意思是通用的搜索树(Generalized Search Tree)。内部是平衡树的访问方式,GiST索引通常可以用来替代其他索引,比如Btree。

Gist索引的创建方式如下:

CREATE INDEX gist_idx_test ON GIST_IDX USING gist(circle_dim);

Gist允许自定义类型,自定义数据类型是Gist索引的强项。

但是GiST 索引不是单独一种索引类型,而更像是一种架构,在这个架构上可以扩展出其他的索引,因为Gist索引支持多种索引策略,PostgresSql提供了多个二维几何数据类型的 GiST 运算符类,这些运算符类支持使用以下运算符的索引查询:

<<   &<   &>   >>   <<|   &<|   |&>   |>>   @>   <@   ~=   &&

关于这些操作符号的含义可以看下面的官方文档,文档主要介绍了一些三角函数,多边形,坐标运算等等。个人数学渣就不过多展开了。

PostgreSQL: Documentation: 14: 9.11. Geometric Functions and Operators

GiST 索引还能够优化“近邻”搜索,例如下面这样的搜索:

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

SP-Gist索引

SP-GiST是指空间分区的GiST。SP-GiST支持分区搜索树,便于开发各种不同的非平衡数据结构。

Sp-Gist 索引的创建方式如下:

CREATE INDEX spgist_idx ON spgist_table USING SPGiST (phone_no);

在官方介绍中介绍Sp-Gist的核心如下:

 SP-GiST permits implementation of a wide range of different non-balanced disk-based data structures, such as quadtrees, k-d trees, and radix trees (tries).

SP-GiST 允许实现各种不同的基于磁盘的非平衡数据结构,例如四叉树k-d 树和基数树(tries)。Postgresql支持二维点位的运算符,这些运算符类支持使用以下运算符的索引查询:

<<   >>   ~=   <@   <<|   |>>

与 GiST 一样,SP-GiST 支持“相邻”搜索。对于支持距离排序的 SP-GiST 运算符类,在官方文档的 PostgreSQL: Documentation: 14: 66.2. Built-in Operator Classes中列出了相应的运算符。

SP-GiST索引对那些具有自然聚类元素,同时也不是等量平衡树的数据最有用,例如,GIS、多媒体、电话路由、IP路由等。

GIN索引(Generalized Inverted Index-基因倒排索引)

基因倒排索引(Generalized Inverted Index) 存储了一个Key/posting list 的结构,Key是唯一键。posting list里面存储了Key出现的行。

GIN索引的创建方式如下:

CREATE INDEX GIN_idx1 ON student USING GIN (to_tsvector('english', stud_name));

GIN 索引显然是给搜索优化做准备的,GIN 索引是反转索引,它可以处理包含多个键的值(比如数组)。Gin和上面提到Gist索引一样支持自定义索引策略。

PostgreSQL 的标准发布中包含用于一维数组的 GIN 操作符类,它支持下面的操作:

<@

@>

=

&&

GIN使用技巧

注意GIN索引的插入是非常慢的,一般建议维护GIN索引是和图数据库一样先删除后重新建立。另外GIN 有一个可配置的结果集大小软上限配置参数 gin_fuzzy_search_limit,这个参数可以防止因为海量搜索匹配进行大量的磁盘读写。

缺省值 0 表示没有限制。如果设置了非零值,那么返回的结果就是从完整结果集中随机选择的一部分

局限性

GIN的局限性是不能进行完整索引扫描,因为对应多个key,所以堆指针会多次扫描回退,如果索引返回0个Key也会报错,空白查询也有可能匹配部分或者完全无法匹配内容。

GIN用于使用等值匹配比较合适,对于复杂的场景查询结果比较难控制,个人认为适用于轻量级的替代ES这样的中间件进行简单搜索的场景可以适用。

BRIN 索引(Block Range Indexes)

BRIN索引(Block Range INdexes的缩写)存储了关于存储在一个表的连续物理块范围内的值的摘要,也就是引用数据对应于每个块范围的列中数值的最小值最大值

BRIN索引的创建方式如下:

CREATE INDEX brin_idx ON test_idx USING BRIN(phone);

像GiST、SP-GiST和GIN一样,BRIN可以支持许多不同的索引策略,而且根据索引策略的不同,BRIN索引所能使用的特定运算符也不同。

比如Brin索引支持下面的运算符:

<   <=   =   >=   >

在官方文档的 https://www.postgresql.org/docs/current/brin-builtin-opclasses.html#BRIN-BUILTIN-OPCLASSES-TABLE 中有更多操作符的介绍。

小结

从全文列出的索引当中,可以看到用的比较多的基本是Btree、GIN、Brin 这几个索引,而对于复杂的倒排索引以及Hash索引都有着比较特殊的业务才有可能用到,而Hash索引仅仅适用于等值查询这样单一的场景。

参考资料


Xander
198 声望51 粉丝