欢迎光临
我们一直在努力
Hi, 请登录     我要注册     找回密码
当前位置：小明编程 资讯 (应用 , App) 正文

[转]sqlite3全文检索

2018-05-23 分类：(应用 , App) / (算法 , math) 阅读(5130) 评论(0)

http://www.cxwloves.cc/cxw/blog/article/59f71dde-e2a4-11e6-b834-f4066974556c

http://boztrail.com/2016/07/16/full-text-search/

http://tiimor.cn/Android%E9%93%82%E9%87%91%E7%AF%87-SQLite%E5%85%A8%E6%96%87%E6%A3%80%E7%B4%A2/

https://blog.csdn.net/andanlan/article/details/54237493

在设计后台的时候，需要管理blog当中的静态图片，于是就有了这样的需求：

  • 根据图片名检索数据库，获取图片的使用情况

搜索了一番，找到了如下网页 http://www.sqlite.org/fts3.html 。 sqlite也能实现全文检索。官网介绍了fts3，fts4插件，需求的版本应分别高于3.5.0和3.7.4。而fts1，fts2因为年久失修不被推荐使用。据介绍，ft4显著快于ft4，但可能会多占用1~2%的磁盘空间。

如何使用？

优化

为了支持全文检索，fts会维护一个反向索引（inverted index）映射了分词及其在表中的位置。详细内容参考（http://www.sqlite.org/fts3.html#data_structures）。在查询的时候，使用”INSERT INTO <fts-table>(<fts-table>) VALUES(‘optimize’)”这样的语法来加快后续检索速度，但它是一个开销很大的操作。

在SELECT查询中，注意几点可以提高查询速度

  1. 使用MATCH取代=，MATCH会利用到内置的全文索引，速度更快
  2. 使用rowid查询，会直接使用整数主键索引，速度更快
  3. 除了以上两种情况，其他查询方式都会进行全表搜索，1.5GB大概需要30s完成

查询语法

fts4表提供很了很多full-text-expression帮助各种姿势的查询。

我的实现

结果如下：…img alt=”” src=”/static/upload/<b style=”color:red”;>20170124165204</b>.jpeg” style=”height:614px…

如果你使用最新的fts5搜索，它还多了一个highlight()函数，功能类似snippet()，参考： http://www.sqlite.org/fts5.html#the_highlight_function

错误no such module: fts4

在Python部署的时候遇到了no such module: fts4的错误，而我在使用SQLite Expert的时候并没有如上状况。

  1. windows 在http://www.sqlite.org/download.html 下载对应版本的sqlite.dll复制替换到python27/DLLs/文件夹
  2. linux 重新编译sqlite 加上-DSQLITE_ENABLE_FTS4 flag

注:示例基于iOS平台

随App里内容的增多，查找的复杂度也越来越高，用户操作就会显得很麻烦，简单举个例子，社交App的聊天记录随时间积累到一定程度的时候，想查看历史聊天记录，就得在App里慢慢翻历史消息到之前聊过的地方，查看成本非常高，这时候在App里做全文检索就显得很必要。

那全文检索的实现复杂度怎么样呢，其实只要熟悉SQLite的使用，再了解一下SQLite FTS的特性，就能很简单的添加此功能，下面就介绍一下自己的关于全文检索的理解，不足之处望指出。

FTS

首先简单介绍一下FTS，全称Full Text Search，也就是全文检索，是Sqlite自带的功能模块，基于虚表、分词器及文件实现的全文检索方案，检索结果的理想程度跟分词器有关，分词器越智能，检索结果越理想。SQLite的版本有多个，主要使用FTS3和FTS4，FTS5也发布了，但需要自己编译才能使用，详细的FTS文档参考这，下面几条主要先介绍FTS的相关概念及语法:

1.Tokenizer(分词器)

分词器是FTS的核心，没有分词器模块，FTS就没法工作，分词器主要是将输入文本进行拆分，以便SQLite进行Match，例如一段文本“全文检索”,可能被拆分为“全文、检索”，也可能本拆分为“全、文、检、索”，最终检索结果也完全取决于分词器的拆分。假设上面两种拆分分别为A和B，那全文检索的时候检索“文”和“全文”，“文”在A方案中是没法检索出文的，而B方案可以，“全文“在A、B方案中都能检索出来，这就是分词器的大概作用。
SQLite也提供了相关分词器，比如simple、icu、unicode61等，只有icu、unicode61支持中文，但unicode61按标点拆分，不可用，而icu是按字拆分的，可以用，只是检索结果会比较乱。

2.MATCH

MATCH是FTS的检索操作，MATCH会比LIKE快上很多倍，MATCH是全匹配，MATCH还可以执行MATCH * 操作，类似于LIKE 的后%操作，就是前缀匹配，下面会有使用实例，继续。

3.offsets

offset类似于SQLite里的count，是一个功能函数，获取匹配结果在文本中的位置偏移，最终会获取到匹配文本在数据表中的第几行、第几列、已经匹配文本的Range，详细参考官方文档。

4.snippet

snippet也类似于offsets，都是用于获取匹配文本的位置，方便查询结果的高亮处理，语法如下：snippet(table name, [, ], …, 1, 10) as text;参数分别是table name、关键字左括弧、关键字右括弧、省略符，生效列，关键字前后字符限制，例如在文本“xxxxxxxxxxxxxx位置xxxxxxxxxxxx”中Match“位置”，得到的结果为：“…xxxxxxxxxx[位置]xxxxxxxxxx…”。简单说就是将匹配到的文本括起来，这里用的[]括号，匹配位置距文本首尾过长都会以指定的…省略。

FMDB（FTS）

iOS平台的FMDB在最新版本中拓展了FTS3的支持，其拓展简单理解就是自定义分词器的接入，FMDB基于CFStringTransform实现了以词为单位做拆分的分词器，基本能满足检索需要。下面是FMDB加载自定义分词器的示例代码:

1
2
3
4
5
6
7
8
// 创建分词器
FMSimpleTokenizer *simpleTok = [[FMSimpleTokenizer alloc] initWithLocale:NULL];
// 安装分词器模块 默认为fmdb
[db installTokenizerModule];
// 注册具体的分词器
[FMDatabase registerTokenizer:simpleTok withKey:@”simple”];
// 使用
CREATE VRITUAL TABLE doc USING FTS3(subject,body,tokeniz=fmdb simple);

不使用FMDB的话也可以按官方文档实现分词器模块，并以SQL语句直接注册自定义分词器，参考如下FMDB代码:
注册自定义分词器

全文检索

首先创建FTS虚拟表，指定分词器为（fmdb simple）,分词器的注册如上文介绍，创建语法如下:

1
CREATE VRITUAL TABLE doc USING FTS3(subject,body,tokeniz=fmdb simple);

更新数据到FTS表:

1
INSERT INTO doc (subject,body) VALUES (‘tokenizer test’,‘这是关于SQLite分词器的测试’)；

群文检索语法:

1
2
3
4
5
6
7
8
9
10
11
— 普通检索
SELECT * FROM doc WHERE doc MATCH ‘测试’;
— MATCH * 检索
SELECT * FROM doc WHERE doc MATCH ‘测*’;
— MATCH 指定字段 检索
SELECT * FROM doc WHERE doc MATCH ‘body:测’;
SELECT * FROM doc WHERE doc.body MATCH ‘测*’;
— offsets 语法
SELECT subject,body,offsets(doc) as offset FROM doc WHERE doc MATCH ‘测试’;
— snippet
SELECT subject,body,snippet(doc,[,],…,2,10) FROM doc WHERE doc MATCH ‘测试’;

高亮显示:有上面offsets和snippet两种方式获取关键字range(一般使用snippet)，文本高亮的显示就很简单了。

在检索大量数据时，普通的数据检索查询条件处理不当时，会检索全部的记录数，导致耗费一定的时间，除了优化查询语句、分页检索等方式提高效率外，全文检索可以让查询速度变得很快。

 

全文检索简介

全文检索的简称是FTS(full text search), 在sqlite里面的话，我们有FTS3和FTS4可以使用。FTS其实就是创建一张虚拟表以供查询；它的一个很重要的作用就是可以让查询速度变得很快。根据官方统计数据统计显示，下面是两种查询的速度比较：

  • 创建两种表
    1
    2
    CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT); /* FTS3 table */
    CREATE TABLE enrondata2(content TEXT); /* Ordinary table */
  • 查询速度比较
    1
    2
    SELECT count(*) FROM enrondata1 WHERE content MATCH ‘linux’; /* 0.03 seconds */
    SELECT count(*) FROM enrondata2 WHERE content LIKE ‘%linux%’; /* 22.5 seconds */

全文检索使用

创建虚表

对于已经存在数据记录的数据表，可以创建虚表，然后复制这些记录到虚表中，然后对虚表进行查询，而非查询原数据表

1
2
3
4
5
6
7
8
// 创建虚表vir_table，字段可以不带类型
CREATE VIRTUAL TABLE IF NOT EXISTS vir_table USING FTS3(column1, column2…);
// 拷贝原表记录
INSERT INTO vir_table SELECT * FROM table
// 删除虚表
DROP TABLE IF EXISTS vir_table

虚表增删改查

虚表的基本操作与普通表的操作是一样的

1
2
3
4
5
6
7
8
9
10
11
// 增
INSERT INTO vir_table (column1, column2…) VALUES (‘column1’, ‘column2’…);
// 删
DELETE FROM vir_table WHERE column1=’column1′;
// 改
UPDATE vir_table SET column1=’column’ WHERE column1=’column1′;
// 查
SELECT * FROM vir_table WHERE column1 like ‘column1%’ and column2 like ‘column2%’;

虽然可以使用普通表的查询，但是效率上非常糟糕（SQLite将做全表扫描），体现不出全文检索的好处来，虚表的检索使用的是 MATCH 运算符：

1
2
SELECT column1, column2 FROM vir_table WHERE column1 MATCH ‘column1’;
SELECT column1, column2 FROM vir_table WHERE column2 MATCH ‘column2’;

MATCH 右侧的表达式支持模糊查询（类似like）、之处指定列查询、支持AND/OR/NEAR/NOT等运算，模糊查询使用的是*，不再是%，下面是MATCH的几种写法：

1
2
3
4
SELECT column1, column2 FROM vir_table WHERE vir_table MATCH ‘col*’;
SELECT column1, column2 FROM vir_table WHERE vir_table MATCH ‘column1:col*’;
SELECT column1, column2 FROM vir_table WHERE vir_table MATCH ‘column1 AND column2’;
SELECT column1, column2 FROM vir_table WHERE vir_table MATCH ‘(column1 NEAR column3) OR (column2 AND column4)’;

注意：

  • 一条sql语句里只能出现一次MATCH判断，WHERE column1 MATCH 'column1' AND column2 MATCH 'column2'是不行的，可以改成WHERE vir_table MATCH 'column1:col* AND column2:col*'。
  • MATCH的模糊查询不能是这种形式：'*col*'，只能模糊右边：col*或者不使用通配符：col。
  • 全文检索会导致虚表增加，更新和删除记录变慢，需要考虑实际情况进行利弊权衡，对于查询操作量级较大，可以考虑全文检索。

触发器

对虚表的增删改查不会影响到原普通表的数据记录，为了保证虚表、原普通表数据一致，所以要对原普通表进行修改，在对虚表增删改的同时，对原普通表同时进行增删改。对于多个地方修改虚表和原普通表，同时操作两个表比较麻烦，因此可以考虑触发器，即在普通表中建立触发器 （虚表中不可以建触发器） ，当虚表操作时，触发器就会触发，自动对普通表进行增删改。

创建虚表的同时，创建触发器：

1
2
3
4
5
6
7
8
9
10
11
// 创建插入触发器
CREATE TRIGGER IF NOT EXISTS tri_insert AFTER INSERT ON vir_table BEGIN INSERT INTO table(column1, column2) VALUES(NEW.column1, NEW.column2); END
// 创建删除触发器
CREATE TRIGGER IF NOT EXISTS tri_delete AFTER DELETE ON vir_table BEGIN DELETE FROM table WHERE column1 = OLD.column1; END
// 创建修改触发器
CREATE TRIGGER IF NOT EXISTS tri_update AFTER UPDATE ON vir_table BEGIN UPDATE table SET column1 = NEW.column1 WHERE column2 = NEW.column2; END
// 触发器的删除
DROP TRIGGER IF EXISTS tri_insert

参考

 

前言

我们的APP部分功能为了满足用户离线使用搜索的场景，使用了内置SQLite数据库的方式，随着内容的日益丰富，数据库记录快速增多，导致搜索速度明显变慢，为了提升搜索速度，给我们的数据做了全文检索的支持，在3W+的数据下，搜索速度由原来的数秒提升至几十到几百毫秒（设备不同，搜索效率存在差别）。

一、基本概念

  1. 概述 全文检索是从文本或数据库中，不限定数据字段，自由地搜索出消息的技术。 运行全文检索任务的程序，一般称作搜索引擎，它可以将用户随意输入的文字从数据库中找到匹配的内容。

  2. 工作原理 它的工作原理是计算机索引程序通过扫描文章中的每一个词，对每一个词建立一个索引，指明该词在文章中出现的次数和位置，当用户查询时，检索程序就根据事先建立的索引进行查找，并将查找的结果反馈给用户的检索方式。

  3. 分类

    • 按字检索 指对于文章中的每一个字都建立索引，检索时将词分解为字的组合。

    • 按词检索 指对文章中的词，即语义单位建立索引，检索时按词检索。

注意： 在中文里面，每个汉字都有单独的含义，而英文中最小的语义单位是词，所以在英文搜索中按字搜索和按词搜索并没有明显的区分。

二、为什么使用SQLite全文检索

在SQLite对全文检索的官方介绍中的开篇，有下面一段内容：

For example, if each of the 517430 documents in the “Enron E-Mail Dataset” is inserted into both an FTS table and an ordinary SQLite table created using the following SQL script:

Then either of the two queries below may be executed to find the number of documents in the database that contain the word “linux” (351). Using one desktop PC hardware configuration, the query on the FTS3 table returns in approximately 0.03 seconds, versus 22.5 for querying the ordinary table.

Of course, the two queries above are not entirely equivalent. For example the LIKE query matches rows that contain terms such as “linuxophobe” or “EnterpriseLinux” (as it happens, the Enron E-Mail Dataset does not actually contain any such terms), whereas the MATCH query on the FTS3 table selects only those rows that contain “linux” as a discrete token. Both searches are case-insensitive. The FTS3 table consumes around 2006 MB on disk compared to just 1453 MB for the ordinary table. Using the same hardware configuration used to perform the SELECT queries above, the FTS3 table took just under 31 minutes to populate, versus 25 for the ordinary table.

在相同的设备环境下，包含 517430条 记录的SQLite数据库中，使用全文检索FTS3创建的数据库 MATCH 查询耗时0.03秒，没有使用全文检索的数据库，使用 LIKE 查询耗时22.5秒。

三、版本选择

SQLite提供的FTS（Full Text Search）模块，就是用来支持全文检索的。

FTS从1到5已经发展了5个版本，1和2已经废弃了。

通常情况下使用最新的版本一般性能上会有最好的优化，这样看FTS5似乎是最好的选择。但是由于FT5需要SQLite 3.9.0以上支持，iOS 9内置的SQLite版本还是3.8.8，而且FTS5暂时没有对中文支持比较好的分词器，所以简单起见可以考虑FTS4或者FTS3。FTS3/FTS4是比较常用的版本，性能上，50W条记录搜索耗时0.03秒，对移动端来说效率已经能满足用户的体验需求。

当然，如果想支持FTS5，可以不使用系统自带的SQLite版本，直接在Podfile下加入最新的支持FTS5的sqlite3版本即可：

四、分词器

FTS3 和 FTS4 提供四种系统分词器，除了系统分词器外，也可以自定义分词器，这里主要介绍系统分词器。

类型 是否支持中文 特性 注意
simple 否 连续的合法字符（unicode大于等于128）和数字组词 全都会转换为小写字母
porter 否 同上，支持生成原语义词（如一个语义的动词、名词、形容词会生成统一的原始词汇） 同上
icu 是 多语言，需要指明本地化语言，根据语义进行分词（如“北京欢迎你”，可以分为“北”、“北京”、“欢迎”、“欢迎你”等词汇） 可以自定义分词规则
unicode61 是 特殊字符分词，（unicode的空格+字符，如“:”、“-”等） 只能处理ASCII字符，需要SQLite 3.7.13及以上

五、使用步骤

1.创建 VIRTUAL TABLE

2.迁移数据

3.全文检索查询

六、MATCH部分语法

FTS中MATCH右侧的表达式支持 AND/OR/NEAR/NOT 等运算，注意，需要区分大小写，小写不不可以的。

  • AND

    AND 用来连接两个想要匹配的关键词，所查询到的结果必须同时包含 AND 连接的两个关键词。

  • OR

    与 AND 类似，它也连接两个想要匹配的关键词，不同的是，结果只要包含二者之一即可。

  • NOT

    NOT 也连接两个想要匹配的关键词，它匹配的结果包含前一个关键词、且不包含第二个关键词。

    注意：NOT不能单独使用，必须连接两个关键词。

  • NEAR

    NEAR 也连接两个想要匹配的关键词，它匹配的结果同时包含两个关键词，但是结果里面的这两个关键词中间默认必须不多余10个根据分词器分出的词。另外 NEAR 可以指定最小的间隔数量， NEAR/5 即指定间隔数最大为5。

七、Demo

  • simple分词

  • porter分词

  • unicode-61分词

  • icu分词(中文)

# 参考

SQLite FTS3 and FTS4 Extensions

SQLite全文检索(1)

赞(0)
未经允许不得转载：小明编程 » [转]sqlite3全文检索
分享到

相关推荐

评论 抢沙发

取消