萬盛學電腦網

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

MySQL 全文索引建立與使用全攻略:從建表到中文分詞落地

全文索引是資料庫內建搜尋引擎的核心,MySQL 5.6+ 於 InnoDB、MyISAM 均支援。本文涵蓋建立語法、查詢模式、限制條件與中文分詞實戰方案。

全文索引基礎概念

傳統 LIKE '%keyword%' 無法使用索引,全文索引透過分詞建立倒排索引,支援自然語言、布林模式與查詢擴展三種搜尋模式,效能較 LIKE 提升數量級。

建立全文索引三種方式

1. 建表時同步建立

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;

2. 既有表新增索引

ALTER TABLE article ADD FULLTEXT INDEX ft_title_content (title, content);

3. 獨立建立語句

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% 行的詞會被忽略改用布林模式

中文全文搜尋實戰方案

方案 A:MySQL 5.7.6+ 內建 ngram parser(零依賴)

ALTER TABLE article 
ADD FULLTEXT INDEX ft_zh (title, content) 
WITH PARSER ngram;
  • 將中文按 bigram(雙字元)切分,無需外部元件
  • 搜尋時自動匹配:「資料庫」→「資料」「料庫」
  • 缺點:索引體積較大、精確度不如專業分詞

方案 B:外部分詞 + 儲存分詞結果(高效能)

  1. 應用層用 Jieba、IK Analyzer 等分詞
  2. 將分詞結果存入獨立欄位 keywords TEXT
  3. 對 keywords 建立全文索引
  4. 查詢時同樣分詞後送入 MATCH(keywords) AGAINST(...)

方案 C:專業搜尋引擎(企業級)

資料量超過千萬、需高亮、同義詞、模糊匹配時,建議接入 Elasticsearch / OpenSearch / Sphinx(Coreseek)。

效能優化建議

  • 先匯入資料再建索引:批次建立比逐筆維護快數倍
  • 調整緩衝區:innodb_ft_cache_size、innodb_ft_total_cache_size
  • 定期優化:OPTIMIZE TABLE article; 合併碎片化的倒排索引
  • 監控指標:Innodb_ft_index_cache_size、Innodb_ft_index_table_size

善用全文索引,讓資料庫內建搜尋能力滿足 80% 的業務場景,超大規模再上專業引擎。

copyright © 萬盛學電腦網 all rights reserved