iOS/Android SQLite全文检索——FTS (full text search)

顺心 等级 722 0 0

前言

我们的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:

CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT);     /* FTS3 table */
CREATE TABLE enrondata2(content TEXT);                        /* Ordinary table */ 

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.

SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux';  /* 0.03 seconds */
SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */ 

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或者FTS3FTS3/FTS4是比较常用的版本,性能上,50W条记录搜索耗时0.03秒,对移动端来说效率已经能满足用户的体验需求。

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

pod 'sqlite3/fts5' 

四、分词器

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

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

五、使用步骤

  1. 创建 VIRTUAL TABLE

默认分词
-- Create an FTS table named "pages" with three columns:
CREATE VIRTUAL TABLE pages USING fts4(title, keywords, body);
指定分词
-- Create an FTS table with a single column - "content" - that uses
-- the "porter" tokenizer.
CREATE VIRTUAL TABLE data USING fts4(tokenize= porter);

 2. 迁移数据

    ```
// 插入一条记录
INSERT INTO pages(docid, keywords, title, body) VALUES(53, ‘Home Page’ 'Home Page', 'SQLite is a software...');
// 更新一条记录
UPDATE pages SET title = 'Download SQLite' WHERE rowid = 54;
// 删除所有记录
DELETE FROM pages; 
  1. 全文检索查询

// 全表匹配
SELECT * FROM pages WHERE pages MATCH ‘sqlite’;
// 按列匹配
SELECT * FROM pages WHERE title MATCH 'sqlite';
SELECT * FROM pages WHERE keywords MATCH ‘sqlite';
SELECT * FROM pages WHERE body MATCH 'sqlite';

 #六、```MATCH```部分语法

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

- AND

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

// 搜索pages表body列中同时包含红和蓝的数据
SELECT * FROM pages WHERE body MATCH 'blue AND red';

 - OR

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

    ```
// 搜索pages表body列中同时包含白或绿的数据
SELECT * FROM pages WHERE body MATCH 'white OR green'; 
  • NOT

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

    // 搜索pages表body列中同时包含白,但是不包含绿的数据
    SELECT * FROM pages WHERE body MATCH 'white NOT green'; 

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

    // 错误写法:搜索pages表body列中所有不包含绿的数据
    SELECT * FROM pages WHERE body MATCH 'NOT green'; 
  • NEAR

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

    // 搜索t_guides_terms表所有列中同时包含“科”和“南”的记录,他们中间不多于一个分词结果。
    select * from t_guides_terms where t_guides_terms match '科 NEAR/1 南';; 

七、Demo

  • simple分词

    --simple tokenize 

create VIRTUAL TABLE t_pages USING fts4(title, body);
--insert
insert into t_pages(title, body) VALUES ('Hello world', 'Hello world! It is a good day!');
insert into t_pages(title, body) VALUES ('Hello world2', 'Hello world2! It is a good day too!');
insert into t_pages(title, body) VALUES ('Hello world-h', 'Hello world-j! It is a good day!');
insert into t_pages(title, body) VALUES ('How are you', 'The dog is interesting');
update t_pages set title = 'What is that' where title = 'Hello world2';
--all table columns match
--前文提到连续的合法字符被分词,helle所有字符都合法,它与下个词world有空格,空格不是合法字符,会以空格分开分词——“hello”和“world”
select * from t_pages where t_pages match 'hello';
--字符可以做模糊匹配的通配符,类似like的%
select * from t_pages where t_pages match 'hell
';
--hell不会被分词,所以没有结果
select * from t_pages where t_pages match 'hell';
select * from t_pages where t_pages match 'day*';
select * from t_pages where t_pages match 'day';
SELECT * FROM t_pages WHERE t_pages MATCH 'world';
SELECT * FROM t_pages WHERE t_pages MATCH 'world2';
--terms
SELECT * FROM t_pages WHERE t_pages MATCH 'hello world';
--这里有结果
SELECT * FROM t_pages WHERE t_pages MATCH 'world-h hello';
--这里没结果,因为使用“"”包括的字符不进行分词,所以没有结果
SELECT * FROM t_pages WHERE t_pages MATCH '"world-h hello"';
--column match
select * from t_pages where title match 'hello';
select * from t_pages where title match 'what';
--match vs like
--match可以任意交换关键字顺序,不影响搜索结果,like会没有结果
--macth可以简单的做全表匹配,而like需要指定对应的列
select * from t_pages where t_pages match 'that what';
select * from t_pages where title like '%what%that%';
select * from t_pages where title like '%that%what%';
--OR AND NEAR NOT
SELECT title, body FROM t_pages WHERE t_pages MATCH 'hello AND world';
--AND和直接搜两个关键词结果一致
SELECT title, body FROM t_pages WHERE t_pages MATCH 'hello world';
SELECT title, body FROM t_pages WHERE t_pages MATCH '(hello NEAR world) OR (program AND language)';
SELECT title, body FROM t_pages WHERE t_pages MATCH 'It NEAR is';
--包含hello但不包含world2的
SELECT title, body FROM t_pages WHERE t_pages MATCH 'hello NOT world2';
```

  • porter分词

    --porter tokenize 

create virtual table t_books using fts4(title, description, content, tokenize=porter);
insert into t_books(title, description, content) values ('Who can who up', 'No can no bibi', 'To be No1');
insert into t_books(title, description, content) values ('How are you', 'I''am fine', 'The dog is interesting');
--all table columns match
select * from t_books where t_books match 'who';
select * from t_books where t_books match 'bibi*';
select * from t_books where t_books match 'dog';
select * from t_books where t_books match 'is';
--column match
select * from t_books where title match 'how';
select * from t_books where content match 'how';
--simple vs porter
--无结果
select * from t_pages where t_pages match 'interested';
--有结果,porter分词会将interesting转换为原词interest做索引存储,搜索interest和interested都有结果
select * from t_books where t_books match 'interest';
select * from t_books where t_books match 'interested';
```

  • unicode-61分词

    --unicode61,不分词 

CREATE VIRTUAL TABLE t_guides USING fts4(title, content, tokenize=unicode61)
insert into t_guides(title, content) VALUES ('医学指南', '我是一篇指南');
--no terms
--有结果,因为匹配全表
select * from t_guides where t_guides match '医学指南';
--无结果
select * from t_guides where content match '医学指南';
select * from t_guides where content match '我是一篇指南';
--无结果,前文讲到,以空格个特殊字符分词,插入的记录没有空格和特殊字符,所以字段内容整个做分词
select * from t_guides where content match '我是一篇';
--模糊匹配有结果
select * from t_guides where content match '我是一篇*';
--unicode61,文字间加特殊字符,用以分词
CREATE VIRTUAL TABLE t_guides_terms USING fts4(title, content, tokenize=unicode61)
insert into t_guides_terms(title, content) VALUES ('医 学 指 南', '我 是 一 篇 指 南');
insert into t_guides_terms(title, content) VALUES ('骨|科|指|南', '第|二|篇|指|南');
insert into t_guides_terms(title, content) VALUES ('专|科|指|南', '专|二|篇|指|南');
--terms
--无结果,因为关键词没有空格或者特殊字符隔开,无法分词
select * from t_guides_terms where t_guides_terms match '医学指南';
--有结果,空格可以分词
select * from t_guides_terms where t_guides_terms match '医 学 指';
select * from t_guides_terms where content match '一 篇';
select * from t_guides_terms where content match '一 南';
select * from t_guides_terms where content match '"一 南"';
select * from t_guides_terms where content match '"一 篇"';
select * from t_guides_terms where content match '一';
--区分大小写,无结果
select * from t_guides_terms where t_guides_terms match '科 and 骨';
-- 有结果,包含科和骨
select * from t_guides_terms where t_guides_terms match '科 NOT 骨';
--有结果
select * from t_guides_terms where t_guides_terms match '科 NEAR/6 南';
--无结果,科和南中间隔了一个字符
select * from t_guides_terms where t_guides_terms match '科 NEAR/0 南';
```

  • icu分词(中文)
 --icu 

CREATE VIRTUAL TABLE t_school USING fts4(name, content, tokenize=icu zh_CN);
--drop table t_school;
insert into t_school(name, content) VALUES ('北京第一实验小学', '我是北京第一实验小学');
insert into t_school(name, content) VALUES ('河北京东', '我是河北京东');
--有结果,icu按语义分词,“北京”是一个有语义的词
select * from t_school where t_school match '北京';
--无结果(猜测是词库不够全,可以自定义分词来解决)
select * from t_school where t_school match '京东';
select * from t_school where t_school match '河';
--有结果,icu按语义分词,“第一实验小学”是一个有语义的词
select * from t_school where t_school match '第一实验小学';
select * from t_school where t_school match '实验小学';
select * from t_school where t_school match '北京第';
select * from t_school where t_school match '北京第一';
select * from t_school where t_school match '第一实';
```

本文转自 https://www.jianshu.com/p/32563e843cc0,如有侵权,请联系删除。

收藏
评论区

相关推荐

学完了C++语法之后该学什么??(数据库篇)
数据库与中间件 主要是MySQL、MongDB、Redis、Nginx等; 在大学的课程里,一般都会开设一门数据库的课程,不过这门数据库是没有针对某一种数据库语言的(例如 MySQL、SQlite)。不过我这里只讲 MySQL,因为最频繁。数据库不在多。 把MySQL学好,还是特别重要的,千万不能停留在会用的层面上,而是应该
14. Python 与数据库那点事儿,滚雪球学 Python
本篇文章将给大家介绍 Python 如何操作 SQLite 数据库,本文将是滚雪球学 Python 第一阶段的倒数第二篇文章。 <center<font color
iOS/Android SQLite全文检索——FTS (full text search)
前言 我们的APP部分功能为了满足用户离线使用搜索的场景,使用了内置SQLite数据库的方式,随着内容的日益丰富,数据库记录快速增多,导致搜索速度明显变慢,为了提升搜索速度,给我们的数据做了全文检索的支持,在3W的数据下,搜索速度由原来的数秒提升至几十到几百毫秒(设备不同,搜索效率存在差别)。 一、基本概念 1. 概述
Android:SQLiteOpenHelper类(SQLlite数据库操作)详细解析
Android:SQLiteOpenHelper类(SQLlite数据库操作)详细解析 前言 SQLite数据库操作在Android开发中非常常用 今天我将带大家全面了解关于SQLite数据库的操作(增、删、查、改) image(https:/
Python SQLite 基本操作和经验技巧(一)
1.插入单行数据 python import sqlite3 con sqlite3.connect('xxxx/test.db') cur con.cursor() 创建游标对象 cur.execute("create table Student(SNO char(10) UNIQUE primary key,Sname char(20),Sse
手把手教你进行Mysql5.x版本的安装及解决安装过程中的bug
前言 Hey,大家好,我是码农星期八,似乎还没有更新过关于Mysql的相关文章,同时也是因为工作需要,一起来学学叭。 数据库种类 常见的数据库有Mysql,Oracle,SQLite,Access,MS SQL Server。 这时候可能有人一看,what?数据库这么多,我要全部都学吗???,学这么多需要多久,是不是又是几个月。。。 我的回答是,
Python SQLite常见异常及解决办法
1.sqlite获取某一列数值中的最大值一直返回9问题及解决办法sqlite获取最大值一般使用max函数来获取,但是返回值一直是9。原因是因为该列字段值类型不是数值类型。 可以通过cast来改变字段值类型,使用以下语句解决: pythonselect max(cast(列名 as int)) from 表名 2.sqlite3.Opera
Python SQLite 基本操作和经验技巧(二)
1.sqlite3模块删除整个数据表test1.db是整个数据库文件,而不是一个表,它可以包括多个表pythonimport osos.unlink("test1.db")用上面的代码删除test1.db文件如要删除单个表people:pythoncon.execute('drop table people') 2.用pytho
Android 打造属于自己的数据库操作类。
1、概述 开发Android的同学都知道sdk已经为我们提供了一个SQLiteOpenHelper类来创建和管理SQLite数据库,通过写一个子类去继承它,就可以方便的创建、管理数据库。但是当我们需要去做增删改查的操作的时候,就得通过get
MySQL基础(非常全)
MySQL基础一、MySQL概述1、什么是数据库 ? 答:数据的仓库,如:在ATM的示例中我们创建了一个 db 目录,称其为数据库2、什么是 MySQL、Oracle、SQLite、Access、MS SQL Server等 ? 答:他们均是一个软件,都有两个主要的功能: a. 将数据保存到文件或内存 b. 接收特定的命令,然后对文件进行相应
干货|Sqlite数据库知识必知必会(上篇)
大家好,我是IT共享者,人称皮皮。前面几天给大家分享了MySQL数据库知识,没来得及看的小伙伴可以前往:,,关于数据库的安装可以参考:,今天皮皮继续给大家分享数据库知识,但是换了一个主角,它就是Sqlite。 前言前面我们学习了很多数据库,虽然它们功能非常丰富,但是占用空间比较大,使得我们的系统负荷变大,这对于我们新手小白来说不是一个理想的选择,于是乎,Sq
干货|Sqlite数据库知识必知必会(下篇)
大家好,我是IT共享者,人称皮皮。前面几天给大家分享了MySQL数据库知识,没来得及看的小伙伴可以前往:,,关于数据库的安装可以参考:。上篇文章主要给大家介绍了Sqlite数据库的安装、图形化管理系统、基本数据类型、基本关键字和数据库的相关操作,,今天皮皮紧接上篇文章,继续给大家分享数据库知识,一起来看看吧。 前言前面我们学习了很多数据库,虽然它们功能非常丰
干货|Sqlite数据库知识必知必会(上篇)
大家好,我是IT共享者,人称皮皮。前面几天给大家分享了MySQL数据库知识,没来得及看的小伙伴可以前往:,,关于数据库的安装可以参考:,今天皮皮继续给大家分享数据库知识,但是换了一个主角,它就是Sqlite。 前言前面我们学习了很多数据库,虽然它们功能非常丰富,但是占用空间比较大,使得我们的系统负荷变大,这对于我们新手小白来说不是一个理想的选择,于是乎
干货|Sqlite数据库知识必知必会(下篇)
大家好,我是IT共享者,人称皮皮。前面几天给大家分享了MySQL数据库知识,没来得及看的小伙伴可以前往:,,关于数据库的安装可以参考:。上篇文章主要给大家介绍了Sqlite数据库的安装、图形化管理系统、基本数据类型、基本关键字和数据库的相关操作,,今天皮皮紧接上篇文章,继续给大家分享数据库知识,一起来看看吧。 前言前面我们学习了很多数据库,虽然它们功能非常
如何快速给Flutter增加云同步功能
如何快速给Flutter增加云同步功能今天以一个漂亮的开源为例,来给大家提供一个思路,用最简单的方法将一个本地存储项目变为可以云同步的项目。这个App名叫《出入》,地址在https://github.com/Livinglist/Churu。为什么选这个项目呢?因为这个项目本身就是使用的本地sqlite进行数据存储,可以比较方便将它改成在线postgre数据