PostgreSQL 8.3 以上的中文全文索引使用介绍

PostgreSQL 8.3 以上的中文全文索引使用介绍
From PostgreSQL 中文维基, PostgreSQL 中文站, PostgreSQL 中国社区, PostgreSQL Chinese community
Jump to: navigation, search

目录

[隐藏]

1 前提
1.1 安装 PostgreSQL 4E 包
1.2 初始化 PostgreSQL 4E 包
1.3 配置PostgreSQL 4E 包
2 概念
3 实例
3.1 场景设计
3.2 设计相关表
3.3 书写触发器
3.3.1 触发器函数
3.3.2 创建触发器
3.4 创建全文索引
3.5 运行上面SQL代码
3.6 测试！
3.7 全文搜索
3.8 获取摘要

[编辑] 前提

[编辑] 安装 PostgreSQL 4E 包

使用全文索引最简单的方法是使用 4E 的 PostgreSQL 包，已经有大量预编译好的包存在，可以在获取安装包里头找到。

[编辑] 初始化 PostgreSQL 4E 包

参考管理员手册里面的初始化 PostgreSQL 和/或集群。

[编辑] 配置PostgreSQL 4E 包

参考管理员手册里面的配置 PostgreSQL 和/或集群。

特别要注意的是default_text_search_config的配置，一定要按照配置来搞。

[编辑] 概念

基于 PostgreSQL 的全文搜索的使用是很简单的，其基本概念是：

在数据库中有个特殊的数据类型，叫做 tsvector；

tsvector 代表“语意”，语意就是一个词的概念；
tsvector 中包括词和词的位置，用于相关性（ranking）的计算；
数据库具备完整的框架：自动分词和计算每个词的位置；
数据库具备完整的框架：可以由用户选定分词方法（模块）和使用的词典；
数据库对 tsvector 类型进行倒排索引或者是深度的b-tree（平衡树索引）提供快速的检索服务；
索引本身如同标准数据库字段一样，是在INSERT和UPDATE的时候自动更新的；
查询是以SQL语言方式进行的
标准的 SELECT
一些特殊的操作符，比如 @@ 是全文匹配的操作符
一个特殊的 ts_query 类型，用于对查询本身进行分词、标记位置等处理，通过全文匹配操作符与 tsvector 类型进行比对

更多信息可以参考官方的文档

[编辑] 实例

[编辑] 场景设计

假设我们设计一个保存BBS的帖子的表，为了简化，我们只有两个字段：标题和正文，表结构如下：

[编辑] 设计相关表

我们设计一个很简单的表：

create table content(
id serial primary key,
subject text,
body text);

其中 subject 字段保存帖子的标题，body字段保存帖子的正文。

然后我们要设计一个保存全文索引内容的表：

create table content_search(
id integer primary key,
subject_fti tsvector,
body_fti tsvector,
foreign key (id) references content(id));

注意这里的数据类型：tsvector，是PostgreSQL为了全文索引专门制作的数据类型。请用这个类型保存分词后的内容字段。

[编辑] 书写触发器

写一个更新全文的触发器，触发器的文档详情可以参考：

http://www.pgsqldb.org/pgsqldoc-8.1c/plpgsql-trigger.html

我在这里直接写代码，很简单：

[编辑] 触发器函数

create or replace function content_trigger() returns trigger as $
begin
if TG_OP = 'INSERT' then
insert into content_search(id, subject_fti, body_fti) values(NEW.id,
to_tsvector(NEW.subject), to_tsvector(NEW.body));
end if;
return NEW;end;$
language plpgsql;

我这里先考虑了INSERT的情况，UPDATE的一会儿更新版本的时候再说。

[编辑] 创建触发器

create trigger content_table_trig after insert or update on content
for each row execute procedure content_trigger();

[编辑] 创建全文索引

给全文索引的表创建索引，我们需要建两个，给标题和正文各自创建一个：

create index content_search_subject_idx on content_search using gin(subject_idx);
create index content_search_body_idx on content_search using gin(body_idx);

这里using后头也可以用gist类型的索引，我在gist和gin的索引类型的区别里讨论他们的区别。

[编辑] 运行上面SQL代码

我们创建所有这些对象，方法是把上面的SQL语句保存在一个文件里，比如文件名叫 my_fti_test.sql，然后用psql执行：

/usr/local/pgsql/bin psql -d testdb -f my_fti_test.sql

你可能需要换成自己的数据库名字（-d 后面的），如果还没有数据库的话，可能需要创建之：

/usr/local/pgsql/bin createdb testdb

这里的psql和 createdb的路径如果用安装包的话，缺省在 /usr/local/pgsql/bin里头，我在上面使用了缺省的路径。

[编辑] 测试！

添加些测试数据：

insert into content(subject, body) values('linux和linux',
'linux,linux,linux,linux,linux,linux,linux,linux,linux');

insert into content(subject, body) values('linux使用和linux安装',
'linux,linux,linux,linux,linux应用是一个很复杂的问题');

insert into content(subject, body) values('linux应用', 'linux应用是一个
很复杂的问题');
insert into content(subject, body) values('linux',
'linux,linux,linux,linux,linux,linux,linux,linux');

注意这里我忽略了 ID 字段，因为我用了缺省值。

可以查询一下这些表：

select * from content;
select * from content_search;

看看是啥东西。

[编辑] 全文搜索

执行下面的查询：

select subject from mail_archive where id in (
select id from mail_archive_fti where subject_fti @@ 'linux' order by
ts_rank_cd(subject_fti, to_tsquery('linux')), ts_rank_cd(mail_body_fti,
to_tsquery('linux')) );

注意，我们已经有基本的排序：先看subject的关键字密度，然后再看正文的。下面的章节将介绍更多的内容。

[编辑] 获取摘要

搜索结果都希望能把摘要拿出来，而不是其中一段东西，那么，这个事情在 4E 的 PostgreSQL 包里面可以这么用：

select fts_summary(mail_body, to_tsquery('linux'), 'HighlightAll=1') from
mail_archive where id in
(select id from mail_archive_fti where mail_body_fti @@ to_tsquery('linux')) limit 1;

注意一下输出的结果，会发现在搜索词周围添加了的高亮标识，而且，查出来的东西就是关键词左近的一些原文。