LIKE '%xxx' 不走索引，因B+树依赖有序前缀而左通配无确定起点；可行方案仅全文索引或生成列反转字符串实现后缀转前缀匹配。
LIKE '%xxx' 这类左通配符查询，MySQL 原生 B+ 树索引完全无效，必须换思路——全文索引或生成列倒排是唯二靠谱的落地方案。
为什么 LIKE '%xxx' 一定不走索引
MySQL 的 B+ 树索引依赖「有序前缀」，而 '%xxx' 没有确定的起始点，优化器无法定位扫描边界，只能全表扫描。EXPLAIN 中会看到 type: ALL 或 key: NULL，哪怕字段上建了 INDEX(name) 也毫无作用。
- 注意：即使加了
FORCE INDEX，也不会生效——这不是优化器选择问题，是索引结构根本不支持 -
LIKE 'xxx%'可用索引，LIKE '%xxx%'和LIKE '%xxx'同样失效 - 函数包裹（如
LOWER(name) LIKE '%xxx%'）会让索引彻底作废，连覆盖索引都救不了
用 FULLTEXT 索引替代 LIKE 的实操要点
FULLTEXT 是 MySQL 原生支持的分词检索方案，适合标题、简介等中长文本字段，但对单字搜索、短关键词或高精度匹配有约束。
- 建表时加索引：
ALTER TABLE articles ADD FULLTEXT(title, content)；已有表可在线添加，但需注意锁表现象 - 查询必须用
MATCH() AGAINST()，不能混用LIKE：SELECT * FROM articles WHERE MATCH(title) AGAINST('数据库' IN NATURAL LANGUAGE MODE) - 中文需启用
ngram分词器（MySQL ≥ 5.7.6），并在配置中设ft_parser=ngram；默认 ngram_token_size=2，搜单字要改ft_min_word_len=1并重建索引（风险高，慎动） - 区分大小写由字段
COLLATION决定，建议统一用utf8mb4_0900_as_cs避免隐式转换导致失效
用生成列 + 反转字符串实现「后缀匹配」索引
当业务强依赖 WHERE name LIKE '%john'（即后缀匹配），且无法改前端逻辑时，可用生成列兜底。本质是把后缀问题转为前缀问题。
- 添加存储型生成列：
ALTER TABLE users ADD COLUMN name_reversed VARCHAR(100) AS (REVERSE(name)) STORED - 对其建普通索引：
CREATE INDEX idx_name_rev ON users(name_reversed) - 改写查询：
WHERE name_reversed LIKE CONCAT(REVERSE('john'), '%')—— 此时能命中idx_name_rev - 注意：
STORED列会占用磁盘空间，写入性能略降；VIRTUAL不落盘但无法建索引
别踩这些坑
很多优化尝试失败，不是方案不行，而是细节失控。
-
INSTR(name, 'xxx') > 0看似简洁，但完全不走索引，性能和LIKE '%xxx%'一样差 -
REGEXP比LIKE更慢，且 100% 无法利用索引，纯属加重负担 - 联合索引里把模糊字段放前面，比如
INDEX(status, name)，对name LIKE '%xxx'依然无效——B+ 树只认最左前缀 - 字符集不一致会触发隐式转换：
SELECT COLLATION(name)和SELECT COLLATION('xxx')必须相同，否则索引失效
真正卡住的从来不是 SQL 写法，而是没意识到：MySQL 的索引只服务「确定起点」的查找。一旦需求要求任意位置匹配，就得在存储层做取舍——要么接受 FULLTEXT 的语义妥协，要么用生成列把问题变形，而不是硬刚 LIKE。
