【全文检索】sqlite-fts4和pgsql的全文检索对比-Toy模板网

这篇具有很好参考价值的文章主要介绍了【全文检索】sqlite-fts4和pgsql的全文检索对比。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

因为是Android项目，老系统中的全文检索是采用sqlite自带的fts4，然而后续由于地图要素全部转为线上，全文检索也需要同步在线查询，所以将整个全文检索的功能迁移到pgsql中。目前这块功能基本结束，这里来对两种全文检索方案做一个对比总结。

（一）sqlite-fts4

相比与fts5，fts4的好处是原生支持在android系统上，不需要额外进行配置，对于我这种懒人废柴来说特别友好；并且fts5能够拓展自定义分词的优势在实际项目中用处不大。这里浅谈一些sqlite中fts4的用法：

建表语句

CREATE VIRTUAL TABLE T_MyTable USING fts4(UID INTEGER,x REAL,y 
REAL,content TEXT,fts_content TEXT,tokenize = 'unicode61');

创建一个表名为T_MyTable的虚拟表，sqlite会自动创建若干个影子表，如下：
【全文检索】sqlite-fts4和pgsql的全文检索对比
不需要指定主键，fts4会自动生成一个id字段作为主键，tokenize指定的是分词器，fts4原生自带了一些，详见Fts3/Fts4官方文档，这里我使用的是unicode61，支持中文和特殊字符分词，空格分割。

插入数据

（1）直接通过navicat导入，需要注意的是直接导入的是建表语句中创建的table，导入之后其他的影子表会自动生成相关的索引之类的内容，只要主表中完成数据导入了即可。

（2）使用insert语句插入。

INSERT INTO T_MyTable(UID,x,y,content,fts_content) VALUES(1,121.48672,34.5964231,'同福社区','同 福 社 区');

在本例子中，uid为数据自定义唯一标识码，xy为经纬度，fts_content为外部提前处理好的单字分词结果。使用单字分词是考虑到外部query分词效果不一定理想，所以直接拆分构建单字索引（这是一种非常原始的分词处理，建议不要学习）。

全文检索

SELECT * FROM T_MyTable WHERE T_MyTable MATCH '同福社区';     -- Fast. Full-text query.
SELECT * FROM T_MyTable WHERE content MATCH '同福社区';       -- Fast. Full-text query.
SELECT * FROM T_MyTable WHERE rowid = 15;                 -- Fast. Rowid lookup.
SELECT * FROM T_MyTable WHERE rowid BETWEEN 15 AND 20;   -- Fast. Rowid lookup.
SELECT * FROM T_MyTable WHERE content = '同福社区';           -- Slow. Linear scan.

fts检索的优势在于可以不需要指定检索某一列，它提供了一种match方法可以直接检索整张表中含有检索词的内容，而且不需要额外手动构建索引，直接开箱即用。

中文全文检索的精度受限于分词的准确度，如果不进行分词处理，以同福社区为例，外部query输入为“同福”时候，是无法检索出相应的结果，所以fts也提供了前缀查询。

前缀查询

在词后面加入一个星号（*）即构成以该词为前缀的查询，下列语句表示检索以同为开头的所有结果。

SELECT * FROM T_MyTable WHERE T_MyTable MATCH '同福*';

但是经过一些实验，该方法检索速度会有一定程度的降低，加了*检索，时间从0.002s延长到了2.6s。~~（不知道我是否是个例，但是也确实遇到了这样的问题）~~

【全文检索】sqlite-fts4和pgsql的全文检索对比

尤其是在千万数据量下，同时为了保证速度并解决“同福”一词的检索问题，最后还是进行单字分词，外部输入的query也进行了单字空格分词处理。好在match匹配符支持空格分割，代表逻辑与（AND，&），因此最后的输入query查询语句为：

SELECT * FROM T_MyTable WHERE T_MyTable MATCH '同 福';

数据更新

fts4的一些缺点在于，当它构建好了一张表之后，是无法新增列或者改变表结构的，如果不需要修改表结构，只需要更新某些字段，使用普通的update语句即可。

UPDATE T_MyTable SET content = '新值' WHERE UID = 2;

但是如果需要增加某一列，alert语句是不能使用的，只能导出表重新构建新的虚拟表结构，再重新导入数据。

如果批量执行update之后数据库体积变大，排除中文字段储存大问题的以外，可能还有数据库内存不释放的原因。解决方法：sqlite操作全部完成后，执行VACUUM命令。

（二）postgreSQL 全文检索

pgsql原生全文检索最大的一个问题就是，不支持中文分词。网上说的很多安装中文插件等方法由于不是官方原生支持，被我导一口否决。不过类比一下当前项目中fts4的分词方案，pgsql是否需要真的使用中文分词器对项目实际的检索差异不大（毕竟都单字索引了），因此只要保证pgsql原生方案能识别空格分词即可，这就相当于把中文手动处理成英文格式，交由英文分词器识别。

因此，pgsql的全文检索的核心就两个函数：to_tsvector()和to_tsquery()

先上个官网链接，再来说说我的使用过程。

建表语句

首先，先建一个全文检索的表。表结构如下：需要构建全文检索倒排索引的字段是fts_content，这里需要的字段类型需要为text，或者有看别人用jsonb储存也可。 ~~（不知道为什么，用varchar检索就很慢）~~

【全文检索】sqlite-fts4和pgsql的全文检索对比

to_tsvector()和to_tsquery()

第二步，了解一下两个核心函数。to_tsvector是PostgreSQL内置的一个分词函数，它可以将一段文本按照某种分词规则进行分词。例如执行：

SELECT  to_tsvector('english','粮 食 生 产 功 能 区 粮食生产功能区 功能区')

数字表示该字符在query中的位置，english表示采用的分词器，英文分词默认按照空格或者符号进行。结果为：

【全文检索】sqlite-fts4和pgsql的全文检索对比
to_tsquery() 用于处理外部输入的query，结合@@符号，例如需要检索词为功能区，那么对应语句为：

SELECT * FROM "T_FTS" WHERE to_tsvector('english',fts_content) @@ to_tsquery('english','功能区');

构建索引

第三步，构建索引。区别于sqlite fts4，需要手动建立GIN倒排索引，语句如下：

CREATE INDEX idx_gin_fts ON "T_FTS" USING gin(to_tsvector('english',fts_content))

一定要指定分词器，同时索引是针对某一个或者多个字段而言，相对应的检索使用to_tsvector 也要带上构建索引使用的分词器，否则索引会失效。

构建索引也可以指定两个列，中间用||分开，但考虑到检索语句过长，实际中并没有使用两个列构建索引，而是将一个列的内容合并到另一个列中，同样进行单字分词处理。

CREATE INDEX idx_gin_fts ON "T_FTS" USING gin(to_tsvector('english',fts_content｜｜content))

附上索引删除语句：

  DROP INDEX idx_gin_fts;

查询结果

第四步，来实验一下查询结果：

【全文检索】sqlite-fts4和pgsql的全文检索对比

使用EXPLAIN ANALYZE查看检索方案，可以看出是使用到了索引。其中单字分词需要用&进行分割，类比于fts4中的空格。我的总数据集是三万多条，茶场的命中结果为62条，耗时0.032s，效率还行，但是当我发现命中结果一多的时候，所耗时就会很慢：

例如查询水田一词，一万六的命中结果，执行时间为12s，对于一个系统来说是不能接受的，目前对于这个问题的理解是：命中结果过多，返回时间太长。【全文检索】sqlite-fts4和pgsql的全文检索对比

经过多次实验，设置不同的limit值进行限定，发现确实是存在检索效率和检索结果数量之间的一个时间相关性，既然要设定返回值，就不可避免对检索结果进行排序，pgsql也提供了一个全文检索的评分排序函数。
【全文检索】sqlite-fts4和pgsql的全文检索对比

检索排序

第五步，相关性查询：ts_rank_cd()：

Pgsql提供了两个预定义的相关函数（ts_rank和rs_rank_cd），考虑了查询词在文档中出现的频率，术语在文档中的紧密程度，以及它们在文档中的部分的重要性。即为相关度最高的优先返回。

具体用法为ts_rank_cd(textsearch, query) ，最终检索语句如下，返回最符合检索词的前50条记录：

SELECT *,ts_rank_cd(to_tsvector('english', fts_content), '茶&场') as score
FROM "T_FTS"
WHERE to_tsvector('english', fts_content)@@ to_tsquery('english','茶&场') 
ORDER BY ts_rank_cd(to_tsvector('english', fts_content), '茶&场') DESC
limit 50

到这pg的全文检索已经基本完成了，最后的项目里也是使用如上的检索语句，检索效率也在0.03s左右。需要额外处理的是外部搜索框输入的query，需要用&进行分割。单字分词函数如下：

// 只分汉字，不分数字字母
private static String StringToCharList(String query) {
        StringBuilder charList = new StringBuilder();
        if(query == null){
            return "";
        }else {
            char[] letters = query.toCharArray();
            for(int i = 0; i < letters.length; i++){
                if(Character.isDigit(letters[i])||(letters[i] >= 'A' && letters[i] <= 'Z') || (letters[i] >= 'a' && letters[i] <= 'z')){
                    charList.append(letters[i]);
                }else{
                    charList.append(letters[i]);
                    charList.append("&");
                }
            }
            return charList.toString();
        }
    }

补充一个索引的统计函数：ts_stat()：

SELECT * FROM ts_stat('SELECT to_tsvector(fts_content) FROM "T_FTS"')
ORDER BY nentry DESC,ndoc DESC, word
LIMIT 100;

ts_stat()需要输入检索格式为ts_vector列，因此括号中的sql语句就是表示将全文检索表转为ts_vector格式，也可以不使用to_tsvector函数，直接指定一个格式为ts_vector也是一样的。检索的结果中word：词的值。ndoc ：单词出现的文档数。nentry ：单词出现的总数。

【全文检索】sqlite-fts4和pgsql的全文检索对比