like '%关键词%' 在 mysql 中无法使用 b+ 树索引，必然导致全表扫描；有效替代方案仅有全文索引、反向索引和外挂搜索引擎三种。
直接说结论：LIKE '%关键词%' 在 MySQL 中天然无法走 B+ 树索引，只要数据量上万，基本就是全表扫描。想靠改写 SQL 或换函数（比如 LOCATE、INSTR）来“绕过”这个问题，实测无效——它们一样不走索引，性能甚至更差。
为什么 LIKE '%关键词%' 一定慢？
MySQL 的 B+ 树索引是按字段完整值排序的，只支持“从左开始匹配”。LIKE '关键词%' 能用索引，是因为可以定位到“以关键词开头”的第一个位置；而 LIKE '%关键词%' 要找的是中间任意位置出现的子串，引擎没法跳过前面的数据直接定位，只能逐行扫描。
常见错误现象：
- 执行
EXPLAIN显示type: ALL，key: NULL - 查询耗时随数据量线性增长（10 万行可能 0.2s，100 万行就 2s+）
- 加了索引但完全没被用上，
SHOW INDEX看着索引在，就是不生效
真正有效的替代方案只有这三种
别在原表硬扛，得换思路：
-
全文索引 +
MATCH() AGAINST()：适合字段内容较长、语义较明确的场景（如文章标题、描述）。需注意：innodb_ft_min_token_size默认为 3，中文需配合分词插件（如ngram），否则单字搜不到 -
反向索引 +
REVERSE()：仅适用于固定后缀类查询（如查邮箱域名、文件扩展名）。例如查'%@gmail.com'，可建INDEX idx_email_rev (REVERSE(email))，再写成REVERSE(email) LIKE REVERSE('%@gmail.com') -
外挂搜索引擎（Elasticsearch / OpenSearch）：对实时性要求不高、且模糊查询频次高的业务（如商品搜索、用户昵称搜索），同步关键字段到 ES，用
wildcard或match_phrase查询，响应稳定在毫秒级
哪些“优化技巧”实际踩坑最多？
这些看似聪明的做法，线上已反复验证无效或引入新问题：
- 用
LOCATE('关键词', field) > 0或INSTR(field, '关键词') > 0：函数包裹字段导致索引失效，执行计划仍是ALL，且函数调用本身有开销 - 给字段加前缀索引（如
INDEX idx_name(20)）：对%关键词%完全无用，前缀索引只帮关键词% - 用
REGEXP替代LIKE：语法更灵活，但性能更差，且同样无法利用索引 - 强行在大字段上建普通索引：不仅没加速，还拖慢写入、增大磁盘占用，得不偿失
真正要动手前，先确认查询是否真的必须支持任意位置的模糊匹配——很多所谓“模糊需求”，其实能收敛成前缀、后缀、或枚举标签（比如用 category IN ('手机', '平板') 替代 name LIKE '%手机%'）。一旦落到 %关键词% 这个层面，就不是 SQL 层能优雅解决的问题了。
