告別 LIKE '%keyword%' 全表掃描：MySQL FULLTEXT 全文索引實戰指南

使用 LIKE '%關鍵字%' 進行全文搜尋是效能殺手：無法使用索引、全表掃描、不支援相關性排序。MySQL FULLTEXT 索引 專為全文檢索設計，提供自然語言相關性評分、布林模式精確控制、極速查詢體驗。

核心優勢

• 索引加速：倒排索引結構，毫秒級響應百萬行資料
• 智能相關性：自然語言模式自動評分，最相關結果優先
• 豐富查詢語法：布林模式支援 + - * " " 等操作符
• 中文支援**：MySQL 5.7+ 內建 ngram parser，無需第三方分詞器

建立 FULLTEXT 索引

建表時指定

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_title_content (title, content)
) ENGINE=InnoDB;

既有表新增索引

ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);

指定中文分詞器 (MySQL 5.7+)

ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content) WITH PARSER ngram;

驗證索引建立

SHOW INDEXES FROM articles WHERE Index_type = 'FULLTEXT';

核心查詢語法：MATCH ... AGAINST

1. 自然語言模式 (預設) - 智能相關性排序

SELECT title, content, 
       MATCH (title, content) AGAINST ('MySQL 優化') AS relevance
FROM articles
WHERE MATCH (title, content) AGAINST ('MySQL 優化')
ORDER BY relevance DESC;

• 自動分詞、去停用詞、計算 TF-IDF 相關性分數
• 結果按相關性降序排列
• 需符合 50% 門檻：若關鍵字出現在超過 50% 行中，視為常見詞自動忽略

2. 布林模式 - 精確控制邏輯

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

操作符	含義	範例
+	必須包含	+MySQL
-	必須不包含	-Oracle
*	萬用字元 (僅尾部)	優化* 匹配 優化、優化方案
" "	精確片語	"全文搜尋"
~	降低相關性	~教學 降低包含「教學」的權重

3. 查詢擴展模式 - 自動擴展相關詞

SELECT * FROM articles
WHERE MATCH (title, content) AGAINST ('資料庫' WITH QUERY EXPANSION);

兩階段：先自然語言搜尋 → 分析高相關文檔提取特徵詞 → 再次搜尋。適合查詢詞過短場景。

中文全文搜尋實戰配置

MySQL 5.7+ 內建 ngram parser (零配置)

CREATE TABLE docs (
    id INT PRIMARY KEY,
    content TEXT,
    FULLTEXT INDEX ft_content (content) WITH PARSER ngram
) ENGINE=InnoDB;

-- ngram 預設 token 長度 2 (bigram)，可調整
SET GLOBAL ngram_token_size = 2; -- 需重啟生效

MySQL 5.6 或需精準分詞：整合外部分詞器

• 應用層分詞 (jieba、IK Analyzer) → 儲存分詞結果到專用欄位 → 建立 FULLTEXT
• 或使用 MeCab 插件 (需編譯安裝)

效能調優關鍵參數

參數	建議值	說明
innodb_ft_min_token_size	2 (中文)	最小索引詞長度，預設 3 導致中文單字不入索引
innodb_ft_max_token_size	84	最大詞長
innodb_ft_enable_stopword	OFF	中文停用詞表通常不適用，建議關閉
ft_min_word_len (MyISAM)	2	MyISAM 引擎對應參數

限制與注意事項

• InnoDB FULLTEXT 索引即時更新 (5.6.4+)，但大量寫入時可 SET GLOBAL innodb_ft_enable_diag_print=ON 批次更新
• 不支援事務回滾索引變更：DML 後索引立即可見，回滾不還原索引
• 最小詞長限制：預設 3 字元，中文必須調為 1 或 2
• 50% 門檻：小資料集測試時常見「搜尋不到」問題，改用布林模式繞過
• 中文停用詞：內建英文停用詞表，中文需自定或關閉

總結

MySQL FULLTEXT 索引是關係型資料庫內建全文檢索的最佳選擇：零外部依賴、支援中文 (ngram)、語法豐富、效能優異。適用於 CMS 文章搜尋、電商商品搜尋、日誌關鍵字檢索等場景。資料量超億級或需極致分詞精度時，再考慮 Elasticsearch 等專用搜尋引擎。