萬盛學電腦網

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

MySQL 全文索引實戰:創建、查詢模式、中文分詞限制與替代方案

全文索引通過倒排索引實現自然語言搜索,是 MySQL 內建的文本檢索能力。本文覆蓋創建語法、三大查詢模式、關鍵配置參數、MyISAM/InnoDB 版本差異、中文不支持的根因與替代方案,幫你選對技術棧。

創建全文索引

建表時同步創建

CREATE TABLE article (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT (title, content)  -- 多列聯合全文索引
) ENGINE=MyISAM;  -- 5.6 前僅 MyISAM 支持

事後添加

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

三大查詢模式

模式語法特點適用場景
自然語言MATCH(col) AGAINST('keywords')按相關性排序,忽略停用詞,<50% 閾值行被忽略通用搜索、相關性排序
布爾模式MATCH(col) AGAINST('+must -not' IN BOOLEAN MODE)支持 + - * "" ~ 運算符,不按相關性排序,無 50% 閥值精確控制、必含/必不含
查詢擴展MATCH(col) AGAINST('keywords' WITH QUERY EXPANSION)兩階段:先自然語言,再用高相關行詞擴展再查查詢詞太短、需擴展召回

實戰示例

-- 自然語言(返回相關性分數)
SELECT *, MATCH(title, content) AGAINST('MySQL optimization') AS relevance
FROM article
WHERE MATCH(title, content) AGAINST('MySQL optimization')
ORDER BY relevance DESC LIMIT 10;

-- 布爾模式:必含 MySQL、排除 Oracle、通配優化*
SELECT * FROM article
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle optimiz*' IN BOOLEAN MODE);

關鍵配置參數

[mysqld]
ft_min_word_len = 3          # 最小索引詞長度(默認 4),修改後需 REPAIR TABLE 重建
ft_max_word_len = 84         # 最大詞長
ft_stopword_file = ''        # 禁用內置英文停用詞表(或指定自定義文件)
innodb_ft_min_token_size = 3 # InnoDB 專用(5.6+)
innodb_ft_max_token_size = 84

修改生效流程

SET GLOBAL ft_min_word_len = 3;  -- 僅運行時生效
-- 必須修改 my.cnf 持久化
-- 重啟後對現有表重建索引
REPAIR TABLE article QUICK;  -- 或 ALTER TABLE article DROP INDEX ft_xxx, ADD FULLTEXT...

版本與引擎支持矩陣

版本MyISAMInnoDB
5.5 及之前✅ 支持❌ 不支持
5.6 / 5.7✅✅ 支持
8.0+✅✅ + ngram parser 支持中日韓分詞

⚠️ 核心限制:不支持中文分詞

MySQL 內建全文索引基於空格/標點分詞,中文無分隔符,導致:

  • 「數據庫優化」被視為單一詞「數據庫優化」
  • 搜「數據庫」無法命中「數據庫優化」
  • ngram parser (8.0+) 按 bigram 強制切分(「數據」「據庫」「庫優」「優化」),精度召回率雙低

中文全文搜索替代方案選型

方案架構優點缺點推薦指數
Elasticsearch + IK獨立集群專業分詞、分佈式、高性能、生態豐富運維成本高、數據同步延遲⭐⭐⭐⭐⭐
Sphinx / Coreseek獨立守護進程輕量、支持中文、SQL 接口、實時增量功能弱於 ES、社區較小⭐⭐⭐⭐
MySQL 8.0 ngram內建零依賴、事務一致性、簡單分詞粗糙、精度低、不支持詞典⭐⭐
應用層分詞 + 倒排表自研表完全可控開發維護成本極高⭐

最佳實踐建議

  1. 數據量 < 1000 萬、純英文/混合簡單中文:MySQL 8.0 ngram 可嘗試
  2. 數據量大、中文業務核心:首選 Elasticsearch,Binlog 同步(Canal/Debezium)
  3. 輕量級、無獨立集群資源:Sphinx/Coreseek
  4. 建索引前先導入全量數據,再 ADD FULLTEXT INDEX,效率遠高於建表時同步創建
  5. 定期 OPTIMIZE TABLE 碎片整理,保持索引健康

選對全文搜索方案,是搜索業務成敗的關鍵技術決策。

copyright © 萬盛學電腦網 all rights reserved