【Postgresql】索引类型（btree、hash、GIST、GIN）

2023-02-23 13:40:28 浏览数 (1)

引言

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

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

官方文档

PostgreSQL: Documentation: 14: 11.2. Index Types

pgsql索引类型

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

B-tree 索引

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

代码语言：javascript复制
<
<=
=
>=
>

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

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

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

模式匹配

比如：col LIKE 'foo%' 或 col ~ '^foo'，这些操作可以认为是可以动用索引的，但是注意col LIKE '�r'这样的操作就不可以使用正则，因为几乎所有数据库都不支持后缀索引，这和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如下：

代码语言：javascript复制
CREATE INDEX index_name 
ON table_name USING HASH (indexed_column);

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

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

Gist索引的创建方式如下：

代码语言：javascript复制
CREATE INDEX gist_idx_test ON GIST_IDX USING gist(circle_dim);

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

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

代码语言：javascript复制
<<   &<   &>   >>   <<|   &<|   |&>   |>>   @>   <@   ~=   &&

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

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

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

代码语言：javascript复制
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

SP-Gist索引

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

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

代码语言：javascript复制
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支持二维点位的运算符，这些运算符类支持使用以下运算符的索引查询：

代码语言：javascript复制
<<   >>   ~=   <@   <<|   |>>

与 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索引的创建方式如下：

代码语言：javascript复制
CREATE INDEX GIN_idx1 ON student USING GIN (to_tsvector('english', stud_name));

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

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

代码语言：javascript复制
<@

@>

=

&&

GIN使用技巧

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

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

局限性

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

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

BRIN 索引（Block Range Indexes）

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

BRIN索引的创建方式如下：

代码语言：javascript复制
CREATE INDEX brin_idx ON test_idx USING BRIN(phone);

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

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

代码语言：javascript复制
<   <=   =   >=   >

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

小结

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

参考资料

  • (1条消息) 15.pgsql索引类型（btree、hash、GIST、GIN）_Lei_Da_Gou的博客-CSDN博客_pgsql索引类型
  • PostgreSQL: Documentation: 15: 11.2. Index Types
存储 官方文档 postgresql

0 人点赞