MySQL 全文搜索實戰：從 LIKE 到 MATCH AGAINST 的效能躍升

痛點：LIKE '%keyword%' 的效能深淵

傳統 SELECT * FROM articles WHERE content LIKE '%keyword%' 無法利用索引，全表掃描，數據量超 10 萬行即明顯卡頓，百萬級直接超時。MySQL 內建全文索引（Fulltext Index）提供了原生、高效、支援相關性排序的解決方案。

快速上手三步走

1. 建表與測試數據

CREATE TABLE reviews (
  id INT(5) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  data TEXT
) ENGINE=MyISAM;  -- 5.6 前僅 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 burn instruments on stage!');

2. 創建全文索引

ALTER TABLE reviews ADD FULLTEXT INDEX ft_data (data);
-- 或建表時指定
CREATE TABLE reviews (
  id INT PRIMARY KEY AUTO_INCREMENT,
  data TEXT,
  FULLTEXT KEY ft_data (data)
) ENGINE=InnoDB;

3. 基礎搜索：自然語言模式

SELECT id, data FROM reviews
WHERE MATCH(data) AGAINST('single');

返回包含 "single" 的第 1、2 條記錄。MySQL 內部計算相關性分數，按分數降序返回。

進階技巧

查看相關性分數

SELECT id, MATCH(data) AGAINST('rock') AS score FROM reviews;
-- 結果：第 3 行 score=1.38，其餘 0

布爾模式：精確控制邏輯

-- 必須包含 single，且不包含 madonna
SELECT id FROM reviews
WHERE MATCH(data) AGAINST('+single -madonna' IN BOOLEAN MODE);

-- 通配符前綴搜索：hot* 匹配 hot、hotter、hottest
SELECT id FROM reviews
WHERE MATCH(data) AGAINST('hot*' IN BOOLEAN MODE);

-- 任意匹配：hell 或 rocks
SELECT id FROM reviews
WHERE MATCH(data) AGAINST('hell rocks' IN BOOLEAN MODE);

布爾操作符速查：
+ 必須包含 | - 必須不包含 | * 後綴通配符 | () 分組 | "" 短語精確匹配 | ~ 降低相關性 | @distance 近似距離

效能優化關鍵點

1. 引擎選擇：5.6+ 強烈建議用 InnoDB（支援事務、崩潰恢復、行鎖），MyISAM 僅適合唯讀歸檔表。
2. 最小詞長：預設 4 字符（MyISAM）或 3 字符（InnoDB），中文需配置 ngram 解析器（5.7.6+）或第三方分詞插件。
3. 停用詞表：預設過濾常見詞（the、and 等），可自定義 ft_stopword_file。
4. 索引維護開銷：大批量導入時先 DROP INDEX，導完再 ADD FULLTEXT INDEX，比逐行維護快數量級。
5. 大文本拆分：超長 TEXT 建議拆到子表，主表只存摘要或標題，減少全文索引體積。

中文全文搜索實踐（ngram 解析器）

-- 5.7.6+ 支援內建 ngram
CREATE TABLE articles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200),
  content TEXT,
  FULLTEXT KEY ft_content (content) WITH PARSER ngram
) ENGINE=InnoDB;

-- 搜索「數據庫優化」
SELECT * FROM articles
WHERE MATCH(content) AGAINST('數據庫 優化' IN BOOLEAN MODE);

ngram 將中文按字符或雙字符切分，無需外部分詞器，適合中小型應用；大型搜尋建議接入 Elasticsearch。

總結

MySQL 全文索引以 原生、零依賴、支援相關性排序 為優勢，完美解決中小型應用的關鍵字搜索需求。掌握 MATCH...AGAINST 自然語言與布爾兩種模式，配合 ngram 處理中文，能以極低成本交付優秀的搜索體驗。當數據量超億、需複雜聚合分析、多租戶隔離時，再考慮遷移至 Elasticsearch。