Atitit 全文检索 pgsql 艾提拉总结
分词
全文索引的实现要靠 PgSQL 的 gin 索引。分词功能 PgSQL 内置了英文、西班牙文等,但中文分词需要借助开源插件 zhparser;
SQL
这样,再进行查询时就可以直接使用 SELECT * FROM table WHERE tsv_column @@ 'keyword' 了
查询中我们可以使用最简单的 SELECT * FROM table WHERE to_tsvector('parser_name', field) @@ 'word' 来查询 field 字段分词中带有 word 一词的数据;
使用 to_tsquery() 方法将句子解析成各个词的组合向量,如 国家大剧院 的返回结果为 '国家' & '大剧院' & '大剧' & '剧院' ,当然我们也可以使用 & | 符号拼接自己需要的向量;在查询 长句 时,可以使用 SELECT * FROM table WHERE to_tsvector('parser_name', field) @@ to_tsquery('parser_name','words');
有时候我们想像 MySQL 的 SQL_CALC_FOUND_ROWS 语句一样同步返回结果条数,则可以使用 SELECT COUNT(*) OVER() AS score FROM table WHERE ...,PgSQL 会在每一行数据添加 score 字段存储查询到的总结果条数;
到这里,普通的全文检索需求已经实现了。
总结
简单的数据迁移并不是终点,后续要做的还有很多,如整个系统的数据同步、查询效率优化、查询功能优化(添加拼音搜索、模糊搜索)等。特别是查询效率,不知道是不是我配置有问题,完全达不到那种 E级毫秒 的速度,1kw 的数据效率在进行大结果返回时就大幅下降(200ms),只好老老实实地提前进行了分表,目前百万级查询速度在 20ms 以内,优化还有一段路要走。
不过这次倒是对 技术的“生态”有了个更深的体会,这方面 PgSQL 确实和 MySQL 差远了,使用 MySQL 时再奇葩的问题都能在网上快速找到答案,而 PgSQL 就尴尬了,入门级的问题搜索 stackoverflow 来来回回就那么几个对不上的回答。虽然也有阿里的“德哥”一样的大神在辛苦布道,但用户的数量才是根本。不过,随着 PgSQL 越来越完善,使用它的人一定会越来越多的,我这篇文章也算是为 PgSQL 加温了吧,哈哈~希望能帮到后来的使用者。
关于本文有什么问题可以在下面留言交流,如果您觉得本文对您有帮助,可
们为该表的的name字段创建一个全文索引,并将它的信息独立的存储在一个表中,表如下:
CREATE TABLE full_text_index
(
<pre name="code" class="sql"> id bigserial NOT NULL,
CREATE TABLE full_text_index
(
<pre name="code" class="sql"> id bigserial NOT NULL, text_fti tsvector NOT NULL,)WITH ( OIDS=FALSE);ALTER TABLE full_text_index OWNER TO postgres;
);
我们注意看字段text_fti,它的类型是tsvector,说明该字段是存储全文索引的,这就是postgresql数据库中全文索引的字段类型。
那么如何将我们的信息自动的创建全文索引信息添加到我们的full_text_index 中呢?那么这里将要用到sql中的触发器,
如下创建触发器函数:
1. CREATE OR