此文已由作者王攀授權(quán)網(wǎng)易云社區(qū)發(fā)布。

歡迎訪問網(wǎng)易云社區(qū)，了解更多網(wǎng)易技術(shù)產(chǎn)品運(yùn)營(yíng)經(jīng)驗(yàn)。

簡(jiǎn)介

對(duì)于今天的移動(dòng)、桌面客戶端應(yīng)用而言，離線全文檢索的需求已經(jīng)十分強(qiáng)烈，我們?nèi)粘Ｊ褂玫泥]件客戶端、云音樂、云筆記、易信等就是離線全文檢索的潛在用戶。

作為目前使用最為廣泛的嵌入式數(shù)據(jù)庫，SQLite3其實(shí)內(nèi)置了全文檢索的擴(kuò)展模塊——FTS。FTS分為FTS1、FTS2、FTS3、FTS4和FTS5幾個(gè)版本，其中FTS1和FTS2已經(jīng)被廢棄，而FTS3在2007年9月4日發(fā)布的SQLite 3.5.0中被引入，其增強(qiáng)版FTS4則第一次出現(xiàn)在2010年12月8日的SQLite 3.7.4中。由于FTS3與FTS4有著千絲萬縷的聯(lián)系，所以本文將兩種FTS引擎放在一起來介紹。FTS5則它們不兼容，所以筆者將以另外一個(gè)文章來單獨(dú)作介紹。

相比于普通表，FTS3/FTS4其實(shí)是兩種虛表。當(dāng)你創(chuàng)建一個(gè)名為t的FTS虛表的時(shí)候，你會(huì)發(fā)現(xiàn)數(shù)據(jù)庫中其實(shí)創(chuàng)建了若干個(gè)普通表用于存儲(chǔ)物理數(shù)據(jù)，它們被稱為影子表（shadow tables），分別命名為t_content、t_messageize、t_segdir、t_segments、t_stat等。

編譯

想讓SQLite支持FTS3/FTS4，在編譯SQLite的時(shí)候需要打開以下編譯開關(guān)

-DSQLITE_ENABLE_FTS3

注：Chromium、CEF和iOS7及以后的版本內(nèi)建的SQLite都默認(rèn)打開了此選項(xiàng)。 ?

如果想要讓FTS3/FTS4支持帶括號(hào)優(yōu)先級(jí)的高級(jí)查詢（見下文），那么需要同時(shí)打開以下開關(guān)：

-DSQLITE_ENABLE_FTS3_PARENTHESIS

注:Chromium、CEF內(nèi)建SQLite沒有打開該開關(guān)。 ?

如果想要讓FTS3/FTS4支持ICU分詞器，則需要再打開以下開關(guān)：

-DSQLITE_ENABLE_\ICU

注：Chromium、CEF內(nèi)建SQLite打開并實(shí)現(xiàn)了該開關(guān)；iOS自帶的沒有。

表操作

最簡(jiǎn)單地創(chuàng)建表的形式：

--?創(chuàng)建一個(gè)fts3表message，包含title和body兩列CREATE?VIRTUAL?TABLE?message?USING?fts3(title,?body);--?創(chuàng)建一個(gè)fts4表message，包含title和body兩列CREATE?VIRTUAL?TABLE?message?USING?fts4(title,?body);

需要注意的是如果在創(chuàng)建表的時(shí)候給某個(gè)列指定了類型，那么這些類型將被完全忽略。 我們還可以在建表的時(shí)候給表指定分詞器。例如：

CREATE?VIRTUAL?TABLE?message?USING?fts3(title,?body,?tokenize=porter);

以上創(chuàng)建了一個(gè)使用porter分詞器的表。此外FTS3/FTS4還支持simple、unicode61和外置的ICU等分詞器。對(duì)于中文，我們建議使用ICU分詞器。此外，FTS3/FTS4還支持自定義的分詞器，筆者將在之后介紹FTS5的文章中以FTS5為例介紹自定義分詞器。

創(chuàng)建FTS4表的時(shí)候我們還可以使用一些特殊選項(xiàng)：

compress=、uncompress= 用于支持壓縮和解壓縮

content= 用于創(chuàng)建無正文表（只有索引）和外部正文表（正文來自其他表而非虛表本身）等

matchinfo= 用于以FTS3方式存儲(chǔ)FTS4，忽略FTS4額外所需的信息，但是功能也會(huì)因此受限

notindexed= 指定某個(gè)列為非索引列

prefix= 額外為指定自己的前綴創(chuàng)建索引，這可以加快前綴查詢（見后文)

刪除FTS表非常簡(jiǎn)單，實(shí)用DROP語句即可。

增刪改

要向FTS表中插入數(shù)據(jù)類似普通表：

INSERT?INTO?message(title,?body)?VALUES('警告',?'10086提醒您：您移動(dòng)卡上余額不足10元');?? INSERT?INTO?message(docid,?title,?body)?VALUES(2,?'警告',?'10086提醒您：您移動(dòng)卡上余額不足5元');

注意到第二句中我們指定了一個(gè)叫docid的列，這是隱藏列rowid的一個(gè)別名，類似于普通表。

更新和刪除和普通表無異：

UPDATE?message?SET?title?=?'提示'?WHERE?rowid?=?1;DELETE?FROM?message?WHERE?rowid?=?1;

查詢

查詢操作是FTS表存在的最大意義。兩類查詢?cè)贔TS表上是比較高效的，它們是：

• 僅包含rowid的普通查詢

• 全文檢索

SELECT?*?FROM?message?WHERE?rowid?=?1??SELECT?*?FROM?message?WHERE?body?MATCH?'10086'

下面以ICU為分詞器針對(duì)全文檢索進(jìn)行進(jìn)一步介紹。

詞查詢

查詢可以針對(duì)整個(gè)文檔或者文檔的某些列來進(jìn)行精確的詞查詢：

--?查詢包含“移動(dòng)”關(guān)鍵字的文檔SELECT?*?FROM?message?WHERE?message?MATCH?'移動(dòng)'--?查詢消息體包含“移動(dòng)”關(guān)鍵字的文檔SELECT?*?FROM?message?WHERE?body?MATCH?'移動(dòng)'SELECT?*?FROM?message?WHERE?message?MATCH?'body:移動(dòng)'--?查詢消息體包含“移動(dòng)”且文檔中包含“您”關(guān)鍵字的文檔SELECT?*?FROM?message?WHERE?message?MATCH?'body:移動(dòng)?您'

注意到，用“列名:詞”的方式可以指定在某個(gè)列上查詢，而用空格隔開可以以“且”的方式連接多個(gè)條件。

在FTS4下，在詞前面加入^，表示該詞必須是某個(gè)列的第一個(gè)詞：

SELECT?*?FROM?message?WHERE?message?MATCH?'body:^移動(dòng)'

特別需要注意的是：英文詞必須使用小寫。因?yàn)楹笪闹泻芏嚓P(guān)鍵字需要用它們的大寫身份來被識(shí)別。

前綴查詢

我們?cè)谠~后面加入一個(gè)星號(hào)（*）即構(gòu)成以該詞為前綴的查詢：

--?下面的查詢包含“移動(dòng)”的文檔會(huì)被命中SELECT?*?FROM?message?WHERE?message?MATCH?'移*'

在FTS4下，^同樣適用于前綴查詢。

短語查詢

如果我們給定一個(gè)由詞和前綴組成的有序序列，去數(shù)據(jù)庫中匹配一個(gè)連續(xù)的有序詞序列，使得兩個(gè)序列中詞/前綴逐個(gè)依序匹配，就構(gòu)成了短語查詢。

--?下面的查詢將匹配以上兩條記錄SELECT?*?FROM?message?WHERE?message?MATCH?'"移?動(dòng)"';--?下面的查詢將無法匹配，因?yàn)樵闹小耙啤背霈F(xiàn)在“動(dòng)”之前而查詢中則相反SELECT?*?FROM?message?WHERE?message?MATCH?'"動(dòng)?移"';--?下面的查詢將無法匹配，因?yàn)椤耙啤薄ⅰ翱ā敝g隔了一個(gè)“動(dòng)”SELECT?*?FROM?message?WHERE?message?MATCH?'"移?卡"';

注意短語查詢必須將有序詞/前綴集用雙引號(hào)引起來，并且將有序集內(nèi)每個(gè)詞用空格隔開。

NEAR查詢

短語查詢要求詞之間必須連續(xù)重現(xiàn)，但是有時(shí)候我們?cè)试S他們就近出現(xiàn)，這個(gè)時(shí)候就需要使用NEAR查詢。

SELECT?*?FROM?message?WHERE?message?MATCH?'"移?NEAR?動(dòng)"';

默認(rèn)情況下兩個(gè)詞允許最大間隔10個(gè)詞，但是你也可以自定義：

SELECT?*?FROM?message?WHERE?message?MATCH?'"移?NEAR/6?動(dòng)"';

上例最多允許“移”、“動(dòng)”之間出現(xiàn)6個(gè)詞。

邏輯操作

FTS3、FTS4支持邏輯條件關(guān)鍵字（必須大寫）：

AND：邏輯與，取交集；默認(rèn)不加條件關(guān)鍵字的情況下，就是這種關(guān)系。

OR：邏輯或，取并集

NOT：邏輯非，取補(bǔ)集。可以使用 - 代替

--?以下兩個(gè)查詢是一致的SELECT?*?FROM?message?WHERE?message?MATCH?'移?AND?動(dòng)';SELECT?*?FROM?message?WHERE?message?MATCH?'移?動(dòng)';

優(yōu)先級(jí)方面，NOT高于AND，高于OR。FTS3/FTS4支持使用括號(hào)來改變的優(yōu)先級(jí)：

SELECT?*?FROM?message?WHERE?message?MATCH?'(移?OR?動(dòng))?AND?卡';

再次提醒：使用帶括號(hào)優(yōu)先級(jí)的查詢支持，需要打開 -DSQLITE_ENABLE_FTS3_PARENTHESIS 開關(guān)編譯SQLite

內(nèi)建函數(shù)

FTS3/FTS4支持三個(gè)非常有用的內(nèi)建函數(shù)：offsets、snippet、matchinfo。

offsets

offsets函數(shù)返回所有匹配項(xiàng)的偏移信息。總體上來說，offsets針對(duì)每個(gè)匹配項(xiàng)將返回一個(gè)四元組，一句話概括就是：詞號(hào)為term的詞在表中第column列的offset字節(jié)處命中了連續(xù)的size字節(jié)的目標(biāo)。offsets返回所有這樣的四元組的文本形式，例如若：

SELECT?offsets(tb1)?FROM?tb1?WHERE?tb1?MATCH?'term1?term2';

返回

"0 1 3 4 1 0 0 6"

那么就表示有兩處被命中：

• 第1列的3字節(jié)處被2號(hào)詞命中，命中長(zhǎng)度為4

• 第2列的0字節(jié)處被1號(hào)詞命中，命中長(zhǎng)度為6

注意：column、term、offset編號(hào)都從0開始的。

snippet

此函數(shù)用于返回最佳命中目標(biāo)及其周圍的切片。例如，SQLite官網(wǎng)的搜索功能的高亮顯示就是用此函數(shù)實(shí)現(xiàn)的。

這個(gè)函數(shù)支持可變參數(shù)，我們可以給它傳1至6個(gè)參數(shù)。6個(gè)參數(shù)按照從0開始編號(hào)說明如下： 0：必須使用隱藏列，也就是要查詢的虛表名，比如上面的message。
1：返回值中被命中目標(biāo)開始處的標(biāo)記文本，默認(rèn)為“”
2：返回值中被命中目標(biāo)結(jié)束處的標(biāo)記文本，默認(rèn)為“”
3：被省略文本的標(biāo)識(shí)，比如“...”
4：強(qiáng)制指定從哪個(gè)列提取切片文本，默認(rèn)為-1，表示可從任意列提取
5：此值的絕對(duì)值表示返回值中大致包含多少個(gè)單詞，最大可取64，默認(rèn)-15

SELECT?snippet(message,?'[?']',?'...')?FROM?message?WHERE?message?MATCH?'"移*?余*"'

matchinfo

這是一個(gè)更加高效的函數(shù)，因?yàn)樗旧淼姆祷刂挡恍枰獙⒄麄€(gè)行全部從磁盤調(diào)入內(nèi)存而只需要查詢索引數(shù)據(jù)。此外，這個(gè)函數(shù)也提供了足夠的用于運(yùn)行常用結(jié)果評(píng)價(jià)算法的信息。

限于篇幅，本函數(shù)不作展開詳述，大家可以參考最后給出的鏈接查閱。

常用特殊命令

FTS3/FTS4支持一些特殊命令來維護(hù)索引等。下面是我們會(huì)常用的兩條：

-- 優(yōu)化表，本質(zhì)是將所有獨(dú)立的小索引樹合并成一整棵B樹

INSERT INTO xyz(xyz) VALUES('optimize');

-- 重建索引

INSERT INTO xyz(xyz) VALUES('rebuild');

FTS3與FTS4的區(qū)別

FTS3和FTS4是比較相似的，它們共享了很多底層技術(shù)，也共享了相同的接口。它們的不同點(diǎn)在于：

FTS4包含了查詢優(yōu)化，可以顯著提升高頻詞的檢索性能

FTS4下matchinfo()內(nèi)建函數(shù)得到更多的可選信息

FTS4為了實(shí)現(xiàn)1中提到的優(yōu)點(diǎn)，需要額外的存儲(chǔ)空間，不過一般情況下這部分空間開銷比較小

FTS4支持hooks來實(shí)現(xiàn)壓縮存儲(chǔ)以減小磁盤開銷

優(yōu)化建議

控制范圍：我們真的必要返回所有結(jié)果么？是否可以考慮按區(qū)間分批返回呢？

考慮matchinfo：有些時(shí)候我們只需要返回部分查詢結(jié)果的偏移量或者片段，這個(gè)時(shí)候我們可以考慮先用帶matchinfo的子查詢確定我們需要返回偏移量或片段的rowid集，然后再對(duì)這個(gè)集合內(nèi)的記錄進(jìn)行深度的offsets或者snippet。因?yàn)閛ffsets和snippet需要從磁盤調(diào)取整行數(shù)據(jù)，并作一定的字符串加工，效率較低。這方面大家可以讀下SQLite源碼。

考慮外部正文：如果你需要索引的內(nèi)容完全可以從一個(gè)必要的外部表中獲取，不妨考慮下外部正文。這樣就可以有效減小存儲(chǔ)正文所需要的磁盤和時(shí)間開銷。遺憾的是，通過提取iOS版QQ郵箱某個(gè)版本的數(shù)據(jù)文件，我們發(fā)現(xiàn)QQ郵箱這方面似乎做得不太好。

我們的困擾

FTS3/FTS4是好東西，但在實(shí)際項(xiàng)目中我們發(fā)現(xiàn)它們無法完全滿足我們的需求：

查詢語法過于模糊，容易產(chǎn)生歧義，搜索結(jié)果不可控
內(nèi)建函數(shù)可定制性不夠
offsets返回值為字符串，多次realloc和字符串轉(zhuǎn)換，效率太低
一定情況下會(huì)更費(fèi)內(nèi)存
過時(shí)，采用FTS5后未來需要升級(jí)數(shù)據(jù)庫

于是我們找到了替代它們的神器——FTS5！結(jié)合我們自定義的分詞器（代號(hào)mmfts5），需求終于被完全滿足了。

參考

http://www.sqlite.org/fts3.html

網(wǎng)易云免費(fèi)體驗(yàn)館，0成本體驗(yàn)20+款云產(chǎn)品！

更多網(wǎng)易技術(shù)、產(chǎn)品、運(yùn)營(yíng)經(jīng)驗(yàn)分享請(qǐng)點(diǎn)擊。

相關(guān)文章：
【推薦】?一個(gè)內(nèi)部增長(zhǎng)案例的分享
【推薦】?[5.19 線下活動(dòng)]Docker Meetup杭州站—擁抱Kubernetes，容器深度實(shí)踐
【推薦】?6本互聯(lián)網(wǎng)技術(shù)暢銷書免費(fèi)送（數(shù)據(jù)分析、深度學(xué)習(xí)、編程語言）！

轉(zhuǎn)載于:https://www.cnblogs.com/zyfd/p/9803303.html

總結(jié)

以上是生活随笔為你收集整理的SQLite FTS3/FTS4与一些使用心得的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。