mysql全文索引在千万级表上可能失效，因其仅支持自然语言模式下的单词级匹配，不支持中/后缀模糊、中文分词、短词检索（默认最小长度4）、布尔运算符，且索引构建慢、体积大、更新延迟高、无法与order by/limit深度分页协同。
全文索引在千万级数据上能显著加速模糊查询，但仅适用于自然语言类文本搜索；Elasticsearch更适合复杂模糊、前缀/后缀/拼音/分词等场景，且性能更稳定。直接用 LIKE 或 LOCATE 在千万级表上基本不可行。
为什么全文索引（FULLTEXT）在千万级表上仍可能失效
MySQL 的 FULLTEXT 索引不是“万能模糊加速器”，它有明确适用边界：
- 仅对
CHAR/VARCHAR/TEXT字段生效，且建索引时必须显式声明：ALTER TABLE articles ADD FULLTEXT(title, content) - 默认使用自然语言模式（
IN NATURAL LANGUAGE MODE），不支持通配符、布尔运算符（如+/-）或短词（少于 4 字符，默认ft_min_word_len=4），改配置需重启 MySQL，线上风险高 - 索引构建慢、体积大，千万级文本字段建
FULLTEXT可能卡住数小时，期间阻塞写入 - 不支持中文分词（InnoDB 原生无中文 tokenizer），直接对中文字段建全文索引效果极差，查“数据库”可能匹配不到“数据库优化”
-
MATCH ... AGAINST返回的是相关性排序结果，无法和ORDER BY created_at DESC混用，也不能带LIMIT跳过前 N 条——深度分页依然卡顿
什么时候该果断放弃全文索引，选 Elasticsearch
当你的模糊查询需求超出 MySQL 原生能力时，Elasticsearch 不是“过度设计”，而是必要选择。典型信号包括：
- 需要支持
LIKE '%关键词%'（中缀匹配）或LIKE '%关键词'（后缀匹配）——MySQL 全文索引完全不支持 - 用户输入带拼音、错别字、简繁体混输（如搜“微信”要命中“微Xin”“微信支付”“WeChat”）
- 要求实时性：新增记录秒级可搜，而 MySQL 全文索引更新延迟高、不支持近实时刷新
- 需聚合统计（如按城市、时间桶统计搜索热词）、高亮返回、同义词扩展
- 已有服务已用 Spring Data Elasticsearch 或 Logstash 同步机制，接入成本低
注意：SELECT ... FROM mysql_table WHERE MATCH(...) AGAINST(...) 和 curl -X GET 'es:9200/_search' 是两类查询，不能简单替换 SQL 就完事——ES 需要独立建索引、定义 analyzer、维护同步链路。
如果坚持用 MySQL，哪些操作真能见效
全文索引不是唯一出路，但盲目加索引或换函数也没用。以下操作经实测在千万级表上有明确收益：
- 对固定前缀场景（如搜索补全“张”→“张三”“张飞”），确保字段有 B+ 树索引，并严格走
WHERE name LIKE '张%'——此时EXPLAIN的type必须是range，rows应远小于总行数 - 禁用隐式转换：
phone是VARCHAR，就绝不用WHERE phone = 13812345678，必须写成WHERE phone = '13812345678'，否则索引直接失效 - 避免在索引列上用函数：
WHERE UPPER(name) = 'ZHANG'会跳过索引；改成WHERE name = 'zhang'并统一存储小写 - 用生成列 + 索引处理后缀匹配：
ALTER TABLE users ADD COLUMN name_reverse VARCHAR(100) GENERATED ALWAYS AS (REVERSE(name)) STORED, ADD INDEX idx_name_reverse (name_reverse)，查“abc”结尾就写WHERE name_reverse LIKE REVERSE('abc')% - 高频模糊字段单独抽成轻量搜索表，只存
id+search_text+ts，定期清理过期数据，降低主表压力
最容易被忽略的坑：同步延迟与一致性
无论选全文索引还是 Elasticsearch，最大的落地障碍不是性能，而是数据一致性。MySQL 主库写入后，ES 同步延迟若达秒级，用户刚发的帖子搜不到，体验直接崩坏。常见陷阱：
- 用双写（应用层同时写 MySQL 和 ES）——失败时难回滚，事务无法保证
- 用 binlog 解析同步（如 canal）——配置复杂，位点丢失或重复消费会导致 ES 数据错乱
- 没设好 ES refresh_interval（默认 1s），误以为“已写入就可搜”，实际可能延迟 1–2 秒
- 全文索引未定期
OPTIMIZE TABLE，碎片率高导致查询变慢，但该操作会锁表，千万级表执行一次可能数分钟
真实项目里，宁可接受 200ms 延迟的 ES 搜索，也不要赌 MySQL 全文索引在高并发下的稳定性——后者一旦卡住，整个 DB 连接池都可能被拖垮。
