因?yàn)槭茿ndroid項(xiàng)目，老系統(tǒng)中的全文檢索是采用sqlite自帶的fts4，然而后續(xù)由于地圖要素全部轉(zhuǎn)為線上，全文檢索也需要同步在線查詢，所以將整個(gè)全文檢索的功能遷移到pgsql中。目前這塊功能基本結(jié)束，這里來(lái)對(duì)兩種全文檢索方案做一個(gè)對(duì)比總結(jié)。
（一）sqlite-fts4
相比與fts5，fts4的好處是原生支持在android系統(tǒng)上，不需要額外進(jìn)行配置，對(duì)于我這種懶人廢柴來(lái)說(shuō)特別友好；并且fts5能夠拓展自定義分詞的優(yōu)勢(shì)在實(shí)際項(xiàng)目中用處不大。這里淺談一些sqlite中fts4的用法：
建表語(yǔ)句
CREATE VIRTUAL TABLE T_MyTable USING fts4(UID INTEGER,x REAL,y
REAL,content TEXT,fts_content TEXT,tokenize = 'unicode61');
創(chuàng)建一個(gè)表名為T_MyTable的虛擬表，sqlite會(huì)自動(dòng)創(chuàng)建若干個(gè)影子表，如下：
不需要指定主鍵，fts4會(huì)自動(dòng)生成一個(gè)id字段作為主鍵，tokenize指定的是分詞器，fts4原生自帶了一些，詳見(jiàn)Fts3/Fts4官方文檔，這里我使用的是unicode61，支持中文和特殊字符分詞，空格分割。
插入數(shù)據(jù)
（1）直接通過(guò)navicat導(dǎo)入，需要注意的是直接導(dǎo)入的是建表語(yǔ)句中創(chuàng)建的table，導(dǎo)入之后其他的影子表會(huì)自動(dòng)生成相關(guān)的索引之類的內(nèi)容，只要主表中完成數(shù)據(jù)導(dǎo)入了即可。
（2）使用insert語(yǔ)句插入。
INSERT INTO T_MyTable(UID,x,y,content,fts_content) VALUES(1,121.48672,34.5964231,'同福社區(qū)','同 福 社 區(qū)');
在本例子中，uid為數(shù)據(jù)自定義唯一標(biāo)識(shí)碼，xy為經(jīng)緯度，fts_content為外部提前處理好的單字分詞結(jié)果。使用單字分詞是考慮到外部query分詞效果不一定理想，所以直接拆分構(gòu)建單字索引（這是一種非常原始的分詞處理，建議不要學(xué)習(xí)）。
全文檢索
SELECT * FROM T_MyTable WHERE T_MyTable MATCH '同福社區(qū)'; -- Fast. Full-text query.
SELECT * FROM T_MyTable WHERE content MATCH '同福社區(qū)'; -- 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 = '同福社區(qū)'; -- Slow. Linear scan.
fts檢索的優(yōu)勢(shì)在于可以不需要指定檢索某一列，它提供了一種match方法可以直接檢索整張表中含有檢索詞的內(nèi)容，而且不需要額外手動(dòng)構(gòu)建索引，直接開(kāi)箱即用。
中文全文檢索的精度受限于分詞的準(zhǔn)確度，如果不進(jìn)行分詞處理，以同福社區(qū)為例，外部query輸入為“同?！睍r(shí)候，是無(wú)法檢索出相應(yīng)的結(jié)果，所以fts也提供了前綴查詢。
前綴查詢
在詞后面加入一個(gè)星號(hào)（*）即構(gòu)成以該詞為前綴的查詢，下列語(yǔ)句表示檢索以同為開(kāi)頭的所有結(jié)果。
SELECT * FROM T_MyTable WHERE T_MyTable MATCH '同福*';
但是經(jīng)過(guò)一些實(shí)驗(yàn)，該方法檢索速度會(huì)有一定程度的降低，加了*檢索，時(shí)間從0.002s延長(zhǎng)到了2.6s。（不知道我是否是個(gè)例，但是也確實(shí)遇到了這樣的問(wèn)題）
尤其是在千萬(wàn)數(shù)據(jù)量下，同時(shí)為了保證速度并解決“同福”一詞的檢索問(wèn)題，最后還是進(jìn)行單字分詞，外部輸入的query也進(jìn)行了單字空格分詞處理。好在match匹配符支持空格分割，代表邏輯與（AND，&），因此最后的輸入query查詢語(yǔ)句為：
SELECT * FROM T_MyTable WHERE T_MyTable MATCH '同 福';
數(shù)據(jù)更新
fts4的一些缺點(diǎn)在于，當(dāng)它構(gòu)建好了一張表之后，是無(wú)法新增列或者改變表結(jié)構(gòu)的，如果不需要修改表結(jié)構(gòu)，只需要更新某些字段，使用普通的update語(yǔ)句即可。
UPDATE T_MyTable SET content = '新值' WHERE UID = 2;
但是如果需要增加某一列，alert語(yǔ)句是不能使用的，只能導(dǎo)出表重新構(gòu)建新的虛擬表結(jié)構(gòu)，再重新導(dǎo)入數(shù)據(jù)。
如果批量執(zhí)行update之后數(shù)據(jù)庫(kù)體積變大，排除中文字段儲(chǔ)存大問(wèn)題的以外，可能還有數(shù)據(jù)庫(kù)內(nèi)存不釋放的原因。解決方法：sqlite操作全部完成后，執(zhí)行VACUUM命令。
（二）postgreSQL 全文檢索
pgsql原生全文檢索最大的一個(gè)問(wèn)題就是，不支持中文分詞。網(wǎng)上說(shuō)的很多安裝中文插件等方法由于不是官方原生支持，被我導(dǎo)一口否決。不過(guò)類比一下當(dāng)前項(xiàng)目中fts4的分詞方案，pgsql是否需要真的使用中文分詞器對(duì)項(xiàng)目實(shí)際的檢索差異不大（畢竟都單字索引了），因此只要保證pgsql原生方案能識(shí)別空格分詞即可，這就相當(dāng)于把中文手動(dòng)處理成英文格式，交由英文分詞器識(shí)別。
因此，pgsql的全文檢索的核心就兩個(gè)函數(shù)：to_tsvector()和to_tsquery()
先上個(gè)官網(wǎng)鏈接，再來(lái)說(shuō)說(shuō)我的使用過(guò)程。
建表語(yǔ)句
首先，先建一個(gè)全文檢索的表。表結(jié)構(gòu)如下：需要構(gòu)建全文檢索倒排索引的字段是fts_content，這里需要的字段類型需要為text，或者有看別人用jsonb儲(chǔ)存也可。 （不知道為什么，用varchar檢索就很慢）
to_tsvector()和to_tsquery()
第二步，了解一下兩個(gè)核心函數(shù)。to_tsvector是PostgreSQL內(nèi)置的一個(gè)分詞函數(shù)，它可以將一段文本按照某種分詞規(guī)則進(jìn)行分詞。例如執(zhí)行：
SELECT to_tsvector('english','糧 食 生 產(chǎn) 功 能 區(qū) 糧食生產(chǎn)功能區(qū) 功能區(qū)')
數(shù)字表示該字符在query中的位置，english表示采用的分詞器，英文分詞默認(rèn)按照空格或者符號(hào)進(jìn)行。結(jié)果為：
to_tsquery() 用于處理外部輸入的query，結(jié)合@@符號(hào)，例如需要檢索詞為功能區(qū)，那么對(duì)應(yīng)語(yǔ)句為：
SELECT * FROM "T_FTS" WHERE to_tsvector('english',fts_content) @@ to_tsquery('english','功能區(qū)');
構(gòu)建索引
第三步，構(gòu)建索引。區(qū)別于sqlite fts4，需要手動(dòng)建立GIN倒排索引，語(yǔ)句如下 ：
CREATE INDEX idx_gin_fts ON "T_FTS" USING gin(to_tsvector('english',fts_content))
一定要指定分詞器，同時(shí)索引是針對(duì)某一個(gè)或者多個(gè)字段而言，相對(duì)應(yīng)的檢索使用to_tsvector 也要帶上構(gòu)建索引使用的分詞器，否則索引會(huì)失效。
構(gòu)建索引也可以指定兩個(gè)列，中間用||分開(kāi)，但考慮到檢索語(yǔ)句過(guò)長(zhǎng)，實(shí)際中并沒(méi)有使用兩個(gè)列構(gòu)建索引，而是將一個(gè)列的內(nèi)容合并到另一個(gè)列中，同樣進(jìn)行單字分詞處理。
CREATE INDEX idx_gin_fts ON "T_FTS" USING gin(to_tsvector('english',fts_content｜｜c(diǎn)ontent))
附上索引刪除語(yǔ)句：
DROP INDEX idx_gin_fts;
查詢結(jié)果
第四步，來(lái)實(shí)驗(yàn)一下查詢結(jié)果：
使用EXPLAIN ANALYZE查看檢索方案，可以看出是使用到了索引。其中單字分詞需要用&進(jìn)行分割，類比于fts4中的空格。我的總數(shù)據(jù)集是三萬(wàn)多條，茶場(chǎng)的命中結(jié)果為62條，耗時(shí)0.032s，效率還行，但是當(dāng)我發(fā)現(xiàn)命中結(jié)果一多的時(shí)候，所耗時(shí)就會(huì)很慢：
例如查詢水田一詞，一萬(wàn)六的命中結(jié)果，執(zhí)行時(shí)間為12s，對(duì)于一個(gè)系統(tǒng)來(lái)說(shuō)是不能接受的，目前對(duì)于這個(gè)問(wèn)題的理解是：命中結(jié)果過(guò)多，返回時(shí)間太長(zhǎng)。
經(jīng)過(guò)多次實(shí)驗(yàn)，設(shè)置不同的limit值進(jìn)行限定，發(fā)現(xiàn)確實(shí)是存在檢索效率和檢索結(jié)果數(shù)量之間的一個(gè)時(shí)間相關(guān)性，既然要設(shè)定返回值，就不可避免對(duì)檢索結(jié)果進(jìn)行排序，pgsql也提供了一個(gè)全文檢索的評(píng)分排序函數(shù)。
檢索排序
第五步，相關(guān)性查詢：ts_rank_cd()：
Pgsql提供了兩個(gè)預(yù)定義的相關(guān)函數(shù)（ts_rank和rs_rank_cd），考慮了查詢?cè)~在文檔中出現(xiàn)的頻率，術(shù)語(yǔ)在文檔中的緊密程度，以及它們?cè)谖臋n中的部分的重要性。即為相關(guān)度最高的優(yōu)先返回。
具體用法為ts_rank_cd(textsearch, query) ，最終檢索語(yǔ)句如下，返回最符合檢索詞的前50條記錄：
SELECT *,ts_rank_cd(to_tsvector('english', fts_content), '茶&場(chǎng)') as score
FROM "T_FTS"
WHERE to_tsvector('english', fts_content)@@ to_tsquery('english','茶&場(chǎng)')
ORDER BY ts_rank_cd(to_tsvector('english', fts_content), '茶&場(chǎng)') DESC
limit 50
到這pg的全文檢索已經(jīng)基本完成了，最后的項(xiàng)目里也是使用如上的檢索語(yǔ)句，檢索效率也在0.03s左右。需要額外處理的是外部搜索框輸入的query，需要用&進(jìn)行分割。單字分詞函數(shù)如下：
// 只分漢字，不分?jǐn)?shù)字字母
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();
}
}
補(bǔ)充一個(gè)索引的統(tǒng)計(jì)函數(shù)：ts_stat()：
SELECT * FROM ts_stat('SELECT to_tsvector(fts_content) FROM "T_FTS"')
ORDER BY nentry DESC,ndoc DESC, word
LIMIT 100;
ts_stat()需要輸入檢索格式為ts_vector列，因此括號(hào)中的sql語(yǔ)句就是表示將全文檢索表轉(zhuǎn)為ts_vector格式，也可以不使用to_tsvector函數(shù)，直接指定一個(gè)格式為ts_vector也是一樣的。檢索的結(jié)果中word：詞的值。ndoc ：?jiǎn)卧~出現(xiàn)的文檔數(shù)。nentry ：?jiǎn)卧~出現(xiàn)的總數(shù)。
（三）總結(jié)
1、Sqlite-fts4屬于開(kāi)箱即用，不需要手動(dòng)構(gòu)建索引；pgsql需要手動(dòng)構(gòu)建索引，一般使用GIN倒排索引，而且索引對(duì)于全文檢索的效率非常重要；
2、Sqlite-fts4可以檢索整張表的所有字段，但是pgsql在檢索時(shí)候需要指定字段，并且需要采用符合索引的分詞器，否則索引會(huì)失效；
3、Sqlite-fts4檢索“和”采用空格，pgsql采用&符號(hào)；
4、Sqlite-fts4匹配采用match，pgsql采用@@符號(hào)；
5、二者檢索精度都與分詞粒度高度相關(guān)；為了避免外部分詞器分詞粒度與庫(kù)中分詞結(jié)果匹配不佳問(wèn)題，單字分詞是一個(gè)簡(jiǎn)單粗暴的解決方案；文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-446146.html
6、查詢效率和檢索詞命中數(shù)量多少有關(guān)，可以通過(guò)設(shè)定limit和評(píng)分排序解決此問(wèn)題；文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-446146.html
到了這里，關(guān)于【全文檢索】sqlite-fts4和pgsql的全文檢索對(duì)比的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
