Postgres 全文本搜索已经足够好了(一)

Tags: postgresql database

当你要建立一个 web 应用程序时,您通常要添加搜索功能。我们添加搜索功能时甚至不知道我们将要搜索的东西。
搜索已成为一项重要的功能,我们看到很多工具用户量非常大,如:elasticsearch和SOLR他们都基于 lucene,他们是伟大的工具,但在使用这种重量级、大规模毁灭性搜索武器之前,也许你的需要是轻量级的足够用的搜索工具 ! !

我的意思是 '足够好' 由你做什么决定的?一个搜索引擎应该具有以下特点:

  • 词干

  • Ranking/Boost

  • 支持多种语言

  • 针对拼写错误进行模糊搜索

  • 方言的支持

幸运的是 PostgreSQL 支持所有这些功能。
这篇文章针对以下人群

  •  使用 PostgreSQL 作为搜索引擎并不想安装额外的系统。

  • 使用一个其他数据库(如: MySQL),但需要有更好的全文搜索功能。

在这篇文章我们要逐步说明一些中 Postgres 基于下列表和数据的全文搜索功能:

CREATE TABLE author(
   id SERIAL PRIMARY KEY,
   name TEXT NOT NULL
);

CREATE TABLE post(
   id SERIAL PRIMARY KEY,
   title TEXT NOT NULL,
   content TEXT NOT NULL,
   author_id INT NOT NULL references author(id) 
);

CREATE TABLE tag(
   id SERIAL PRIMARY KEY,
   name TEXT NOT NULL 
);

CREATE TABLE posts_tags(
   post_id INT NOT NULL references post(id),
   tag_id INT NOT NULL references tag(id)
 );

INSERT INTO author (id, name) 
VALUES (1, 'Pete Graham'), 
       (2, 'Rachid Belaid'), 
       (3, 'Robert Berry');

INSERT INTO tag (id, name) 
VALUES (1, 'scifi'), 
       (2, 'politics'), 
       (3, 'science');

INSERT INTO post (id, title, content, author_id) 
VALUES (1, 'Endangered species', 'Pandas are an endangered species', 1 ), 
       (2, 'Freedom of Speech', 'Freedom of speech is a necessary right missing in many countries', 2), 
       (3, 'Star Wars vs Star Trek', 'Few words from a big fan', 3);


INSERT INTO posts_tags (post_id, tag_id) 
VALUES (1, 3), 
       (2, 2), 
       (3, 1);

它是传统的博客应用包含Post对象有Title和Content。一个Post对象是与Author通过一个外键相关联。Post本身可以有多个Tags

什么是全文搜索。

首先,让我们看看定义:

在文本检索、全文本搜索指的是在全文数据库中搜索单个计算机存储文档或集合的技术。全文本搜索区别于搜索的方面是:基于元数据或代表数据库中的原始文本的某个部分。

-- 维基百科

这一定义介绍了Document这是很重要的概念。当您运行一个搜索跨越您的数据,您搜索对您意义的实体时,这些都是您的文档 !PostgreSQL对文档解释令人惊讶。

文档是全文搜索系统中的单位,例如:杂志文章或电子邮件消息。

-- Postgres文档

文档可以跨多个表,它代表我们想要搜索的逻辑实体。

创建我们的文档

在上一节中,我们介绍了文档的概念。文档与我们表结构不相关但与数据相关 。
基于我们的示例表结构,文档是由以下部分组成:

  • post.title

  • post.content

  • Post的author.name

  • 与文档关联的所有tag.name

若要创建基于这一标准的文档,考虑下面的 SQL 语句:

SELECT post.title || ' ' || 
        post.content || ' ' ||
        author.name || ' ' ||
        coalesce((string_agg(tag.name, ' ')), '') as document
 FROM post
 JOIN author ON author.id = post.author_id
 JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
 JOIN tag ON tag.id = posts_tags.tag_id
 GROUP BY post.id, author.id;

               document
 --------------------------------------------------
 Endangered species Pandas are an endangered species Pete Graham politics
 Freedom of Speech Freedom of speech is a necessary right missing in many countries Rachid Belaid politics
 Star Wars vs Star Trek Few words from a big fan Robert Berry politics
(3 rows)

我们根据post和author对数据进行分组,我们采用 string_agg() 作为聚合函数,因为多个tag可以关联到一个post。即使如果author是一个外键并且post不能有多个author,就需要添加author的聚合函数或将author添加到 GROUP BY。

我们还使用coalesce()函数。当一个值可以为空则最好使用 coalesce() 函数,否则串联会导致一个 NULL 值。

在这一阶段我们的文档是只是一个长的字符串,这对我们的搜索不会有帮助 ;我们需要通过函数 to_tsvector()将它转化为正确的格式。

SELECT to_tsvector(post.title) || 
       to_tsvector(post.content) ||
       to_tsvector(author.name) ||
       to_tsvector(coalesce((string_agg(tag.name, ' ')), '')) as document
FROM post
JOIN author ON author.id = post.author_id
JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
JOIN tag ON tag.id = posts_tags.tag_id
GROUP BY post.id, author.id;

               document
 --------------------------------------------------
 'endang':1,6 'graham':9 'panda':3 'pete':8 'polit':10 'speci':2,7
 'belaid':16 'countri':14 'freedom':1,4 'mani':13 'miss':11 'necessari':9 'polit':17 'rachid':15 'right':10 'speech':3,6
'berri':13 'big':10 'fan':11 'polit':14 'robert':12 'star':1,4 'trek':5 'vs':3 'war':2 'word':7
(3 rows)

此查询将我们的文档作为 tsvector返回,是一种适合于进行全文搜索。让我们尝试一个简单的字符串转换成 tsvector。

SELECT to_tsvector('Try not to become a man of success, but rather try to become a man of value');

这个查询将返回如下结果:

                             to_tsvector
----------------------------------------------------------------------
'becom':4,13 'man':6,15 'rather':10 'success':8 'tri':1,11 'valu':17
(1 row)

发生了一些怪事。首先结果比原句中的词少了一些,一些单词与原剧中不同不同 (try成为tri)并且所有词后面都跟着数字。为什么呢?
Tsvector值是将不同的变体归一化的,使相同的词的变体看了起来一致。例如,归一化几乎总是包括转换大写字母到小写字母同时经常涉及删除后缀(如英语中的’s ‘,’es’,  ‘ing')。这允许搜索来查找异体字的同一个词,而无需繁琐地查找所有可能的变式。
这些数字代表词原始字符串中的位置。例如,"man"是在 6 和第 15 的位置。
Postgres使用英语作为函数 to_tsvector的默认文本搜索配置,它也将忽略英语的停止词。
这解释了为什么 tsvector 结果相对于原句子要少一些。后面将会看奥更多关于语言和文本搜索的配置。

查询

我们已经看到如何创建文档,但在这里的目标是要找到该文档。对于针对 tsvector 运行一个查询,我们可以使用@@运算符。让我们看到一些例子关于如何查询我们的文档。

> select to_tsvector('If you can dream it, you can do it') @@ 'dream';
 ?column?
----------
 t
(1 row)

> select to_tsvector('It''s kind of fun to do the impossible') @@ 'impossible';

 ?column?
----------
 f
(1 row)

第二个查询返回 false,因为我们需要使用运算符的 tsquery创建相同的词汇,使用@@强制字符串转换成 tsquery。以下代码显示casting和使用函数 to_tsquery() 之间的区别

SELECT 'impossible'::tsquery, to_tsquery('impossible');
   tsquery    | to_tsquery
--------------+------------
 'impossible' | 'imposs'
(1 row)


但是在 'dream' 的情况stem等于这个词。

SELECT 'dream'::tsquery, to_tsquery('dream');
   tsquery    | to_tsquery
--------------+------------
 'dream'      | 'dream'
(1 row)

现在我们将使用to_tsquery 来查询文档

SELECT to_tsvector('It''s kind of fun to do the impossible') @@ to_tsquery('impossible');

 ?column?
----------
 t
(1 row)

tsquery存储的词汇都将被搜索,可以结合布尔运算符 & (AND)  |(OR) 和  !(Not)。可以使用括号强制执行分组运算

> SELECT to_tsvector('If the facts don't fit the theory, change the facts') @@ to_tsquery('! fact');

 ?column?
----------
 f
(1 row)

> SELECT to_tsvector('If the facts don''t fit the theory, change the facts') @@ to_tsquery('theory & !fact');

 ?column?
----------
 f
(1 row)

> SELECT to_tsvector('If the facts don''t fit the theory, change the facts.') @@ to_tsquery('fiction | theory');

 ?column?
----------
 t
(1 row)

在使用:* 时,我们也可以使用startwith样式查询。

> SELECT to_tsvector('If the facts don''t fit the theory, change the facts.') @@ to_tsquery('theo:*');

 ?column?
----------
 t
(1 row)

现在,我们知道如何使全文搜索查询,我们可以回到我们最初的表结构,并尝试查询我们的文档。

SELECT pid, p_title
FROM (SELECT post.id as pid,
             post.title as p_title,
             to_tsvector(post.title) || 
             to_tsvector(post.content) ||
             to_tsvector(author.name) ||
             to_tsvector(coalesce(string_agg(tag.name, ' '))) as document
      FROM post
      JOIN author ON author.id = post.author_id
      JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
      JOIN tag ON tag.id = posts_tags.tag_id
      GROUP BY post.id, author.id) p_search
WHERE p_search.document @@ to_tsquery('Endangered & Species');

 pid |      p_title
-----+--------------------
   1 | Endangered species
(1 row)

这将会找到包含Endangered和Species以及近义词的文档。

Postgres 全文本搜索已经足够好了(二)

本文链接:http://www.4byte.cn/learning/120045/postgres-quan-wen-ben-sou-suo-yi-jing-zu-gou-hao-liao-yi.html



相关文章