Jason's Blog

简体中文

English

简体中文

English

外观

MySQL InnoDB 全文索引失效故障文档 & 文本检索方案对比手册

约 1504 字大约 5 分钟

2026-08-04

一、故障问题记录：MyISAM 迁移 InnoDB 全文索引失效

1. 故障基本信息

  • 故障模块：用户全文检索功能

  • 涉及数据表：tb_user

  • 触发操作：执行 ALTER TABLE tb_user ENGINE = InnoDB，将数据表从 MyISAM 引擎迁移至 InnoDB 引擎

  • 故障现象：检索接口无报错、SQL 执行正常，但所有关键词查询均零命中，检索功能完全失效

  • 影响范围：用户昵称、账号等全文检索业务

2. 故障根因分析

MyISAM 与 InnoDB 的 FULLTEXT 全文索引底层实现完全不兼容：

InnoDB 全文检索依赖数据库内部一组专属 FTS 辅助系统表（fts_*），通过数据表唯一 table_id 与业务表强绑定，用于存储倒排索引数据、分词映射关系。

执行引擎迁移语句时，数据库仅迁移数据表行数据、常规 B+ 索引，不会重建 InnoDB 专属的 FTS 倒排索引结构。此时通过 show create table 仍可查询到全文索引元数据定义，但底层无有效倒排检索数据，最终导致查询无结果、功能失效。

额外中文适配坑点：MySQL 默认全文分词器仅支持英文空格分词，中文检索必须依赖 ngram 分词器，否则即便索引重建成功，中文搜索依旧失效。

3. 修复方案

删除失效旧全文索引，重新创建适配中文场景的 ngram 全文索引：

ALTER TABLE tb_user 
DROP INDEX 原有全文索引名,
ADD FULLTEXT INDEX ft_user_search(检索字段) WITH PARSER ngram;

4. 复盘规范与上线约束

  • 数据表引擎迁移、大表 DDL 变更时，常规 B+ 索引可平滑迁移，FULLTEXT 全文索引、空间索引无法自动兼容重建，需手动重建；

  • 禁止仅通过表元数据判断索引有效性，索引变更、引擎迁移后，必须手动构造中英文测试用例，完成检索功能回归；

  • 所有中文场景全文索引，强制指定 ngram 分词器，纳入开发规范。

二、InnoDB 文本检索方案对比：LIKE 模糊查询 VS FULLTEXT 全文索引

1. 核心原理说明

1.1 LIKE 模糊查询

基于 B+ 树索引实现纯字符串匹配，无分词、无倒排索引机制，仅通过通配符 %、_ 完成字符比对，分为两种匹配模式：

  • 前缀匹配（xxx%）：遵循 B+ 树有序特性，可正常走索引，查询性能高；

  • 中缀/后缀匹配（%xxx、%xxx%）：无法利用 B+ 索引，强制全表扫描，数据量增大后性能雪崩。

1.2 FULLTEXT 全文索引

InnoDB 专属检索方案，基于 FTS 倒排索引 实现：将文本内容分词后，建立「关键词-文档ID」映射关系，查询时直接匹配倒排表，无需全表扫描，支持检索相关性权重排序。

中文场景依赖 ngram 滑动分词机制，解决默认分词器不支持中文的问题。

2. 优缺点全面对比

对比维度LIKE 模糊查询FULLTEXT 全文索引
底层结构依赖 B+ 树，无专属检索结构独立 FTS 倒排索引，专属检索结构
索引有效性仅前缀匹配走索引，中缀/后缀全表扫描所有关键词检索均走倒排索引，性能稳定
中文支持原生支持中文，无需额外配置默认不支持中文，必须开启 ngram 分词器
分词能力无分词，仅匹配连续完整字符串支持分词匹配，可匹配片段关键词
检索能力仅简单通配符匹配，无相关性排序支持自然语言检索、布尔检索、权重排序
读写性能写无额外开销，大范围检索性能极差查询性能优异，写入需维护倒排索引，轻微损耗性能
运维风险结构稳定，无特殊兼容问题引擎迁移、大表 DDL 易出现索引失效，运维成本更高
存储开销仅依赖原生索引，开销极小倒排索引占用额外磁盘空间

3. 业务场景选型规范

3.1 优先使用 LIKE 模糊查询

  • 学号、手机号、编号等固定格式前缀检索（前缀匹配）；

  • 小数据量表简易查询，无分词、相关性检索需求；

  • 需要严格匹配连续完整字符串的业务场景。

3.2 优先使用 FULLTEXT 全文索引

  • 物品标题、描述、用户名等文本模糊检索场景；

  • 需要关键词分词匹配、相关性排序的业务；

  • 中小数据量（万级、百万级），无需部署 ES 中间件的场景。

3.3 两种方案均不适用（推荐 ES）

  • 大数据量、高并发检索场景；

  • 需要语义分词、同义词、检索高亮、复杂布尔筛选；

  • 需要将检索压力与 MySQL 数据库解耦的生产场景。

4. 核心总结与技术取舍

LIKE 模糊查询优势是轻量、无运维成本，仅适合前缀匹配简单查询，复杂文本检索存在致命性能短板；FULLTEXT 全文索引是 MySQL 原生最优文本检索方案，解决了模糊查询全表扫描问题，适配绝大多数中小型业务搜索需求，但存在引擎兼容问题、需适配中文分词。

在失物招领系统中，物品、用户检索业务数据量适中、并发可控，优先采用 ngram 全文索引 实现检索能力；后续业务体量扩张、检索复杂度提升，可平滑迁移至 Elasticsearch，实现检索与数据库解耦。

（注：部分内容可能由 AI 生成）