当前位置：首页 > news >正文

面试官: MySQL LIKE索引失效原因解析（答案深度解析）持续更新

news 2026/4/16 23:06:53

面试题：`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 的高级优化手段