MySQL 全文搜索實戰：告別 LIKE 低效，擁抱 MATCH AGAINST 高效檢索

為什麼要拋棄 LIKE '%keyword%'？

傳統 LIKE '%關鍵詞%' 在大數據量下性能災難級：

• 無法使用索引，全表掃描
• 併發高時鎖表嚴重，QPS 直線下跌
• 無相關性排序，結果體驗差

MySQL 全文索引核心優勢

1. 倒排索引結構：Token 級映射，毫秒級命中
2. 自然語言相關性：TF-IDF 評分，自動過濾噪音
3. 布爾模式精準控制：支持 + - * "" 等操作符

三步建立全文索引

-- 1. 建表時指定（推薦）
CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,
  FULLTEXT KEY ft_title_content (title, content)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- 2. 或後續添加
ALTER TABLE articles ADD FULLTEXT INDEX ft_title_content (title, content);

-- 3. 驗證索引生效
SHOW INDEX FROM articles WHERE Key_name = 'ft_title_content';

核心查詢語法

自然語言模式（默認，相關性排序）

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

布爾模式（精準控制）

-- 必須包含 MySQL，排除入門，優化權重提高
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -入門 優化*' IN BOOLEAN MODE);

查詢擴展模式（自動發掘相關詞）

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('數據庫調優' WITH QUERY EXPANSION);

進階調優參數

注意：修改參數需重建索引 REPAIR TABLE tbl QUICK 或 ALTER TABLE tbl DROP INDEX ..., ADD FULLTEXT INDEX ...

中文分詞實戰方案

MySQL 內置分詞器對中文支持弱，生產環境建議：

1. 應用層分詞：jieba、IK Analyzer 預分詞，存入專用 keywords 字段建索引
2. 插件方案：mysql-jieba、ngram parser (MySQL 5.7.6+)
3. 混合方案：ngram 做召回，應用層重排

避坑指南

• ❌ 表行數 < 10 萬時，全文索引反而比 LIKE 慢（優化器偏好全掃）
• ❌ 頻繁 UPDATE 全文索引列會導致碎片化，定期 OPTIMIZE TABLE
• ❌ 忽略 ft_min_word_len 導致短詞搜不到
• ✅ 使用 EXPLAIN 確認 type=fulltext
• ✅ 監控 Handler_read_key 指標評估索引命中率

總結

MySQL 全文搜索以 MATCH ... AGAINST 為核心，配合合理的分詞策略與參數調優，可將文本檢索效率提升 2-3 個數量級。但對於億級數據、複雜語義查詢，仍建議引入 Elasticsearch 等專業搜索引擎。