簡單聊聊 MySQL 全文索引

最近踩 MYSQL 中文本搜索的坑踩了挺多，來寫個具體的文章總結下 MYSQL 中文本搜索的一些知識點吧

模糊搜索#

在我們使用 MYSQL 的過程中，總會有一些模糊搜索的需求，比如我們現在有這樣一張表

create table if not exists `user`
(
    `id`         bigint(20)   not null auto_increment,
    `name`       varchar(255) not null,
    `age`        int          not null,
    `update_time` timestamp    not null,
    `create_time` timestamp    not null,
    index (`name`),
    primary key (`id`)
) engine = InnoDB
  charset = 'utf8mb4';

現在我們需要對於 name 做一些模糊匹配的需求，比如我們需要去匹配 name 中包含 草 字，於是大家仔細一想，OK，寫出了如下的 SQL

select * from user where name like '%草%'

好了，當你興高采烈的將這段代碼上線後，你發現，線上炸了，為啥？因為 MYSQL 的坑. MYSQL 的 like 查詢存在這樣兩個限制

只有前綴匹配 ' 草 %' 和後綴匹配 '% 草 ' 才會走索引，而任意匹配則不會
當無法走索引的時候，MYSQL 會遍歷全表來查詢數據

當你一個表的數據規模很大的時候，那麼暴力掃表必然會帶來極大的開銷

但是我們實際工作中這樣的任意匹配的需求肯定很多，那麼我們應該怎麼做？或許可以嘗試下全文搜索

全文搜索#

簡單聊聊全文搜索#

全文搜索大家已經不太陌生了，簡而言之用一種不太精確的說法就是，用一組關鍵詞在一堆文本數據中尋找匹配項。在目前業界比較主流的全文搜索方案有：

支持全文搜索的關係行數據庫
Apache Lucene
基於 Apache Lucene 的 ElasticSearch
Apache Solr

後兩種是目前業界主要的方案，可能很多全文搜索的需求都會考慮用 ES 或者 Solr 實現。但是這樣一種方法並不是無代價的。有這樣幾個比較現實的問題

ES/Solr 在數量比較大的情況下的運維問題，怎麼樣保證集群的 HA 將是一個很考驗團隊功底的問題
怎麼樣將 MYSQL 或其餘數據源中的數據實時 / 離線 ETL 至 Search Engine 中
新增的學習與 Codebase 的維護成本。
新增一個依賴之後，對於系統整體的 HA 的保證

在技術決策中，我們往往需要去衡量一個選項的 ROI 來輔助決策。如果我們面對一個比較簡單的搜索場景，那麼選用 ES/Solr 所帶來的開銷將會使其 ROI 變得相對較低。因此在一些簡單的場景，我們可能會更希望利用數據庫本身的能力來完成我們的需求

所幸，在 MySQL 5.5 之後，其支持了一定的全文搜索的能力

MySQL 全文搜索#

MYSQL 全文搜索的前提是需要在表中建立一個 Full Text Index

alter table `user`
    ADD FULLTEXT INDEX name_index (`name`);

注意全文索引，僅對類型為 CHAR/VARCHAR/TEXT 的字段生效。

然後，我們插入兩條數據

insert into `user` (name, age, createTime, updateTime)
values ('Jeff.S.Wang', 18, current_timestamp, current_timestamp);

insert into `user` (name, age, createTime, updateTime)
values ('Jeff.Li', 18, current_timestamp, current_timestamp);

好了，我們可以來看看 MYSQL 怎麼進行全文查詢了

首先，按照官方的定義，

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

而 search_modifier 是所選取的匹配模式，在 MYSQL 中共有四種

IN NATURAL LANGUAGE MODE 自然語言模式
IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 自然語言帶擴展模式
IN BOOLEAN MODE 邏輯模式
WITH QUERY EXPANSION 擴展模式

我們常用的是 自然語言模式 和 邏輯模式。

首先來聊聊 自然語言模式，很簡單，顧名思義，MYSQL 會直接計算待匹配關鍵字，然後返回對應的值，這裡引用一段官網的解釋：

By default or with the IN NATURAL LANGUAGE MODE modifier, the MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to AGAINST(). For each row in the table, MATCH() returns a relevance value; that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.

我們來寫一段 SQL

select * 
from `user` 
where MATCH(name) AGAINST('Jeff' IN NATURAL LANGUAGE MODE)

然後我們發現能得到如下的結果

id	name	age	updateTime	createTime
1	Jeff Li	18	2020-03-01 15:38:07	2020-03-01 15:38:07
2	Jeff.S.Wang	18	2020-03-01 15:42:28	2020-03-01 15:42:28

然後，我們來嘗試匹配下用戶的 LastName，比如我們想找一位姓 Wang 的用戶

然後我們寫出了如下的 SQL

select * 
from `user` 
where MATCH(name) AGAINST('Jeff' IN NATURAL LANGUAGE MODE)

得到如下結果

id	name	age	updateTime	createTime
2	Jeff.S.Wang	18	2020-03-01 15:42:28	2020-03-01 15:42:28

然後我們開始嘗試，去搜索一位姓 Li 的用戶，然後我們寫下了，如下的 SQL

select * 
from `user` 
where MATCH(name) AGAINST('Li' IN NATURAL LANGUAGE MODE)

然後我們發現，什麼結果都沒有？？？？？WTF？Why？

原因在於分詞粒度，在我們進行錄入新數據的時候，MySQL 會將我們的索引字段中的數據按照一定的分詞基準長度進行分詞，然後存儲以待查詢，其有四個參數控制分詞的長度

innodb_ft_min_token_size
innodb_ft_max_token_size
ft_min_word_len 作用同上，不過是針對 MyISAM 引擎
ft_max_word_len

以 InnoDB 為例，其默認的 innodb_ft_min_token_size 的值是 3，換句話說在我們之前的錄入的數據中，我們數據中存儲的分詞後的單元是

Jeff
Wang

所以我們第二次搜索沒有結果，現在我們將 MySQL 的參數修改一下後，重新執行一下？

select * 
from `user` 
where MATCH(name) AGAINST('Li' IN NATURAL LANGUAGE MODE)

還還還是行不通？？？？

查了下官方文檔後，我們發現有這樣的描述

Some variable changes require that you rebuild the FULLTEXT indexes in your tables. Instructions for doing so are given later in this section.

而索引分詞粒度也包含在其中，所以我們需要刪除 /rebuild 索引，然後重新執行（有點坑。。）

select * 
from `user` 
where MATCH(name) AGAINST('Li' IN NATURAL LANGUAGE MODE)

好了，現在正常的返回結果了

id	name	age	updateTime	createTime
1	Jeff Li	18	2020-03-01 15:38:07	2020-03-01 15:38:07

現在讓我們來聊聊另一種匹配模式，BOOLEAN MODE

邏輯模式允許我們用一些操作符來檢索一些數據，我們舉一些常見的例子，剩下大家可以去看看 MYSQL 官方文檔

AGAINST ('Jeff Li' IN BOOLEAN MODE) 表示，要麼存在 Jeff 要麼存在 Li
AGAINST ('+Jeff' IN BOOLEAN MODE) 表示，必須存在 Jeff
AGAINST ('+Jeff -Li' IN BOOLEAN MODE) 表示必須存在 Jeff 且 Li 必須不存在

我們來執行下這幾個 SQL

select * 
from `user` 
where MATCH(name) AGAINST('Jeff Li' IN BOOLEAN MODE)

結果

id	name	age	updateTime	createTime
1	Jeff Li	18	2020-03-01 15:38:07	2020-03-01 15:38:07
2	Jeff.S.Wang	18	2020-03-01 15:42:28	2020-03-01 15:42:28

select *
from `user` 
where MATCH(name) AGAINST('+Jeff' IN BOOLEAN MODE)

結果

id	name	age	updateTime	createTime
1	Jeff Li	18	2020-03-01 15:38:07	2020-03-01 15:38:07
2	Jeff.S.Wang	18	2020-03-01 15:42:28	2020-03-01 15:42:28

select * 
from `user` 
where MATCH(name) AGAINST('+Jeff -Li' IN BOOLEAN MODE)

結果

id	name	age	updateTime	createTime
2	Jeff.S.Wang	18	2020-03-01 15:42:28	2020-03-01 15:42:28

好，現在我們有一些中文搜索的需求，我們先來插入數據

insert into `user` (name, age, createTime, updateTime)
values ('奧特曼', 18, current_timestamp, current_timestamp);

現在我們來搜索姓奧的用戶，我們按照之前的 Guide 寫出了如下的 SQL

select *
from `user`
where MATCH(name) AGAINST('+奧' IN BOOLEAN MODE)

然後我們驚喜的發現，又又又沒有結果？？？Why？？？

其實還是之前提到過的一個問題，分詞，MySQL 的默認的分詞引擎，只支持英文的分詞，而不支持中文分詞，那麼沒有分詞，沒有搜索？怎麼辦？

在 MySQL 5.7 之後，MySQL 提供了 ngram 這個組件來幫助我們進行中文分詞，使用很簡單

alter table `user`
    add fulltext index name_index (`name`) with parser ngram;

這裡有幾點要注意：

ngram 不僅適用於中文，按照官方文檔，韓文，日文也都支持
一個字段上只能有一個全文索引，所以需要刪除原有全文索引

同時，如同默認的分詞一樣，ngram 也受分詞粒度的限制，不過 ngram 的設置參數是

ngram_token_size

我們按照需要設置即可

總結#

全文搜索對於日常開發來講，是一個很常見的需求，在我們 infra 沒法讓我們去安心的使用外部組件的時候，利用數據庫提供的能力也許是個不錯的選項。不過還是有很多的坑要踩，有很多的參數要優化。。BTW 阿里雲的 RDS 設置真的難用（小聲吐槽

好了。。我的拖延症實在沒救了。。而且這兩天牙疼真的無奈，呜呜呜呜呜