MySQL全文索引

在MySQL中，全文索引（Full-Text Index） 是一种专门为文本数据设计的索引类型，用于高效处理自然语言搜索（如文章内容、评论、关键词匹配等）。与传统的B-Tree索引相比，全文索引在处理大量文本数据时性能更优，且支持更复杂的搜索语法。

核心概念

1. 适用场景：

   • 文本字段（CHAR、VARCHAR、TEXT）的模糊搜索。
   • 关键词匹配、短语搜索、语义搜索等。

2. 索引原理：

   • 分词（Tokenizer）：将文本拆分为词元（Tokens）。
   • 去重和排序：构建倒排索引（Inverted Index），记录每个词元出现的位置。

3. 支持的存储引擎：

   • InnoDB（MySQL 5.6+）和 MyISAM。

创建全文索引

单字段索引

CREATE TABLE articles (id INT PRIMARY KEY,title VARCHAR(255),content TEXT,FULLTEXT(title, content)  -- 创建联合全文索引
);

ALTER TABLE添加索引

ALTER TABLE articles ADD FULLTEXT(title, content);

索引选项

• 最小/最大词长：通过ft_min_word_len和ft_max_word_len配置（默认4和84字节）。
• 停用词（Stopwords）：忽略常见词（如"the"、“and”），可自定义。

全文搜索语法

自然语言模式（Natural Language Mode）

SELECT * 
FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL索引优化' IN NATURAL LANGUAGE MODE);

• 匹配包含关键词的文档，按相关性排序。

布尔模式（Boolean Mode）

支持高级搜索操作符：

SELECT * 
FROM articles 
WHERE MATCH(title, content) AGAINST('+MySQL +"索引优化"'  -- 必须包含"MySQL"和短语"索引优化"IN BOOLEAN MODE
);

常用操作符：

• +：必须包含。
• -：必须排除。
• *：前缀匹配（如MySQL*匹配"MySQL"、“MySQL优化”）。
• "：短语匹配（如"数据库设计"）。

全文索引与LIKE的对比

特性	LIKE ‘%关键词%’	全文索引（MATCH AGAINST）
性能	全表扫描，数据量大时极慢	索引扫描，速度快
语法灵活性	仅支持简单模糊匹配	支持布尔逻辑、短语搜索、权重
分词处理	不支持，需手动处理	自动分词（如中文需自定义分词）
排序方式	无特定顺序	按相关性评分排序
适用场景	少量数据的简单模糊查询	大量文本的复杂搜索

中文全文索引优化

1. 分词问题：
   MySQL内置分词器对中文支持不佳（默认按空格分词），需使用第三方插件（如ngram分词器）：

   -- 启用ngram分词器
   ALTER TABLE articles ADD FULLTEXT(title, content) WITH PARSER ngram;
   

2. 最小词长调整：

   -- 修改配置文件my.cnf
   ft_min_word_len = 1-- 重建索引使配置生效
   ALTER TABLE articles DROP INDEX idx_ft;
   ALTER TABLE articles ADD FULLTEXT(title, content);
   

性能注意事项

1. 索引更新开销：
   全文索引的维护成本高于普通索引，写入操作可能变慢。

2. 查询优化：

   • 避免在MATCH字段上使用函数（如UPPER()），会导致索引失效。
   • 复合查询中，全文索引条件应放在WHERE子句的前面。

3. 索引大小：
   全文索引通常比原数据大，需预留足够磁盘空间。

EXPLAIN验证全文索引

EXPLAIN SELECT * FROM articles WHERE MATCH(title, content) AGAINST('MySQL');

• type列显示fulltext：表示使用了全文索引。
• Extra列显示Using where：表示通过全文索引过滤结果。

适用场景

• 全文索引不适用于 OLTP 场景。