MySQL FULLTEXT 全文索引詳解：自然語言、布爾模式、中文分詞與遷移建議

MySQL FULLTEXT 全文索引詳解

MySQL 3.23.23+ 引入 FULLTEXT 索引，支援 MyISAM (早期) 和 InnoDB (5.6+)。本文詳細講解自然語言模式、布爾模式、查詢擴展模式、中文分詞限制、效能調優與遷移 Elasticsearch 建議。

一、核心概念

• 索引類型：FULLTEXT，僅支援 CHAR、VARCHAR、TEXT 列。
• 引擎支援：MyISAM (全版本)、InnoDB (5.6.4+)、不支援 Memory/NDB。
• 建索引時機：大數據量先導入數據後 ALTER TABLE ADD FULLTEXT 或 CREATE FULLTEXT INDEX，比邊導入邊建索引快數量級。
• 最小詞長：innodb_ft_min_token_size (默認 3)、ft_min_word_len (MyISAM 默認 4)，中文單字需設為 1。
• 停用詞：內建停用詞表 (the, and, of...)，可自定義 innodb_ft_stopword_table。

二、建表與建索引

-- 建表時創建
CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT (title, body)
) ENGINE=InnoDB;

-- 後加索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body);
CREATE FULLTEXT INDEX ft_body ON articles (body);

三、三大搜索模式

1. 自然語言模式 (默認)

SELECT * FROM articles
WHERE MATCH (title, body) AGAINST ('database');

• 相關度排序 (高→低)，基於 TF-IDF 加權。
• 不區分大小寫。
• 50% 閾值：若搜索詞出現在超過 50% 行中，視為常用詞，自動忽略 (自然語言模式特有)。
• 不支援操作符。

2. 布爾模式 (IN BOOLEAN MODE)

SELECT * FROM articles
WHERE MATCH (title, body) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

• 不自動按相關度排序 (需手動 ORDER BY MATCH(...) AGAINST(...) DESC)。
• 無 50% 閾值限制。
• 無 FULLTEXT 索引也可工作 (全表掃描，極慢)。

3. 查詢擴展模式 (WITH QUERY EXPANSION)

SELECT * FROM articles
WHERE MATCH (title, body) AGAINST ('database' WITH QUERY EXPANSION);

• 兩階段：自然語言搜索 → 取高相關度行提取關鍵詞 → 二次搜索。
• 適用於搜索詞過短、用戶查詢模糊場景。

四、相關度計算與排序

-- 顯式獲取相關度分值
SELECT id, MATCH (title, body) AGAINST ('Tutorial') AS relevance
FROM articles
ORDER BY relevance DESC;

分值範圍 0.0 - 1.0+，越高越相關。自然語言模式默認按相關度降序；布爾模式不排序。

五、中文分詞痛點與解決方案

ngram_parser 使用示例 (5.7.6+)

SET GLOBAL innodb_ft_min_token_size = 1;
CREATE TABLE articles_cn (
  id INT PRIMARY KEY,
  content TEXT,
  FULLTEXT (content) WITH PARSER ngram
) ENGINE=InnoDB;

-- 搜索
SELECT * FROM articles_cn
WHERE MATCH (content) AGAINST ('数据库' IN BOOLEAN MODE);

六、效能調優與維護

• innodb_ft_cache_size (默認 32M)：構建索引時緩存大小，大表調大。
• innodb_ft_total_cache_size (默認 640M)：全局緩存上限。
• OPTIMIZE TABLE：碎片整理，合併刪除標記的詞條。
• ANALYZE TABLE：更新詞頻統計，優化相關度計算。
• 監控：SHOW ENGINE INNODB STATUS 觀察 FULLTEXT INDEX 區塊。

七、遷移 Elasticsearch 建議

MySQL FULLTEXT 適合：

• 數據量 < 1000 萬行、單表、簡單搜索、無同義詞/拼音/模糊需求、團隊無 ES 運維能力。

必須遷移 ES 場景：

• 中文全文搜索核心業務 (電商搜索、內容檢索、日誌分析)。
• 需同義詞、拼音、拼寫糾錯、高亮、聚合分析、地理空間。
• 寫入 QPS > 1000、查詢 QPS > 5000、多租戶隔離、跨索引搜索。
• MySQL 主庫寫入壓力大，搜索讀放大影響主業務。

遷移架構：MySQL Binlog → Canal/Debezium → Kafka → Logstash/Flint → Elasticsearch，實現準實時同步。

MySQL FULLTEXT 是關係型數據庫內建的輕量全文檢索能力，滿足簡單場景。面對中文複雜語義、高併發搜索、豐富檢索功能，Elasticsearch/OpenSearch 才是專業選擇。架構演進路徑：MySQL FULLTEXT → 應用層分詞+普通索引 → Elasticsearch 集群，按業務價值與團隊能力逐步升級。

1. 上一篇文章： MySQL 用戶與權限管理實戰：GRANT/REVOKE 語法、分層授權、審計與安全基線
2. 下一篇文章： MySQL LEFT JOIN 執行機制與效能優化深度解析

MySQL 資料庫相關知識

MySQL 數據庫分區功能深度實戰：RANGE/LIST/HASH/KEY/子分區/Columns 全解析

什麼是分區 將一張大表的物理文件（.MYD/.MYI/.fr

MySQL入門系列：MySQL 資料目錄位置與管理

資料目錄的角色與預設位置 MySQL 以資料目錄儲存所有資料

CentOS 7 部署 MySQL 5.7 二進制包完整實戰：多實例架構與系統整合

安裝方式對比 方式優點缺點推薦場景 二進制包版本自主、路

Linux 異構網路環境下 MySQL 資料庫備份策略：本地、DVD 與網路備份實戰

針對中小型 Linux 網站（RHEL + PHP + My