MySQL 全文檢索實戰：FULLTEXT 索引建立、自然語言模式與布爾模式查詢

為什麼不用 LIKE？

LIKE '%keyword%' 無法使用索引，大表全掃描極慢，且無相關性排序，結果噪音大。

建立測試表與全文索引

CREATE TABLE reviews (
  id INT(5) PRIMARY KEY AUTO_INCREMENT,
  data TEXT
) ENGINE=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 have five members...');

ALTER TABLE reviews ADD FULLTEXT INDEX (data);

驗證索引

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

自然語言模式（預設）

按相關性降序返回，智能識別停用詞、詞幹。

SELECT * FROM reviews
WHERE MATCH(data) AGAINST('new single');

返回包含 "new"、"single" 的記錄，按出現頻率、位置、長度加權排序。

布爾模式（精確控制）

SELECT * FROM reviews
WHERE MATCH(data) AGAINST('+new -Madonna' IN BOOLEAN MODE);

查詢擴展模式

SELECT * FROM reviews
WHERE MATCH(data) AGAINST('database' WITH QUERY EXPANSION);

先按自然語言搜尋，取前幾行高頻詞自動擴展查詢，再二次搜尋。適合用戶查詢詞過短。

限制與注意事項

• 最小詞長： 預設 4 字符（ft_min_word_len），修改需重建索引。
• 停用詞表： 內建常見詞（the, and 等）不索引，可自定義 ft_stopword_file。
• 50% 閾值： 自然語言模式下，若詞出現在 >50% 行中自動忽略（視為停用詞）。布爾模式不受此限。
• 中文支援： MySQL 5.7.6+ 內建 ngram parser（WITH PARSER ngram），無需分詞器即可中文全文檢索。
• 引擎限制： 5.6 前僅 MyISAM，5.6+ InnoDB 完整支援。

效能優化建議

1. 大批量導入時：先 DROP INDEX，導完再 ADD FULLTEXT INDEX，避免逐行維護索引開銷。
2. 定期 OPTIMIZE TABLE 整理碎片，提升檢索速度。
3. 超大數據量（億級）建議外接 Elasticsearch/OpenSearch。