806
魔獸
PostgreSQL · 特性介紹 · 全文搜索介紹
背景
在日常的數據處理中，我們經常會有這樣的需求：從一個文本中尋找某個字符串（比如某個單詞）。
對這個需求，我們可以用類似這樣的SQL完成：**SELECT * FROM tbl WHERE text LIKE '%rds PostgreSQL%';**（找到含有“rds PostgreSQL”的文本）
現在我們考慮一些特殊的情形：
需要查找的文本特別多，特別大；
不做單純的字符串匹配，而是考慮自然語言的一些特性，比如匹配某一類字符串（域名，人名）或者匹配單詞的所有形式（不考慮它的詞性及變化，比如have，has，had都匹配出來）；
對中文自然語言特性的支持。
那麼此時再用以上的“SELECT ... LIKE ...”就不明智了，因為對數據庫來說，這樣的SQL必然走的是全表掃描，那麼當文本特別多，特別大的時候，查找效率就會很低。
另外，這樣的SQL也不會智能到可以處理自然語言的特性。
怎麼辦呢？PostgreSQL（以下簡稱PG）提供了強大的全文搜索功能可以滿足這樣的需求。
對文本的預處理
全文搜索首先需要對文本預處理，包括3步：
將文本分解成一個個token，這些token可以是數字，單詞，域名，人名，email的格式等等。在PG中可以定義一個parser來做這個工作。
將第一步分解成的token標準化，所謂的標準化就是利用一些規則將token分好類（比如人名是一類，域名是一類等等）。標準化後的token我們稱之為lexeme。在PG中是通過定義一個詞典來做這個工作。PG裏最簡單的詞典simple的標準化過程就是將大寫字母轉成小寫字母。
對文本打分，優化查找過程。比如對於待查找的詞，文本1匹配的數量大於文本2匹配的數量，那麼在這個查找過程，文本1的優先級大於文本2的優先級。
在PG中，以上對文本的預處理可以通過一個函數to_tsvector來完成，函數的返回值是tsvector這個數據類型。
另外，對於待查找的單詞，我們也要用to_tsquery這個函數包裝起來，函數的返回值是tsquery這個數據類型。
一個簡單的例子見下麵，to_tsquery裏的參數可以使用運算符(&:與，|:或，!:非)：
SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat');
?column?
----------
t
Quick Start
在了解了這些概念之後，我們用實際的例子來玩一玩PG的全文搜索。
我們在client端輸入以下命令，\dFp顯示的是所有的parser，這裏隻有一個默認parser(default)。
\dFp+ default 顯示默認parser(default)的詳細信息：parse的過程（5個函數），parse的Token類型（asciihword, asciiword...）。
sbtest=# \dFp
List of text search parsers
Schema | Name | Description
------------+---------+---------------------
pg_catalog | default | default word parser
(1 row)
sbtest=# \dFp+ default
Text search parser "pg_catalog.default"
Method | Function | Description
-----------------+----------------+-------------
Start parse | prsd_start | (internal)
Get next token | prsd_nexttoken | (internal)
End parse | prsd_end | (internal)
Get headline | prsd_headline | (internal)
Get token types | prsd_lextype | (internal)
Token types for parser "pg_catalog.default"
Token name | Description
-----------------+------------------------------------------
asciihword | Hyphenated word, all ASCII
asciiword | Word, all ASCII
blank | Space symbols
email | Email address
entity | XML entity
file | File or path name
float | Decimal notation
host | Host
hword | Hyphenated word, all letters
hword_asciipart | Hyphenated word part, all ASCII
hword_numpart | Hyphenated word part, letters and digits
hword_part | Hyphenated word part, all letters
int | Signed integer
numhword | Hyphenated word, letters and digits
numword | Word, letters and digits
protocol | Protocol head
sfloat | Scientific notation
tag | XML tag
uint | Unsigned integer
url | URL
url_path | URL path
version | Version number
word | Word, all letters
(23 rows)
輸入\dF+ english，給出標準化各類英語token時所用到的dictionary：
sbtest=# \dF+ english
Text search configuration "pg_catalog.english"
Parser: "pg_catalog.default"
Token | Dictionaries
-----------------+--------------
asciihword | english_stem
asciiword | english_stem
email | simple
file | simple
float | simple
host | simple
hword | english_stem
hword_asciipart | english_stem
hword_numpart | simple
hword_part | english_stem
int | simple
numhword | simple
numword | simple
sfloat | simple
uint | simple
url | simple
url_path | simple
version | simple
word | english_stem
創建以default為parser的配置defcfg，並增加token映射，這裏我們隻關心email, url, host：
sbtest=# CREATE TEXT SEARCH CONFIGURATION defcfg (PARSER = default);
CREATE TEXT SEARCH CONFIGURATION
sbtest=# ALTER TEXT SEARCH CONFIGURATION defcfg ADD MAPPING FOR email,url,host WITH simple;
ALTER TEXT SEARCH CONFIGURATION
建好配置defcfg後，我們看看利用defcfg對文本進行處理的結果。這裏使用to_tsvector函數，可以看到email，url，host都被識別出來了：
sbtest=# select to_tsvector('defcfg','xxx yyy xxx@taobao.com yyy@sina.com https://google.com/123 12345 ');
to_tsvector
-----------------------------------------------------------------------
'google.com':4 'google.com/123':3 'xxx@taobao.com':1 'yyy@sina.com':2
(1 row)
在實際對表內的文本做全文搜索時，一般對目標列建立gin索引（也就是倒排索引，詳情見官方文檔），這樣可以加快查詢效率，具體操作如下：
sbtest=# CREATE TABLE t1(c1 text);
CREATE TABLE
sbtest=# CREATE INDEX c1_idx ON t1 USING gin(to_tsvector('defcfg', c1));
CREATE INDEX
sbtest=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+------+-----------
c1 | text |
Indexes:
"c1_idx" gin (to_tsvector('defcfg'::regconfig, c1))
這裏我們插入2條文本，並做一些匹配：
sbtest=# INSERT INTO t1 VALUES('xxx yyy xxx@taobao.com yyy@sina.com https://google.com 12345');
INSERT 0 1
sbtest=# INSERT INTO t1 VALUES('xxx yyy xxx@gmail.com yyy@sina.com https://google.com 12345');
INSERT 0 1
sbtest=# select * from t1;
c1
-------------------------------------------------------------
xxx yyy xxx@taobao.com yyy@sina.com https://google.com 12345
xxx yyy xxx@gmail.com yyy@sina.com https://google.com 12345
(2 rows)
sbtest=# select * from t1 where to_tsvector('defcfg',c1) @@ 'google.com';
c1
-------------------------------------------------------------
xxx yyy xxx@taobao.com yyy@sina.com https://google.com 12345
xxx yyy xxx@gmail.com yyy@sina.com https://google.com 12345
(2 rows)
sbtest=# select * from t1 where to_tsvector('defcfg',c1) @@ to_tsquery('google.com & yyy@sina.com');
c1
-------------------------------------------------------------
xxx yyy xxx@taobao.com yyy@sina.com https://google.com 12345
xxx yyy xxx@gmail.com yyy@sina.com https://google.com 12345
(2 rows)
sbtest=# select * from t1 where to_tsvector('defcfg',c1) @@ to_tsquery('google.com & xxx@gmail.com');
c1
------------------------------------------------------------
xxx yyy xxx@gmail.com yyy@sina.com https://google.com 12345
(1 row)
以上的操作都是針對英文，實際上對中文也是支持的，不過會稍微麻煩點，因為中文的token必須通過分詞才能產生，所以需要先裝分詞的組件scws和zhparser，具體可以參考這篇博文。
結語
本文對PG的全文搜索做了一個入門級的介紹，方便用戶快速上手，如果需要對全文搜索作更深入的研究，建議閱讀官方文檔第12章。
最後更新：2017-04-01 13:38:49