PgSQL · 特性介绍 · 全文搜索介绍
最后更新于:2022-04-01 10:37:09
## 背景
在日常的数据处理中,我们经常会有这样的需求:从一个文本中寻找某个字符串(比如某个单词)。
对这个需求,我们可以用类似这样的SQL完成:SELECT * FROM tbl WHERE text LIKE ‘%rds PostgreSQL%’;(找到含有“rds PostgreSQL”的文本)。
现在我们考虑一些特殊的情形:
1. 需要查找的文本特别多,特别大;
2. 不做单纯的字符串匹配,而是考虑自然语言的一些特性,比如匹配某一类字符串(域名、人名)或者匹配单词的所有形式(不考虑它的词性及变化,比如have,has,had都匹配出来);
3. 对中文自然语言特性的支持。
那么此时再用以上的 “SELECT … LIKE …” 就不明智了,因为对数据库来说,这样的SQL必然走的是全表扫描,那么当文本特别多、特别大的时候,查找效率就会很低。
另外,这样的SQL也不会智能到可以处理自然语言的特性。
怎么办呢?PostgreSQL(以下简称PG)提供了强大的全文搜索功能可以满足这样的需求。
## 对文本的预处理
全文搜索首先需要对文本预处理,包括3步:
1. 将文本分解成一个个token,这些token可以是数字、单词、域名、人名、email的格式等等。在PG中可以定义一个parser来做这个工作。
2. 将第一步分解成的token标准化,所谓的标准化就是利用一些规则将token分好类(比如人名是一类、域名是一类等等)。标准化后的token我们称之为lexeme。在PG中是通过定义一个词典来做这个工作。PG里最简单的词典simple的标准化过程就是将大写字母转成小写字母。
3. 对文本打分,优化查找过程,比如对于待查找的词,文本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 http://google.com/123 12345 ');
to_tsvector
-----------------------------------------------------------------------
'google.com':4 'google.com/123':3 'xxx@taobao.com':1 'yyy@sina.com':2
(1 row)
~~~
在实际对表内的文本做全文搜索时,一般对目标列建立gin索引(也就是倒排索引,详情见[官方文档](http://www.postgresql.org/docs/9.4/static/textsearch-indexes.html)),这样可以加快查询效率,具体操作如下:
~~~
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 http://google.com 12345');
INSERT 0 1
sbtest=# INSERT INTO t1 VALUES('xxx yyy xxx@gmail.com yyy@sina.com http://google.com 12345');
INSERT 0 1
sbtest=# select * from t1;
c1
-------------------------------------------------------------
xxx yyy xxx@taobao.com yyy@sina.com http://google.com 12345
xxx yyy xxx@gmail.com yyy@sina.com http://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 http://google.com 12345
xxx yyy xxx@gmail.com yyy@sina.com http://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 http://google.com 12345
xxx yyy xxx@gmail.com yyy@sina.com http://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 http://google.com 12345
(1 row)
~~~
以上的操作都是针对英文,实际上对中文也是支持的,不过会稍微麻烦点,因为中文的token必须通过分词才能产生,所以需要先装分词的组件scws和zhparser,具体可以参考[这篇博文](http://blog.chinaunix.net/uid-20726500-id-4820580.html)。
## 结语
本文对PG的全文搜索做了一个入门级的介绍,方便用户快速上手,如果需要对全文搜索作更深入的研究,建议阅读[官方文档第12章](http://www.postgresql.org/docs/9.4/static/textsearch.html)。