全文索引通過倒排索引實現自然語言搜索,是 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...
| 版本 | MyISAM | InnoDB |
|---|---|---|
| 5.5 及之前 | ✅ 支持 | ❌ 不支持 |
| 5.6 / 5.7 | ✅ | ✅ 支持 |
| 8.0+ | ✅ | ✅ + ngram parser 支持中日韓分詞 |
MySQL 內建全文索引基於空格/標點分詞,中文無分隔符,導致:
| 方案 | 架構 | 優點 | 缺點 | 推薦指數 |
|---|---|---|---|---|
| Elasticsearch + IK | 獨立集群 | 專業分詞、分佈式、高性能、生態豐富 | 運維成本高、數據同步延遲 | ⭐⭐⭐⭐⭐ |
| Sphinx / Coreseek | 獨立守護進程 | 輕量、支持中文、SQL 接口、實時增量 | 功能弱於 ES、社區較小 | ⭐⭐⭐⭐ |
| MySQL 8.0 ngram | 內建 | 零依賴、事務一致性、簡單 | 分詞粗糙、精度低、不支持詞典 | ⭐⭐ |
| 應用層分詞 + 倒排表 | 自研表 | 完全可控 | 開發維護成本極高 | ⭐ |
ADD FULLTEXT INDEX,效率遠高於建表時同步創建OPTIMIZE TABLE 碎片整理,保持索引健康選對全文搜索方案,是搜索業務成敗的關鍵技術決策。