萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 全文搜索實戰:從基礎 MATCH AGAINST 到布爾模式精準檢索

為什麼需要全文索引?

傳統 LIKE '%keyword%':

  • ❌ 無法使用索引(前導通配符)
  • ❌ 全表掃描,性能隨數據量線性下降
  • ❌ 無相關性排序,結果質量差

MySQL 內建 **Full-Text Search**(MyISAM 5.6+、InnoDB 5.6.4+)解決以上問題:

  • ✅ 倒排索引,毫秒級檢索百萬文檔
  • ✅ 自然語言模式:按相關性自動評分排序
  • ✅ 布爾模式:精準邏輯控制(必含/排除/通配/權重)

實戰演示:音樂評論搜索系統

1. 建表與建立全文索引

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

2. 自然語言模式:智能相關性排序

-- 搜索包含 "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)、文檔長度歸一化計算相關性分數。分數越高 = 越相關。無需手動排序,結果天然按相關性降序返回。

3. 布爾模式:精準邏輯控制

語法: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)

進階優化與避坑指南

1. 最小詞長限制

  • MyISAM:ft_min_word_len(默認 4),InnoDB:innodb_ft_min_token_size(默認 3)
  • 中文需設置為 1 或 2,並配合分詞器(見下文)

2. 停用詞表

  • 默認內建英文停用詞(the, and, of...),可自定義:ft_stopword_file / innodb_ft_stopword_table
  • 中文場景**必須自定義停用詞表**(的、了、在、是...)

3. 中文分詞:MySQL 內建不支持,需外部方案

方案原理優點缺點
ngram 解析器 (MySQL 5.7.6+)將文本切為 N-gram(如 bigram)無需外部依賴、支持中日韓索引膨脹大、精度不如詞法分詞
插件式分詞器安裝 lib_mysqludf_jieba 等 UDF精準分詞、詞法分析需編譯安裝、版本兼容性維護
外部搜索引擎同步數據到 Elasticsearch / Solr / Meilisearch功能豐富、高性能、分佈式、多語言架構複雜、數據同步延遲、運維成本高

4. 索引維護開銷

  • InnoDB 全文索引**異步更新**:寫入時先寫 FTS_DOC_ID 表,後台線程合併到主索引
  • 大批量導入前:**刪除全文索引 → 導入數據 → 重建索引**,比逐行維護快數量級
  • 監控 INNODB_FTS_INDEX_TABLE、INNODB_FTS_DELETED 表大小

5. 相關性調優

  • 權重調整: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 等專用引擎是自然演進路徑。

copyright © 萬盛學電腦網 all rights reserved