【数据库之postgreSQL】全文索引之飞快

搜索是项非常重要的功能，所以像和solr和es这样的基于的工具变得很流行。但使用这些大规模“杀伤性”的搜索武器，需要额外的成本。有时候我们只想用下简单的搜索，但是关系型数据库的的like和in又太慢，更别提find_in_set()函数，任何在密集sql查询里面使用函数都是罪大恶极。

那么postgresql就是你的首选了，使用它的理由是

• 我们只需使用postgresql，不必安装其它的搜索引擎，就能支持良好的千万级搜索性能。
• 使用其它的数据库（比如mysql），全文索引十分不稳定。
  这里不再介绍什么是全文索引，请自行gg or dd
  注意这里还有个坑：如果某个索引字段值为空，会导致索引失效，查不出数据。所以一定不能为null

PostgreSQL 中的全文搜索

全文搜索(FTS) 允许对文档进行预处理并保存索引以供以后快速搜索和排名。请参阅官方文档（https://www.postgresql.org/docs/13/textsearch-intro.html），该文档非常完整，提供了理解和实施 FTS 所需的所有信息。
PG（Postgres）中 FTS 的主要构建块是：

• tsvector，它代表一个可搜索的文档
• tsquery，这是针对文档执行的搜索查询

转换为文档类型

to_tsvector函数解析输入文本并将其转换为表示可搜索文档的搜索类型。说人话就是to_tsvector是PostgreSQL内置的一个分词函数，它可以将一段文本按照某种分词规则进行分词

SELECT to_tsvector('Java in a nutshell')
1

将输出以下内容：

"'java':1 'nutshel':4"
1

结果是准备被搜索的词位列表，停用词（“in”、“a”、“the”等）被删除
数字是文档中词位的位置：java:1从第 1 个位置开始，而nutshell:4从第 4 个位置开始（PostgreSQL目前并不支持中文分词，如果需要对中文分词，则需要安装中文分词器，现在最流行的是zhparser）

查询

to_tsquery函数解析输入文本并将其转换为表示查询的搜索类型。例如，用户想要搜索“java in a nutshell”：

SELECT to_tsquery('java & in & a & nutshell');
1

将给出以下内容

"'java' & 'nutshel'"
1

结果是准备好被查询的列表
停用词（“in”、“a”、“the”等）被删除

运算法

@@ 运算符允许将查询与文档匹配并返回 true 或 false

/* true */
SELECT to_tsquery('java & in & a & nutshell') @@ to_tsvector('Java in a nutshell'); 
1
2

发现没有，其实到目前为止我们并没有建索引，而是通过to_tsquery转换来实现完成全文检索功能。

实例

在PostgreSQL中为gin和gist来加速查询，一般推荐使用gin，gin索引查询速度优于gist，但创建过程比较慢，且索引占用的磁盘量比较高
默认只支持英文，中文也分词也不太准确。他是一句一个词库来分词的，如果有逗号和英文中的介词这种比较好分，也比较准。

创建索引

create index 索引名称 on 表名 using gin(to_tsvector(‘english’,字段名))
1

查询

select * from 表名 where to_tsvector(‘english’，字段名) @@ to_tsquery(‘english’,‘要搜索的名次’)
1

@@指包含 因为建立索引的时候指明了语言，所以查询的时候也要指明语言，否则不会使用索引。
支持多个字段

支持多列

CREATE INDEX 索引名 ON 表名 USING gin(to_tsvector('english', 字段一 || 字段二));

1
2

这里注意，如果有一列为空，死活都查不出来。我当时是直接保存了空串进去，但是不能为null。函数coalesce来确保字段为NULL的可以建立索引，如下：

ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;
UPDATE pgweb SET textsearchable_index_col =
     to_tsvector('english', coalesce(title,'') || coalesce(body,''));
然后，我们就可以创建倒排的索引
CREATE INDEX textsearch_idx ON pgweb USING gin(textsearchable_index_col);
索引创建完毕，我们就可以使用全文检索了。
SELECT title
FROM pgweb
WHERE textsearchable_index_col @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC LIMIT 10;
1
2
3
4
5
6
7
8
9
10