萬盛學電腦網

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

MySQL 全文索引實戰:中文分詞配置、性能優化與避坑指南

為什麼需要全文索引?

傳統 LIKE '%關鍵詞%' 無法利用索引,全表掃描導致:

  • 10 萬行數據查詢 > 1 秒
  • 百萬級數據直接拖垮數據庫
  • 高併發下 CPU 100%,連接數耗盡

全文索引通過倒排索引結構,實現毫秒級關鍵詞檢索,支撐百萬級數據。

MySQL 內置全文索引局限

版本 引擎支持 中文分詞 最小詞長 評價
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 插件 (詞級分詞) 可配置 推薦生產環境

核心配置:啟用中文分詞

方案 A:ngram 解析器 (無需編譯,MySQL 5.7.6+ 內置)

[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;

方案 B:MeCab 解析器 (詞級分詞,精度更高,需編譯安裝)

# 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);

性能優化關鍵點

1. 索引維護策略

-- 大批量導入時:先導數據,後建索引 (快 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;  -- 重建全文索引,回收刪除標記空間

2. 查詢優化技巧

-- ❌ 錯誤: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 索引再回表全文檢索

3. 相關性評分調優

-- 調整權重:標題權重 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;

生產環境避坑指南

🚫 禁忌 1:大表直接 ALTER ADD FULLTEXT

會鎖表數小時。必須用 pt-online-schema-change 或先建空表導數據再建索引。

🚫 禁忌 2:ft_min_word_len=1 導致索引爆炸

MyISAM 時代為了中文改 ft_min_word_len=1,會導致索引體積膨脹 10 倍,寫入極慢。InnoDB + ngram 完美解決此問題,不要動 ft_min_word_len。

🚫 禁忌 3:忽略停用詞表

ngram 無停用詞概念,MeCab 需配置 mecabrc 排除「的、了、在」等高頻無意義詞,否則索引膨脹且相關性差。

替代方案:專業搜索引擎

當需求超出 MySQL 能力時:

  • Elasticsearch:複雜查詢、高亮、聚合、多租戶、水平擴展
  • OpenSearch:ES 開源分支,AWS 首選
  • Typesense/Meilisearch:輕量級、即時、適合中小應用
  • MySQL + Canal + ES 同步:主庫寫入,Binlog 同步至 ES,查詢走 ES

監控指標

-- 全文索引大小
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。

copyright © 萬盛學電腦網 all rights reserved