昨天帮一个 5 年经验的大厂兄弟复盘拼多多三面，他也是一脸懵逼。
面试官给了一个真实的线上事故场景： “我们有一张 500 万数据的用户表，phone字段加了普通索引。有一天，运营跑来反馈说查询巨慢。DBA 一看，发现一条简单的SELECT * FROM user WHERE phone = 13800001234居然走了全表扫描（ALL），把数据库 CPU 打满了。你觉得是为什么？”
这兄弟下意识地回答：
- “是不是用了
LIKE '%...'？” （面试官：是等值查询） - “是不是用了
OR？” （面试官：单条件查询） - “是不是
phone列上有函数？” （面试官：SQL 很干净，没加函数）
兄弟彻底没辙了：“那...那就是 MySQL 抽风了？”
面试官叹了口气：“你对**隐式类型转换**和成本优化器（CBO）一无所知。”
其实， “索引失效” 绝不仅仅是 SQL 写法的问题，更多时候是数据分布和类型定义挖的坑。
今天带你拆解 MySQL 索引失效的3 个“隐形杀手” ，全是书上不怎么讲，但线上天天发生的血案。
杀手一：隐式类型转换（最坑爹的低级错误）
回到上面那个面试题。为什么phone = 13800001234会全表扫描？
真相是： 在数据库定义里，phone字段通常是**VARCHAR类型（为了存前导0或者兼容性）。 但是！开发人员在写 SQL 时，为了省事，直接写了数字**（没加引号）：
-- 你的 SQL（埋雷版）SELECT*FROMuserWHEREphone=13800001234;
MySQL 的内心 OS：
“你给我传了个数字，但表里是字符串。那我得把表里的字符串转成数字才能比较啊！” 于是，SQL 等价于：
-- MySQL 实际执行的 SQLSELECT*FROMuserWHERECAST(phoneASUNSIGNED)=13800001234;
后果： 索引列上被加了函数！
B+ 树的结构是按字符串排序的，不是按转换后的数字排序的。一旦在索引列上用了函数，B+ 树就废了。全表扫描，卒。
⚠️ 关键防杠细节（反向不失效）：
如果面试官反问：“那如果字段是INT，我传了字符串'123'会失效吗？”**
**
答案是：不会失效！ 因为 MySQL 会把输入的常量字符串转成数字，它动的是输入参数，没动数据库字段，所以索引依然有效。
String列传Int->挂。Int列传String->稳。 记死这个结论，面试能救命。
杀手二：回表成本太高，MySQL 弃用索引（反直觉）
场景复现： 有一张表t_order，status字段加了索引。 SQL：SELECT * FROM t_order WHERE status > 1。
- 情况 A：表里有 100 条数据，满足条件的有 10 条。 ->走索引。
- 情况 B：表里有 100 万条数据，满足条件的有 90 万条。 ->全表扫描（不走索引） 。
**
**
面试官问：为什么数据量大了反而不走索引？
真相（CBO 成本计算）： MySQL 的优化器是基于成本（Cost）的。
-
走索引的成本= 搜索二级索引树 +回表（随机 IO） 。
-
不走索引的成本= 全表扫描（顺序 IO）。
如果满足条件的数据太多（比如超过 30%），回表的代价（90 万次随机 IO）远远大于全表扫描的代价。 优化器非常聪明，它会觉得：“折腾那一趟干啥？直接扫表算了。”
✅ 避坑指南： 别以为建了索引就一定会被用。如果你的查询结果集很大（区分度不高），索引就是个摆设。**
**
优化方案：尽量使用**覆盖索引**（SELECT status, id ...），去掉SELECT *。只要不需要回表，MySQL 就会强制走索引了。
杀手三：Order By 导致的文件排序（FileSort）
场景复现： 联合索引idx_a_b_c (a, b, c)。 SQL：SELECT * FROM t WHERE a = 1 ORDER BY c。
很多人以为： “a用到了索引，c也在索引里，应该没问题吧？”
真相： 索引失效（部分），触发 FileSort。 根据最左前缀原则，索引的排序是：先按 a 排，a 相同按 b 排，b 相同按 c 排。 中间跳过了b，直接按c排序？ B+ 树里，跨过b之后，c是无序的！
MySQL 没办法利用索引的顺序，只能把数据取出来，在内存（Sort Buffer）里重新排一遍。这就是Using filesort，性能杀手。
✅ 避坑指南： 遵守最左匹配，不仅仅是WHERE，ORDER BY也要遵守。 要么ORDER BY b, c，要么WHERE a=1 AND b=常量 ORDER BY c。
面试标准答案模板（直接背）
下次被问“索引失效”，别只背“最左前缀”，直接甩出这套“底层原理 + 线上实战”的组合拳：
“索引失效在生产环境中非常常见，除了基础的‘最左前缀’、‘LIKE %’之外，我认为最容易被忽视的杀手有三个：
-
隐式类型转换（致命） ：这是开发最容易犯的错。比如
varchar字段传了int值，导致 MySQL 内部触发CAST函数，索引列变成了函数运算，直接导致 B+ 树失效。但反过来int字段传字符串通常是安全的。 -
成本优化器的选择（CBO） ：MySQL 选不选索引，取决于Cost（成本） 。如果查询条件命中率太高（比如筛选出了 30% 以上的数据），导致回表（随机 IO）的成本超过了全表扫描（顺序 IO） ，优化器会主动放弃索引。解决办法是利用覆盖索引减少回表。
-
排序失效（FileSort） ：联合索引中，如果中间断层（比如
WHERE a=1 ORDER BY c跳过了 b），索引的有序性就利用不上了，MySQL 必须进行文件排序。这点在做分页查询时要特别小心。
所以，分析 SQL 慢查询，不能光看有没有索引，必须结合**EXPLAIN的type、key_len和Extra**（是否 Using filesort/index condition）来综合判断。”
老哥最后再唠两句
兄弟，数据库这块，EXPLAIN是你的亲爹。 代码写完了，上线前必须拿 EXPLAIN 跑一遍。 看到type = ALL，赶紧改； 看到Extra = Using filesort，赶紧改； 看到key_len不对（没完全命中联合索引），赶紧改。
别信什么“理论上应该走索引”，MySQL 优化器有时候比你想象的“聪明”，也比你想象的“蠢”。
