昨天帮一个 5 年经验的大厂兄弟复盘拼多多三面，他也是一脸懵逼。

面试官给了一个真实的线上事故场景：“我们有一张 500 万数据的用户表，phone 字段加了普通索引。有一天，运营跑来反馈说查询巨慢。DBA 一看，发现一条简单的 SELECT * FROM user WHERE phone = 13800001234 居然走了全表扫描（ALL），把数据库 CPU 打满了。你觉得是为什么？”

这兄弟下意识地回答：

1. “是不是用了 LIKE '%...'？”（面试官：是等值查询）

2. “是不是用了 OR？”（面试官：单条件查询）

3. “是不是 phone 列上有函数？”（面试官：SQL 很干净，没加函数）

兄弟彻底没辙了：“那...那就是 MySQL 抽风了？” 

面试官叹了口气：“你对隐式类型转换和成本优化器（CBO）一无所知。”

其实，“索引失效” 绝不仅仅是 SQL 写法的问题，更多时候是 数据分布 和 类型定义 挖的坑。

今天带你拆解 MySQL 索引失效的 3 个“隐形杀手”，全是书上不怎么讲，但线上天天发生的血案。

杀手一：隐式类型转换（最坑爹的低级错误）

回到上面那个面试题。为什么 phone = 13800001234 会全表扫描？

真相是：在数据库定义里，phone 字段通常是 VARCHAR 类型（为了存前导0或者兼容性）。 但是！开发人员在写 SQL 时，为了省事，直接写了 数字（没加引号）：

-- 你的 SQL（埋雷版）SELECT * FROM user WHERE phone = 13800001234;

MySQL 的内心 OS：

“你给我传了个数字，但表里是字符串。那我得把表里的字符串转成数字才能比较啊！” 于是，SQL 等价于：

-- MySQL 实际执行的 SQLSELECT * FROM user WHERE CAST(phone AS UNSIGNED) = 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）的。

1. 走索引的成本 = 搜索二级索引树 + 回表（随机 IO）。

2. 不走索引的成本 = 全表扫描（顺序 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 %’之外，我认为最容易被忽视的杀手有三个：

1. 隐式类型转换（致命）：这是开发最容易犯的错。比如 varchar 字段传了 int 值，导致 MySQL 内部触发 CAST 函数，索引列变成了函数运算，直接导致 B+ 树失效。但反过来 int 字段传字符串通常是安全的。

2. 成本优化器的选择（CBO）：MySQL 选不选索引，取决于 Cost（成本）。如果查询条件命中率太高（比如筛选出了 30% 以上的数据），导致回表（随机 IO）的成本超过了全表扫描（顺序 IO），优化器会主动放弃索引。解决办法是利用覆盖索引减少回表。

3. 排序失效（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 优化器有时候比你想象的“聪明”，也比你想象的“蠢”。

https://mp.weixin.qq.com/s/VeeJxBH9V9gFl59g_fWIvg