詳細講解PostgreSQL中的全文搜索的用法

2020-10-29 21:49:50

字體：大中小

來源：轉載

供稿：網友

開發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 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;    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'; ?column?---------- t(1 row) > select to_tsvector('It''s kind of fun to do the impossible') @@ 'impossible';  ?column?---------- f(1 row)

第二個查詢返回了假，因為我們需要構建一個tsquery，使用@@操作符時，把字符串轉型(cast)成了tsquery。下面顯示了這種l轉型和使用to_tsquery()之間的差別。

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');  ?column?---------- t(1 row)

tsquery存儲了要搜索的詞位，可以使用&（與）、|（或）和!（非）邏輯操作符?？梢允褂脠A括號給操作符分組。

> 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)

我們也可以使用：*來表達以某詞開始的查詢。

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

既然我們知道了怎樣使用全文搜索查詢了，我們回到開始的表模式，試著查詢文檔。

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('èéê?'); unaccent---------- eeee(1 row)

讓我們添加一些重音的你內容到我們的post表中。

INSERT INTO post (id, title, content, author_id, language) VALUES (4, 'il était une fois', 'il était une fois un h?tel ...', 2,'french')

如果我們想要忽略重音在我們建立文檔時,之后我們可以簡單做到以下幾點:

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在法國是一個無用詞。這是一個問題讓這些詞停止在我們的文件嗎?我不這么認為etait不是一個真正的無用詞而是拼寫錯誤。

SELECT to_tsvector('fr', 'H?tel') @@ to_tsquery('hotels') as result; result-------- t(1 row)

如果我們為每種語言創建一個無重音的搜索配置,這樣我們的post可以寫入并且我們保持這個值在post.language的中,然后我們可以保持以前的文檔查詢。

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索引? 這兩個索引會成為與他們相關的博文的主題. GiST會導出一個錯誤的匹配，之后需要一個額外的表行查找來驗證得到的匹配. 另一方面, GIN 可以更快地查找但是在創建時會更大更慢.

一個經驗, GIN索引適合靜態的數據因為查找是迅速的. 對于動態數據, GiST 可以更快的更新. 具體來說, GiST索引在動態數據上是好用的并且如果單獨的字（詞位）在100,000以下也是快速的,然而GIN 索引在處理100,000詞位以上時是更好的但是更新就要慢點了.

-- Postgres 文檔 : 第12章全文搜索

在我們的例子中,我們選擇GIN。但是這個選擇不是一定的，你可以根據你自己的數據來作出決定。

我們的架構例子中有一個問題; 分當時分布在擁有不同權重的不同表中的. 為了更好的運行，通過觸發器和物化視圖使得數據非規范化是必要的.

我們并非總是需要非規范化并且有時也需要加入基于索引的功能，就像上面所做的那樣. 另外你可以通過postgres觸發器功能tsvector_update_trigger(...)或者tsvector_update_trigger_column(...)實現相同表的數據的非規范化.參見Postgres文檔以得到更多詳細的信息.

在我們的應用中在結果返回之前存在著一些可接受的延遲. 這是一個使用物化視圖將額外索引加載其中的好的情況.

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元語法比較有用因為它可以查找相似的字符串，其實，這就是拼寫錯誤的定義亚洲香蕉成人av网站在线观看_欧美精品成人91久久久久久久_久久久久久久久久久亚洲_热久久视久久精品18亚洲精品_国产精自产拍久久久久久_亚洲色图国产精品_91精品国产网站_中文字幕欧美日韩精品_国产精品久久久久久亚洲调教_国产精品久久一区_性夜试看影院91社区_97在线观看视频国产_68精品久久久久久欧美_欧美精品在线观看_国产精品一区二区久久精品_欧美老女人bb