面试题：LIKE为什么会失效？——索引失效的底层逻辑与实战避坑指南
⚠️ 这是 MySQL 索引优化中高频踩坑点，90% 的候选人只答出“以
%开头就失效”，但面试官真正想听的是：为什么失效？底层发生了什么？有没有例外？怎么验证？
一、概念解释：LIKE失效 ≠LIKE不能用，而是「索引失效」
LIKE是 SQL 模糊匹配操作符，语法：column LIKE pattern- 「失效」特指：MySQL 无法使用 B+ 树索引快速定位数据，被迫退化为全表扫描（Full Table Scan）
- 关键结论先行：
✅LIKE 'abc%'→可用索引（最左前缀匹配）
❌LIKE '%abc'→索引失效（无法定位起始位置）
⚠️LIKE '%abc%'→索引失效（两端模糊，无起点可言）
二、原理说明：B+ 树索引的「有序性」是核心约束
MySQL 的 InnoDB 引擎使用B+ 树索引，其本质是有序数组 + 多路搜索树。索引能加速查询，前提是能「从某个确定的起点开始向右遍历」。
🔍 举个真实例子（假设name字段有 B+ 树索引）：
CREATETABLEuser(idINTPRIMARYKEY,nameVARCHAR(50),INDEXidx_name(name));-- 插入数据（按字典序存储在 B+ 树叶子节点）：-- 'Alice', 'Bob', 'Charlie', 'David', 'Eve', 'Frank'✅
WHERE name LIKE 'Da%'
→ MySQL 找到第一个>= 'Da'的记录（即'David'），然后向右顺序扫描所有以'Da'开头的值→高效！❌
WHERE name LIKE '%vid'
→ 要找结尾是'vid'的名字（如'David','Livid'），但 B+ 树只按完整字符串排序，不存反向索引。
→ MySQL 无法知道'vid'在哪个分支，只能从根节点一路查到所有叶子节点 →全表扫描！
💡 类比理解：就像查《新华字典》——
- 查「张*」→ 翻到「张」部首，往后扫即可（✅）
- 查「*伟」→ 你得把整本字典每个字都看一遍末笔是不是「伟」（❌）
三、示例代码：用EXPLAIN验证索引是否生效
-- 建表 & 插入测试数据CREATETABLEproduct(idINTPRIMARYKEY,titleVARCHAR(100),INDEXidx_title(title));INSERTINTOproductVALUES(1,'iPhone 15 Pro'),(2,'Samsung Galaxy S24'),(3,'Xiaomi 14 Ultra');-- ✅ 查看执行计划：key = idx_title → 索引命中EXPLAINSELECT*FROMproductWHEREtitleLIKE'iPhone%';-- ❌ key = NULL → 索引失效，type = ALL（全表扫描）EXPLAINSELECT*FROMproductWHEREtitleLIKE'%Pro';-- ⚠️ 即使加了索引，以下也失效（注意：MySQL 8.0+ 对 `LIKE '%abc%'` 仍无法用索引）EXPLAINSELECT*FROMproductWHEREtitleLIKE'%Galaxy%';📌 面试加分点：
LIKE 'abc%'和LIKE 'abc_'（下划线单字符）都能走索引；LIKE 'ab%c'（中间通配）→同样失效！因为'ab%c'不满足最左前缀连续性（B+ 树无法跳过中间段继续匹配）。
四、常见误区（面试官最爱追问！）
| 误区 | 正解 | 为什么错？ |
|---|---|---|
❌ “只要用了%就一定失效” | ✅ 只有%在模式开头才必然失效；'abc%'完全可用索引 | 忽略了 B+ 树「从左到右匹配」的本质 |
| ❌ “加了索引就一定能加速 LIKE” | ✅ 索引类型很重要！普通 B+ 树对%abc无效；但全文索引（FULLTEXT）或倒排索引（如 Elasticsearch）可支持后缀匹配 | 混淆了不同索引结构的能力边界 |
❌ “LIKE失效只能靠改 SQL” | ✅ 可用函数索引（MySQL 8.0+）或生成列+索引：ALTER TABLE product ADD COLUMN title_rev VARCHAR(100) AS (REVERSE(title)) STORED, ADD INDEX idx_rev (title_rev);→ 查询WHERE REVERSE(title) LIKE 'orP%' | 忽略了高版本 MySQL 的高级优化手段 |
五、生产建议：不止于“别写%abc”
- ✅优先用前缀匹配：设计字段时预留可索引的前缀（如订单号
ORD20240515XXXXX，查LIKE 'ORD20240515%'） - ✅后缀需求 → 改存储：存正向 + 反向两列，或用 Redis 做模糊缓存
- ✅大数据量模糊搜索 → 上专业方案：Elasticsearch / Solr（基于倒排索引，天生支持任意位置匹配）
- ✅必须用
%abc？加 LIMIT + 覆盖索引减少回表（虽仍全表扫，但至少不查磁盘行数据）
🎯终极总结一句话：LIKE失效不是LIKE的锅，而是你在要求 B+ 树做它做不到的事——它是一棵按完整值排序的树，不是一台万能模糊匹配引擎。理解索引结构，才能写出真正高效的 SQL。
更多Java面试题整理：
JVM面试题
MySQL面试题
Redis面试题
Spring面试题
完整面试题库：
https://myquotego.com/html/questions?_from=csdn_123_4
