前缀匹配（如LIKE 'abc%'）能走B+树索引，因等价于范围查询；后缀或全模糊（LIKE '%abc%'）无法使用普通索引，易致全表扫描，应视场景选用全文索引、字段拆分或外部搜索引擎。
前缀匹配能走索引，后缀或全模糊基本绕不开全文索引或数据改造。 直接用 LIKE '%xxx%' 查千万级表，十有八九会触发全表扫描，响应时间从毫秒跳到秒级甚至超时。别硬扛，得按匹配模式选路子。
LIKE 'abc%' 为什么能用上索引？
B+ 树索引天然支持范围查找，LIKE 'abc%' 等价于 name >= 'abc' AND name ，MySQL 可以快速定位起始位置并顺序遍历符合条件的叶子节点。
- 必须确保查询字段上有普通 B-TREE 索引，例如：
CREATE INDEX idx_name ON users(name); - 如果字段很长（比如
VARCHAR(500)），可考虑前缀索引节省空间：CREATE INDEX idx_name_prefix ON users(name(20));，但要注意截断后是否仍能区分大部分值 - 避免在索引列上套函数，比如
LOWER(name) LIKE 'abc%'—— 这会让索引失效，除非你额外建函数索引（MySQL 8.0+）：CREATE INDEX idx_lower_name ON users((LOWER(name)));
LIKE '%abc' 或 LIKE '%abc%' 怎么办？
常规索引完全无效，因为无法确定扫描起点。硬查等于放弃性能，必须换思路：
- 后缀匹配（
LIKE '%abc'）：建反向列 + 前缀索引，例如：ALTER TABLE files ADD COLUMN name_rev VARCHAR(255) AS (REVERSE(name)); CREATE INDEX idx_name_rev ON files(name_rev);，查询改写为：SELECT * FROM files WHERE name_rev LIKE REVERSE('abc') || '%'; - 全模糊（
LIKE '%abc%'）：优先上FULLTEXT索引，尤其是字段内容较长、语义相关时。注意：MATCH(title, body) AGAINST('abc')不等价于LIKE，它按词切分、去停用词、支持自然语言/布尔模式，更适合“搜索”而非“字符串包含” - 若业务真需要字面级包含且数据量不大，可接受临时性能损耗，就加
LIMIT控制返回条数，并确保有高选择性过滤条件（如时间范围、状态字段）前置：WHERE status = 1 AND created_at > '2025-01-01' AND content LIKE '%abc%'
FULLTEXT 索引有哪些坑？
不是所有 LIKE 场景都适合直接切 FULLTEXT，尤其当字段含大量短字符串、特殊符号或需精确大小写/标点匹配时：
- 默认最小词长是 4（
ft_min_word_len），'abc'这种三字母词搜不到，得调低配置并重建索引 - 中文支持依赖插件和版本：InnoDB 从 5.7.6 起才原生支持中文分词，之前得靠第三方插件或自己分词入库
-
AGAINST不支持通配符组合，比如AGAINST('abc* def*')是合法的，但AGAINST('ab*c')会报错 —— 它不是正则，而是布尔语法 - 全文索引不适用于主键或唯一性校验场景，它只为检索服务，不能替代约束
什么时候该放弃 MySQL 自带方案？
当单表行数破亿、QPS 高、且模糊条件多变（拼音、近义词、错别字、模糊音）时，MySQL 的全文能力就明显吃力了。
- 同步延迟容忍度低的业务，别直接上 Elasticsearch；可先用 Canal / Debezium 捕获 binlog，再推送到 ES，比定时导出更实时
- Sphinx 更轻量，适合中小团队做垂直搜索，但社区活跃度和生态不如 ES
- 如果只是想加速“文件名后缀”“邮箱域名”这类结构化模糊，其实拆字段更简单：
ALTER TABLE users ADD COLUMN domain VARCHAR(64) AS (SUBSTRING_INDEX(email, '@', -1))，然后对domain建普通索引
真正难的不是选哪种技术，而是厘清“用户到底在找什么”——是关键词、是拼写片段、还是语义相似？模糊背后的需求没定义清楚，再好的索引也救不了查询逻辑本身。
