傳統 LIKE '%keyword%':
MySQL 內建 **Full-Text Search**(MyISAM 5.6+、InnoDB 5.6.4+)解決以上問題:
CREATE TABLE reviews (
id INT(5) PRIMARY KEY NOT NULL AUTO_INCREMENT,
data TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入測試數據
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 currently playing... I''ve been listening to it all day'),
('Have you heard the new band Hotter Than Hell? They have five members and they burn their instruments when they play in concerts. These guys totally rock! Like, awesome, dude!');
-- 添加全文索引(InnoDB 5.6.4+ 支持)
ALTER TABLE reviews ADD FULLTEXT INDEX ft_data (data);
-- 驗證索引
SHOW INDEXES FROM reviews;
-- Key_name=ft_data, Index_type=FULLTEXT
-- 搜索包含 "single" 的評論
SELECT id, data FROM reviews
WHERE MATCH(data) AGAINST('single');
-- 顯示相關性評分
SELECT id, MATCH(data) AGAINST('rock') AS relevance FROM reviews;
+----+-------------------------------+
| id | relevance |
+----+-------------------------------+
| 1 | 0 |
| 2 | 0 |
| 3 | 1.3862514533815 |
+----+-------------------------------+
原理:MySQL 基於詞頻 (TF)、逆文檔頻 (IDF)、文檔長度歸一化計算相關性分數。分數越高 = 越相關。無需手動排序,結果天然按相關性降序返回。
語法:AGAINST('expression' IN BOOLEAN MODE)
| 操作符 | 含義 | 示例 |
|---|---|---|
+ | 必須包含 | +single |
- | 必須不包含 | -madonna |
* | 通配符(僅作為詞尾) | hot* 匹配 hot, hotter, hottest |
"" | 短語精確匹配 | "throwing rocks" |
@distance | 近似搜索(詞距) | "new single" @10 |
>< | 調整權重 | >rock <pop 提高 rock 權重、降低 pop |
() | 分組 | +apple +(>pie |
~ | 降低貢獻值(軟排除) | ~noise |
-- 必含 single,排除 madonna
SELECT id FROM reviews
WHERE MATCH(data) AGAINST('+single -madonna' IN BOOLEAN MODE);
-- 結果:id=1
-- 詞幹搜索:hot* 匹配 hot, hotter, hottest
SELECT id FROM reviews
WHERE MATCH(data) AGAINST('hot*' IN BOOLEAN MODE);
-- 結果:id=3 (Hotter Than Hell), id=2 (hottest tracks)
-- 任意匹配:包含 hell 或 rocks 之一
SELECT id FROM reviews
WHERE MATCH(data) AGAINST('hell rocks' IN BOOLEAN MODE);
-- 結果:id=1 (rocks), id=3 (Hell)
ft_min_word_len(默認 4),InnoDB:innodb_ft_min_token_size(默認 3)ft_stopword_file / innodb_ft_stopword_table| 方案 | 原理 | 優點 | 缺點 |
|---|---|---|---|
| ngram 解析器 (MySQL 5.7.6+) | 將文本切為 N-gram(如 bigram) | 無需外部依賴、支持中日韓 | 索引膨脹大、精度不如詞法分詞 |
| 插件式分詞器 | 安裝 lib_mysqludf_jieba 等 UDF | 精準分詞、詞法分析 | 需編譯安裝、版本兼容性維護 |
| 外部搜索引擎 | 同步數據到 Elasticsearch / Solr / Meilisearch | 功能豐富、高性能、分佈式、多語言 | 架構複雜、數據同步延遲、運維成本高 |
FTS_DOC_ID 表,後台線程合併到主索引INNODB_FTS_INDEX_TABLE、INNODB_FTS_DELETED 表大小AGAINST('>keyword1 MATCH(title, content) AGAINST(...),標題權重自動更高(因長度短)ORDER BY MATCH(...) AGAINST(...) DESC, created_at DESC| 數據量/並發 | 推薦方案 |
|---|---|
| < 100 萬行、低併發、純中文少 | MySQL InnoDB Fulltext + ngram |
| 100 萬 - 1 億、中等併發、需中文分詞 | MySQL + Jieba UDF 插件 / 或 同步到 Elasticsearch |
| > 1 億、高併發、複雜查詢(高亮、聚合、建議) | Elasticsearch / OpenSearch / Meilisearch 專用搜索引擎 |
MySQL 全文搜索為**中小規模、中低併發、結構化+非結構化混合查詢**場景提供了**零架構成本**的檢索能力。掌握 MATCH...AGAINST 自然語言/布爾雙模式、理解倒排索引原理、正確配置分詞與停用詞,即可在不引入外部引擎的前提下構建高質量搜索服務。當業務突破單機 MySQL 承載上限時,平滑遷移至 Elasticsearch 等專用引擎是自然演進路徑。