全文索引基礎

MySQL 5.6+ InnoDB、MyISAM 均支援全文索引。適用於**長文本欄位**（VARCHAR/TEXT）的關鍵字搜尋，比 LIKE '%kw%' 快數量級。

建立全文索引

-- 建表時
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 既有表新增
ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body);

三種搜尋模式

1. 自然語言模式（預設）

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

• 計算相關性分數，按分數降序排列
• 忽略停用詞、50% 門檻值（出現超過 50% 文檔的詞被忽略）
• 不支援運算子

2. 布林模式（IN BOOLEAN MODE）

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

優點：無 50% 門檻、支援精確控制、不計算相關性（較快）。

3. 查詢擴展模式（WITH QUERY EXPANSION）

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

兩階段：先自然語言搜尋 → 取高相關文檔的高頻詞 → 再次搜尋。適合查詢詞過短。

實測效能筆記（千萬級資料）

運算子	含義	範例
+	必須包含 (AND)	+apple +juice
-	必須不包含 (NOT)	+apple -pie
(空白)	可選包含 (OR)	apple banana
> / <	提高/降低權重	+apple +(>pie <strudel)
~	降低權重（軟排除）	+apple ~banana
*	通配符（僅尾部）	appl*
"..."	片語搜尋	"mysql tutorial"

指標	自然語言	布林模式	LIKE %kw%
查詢速度	0.0x 秒	0.0x 秒	20+ 秒
索引大小	~5x 資料	同左	無額外索引
支援中文	需 ngram parser (5.7+)	需 ngram parser	支援但極慢

中文分詞：ngram Parser (MySQL 5.7.6+)

-- 建表指定 parser
CREATE TABLE articles_zh (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title, body) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ngram 預設 token 大小 2 (bigram)，可調 my.cnf: ngram_token_size=2

注意：ngram 不支援布林模式的 * 通配符與 "phrase" 片語搜尋。

常見坑點與解決

坑點	現象	解決
無 LIMIT 導致全表掃描	SELECT COUNT(*) 或無 LIMIT 查詢極慢/卡死	**必須加 LIMIT**；計數用應用層快取或單獨統計表
搜尋不存在關鍵字卡死	全文索引需掃描所有倒排列表	應用層維護「熱門關鍵字白名單」，不在白名單直接回空
索引膨脹	大量 UPDATE/DELETE 導致碎片	定期 OPTIMIZE TABLE 或 ANALYZE TABLE
最小詞長限制	ft_min_word_len=4，搜 3 字無果	改 my.cnf ft_min_word_len=2 並重建索引
停用詞表	常見詞被忽略	自訂 ft_stopword_file 或空檔案禁用

最佳實踐建議

1. 中文環境必用 ngram parser（MySQL 5.7+）
2. 布林模式為主，避開 50% 門檻與停用詞干擾
3. 所有查詢加 LIMIT（如 20/50），禁止無限制查詢
4. 關鍵字白名單機制：記錄無結果關鍵字，新資料寫入時檢查並更新白名單
5. 大規模/高併發：考慮 Elasticsearch、OpenSearch、Typesense 等專用搜尋引擎
6. 監控指標：Innodb_ft_index_cache_size、Innodb_ft_total_cache_size