📌 PDF：大白话说Java面试题 — 03-Mysql篇
第10题：MySQL 在什么条件下索引失效
📚 回答：
- 核心考点： 大厂面试要求不仅罗列索引失效场景，更要理解底层原理（为什么失效）、优化器决策逻辑（为什么不走索引），以及如何通过EXPLAIN分析和如何规避。面试官常追问："这个SQL走了索引吗？为什么？"
1. 索引失效的本质
索引失效，本质上是MySQL查询优化器决定不使用索引，原因可分为两类：
| 失效类型 | 根本原因 | 优化器行为 |
|---|---|---|
| 无法利用索引 | 查询条件破坏了索引的有序性，或索引无法加速该查询 | 无法使用索引（或只能部分使用） |
| 优化器放弃索引 | 优化器估算全表扫描成本 < 使用索引成本 | 主动选择全表扫描 |
关键理解："索引失效"不一定是索引真的"坏了"，而是MySQL认为用索引不如全表扫描快。
2. 索引失效的完整场景分类
分类一：无法利用索引的有序性
| 场景 | 示例 | 原理 | 是否绝对失效 |
|---|---|---|---|
| 不遵循最左前缀原则 | 索引(a,b,c)，WHERE b=2 | 联合索引按最左列排序，缺最左列无法定位起始位置 | ✅ 绝对失效 |
| LIKE前导模糊查询 | name LIKE '%张三' | 通配符在前，无法判断前缀，无法利用B+树有序性 | ✅ 绝对失效 |
| 对索引列使用函数 | WHERE UPPER(name)='ABC' | 索引存原始值，函数后值变了，无法匹配 | ✅ 绝对失效 |
| 隐式类型转换 | WHERE phone=13800138000（phone是VARCHAR） | 相当于CAST(phone AS SIGNED)，索引列被函数处理 | ✅ 绝对失效 |
使用!=或<> | WHERE status != 'done' | 非等值查询扫描量大，通常退化为全表扫描 | ⚠️ 非绝对，但常见 |
使用IS NULL/IS NOT NULL | WHERE age IS NOT NULL | 优化器根据NULL比例决定 | ⚠️ 不一定失效 |
| 范围查询后列失效 | 索引(a,b,c)，WHERE a=1 AND b>10 AND c=3 | 范围查询b>后，c无法用于索引查找 | ⚠️ 部分失效（c列失效） |
| 跳过中间列 | 索引(a,b,c)，WHERE a=1 AND c=3 | 跳过b，c无法用于索引查找 | ⚠️ 部分失效（c列失效） |
分类二：优化器选择全表扫描
| 场景 | 示例 | 原理 | 如何确认 |
|---|---|---|---|
| 表数据量极小 | 几百行的配置表 | 全表扫描一次I/O，回表多次I/O反而慢 | EXPLAIN显示type=ALL |
| 索引区分度低 | 性别字段，WHERE gender='male'匹配50%数据 | 回表随机I/O成本 > 顺序扫描全表 | rows接近表总行数 |
| 统计信息过期 | 大量数据变更后未ANALYZE | 优化器误判扫描行数 | SHOW INDEX看Cardinality |
| 查询返回大量数据 | WHERE id>0（匹配全表） | 使用索引还需回表，不如直接全表扫描 | filtered接近100% |
3. 各场景深度解析
3.1 不遵循最左前缀原则
-- 索引：idx_a_b_c (a, b, c)
-- ✅ 走索引（完全命中 a, b）
SELECT * FROM t WHERE a = 1 AND b = 2;
-- ❌ 不走索引（缺少最左列 a）
SELECT * FROM t WHERE b = 2;
-- ⚠️ 部分走索引（a命中，c用ICP）
SELECT * FROM t WHERE a = 1 AND c = 3;
原理：联合索引B+树先按a排序，a相同再按b排序，b相同再按c排序。缺最左列a时，无法确定起始搜索位置。
3.2 LIKE前导模糊查询
-- ✅ 走索引（前缀匹配）
SELECT * FROM users WHERE name LIKE '张%';
-- ❌ 不走索引（前导通配符）
SELECT * FROM users WHERE name LIKE '%张';
SELECT * FROM users WHERE name LIKE '%张%';
原理：B+树按字符串前缀排序。'张%'可定位到以"张"开头的起始位置；'%张'不知道开头是什么，只能全扫描。
3.3 对索引列使用函数或表达式
-- ❌ 不走索引（对索引列使用函数）
SELECT * FROM orders WHERE DATE(create_time) = '2026-05-28';
-- ✅ 改写后走索引（范围查询）
SELECT * FROM orders WHERE create_time >= '2026-05-28 00:00:00'
AND create_time < '2026-05-29 00:00:00';
-- ❌ 不走索引（对索引列进行计算）
SELECT * FROM products WHERE price * 0.8 > 100;
-- ✅ 改写后走索引
SELECT * FROM products WHERE price > 125;
原理：索引存储的是原始列值。DATE(create_time)后，MySQL无法知道计算后的值对应哪个索引位置。
3.4 隐式类型转换
-- 表结构：phone VARCHAR(20)
-- ❌ 不走索引（隐式转换：字符串列 vs 数字）
SELECT * FROM users WHERE phone = 13800138000;
-- 等价于：WHERE CAST(phone AS SIGNED) = 13800138000
-- ✅ 走索引（类型匹配）
SELECT * FROM users WHERE phone = '13800138000';
原理：MySQL将字符串列与数字比较时，会把字符串转为数字（相当于对索引列用了CAST函数），导致索引失效。
注意反向情况：
-- 数字列 vs 字符串（索引在数字列上）
SELECT * FROM users WHERE id = '123'; -- ✅ 走索引
-- 等价于：WHERE id = CAST('123' AS SIGNED)，对常量转换，不影响索引列
3.5 范围查询后面的列失效
-- 索引：idx_a_b_c (a, b, c)
-- ⚠️ 部分失效：a=1用索引，b>10用索引（范围），c=3无法用索引查找
SELECT * FROM t WHERE a = 1 AND b > 10 AND c = 3;
原理：b > 10返回一个区间，区间内c的值不再有序，无法用二分查找定位c=3。但c=3仍可能通过**索引下推(ICP)**过滤。
3.6 使用!=、<>、NOT IN
-- ⚠️ 通常不走索引（或走全索引扫描）
SELECT * FROM orders WHERE status != 'done';
-- 例外：如果status只有两个值，且查询的是少数值，可能走索引
-- 例如 90% done，10% pending，查 pending 可能走索引
原理：!=匹配大量数据（通常是大部分行），优化器认为回表随机I/O成本高于全表扫描顺序I/O。
3.7 区分度低导致不走索引
-- 索引：idx_status (status)，status 只有 'done'(90%)、'pending'(10%)
-- ❌ 不走索引（匹配90%数据）
SELECT * FROM orders WHERE status = 'done';
-- ✅ 可能走索引（匹配10%数据，具体看优化器估算）
SELECT * FROM orders WHERE status = 'pending';
原理：匹配数据超过**20%-30%**阈值时，回表随机I/O成本 > 全表扫描顺序I/O成本。
查看区分度：
SHOW INDEX FROM orders;
-- Cardinality（基数）列：不同值的数量。Cardinality/总行数 = 区分度
4. 如何判断索引是否失效？
核心工具：EXPLAIN
| EXPLAIN字段 | 判断依据 | 说明 |
|---|---|---|
type | ALL = 全表扫描（索引失效）；ref/range/index = 用了索引 | index是全索引扫描，比ALL好但不如ref |
key | NULL = 未使用索引；不为NULL = 使用了该索引 | 最直接的判断 |
rows | 估算扫描行数，接近表总行数说明索引效果差 | 结合filtered看 |
filtered | 越低越好，表示存储引擎返回数据经过WHERE过滤后的比例 | 低值+大rows说明回表浪费严重 |
Extra | Using where（Server层过滤）；Using index condition（用了ICP）；Using index（覆盖索引） | 辅助判断 |
示例分析：
EXPLAIN SELECT * FROM users WHERE name LIKE '%张';
-- type=ALL, key=NULL → 索引失效，全表扫描
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- type=ref, key=idx_name → 索引有效
EXPLAIN SELECT name, age FROM users WHERE name = '张三';
-- type=ref, Extra=Using index → 覆盖索引，完美
5. 索引失效场景速查表（面试必备）
| 场景 | 示例（索引在查询列上） | 是否失效 | 原因 | 解决方案 |
|---|---|---|---|---|
| 不遵循最左前缀 | WHERE b=1（索引(a,b)） | ✅ 失效 | 缺最左列 | 建(b)索引或调顺序 |
| LIKE前导模糊 | WHERE name LIKE '%张' | ✅ 失效 | 无法前缀匹配 | 改用LIKE '张%'或全文索引 |
| 使用函数 | WHERE DATE(create)='2026-01-01' | ✅ 失效 | 索引列被计算 | 改用范围查询 |
| 隐式类型转换 | WHERE phone=138（phone是VARCHAR） | ✅ 失效 | 索引列被函数处理 | 统一类型 |
| 范围后列 | WHERE a=1 AND b>10 AND c=3（索引(a,b,c)） | ⚠️ c失效 | 范围后无序 | 调整索引顺序(a,c,b) |
!= / <> | WHERE status != 'done' | ⚠️ 通常失效 | 匹配大量数据 | 考虑IN包含需要的值 |
| 低区分度 | WHERE gender='male'（90%数据） | ⚠️ 通常失效 | 回表成本高 | 考虑覆盖索引 |
OR条件 | WHERE a=1 OR b=2（a有索引，b无索引） | ✅ 失效 | 无法合并索引 | 拆成UNION或给b加索引 |
IS NULL | WHERE age IS NULL | ⚠️ 不一定 | 取决于NULL比例 | 分析执行计划 |
| 数据量极小 | 几十行小表 | ⚠️ 失效 | 全表扫描更快 | 无需处理 |
6. 面试官追问与高分回答
Q1：LIKE 'abc%'会走索引吗？LIKE '%abc'呢？
A：LIKE 'abc%'可以走索引（前缀匹配，能定位起始位置）；LIKE '%abc'不能走索引（前导通配符，无法定位起始位置）；LIKE '%abc%'也不能。
Q2：为什么!=通常不走索引？
A：!=匹配的是大部分数据（如status != 'done'可能匹配90%数据）。使用索引需要大量回表（随机I/O），优化器估算成本后认为全表扫描（顺序I/O）更快。但如果是少数值（如status != 'deleted'匹配5%数据），仍可能走索引。
Q3：联合索引(a,b,c)，WHERE a=1 AND c=3能走索引吗？
A：能部分命中：a=1用于索引查找，c=3无法用于索引范围查找（因为跳过了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：索引失效时，type=index是什么意思？和ALL有什么区别？
A：type=index表示全索引扫描，遍历整个索引树（不一定是叶子节点），比ALL（全表扫描）好，但比ref/range差。常见于覆盖索引场景，或查询条件无法走索引查找但索引比表小时。
7. 总结对比表
| 失效原因分类 | 典型场景 | 是否绝对失效 | 解决方向 |
|---|---|---|---|
| 破坏有序性 | 不遵循最左前缀、LIKE前导模糊 | ✅ 绝对 | 调整查询条件或索引设计 |
| 索引列被计算 | 函数、隐式类型转换 | ✅ 绝对 | 改写查询、函数索引、生成列 |
| 优化器放弃 | 低区分度、!=、数据量小 | ⚠️ 相对 | 覆盖索引、FORCE INDEX（临时）、分析统计信息 |
| 部分失效 | 范围后列、跳过中间列 | ⚠️ 部分 | 调整索引顺序、依赖ICP |
💡 面试官想要的满分总结：
"索引失效分为两大类：无法利用索引的有序性和优化器放弃使用索引。
无法利用索引（绝对失效）：
- 不遵循最左前缀原则：联合索引缺最左列
LIKE '%abc'：前导通配符无法定位- 对索引列用函数/计算：
DATE(col)、col*2- 隐式类型转换：字符串列 = 数字
优化器放弃索引（相对失效）：
- 低区分度（如性别字段匹配50%数据）
!=、<>、NOT IN匹配大量数据- 数据量极小，全表扫描更快
- 统计信息过期导致误判
判断方法：用
EXPLAIN，看type（ALL=失效）、key（NULL=失效）、Extra（Using index condition=ICP已用）。
优化方向：调整查询条件、建覆盖索引、更新统计信息（
ANALYZE TABLE）、拆分复杂查询。
一句话：索引失效的本质，要么是查询条件破坏了B+树有序性，要么是优化器算账后发现走索引不划算。"
觉得对您有帮助，麻烦点点关注啦，您的关注是我创作的最大动力~ 🎯
