拼多多三面挂了！问 “索引明明建了，为什么不生效？”，我背了最左前缀，面试官：你只懂皮毛。-编程实验室

昨天帮一个 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 必须进行文件排序。这点在做分页查询时要特别小心。