PostgreSQL的全文检索(一)

最新推荐文章于 2024-04-20 21:44:04 发布

weixin_33755557

最新推荐文章于 2024-04-20 21:44:04 发布

阅读量213

点赞数

文章标签：数据库 java

原文链接：https://my.oschina.net/Kenyon/blog/80904

版权

为什么80%的码农都做不了架构师？>>>

在全文检索没有出来之前，普通的文件检索都是采用的like,~,或者ilike来匹配文档字段中内容，这种检索方法对小数据量的文本检索是OK的，但数据量大了就不行了。

普通检索的劣势：
1.语言不能完全支持,哪怕是英文,比如检索friend时不能检索出friends或者friendly
2.检索出的结果排序功能不好
3.缺少索引支持,查询速度慢,特别是两头加了两个%时根本就不走索引

PostgreSQL在8.3.x版本后开始支持全文检索。执行步骤，主要分三步走：
1.将文档分词(parsing documents into tokens)
2.转换分词规则(converting tokens into lexemes),如去掉复数后缀s/es,以及加入stop词,使之不会在分词中出现,如常用的'的'
3.按一定顺序查询的优化方式存储(storing preprocessed documents optimized for searching) tsvector存储,使用tsquery查询
注：这里tokes是原始的拆分分词,可能包含常用的无意义的词,lexemes是加工过的有价值的分词

一、全文检索的环境和例子：

postgres=# show default_text_search_config ;
default_text_search_config
----------------------------
pg_catalog.english
(1 row)

--全文检索配置
postgres=# \dF
               List of text search configurations
   Schema   |    Name    |              Description             
------------+------------+---------------------------------------
pg_catalog | danish     | configuration for danish language
pg_catalog | dutch      | configuration for dutch language
pg_catalog | english    | configuration for english language
pg_catalog | finnish    | configuration for finnish language
pg_catalog | french     | configuration for french language
pg_catalog | german     | configuration for german language
pg_catalog | hungarian  | configuration for hungarian language
pg_catalog | italian    | configuration for italian language
pg_catalog | norwegian  | configuration for norwegian language
pg_catalog | portuguese | configuration for portuguese language
pg_catalog | romanian   | configuration for romanian language
pg_catalog | russian    | configuration for russian language
pg_catalog | simple     | simple configuration
pg_catalog | spanish    | configuration for spanish language
pg_catalog | swedish    | configuration for swedish language
pg_catalog | turkish    | configuration for turkish language
(16 rows)

--全文检索查看russian具体配置
postgres=# \dF+ russian
Text search configuration "pg_catalog.russian"
Parser: "pg_catalog.default"
      Token      | Dictionaries
-----------------+--------------
asciihword      | english_stem
asciiword       | english_stem
email           | simple
file            | simple
float           | simple
host            | simple
hword           | russian_stem
hword_asciipart | english_stem
hword_numpart   | simple
hword_part      | russian_stem
int             | simple
numhword        | simple
numword         | simple
sfloat          | simple
uint            | simple
url             | simple
url_path        | simple
version         | simple
word            | russian_stem

--查看全文检索模板
postgres=# \dFt+
                                             List of text search templates
   Schema   |   Name    |      Init      |      Lexize      |                        Description                       
------------+-----------+----------------+------------------+-----------------------------------------------------------
pg_catalog | ispell    | dispell_init   | dispell_lexize   | ispell dictionary
pg_catalog | simple    | dsimple_init   | dsimple_lexize   | simple dictionary: just lower case and check for stopword
pg_catalog | snowball  | dsnowball_init | dsnowball_lexize | snowball stemmer
pg_catalog | synonym   | dsynonym_init  | dsynonym_lexize  | synonym dictionary: replace word by its synonym
pg_catalog | thesaurus | thesaurus_init | thesaurus_lexize | thesaurus dictionary: phrase by phrase substitution
(5 rows)

--全文检索字典
postgres=# \dFd+
                                                                  List of text search dictionaries
   Schema   |      Name       |      Template       |                   Init options                    |                        Description                       
------------+-----------------+---------------------+---------------------------------------------------+-----------------------------------------------------------
pg_catalog | danish_stem     | pg_catalog.snowball | language = 'danish', stopwords = 'danish'         | snowball stemmer for danish language
pg_catalog | dutch_stem      | pg_catalog.snowball | language = 'dutch', stopwords = 'dutch'           | snowball stemmer for dutch language
pg_catalog | english_stem    | pg_catalog.snowball | language = 'english', stopwords = 'english'       | snowball stemmer for english language
pg_catalog | finnish_stem    | pg_catalog.snowball | language = 'finnish', stopwords = 'finnish'       | snowball stemmer for finnish language
pg_catalog | french_stem     | pg_catalog.snowball | language = 'french', stopwords = 'french'         | snowball stemmer for french language
pg_catalog | german_stem     | pg_catalog.snowball | language = 'german', stopwords = 'german'         | snowball stemmer for german language
pg_catalog | hungarian_stem  | pg_catalog.snowball | language = 'hungarian', stopwords = 'hungarian'   | snowball stemmer for hungarian language
pg_catalog | italian_stem    | pg_catalog.snowball | language = 'italian', stopwords = 'italian'       | snowball stemmer for italian language
pg_catalog | norwegian_stem  | pg_catalog.snowball | language = 'norwegian', stopwords = 'norwegian'   | snowball stemmer for norwegian language
pg_catalog | portuguese_stem | pg_catalog.snowball | language = 'portuguese', stopwords = 'portuguese' | snowball stemmer for portuguese language
pg_catalog | romanian_stem   | pg_catalog.snowball | language = 'romanian'                             | snowball stemmer for romanian language
pg_catalog | russian_stem    | pg_catalog.snowball | language = 'russian', stopwords = 'russian'       | snowball stemmer for russian language
pg_catalog | simple          | pg_catalog.simple   |                                                   | simple dictionary: just lower case and check for stopword
pg_catalog | spanish_stem    | pg_catalog.snowball | language = 'spanish', stopwords = 'spanish'       | snowball stemmer for spanish language
pg_catalog | swedish_stem    | pg_catalog.snowball | language = 'swedish', stopwords = 'swedish'       | snowball stemmer for swedish language
pg_catalog | turkish_stem    | pg_catalog.snowball | language = 'turkish', stopwords = 'turkish'       | snowball stemmer for turkish language

--查看全文检索分析器，带加号可看详细配置,命令\dFp+
postgres=# \dFp
           List of text search parsers
   Schema   |     Name      |     Description     
------------+---------------+---------------------
 pg_catalog | chineseparser | 
 pg_catalog | default       | default word parser
(2 rows)

参数和配置文件的具体位置一般在$PGHOME/SHARE里面，stop词是存放在$PGHOME/share/tsearch_data下面的

二、实际例子,以英文例子为例

postgres=# SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery as search;
search 
--------
t
(1 row)

postgres=# SELECT 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector as search;
search 
--------
f
(1 row)

postgres=# SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat') as search;
search 
--------
t
(1 row)

postgres=# SELECT 'fat cats ate fat rats'::tsvector @@ to_tsquery('fat & rat') as search;
search 
--------
f
(1 row)

--默认的english分词,to_tevector区别于::tsvector是前者会加工分词,后者默认是加工好了
postgres=# SELECT to_tsvector('english','fat cats ate fat rats') @@ to_tsquery('english','fat & rat') as search;            
search
--------
t
(1 row)

--plainto_tsquery不却分分隔符,权重标签
postgres=# SELECT plainto_tsquery('english', 'The Fat & Rats:C');  
   plainto_tsquery
---------------------
'fat' & 'rat' & 'c'
(1 行记录)

--分词之间不会区分分隔符,每个分词之间插入&;,::tsquery和to_tsquery则必须要用到
postgres=# SELECT plainto_tsquery('english', 'The Fat Rats');      
plainto_tsquery
-----------------
'fat' & 'rat'
(1 行记录)


postgres=# SELECT 'The & Fat & Rats'::tsquery;
        tsquery
------------------------
'The' & 'Fat' & 'Rats'
(1 行记录)


postgres=# SELECT to_tsquery('english', 'The & Fat & Rats');
  to_tsquery
---------------
'fat' & 'rat'
(1 行记录)

三、对全文检索建立索引
有两种办法，一种是对当前文档字段加内置的转换函数，然后建索引，另一种办法是新增一个字段，然后更新原文档内容(需建立触发器和函数转换)上建立索引。推荐后一个。
方法1.原字段上建索引
CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', body));
CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector(config_name, body)); --组合索引,config_name是表pgweb的一个字段
CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', title || ' ' || body));

方法2.新增一列转换后建索引
ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector; --新建字段列类型是tsvector
UPDATE pgweb SET textsearchable_index_col = to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,'')); CREATE INDEX textsearch_idx ON pgweb USING gin(textsearchable_index_col);
SELECT title FROM pgweb WHERE textsearchable_index_col @@ to_tsquery('create & table') ORDER BY last_mod_date DESC LIMIT 10;

说明：
a.新增字段建的索引还需要创建一个触发器来实时更新新建字段内容
b.表达式索引的优点是简单，占用的空间少，缺点是每次执行需要调用to_tsvector函数来确保索引值关联
c.新建字段索引的有点是查询的速度快(无需每次去调用to_tsvevtor),尤其是使用Gist索引的时候。缺点是新建一个单独的列，消耗更多的存储空间。

四、内置实用函数示例
诸如to_tsvector,to_tsquery,tsvector_update_trigger,tsvector_update_trigger_column,ts_stat等等

--tsvector_update_trigger示例

CREATE TABLE messages (
title text,
body text,
tsv tsvector
);

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON messages FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);

INSERT INTO messages VALUES('title here','the body text is here');

postgres=# select * from messages;
   title    |         body          |            tsv             
------------+-----------------------+----------------------------
title here | the body text is here | 'bodi':4 'text':5 'titl':1
(1 row)

postgres=# SELECT title, body FROM messages WHERE tsv @@ to_tsquery('title & body');
   title    |         body         
------------+-----------------------
title here | the body text is here
(1 row)

--ts_stat的使用

--寻找文档中出现词汇的排序
-- nentry是总的出现次数
-- ndoc是文档中(tsvector)出现的次数，重复的记为1次
postgres=# select * from messages;
        title         |                             body                             |                                                tsv                                                
----------------------+--------------------------------------------------------------+----------------------------------------------------------------------------------------------------
title here           | the body text is here                                        | 'bodi':4 'text':5 'titl':1
kenyon               | a chinese boy                                                | 'boy':4 'chines':3 'kenyon':1
Andy Roddick retired | Andy Roddick retired,a former rank number 1 player in tennis | '1':11 'andi':1,4 'former':8 'number':10 'player':12 'rank':9 'retir':3,6 'roddick':2,5 'tenni':14
kenyon retired       | kenyon retired,a open-source lover,inserting in this area    | 'area':13 'insert':10 'kenyon':1,3 'lover':9 'open':7 'open-sourc':6 'retir':2,4 'sourc':8
Michael Jordan       | MJ is an American former professional basketball player      | 'american':6 'basketbal':9 'former':7 'jordan':2 'michael':1 'mj':3 'player':10 'profession':8
(5 rows)

postgres=# SELECT * FROM ts_stat('SELECT tsv FROM messages') ORDER BY nentry DESC, ndoc DESC, word LIMIT 10;
   word    | ndoc | nentry
-----------+------+--------
retir     |    2 |      4
kenyon    |    2 |      3
former    |    2 |      2
player    |    2 |      2
andi      |    1 |      2
roddick   |    1 |      2
1         |    1 |      1
american  |    1 |      1
area      |    1 |      1
basketbal |    1 |      1
(10 rows)

五、全文检索的限制
1.The length of each lexeme must be less than 2K bytes
2.The length of a tsvector (lexemes + positions) must be less than 1 megabyte
3.The number of lexemes must be less than 264
4.Position values in tsvector must be greater than 0 and no more than 16,383 No more than 256 positions per lexeme 5.The number of nodes (lexemes + operators) in a tsquery must be less than 32,768

六、总结：
以上是PostgreSQL内置的全文检索的环境和实际使用例子，目前对中文的全文检索并不支持，但已经有比较好的第三方工具结合使用，下一篇继续PostgreSQL中文全文检索环境搭建和实际使用。

转载于:https://my.oschina.net/Kenyon/blog/80904