MySQL 全文搜尋實戰：從 LIKE 低效到 MATCH AGAINST 高效與相關性排序

前言：為什麼 LIKE 不適合全文搜尋？

WHERE content LIKE '%keyword%' —— 無法使用索引、全表掃描、不支援相關性排序、多關鍵字邏輯極其繁瑣。資料量超過萬筆即明顯卡頓，百萬級直接不可用。

MySQL 內建全文索引方案

MySQL 3.23.23+ 引入 Full-Text Search，透過 FULLTEXT INDEX + MATCH() AGAINST() 實現：

• 倒排索引結構，查詢速度與資料量近乎無關。
• 自然語言模式自動計算相關性分數，結果按相關度排序。
• 布林模式支援 + - * 邏輯運算，精準控制匹配規則。

一、建立測試資料與全文索引

CREATE TABLE reviews (
    id INT(5) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    data TEXT
) ENGINE=MyISAM;  -- MySQL 5.6 前僅 MyISAM 支援，5.6+ InnoDB 也支援

INSERT INTO reviews (data) VALUES
('Gingerboy has a new single out called Throwing Rocks. It\'s great!'),
('Hello all, I really like the new Madonna single. One of the hottest tracks...'),
('Have you heard the new band Hotter Than Hell? They burn instruments...');

-- 建立全文索引
ALTER TABLE reviews ADD FULLTEXT INDEX (data);

-- 驗證
SHOW INDEXES FROM reviews;
-- Key_name: data, Index_type: FULLTEXT

二、自然語言模式：預設相關性排序

-- 簡單查詢
SELECT id FROM reviews WHERE MATCH(data) AGAINST('single');
-- 返回 id 1, 2（包含 single）

-- 查看相關性分數
SELECT id, MATCH(data) AGAINST('rock') AS relevance
FROM reviews;
-- id=3 分數 1.386，id=1,2 分數 0

• 分數 0 = 不匹配；分數越高 = 越相關。
• 相關性算法基於詞頻（TF）、逆文檔頻率（IDF）、文檔長度正規化。
• MATCH() 可接受多欄位：MATCH(title, body, tags)。

三、布林模式：精準邏輯控制

語法：AGAINST('expression' IN BOOLEAN MODE)

實戰範例

-- 必須含 single，排除 madonna
SELECT id FROM reviews WHERE MATCH(data) AGAINST('+single -madonna' IN BOOLEAN MODE);
-- 僅 id=1

-- 前綴搜尋 hot*
SELECT id FROM reviews WHERE MATCH(data) AGAINST('hot*' IN BOOLEAN MODE);
-- id=2,3

-- 至少包含 hell 或 rocks 之一
SELECT id FROM reviews WHERE MATCH(data) AGAINST('hell rocks' IN BOOLEAN MODE);
-- id=1,3（預設 OR 邏輯）

四、效能與限制必知

• 最小詞長： ft_min_word_len（預設 4），中文需設為 1 或使用 ngram parser（MySQL 5.7.6+）。
• 停用詞： 內建停用詞表（the, and, is...），可自訂 ft_stopword_file。
• 50% 門檻： 自然語言模式下，若詞出現在超過 50% 文檔中，自動視為停用詞（布林模式不受影響）。
• 中文支援： MySQL 5.7.6+ ngram parser 解決無分詞問題：FULLTEXT INDEX (data) WITH PARSER ngram。
• InnoDB 支援： 5.6+ 完整支援，含事務一致性。
• 索引維護成本： 寫入時需更新倒排索引，大量批次寫入建議 DISABLE KEYS / ENABLE KEYS。

五、遷移建議：從 LIKE 到 MATCH

1. 確認引擎支援（MyISAM 全版本、InnoDB 5.6+）。
2. 為搜尋欄位加 FULLTEXT INDEX。
3. 改寫 WHERE col LIKE '%kw%' 為 WHERE MATCH(col) AGAINST('kw')。
4. 需精準邏輯時加 IN BOOLEAN MODE。
5. 中文環境務必啟用 ngram parser 或整合外部分詞器（如 jieba + 儲存分詞結果）。

結語

MySQL 全文搜尋以倒排索引 + 相關性排序徹底解決 LIKE 低效難題。掌握自然語言模式（智能排序）與布林模式（精準邏輯）雙模切換，並針對中文啟用 ngram parser，即可在資料庫層面實現生產級搜尋體驗。超大規模、高併發、複雜語義場景，建議升級 Elasticsearch/OpenSearch 專用搜尋引擎。