您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 全文索引實戰指南：建立、配置、中文分詞限制與 Sphinx 替代方案

編輯：MySQL 綜合教學

前言

全文索引是 MySQL 內建的文字搜尋引擎，透過倒排索引實現自然語言/布爾模式搜尋。但 MySQL 內建全文索引僅支援 MyISAM（5.6 前）與 InnoDB（5.6+），且不支援中文分詞。本文詳細講解建立語法、配置參數、查詢模式、效能優化，並給出中文搜尋的生產級替代方案。

一、核心限制與前置知識

引擎限制：MySQL 5.6 前僅 MyISAM 支援；5.6+ InnoDB 也支援
語言限制：內建分詞器基於空白字元，無法正確分詞中文/日文/韓文
最小詞長：預設 ft_min_word_len = 4（MyISAM）或 innodb_ft_min_token_size = 3（InnoDB），過短詞被忽略
停用詞：內建停用詞表過濾常見詞（the、and 等），可自定義
50% 閾值：自然語言模式下，出現在超過 50% 行的詞會被忽略（布爾模式不受影響）

二、配置參數調整

MyISAM（`my.cnf`）

[mysqld]
ft_min_word_len = 3          # 最小詞長，改 3 可索引 3 字詞
ft_stopword_file = ''        # 空字串禁用停用詞，或指定自定義檔案路徑

修改後需重建索引：

REPAIR TABLE table_name QUICK;

InnoDB（動態參數，5.7+ 可線上修改）

SET GLOBAL innodb_ft_min_token_size = 2;
SET GLOBAL innodb_ft_stopword_table = 'db/stopwords';  -- 自定義停用詞表
-- 重建索引
OPTIMIZE TABLE table_name;

三、建立全文索引

1. 建表時同步建立

CREATE TABLE article (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,
  FULLTEXT (title, content)  -- 多欄聯合全文索引
) ENGINE=InnoDB;

2. 既有表新增

ALTER TABLE article ADD FULLTEXT INDEX ft_title_content (title, content);

3. 最佳實踐：先導數據再建索引

大量導入時，先建表導數據，再建全文索引，效率遠高於建表時同步建立。

四、全文搜尋查詢語法

1. 自然語言模式（預設）

SELECT * FROM article
WHERE MATCH(title, content) AGAINST('搜尋關鍵詞');

自動計算相關度，結果按相關度降序排列
不支援運算符，視為整體短語
受 50% 閾值影響

2. 布爾模式（推薦精準控制）

SELECT * FROM article
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle 全文索引' IN BOOLEAN MODE);

運算符	含義
`+`	必須包含
`-`	必須不包含
`*`	萬用字元（僅尾部），如 `MySQL*`
`"..."`	精確短語
`@distance`	近似搜尋，如 `"MySQL 索引"@10`
`>` `<`	調整相關度權重
`()`	分組
`~`	降低相關度（模糊匹配）

3. 查詢擴展模式

SELECT * FROM article
WHERE MATCH(title, content) AGAINST('資料庫' WITH QUERY EXPANSION);

兩階段：先自然語言搜尋，取高相關度行的關鍵詞再二次搜尋。適合查詢詞過短場景。

4. 取得相關度分數

SELECT *, MATCH(title, content) AGAINST('MySQL 全文索引') AS relevance
FROM article
WHERE MATCH(title, content) AGAINST('MySQL 全文索引')
ORDER BY relevance DESC
LIMIT 10;

或用 HAVING 過濾低分：

SELECT *, MATCH(title, content) AGAINST('MySQL 全文索引') AS rel
FROM article
HAVING rel > 0.01
ORDER BY rel DESC;

五、效能優化與維護

批量導入優化：LOAD DATA INFILE 導入後再 OPTIMIZE TABLE 建索引
定期重建：碎片導致效能下降，定期 OPTIMIZE TABLE 或 ALTER TABLE ... DROP INDEX ... ADD INDEX ...
監控索引大小：SHOW TABLE STATUS LIKE 'article'\G 觀察 Index_length
避免頻繁更新全文欄位：每次 UPDATE 觸發索引重建，考慮將全文欄位拆分到獨立表

六、中文全文搜尋：生產級替代方案

MySQL 內建全文索引無法用於中文生產環境。主流方案：

方案	架構	優點	缺點
Elasticsearch	獨立搜尋叢集，Logstash/Beats/Canal 同步 MySQL 數據	專業分詞（IK、jieba）、分散式、高併發、豐富查詢 DSL	運維複雜度高、資料最終一致性
Sphinx / Coreseek (Manticore)	索引器定期從 MySQL 抽取建立索引，搜尋端查詢 Sphinx 取 ID 再回表	輕量、支援中文分詞、SQL 介面熟悉、可即時索引	功能較 ES 簡單、社區活躍度降低
MySQL 8.0+ ngram parser	`FULLTEXT INDEX ... WITH PARSER ngram`	零外部依賴、支援中日韓、InnoDB 原生	精度較專業分詞器低、無法自定義詞典、相關度計算簡單

MySQL 8.0 ngram 範例

CREATE TABLE article_ngram (
  id INT PRIMARY KEY,
  content TEXT,
  FULLTEXT INDEX ft_content (content) WITH PARSER ngram
) ENGINE=InnoDB;

SELECT * FROM article_ngram
WHERE MATCH(content) AGAINST('全文索引' IN NATURAL LANGUAGE MODE);

七、常見坑點避雷

WHERE 條件中不能用別名：WHERE match_score > 0.1 錯誤，需用 HAVING 或子查詢
MATCH() 欄位順序必須與索引定義一致
AGAINST() 空字串會報錯，需應用層攔截
大量 INSERT/UPDATE 導致索引碎片，定期 OPTIMIZE TABLE
布爾模式下 + - 需緊貼詞，+ MySQL 視為 +MySQL

結語

MySQL 全文索引適合純英文、小規模、低維護成本場景。中文搜尋、大規模、高併發、複雜查詢需求下，Elasticsearch 是事實標準；輕量級專案可考慮 Manticore（Sphinx 繼任者）或 MySQL 8.0 ngram。選型原則：業務需求決定架構，不要為了省事將不合適的工具硬上生產。