Oracle Berkeley DB SQL全文检索简介

(一) 背景

最新发布的Oracle Berkeley DB 11gR2 5.0引入了一个SQL用户期盼已久的新特性—— Oracle Berkeley DB SQL ,简称DBSQL。该版本兼容SQLite 3.6.22,继承并支持SQLite中的全文检索功能。SQLite全文检索功能先后支持FTS1,FTS2和FTS3,其中FTS1/FTS2与SQLite不再兼容,Berkeley DB(简称BDB)同SQLite一样,完美支持FTS3(若需要FTS1和FTS2的支持,请与我联系emily.fu[at]oracle.com)。

全文检索以文档的全部文本信息作为检索对象,为每一个词建立索引,当用户查询时,根据预先建立的索引进行查找,从而很快找到需要的内容。较普通检索,不论从查全率和查准率,还是查询速度上,全文检索在文本搜索上都有较大的优势。如SQLite文档中提到,在相同的文本数据上,SQLite的普通检索和全文检索的检索速度差距上百倍。

全文检索最初起源于文档检索,搜索引擎繁荣时期得以高速的发展,成就了google,baidu等脍炙人口的搜索引擎。同时,它在主流数据库中也得到了充分的体现。Oracle 9i中的全文检索技术Oracle text已经非常完善,并具有强大的文本检索能力和智能化的文本管理能力。又如SQL Server,My SQL,DB2,Sybase等数据库都有相应的全文检索工具,并得以广泛应用。如今,全文检索的应用范围已不仅仅限于文本数据,在非结构化数据如图像,声音,视频等等亦有各种尝试。除了上述数据库的全文检索工具以外,常用的全文检索工具还有开源的lucene等等。

如今BDB提供全文检索功能,BDB应用程序无须依赖外部全文检索工具,而专注提升全文检索的性能和结果满意度。全文检索的关键技术在于分词,BDB全文检索工具兼容自定义分词器,应用程序可定制分词器,从而提供高效而专业的检索。此外,排序算法很大程度影响结果集的呈现,BDB全文检索基于通用的TF-IDF算法,多维度分析结果的相关性,将结果集进行科学地排序,使得相关性越高的结果越早出现。

本文从SMS示例,引入FTS3的工作原理,在BDB中的编译,使用和常用文档。

(二) SMS示例

BDB 11gR2 5.0开发包中有一个FTS3的示例,模拟短信数据库,对短信进行全文检索。假设一条短信记录包括联系人,号码和短信内容三部分信息。通常情况下,短信内容长度在几十个字节到一百多个字节不等,若逐个匹配字符查找到相应关键字,搜索效率很低。若使用全文检索,联系人,号码和短信内容中的每一个词项都将建立其相应的倒排索引,即每个词项都成为关键字,大大加快了查询速度。

该FTS3示例源代码在<db>\sql\examples\c\ex_sql_fts3.c,短信数据来源于<db>\sql\examples\data\sms.csv。

短信数据库设计如下:

const sample_data sms_sample_data = {
"sms",
"\tDROP TABLE IF EXISTS sms;\n"
"\tCREATE VIRTUAL TABLE sms\n"
"\tUSING fts3(\n"
"\t\tnumber varchar(10) NOT NULL,\n"
"\t\tname   varchar(20),\n"
"\t\tcontent    TEXT\n"
"\t, tokenize=simple);",
"../sql/examples/data/sms.csv", 3};

部分短信数据:

1111111111,Aaron,i really like the way you sing but don t give up your day job
2222222222,Belle,income tax is a real cash cow for the government
…

该示例编译方法请参考本文第IV部分,其演示过程和部分效果如下:
1. 初始化示例,创建短信数据库,并载入原始数据。
2. rowid等于10的(也称为docid)短信记录查询。rowid是一个FTS3表中每一条记录的全局标识。

SELECT rowid, * FROM sms WHERE rowid=10;"

3. 用MATCH查询所有联系人是Aaron的短信记录。

SELECT * FROM sms WHERE name MATCH 'Aaron';

4. 查询联系人和短信内容中出现以Jack为前缀的单词的短信记录。

SELECT * FROM sms WHERE sms MATCH 'Jack*';

5. 查询短信内容含Demitrius grey的短信记录。

SELECT * FROM sms WHERE content match 'name:Demitrius grey';

6. 查询短信内容含eag* beav*词组的短信记录,其中*表示任意字符。

SELECT * FROM sms WHERE content MATCH '\"eag* beav*\"';

7. 查询联系人和短信内容中既出现Hedin又出现english的短信记录。

SELECT * FROM sms WHERE sms MATCH 'Hedin AND english';

8. 使用NEAR查询clear和air距离不超过2个词的短信记录。

SELECT * FROM sms WHERE sms MATCH 'clear NEAR/2 air'

9. 使用snippet功能,显示含Elymas的短信记录的片段。

SELECT snippet(sms) FROM sms WHERE sms MATCH 'Elymas';

下图是在android上运行该实例的截屏。

FTS3全文检索示例截图

示例更详细的信息,请参考<db>/sql/examples/c/README以及程序中的注释。

FTS3提供了一些丰富的特性,辅助用户得到更好的查询结果,后文将逐一介绍。

(三) FTS3工作原理

FTS3允许用户创建内置全文索引的特殊表(我们称为FTS3表),其中的全文索引有助于在数据库中高效地查找某一个或者几个特殊词汇。

FTS3表全文检索的工作原理如图所示:

FTS3工作原理

存储一条记录(文档数据)时,将该记录划分成各自独立的词项,为每个词项建立一个倒排索引。当查询时,遍历倒排索引,找到其相应的记录号,根据与查询条件的相关性等排序规则,返回结果集。

在DBSQL中,FTS3表的使用方法同普通表类似,可以进行插入、删除、修改等操作,但略有差异:

  • FTS3表为虚拟表

CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT);

  • 数据类型定义无效,自动转换成TEXT
  • 自定义内置分词器(如porter,icu等),也可定制分词器

CREATE VIRTUAL TABLE papers USING fts3(author, document, tokenize=porter);

  • 使用MATCH关键字进行查询

SELECT count(*) FROM enrondata1 WHERE content MATCH ‘linux’

  • 使用Snippet,Offset,Matchinfo提供更精准的结果
  • FTS3表可通过以下SQL进行优化

INSERT INTO docs(docs) VALUES(‘optimize’);

  • FTS3表不支持索引和触发器

(四) 启动FTS3全文检索功能

1) 在BDB 11gR2 5.0开发包,启动FTS3全文检索功能,需要在编译BDB时指定编译选项SQLITE_ENABLE_FTS3,编译后得到包含FTS3的BDB SQL库和相应的shell命令工具dbsql。

  • Linux

cd <db>/build_unix
../dist/configure CPPFLAGS=-DSQLITE_ENABLE_FTS3 –enable-sql <configure options>
make dbsql

由此,生成支持FTS3的BDB SQL库libdb_sql-5.0.a或者libdb_sql-5.0.so。

  • Windows

打开<db>/build_windows/Berkeley_DB.sln,右键单击其中的db_sql,选择properties ,打开C/C++,在Preprocessor Definitions里增加 SQLITE_ENABLE_FTS3,继而编译solution或者db_sql工程,即可得到支持FTS3的BDB SQL库libdb_sql50.dll和libdb_sql50.lib。

链接上述的BDB SQL库,即可在应用程序或者BDB SQL Shell(dbsql)中使用FTS3。

2) 编译得到BDB SQL库后,编译和运行第II部分提到的FTS3示例如下:

  • Linux

cd <db>/build_unix
make ex_sql_fts3

生成ex_sql_fts3,运行即可看到所有示例中所有查询结果。

  • Windows

打开<db>/build_windows/ Berkeley_DB_examples.sln,右键单击其中的ex_sql_fts3,编译得到ex_sql_fts3.exe,运行即可。

(五) DBSQL全文检索特性

下面将用一个新的例子,更详细的介绍DBSQL全文检索的特性。
首先创建表格和载入数据如下:

CREATE VIRTUAL TABLE docs USING fts3(title, body);
INSERT INTO docs(docid, title, body) VALUES(1, 'database','a database is a software system');
INSERT INTO docs(docid, title, body) VALUES(2,'BDB','Berkeley DB is called BDB for short');
INSERT INTO docs(docid, title, body) VALUES(3,'Berkeley DB','Berkeley DB is the industry-leading open source embedded database')

DBSQL提供的特性有:

1. 前缀查询 :前缀后加“*”作为查询条件,返回以该前缀开头的单词所在的文本,或者文本中的某个域。

SELECT * FROM docs WHERE docs MATCH 'Be*'; -- Select rows 2 and 3
SELECT * FROM docs WHERE title MATCH 'Be*'; -- Select rows 3

2. 词组查询 :返回含有某个词组的文本或者文本的某个域。

SELECT * FROM docs WHERE docs MATCH '"software system"'; -- Select rows 1
SELECT * FROM docs WHERE docs MATCH '"soft* sy*"'; -- Select rows 1
SELECT * FROM docs WHERE body MATCH '"soft* sy*"'; -- Select rows 1

3. 邻近查询 :返回距离在指定范围的多个单词所在的文本或者文本中的某个域。

SELECT * FROM docs WHERE docs MATCH 'Berkeley NEAR open'; -- Select rows 3
SELECT * FROM docs WHERE docs MATCH 'Berkeley NEAR/6 open'; -- Select rows 3
SELECT * FROM docs WHERE docs MATCH '"Berkeley DB" NEAR/6 open'; -- Select rows 3

4. 布尔逻辑 :支持NOT,OR,AND二进制集合操作。多个单词之间“-”用于表示NOT语义,隐式支持AND,即多个单词之间若没有操作符,默认使用AND。若添加编译选项SQLITE_ENABLE_FTS3_PARENTHESIS,则显式支持AND和NOT。

SELECT * FROM docs WHERE docs MATCH 'Berkeley-database'; -- Select rows 2
SELECT * FROM docs WHERE docs MATCH 'Berkeley database'; -- None
SELECT * FROM docs WHERE docs MATCH 'Berkeley OR database'; -- Select rows 1, 2 and 3

5. Snippet :格式化查询结果中的查询条件,可自定义格式,默认用<b></b>标识单词出现的位置,提取查询结果中的所有列,最多返回64个字符。

SELECT snippet(docs) FROM docs WHERE docs MATCH 'database’;
-- <b>database</b>
Berkeley DB is the industry-leading open source embedded <b>database</b>

6. Offset :返回某个单词或多个单词在文本中出现的位置集合,每个位置由四个数字表示,分别是单词在查询中位置,所在文本的列,位移和长度。

SELECT offsets(docs) FROM docs WHERE docs MATCH 'database';
-- 0 0 0 8 1 0 2 8
-- 1 0 57 8

7. Matchinfo :返回满足条件的文本的BLOB值,该BLOB由(2 + C * P * 3)个32位无符号整型表示,其中C为FTS3表被查询的列数,P为查询的单词数目。前两个字节分别表示查询条件的词组数量和被查询的列数,此后每三个整型数表示当前文本中查询单词或词组在列中出现的位置,这三个整型数分别是当前单词是否出现在当前行的当前列,当前单词所有行的该列出现的次数,当前单词在所有行出现的次数。

SELECT quote(matchinfo(docs)) FROM docs WHERE docs MATCH 'database';
-- X'0100000002000000010000000100000001000000010000000200000002000000'
-- X'0100000002000000000000000100000001000000010000000200000002000000'

8. 分词器 :可选择simple或者porter分词器,其中默认选择simple分词器,两者的差异在于porter匹配拥有相同词根的单词,例如frustrated和frustrate可以搜出相同的结果,而simple仅仅匹配单词本身。此外,可自定义一个分词器,以适应不同地区和语言的要求。

(六) 参考文档

我来评几句
登录后评论

已发表评论数()

相关站点

热门文章