搞后端开发的人几乎都遇到过同一个诡异场景SQL写得很直白表上索引也建了EXPLAIN一执行possible_keys那栏明明白白写着索引名结果key却是NULL直接全表扫描。线上一条慢查询就能把接口拖到两三秒数据库CPU飙上去紧接着是一连串连锁反应。MySQL索引失效这个话题既高频出现在面试题里也是日常排查慢SQL躲不开的硬仗。这篇文章我把自己这几年踩过的索引失效坑整理了一遍不单是罗列现象还会把每条失效背后的原理讲清楚配EXPLAIN实测步骤作为验证手段。看完之后你至少能自己定位八成以上的慢查询问题。1. 索引失效的本质先搞清楚索引到底是怎么“工作”的聊失效之前得先明白索引为什么快。MySQL里InnoDB的索引底层是B树它做的事情就是让数据按照索引列的值排列成一种“有序结构”。拿生活中的例子类比一本电话簿如果按姓氏拼音排好了你想找“张”姓直接翻到Z那一带就行但如果这本电话簿是按电话号码排的你想按姓氏找人就只能从头翻到尾。B树能加速查询本质是它把“无序的查找”变成“有序的定位”这个定位依赖三个前提条件列的值在索引里是排好序的所以等值查询可以快速定位到叶子节点。条件能直接和索引列进行比较中间不能隔着任何加工。查询逻辑是“定位到一小片范围”而不是“排除绝大部分数据”。索引失效说白了就是查询计划没办法利用索引的有序性和可比较性要么是索引树定位不了要么是定位成本反而比全表扫描更高优化器直接放弃。这里要强调一个容易误解的点索引失效不一定是“物理上不能走索引”很多时候是“优化器算完账之后觉得不划算”。比如一张表只有一千行你建了索引优化器发现全表扫描也就读一千个数据页比走索引回表还快那它就选全表扫描。这种也算广义的失效后面会单独展开。理解了这层我们再看那些具体的失效场景就顺理成章了——它们都是在破坏上面三个前提中的一个或几个。2. 最常见的索引失效场景与原理拆解2.1 隐式类型转换列和条件的“性别”对不上这是新手最容易踩的坑。表里某个字段是VARCHAR查询时却传了数字MySQL会隐式地把字符串列转成数字再比较。举个例子-- phone 是 VARCHAR(20)查询条件却是数字 SELECT * FROM user WHERE phone 13800138000;看着没毛病实际上MySQL内部的执行是CAST(phone AS SIGNED) 13800138000。一旦对索引列做了CAST索引列的有序排列就失效了——B树里存的是字符串的顺序而字符串转成数字后的顺序和字典序并不是一回事优化器就没法用索引树去定位。反过来也一样。如果列是INT条件传了字符串MySQL会尝试把字符串转数字这倒不会让索引失效因为转换发生在常量这一侧。但为了统一规范开发规范里通常都要求“什么类型的字段就传什么类型的值”避免任何一方出现隐式转换。排查技巧这类问题用EXPLAIN看type就能发现正常应该是ref失效后是ALL。修正方式是保证类型一致SELECT * FROM user WHERE phone 13800138000;2.2 对索引列使用函数或表达式DATE()、SUBSTR()这些操作我们经常会在查询条件里写WHERE DATE(create_time) 2025-01-01这种写法非常顺手但它会让create_time上的索引彻底失效。原因是索引里存的是create_time的原始值比如2025-01-01 08:30:00而查询条件是DATE(create_time) 2025-01-01MySQL需要对每一行的create_time做DATE()运算再把结果跟常量比较。这个运算结果在B树里不是连续排布的所以没办法从根节点往下定位只能扫描全表。真正正确的写法是改成范围查询-- 把函数操作改成对常量的范围限定 SELECT * FROM user WHERE create_time 2025-01-01 00:00:00 AND create_time 2025-01-02 00:00:00;这样索引列保持“裸列”B树就能直接沿着范围定位性能差别经常是几十倍。类似的还有WHERE YEAR(create_time) 2025、WHERE SUBSTR(name, 1, 2) 张、WHERE LENGTH(name) 5等等只要索引列被函数包住基本就是失效。表达式也是同一个道理比如WHERE id 1 100索引列参与了运算优化器没法直接拿id去B树里找只能每行算一遍。改成WHERE id 99就能走主键索引。2.3 前缀模糊查询LIKE %xxx为什么不行LIKE xxx%是可以走索引的B树的有序性支持“从左往右的匹配”。比如索引列存储了apple、apply、banana你查LIKE app%B树能找到app这个前缀的起始位置然后往后扫一小段这是典型的range扫描。但LIKE %apple或者LIKE %apple%就完全不一样了。后缀匹配没办法确定起点因为索引是按字符串完整顺序排的你只知道结尾是什么不知道开头在哪B树的优势完全用不上只能全表扫描。业务里确实有中缀模糊搜索的需求我的建议是能改成前缀匹配就改这是最简单有效的方案。中缀匹配需求多的话别死磕MySQL的LIKE考虑全文索引或者专业的搜索引擎。如果表数据量不大硬扛全表扫描也能接受但要心里有数数据量上来之后扛不住。在数据量小的阶段很多人不把这个当回事等表到百万行再改就很痛苦了。2.4 联合索引跳过引导列最左前缀原则联合索引是最容易出问题的部分。建一个(a, b, c)的联合索引B树是先按a排a相同再按b排b相同再按c排。这就像查字典先按拼音首字母再按音节最后按声调。你只提供“声调是第二声”是没法定位的但提供“拼音首字母是Z”就能定位。失效场景有三种典型-- 场景1完全跳过第一列 WHERE b 1 AND c 2; -- 场景2只用了第一列和第三列中间列缺失 WHERE a 1 AND c 2; -- 场景3第一列用了范围查询 WHERE a 1 AND b 2;场景1里索引的第一列a没有出现在条件里B树的根节点就不知道往哪走整个索引失效。场景2里a能用到索引定位但c用不到——因为a确定之后b是不确定的同一a值下有多个b值而c只有在b确定时才是有序的。场景3里a是范围条件b同样无法继续定位原理和2.5节讲的范围右侧列失效一致。最左前缀不是说“必须全部出现”而是“必须从第一列开始连续出现”。a 1 AND b 2能用到两列a 1 AND c 2只能用一列。设计联合索引时要把等值查询的列放在最前面范围查询的列往后放这是铁律。2.5 范围查询右侧的列失效范围之后全失效这个场景值得单独强调因为它特别隐蔽。联合索引(a, b, c)查询是WHERE a 1 AND b 10 AND c 1很多人以为三列都用了索引实际上只有a和b能用c直接失效。原因在于当限定a 1且b 10之后命中的这些行里c的排列是乱序的。B树只保证“b相同的那一组内部c有序”b不同时c的大小关系和b没有绑定关系。比如b11的c是50b12的c是1虽然b是连续递增的但c不是。因此优化器没法在c上继续做等值定位。这个问题的解法很直接在设计联合索引时把等值条件列放在前面范围条件列放在最后。(a, c, b)这样的顺序就比(a, b, c)更合理——a 1 AND c 1先定位b 10再扫范围。这里再强调一次联合索引的列顺序不是拍脑袋定的是根据SQL模式反推出来的。2.6 OR条件连接并集查询的尴尬WHERE a 1 OR b 2如果a和b都有单列索引MySQL有可能会用index_merge合并两个索引的结果集但这是特殊情况。更常见的情况是a有索引而b没有或者优化器评估合并成本太高最后整条SQL变成全表扫描。为什么OR会让索引失效因为OR表达的是“两个条件满足任意一个即可”在B树里没有一条路径能同时覆盖两个分支的并集。走索引的话要分别查两个分支然后合并去重全表扫描的话一遍扫完顺便判断两个条件。当两个分支各自的选择性都不高时全表扫描往往更快。实际项目中我遇到更多的是这种写法SELECT * FROM user WHERE status 1 OR name 张三;status的选择性很低name有索引但OR之后优化器直接全扫。改写方式是拆成两个查询再用UNION ALL合并SELECT * FROM user WHERE status 1 UNION ALL SELECT * FROM user WHERE name 张三;这里要注意UNION ALL不会去重如果业务上需要去重就换UNION。拆开后每个查询都能独立走索引性能提升立竿见影。另外如果OR两边的列都有索引且区分度都很高MySQL的index_merge也不差但依赖优化器做合并不如自己控制来得稳。2.7 索引列参与算术运算或隐式拼接和函数操作类似索引列一旦进入算术表达式索引就废了。看看这个常见写法-- 希望查出num*2等于20的记录 WHERE num * 2 20;MySQL无法用num的索引因为它要先对每一行的num做乘2运算才能跟20比较。改写方式是把表达式挪到常量一侧WHERE num 20 / 2;这里20 / 2是常量表达式MySQL在优化阶段就能算出来不涉及任何一行数据的计算。还有个容易忽略的场景是字符串拼接。比如WHERE concat(first_name, last_name) 张三即使你在first_name上建了索引works也用不上。这种“加工索引列”的思路本质上和函数操作是一回事破坏了索引列与B树之间的直接对应关系。2.8 否定式查询与空值判断NOT IN、!、IS NOT NULLWHERE status ! 1、WHERE id NOT IN (1, 2, 3)、WHERE status IS NOT NULL这类“排除型”条件B树往往帮不上忙。原因是索引擅长“定位命中目标”不擅长“确认哪些不需要”——要想知道哪些记录不等于某个值基本上要把整棵索引树扫一遍区别不大。判断逻辑很简单如果status只有两个值比如0和1status ! 1意味着要命中表中几乎全部数据这时候全表扫描确实比走索引快优化器放弃索引是正确决策。这类情况就算你用FORCE INDEX强行走索引性能也不会变好反而可能更差。IS NULL和IS NOT NULL是很多人的盲区。普通二级索引可以存NULL值但IS NOT NULL往往被优化器判定为扫描大量数据IS NULL在部分版本里可能用得上索引前提是索引区分度足够且统计信息认为划算。实务上这两种写法都不太指望索引能用默认值兜底就兜底比如给字段设置默认值0或空字符串业务查询就完全避开NULL判断。2.9 排序与分组filesort背后的索引失效ORDER BY和GROUP BY能不能用上索引要看排序字段和索引列的顺序、方向是否匹配。联合索引(a, b)执行ORDER BY b但WHERE里没有限定a由于索引先按a排序b的全局顺序是不连续的MySQL只能把结果集放到内存或磁盘里做filesort。更隐蔽的是排序方向的问题。ORDER BY a ASC, b DESC一个升序一个降序InnoDB的索引扫描可以正着走也可以倒着走但同一个索引树同时满足两列不同方向就比较困难大概率还是filesort。遇到排序慢优先考虑调整索引列顺序让排序字段成为索引的“紧邻排列”而不是直接加内存参数。filesort不一定比索引排序慢很多但当排序数据集远超sort_buffer_size时磁盘临时文件就会拖垮性能这时候通过索引消除排序的收益非常明显。2.10 优化器主动“降级”统计信息与成本判断前几节讲的都是SQL写法破坏索引结构这一节讲的是索引本身没问题但优化器“不领情”。即使SQL完全满足索引匹配规则如果优化器根据统计信息判断“走索引回表的成本 全表扫描成本”它就会放弃索引。触发这种情况的条件通常有查询条件命中表里过大的数据比例比如超过20%到30%。索引区分度低比如性别字段只有两个值走索引反而要多次回表。表数据量小几百行数据全扫也就几个页走索引反而多一次额外IO。统计信息过期MySQL对数据分布的估算不准。解决思路不是硬逼优化器而是先把SQL和数据分布看清楚。如果是因为命中比例确实太高走索引本来就无益如果是因为统计信息过期执行ANALYZE TABLE刷新一下统计信息往往就好了。实在优化器犯倔可以用FORCE INDEX指定索引但这属于最后手段我一般只在临时救火时用。3. 用EXPLAIN实测验证索引到底有没有失效理论讲再多不如动手查一次。EXPLAIN是MySQL自带的执行计划查看工具用法就是在SQL前面加EXPLAIN关键字-- 注意EXPLAIN也可以用于UPDATE、DELETE、INSERT EXPLAIN SELECT * FROM user WHERE phone 13800138000;3.1 先看四个关键字段执行EXPLAIN之后结果集里有几列需要重点关注字段含义判断标准type访问类型从好到差依次是system const eq_ref ref range index ALLkey实际使用的索引NULL代表没走索引rows预估扫描行数越小越好如果接近全表行数就是没用好索引Extra额外信息出现Using filesort、Using temporary要注意type是ALL的时候基本可以断定全表扫描。type是ref说明走了普通索引等值查询range说明走了范围扫描index表示扫描了整棵索引树但没用树定位能力——比如覆盖索引扫描全量这种虽然用了索引但效果不一定好。3.2 一个完整的对比实验建一张简单的测试表CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR(20) NOT NULL, create_time DATETIME NOT NULL, KEY idx_phone (phone), KEY idx_create_time (create_time) ) ENGINEInnoDB;先看失效写法EXPLAIN SELECT * FROM user WHERE phone 13800138000;结果里type大概率是ALLkey是NULLrows接近全表行数。这是因为VARCHAR列和数字常量比较发生了隐式CAST。改成正确写法EXPLAIN SELECT * FROM user WHERE phone 13800138000;这时候type变成refkey显示idx_phonerows骤降Extra里可能还出现Using index condition说明索引生效了。再试函数包裹的情况EXPLAIN SELECT * FROM user WHERE DATE(create_time) 2025-01-01;type是ALLkey是NULL。改成范围查询EXPLAIN SELECT * FROM user WHERE create_time 2025-01-01 00:00:00 AND create_time 2025-01-02 00:00:00;type变成rangekey显示idx_create_timerows和执行时间都有明显改善。3.3 key_len判断联合索引实际用到了几列key_len这个字段很多人忽略但它非常有用。它表示MySQL在索引里实际使用到的字节数通过计算可以反推联合索引到底生效了几列。假设一个联合索引(a, b, c)三列都是INT NOT NULL那么每列占4字节。如果key_len是4说明只用到了第一列a如果key_len是8说明用到了a和b两列如果是12三列全部用上。VARCHAR的计算稍微复杂VARCHAR(20)在utf8mb4字符集下最大长度20乘以4等于80字节再加2字节长度标识如果允许NULL再加1字节得出83字节。通过EXPLAIN之后核对key_len能快速发现“我以为索引全用上了实际上只用了前缀列”这类隐蔽问题。3.4 进阶用OPTIMIZER_TRACE看优化器的内心戏EXPLAIN告诉你怎么执行但有时候你想知道“为什么这么选”可以用OPTIMIZER_TRACE。MySQL会记录优化器做决策时的详细过程包括每个执行计划的成本估算、为什么不选某个索引等。SET SESSION optimizer_trace enabledon; SELECT * FROM user WHERE phone 13800138000; SELECT * FROM information_schema.OPTIMIZER_TRACE\G SET SESSION optimizer_trace enabledoff;输出里的rows_estimation能看到每个索引的预估扫描行数considered_execution_plans能看到最终候选方案。这个工具适合排查“明明索引存在但优化器不选”的疑难杂症看一次内部决策过程比猜半天强多了。4. 避坑指南与线上排查实录理论场景聊完了分享几个我在真实项目里遇到过的案例每一个都花了不少时间定位希望你能跳过这些坑。4.1 字符集不一致导致的隐式转换有一次排查线上慢查询order表和user表做JOIN关联字段都叫user_id各自也都建了索引但执行计划显示驱动表全表扫描。检查字段类型两个表都是BIGINT类型完全一致但就是不走索引。后来发现两张表的字符集不一样一张是utf8mb4一张是latin1。MySQL在做JOIN时如果两张表的字符集不同低优先级的字符集会被隐式转换成高优先级这个转换发生在关联字段上相当于再次对索引列做了一次隐式CAST。修复方式是统一两个表的字符集或者至少保证关联字段的字符集一致。这个坑非常隐蔽遇到JOIN不走索引时先查两张表的字符集和排序规则。4.2 WHERE DATE()查询拖垮接口的案例一个报表接口查询条件是WHERE DATE(create_time) CURDATE()数据量500万接口从1秒慢慢退化到3秒。EXPLAIN一看type是ALLkey是NULL。改成create_time 2025-01-01 00:00:00 AND create_time 2025-01-02 00:00:00之后执行时间从2秒多降到20毫秒左右接口响应直接恢复。改动只有一行SQL但性能差异是百倍级别。这个案例我讲过很多次因为它太典型了不是没建索引而是函数包裹把索引毁了。4.3 区分度低的列建了索引也别指望还有一个案例是给status字段建了索引字段只有两个值0和1数据分布是90%的1和10%的0。查询WHERE status 1的时候优化器选择全表扫描——这个选择完全正确因为命中90%的数据走索引意味着90%的行都要回表效率远不如直接全扫。遇到这类需求正确的做法不是优化SQL而是考虑数据分布本身。如果业务确实经常需要查“少数派”的0可以考虑把“0”单独摘出来建一个部分索引或者用其他手段避免扫描大量数据。4.4 常见问题速查表现象可能原因解决方式EXPLAIN显示key为NULLSQL写法破坏索引结构检查类型转换、函数包裹、LIKE前缀、OR连接等key_len明显小于预期联合索引只用了前缀列调整SQL或索引列顺序明明能走索引却全表扫描统计信息过期或命中比例过高ANALYZE TABLE刷新统计信息或评估SQL本身type是index但rows很大覆盖索引扫描但没利用定位能力增加等值条件缩小扫描范围JOIN不走索引关联字段类型或字符集不一致统一字段类型和字符集排序很慢ORDER BY字段与索引顺序不匹配调整索引设计或SQL写法4.5 写SQL时养成两个习惯最后说两个我自己很受用的习惯。第一个是“索引列独立原则”——凡是在WHERE、JOIN、ORDER BY、GROUP BY里出现的索引列保持它是“裸列”不要对它做任何函数、运算、拼接。这个习惯养成之后很多失效问题在写SQL的阶段就自动避免了。第二个是“每一条慢查询都先看EXPLAIN”——不要凭感觉猜先看type、key、key_len、rows、Extra这五个字段基本能定位百分之八十的问题。再解决不了就上OPTIMIZER_TRACE看优化器是怎么做成本决策的。排查次数多了你自然就能一眼看出SQL里的隐患。我个人的体会是索引本身是门“让数据有序可查”的手艺活理解得越透踩的坑就越少。写SQL时多想一步线上就能少熬一次夜。这个账怎么算都划算。