傳統 LIKE '%關鍵詞%' 無法利用索引,全表掃描導致:
全文索引通過倒排索引結構,實現毫秒級關鍵詞檢索,支撐百萬級數據。
| 版本 | 引擎支持 | 中文分詞 | 最小詞長 | 評價 |
|---|---|---|---|---|
| 5.6 前 | MyISAM Only | ❌ 無 (按空格分詞) | ft_min_word_len=4 | 中文不可用 |
| 5.6+ | MyISAM + InnoDB | ✅ ngram 插件 (按字符 n-gram) | ngram_token_size=2 | 可用但精度一般 |
| 5.7+ | MyISAM + InnoDB | ✅ MeCab 插件 (詞級分詞) | 可配置 | 推薦生產環境 |
[mysqld]
# 全文索引最小 token 長度 (ngram 模式下建議 2)
ngram_token_size = 2
# 創建表時指定解析器
CREATE TABLE articles (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
FULLTEXT INDEX ft_title_content (title, content) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 1. 安裝 MeCab 及 MySQL 插件
# CentOS: yum install mecab mecab-devel
# Ubuntu: apt install libmecab-dev mecab-ipadic-utf8
# 編譯安裝 libpluginmecab.so 至 MySQL plugin 目錄
# 2. 配置 my.cnf
[mysqld]
loose_mecab_rc_file = /etc/mecabrc
# 3. 創建表
CREATE TABLE articles (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
FULLTEXT INDEX ft_title_content (title, content) WITH PARSER mecab
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 1. 自然語言模式 (默認,按相關性排序)
SELECT *, MATCH(title, content) AGAINST('MySQL 性能優化' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 性能優化' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;
-- 2. 布爾模式 (精確控制,支持 + - * ~)
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle 優化*' IN BOOLEAN MODE);
-- +MySQL: 必須包含
-- -Oracle: 必須不包含
-- 優化*: 前綴匹配 (優化、優化器、優化建議)
-- 3. 查詢擴展模式 (自動擴展相關詞)
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('數據庫調優' WITH QUERY EXPANSION);
-- 大批量導入時:先導數據,後建索引 (快 10x+)
LOAD DATA INFILE 'data.csv' INTO TABLE articles;
ALTER TABLE articles ADD FULLTEXT INDEX ft_title_content (title, content) WITH PARSER ngram;
-- 定期優化碎片 (類似 OPTIMIZE TABLE)
OPTIMIZE TABLE articles; -- 重建全文索引,回收刪除標記空間
-- ❌ 錯誤:WHERE 子句中混用全文索引和普通索引,導致全表掃描
SELECT * FROM articles
WHERE MATCH(content) AGAINST('MySQL') AND status = 1;
-- ✅ 正確:子查詢/臨時表先用全文索引過濾,再關聯過濾
SELECT a.* FROM articles a
JOIN (
SELECT id FROM articles
WHERE MATCH(content) AGAINST('MySQL' IN BOOLEAN MODE)
) ft ON a.id = ft.id
WHERE a.status = 1;
-- ✅ 更好:生成列 + 普通索引 (MySQL 5.7+)
ALTER TABLE articles ADD COLUMN status_flag TINYINT GENERATED ALWAYS AS (status) VIRTUAL;
CREATE INDEX idx_status ON articles(status_flag);
-- 優化器可選擇先走 status 索引再回表全文檢索
-- 調整權重:標題權重 3x 內容
SELECT *,
(MATCH(title) AGAINST('MySQL' IN NATURAL LANGUAGE MODE) * 3 +
MATCH(content) AGAINST('MySQL' IN NATURAL LANGUAGE MODE)) AS weighted_score
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL' IN NATURAL LANGUAGE MODE)
ORDER BY weighted_score DESC;
會鎖表數小時。必須用 pt-online-schema-change 或先建空表導數據再建索引。
MyISAM 時代為了中文改 ft_min_word_len=1,會導致索引體積膨脹 10 倍,寫入極慢。InnoDB + ngram 完美解決此問題,不要動 ft_min_word_len。
ngram 無停用詞概念,MeCab 需配置 mecabrc 排除「的、了、在」等高頻無意義詞,否則索引膨脹且相關性差。
當需求超出 MySQL 能力時:
-- 全文索引大小
SELECT TABLE_NAME, INDEX_NAME,
ROUND(INDEX_LENGTH/1024/1024, 2) AS idx_size_mb
FROM information_schema.STATISTICS
WHERE INDEX_TYPE = 'FULLTEXT';
-- 查詢執行計劃
EXPLAIN SELECT * FROM articles
WHERE MATCH(content) AGAINST('MySQL' IN BOOLEAN MODE);
-- type=fulltext 表示走全文索引
總結:MySQL 5.7+ InnoDB + ngram/MeCab 可滿足中小型應用全文搜索需求。關鍵在於:正確選擇解析器、避免混合查詢導致索引失效、定期 OPTIMIZE 維護索引健康。大規模/複雜搜索場景請上 Elasticsearch。