详细讲解PostgreSQL中的全文搜索的用法
开发Web应用时，你经常要加上搜索功能。甚至还不知能要搜什么，就在草图上画了一个放大镜。
搜索是项非常重要的功能，所以像elasticsearch和SOLR这样的基于lucene的工具变得很流行。它们都很棒。但使用这些大规模“杀伤性”的搜索武器前，你可能需要来点轻量级的，但又足够好的搜索工具。
所谓“足够好”，我是指一个搜索引擎拥有下列的功能：
- 词根（Stemming）
- 排名/提升(Ranking / Boost)
- 支持多种语言
- 对拼写错误模糊搜索
- 方言的支持
幸运的是PostgreSQL对这些功能全支持。
本文的目标读者是：
- 使用PostgreSQL，同时又不想安装其它的搜索引擎。
- 使用其它的数据库（比如MySQL），同时需要更好的全文搜索功能。
本文中我们将通过下面的表和数据说明PostgreSQL的全文搜索功能。
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自身还有多个标签(tag)。
什么是全文搜索
首先，让我们看一下定义：
在文本检索中，全文搜索是指从全文数据库中搜索计算机存储的单个或多个文档(document)的技术。全文搜索不同于基于元数据的搜索或根据数据库中原始文本的搜索。
-- 维基百科
这个定义中引入了文档的概念，这很重要。当你搜索数据时，你在寻找你想要找到的有意义的实体，这些就是你的文档。PostgreSQL的文档中解释地很好。
文档是全文搜索系统中的搜索单元。比如，一篇杂质文章或是一封邮件消息。
-- Postgres 文档
这里的文档可以跨多个表，代表为我们想要搜索的逻辑实体。
构建我们的文档(document)
上一节，我们介绍了文档的概念。文档与表的模式无关，而是与数据相关，把字段联合为一个有意义的实体。根据示例中的表的模式，我们的文档(document)由这些组成：
- post.title
- post.content
- post的author.name
- 关联到post的所有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分组了，因为有多个tag关联到一个post，我们使用string_agg()作聚合函数。即使author是外键并且一个post不能有多个author，也要求对author添加聚合函数或者把author加到GROUP BY中。
我们还用了coalesce()。当值可以是NULL时，使用coalesce()函数是个很好的办法，否则字符串连接的结果将是NULL。
至此，我们的文档只是一个长string，这没什么用。我们需要用to_tsvector()把它转换为正确的格式。
SELECT to_tsvector(post.title) ||
to_tsvector(post.content) ||
to_tsvector(author.name) ||
to_tsvector(coalesce((string_agg(tag.name, ' ')), '')) as documentFROM 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是一个标准词位的有序列表（sorted list），标准词位（distinct lexeme）就是说把同一单词的各种变型体都被标准化相同的。
标准化过程几乎总是把大写字母换成小写的，也经常移除后缀（比如英语中的s,es和ing等）。这样可以搜索同一个字的各种变体，而不是乏味地输入所有可能的变体。
数字表示词位在原始字符串中的位置，比如“man"出现在第6和15的位置上。你可以自己数数看。
Postgres中to_tesvetor的默认配置的文本搜索是“英语“。它会忽略掉英语中的停用词（stopword，译注：也就是am is are a an等单词)。
这解释了为什么tsvetor的结果比原句子中的单词少。后面我们会看到更多的语言和文本搜索配置。
查询
我们知道了如何构建一个文档，但我们的目标是搜索文档。我们对tsvector搜索时可以使用@@操作符，使用说明见此处。看几个查询文档的例子。
> select to_tsvector('If you can dream it, you can do it') @@ 'dream';
"htmlcode">
SELECT 'impossible'::tsquery, to_tsquery('impossible');
tsquery | to_tsquery
--------------+------------
'impossible' | 'imposs'(1 row)
但"dream"的词位与它本身相同。
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');
"htmlcode">
> SELECT to_tsvector('If the facts don't fit the theory, change the facts') @@ to_tsquery('! fact');
"htmlcode">
> SELECT to_tsvector('If the facts don''t fit the theory, change the facts.') @@ to_tsquery('theo:*');
"htmlcode">
SELECT pid, p_titleFROM (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 内置的文本搜索功能支持多种语言： 丹麦语，荷兰语，英语，芬兰语，法语，德语，匈牙利语，意大利语，挪威语，葡萄牙语，罗马尼亚语，俄语，西班牙语，瑞典语，土耳其语。
SELECT to_tsvector('english', 'We are running');
to_tsvector-------------
'run':3
(1 row)SELECT to_tsvector('french', 'We are running');
to_tsvector----------------------------
'are':2 'running':3 'we':1
(1 row)
基于我们最初的模型，列名可以用来创建tsvector。 假设post表中包含不同语言的内容，且它包含一列language。
ALTER TABLE post ADD language text NOT NULL DEFAULT('english');
为了使用language列，现在我们重新编译文档。
SELECT to_tsvector(post.language::regconfig, post.title) ||
to_tsvector(post.language::regconfig, post.content) ||
to_tsvector('simple', author.name) ||
to_tsvector('simple', coalesce((string_agg(tag.name, ' ')), '')) as documentFROM postJOIN author ON author.id = post.author_idJOIN posts_tags ON posts_tags.post_id = posts_tags.tag_idJOIN tag ON tag.id = posts_tags.tag_idGROUP BY post.id, author.id;
如果缺少显示的转化符：：regconfig，查询时会产生一个错误：
ERROR: function to_tsvector(text, text) does not exist
regconfig是对象标识符类型，它表示Postgres文本搜索配置项。:http://www.postgresql.org/docs/9.3/static/datatype-oid.html
现在，文档的语义会使用post.language中正确的语言进行编译。
我们也使用simple，它也是Postgres提供的一个文本搜索配置项。simple并不忽略禁用词表，它也不会试着去查找单词的词根。使用simple时，空格分割的每一组字符都是一个语义；对于数据来说，simple文本搜索配置项很实用，就像一个人的名字，我们也许不想查找名字的词根。
SELECT to_tsvector('simple', 'We are running');
to_tsvector
---------------------------- 'are':2 'running':3 'we':1(1 row)
重音字符
当你建立一个搜索引擎支持多种语言时你也需要考虑重音问题。在许多语言中重音非常重要,可以改变这个词的含义。Postgres附带一个unaccent扩展去调用 unaccentuate内容是有用处的。
CREATE EXTENSION unaccent;SELECT unaccent('èéê"htmlcode">
INSERT INTO post (id, title, content, author_id, language)
VALUES (4, 'il était une fois', 'il était une fois un h"htmlcode">
SELECT to_tsvector(post.language, unaccent(post.title)) ||
to_tsvector(post.language, unaccent(post.content)) ||
to_tsvector('simple', unaccent(author.name)) ||
to_tsvector('simple', unaccent(coalesce(string_agg(tag.name, ' '))))JOIN author ON author.id = post.author_idJOIN posts_tags ON posts_tags.post_id = posts_tags.tag_idJOIN tag ON author.id = post.author_idGROUP BY p.id
这样工作的话，如果有更多错误的空间它就有点麻烦。 我们还可以建立一个新的文本搜索配置支持无重音的字符。
CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPINGFOR hword, hword_part, word WITH unaccent, french_stem;
当我们使用这个新的文本搜索配置,我们可以看到词位
SELECT to_tsvector('french', 'il était une fois');
to_tsvector-------------
'fois':4
(1 row)SELECT to_tsvector('fr', 'il était une fois');
to_tsvector--------------------
'etait':2 'fois':4
(1 row)
这给了我们相同的结果，第一作为应用unaccent并且从结果建立tsvector。
SELECT to_tsvector('french', unaccent('il était une fois'));
to_tsvector--------------------
'etait':2 'fois':4
(1 row)
词位的数量是不同的,因为il était une在法国是一个无用词。这是一个问题让这些词停止在我们的文件吗"htmlcode">
SELECT to_tsvector('fr', 'H"htmlcode">
SELECT to_tsvector(post.language, post.title) ||
to_tsvector(post.language, post.content) ||
to_tsvector('simple', author.name) ||
to_tsvector('simple', coalesce(string_agg(tag.name, ' ')))JOIN author ON author.id = post.author_idJOIN posts_tags ON posts_tags.post_id = posts_tags.tag_idJOIN tag ON author.id = post.author_idGROUP BY p.id
如果你需要为每种语言创建无重音的文本搜索配置由Postgres支持,然后你可以使用gist
我们当前的文档大小可能会增加,因为它可以包括无重音的无用词但是我们并没有关注重音字符查询。这可能是有用的如有人用英语键盘搜索法语内容。
归类
当你创建了一个你想要的搜索引擎用来搜索相关的结果（根据相关性归类）的时候，归类可以是基于许多因素的，它的文档大致解释了这些（归类依据）内容。
归类试图处理特定的上下文搜索, 因此有许多个配对的时候，相关性最高的那个会被排在第一个位置。PostgreSQL提供了两个预定义归类函数，它们考虑到了词法解释，接近度和结构信息；他们考虑到了在上下文中的词频，如何接近上下文中的相同词语，以及在文中的什么位置出现和其重要程度。
-- PostgreSQL documentation
通过PostgreSQL提供的一些函数得到我们想要的相关性结果，在我们的例子中我们将会使用他们中的2个：ts_rank() 和 setweight() 。
函数setweight允许我们通过tsvector函数给重要程度（权）赋值；值可以是'A', 'B', 'C' 或者 'D'。
SELECT pid, p_titleFROM (SELECT post.id as pid,
post.title as p_title,
setweight(to_tsvector(post.language::regconfig, post.title), 'A') ||
setweight(to_tsvector(post.language::regconfig, post.content), 'B') ||
setweight(to_tsvector('simple', author.name), 'C') ||
setweight(to_tsvector('simple', coalesce(string_agg(tag.name, ' '))), 'B') 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_searchWHERE p_search.document @@ to_tsquery('english', 'Endangered & Species')ORDER BY ts_rank(p_search.document, to_tsquery('english', 'Endangered & Species')) DESC;
上面的查询，我们在文中不同的栏里面赋了不同的权值。post.title的重要程度超过post.content和tag的总和。最不重要的是author.name。
这意味着如果我们搜索关键词“Alice”，那么在题目中包含这个关键词的文档就会排在搜索结果的前面，在此之后是在内容中包含这些关键词的文档，最后才是作者名字中包含这些关键词的文档.
基于对文档各个部分的权重分配ts_rank()这个函数返回一个浮点数，这个浮点数代表了文档和查询关键词的相关性.
SELECT ts_rank(to_tsvector('This is an example of document'),
to_tsquery('example | document')) as relevancy;
relevancy-----------
0.0607927
(1 row)SELECT ts_rank(to_tsvector('This is an example of document'),
to_tsquery('example ')) as relevancy;
relevancy-----------
0.0607927
(1 row)SELECT ts_rank(to_tsvector('This is an example of document'),
to_tsquery('example | unkown')) as relevancy;
relevancy-----------
0.0303964
(1 row)SELECT ts_rank(to_tsvector('This is an example of document'),
to_tsquery('example & document')) as relevancy;
relevancy-----------
0.0985009
(1 row)SELECT ts_rank(to_tsvector('This is an example of document'),
to_tsquery('example & unknown')) as relevancy;
relevancy-----------
1e-20
(1 row)
但是, 相关性的概念是模糊的，而且是与特定的应用相关. 不同的应用可能需要额外的信息来得到想要的排序结果,比如,文档的修改时间. 内建的排序功能如asts_rank只是个例子. 你可以写出自己的排序函数 并且/或者 将得到的结果和其他因素混合来适应你自己的特定需求.
这里说明一下, 如果我们想是新的文章比旧的文章更重要，可以讲ts_rank函数的数值除以文档的年龄+1(为防止被0除).
优化与索引
将一个表中的搜索结果优化为直线前进的. PostgreSQL 支持基于索引的功能，因此你可以用tsvector()函数方便地创建GIN索引.
CREATE INDEX idx_fts_post ON post
USING gin(setweight(to_tsvector(language, title),'A') ||
setweight(to_tsvector(language, content), 'B'));
GIN还是GiST索引"htmlcode">
CREATE MATERIALIZED VIEW search_index AS SELECT post.id,
post.title,
setweight(to_tsvector(post.language::regconfig, post.title), 'A') ||
setweight(to_tsvector(post.language::regconfig, post.content), 'B') ||
setweight(to_tsvector('simple', author.name), 'C') ||
setweight(to_tsvector('simple', coalesce(string_agg(tag.name, ' '))), 'A') as documentFROM postJOIN author ON author.id = post.author_idJOIN posts_tags ON posts_tags.post_id = posts_tags.tag_idJOIN tag ON tag.id = posts_tags.tag_idGROUP BY post.id, author.id
之后重新索引搜索引擎就是定期运行REFRESH MATERIALIZED VIEW search_index这么简单.
现在我们可以给物化视图添加索引.
CREATE INDEX idx_fts_search ON search_index USING gin(document);
查询也变得同样简单.
SELECT id as post_id, titleFROM search_indexWHERE document @@ to_tsquery('english', 'Endangered & Species')ORDER BY ts_rank(p_search.document, to_tsquery('english', 'Endangered & Species')) DESC;
如果延迟变得无法忍受，你就应该去研究一下使用触发器的替代方法.
建立文档存储的方式并不唯一;这取决于你文档的情况: 单表、多表，多国语言，数据量 ...
Thoughtbot.com 发表了文章"Implementing Multi-Table Full Text Search with Postgres in Rails" 我建议阅读以下.
拼写错误
PostgreSQL 提供了一个非常有用的扩展程序pg_trgm。 相关文档见pg_trgm doc。
CREATE EXTENSION pg_trgm;
pg_trgm支持N元语法如N==3。N元语法比较有用因为它可以查找相似的字符串，其实，这就是拼写错误的定义 – 一个相似但不正确的单词。
SELECT similarity('Something', 'something');
similarity------------
1
(1 row)SELECT similarity('Something', 'samething');
similarity------------
0.538462
(1 row)SELECT similarity('Something', 'unrelated');
similarity------------
0
(1 row)SELECT similarity('Something', 'everything');
similarity
------------
0.235294
(1 row)SELECT similarity('Something', 'omething');
similarity------------
0.583333
(1 row)
通过上面的示例你可以看到，similarity 函数返回一个表示两个字符串之间相似度的浮点值。 检测拼写错误就是一系列的收集文档中使用的词位、比较词位与输入文本的相似度的过程。 我发现检测拼写错误时，相似度临界值设置为0.5比较合适。 首先，我们需要根据文档创建一个唯一性词位列表，在列表中每一个词位都是唯一的。
CREATE MATERIALIZED VIEW unique_lexeme ASSELECT word FROM ts_stat('SELECT to_tsvector('simple', post.title) ||
to_tsvector('simple', post.content) ||
to_tsvector('simple', author.name) ||
to_tsvector('simple', coalesce(string_agg(tag.name, ' ')))
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');
上面的脚本使用word列创建了一个视图，word列内容来自于词位列表。 我们使用simple关键字，这样table表中可以存储多种语言的文本。 一旦创建了这个实体化视图，我们需要添加一个索引来使相似度查询速度更快。
CREATE INDEX words_idx ON search_words USING gin(word gin_trgm_ops);
幸运的是，搜索引擎中使用的唯一性词位列表不会快速变化，这样我们就无需通过下面脚本经常刷新实体化视图：
REFRESH MATERIALIZED VIEW unique_lexeme;
一旦我们建立起这个表，查找最接近的匹配是很容易的。
SELECT word
WHERE similarity(word, 'samething') > 0.5 ORDER BY word <-> 'samething'LIMIT 1;
这个查询返回的是这样一个语义，它相似度满足(>0.5)，再根据输入的samething将其最接近的排在首位。操作符<->返回的是参数间的“距离”，而且是一减去similarity()的值。
当你决定在你的搜索中处理拼写错误的时候，你不会希望看到它（拼写错误）出现在每一个查询中。相反地，当你在搜索无结果时，可以为了拼写错误去查询，并使用查询所提供结果给用户一些建议。如果数据来自于非正式的通讯，例如：社交网络，可能你的数据中会包含拼写错误。你可以通过追加一个类似的语义到你的tsquery中，来获得一个好点的结果。
"Super Fuzzy Searching on PostgreSQL" 是一篇很好的关于为拼写错误和搜索Postgres使用三字母组的参考文章。
在我使用的例子中，使用unique语义的表不会大于2000行，而且我的理解是，如果你有超过1M的文本时使用unique语义，你将会遇到该方法的性能问题。
关于MySQL和RDS（远程数据服务）
这在Postgres RDS上能运行吗？
上面所有的示例在RDS上都是可以运行的。 据我所知，RDS搜索特性中唯一的限制是搜索某些数据时需要访问文件系统，如自定义字典，拼写检查程序，同义词，主题词表。 相关信息见亚马逊aws论坛。
我使用的是MYSQL数据库，我可以使用内置的全文本搜索功能吗？
如果是我，我不会去用这个功能。 无需争论，MySQL的全文本搜索功能非常局限。 默认情况，它不支持任何语言的词干提取功能。 我偶然发现一个可以安装的词干提取的函数，但是MYSQL不支持基于索引的函数。
那么你可以做些什么？ 鉴于我们上面的讨论，如果 Postgres能够胜任你使用的各个场景，那么考虑下把数据库换为 Postgres。 数据库迁移工作可以通过工具如 py-mysql2pgsql方便地完成。 或者你可以研究一下更高级的解决方案如 SOLR（基于 Lucene的全文搜索服务器）和 Elasticsearch（基于 Lucene的开源、分布式、 RESTful搜索引擎）。
总结
我们已经了解了基于一个特殊的文档如何构建一个性能良好且支持多语言的文本搜索引擎。 这篇文章只是一个概述，但是它已经给你提供了足够的背景知识和示例，这样你可以开始构建自己的搜索引擎。 在这篇文章中，我也许犯了一些错误，如果你能把错误信息发送到blog@lostpropertyhq.com，我将感激不尽。
Postgres的全文本搜索特性非常好，而且搜索速度足够快。 这可以使你的应用中的数据不断增长，而无需依赖其它工具进行处理。 Postgres的搜索功能是银弹吗？ 如果你的核心业务围绕搜索进行，它可能不是的。
它移除了一些特性，但是在大部分场景中你不会用到这些特性。 毫无疑问，你需要认真分析和理解你的需求来决定使用哪种搜索方式。
就我个人而言，我希望Postgres全文本搜索功能继续改善，并新增下面的一些特性：
- 额外的内置语言支持： 汉语，日语...
- 围绕Lucene的外国数据包装程序。 在全文本搜索功能上，Lucene仍然是最优秀的工具，把它集成到Postgres中会有很多好处。
- 更多排名结果的提高或评分特性会是一流的。 Elasticsearch 和 SOLR已经提供了先进的解决方案。
- 进行模糊查询（tsquery）时不使用trigram的方式会非常棒。 Elasticsearch 提供了一种非常简单的方式来实现模糊搜索查询。
- 能够通过SQL动态创建和编辑如字典内容、同义词、主题词表的特性，而不再使用把文件添加到文件系统的方式。
Postgres 没有ElasticSearch 和 SOLR 那么先进，毕竟ElasticSearch 和 SOLR是专门进行全文本搜索的工具，而全文本搜索只是PostgresSQL一个比较优秀的特性。
上一篇：在PostgreSQL上安装并使用扩展模块的教程
下一篇：介绍PostgreSQL中的范围类型特性