MySQL 全文索引深度解析：從基礎語法到進階調優實戰

前言：為什麼需要全文索引？

傳統 LIKE '%keyword%' 查詢在大數據量下效能極差，且無法支援語意相關度排序。MySQL 內建的 全文索引 專為自然語言搜尋設計，支援布林模式、相關度評分與停用詞過濾，是中小型專案實現全文檢索的輕量化首選。

一、全文索引核心概念

• 適用引擎：MyISAM（MySQL 5.6+ InnoDB 亦支援）
• 索引欄位類型：CHAR、VARCHAR、TEXT
• 建立時機：大量匯入資料 前 建立索引速度最快；已有索引的表寫入資料會顯著變慢。

二、基本語法與範例實戰

1. 建表時直接定義

CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT (title, body)
) ENGINE=MyISAM;

2. 事後新增索引

ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body);

3. 自然語言模式搜尋（預設）

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('database');

特性：自動按相關度降序排列；忽略大小寫；若關鍵字出現在超過 50% 記錄中，視為停用詞而不回傳結果。

4. 顯示相關度分數

SELECT id, MATCH(title, body) AGAINST('Tutorial') AS relevance
FROM articles;

5. 複雜查詢：同時取得資料與分數並排序

SELECT id, body,
       MATCH(title, body) AGAINST('Security implications of running MySQL as root') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('Security implications of running MySQL as root');

MySQL 優化器會合併兩次相同的 MATCH()，僅執行一次全文搜尋，無額外開銷。

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

透過 IN BOOLEAN MODE 修飾詞啟用，不受 50% 門檻限制，且不自動按相關度排序。

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL -YourSQL' IN BOOLEAN MODE);

四、全文索引限制與注意事項

• MATCH() 參數必須來自同一張表，且須完全符合某個 FULLTEXT 索引的欄位組合（布林模式除外）。
• AGAINST() 參數必須為常數字串，不可使用變數或欄位。
• 最小詞長預設 4 字元（由 ft_min_word_len 控制），中文環境需配合分詞器或調整參數。
• 停用詞表可透過 ft_stopword_file 自訂。

五、進階微調與效能優化

1. 修改最小詞長

SET GLOBAL ft_min_word_len=2; -- 需重建索引生效

2. 自訂停用詞表

建立文字檔列出停用詞，設定 ft_stopword_file=/path/stopwords.txt 並重啟 MySQL、重建索引。

3. 關閉 50% 門檻（僅限自然語言模式）

修改原始碼 myisam/ftdefs.h：#define GWS_IN_USE GWS_FREQ 並重新編譯 MySQL。警告：會嚴重降低相關度判準確度，建議改用布林模式。

4. 重建索引最快指令

REPAIR TABLE tbl_name QUICK;

六、未來展望（官方 TODO）

• 鄰近度運算子
• 支援 "always-index words"（如 C++、TCP/IP）
• 多位元組字元集（中日韓）完整支援
• 依語言自動產生停用詞與詞幹擷取
• 使用者自訂 UDF 前置解析器

結語

MySQL 全文索引雖不及 Elasticsearch 功能豐富，但在中小型應用、單機部署、維運成本敏感的場景下極具性價比。掌握自然語言與布林兩大模式、善用相關度分數、根據業務調整詞長與停用詞，即能以最低成本提供優質搜尋體驗。