MySQL全文索引优化：B树、默认FULLTEXT与n-gram实战对比

测试环境与方法

为了准确评估不同索引策略对全文搜索的影响，我们构建了一个包含1,000,000条随机生成的客户记录的数据集，字段包括ID、姓名（规范格式）和邮箱（混乱格式）。测试在MySQL 8.0.32 Docker镜像上进行，硬件配置为AMD 6800U、32GB RAM及NVMe SSD。

首先创建基础表结构：

CREATE TABLE `address_book` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(128) NOT NULL,
    `email` VARCHAR(128) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4;

方案一：无索引（全表扫描）

最基础的模糊查询不依赖任何索引，直接使用LIKE '%john%' AND LIKE '%doe%'语法。

优势与劣势： 写入速度快（约23分钟加载100万条数据），无额外存储空间消耗。但由于缺乏索引，MySQL需逐行扫描并使用Turbo Boyer-Moore算法进行匹配，查询极慢（约0.22秒/次）。EXPLAIN显示类型为ALL，扫描行数接近百万。

适用场景： 低频搜索的小数据集，或对实时性要求极低且不能增加存储成本的场景。

风险： 在高并发或大数据量下，全表扫描会耗尽I/O资源并可能阻塞其他需要排他锁的操作。

方案二：B树索引

B树索引是MySQL默认的索引类型，适用于精确匹配或前缀匹配。通过对name和email字段添加普通索引：

ALTER TABLE `address_book` ADD KEY (`name`), ADD KEY (`email`);

局限性： B树索引基于字符的前缀进行比较。如果搜索条件以通配符开头（如'%john%'），索引失效，退化为全表扫描。只有当通配符位于末尾（如'john%'）时才能利用索引加速。

测试结果： - 前缀匹配（name LIKE 'john%'...）：性能显著提升（0.003秒），EXPLAIN显示使用range索引。 - 模糊匹配（email LIKE '%john%'）：性能无改善（0.314秒），索引未被使用。

代价： 数据加载时间略微增加，且索引占用了约107MB的额外存储空间（约为数据量的1.5倍）。

方案三：默认FULLTEXT索引（词边界分词）

为了解决前缀匹配的限制，引入MySQL的原生FULLTEXT索引。默认解析器基于空格和标点符号进行分词，将连续文本视为一个Token。

操作方式：

ALTER TABLE `address_book` ADD FULLTEXT (`name`), ADD FULLTEXT(`email`);

查询语法： 必须使用MATCH ... AGAINST()，推荐使用BOOLEAN MODE以支持复杂的逻辑组合（如+john +doe）。

SELECT * FROM `address_book` 
WHERE MATCH(`name`) AGAINST('+johnie +doemel' IN BOOLEAN MODE); -- 耗时 0.001秒

性能表现： 速度极快（比无索引快200倍以上），直接定位行，无需二次过滤。

重大缺陷： 1. **最短Token限制**：默认情况下，查询短语长度若小于分词长度（通常是整个单词），则无法找到结果（例如搜索'+john +doe'可能返回空，因为数据库中可能是Johnnie）。 2. **存储开销巨大**：FULLTEXT索引占用空间约为原始数据的7倍（约470MB+）。 3. **写入延迟高**：数据加载时间延长至近30分钟。

方案四：n-gram解析器FULLTEXT索引

当业务需要支持子字符串搜索（如中文分词或部分拼音匹配）时，可使用n-gram解析器。它将文本拆解为长度为n的子串。

关键配置： n-gram大小由全局变量ngram_token_size控制，默认值为2。修改此值需重启MySQL服务器，且影响全局所有使用该解析器的表。

操作步骤：

-- 设置全局变量并重启后执行
ALTER TABLE `address_book` ADD FULLTEXT (`name`) WITH PARSER ngram, ADD FULLTEXT(`email`) WITH PARSER ngram;

性能权衡： - **优点**：成功匹配了john和doe这类短子串，解决了默认FULLTEXT的短板。 - **缺点**：性能显著下降。若搜索词长度大于n-gram大小，MySQL需多次查询索引并合并结果，导致耗时激增（从0.001秒跌至0.26秒甚至更慢）。 - **存储与写入**：索引体积依然庞大（约为数据的5倍），写入性能较差。

InnoDB FULLTEXT索引的维护陷阱

InnoDB的FULLTEXT索引存在一个严重的设计缺陷：删除数据不会自动清理索引中的过时条目，而是将其标记为“已删除”。随着删除操作的积累，查询效率会急剧下降，即使表中只剩少量活跃数据，查询也可能变得极慢。

恢复性能的方法： 1. OPTIMIZE TABLE：彻底重建表，耗时极长且会导致应用停机。 2. 启用innodb_optimize_fulltext_only = ON：允许在不重建整个表的情况下仅优化FULLTEXT索引。可通过innodb_ft_num_word_optimize控制每次优化的词数上限（最大10,000），需多次循环执行直至稳定。

替代方案建议： 对于高频、复杂的全文搜索需求，特别是涉及中文字符或高性能要求的场景，强烈建议使用外部搜索引擎如ElasticSearch或Sphinx，或将表切换至MyISAM引擎（尽管失去了事务支持）。PostgreSQL提供的GiST/GIN索引也是比MySQL原生全文搜索更成熟的选择。

以上就是MySQL全文搜索优化中B树、默认FULLTEXT与n-gram索引详细对比与评测，更多关于MySQL全文索引优化的资料请关注本站其它相关文章！