当前位置:   article > 正文

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

sqlite全文检索

因为是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');
  • 1
  • 2

创建一个表名为T_MyTable的虚拟表,sqlite会自动创建若干个影子表,如下:
在这里插入图片描述
不需要指定主键,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,'同福社区','同 福 社 区');
  • 1

在本例子中,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.
  • 1
  • 2
  • 3
  • 4
  • 5

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

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

前缀查询

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

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

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

在这里插入图片描述

在这里插入图片描述

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

SELECT * FROM T_MyTable WHERE T_MyTable MATCH '同 福';
  • 1
数据更新

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

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

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

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

(二)postgreSQL 全文检索

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

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

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

建表语句

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

在这里插入图片描述

to_tsvector()和to_tsquery()

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

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

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

在这里插入图片描述
to_tsquery() 用于处理外部输入的query,结合@@符号,例如需要检索词为功能区,那么对应语句为:

SELECT * FROM "T_FTS" WHERE to_tsvector('english',fts_content) @@ to_tsquery('english','功能区');
  • 1
构建索引

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

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

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

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

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

附上索引删除语句:

  DROP INDEX idx_gin_fts;
  • 1
查询结果

第四步,来实验一下查询结果:

在这里插入图片描述

在这里插入图片描述

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

例如查询水田一词,一万六的命中结果,执行时间为12s,对于一个系统来说是不能接受的,目前对于这个问题的理解是:命中结果过多,返回时间太长。在这里插入图片描述

经过多次实验,设置不同的limit值进行限定,发现确实是存在检索效率和检索结果数量之间的一个时间相关性,既然要设定返回值,就不可避免对检索结果进行排序,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
  • 1
  • 2
  • 3
  • 4
  • 5

到这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();
        }
    }
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

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

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

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

在这里插入图片描述

(三)总结

1、Sqlite-fts4属于开箱即用,不需要手动构建索引;pgsql需要手动构建索引,一般使用GIN倒排索引,而且索引对于全文检索的效率非常重要;

2、Sqlite-fts4可以检索整张表的所有字段,但是pgsql在检索时候需要指定字段,并且需要采用符合索引的分词器,否则索引会失效;

3、Sqlite-fts4检索“和”采用空格,pgsql采用&符号;

4、Sqlite-fts4匹配采用match,pgsql采用@@符号;

5、二者检索精度都与分词粒度高度相关;为了避免外部分词器分词粒度与库中分词结果匹配不佳问题,单字分词是一个简单粗暴的解决方案;

6、查询效率和检索词命中数量多少有关,可以通过设定limit和评分排序解决此问题;

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/Gausst松鼠会/article/detail/492345
推荐阅读
相关标签
  

闽ICP备14008679号