match against與like查詢的數據量相差很多的原因

先說一下like 與 、match against的用法：

like：

select * from cont where cont like "%關鍵詞%"

match against：

select * from cont where match(cont) against('關鍵詞*')

理想很豐滿，現實很骨感——這里還有坑：

• 忽略停詞（stopword），英語中頻繁出現的and/or/to等詞被認為是沒有實際搜索的意義，搜索這些不會獲得任何結果。
• 如果某個詞在數據集中頻繁出現的幾率超過了50%，也會被認為是停詞，所以如果數據庫中只有一行數據，不管你怎么全文搜索都不能獲得結果。
• 搜索結果都具有一個相關度的數據，返回結果自動按相關度由高到低排列。
• 只針對獨立的單詞進行檢索，而不考慮單詞的局部匹配，如搜索box時，就不會將boxing作為檢索目標。

原因是自然語言搜索方式是默認模式，即我們省略掉的聲明模式：“IN NATURAL LANGUAGE MODE”，完整語句應該是這樣：

select * from cont where match(cont) against('關鍵詞*' IN NATURAL LANGUAGE MODE)

所幸，mysql還有一種模式，布爾查找。這種查找方式沒有自然查找模式中的50%規則，而且檢索時單詞的局部匹配也會被作為目標進行檢索：

select * from cont where match(cont) against('關鍵詞*' IN BOOLEAN MODE)

結果還是……怎么說呢，反正不是我要的結果，這讓人心傷啊。用我的測試數據來對比一下：

SELECT count(*) from gk_cont where p like "%雕塑%" #返回203864
SELECT count(*) from gk_cont where MATCH(p) AGAINST('雕塑*' IN BOOLEAN MODE) #返回23125

十倍之差。。。網上沒找到權威的解釋（英文不行，所以只百度了一下），不過倒是有網友提到可能是分詞把部分我查找的關鍵詞分開了所致。暫時也只能這樣理解，有知道的鋪朋友請告知一下。

用Navicat修改數據引擎并建立全文索引的方法：

對于修改引擎與建立索引，網上有很多內容，自己百度一下就好，筆者把那些命令早忘了（學的時候就沒怎么記?。?，一直都是用Navicat的菜鳥，所以順便說一下Navicat怎么修改數據庫引擎與建立全文索引。

1、右擊數據表，選擇設計表，然后找到選項，修改其中的引擎設置并保存即可。注意，innoDB轉換過來可能會出現問題，你最好提前做好備份。

正確操作導讀：

1、mysql版本需要5.7.6以上，之前的版本都不支持的

2、建立全文索引必須加上WITH PARSER ngram，否則也是無效，我用的Navicat for mysql版本不行，用sql語句手動加索引，代碼如下：

alter table `表名` add fulltext index 索引名稱(`索引字段`) WITH PARSER ngram; 

如果是聯合查詢，就寫多個字段，之間用逗號分隔即可。系統默認為雙字分詞，所以查單字是查詢不到結果的，如：select * from table match(title) against('陳' in boolean mode); 如果想要能夠查到，那你需要先修改分詞方式為單字，可以直接在mysql.ini中修改或添加：ngram_token_size=1。需要注意的是，修改了分詞方式，您需要刪除之前的全文索引重新建立索引，否則也是沒有效果的；還有一點，單字分詞再檢索上比雙字分詞要慢很多，即使查詢條件完全一樣！

另外，測試了一下性能，如果需要單字檢索，建議直接用like吧，性能更好，相反就用match against，對比如下（數據表約有100w條數據）：

© 致遠 2020-05-10，原創內容，轉載請注明出錯：match against與like查詢的數據量相差很多的原因