[2016-08-23] 更新

这篇文章写的很粗糙，对于这方面当时主要更多是从尝试的角度来说的。我是一个很懒的人，本质是想，以分词这种事情来讲，数据库能解决就不用其他外部工具甚至代码解决，所以才研究点点东西写了这篇。但是实际上后来我做的一些工作中遇到了很多问题，我提一下：

1、postgresql的程序执行效率很低，导致文章最后的分词函数占用大量CPU。

2、postgresql插入速度很慢，占用了大量IO吞吐量，表现的是top命令的wa非常高。

3、postgresql 9.6之前不支持将函数声明为线程安全，所以下面的函数执行非常慢。

4、下面的简单方法在十万级数据，有一定意义，再多了肯定不行，必须要其他方法。

5、下面的中文分词函数还有很严重问题，只是简单抛砖引玉，最简单的是，至少要区分中文和英文（CJK or western），然后能保证分别分词，我后来也根据这个原则写了函数，没发出来。因为我后来觉得这个工作实际意义对我来说已经没有了，因为我后续想解决的问题是百万到千万级的数据存储，这个时候postgresql的缺点就非常明显了（现在我的电脑里elasticsearch占用了7GB内存还在持续增长……）

----------

PostgreSQL自然没有Elasticsearch/Lucene那么强大的全文检索能力，不过相对的，Elasticsearch也没有PostgreSQL所拥有的各种方便的关系处理能力。

本文主要是对于笔者进行PostgreSQL进行中文全文搜索的简单尝试，欢迎指教。

需要用到的函数：

to_tsvector

to_tsvector(text， text)

这个函数可以把一个文本转换为一个向量，其中保存单词和其出现的顺序，下面是官方的示例，可以看出其中保存的是英语单词及其位置，例如fat出现了两次，分别在2位置和11位置

SELECT to_tsvector('english', 'a fat  cat sat on a mat - it ate a fat rats');
                  to_tsvector
-----------------------------------------------------
 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4

其中第一个参数是语言，第二个参数是需要转换的文本，当然了，至少官方是不支持中文的。第一个参数也可以省略，默认使用english。

如果是中文的话，就会这样：

SELECT to_tsvector('simple', '我爱文因互联')
-----------------------------------------------------
'我爱文因互联':1

这显然是没意义的，当然我们可以自己给中文分词，例如使用jieba，将文字分词之后再插入，例如：

 SELECT to_tsvector('simple', '我爱 文因互联')
-----------------------------------------------------
'我爱':1 '文因互联':2

to_tsquery

to_tsquery(text)

这个函数作用是用来把文本转化为可查询的语句，例如：

SELECT to_tsquery('我爱&文因互联')

这样就是寻找文本既包含“我爱”，也包含“文因互联”的语句，&是AND与操作，|是OR或操作

如果想知道一个tsvector是否让tsquery成立，可以使用

@@

操作符，例如：

SELECT
to_tsvector('simple', '我爱 文因互联') @@ to_tsquery('我爱&文因互联')

这条语句会返回True

ts_rank

ts_rank(tsvector, tsquery)

这个函数可以计算tsvector和tsquery的近似程度，例如文本是“我爱文因互联”，那么我们肯定希望to_tsquery('我爱&文因互联')的搜索排名比to_tsquery('文因互联')要高，通过这个函数计算出rank之后，就可以排序了

SELECT
ts_rank(
    to_tsvector('simple', '我爱 文因互联'),
    to_tsquery('我爱&文因互联')
)
-----------------------------------------------------
0. 0991832
SELECT
ts_rank(
    to_tsvector('simple', '我爱 文因互联'),
    to_tsquery('文因互联')
)
-----------------------------------------------------
0.0607927

中文一定需要手动分词吗？

当然简单来说，其实只要把中文分为一个字一个字的就好了，我介绍一个提供bigram的写法，具体参考wiki[1]

bigram就是把字分为两两的组合，例如abcd分为ab，bc，cd，这和Elasticsearch的CJK Bigram Token Filter感觉差不多[2]

下面是三个自定义函数：

to_bigram (text)

用来把字符串转换为bigram的形式

CREATE OR REPLACE FUNCTION
to_bigram (input text)
RETURNS text[]
AS
$$
    DECLARE
        retVal text[];
        inputVal text[] := REGEXP_SPLIT_TO_ARRAY(input, '');
    BEGIN
        IF array_length(inputVal, 1) > 1 THEN
            FOR i IN 2 .. ARRAY_UPPER(inputVal, 1)
            LOOP
                retVal[i - 1] = inputVal[i - 1] || inputVal[i];
            END LOOP;
            RETURN retVal;
        ELSE
            RETURN inputVal;
        END IF;
    END;
$$
LANGUAGE plpgsql;

to_chinese_bigram(text)

用来进行中文判断，也就是把“我爱你memect” 分为“我爱爱你 memect”

也就是英文不进行bigram处理

CREATE OR REPLACE FUNCTION
to_chinese_bigram (input text)
RETURNS text[]
AS
$$
    DECLARE
        retVal text[];
        chineseVal text[];
        inputVal text[] := Array(select regexp_matches(input, '([\u4e00-\u9fa5]+)|([-a-zA-Z]+)', 'g'));
    BEGIN
        IF array_length(inputVal, 1) >  THEN
            FOR i IN 1 .. ARRAY_UPPER(inputVal, 1)
            LOOP
                IF inputVal[i][1] IS NULL THEN
                    IF inputVal[i][2] != '-' THEN
                        retVal = ARRAY_APPEND(retVal, inputVal[i][2]);
                    END IF;
                ELSE
                    chineseVal = REGEXP_SPLIT_TO_ARRAY(inputVal[i][1], '');
                    IF array_length(chineseVal, 1) > 1 THEN
                        FOR j IN 2 .. ARRAY_UPPER(chineseVal, 1)
                        LOOP
                            retVal = ARRAY_APPEND(retVal, chineseVal[j - 1] || chineseVal[j]);
                        END LOOP;
                    END IF;
                END IF;
            END LOOP;
            RETURN retVal;
        ELSE
            RETURN inputVal;
        END IF;
    END;
$$
LANGUAGE plpgsql;

to_bigram_tsvector (text)

用来把bigram的数组变为tsvector

CREATE OR REPLACE FUNCTION to_bigram_tsvector(input text)
RETURNS tsvector AS $$
    SELECT
    to_tsvector(
        'simple',
        array_to_string(
            to_chinese_bigram(
                lower(input)
            ), ' '::text
        )
    )
$$ LANGUAGE sql IMMUTABLE;

to_bigram_tsquery (text, text)

用来把bigram的数组变为tsquery，第二个参数是连接字符串，例如'&'或者'|'

CREATE OR REPLACE FUNCTION to_bigram_tsquery(input text, splitor text)
RETURNS tsquery AS $$
    SELECT
    to_tsquery(
        'simple',
        array_to_string(
            to_chinese_bigram(
                lower(input)
            ), splitor
        )
    )
$$ LANGUAGE sql IMMUTABLE;

测试结果：

SELECT
ts_rank(
	to_bigram_tsvector('我爱文因互联'),
	to_bigram_tsquery('文因互联', '&')
)
-----------------------------------------------------
0.26833

索引

最后为了提高查询速度，还需要建索引，PotsgreSQL提供了一个叫做pg_trgm的扩展：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

这个扩展引入了两个索引类型，一个是gin，一个是gist

这两个选择的不同从使用上主要是gist构建速度比较快，搜索速度比gin慢，而gin相反，搜索速度很快，构建速度非常慢，具体可以参考[3]

更多关于PostgreSQL全文搜索的内容参考官方文档[4]

[1]: https://en.wikipedia.org/wiki/Bigram

[2]: https://www.elastic.co/guide/en/elasticsearch/reference/current/analysis-cjk-bigram-tokenfilter.html

[3]: https://www.postgresql.org/docs/current/static/pgtrgm.html
[4]: https://www.postgresql.org/docs/9.5/static/textsearch-controls.html

ITPub博客

PostgreSQL关于中文搜索的简单尝试

本文主要是对于笔者进行PostgreSQL进行中文全文搜索的简单尝试，欢迎指教。

to_tsvector

to_tsquery

ts_rank

中文一定需要手动分词吗？

索引

最新文章