PG插件pg_trgm实现模糊查询走索引

0    247    1

👉 本文共约965个字,系统预计阅读时间或需4分钟。

PG中的索引

参考:https://www.xmmup.com/pgzhongdesuoyin.html

索引类型:B-Tree、hash、Gin、Gist、SP-Gist、Brin

B-Tree:用来查找单个值或者扫描一个范围,比如大于、小于或者等于某个值。

B-Tree索引默认升序存储,从PostgreSQL8.3开始,空值也被存储在其中,但默认放在最后,使用如下SQL可以把这个顺序翻转过来:

Create index idx_111 on t( c1 desc NULLS first);

Hash:用于等值查询并且索引中无NULL值。

Gin:Gin存储了一个由键及其行倒排列表构成的列表,键的行倒排列表中的每一行都包含该键,一行也可能出现在多个键的倒排列表中。Gin也是一种实现全文搜索的方式,在PostgreSQL的contrib模块的pg_trgm扩展可以利用Gin索引来实现全文搜索。

GiST:通用搜索树(GiST)提供了一种用于存储数据的方式来构建平衡的树结构,只需要定义如何对待键即可,内建的B-tree也可以用它构建。这就允许使用该类索引来解决B-tree能处理的常规相对和范围比较之外的问题。例如,PostgreSQL中的几何数据类型包括的操作符允许索引根据项之间的距离以及项之间是否相交来排序。

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

SP-GiST:是Space-Partitioned GiST的缩写。SP-GiST支持划分搜索树,这种书可以用来开发范围广泛的非平衡数据结构,例如四叉树、k-d树和后缀树(字典树)。

Brin:代表块范围索引。块范围是指表中相邻页面的范围。对于每个块(block),在当前的实现当中,Brin索引存储了每个块的最小值和最大值。

创建索引:

建议使用create index concurrently 来构建索引,理由是不会对表的业务访问造成影响,这么做的代价就是create index concurrently的时长较长,另外,create index concurrently一旦创建失败,产生的索引将会被标记为INVALID,INVALID的索引对于查询不可用,但是对表的更改会更新索引中的值,这显然是一种浪费。参考:https://www.xmmup.com/pgbingfachuangjiansuoyinconcurrently.html

示例

脚本:

过程:

注意

pg_trgm使用限制

  1. 数据库排序规则需要使用LC_CTYPE 'zh_CN.utf8';
  2. 查询字符不能少于3个
  3. 存储的内容不能大于8k
    头像

    小麦苗

    学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

    您可能还喜欢...

    发表评论

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

    5 × 3 =

     

    嘿,我是小麦,需要帮助随时找我哦
    • 18509239930
    • 个人微信

    • 麦老师QQ聊天
    • 个人邮箱
    • 点击加入QQ群
    • 个人微店

    • 回到顶部
    返回顶部