面试题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*FROMproductWHEREtitleLIKEiPhone%;-- ❌ 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?_fromcsdn_123_4