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

2022 年 11 月 21 日
筆記
MySQL, SQL, 全文索引, 資料庫

我是風箏，公眾號「古時的風箏」，專註於 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%'

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 '%侵權責任%';

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

別慌，姿勢不太對。全文索引有專門對應的查詢關鍵字。使用 match和against配合查詢，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 及周邊生態。堅持原創乾貨輸出，你可選擇現在就關注我，或者看看歷史文章再關注也不遲。長按二維碼關注，跟我一起變優秀！

Tags: MySQL SQL 全文索引資料庫