原來用 MySQL 也可以做全文檢索

我是風箏,公眾號「古時的風箏」,專註於 Java技術 及周邊生態。
文章會收錄在 JavaNewBee 中,更有 Java 後端知識圖譜,從小白到大牛要走的路都在裡面。

有朋友聊到他們的系統中要接入全文檢索,這讓我想起了很久以前為一個很古老的項目添加搜索功能的事兒。

一提到全文檢索,我們首先就會想到搜索引擎。也就是用一個詞、一段文本搜索出匹配的內容。一般這種技術都有對應的實現方式,ES(ElasticSearch)就是專門干這個的,如果你們的業務中明確需要全文檢索,或者簡單一點說,需要根據關鍵詞搜索出匹配的內容,那就直接用 ES 就好了。

無論你怎麼調研,都不推薦使用 MySQL 實現這種需求,顯而易見,MySQL 作為關係型資料庫,本身就不適合做搜索這種需求。

但是,奈何,今天我們就要用 MySQL 來做這件事兒。

背景

有一個很古老的業務採集了大量的資訊,當然是合法採集了。系統用的人已經不多了,並且在平穩的運行,那就不要動它了就好了嘛。可偏偏為數不多的人非要加一個搜索功能,根據一個關鍵詞來搜索。

這項目直接沒接觸過,咱也不敢隨意改呀,通過和少有的還有了解這個系統的同事溝通,發現有一類角色本來就有搜索功能,只不過這功能基本沒法用,從來搜不出內容。現象就是點完搜索按鈕,後台介面就一直 pending,不用說了,那肯定是因為數據量太大了,或者某種很傻的原因,比如直接在大數據量、大段文本的欄位中使用了 like模糊查詢。

經過一番查看,發現這個準備要支援搜索的欄位是 text類型的, 欄位本身是不參與業務計算的,只是用來展示。而要搜索的內容還不止一個欄位,好幾個欄位,這些欄位的內容是什麼呢,就是一段描述內容,裡面有各種各樣的專業名詞,每一行記錄中這個欄位最大長度可能有幾十到上千個字不等。

這張表由於數據量較大,並且欄位很多,所以進行了分表,根據某個上層類型進行拆分,這樣分出來的表,大的有上百萬,小的有幾十萬。業務運算的時候,也是固定類型後,在這個類型下的分表中進行增刪改查。

一看程式碼,果然,一條查詢好幾個 like,在幾十萬數據量的表中like好幾個欄位,不慢才怪,能查出來就是奇蹟了。

於是勇敢的在資料庫中嘗試了一下一條查詢的完整 SQL,在10分鐘之後,還是果斷結束了任務,一條SQL執行10分鐘,就算用戶能接受,我們自己也接受不了,好不好。

分析並思考解決方案

有需求就要處理,這種搜索的需求很明顯就要用 ES 嘛,下載ES,準備本地搭建環境。

開玩笑的,加上 ES 不知道何年何年了,況且這麼老的項目,能少動就少動,能不碰就不碰。這個法則,每個程式設計師都應該掌握。

思考

如果用戶想要的不是通過任意關鍵字檢索,而是通過指定一些我們為他預設好的關鍵詞查詢,就類似於抽出一些標籤,可以按照標籤組合搜索,那可以將需要搜索的欄位中的內容拿出來分詞、歸類,抽取出相關的標籤。這又是分詞、又是分析的,想想也不比直接上 ES 簡單。

還好,用戶不想要這種的,就要不做限制,直接用關鍵詞搜索。

務實主義

目前的處境是這樣的:

1、不要做大的改動,因為項目老舊,並且不熟悉,用的人也不多了;

2、邏輯很明晰,就是模糊查詢,但是目前性能極低;

3、直接在 MySQL 層做優化,確實是有辦法的,具體效果只能試過之後才知道;

直接的優化手段其實也是非常簡單的,MySQL 5.6版本後,MyISAM 和InnoDB 引擎已經全部支援全文索引了。還好,目前使用的資料庫在5.6版本之後。

為了演示,我將最小的一張 296,560 表縮小了10倍變成了 2萬9千多條,沒有做任何處理,直接在一個最長的 text類型的欄位上做 like查詢,最後的查詢時間是 1秒左右,偶爾慢的時候能達到2、3秒。

select * from case_data where case_name like '%侵權責任%';

explain分析一下,發現是全表掃描。

這只是查詢了將近3萬條數據,並且只查詢了一個欄位,並且沒有其他邏輯,真實環境中的邏輯要複雜的多。

全文索引簡單原理

MySQL 5.6之後的版本支援對 char、varchar、text 類型的欄位創建全文索引。

當添加了全文索引之後,資料庫引擎就會對添加索引的列進行語法語義的分析,並對它進行分詞,之後對這些分出的短語進行索引,每個短語對應包含它的行的集合。

短語 包含的行的集合
合約 第1行、第5行、第10行、第n行
項目管理 第2行、第3行、第22行、第1999行、第n+1行
產品研發 第500行、第3899行、第8899行、第n+2行

這樣當我們搜索某個關鍵詞後,如果正好對應了某個短語,就可以直接命中包含它的行。

有幾個參數是控制全文索引的, ft(FullText) 開頭的。用下面的命令可以查看。

show variables like '%ft%'

image-20221116111933369

ft_boolean_syntax

表示布爾查詢時的可以用的符號,改變IN BOOLEAN MODE的查詢字元,一會兒下面會演示用法。

innodb_ft_min_token_size

對與 innodb 引擎,最短的索引字元串,默認值為84,修改後要重建索引

innodb_ft_max_token_size

對與 innodb 引擎,最長的索引字元串,默認值為3,修改後要重建索引

創建全文檢索

下面這兩種方式都可以對已經存在的表創建全文索引。

CREATE FULLTEXT INDEX <index_name> on tableName(欄位名);  

ALTER TABLE tableName ADD FULLTEXT <index_name>(欄位名);

當然,如果你不想用SQL語句創建,也可以直接使用客戶端工具創建。

比如我測試用的這個表叫做 case_data,要支援全文檢索的欄位叫做 case_name,使用下面的 SQL 創建索引,索引名稱為 inde_case_name

ALTER TABLE case_data ADD FULLTEXT index_case_name(`case_name`);

創建索引的過程比較緩慢,對於大數據量的表更慢,尤其是全文索引,這3萬條數據對這一個欄位創建索引的過程差不多10秒鐘左右,如果是線上正在使用的服務,創建這種耗時索引就要酌情考慮一下什麼時機創建比較合適了。

再次查詢測試性能

全文索引創建好之後,就可以測試一下效果如何了,執行一下,等著見證奇蹟。

select * from case_data where case_name like '%侵權責任%';

咦,怎麼不僅沒快,反而慢了一點兒。

別慌,姿勢不太對。全文索引有專門對應的查詢關鍵字。使用 matchagainst配合查詢,match 表示要匹配的列名稱,against 表示要查詢的關鍵詞。比如下面這樣:

select * from case_data where match(case_name) against('侵權責任');

確實是快了,通過分析可以看出已經開始走全文索引了,掃描的行數已經是常數行了。

但是,一頓操作猛如虎,一看結果啥都沒有啊。

因為全文檢索是有精度的,是按照分詞出來的關鍵詞進行完全匹配的,也就是說當前的分詞短語中並不存在侵權責任這個詞,但是可能存在人身侵權責任無故侵權責任人等短語。最簡單的辦法就是在查詢侵權責任這個短語時,也要命中人身侵權責任無故侵權責任人這兩個短語,又類似於模糊查詢了。

怎麼辦呢,這樣寫就可以了。

select * from case_data where match(case_name) against('*侵權責任*' in boolean mode);

這樣再次查詢,結果就出來了。為什麼會這樣呢,前面我們提到一個變數,叫做ft_boolean_syntax,這個變數中的符號就類似於正則表達式里支援的規則符號。

常見的匹配模式有下面這些:

空格:可選的,包含該詞的順序較高

"text":全詞匹配查找

text*:通配符查找,*只能放在後面

+text:必須包含,+只能放在詞前面

-text:必須不包含,不能單獨使用,如`+aaaa-cccc

>text:如果含有該詞,提高詞的相關性

<text:如果含有該詞,降低詞的相關性

():條件組,如aaaa+(bbbb cccc)表示必須包含 bbbb 或 cccc

本來就叫全文檢索了,結果又整個模糊查找,一點兒也不徹底呀,還有沒有別的辦法了。

有一個,在5.7版本開始就內置了中文分詞插件 ngram,我們將剛才創建的索引刪掉,然後重新用 ngram做分詞重新建立索引。

ALTER TABLE case_data ADD FULLTEXT index_case_name(`case_name`) WITH PARSER ngram;

等個十幾秒中,然後再執行第一次差不到數據的SQL。

select * from case_data where match(case_name) against('侵權責任');

再看查詢結果,已經有數據了。

性能提升

我的測試數據只有2萬多條,這種少量數據的情況下,性能是看不到提升的。並且還由於創建了索引,增大了存儲空間。

但是將數據量提升十倍,到二十多萬,會看到性能明顯提升了幾十倍。我在線上測試了 200萬的表,用全文索引的方式0.5秒內能出結果,用 like 的話,喝完一杯茶,發現還在跑著。

因為全文檢索本來就是適用於大數據量的場景,所以對於小樣本的數據量,直接用 like也查不到哪兒去。

對於大數據量的場景,如果不引入ES等全文檢索的中間件的情況下,用全文索引可以說是最快最划算的方式了。


如果覺得還不錯的話,給個推薦吧!

公眾號「古時的風箏」,Java 開發者,專註 Java 及周邊生態。堅持原創乾貨輸出,你可選擇現在就關注我,或者看看歷史文章再關注也不遲。長按二維碼關注,跟我一起變優秀!