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

From PostgreSQL 中文维基, PostgreSQL 中文站, PostgreSQL 中国社区, PostgreSQL Chinese community

[编辑] 前提

[编辑] 安装 PostgreSQL 4E 包

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

[编辑] 初始化 PostgreSQL 4E 包

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

[编辑] 配置PostgreSQL 4E 包

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

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

[编辑] 概念

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

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

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