【大白话说Java面试题 第80题】【Mysql篇】第10题：MySQL 在什么条件下索引失效？

PDF大白话说Java面试题 — 03-Mysql篇第10题MySQL 在什么条件下索引失效回答核心考点大厂面试要求不仅罗列索引失效场景更要理解底层原理为什么失效、优化器决策逻辑为什么不走索引以及如何通过EXPLAIN分析和如何规避。面试官常追问“这个SQL走了索引吗为什么”1. 索引失效的本质索引失效本质上是MySQL查询优化器决定不使用索引原因可分为两类失效类型根本原因优化器行为无法利用索引查询条件破坏了索引的有序性或索引无法加速该查询无法使用索引或只能部分使用优化器放弃索引优化器估算全表扫描成本 使用索引成本主动选择全表扫描关键理解“索引失效不一定是索引真的坏了”而是MySQL认为用索引不如全表扫描快。2. 索引失效的完整场景分类分类一无法利用索引的有序性场景示例原理是否绝对失效不遵循最左前缀原则索引(a,b,c)WHERE b2联合索引按最左列排序缺最左列无法定位起始位置✅ 绝对失效LIKE前导模糊查询name LIKE %张三通配符在前无法判断前缀无法利用B树有序性✅ 绝对失效对索引列使用函数WHERE UPPER(name)ABC索引存原始值函数后值变了无法匹配✅ 绝对失效隐式类型转换WHERE phone13800138000phone是VARCHAR相当于CAST(phone AS SIGNED)索引列被函数处理✅ 绝对失效使用!或WHERE status ! done非等值查询扫描量大通常退化为全表扫描⚠️ 非绝对但常见使用IS NULL/IS NOT NULLWHERE age IS NOT NULL优化器根据NULL比例决定⚠️ 不一定失效范围查询后列失效索引(a,b,c)WHERE a1 AND b10 AND c3范围查询b后c无法用于索引查找⚠️ 部分失效c列失效跳过中间列索引(a,b,c)WHERE a1 AND c3跳过bc无法用于索引查找⚠️ 部分失效c列失效分类二优化器选择全表扫描场景示例原理如何确认表数据量极小几百行的配置表全表扫描一次I/O回表多次I/O反而慢EXPLAIN显示typeALL索引区分度低性别字段WHERE gendermale匹配50%数据回表随机I/O成本 顺序扫描全表rows接近表总行数统计信息过期大量数据变更后未ANALYZE优化器误判扫描行数SHOW INDEX看Cardinality查询返回大量数据WHERE id0匹配全表使用索引还需回表不如直接全表扫描filtered接近100%3. 各场景深度解析3.1 不遵循最左前缀原则-- 索引idx_a_b_c (a, b, c)-- ✅ 走索引完全命中 a, bSELECT*FROMtWHEREa1ANDb2;-- ❌ 不走索引缺少最左列 aSELECT*FROMtWHEREb2;-- ⚠️ 部分走索引a命中c用ICPSELECT*FROMtWHEREa1ANDc3;原理联合索引B树先按a排序a相同再按b排序b相同再按c排序。缺最左列a时无法确定起始搜索位置。3.2 LIKE前导模糊查询-- ✅ 走索引前缀匹配SELECT*FROMusersWHEREnameLIKE张%;-- ❌ 不走索引前导通配符SELECT*FROMusersWHEREnameLIKE%张;SELECT*FROMusersWHEREnameLIKE%张%;原理B树按字符串前缀排序。张%可定位到以张开头的起始位置%张不知道开头是什么只能全扫描。3.3 对索引列使用函数或表达式-- ❌ 不走索引对索引列使用函数SELECT*FROMordersWHEREDATE(create_time)2026-05-28;-- ✅ 改写后走索引范围查询SELECT*FROMordersWHEREcreate_time2026-05-28 00:00:00ANDcreate_time2026-05-29 00:00:00;-- ❌ 不走索引对索引列进行计算SELECT*FROMproductsWHEREprice*0.8100;-- ✅ 改写后走索引SELECT*FROMproductsWHEREprice125;原理索引存储的是原始列值。DATE(create_time)后MySQL无法知道计算后的值对应哪个索引位置。3.4 隐式类型转换-- 表结构phone VARCHAR(20)-- ❌ 不走索引隐式转换字符串列 vs 数字SELECT*FROMusersWHEREphone13800138000;-- 等价于WHERE CAST(phone AS SIGNED) 13800138000-- ✅ 走索引类型匹配SELECT*FROMusersWHEREphone13800138000;原理MySQL将字符串列与数字比较时会把字符串转为数字相当于对索引列用了CAST函数导致索引失效。注意反向情况-- 数字列 vs 字符串索引在数字列上SELECT*FROMusersWHEREid123;-- ✅ 走索引-- 等价于WHERE id CAST(123 AS SIGNED)对常量转换不影响索引列3.5 范围查询后面的列失效-- 索引idx_a_b_c (a, b, c)-- ⚠️ 部分失效a1用索引b10用索引范围c3无法用索引查找SELECT*FROMtWHEREa1ANDb10ANDc3;原理b 10返回一个区间区间内c的值不再有序无法用二分查找定位c3。但c3仍可能通过**索引下推(ICP)**过滤。3.6 使用!、、NOT IN-- ⚠️ 通常不走索引或走全索引扫描SELECT*FROMordersWHEREstatus!done;-- 例外如果status只有两个值且查询的是少数值可能走索引-- 例如 90% done10% pending查 pending 可能走索引原理!匹配大量数据通常是大部分行优化器认为回表随机I/O成本高于全表扫描顺序I/O。3.7 区分度低导致不走索引-- 索引idx_status (status)status 只有 done(90%)、pending(10%)-- ❌ 不走索引匹配90%数据SELECT*FROMordersWHEREstatusdone;-- ✅ 可能走索引匹配10%数据具体看优化器估算SELECT*FROMordersWHEREstatuspending;原理匹配数据超过**20%-30%**阈值时回表随机I/O成本 全表扫描顺序I/O成本。查看区分度SHOWINDEXFROMorders;-- Cardinality基数列不同值的数量。Cardinality/总行数 区分度4. 如何判断索引是否失效核心工具EXPLAINEXPLAIN字段判断依据说明typeALL 全表扫描索引失效ref/range/index 用了索引index是全索引扫描比ALL好但不如refkeyNULL 未使用索引不为NULL 使用了该索引最直接的判断rows估算扫描行数接近表总行数说明索引效果差结合filtered看filtered越低越好表示存储引擎返回数据经过WHERE过滤后的比例低值大rows说明回表浪费严重ExtraUsing whereServer层过滤Using index condition用了ICPUsing index覆盖索引辅助判断示例分析EXPLAINSELECT*FROMusersWHEREnameLIKE%张;-- typeALL, keyNULL → 索引失效全表扫描EXPLAINSELECT*FROMusersWHEREname张三;-- typeref, keyidx_name → 索引有效EXPLAINSELECTname,ageFROMusersWHEREname张三;-- typeref, ExtraUsing index → 覆盖索引完美5. 索引失效场景速查表面试必备场景示例索引在查询列上是否失效原因解决方案不遵循最左前缀WHERE b1索引(a,b)✅ 失效缺最左列建(b)索引或调顺序LIKE前导模糊WHERE name LIKE %张✅ 失效无法前缀匹配改用LIKE 张%或全文索引使用函数WHERE DATE(create)2026-01-01✅ 失效索引列被计算改用范围查询隐式类型转换WHERE phone138phone是VARCHAR✅ 失效索引列被函数处理统一类型范围后列WHERE a1 AND b10 AND c3索引(a,b,c)⚠️ c失效范围后无序调整索引顺序(a,c,b)!/WHERE status ! done⚠️ 通常失效匹配大量数据考虑IN包含需要的值低区分度WHERE gendermale90%数据⚠️ 通常失效回表成本高考虑覆盖索引OR条件WHERE a1 OR b2a有索引b无索引✅ 失效无法合并索引拆成UNION或给b加索引IS NULLWHERE age IS NULL⚠️ 不一定取决于NULL比例分析执行计划数据量极小几十行小表⚠️ 失效全表扫描更快无需处理6. 面试官追问与高分回答Q1LIKE abc%会走索引吗LIKE %abc呢ALIKE abc%可以走索引前缀匹配能定位起始位置LIKE %abc不能走索引前导通配符无法定位起始位置LIKE %abc%也不能。Q2为什么!通常不走索引A!匹配的是大部分数据如status ! done可能匹配90%数据。使用索引需要大量回表随机I/O优化器估算成本后认为全表扫描顺序I/O更快。但如果是少数值如status ! deleted匹配5%数据仍可能走索引。Q3联合索引(a,b,c)WHERE a1 AND c3能走索引吗A能部分命中a1用于索引查找c3无法用于索引范围查找因为跳过了b但可通过**索引下推(ICP)**在索引层过滤减少回表次数。EXPLAIN会显示Using index condition。Q4函数导致索引失效有没有办法让索引生效A①改写查询消除函数如DATE(col)改范围查询②MySQL 8.0.13支持函数索引Functional Indexes如CREATE INDEX idx_date ON t ((DATE(create_time)))③考虑生成列Generated Column 索引。Q5如何强制MySQL走索引A使用FORCE INDEX或USE INDEX提示但不推荐生产环境使用。应该分析为什么优化器不走索引统计信息过期区分度低从根源解决。Q6索引失效时typeindex是什么意思和ALL有什么区别Atypeindex表示全索引扫描遍历整个索引树不一定是叶子节点比ALL全表扫描好但比ref/range差。常见于覆盖索引场景或查询条件无法走索引查找但索引比表小时。7. 总结对比表失效原因分类典型场景是否绝对失效解决方向破坏有序性不遵循最左前缀、LIKE前导模糊✅ 绝对调整查询条件或索引设计索引列被计算函数、隐式类型转换✅ 绝对改写查询、函数索引、生成列优化器放弃低区分度、!、数据量小⚠️ 相对覆盖索引、FORCE INDEX临时、分析统计信息部分失效范围后列、跳过中间列⚠️ 部分调整索引顺序、依赖ICP面试官想要的满分总结索引失效分为两大类无法利用索引的有序性和优化器放弃使用索引。无法利用索引绝对失效不遵循最左前缀原则联合索引缺最左列LIKE %abc前导通配符无法定位对索引列用函数/计算DATE(col)、col*2隐式类型转换字符串列 数字优化器放弃索引相对失效低区分度如性别字段匹配50%数据!、、NOT IN匹配大量数据数据量极小全表扫描更快统计信息过期导致误判判断方法用EXPLAIN看typeALL失效、keyNULL失效、ExtraUsing index conditionICP已用。优化方向调整查询条件、建覆盖索引、更新统计信息ANALYZE TABLE、拆分复杂查询。一句话索引失效的本质要么是查询条件破坏了B树有序性要么是优化器算账后发现走索引不划算。觉得对您有帮助麻烦点点关注啦您的关注是我创作的最大动力~