全文索引是資料庫內建搜尋引擎的核心,MySQL 5.6+ 於 InnoDB、MyISAM 均支援。本文涵蓋建立語法、查詢模式、限制條件與中文分詞實戰方案。
傳統 LIKE '%keyword%' 無法使用索引,全文索引透過分詞建立倒排索引,支援自然語言、布林模式與查詢擴展三種搜尋模式,效能較 LIKE 提升數量級。
CREATE TABLE `article` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(200) DEFAULT NULL,
`content` TEXT,
PRIMARY KEY (`id`),
FULLTEXT KEY `ft_title_content` (`title`, `content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
ALTER TABLE article ADD FULLTEXT INDEX ft_title_content (title, content);
CREATE FULLTEXT INDEX ft_title_content ON article (title, content);
-- 自然語言模式(預設):按相關性排序
SELECT * FROM article
WHERE MATCH(title, content) AGAINST ('MySQL 優化');
-- 布林模式:支援 + - * ~ 運算子
SELECT * FROM article
WHERE MATCH(title, content) AGAINST ('+MySQL -Oracle' IN BOOLEAN MODE);
-- 查詢擴展:自動擴展相關詞
SELECT * FROM article
WHERE MATCH(title, content) AGAINST ('資料庫' WITH QUERY EXPANSION);
| 限制項目 | 說明 | 解決方案 |
|---|---|---|
| 引擎限制 | 5.6 前僅 MyISAM;5.6+ InnoDB 也支援 | 升級版本或轉換引擎 |
| 最小詞長 | 預設 4 字元(innodb_ft_min_token_size/ft_min_word_len) | 調整參數並重建索引 |
| 停用詞 | 內建停用詞表會被忽略 | 自訂 innodb_ft_stopword_table |
| 中文分詞 | 內建僅支援拉丁語系,中文無法分詞 | 採用 ngram parser 或外部分詞器 |
| 50% 門檻 | 自然語言模式下出現超過 50% 行的詞會被忽略 | 改用布林模式 |
ALTER TABLE article
ADD FULLTEXT INDEX ft_zh (title, content)
WITH PARSER ngram;
keywords TEXTkeywords 建立全文索引MATCH(keywords) AGAINST(...)資料量超過千萬、需高亮、同義詞、模糊匹配時,建議接入 Elasticsearch / OpenSearch / Sphinx(Coreseek)。
innodb_ft_cache_size、innodb_ft_total_cache_sizeOPTIMIZE TABLE article; 合併碎片化的倒排索引Innodb_ft_index_cache_size、Innodb_ft_index_table_size善用全文索引,讓資料庫內建搜尋能力滿足 80% 的業務場景,超大規模再上專業引擎。