我們經(jīng)常在數(shù)據(jù)庫(kù)中使用 LIKE 操作符來完成對(duì)數(shù)據(jù)的模糊搜索，LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

如果需要查找客戶表中所有姓氏是“張”的數(shù)據(jù)，可以使用下面的 SQL 語(yǔ)句：

SELECT * FROM Customer WHERE Name LIKE '張%'

如果需要查找客戶表中所有手機(jī)尾號(hào)是“1234”的數(shù)據(jù)，可以使用下面的 SQL 語(yǔ)句：

SELECT * FROM Customer WHERE Phone LIKE '%123456'

如果需要查找客戶表中所有名字中包含“秀”的數(shù)據(jù)，可以使用下面的 SQL 語(yǔ)句：

SELECT * FROM Customer WHERE Name LIKE '%秀%'

以上三種分別對(duì)應(yīng)了：左前綴匹配、右后綴匹配和模糊查詢，并且對(duì)應(yīng)了不同的查詢優(yōu)化方式。

數(shù)據(jù)概覽

現(xiàn)在有一張名為 tbl_like 的數(shù)據(jù)表，表中包含了四大名著中的全部語(yǔ)句，數(shù)據(jù)條數(shù)上千萬：

左前綴匹配查詢優(yōu)化

如果要查詢所有以“孫悟空”開頭的句子，可以使用下面的 SQL 語(yǔ)句：

SELECT * FROM tbl_like WHERE txt LIKE '孫悟空%'

SQL Server 數(shù)據(jù)庫(kù)比較強(qiáng)大，耗時(shí)八百多毫秒，并不算快：

我們可以在 txt 列上建立索引，用于優(yōu)化該查詢：

CREATE INDEX tbl_like_txt_idx ON [tbl_like] ( [txt] )

應(yīng)用索引后，查詢速度大大加快，僅需 5 毫秒：

由此可知：對(duì)于左前綴匹配，我們可以通過增加索引的方式來加快查詢速度。

右后綴匹配查詢優(yōu)化

在右后綴匹配查詢中，上述索引對(duì)右后綴匹配并不生效。使用以下 SQL 語(yǔ)句查詢所有以“孫悟空”結(jié)尾的數(shù)據(jù)：

SELECT * FROM tbl_like WHERE txt LIKE '%孫悟空'

效率十分低下，耗時(shí)達(dá)到了 2.5秒：

我們可以采用“以空間換時(shí)間”的方式來解決右后綴匹配查詢時(shí)效率低下的問題。

簡(jiǎn)單來說，我們可以將字符串倒過來，讓右后綴匹配變成左前綴匹配。以“防著古海回來再抓孫悟空”為例，將其倒置之后的字符串是“空悟?qū)O抓再來回海古著防”。當(dāng)需要查找結(jié)尾為“孫悟空”的數(shù)據(jù)時(shí)，去查找以“空悟?qū)O”開頭的數(shù)據(jù)即可。

具體做法是：在該表中增加“txt_back”列，將“txt”列的值倒置后，填入“txt_back”列中，最后為 “txt_back”列增加索引。

ALTER TABLE tbl_like ADD txt_back nvarchar(1000);-- 增加數(shù)據(jù)列UPDATE tbl_like SET txt_back = reverse(txt); -- 填充 txt_back 的值CREATE INDEX tbl_like_txt_back_idx ON [tbl_like] ( [txt_back] );-- 為 txt_back 列增加索引

數(shù)據(jù)表調(diào)整之后，我們的 SQL 語(yǔ)句也需要調(diào)整：

SELECT * FROM tbl_like WHERE txt_back LIKE '空悟?qū)O%'

此番操作下來，執(zhí)行速度就非常迅速了：

由此可知：對(duì)于右后綴匹配，我們可以建立倒序字段將右后綴匹配變成左前綴匹配來加快查詢速度。

模糊查詢優(yōu)化

在查詢所有包含“悟空”的語(yǔ)句時(shí)，我們使用以下的 SQL 語(yǔ)句：

SELECT * FROM tbl_like WHERE txt LIKE '%悟空%'

該語(yǔ)句無法利用到索引，所以查詢非常慢，需要 2.7 秒：

遺憾的是，我們并沒有一個(gè)簡(jiǎn)單的辦法可以優(yōu)化這個(gè)查詢。但沒有簡(jiǎn)單的辦法，并不代表沒有辦法。解決辦法之一就是：分詞+倒排索引。

分詞就是將連續(xù)的字序列按照一定的規(guī)范重新組合成詞序列的過程。我們知道，在英文的行文中，單詞之間是以空格作為自然分界符的，而中文只是字、句和段能通過明顯的分界符來簡(jiǎn)單劃界，唯獨(dú)詞沒有一個(gè)形式上的分界符，雖然英文也同樣存在短語(yǔ)的劃分問題，不過在詞這一層上，中文比之英文要復(fù)雜得多、困難得多。

倒排索引源于實(shí)際應(yīng)用中需要根據(jù)屬性的值來查找記錄。這種索引表中的每一項(xiàng)都包括一個(gè)屬性值和具有該屬性值的各記錄的地址。由于不是由記錄來確定屬性值，而是由屬性值來確定記錄的位置，因而稱為倒排索引(inverted index)。帶有倒排索引的文件我們稱為倒排索引文件，簡(jiǎn)稱倒排文件(inverted file)。

以上兩段讓人摸不著頭腦的文字來自百度百科，你可以和我一樣選擇忽略他。

我們不需要特別高超的分詞技巧，因?yàn)闈h語(yǔ)的特性，我們只需“二元”分詞即可。

所謂二元分詞，即將一段話中的文字每?jī)蓚€(gè)字符作為一個(gè)詞來分詞。還是以“防著古海回來再抓孫悟空”這句話為例，進(jìn)行二元分詞之后，得到的結(jié)果是：防著、著古、古海，海回，回來，來再，再抓，抓孫，孫悟，悟空。使用 C# 簡(jiǎn)單實(shí)現(xiàn)一下：

public static List Cut(String str){ var list = new List(); var buffer = new Char[2]; for (int i = 0; i < str.Length - 1; i++) { buffer[0] = str[i]; buffer[1] = str[i + 1]; list.Add(new String(buffer)); } return list;}

測(cè)試一下結(jié)果：

我們需要一張數(shù)據(jù)表，把分詞后的詞條和原始數(shù)據(jù)對(duì)應(yīng)起來，為了獲得更好的效率，我們還用到了覆蓋索引：

CREATE TABLE tbl_like_word ( [id] int identity, [rid] int NOT NULL, [word] nchar(2) NOT NULL, PRIMARY KEY CLUSTERED ([id]));CREATE INDEX tbl_like_word_word_idx ON tbl_like_word(word,rid);-- 覆蓋索引(Covering index)

以上 SQL 語(yǔ)句創(chuàng)建了一張名為 ”tbl_like_word“的數(shù)據(jù)表，并為其 ”word“和“rid”列增加了聯(lián)合索引。這就是我們的倒排表，接下來就是為其填充數(shù)據(jù)。

我們需要先用 LINQPad 自帶的數(shù)據(jù)庫(kù)鏈接功能鏈接至數(shù)據(jù)庫(kù)，之后就可以在 LINQPad 中與數(shù)據(jù)庫(kù)交互了。首先按 Id 順序每 3000 條一批讀取 tbl_like 表中的數(shù)據(jù)，對(duì) txt 字段的值分詞后生成 tbl_like_word 所需的數(shù)據(jù)，之后將數(shù)據(jù)批量入庫(kù)。完整的 LINQPad 代碼如下：

void Main(){ var maxId = 0; const int limit = 3000; var wordList = new List(); while (true) { $"開始處理：{maxId} 之后 {limit} 條".Dump("Log"); //分批次讀取 var items = Tbl_likes .Where(i => i.Id > maxId) .OrderBy(i => i.Id) .Select(i => new { i.Id, i.Txt }) .Take(limit) .ToList(); if (items.Count == 0) { break; } //逐條生產(chǎn) foreach (var item in items) { maxId = item.Id; //單個(gè)字的數(shù)據(jù)跳過 if (item.Txt.Length < 2) { continue; } var words = Cut(item.Txt); wordList.AddRange(words.Select(str => new Tbl_like_word { Rid = item.Id, Word = str })); } } "處理完畢，開始入庫(kù)。".Dump("Log"); this.BulkInsert(wordList); SaveChanges(); "入庫(kù)完成".Dump("Log");}// Define other methods, classes and namespaces herepublic static List Cut(String str){ var list = new List(); var buffer = new Char[2]; for (int i = 0; i < str.Length - 1; i++) { buffer[0] = str[i]; buffer[1] = str[i + 1]; list.Add(new String(buffer)); } return list;}

以上 LINQPad 腳本使用 Entity Framework Core 連接到了數(shù)據(jù)庫(kù)，并引用了 NuGet 包“EFCore.BulkExtensions”來做數(shù)據(jù)批量插入。

之后，就可以把查詢安排上，先查詢倒排索引，然后關(guān)聯(lián)到主表：

SELECT TOP 10 * FROM tbl_like WHERE id IN (SELECT rid FROM tbl_like_word WHERE word IN ('悟空'))

查詢速度很快，僅需十幾毫秒：

因?yàn)槲覀儗⑺械恼Z(yǔ)句分成了二字符詞組，所以當(dāng)需要對(duì)單個(gè)字符模糊查詢時(shí)，直接使用 LIKE 是一個(gè)更加經(jīng)濟(jì)的方案。如果需要查詢的字符多于兩個(gè)時(shí)，就需要對(duì)查詢?cè)~進(jìn)行分詞。如需查詢“東土大唐”一詞，構(gòu)造出的查詢語(yǔ)句可能會(huì)是這樣：

SELECT TOP 10*FROM tbl_like WHERE id IN (SELECT rid FROM tbl_like_word WHERE word IN ('東土','土大','大唐'))

但是，該查詢并不符合我們的預(yù)期，因?yàn)槠鋵⒅话巴链蟆钡恼Z(yǔ)句也篩選了出來：

我們可以采取一些技巧來解決這個(gè)問題，比如先 GROUP 一下：

SELECT TOP 10 *FROM tbl_likeWHERE id IN ( SELECT rid FROM tbl_like_word WHERE word IN ( '東土', '土大', '大唐' ) GROUP BY rid HAVING COUNT ( DISTINCT ( word ) ) = 3 )

在上述 SQL 語(yǔ)句中，我們對(duì) rid 進(jìn)行了分組，并篩選出了不重復(fù)的詞組數(shù)量是三個(gè)(即我們的查詢?cè)~數(shù)量)的。于是，我們可以得到正確的結(jié)果：

由此可知：對(duì)于模糊查詢，我們可以通過分詞+倒排索引的方式優(yōu)化查詢速度。

后記

雖然在講述時(shí)使用的是 SQL Server 數(shù)據(jù)庫(kù)，但是以上優(yōu)化經(jīng)驗(yàn)對(duì)大部分關(guān)系型數(shù)據(jù)庫(kù)來說是通用的，比如 MySQL、Oracle 等。

如果你和筆者一樣在實(shí)際工作中使用 PostgreSQL 數(shù)據(jù)庫(kù)，那么在做倒排索引時(shí)可以直接使用數(shù)組類型并配置 GiN 索引，以獲得更好的開發(fā)和使用體驗(yàn)。需要注意的是，雖然 PostgreSQL 支持函數(shù)索引，但是如果對(duì)函數(shù)結(jié)果進(jìn)行 LIKE 篩選時(shí)，索引并不會(huì)命中。

對(duì)于 SQLite 這種小型數(shù)據(jù)庫(kù)，模糊搜索并不能使用到索引，所以左前綴搜索和右后綴搜索的優(yōu)化方式對(duì)其不生效。不過，一般我們不會(huì)使用 SQLite 去存儲(chǔ)大量的數(shù)據(jù)，盡管分詞+倒排索引的優(yōu)化方式也可以在 SQLite 中實(shí)現(xiàn)。

總結(jié)

以上是生活随笔為你收集整理的android 数据库模糊查询语句_单表千万行数据库：LIKE 搜索优化手记的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

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