原來用 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 型別的欄位創建全文索引，

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

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

有幾個引數是控制全文索引的， 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 及周邊生態，堅持原創干貨輸出，你可選擇現在就關注我，或者看看歷史文章再關注也不遲，長按二維碼關注，跟我一起變優秀！

標籤：MySQL

上一篇：如何在Sympy中將這些不等式中的系數從float轉換為int？

下一篇：mysql hint介紹