MySQL 內建全文索引（Fulltext Index）基於 MyISAM（5.6+ InnoDB 亦支援），適用於文章、日誌、產品描述等長文本的關鍵字檢索。相較 LIKE '%kw%' 全表掃描，全文索引透過倒排鏈結構實現毫秒級檢索，並支援相關度排序、查詢擴展、布爾邏輯三大模式。本文系統梳理建立、查詢、優化全流程。

1. 建立全文索引

-- 建表時指定
CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT KEY ft_title_body (title, body)
) ENGINE=MyISAM;  -- 5.6+ 可用 InnoDB

-- 既有表添加
ALTER TABLE articles ADD FULLTEXT KEY ft_body (body);

-- 大量導入後再建索引（極快），勿先建索引再導入（極慢）

2. 三大搜尋模式語法與差異

① 自然語言模式（預設）

SELECT id, title, MATCH(title, body) AGAINST('database optimization') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('database optimization')
ORDER BY relevance DESC;

• 自動計算相關度（TF-IDF 加權），按相關度降序返回。
• 忽略停用詞、單字長度 < ft_min_word_len（預設 4）的詞。
• 50% 門檻：若某詞出現在超過 50% 記錄中，自動忽略（視為噪音）。

② 查詢擴展模式

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

• 兩階段：先自然語言搜尋 → 取前幾筆高相關文檔提取關鍵詞 → 二次搜尋。
• 適合查詢詞過短、用戶隱性知識缺乏（如搜 "DB" 期望命中 "MySQL/Oracle"）。
• 可能引入噪音，精確度下降，召回率上升。

③ 布爾模式（最強控制力）

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL -Oracle >performance 

  
操作符
含義
範例
  
+
必須包含
+MySQL
  
-
必須不包含
-Oracle
  
> / <
提高/降低相關度權重
>performance <tuning
  
()
分組
+MySQL +(>InnoDB 
  
~
負貢獻（噪音詞）
~noise
  
*
通配符（僅尾部）
optimiz*
  
""
精確短語
"query optimization"


  
不受 50% 門檻限制，不按相關度排序（除非顯式 ORDER BY）。
  
甚至無全文索引也能執行（全表掃描，極慢），但生產環境務必建索引。


3. 關鍵參數調優
[mysqld]
ft_min_word_len = 3              # 最小索引詞長，預設 4，中文建議 1-2（需重建索引）
ft_max_word_len = 84             # 最大詞長
ft_stopword_file = ''            # 禁用內建停用詞表，或指定自定義停用詞檔
innodb_ft_min_token_size = 3     -- InnoDB 專用
innodb_ft_max_token_size = 84
innodb_ft_enable_stopword = ON
修改後必須重建索引： REPAIR TABLE articles QUICK; 或 ALTER TABLE articles DROP INDEX ft_body, ADD FULLTEXT ft_body(body);

4. 中文分詞痛點與替代方案
MySQL 內建分詞器按空白/標點切分，對無分隔符的中文（"數據庫優化" → "數據庫優化" 整詞）無法命中「數據庫」或「優化」。
生產級替代方案：

  
Elasticsearch + IK Analyzer / Jieba： 專業全文搜尋引擎，支援中文分詞、同義詞、模糊、高亮、聚合。
  
Sphinx / Manticore Search： 輕量級、支援 MySQL 協議、中文分詞、實時索引。
  
PostgreSQL + pg_trgm / zhparser： 若技術棧允許遷移。


5. 實戰優化清單

  
大量寫入時 DISABLE KEYS → 載入 → ENABLE KEYS（MyISAM）或批次 INSERT 後 OPTIMIZE TABLE。
  
查詢只選需要欄位，避免 SELECT * 回表開銷。
  
布爾模式優先用 + 強制包含，縮小掃描範圍。
  
監控 Handler_read_key / Handler_read_rnd_next 確認走索引。
  
索引欄位避免頻繁 UPDATE，否則碎片化嚴重，定期 OPTIMIZE TABLE。


MySQL 全文搜尋對英文場景開箱即用、效能優異；中文場景請直接上 Elasticsearch，別在資料庫層硬扛。理解三大模式差異、調好 ft_min_word_len、善用布爾操作符，能解決 80% 的文本檢索需求。