用mysql fulltext search建立簡易搜尋引擎

9 min readNov 23, 2018

記得以前小時候不懂事，要搜尋一個東西會用最愚笨的方式從DB找

SELECT * FROM `test` WHERE `item_name` LIKE ‘%QUERY%';

結果呢這個方式造成了Full table scan，量大一點直接爆炸。而且像是整篇文章這種東西，好像不用多少篇就會死給你看。

剛好最近因為公司的關係開始碰elasticsearch(一個功能強大的search engine加上一堆周邊產品)的ELK stack，那我個人又因為好奇，想知道用mysql做這件事效能會怎麼樣，決定用fulltext search做一個mysql版的超簡單搜尋引擎。

這次的實驗資料約3GB，內容是Wiki的文章，文章數目30萬則，也是個中小型的搜尋引擎，MySQL使用8.0.13 docker image架在GCP上，用1vCPU + 5GB RAM的機器，MySQL設定檔完全使用預設，後端使用GoLang架在本機端，加上網路延遲Demo起來速度也不算太慢，實驗結果如下：

在開始之前首先需要取得測資，而Wiki上的文章都可以在wikidump找到，以下這幾種都可以，我是載下面分割檔一個一個丟進去資料庫，畢竟資料壓縮前就14.3G，壓縮後可是頗大，萬一出錯可是全部重來。

或是這樣下，檔名記得換XD

curl -O https://dumps.wikimedia.org/enwiki/20180820/enwiki-20180820-pages-articles1.xml-p10p30302.bz2 && bzip2 -d enwiki-20180820-pages-articles1.xml-p10p30302.bz2

之後抓一個很好用的工具WikiExtractor，它可以把dump出來的檔案解析成沒這麼亂，並濾掉所有的HTML tag的json格式資料。之後下

要注意的是如果碰到編碼問題，可以參考這裡，或參考下圖，fileinput那行備註解掉換成open，且記得註解掉line.decode。

之後應該會看到當前目錄(pwd)底下出現text資料夾，這時候進去應該會看到幾個資料夾AA, AB等等，底下都是又臭又長的json格式的wiki文章。

之後創建一個這樣的table

CREATE DATABASE demo;
USE demo;
CREATE TABLE IF NOT EXISTS test (
  `id`    int not null auto_increment,
  `url`   varchar(255),
  `title` varchar(255),
  `text`  mediumtext,
  FULLTEXT `fulltext_index` (`title`, `text`),
  primary key (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

這邊因為我們要搜尋文章的內容和標題，所以加上FULLTEXT(`title`, `text`)表示加上fulltext index。

到這邊我寫了一個小工具，利用它就可以直接把這些轉換過的文章通通塞進去MySQL裡面，且它會幫你把非法字元通通escape掉，如”變成\”，而且我還很貼心得編了Windows/Linux/OS X的執行擋讓大家玩XD。

資料都進去之後，要查詢就非常簡單

SELECT `title`, MATCH(`title`, `text`) AGAINST ('keyword' IN NATURAL LANGUAGE MODE) AS `score` 
FROM `test` WHERE MATCH(`title`, `text`) AGAINST ('keyword' IN NATURAL LANGUAGE MODE) > 5;

MATCH (index) AGAINST (‘要找的字’ IN … MODE)是查找語法，它會為每篇文章評比關聯性，最高分的會顯示在第一筆。而使用空格或逗號等stopword分隔關鍵字可以一次查找多個關鍵字，每次查找幾乎都能在1秒之內返回結果，縱使超過5個左右的關鍵字如以下，整體執行時間也在0.8s以內。

結果

甚至是以下我隨便亂貼一段官方文件的字進去，4.5s。

不過結果就有點嗯..，但也有可能是文章裡面包含很多這樣的字，只不過wiki出名的詳細（冗長），所以我也沒仔細對照。

此外這邊介紹另一個功能 - Query Expansion。若使用者要搜尋某個東西，但又不清楚確切的東西是什麼，我們工程師要想辦法幫他猜，不然我們很爛很白痴都是我們的錯(工程師OS..)

例如他想買手機，但忘記iphone叫做iphone，所以搜尋手機，我們應該要返回Samsung J7, iphone Xs, Google Pixel等等。想當然爾，我又不是Google我怎麼可能猜得到(誤)。這時候就可以用Query Expansion。

SELECT `title` FROM `test` MATCH(`title`, `text`) AGAINST ('keyword' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION) > 5;

然後按下去後1分鐘…2分鐘… 5分鐘… 幹我想下班啊～

(MySql: 我就說我不是Google啦)

它的原理其實就是先用關鍵字手機去搜尋，再統計每個搜尋結果出現的字，例如在查手機的時候出現很多次iphone，那它就很貼心的再幫你去查iphone，所以其實它會多次進行查詢，因此相當相當之慢。

(最後在過了40分鐘後仍然在轉圈圈，連ssh都連不進VM，只好透過GCP Console重開XD，這功能不要亂試)

而除了Natural Language Mode還有另一種Boolean mode，可以指定出現某個關鍵字會加分，某個關鍵字減分，或甚至是必須出現跟必定不能出現某個字。

SELECT `title` FROM `test` MATCH(`title`, `text`) AGAINST 
('+english -chinese >spanish <german' IN BOOLEAN MODE); 
// 結果必定出現english，且不得帶有chinese。
// 出現spanish加分，出現german減分

那類似Google的關鍵字猜測怎麼做？

Mysql還有一個功能叫做nGram parser，假設N =2，它會把一個字例如Wine拆成：

[wi, win, wine, in, ine, ne]

其實就是Sliding window去取最小為N的substring，然後做index，所以使用者在打到wi的時候wine就會一起被找到。

ALTER TABLE `test` 
ADD FULLTEXT `ngram_index` (`title`) WITH PARSER ngram;

這樣一來，一個最基本的搜尋引擎就有原型了。至於N，mysql預設是2，要改的話可以在conf設定檔案加上

[mysqld]
ngram_token_size=3

最後呢，簡單比較一下Elasticsearch跟mysql功能上的差異，ES可以做stemming，例如說搜尋buying是可以同時找到bought和buy。

ES還可以做fuzzy search，例如使用者打錯字輸入iphene，mysql會回給你一個空的結果，ES則會把iphone找出來給你。

此外ES能自訂的選項也比較多，外加功能完備的ELK stack，那個精美的Kibana就騙了不知道多少人入坑了！

當然，ES作為一個搜尋引擎，某些RDBMS的constraint或是NoSQL中的功能就比較薄弱，例如Join，官方Doc就寫了ES的Join是prohibitively expensive，最好是不Normalize資料來達到最快的搜尋速度。

且本質上ES的儲存資料方式跟一般通用型DB的方式就有很大的差異，所以一般來說ES都是作為輔助資料庫(主搜尋)，另外會有一個RDBMS或NoSQL來儲存資料，所以要另外維護一個這樣的ELK stack，是很花＄＄＄的，光ES cluster就好幾個node了，更何況是要找會ELK stack的人。

總結呢，就是Google CSE或是AWS Cloudsearch可能才是你最好的選擇，當然如果你的business長大了，或你非常有Hacker精神，自架ELK stack或玩玩MySql好像也是可以。

以上完整程式碼，包含Client端Demo，在此。

用mysql fulltext search建立簡易搜尋引擎

Written by Changheng Liou