MySQL 全文索引入門與實戰：自然語言、布爾模式與查詢擴展詳解

一、 全文索引前置條件

• 存儲引擎：MyISAM（5.6 前）；InnoDB（5.6+ 支援）
• 字段類型：CHAR、VARCHAR、TEXT 及其變體
• 索引定義：FULLTEXT(key1, key2...) 建表時或 ALTER TABLE ADD FULLTEXT 後加
• 大數據量建議：先導入數據再建索引，速度快數量級

二、 語法核心

MATCH (col1, col2, ...) AGAINST (expr [search_modifier])
search_modifier:
    IN NATURAL LANGUAGE MODE
  | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
  | IN BOOLEAN MODE
  | WITH QUERY EXPANSION

三、 三大搜索模式深度解析

1. 自然語言模式（默認）

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

• 將搜索詞拆分為單詞，按相關性評分排序（0~1）
• 忽略停用詞表、過短詞（默認 ft_min_word_len=4）
• 50% 閾值：若某詞出現在超過 50% 行中，視為噪聲自動忽略
• 適合：通用關鍵詞搜索、相關性排序

2. 布爾模式

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

• 無 50% 閾值限制
• 不按相關性排序（默認按表順序）
• 可無索引使用（全表掃描，極慢，勿用）
• 適合：精確邏輯組合、技術文檔/代碼搜索

3. 查詢擴展模式

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

• 兩階段：先自然語言搜索 → 取前幾行高頻詞 → 二次自然語言搜索
• 能發現「間接相關」結果（如搜 "DB" 關聯出 "database"、"SQL"）
• 可能引入噪聲，適合探索性搜索、推薦場景

四、 實戰案例：產品表關鍵詞搜索

表 product(name, ...)，name 建全文索引，近 87 萬行。

-- 自然語言模式，1.15s 命中 10 萬+ 行
SELECT * FROM product WHERE MATCH(name) AGAINST('auto');
-- 相關性分數查看
SELECT MATCH(name) AGAINST('auto') AS relevance, name FROM product
WHERE MATCH(name) AGAINST('auto') ORDER BY relevance DESC;

五、 分詞與配置調優

• 最小詞長：ft_min_word_len=4（my.cnf，需重建索引生效）
• 停用詞表：ft_stopword_file 指定自定義文件，或置空禁用
• 中文分詞：MySQL 內建不支援中文分詞，需插件（如 ngram 5.7+、zhparser）或應用層分詞入庫

六、 避坑指南

七、 總結

MySQL 全文索引提供開箱即用的文本搜索能力：自然語言模式適合通用相關性搜索，布爾模式適合精確邏輯組合，查詢擴展適合探索發現。中文場景需配合分詞插件。百萬級以內數據表現優異，超大規模建議接入 Elasticsearch 等專業搜索引擎。