全文索引基礎
- 索引類型：
FULLTEXT
- 支援引擎：MyISAM (5.6 前)、InnoDB (5.6+)
- 適用欄位：
CHAR、VARCHAR、TEXT 及其變體
- 語言支援：內建停用詞表、詞幹提取 (需配置
ngram 解析器支援中文)
建立全文索引
-- 建表時同步建立
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX ft_title_content (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 既有表新增 (大表建議離線/低峰期)
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);
-- 或使用 CREATE INDEX
CREATE FULLTEXT INDEX ft_title ON articles (title);
全文搜尋查詢模式
| 模式 | 語法 | 特點 | 範例 |
|---|
| 自然語言 | MATCH(col) AGAINST('keyword') | 相關性排序、預設模式 | SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL 優化'); |
| 布林模式 | MATCH(col) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE) | 支援 + - * ~ "" 操作符 | SELECT * FROM articles WHERE MATCH(content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE); |
| 查詢擴展 | MATCH(col) AGAINST('keyword' WITH QUERY EXPANSION) | 兩階段：先自然語言、再用高相關詞擴展搜尋 | SELECT * FROM articles WHERE MATCH(content) AGAINST('資料庫' WITH QUERY EXPANSION); |
布林模式操作符速查
| 操作符 | 含義 | 範例 |
|---|
| + | 必須包含 | +MySQL |
| - | 必須不包含 | -Oracle |
| * | 萬用字元 (僅尾部) | optimiz* |
| ~ | 降低相關性 (軟排除) | ~slow |
| "" | 精確短語 | "query cache" |
中文全文索引：ngram 解析器
-- my.cnf 啟用
[mysqld]
ngram_token_size = 2 # 2-gram (雙字), 可調 1-10
-- 建立索引指定解析器
CREATE TABLE articles_cn (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
content TEXT,
FULLTEXT INDEX ft_content (content) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 查詢 (自動按 ngram 分詞)
SELECT * FROM articles_cn WHERE MATCH(content) AGAINST('資料庫優化' IN BOOLEAN MODE);
大數據量優化策略
- 批次匯入優先，再建索引：
-- 關閉索引 (MyISAM)
ALTER TABLE articles DISABLE KEYS;
-- 大量 INSERT/LOAD DATA
-- 重新開啟索引
ALTER TABLE articles ENABLE KEYS;
- InnoDB 線上建索引 (5.6+)：
ALGORITHM=INPLACE, LOCK=NONE 減少鎖定
- 分區表 + 全文索引： 按時間/分類分區，各分區獨立索引
- 搜尋服務外掛： 資料量 > 億級建議接入 Elasticsearch/Solr，MySQL 全文索引為輔
- 停用詞表自訂：
ft_stopword_file 指定業務專屬停用詞
- 最小詞長調整：
ft_min_word_len (MyISAM) / innodb_ft_min_token_size (InnoDB)，預設 3/3，中文建議 1-2
效能測試與監控
-- 查看索引統計
SHOW TABLE STATUS LIKE 'articles'\G
-- Index_length: 索引大小
-- 慢查詢監控
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
-- 關注 MATCH...AGAINST 執行時間
-- 索引碎片整理 (MyISAM)
OPTIMIZE TABLE articles;
-- InnoDB 線上重建
ALTER TABLE articles ENGINE=InnoDB; -- 或 ALTER TABLE articles ALGORITHM=INPLACE, LOCK=NONE;
結語
MySQL 全文索引適合**中小型資料量、簡單搜尋需求、不願意維護外部搜尋引擎**的場景。核心要點：**InnoDB 5.6+ 原生支援、ngram 解析器解決中文、批次匯入後建索引、布林模式精準控制、大數據量轉向 Elasticsearch**。定期監控索引大小、查詢延遲、碎片程度，及時優化重建。
