【PostgreSQL】GIN索引安装与使用 - 全模糊匹配/数组匹配，PG批量插入上万随机生成数据，随机生成字符串/数组

锥栗

已于 2023-12-07 20:46:17 修改

阅读量4.2k

点赞数 5

于 2022-12-02 10:07:27 首次发布

本文链接：https://blog.csdn.net/fisherish/article/details/128093478

版权

数据库专栏收录该内容

7 篇文章 1 订阅

订阅专栏

在这里插入图片描述

前言

PostgreSQL（简称PG）是一个开源的关系型数据库管理系统，最初由加州大学伯克利分校计算机科学部门开发，PG和MySQL都是流行的开源关系型数据库管理系统。

GIN（Generalized Inverted Index，通用倒排索引）是PostgreSQL中的一种索引类型，它主要用于支持文本搜索和数组数据类型的查询。与传统的B树或哈希索引不同，GIN索引不是直接存储键值对的映射关系，而是将键值拆分成多个元素，并为每个元素创建一个单独的索引条目。

在GIN索引中，每一个被索引的数据行都会有一个与之关联的 posting list（文档列表），这个列表包含了所有出现在该行中的索引键。例如，在一个全文本搜索的场景中，如果一个文档包含单词"apple"、“banana"和"orange”，那么它的posting list就会包含这些词的ID。这样，当进行模糊查询时，就可以快速定位到包含目标单词的所有文档。GIN索引适用于文本搜索，数组数据类型，JSON和JSONB数据类型。

本文讲述GIN索引安装与使用

环境

PostgreSQL

DBeaver

拓展库安装

打开SQL编辑器：

在这里插入图片描述

输入命令运行即可：

CREATE EXTENSION pg_trgm;

CREATE extension btree_gin;

（光标选中某行后，按ctrl + enter可以只运行选中SQL）

其它安装情况可以参考网上教程如：

https://www.kancloud.cn/chunyu/php_basic_knowledge/2280100

随后我们查看拓展库是否安装完成：

--查看pg拓展库
select * from pg_extension;

在这里插入图片描述

生成随机假数据

建表语句有：

--创建实验表
CREATE TABLE public.index_learn_t_gin1 (
	id numeric NULL,
	"name" varchar NULL,
	"content" varchar NULL,
	"arr" numeric(4)[]
);

在这里插入图片描述

随后我们要造假数据，为了合适模糊搜索，最好是随机生成的：

随机生成字符串语句如下：

--生成随机字符串
select string_agg(substring('0123456789abcdefghjklmnopqrstuvwxyz', round(random() * 40)::integer, 1), '') from generate_series(1, 10);

string_agg() 是聚合函数，用于将一列字符串连接成一个字符串

substring() 用于从一个字符串中提取子串，substring(string, start, length)
其中，string: 要从中提取子串的原始字符串。start: 提取子串开始位置的索引（从1开始计数）。length: 子串的长度。

generate_series() 是 PG 中的内置函数，用于生成一系列连续的整数或时间值。可以用于创建测试数据、填充日期范围等。

round() 是 PG 中内置函数，用于将数值四舍五入到指定的小数位数。

random() 是 PG 中的内置函数，用于生成一个随机的浮点数

有结果：

在这里插入图片描述

随机生成中文如下：

select string_agg(substring('的一是在不了有和人这中大为上个国我以要他时来用们生到作地于出就分对成会可主发年动同工也能下过子说产种面而方后多定行学法所民得经十三之进着等部度家电力里如水化高自二理起小物现实加量都两体制机当使点从业本去把性好应开它合还因由其些然前外天政四日那社义事平形相全表间样与关各重新线内数正心反你明看原又么利比或但质气第向道命此变条只没结解问意建月公无系军很情者最立代想已通并提直题党程展五果料象员革位入常文总次品式活设及管特件长求老头基资边流路级少图山统接知较将组见计别她手角期根论运农指几九区强放决西被干做必战先回则任取据处队南给色光门即保治北造百规热领七海口东导器压志世金增争济阶油思术极交受联什认六共权收证改清己美再采转更单风切打白教速花带安场身车例真务具万每目至达走积示议声报斗完类八离华名确才科张信马节话米整空元况今集温传土许步群广石记需段研界拉林律叫且究观越织装影算低持音众书布复容儿须际商非验连断深难近矿千周委素技备半办青省列习响约支般史感劳便团往酸历市克何除消构府称太准精值号率族维划选标写存候毛亲快效斯院查江型眼王按格养易置派层片始却专状育厂京识适属圆包火住调满县局照参红细引听该冯价严龙飞', round(random() * 500)::integer, 1), '')  from generate_series(1, 10);

有结果：

在这里插入图片描述

生成随机数组

--生成随机数组
select array(select random()*1000 from generate_series(1, 10));

在这里插入图片描述

结合这些方法，我们生成10万条随机假数据插入表中，有脚本如下：

--批量插入数据
do language plpgsql $$    
 declare    
 begin    
   for i in 1..100000 loop    
     insert into index_learn_t_gin1 select i, 
     string_agg(substring('0123456789abcdefghjklmnopqrstuvwxyz', round(random() * 40)::integer, 1), ''),
     string_agg(substring('的一是在不了有和人这中大为上个国我以要他时来用们生到作地于出就分对成会可主发年动同工也能下过子说产种面而方后多定行学法所民得经十三之进着等部度家电力里如水化高自二理起小物现实加量都两体制机当使点从业本去把性好应开它合还因由其些然前外天政四日那社义事平形相全表间样与关各重新线内数正心反你明看原又么利比或但质气第向道命此变条只没结解问意建月公无系军很情者最立代想已通并提直题党程展五果料象员革位入常文总次品式活设及管特件长求老头基资边流路级少图山统接知较将组见计别她手角期根论运农指几九区强放决西被干做必战先回则任取据处队南给色光门即保治北造百规热领七海口东导器压志世金增争济阶油思术极交受联什认六共权收证改清己美再采转更单风切打白教速花带安场身车例真务具万每目至达走积示议声报斗完类八离华名确才科张信马节话米整空元况今集温传土许步群广石记需段研界拉林律叫且究观越织装影算低持音众书布复容儿须际商非验连断深难近矿千周委素技备半办青省列习响约支般史感劳便团往酸历市克何除消构府称太准精值号率族维划选标写存候毛亲快效斯院查江型眼王按格养易置派层片始却专状育厂京识适属圆包火住调满县局照参红细引听该冯价严龙飞', round(random() * 500)::integer, 1), ''),
     array(select random()*1000 from generate_series(1, 10))
     from generate_series(1, 10);    
   end loop;    
 end;    
 $$;

要稍微等一下，完成后有：

在这里插入图片描述

查看表有：

在这里插入图片描述
我们有中文，有英文，有数组

在这里插入图片描述

查询使用GIN索引

创建索引前先做一下三组条件查询：

--查询结果与分析，全模糊匹配
select id, name, content, arr from index_learn_t_gin1
where name like '%dad%'
;
explain analyze select id, name, content, arr from index_learn_t_gin1
where name like '%dad%'
;

--查询结果与分析，中文全模糊匹配
select id, name, content, arr from index_learn_t_gin1
where content like '%大中国%'
;
explain analyze select id, name, content, arr from index_learn_t_gin1
where content like '%大中国%'
;

--查询结果与分析，数组匹配
select id, name, content, arr from index_learn_t_gin1
where arr @> array[333.0,444.0]
;
explain analyze select id, name, content, arr from index_learn_t_gin1
where arr @> array[333.0,444.0]
;

在这里插入图片描述

逐个执行后，可以看看explain结果，这些查询都不走索引。

接下来创建GIN索引：

--创建GIN索引
CREATE INDEX idx_index_learn_t_gin1_name ON public.index_learn_t_gin1 USING gin (name GIN_TRGM_OPS);
CREATE INDEX idx_index_learn_t_gin1_content ON public.index_learn_t_gin1 USING gin (content GIN_TRGM_OPS);
CREATE INDEX idx_index_learn_t_gin1_arr ON public.index_learn_t_gin1 USING gin (arr);

数据量比较大，创建索引也会耗时长，索引占用的空间也大，创建完后有：

在这里插入图片描述

这时候我们再分析执行计划，先是英文全模糊匹配：

--查询结果与分析，全模糊匹配
select id, name, content, arr from index_learn_t_gin1
where name like '%dad%'
;
explain analyze select id, name, content, arr from index_learn_t_gin1
where name like '%dad%'
;

在这里插入图片描述

可以看到走索引了

在这里插入图片描述

再看看中文全模糊匹配：

--查询结果与分析，中文全模糊匹配
select id, name, content, arr from index_learn_t_gin1
where content like '%大中国%'
;
explain analyze select id, name, content, arr from index_learn_t_gin1
where content like '%大中国%'
;

在这里插入图片描述

看看数组匹配：

--查询结果与分析，数组匹配
select id, name, content, arr from index_learn_t_gin1
where arr @> array[333.0,444.0]
;
explain analyze select id, name, content, arr from index_learn_t_gin1
where arr @> array[333.0,444.0]
;

在这里插入图片描述

GIN索引使用条件

如果全模糊匹配，字符少于3个（1个或2个时），则不走索引，不论中英文都如此：

--不走索引的情况
explain analyze select id, name, content, arr from index_learn_t_gin1
where name like '%ad%'
;
explain analyze select id, name, content, arr from index_learn_t_gin1
where content like '%中国%'
;

在这里插入图片描述

因此GIN索引 1个 2个字符都不走索引，3个字符以上才开始走索引

GIN索引原理

GIN索引在查找文本时之所以能够快速，是因为它使用了一种特殊的数据结构和算法来优化搜索过程。

倒排索引（Inverted Index）：将数据项与它们出现的位置关联起来的数据结构。对于文本搜索来说，这意味着每个单词或短语都有一个列表，该列表包含了包含该词的所有文档的 ID。使查找包含特定单词或短语的文档变得非常快，可以直接从单词到文档的映射中找到。
文档列表（Posting Lists）：在 GIN 索引中，每个键值都对应着一个 posting list，这个列表包含了所有包含该键值的行的 ID。当进行查询时，系统会检查每个关键词对应的 posting list，并将结果合并为最终的结果集。
文档树（Posting Tree）：GIN 索引内部存储了 posting tree 的数据结构，它将多个posting list组织在一起以节省空间并提高查询性能。在posting tree中，相邻的posting list可以共享相同的前缀，这样可以减少存储空间并加快查找速度。

此外GIN 索引使用了多种压缩技术来减小存储需求和提高查询性能。