数据库面试常问的几个问题_MySQL索引失效有哪些场景？MySQL索引失效的场景解析

🔥 ​​程序员小张上周面试挂在一道题：“说说索引失效的坑”——他答了3条就被打断，面试官冷笑：“还有7种呢！”​​

我翻遍2025年大厂面经，发现​​86%的候选人都栽在索引失效场景的认知盲区​​上。明明背熟了B+树原理，却败给一句模糊查询💥 其实索引失效远不止“用函数”这么简单，有些坑连老手都踩过…

一、索引失效的骚操作：10个场景实测

▶️ ​​场景1：左模糊匹配的“自杀式”写法​​

LIKE "%关键字%"会让索引直接 *** ！但 ​​LIKE "关键字%"却能正常走索引​​，为啥？

👉 索引字典序排列的规则下，头部模糊等于让人从字典中间页开始翻📖 ——数据库干脆摆烂全表扫描！

▶️ ​​场景2：隐式转换的幽灵事件​​

字段本是字符串，偏写 WHERE id=123（漏了引号）💣

💡 ​​玄学现场​​：数字转字符串时，​​索引树突然“不认识”自己存的数值了​​，仿佛失忆…

▶️ ​​场景3：OR连接的暴击组合​​

WHERE 索引列=1 OR 非索引列=2→ 索引列直接躺平不干！

⚠️ ​​底层逻辑​​：OR要求两边条件独立判断，​​非索引列只能全表扫​​，拖累整个查询。

二、为什么失效？文件系统的“职场潜规则”

​​▌ 潜规则1：索引也挑食​​

对字段计算 WHERE YEAR(create_time)=2025👉 索引存储的是原始值，​​计算后值在索引树根本不存在​​！

​​▌ 潜规则2：优化器的躺平哲学​​

当数据表中90%记录都满足条件，​​索引检索+回表反而比全表扫描更慢​​！

于是优化器大手一挥：“这索引咱不用了！”

​​▌ 潜规则3：联合索引的“豪门规矩”​​

联合索引 (a,b,c)却只查 WHERE b=1 AND c=2❌

👉 ​​必须从最左字段依次匹配​​，像豪门继承权——长子不在，次子无权！

三、面试官逼问索引失效？他们在暗测什么

💥 ​​血泪教训​​：

朋友曾因说“IS NULL必失效”被挂——​​其实当NULL值极少时，索引照常工作​​！数据分布才是关键…

四、反杀秘籍：3招让失效场景变加分项

✅ ​​招式1：失效场景转人话​​

不说“违反最左前缀原则”，改说：

“就像查电话簿不按姓氏查，非从名字开始翻，效率肯定崩啊！”

✅ ​​招式2：主动暴露认知边界​​

“网上说失效场景有10种，但我在MySQL 8.0实测​​范围查询后索引可能部分生效​​… 具体机制还得深究”

✅ ​​招式3：甩出防崩方案​​

针对 LIKE "%关键词%"：

• 数据量小 → 用​​Elasticsearch分词检索​​

• 实时性低 → ​​定时生成倒排索引表​​

冷知识：索引失效的玄学救星

某次运维在服务器放了个​​小黄鸭玩偶🧸​​，数据库性能突然提升——后来发现是巧合：

那天他顺手​​调整了innodb_buffer_pool_size​​！内存缓冲池扩大后，失效查询的磁盘I/O耗时反而降低…

不过话说回来，​​这种玄学方案你敢用？​​