如何使用PostgreSQL进行中文全文检索

开始

安装

首先是安装 PgSQL,这里我使用的是 PgSQL 9.6,PgSQL 10 也刚发布了,有兴趣的可以尝下鲜。

PgSQL 的安装可以说非常复杂了,除了要安装 Server 和 Client 外,还需要安装 devel 包。为了实现空间索引功能,我们还要安装最重要的 PostGIS 插件,此插件需要很多依赖,自己手动安装非常复杂而且很可能出错。

推荐自动化方式安装,Yum 一定要配合 epel 这样的 Yum 源,保障能将依赖一网打尽。当然最好的还是使用 docker 来运行,找个镜像就行了。

插件

由于 PgSQL 的很多功能都由插件实现,所以还要安装一些常用的插件,如:

postgis_topology(管理面、边、点等拓扑对象)

pgrouting(路径规划)

postgis_sfcgal(实现3D相关算法)

fuzzystrmatch(字符串相似度计算)

address_standardizer/address_standardizer_data_us(地址标准化)

pg_trgm(分词索引)

这些插件在安装目录 /path/extensions 下编译完毕后,在数据库中使用前要先使用 create extension xxx 启用。

启动

1.切换到非 root 用户。(PgSQL 在安装完毕后会创建一个名为 postgres 的超级用户,我们可以使用这个超级用户来操作 PgSQL,后期建议重新创建一个普通用户用来管理数据);

2.切换到 /installPath/bin/ 目录下,PgSQL 在此目录下提供了很多命令,如 createdb、createuser、dropdb、pg_dump 等;

3.使用 createdb 命令初始化一个文件夹 dir_db (此目录不能已存在)存放数据库物理数据,使用 -E UTF8 参数指定数据库字符集为 utf-8;

4.使用 pg_ctl -D dir_db 指定数据库启动后台服务;

5.使用 psql -d db 在命令行登陆 PgSQL;

配置

安装完毕后还要配置一些比较基本的参数才能正常使用。

Host权限

PgSQL需要在 pg_hba.conf 文件中配置数据库 Host 权限,才能被其他机器访问。

# TYPE  DATABASE        USER            ADDRESS                 METHOD

local   all             all                                     trust

host    all             all             127.0.0.1/32            md5

host    all             all             172.16.0.1/16            md5

文件中注释部分对这几个字段介绍得比较详细, 我们很可能需要添加 host(IP) 访问项, ADDRESS 是普通的网段表示法,METHOD 推荐使用 md5,表示使用 md5 加密传输密码。

服务器配置

服务器配置在 postgresql.conf中,修改配置后需要 使用 pg_ctl restart -D dir_db 命令重启数据库;

此外,我们也可以在登陆数据库后修改配置项:使用 SELECT * FROM pg_settings WHERE name = 'config'; 查询当前配置项,再使用 UPDATE 语句更新配置。但有些配置如内存分配策略是只在当前 session 生效的,全局生效需要在配置文件中修改,再重启服务器。

我们可以修改配置并用客户端验证 SQL 语句的优化,使用 \timing on 开启查询计时,使用 EXPLAIN ANALYSE 语句 分析查询语句效率。 下面介绍两个已实践过的配置参数:

  • shared_buffers:用于指定共享内存缓冲区所占用的内存量。它应该足够大来存储常使用的查询结果,以减少物理I/O。但它也不能太大,以避免系统 内存swap 的发生, 一般设置为系统内存的 20%。
  • work_mem:一个连接的工作内存,在查询结果数据量较大时,此值如果较小的话,会导致大量系统 I/O,导致查询速度急剧下降,如果你的 explain 语句内 buffer 部分 read数值过大,则表示工作内存不足,需要调整加此参数。但此值也不能太大,需要保证 work_mem * max_connections + shared_buffers + 系统内存 < RAM,不然同样可能会导致系统 内存swap。

这样,PgSQL 就能作为一个正常的关系型数据使用了。

分词

全文索引的实现要靠 PgSQL 的 gin 索引。分词功能 PgSQL 内置了英文、西班牙文等,但中文分词需要借助开源插件 zhparser;

SCWS

要使用 zhparser,我们首先要安装 SCWS 分词库,SCWS 是 Simple Chinese Word Segmentation 的首字母缩写(即:简易中文分词系统),其 GitHub 项目地址为 hightman-scws,我们下载之后可以直接安装。

安装完后,就可以在命令行中使用 scws 命令进行测试分词了, 其参数主要有:

  • -c utf8 指定字符集
  • -d dict 指定字典 可以是 xdb 或 txt 格式
  • -M 复合分词的级别, 1~15,按位异或的 1|2|4|8 依次表示 短词|二元|主要字|全部字,默认不复合分词,这个参数可以帮助调整到最想要的分词效果。

zhpaser

1.下载 zhparser 源码 git clone https:github.com/amutu/zhparser.git;

2.安装前需要先配置环境变量:export PATH=$PATH:/path/to/pgsql;

3.make && make install编译 zhparser;

4.登陆 PgSQL 使用 CREATE EXTENSION zhparser; 启用插件;

5.添加分词配置

CREATE TEXT SEARCH CONFIGURATION parser_name (PARSER = zhparser); // 添加配置
ALTER TEXT SEARCH CONFIGURATION parser_name ADD MAPPING FOR n,v,a,i,e,l,j WITH simple; // 设置分词规则 (n 名词 v 动词等,详情阅读下面的文档)

6.给某一列的分词结果添加 gin 索引 create index idx_name on table using gin(to_tsvector('parser_name', field));

7.在命令行中使用上一节中介绍的 scws 命令测试分词配置,如我认为复合等级为 7 时分词结果最好,则我在 postgresql.conf添加配置

zhparser.multi_short = true #短词复合: 1

zhparser.multi_duality = true  #散字二元复合: 2

zhparser.multi_zmain = true  #重要单字复合: 4

zhparser.multi_zall = false  #全部单字复合: 8

SQL

查询中我们可以使用最简单的 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 字段存储查询到的总结果条数;

到这里,普通的全文检索需求已经实现了。

优化

我们接着对分词效果和效率进行优化:

存储分词结果

我们可以使用一个字段来存储分词向量,并在此字段上创建索引来更优地使用分词索引:

ALTER TABLE table ADD COLUMN tsv_column tsvector;           // 添加一个分词字段
UPDATE table SET tsv_column = to_tsvector('parser_name', coalesce(field,''));   // 将字段的分词向量更新到新字段中
CREATE INDEX idx_gin_zhcn ON table USING GIN(tsv_column);   // 在新字段上创建索引
CREATE TRIGGER trigger_name BEFORE INSERT OR UPDATE  ON table FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(tsv_column, 'parser_name', field); // 创建一个更新分词触发器

这样,再进行查询时就可以直接使用 SELECT * FROM table WHERE tsv_column @@ 'keyword' 了。

这里需要注意,这时候在往表内插入数据的时候,可能会报错,提示指定 parser_name 的 schema, 这时候可以使用 \dF 命令查看所有 text search configuration 的参数:

               List of text search configurations

   Schema   |    Name    |              Description

------------+------------+---------------------------------------

 pg_catalog | english    | configuration for english language

 public     | myparser   |

注意 schema 参数,在创建 trigger 时需要指定 schema, 如上面,就需要使用 public.myparser。

添加自定义词典

我们可以在网上下载 xdb 格式的词库来替代默认词典,词库放在 share/tsearch_data/ 文件夹下才能被 PgSQL 读取到,默认使用的词库是 dict.utf8.xdb。要使用自定义词库,可以将词库放在词库文件夹后,在 postgresql.conf 配置 zhparser.extra_dict="mydict.xdb" 参数;

当我们只有 txt 的词库,想把这个词库作为默认词库该怎么办呢?使用 scws 带的scwe-gen-dict 工具或网上找的脚本生成 xdb 后放入词库文件夹后,在 PgSQL 中分词一直报错,读取词库文件失败。我经过多次实验,总结出了一套制作一个词典文件的方法:

1.准备词库源文件 mydict.txt:词库文件的内容每一行的格式为词 TF IDF 词性,词是必须的,而 TF 词频(Term Frequency)、IDF 反文档频率(Inverse Document Frequency) 和 词性 都是可选的,除非确定自己的词典资料是对的且符合 scws 的配置,不然最好还是留空,让 scws 自已确定;

2.在 postgresql.conf 中设置 zhparser.extra_dicts = "mydict.txt" 同时设置 zhparser.dict_in_memory = true;

3.命令行进入 PgSQL,执行一条分词语句 select to_tsquery('parser', '随便一个词') ,分词会极慢,请耐心(请保证此时只有一个分词语句在执行);

4.分词成功后,在/tmp/目录下找到生成的 scws-xxxx.xdb 替换掉 share/tsearch_data/dict.utf8.xdb;

5.删除刚加入的 extra_dicts dict_in_memory 配置,重启服务器。

扩展

由于查询的是 POI 的名称,一般较短,且很多词并无语义,又考虑到用户的输入习惯,一般会输入 POI 名称的前几个字符,而且 scws 的分词准确率也不能达到100%,于是我添加了名称的前缀查询来提高查询的准确率,即使用 B树索引 实现 LIKE '关键词%' 的查询。这里需

这里要注意的是,创建索引时要根据字段类型配置 操作符类,不然索引可能会不生效,如在 字段类型为 varchar 的字段上创建索引需要使用语句CREATE INDEX idx_name ON table(COLUMN varchar_pattern_ops),这里的 varcharpatternops 就是操作符类。

自此,一个良好的全文检索系统就完成了。

总结

简单的数据迁移并不是终点,后续要做的还有很多,如整个系统的数据同步、查询效率优化、查询功能优化(添加拼音搜索、模糊搜索)等。特别是查询效率,不知道是不是我配置有问题,完全达不到那种 E级毫秒 的速度,1kw 的数据效率在进行大结果返回时就大幅下降(200ms),只好老老实实地提前进行了分表,目前百万级查询速度在 20ms 以内,优化还有一段路要走。

不过这次倒是对 技术的“生态”有了个更深的体会,这方面 PgSQL 确实和 MySQL 差远了,使用 MySQL 时再奇葩的问题都能在网上快速找到答案,而 PgSQL 就尴尬了,入门级的问题搜索 stackoverflow 来来回回就那么几个对不上的回答。虽然也有阿里的“德哥”一样的大神在辛苦布道,但用户的数量才是根本。不过,随着 PgSQL 越来越完善,使用它的人一定会越来越多的,我这篇文章也算是为 PgSQL 加温了吧,哈哈~希望能帮到后来的使用者。

以上就是如何使用PostgreSQL进行中文全文检索的详细内容,更多关于使用PostgreSQL进行中文全文检索的资料请关注我们其它相关文章!

(0)

相关推荐

  • PostgreSQL用户登录失败自动锁定的处理方案

    墨墨导读:PostgreSQL使用session_exec插件实现用户密码验证失败几次后自动锁定,本文介绍一种处理方案. 一.插件session_exec安装配置篇 下载插件并编译安装. https://github.com/okbob/session_exec $ unzip session_exec-master.zip $ cd session_exec-master/ $ make pg_config=/opt/pgsql/bin/pg_config $ make pg_config=/

  • postgresql 中的时间处理小技巧(推荐)

    时间格式处理 按照给定格式返回:to_char(timestamp,format) 返回相差的天数:(date(time1) - current_date) 返回时间戳对应的的日期[yyyy-MM-dd]:date(timestamp) 计算结果取两位小数(方便条件筛选):round((ABS(a-b)::numeric / a), 2) * 100 < 10 时间运算 加减运算 '-' :前x天/月/年 '+' :后x天/月/年 current_timestamp - interval 'x

  • Postgresql限制用户登录错误次数的实例代码

    在oracle中我们可以通过设置FAILED_LOGIN_ATTEMPTS来限制用户密码登录错误的次数,但是在postgresql中是不支持这个功能的.尽管PostgreSQL支持event trigger,可是event局限于DDL,对于登录登出事件是没办法使用event trigger的. 不过像登录新建会话触发某个事件这个需求可以通过hook实现,不过该方法比较复杂,需要修改内核代码,在客户端认证中添加逻辑,判断输入密码次数统计.这里推荐一种比较简单的方法实现类似的功能. 这里我们要使用到

  • PostGreSql 判断字符串中是否有中文的案例

    我就废话不多说了,大家还是直接看代码吧~ 实例 imos=# select 'hello' ~ '[\u2e80-\ua4cf]|[\uf900-\ufaff]|[\ufe30-\ufe4f]'; ?column? ---------- f (1 row) imos=# imos=# select 'hello中国' ~ '[\u2e80-\ua4cf]|[\uf900-\ufaff]|[\ufe30-\ufe4f]'; ?column? ---------- t (1 row) 补充:Post

  • 在PostgreSQL中使用ltree处理层次结构数据的方法

    在本文中,我们将学习如何使用PostgreSQL的ltree模块,该模块允许以分层的树状结构存储数据. 什么是ltree? Ltree是PostgreSQL模块.它实现了一种数据类型ltree,用于表示存储在分层树状结构中的数据的标签.提供了用于搜索标签树的广泛工具. 为什么选择ltree? ltree实现了一个物化路径,对于INSERT / UPDATE / DELETE来说非常快,而对于SELECT操作则较快 通常,它比使用经常需要重新计算分支的递归CTE或递归函数要快 如内置的查询语法和专

  • 自定义函数实现单词排序并运用于PostgreSQL(实现代码)

    Python实现逻辑 1.按照分隔符将字符串分割为列表类型 drugs.split(separator) 2.列表排序 drug_list.sort() 3.列表拼接 separator.join(drug_list) 4.类型判断 由于数据库中对应字段可能会出现为NULL的情况,即在Python中为None,而类型None 无split() 用法,因此添加条件判断是否为真.如果为NULL,则直接返回None. Python实现 def 单词排序(drugs,separator): if dru

  • PostgreSQL的中文拼音排序案例

    前一段时间开发人员咨询,说postgresql里面想根据一个字段做中文的拼音排序,但是不得其解 环境: OS:CentOS 6.3 DB:PostgreSQL 9.2.4 TABLE: tbl_kenyon 场景: postgres=# \d tbl_kenyon Table "public.tbl_kenyon" Column | Type | Modifiers --------+------+--------------- vname | text | --使用排序后的结果,不是

  • PostgreSQL将数据加载到buffer cache中操作方法

    我们都知道数据在缓存中访问远比在磁盘中访问速度要快,那么我们怎么在pg中将指定的数据加载到缓存中呢,这有点类似于Oracle的in-memory. 当然要注意并不是把数据加载到内存中就一定是好的,因为相较于磁盘,内存总是有限的,所以一帮我们只是在特殊场合下将需要的数据加载到内存中来加快访问的速度. 我们可以使用pg_prewarm插件来将指定的表加载到OS Buffer或者pg shared buffer中. 安装: bill=# create extension pg_prewarm ; CR

  • postgresql影子用户实践场景分析

    在实际的生产环境 ,我们经常会碰到这样的情况:因为业务场景需要,本部门某些重要的业务数据表需要给予其他部门查看权限,因业务的扩展及调整,后期可能需要放开更多的表查询权限.为解决此种业务需求,我们可以采用创建视图的方式来解决,已可以通过创建影子用户的方式来满足需求,本文主要介绍影子用户的创建及授权方法. 场景1:只授予usage on schema 权限 session 1: --创建readonly用户,并将test模式赋予readonly用户. postgres=# create user r

  • 如何使用PostgreSQL进行中文全文检索

    开始 安装 首先是安装 PgSQL,这里我使用的是 PgSQL 9.6,PgSQL 10 也刚发布了,有兴趣的可以尝下鲜. PgSQL 的安装可以说非常复杂了,除了要安装 Server 和 Client 外,还需要安装 devel 包.为了实现空间索引功能,我们还要安装最重要的 PostGIS 插件,此插件需要很多依赖,自己手动安装非常复杂而且很可能出错. 推荐自动化方式安装,Yum 一定要配合 epel 这样的 Yum 源,保障能将依赖一网打尽.当然最好的还是使用 docker 来运行,找个镜

  • KubeSphere中部署Wiki系统wiki.js并启用中文全文检索

    目录 背景 准备 storageclass 部署 PostgreSQL 数据库 准备用户名密码配置 准备数据库初始化脚本 准备存储 部署 PostgreSQL 数据库 创建供其他 Pod 访问的 Service 完成 PostgreSQL 部署 部署 wiki.js 准备用户名密码配置 准备数据库连接配置 创建数据库用户和数据库 准备 wiki.js 的 yaml 部署文件 创建集群内访问 wiki.js 的 Service 创建集群外访问的 Ingress 执行部署 配置 wiki.js 支持

  • 深度解析MySQL 5.7之中文全文检索

    前言 其实全文检索在MySQL里面很早就支持了,只不过一直以来只支持英文.缘由是他从来都使用空格来作为分词的分隔符,而对于中文来讲,显然用空格就不合适,需要针对中文语义进行分词.这不,从MySQL 5.7开始,MySQL内置了ngram全文检索插件,用来支持中文分词,并且对MyISAM和InnoDB引擎有效. 在使用中文检索分词插件ngram之前,先得在MySQL配置文件里面设置他的分词大小,比如, [mysqld] ngram_token_size=2 这里把分词大小设置为2.要记住,分词的S

  • 利用JSP建立Web站点

    JSP是由Sun微系统公司于1999年6月推出的一项技术,是基于JavaServlet以及整个java体系的Web开发技术,利用这一技术可以建立先进.安全和跨平台的动态网站.JSP 与ASP非常相似.两者都提供在HTML 代码中混合某种程序代码.由语言引擎解释执行程序代码的能力.在ASP或JSP 环境下,HTML代码主要负责描述信息的显示样式,而程序代码则用来描述处理逻辑.ASP下的编程语言是 VBScript 之类的脚本语言,而JSP 使用的是Java.TRS及其JavaBeansTRS系统是

  • 特转载一高手总结PHP学习资源和链接.

    在网上找PHP学习资源的时候,逛到了Openphp.cn ,据我了解该站的站长对PHP可以说比较精通了.值得我学习,他有写的这篇: 介绍几本 PHP 书籍和一些 PHP 相关资源链接. 文章将对我有很大的帮助. 特全文转载之.另外他的站上的链接让我找到PostgreSQL的中文网站http://www.pgsqldb.org/,在这里有很多关于PostgreSQL的资料,是我这几天要找的东东. 以下转自:: http://www.openphp.cn/index.php/article/13/1

  • mysql 全文检索中文解决方法及实例代码

    mysql 全文检索中文解决方法             最近公司项目要求这样的功能,在数据库中检索中文,很是棘手,上网查询下资料,找的类似文章,这里及记录下,希望能帮助到大家, 实例代码:    <?php /* mysql全文检索中文解决方案! */ error_reporting(E_ERROR | E_WARNING | E_PARSE); ini_set('display_errors', '1'); //数据库支持 class SaeMysql{ //phpmysql操作类 } $D

  • Django实现全文检索的方法(支持中文)

    PS: 我的检索是在文章模块下 forum/article 第一步:先安装需要的包: pip install django-haystack pip install whoosh pip install jieba 第二步: 配置需要的文件 settings.py 添加haystack应用模块 INSTALLED_APPS = ( ... 'haystack', ... ) 在settings.py 末尾添加 HAYSTACK_CONNECTIONS = { 'default': { 'ENGI

  • 几款开源的中文分词系统

    以下介绍4款开源中文分词系统. 1.ICTCLAS – 全球最受欢迎的汉语分词系统 中文词法分析是中文信息处理的基础与关键.中国科学院计算技术研究所在多年研究工作积累的基础上,研制出了汉语词法分析系统ICTCLAS(Institute of Computing Technology, Chinese Lexical Analysis System),主要功能包括中文分词:词性标注:命名实体识别:新词识别:同时支持用户词典:支持繁体中文:支持GBK.UTF-8.UTF-7.UNICODE等多种编码

随机推荐