mysql 索引在哪些情况下会失效
索引失效场景总览
| # | 场景 | 是否一定失效 |
|---|---|---|
| 1 | 索引列上使用函数或表达式 | ✅ 必然失效 |
| 2 | 隐式类型转换 | ✅ 字符串列 = 数字时必然失效 |
| 3 | LIKE 以 % 开头 |
✅ 必然失效 |
| 4 |
!= / <> / NOT IN / NOT LIKE
|
⚠️ 通常失效（取决于优化器） |
| 5 |
IS NULL / IS NOT NULL
|
⚠️ 视数据分布而定 |
| 6 | OR 条件中有列无索引 | ✅ 必然失效 |
| 7 | 违反联合索引最左前缀原则 | ✅ 必然失效 |
| 8 | 范围查询右侧列失效 | ✅ 必然失效 |
| 9 | ORDER BY / GROUP BY 与索引不匹配 | ⚠️ 无法利用索引排序 |
| 10 | 字段区分度低 / 表太小 | ⚠️ 优化器主动放弃 |
验证是否走索引：
EXPLAIN SELECT ...，观察type（ref/range > index > ALL）和key字段。
1. 索引列上使用函数或表达式
-- ❌ 失效：对索引列 create_time 做了函数运算
SELECT * FROM orders WHERE YEAR(create_time) = 2024;
-- ❌ 失效：对索引列 price 做了表达式运算
SELECT * FROM orders WHERE price * 1.1 > 100;
索引存储的是列的原始值，函数/表达式改变了值，MySQL 无法利用 B+ 树有序结构定位。
-- ✅ 正确：把运算移到常量侧
SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
SELECT * FROM orders WHERE price > 100 / 1.1;
2. 隐式类型转换
场景 1：字符串列与数字比较（必然失效）
-- phone 是 VARCHAR，传入数字
-- ❌ 失效：等价于 CAST(phone AS signed) = 123456，对索引列做了隐式转换
SELECT * FROM user WHERE phone = 123456;
-- ✅ 正确
SELECT * FROM user WHERE phone = '123456';
场景 2：数字列与字符串比较（通常不失效）
-- id 是 INT，传入字符串
-- ⚠️ MySQL 把 '100' 转成数字，转换发生在常量侧，索引列未被修改
-- 通常能走索引，但仍建议类型匹配，避免歧义
SELECT * FROM user WHERE id = '100';
记忆口诀：转换发生在索引列上 → 失效；转换发生在常量上 → 通常正常。
3. LIKE 以 % 开头
-- ❌ 失效：前缀不确定，无法利用 B+ 树顺序扫描
SELECT * FROM user WHERE name LIKE '%张';
SELECT * FROM user WHERE name LIKE '%张%';
-- ✅ 正常：前缀确定，可以走索引范围扫描
SELECT * FROM user WHERE name LIKE '张%';
如果业务必须全模糊，可考虑以下替代方案：
- 覆盖索引：
SELECT name FROM user WHERE name LIKE '%张%'（只查索引列，走 index 扫描）- 全文索引：
FULLTEXT INDEX+MATCH ... AGAINST- Elasticsearch：搜索场景迁到搜索引擎
4. != / <> / NOT IN / NOT LIKE
-- ❌ 通常失效（全表扫描代价更低时，优化器会放弃索引）
SELECT * FROM orders WHERE status != 1;
SELECT * FROM orders WHERE status NOT IN (1, 2);
这类条件无法缩小范围，MySQL 优化器评估后认为全表扫描更快。
-- ✅ 改写为正向条件（业务允许时）
SELECT * FROM orders WHERE status IN (0, 3, 4);
-- ✅ 强制索引（谨慎使用）
SELECT * FROM orders FORCE INDEX (idx_status) WHERE status != 1;
5. IS NULL / IS NOT NULL
-- 取决于 NULL 值的比例
-- ❌ 若 deleted_at 绝大多数为 NULL，走索引反而更慢，优化器放弃
SELECT * FROM orders WHERE deleted_at IS NULL;
-- ✅ 若 NULL 值少，可以走索引
SELECT * FROM orders WHERE deleted_at IS NOT NULL;
最佳实践：对频繁查询的列设置
NOT NULL DEFAULT默认值，避免 NULL 引发的不确定性。
-- 建表时用数字标记替代 NULL
deleted_at TINYINT NOT NULL DEFAULT 0 COMMENT '0=未删除'
-- 替代 IS NULL
WHERE deleted_at = 0
6. OR 条件中有列无索引
-- 假设 name 有索引，address 无索引
-- ❌ 失效：address 没索引，OR 的另一侧必须全表扫描，整个条件退化为全表扫描
SELECT * FROM user WHERE name = '张三' OR address = '北京';
OR 两侧只要有一侧无法走索引，MySQL 就会放弃整个索引，退化为全表扫描。
-- ✅ 方案1：给 address 也加索引
ALTER TABLE user ADD INDEX idx_address (address);
-- ✅ 方案2：改写为 UNION（各自走各自的索引）
SELECT * FROM user WHERE name = '张三'
UNION
SELECT * FROM user WHERE address = '北京';
7. 违反联合索引最左前缀原则 ⭐
假设联合索引：INDEX idx_a_b_c (a, b, c)
-- ✅ 走索引
SELECT * FROM t WHERE a = 1;
SELECT * FROM t WHERE a = 1 AND b = 2;
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;
SELECT * FROM t WHERE a = 1 AND c = 3; -- 部分走：只用 a 列索引，c 跳过 b 失效
-- ❌ 完全失效（跳过最左列 a）
SELECT * FROM t WHERE b = 2;
SELECT * FROM t WHERE c = 3;
SELECT * FROM t WHERE b = 2 AND c = 3;
索引顺序：
a → b → c，查询必须从 a 开始，才能利用 B+ 树有序性逐级缩小范围。跳过 a 直接查 b/c，相当于在无序数组里找值。
注意：WHERE 子句中列的书写顺序不影响结果，MySQL 优化器会自动调整顺序匹配最左前缀。
-- ✅ 与 WHERE a = 1 AND b = 2 等价，优化器会重排
SELECT * FROM t WHERE b = 2 AND a = 1;
8. 范围查询右侧列失效 ⭐
假设联合索引：INDEX idx_a_b_c (a, b, c)
-- ❌ b 用了范围查询，b 后面的 c 索引失效
-- 实际上：a 走精确匹配，b 走范围扫描，c 完全失效
SELECT * FROM t WHERE a = 1 AND b > 10 AND c = 5;
通过
EXPLAIN的key_len字段可以判断实际使用了几个索引列：
- 只用 a：
key_len = a 的字节数- 用 a+b：
key_len = a+b 的字节数- 用 a+b+c：
key_len = a+b+c 的字节数
优化技巧：将范围列放到联合索引最后，让等值列先收窄范围。
-- a、c 是等值查询，b 是范围查询，建索引时把 b 放最后
ALTER TABLE t ADD INDEX idx_a_c_b (a, c, b);
-- 这样 a、c 都能精确匹配，b 的范围查询不影响前两列
SELECT * FROM t WHERE a = 1 AND c = 5 AND b > 10;
9. ORDER BY / GROUP BY 与索引不匹配
假设索引 idx_a_b (a, b)
-- ✅ 可以利用索引排序（避免 filesort）
SELECT * FROM t WHERE a = 1 ORDER BY b;
SELECT * FROM t ORDER BY a, b;
-- ❌ 无法利用索引排序（方向不一致）
SELECT * FROM t ORDER BY a ASC, b DESC;
-- ❌ 无法利用索引排序（跳过了 a）
SELECT * FROM t ORDER BY b;
-- ❌ ORDER BY 列不在索引中
SELECT * FROM t WHERE a = 1 ORDER BY c;
EXPLAIN 输出中
Extra出现Using filesort说明未利用索引排序，需要关注并优化。
10. 字段区分度低 / 表数据量太小
-- status 只有 0/1 两个值，区分度极低
-- 即使有索引，优化器也会选择全表扫描（回表代价 > 全表扫描代价）
SELECT * FROM orders WHERE status = 1; -- status=1 占 80% 数据
-- 表只有几百行时，全表扫描比索引查找更快
SELECT * FROM small_config WHERE key = 'timeout';
区分度 = 不重复值数 / 总行数，越接近 1 越适合建索引，低于 0.1 的列不建议单独建索引。
-- 计算字段区分度
SELECT COUNT(DISTINCT status) / COUNT(*) AS selectivity FROM orders;
-- 结果 0.002 → 区分度极低，不适合单独建索引
SELECT COUNT(DISTINCT user_id) / COUNT(*) AS selectivity FROM orders;
-- 结果 0.98 → 区分度高，非常适合建索引
快速排查索引失效
-- 1. EXPLAIN 查看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;
-- 关键字段解读：
-- type: const > eq_ref > ref > range > index > ALL
-- ref/range 以上说明用了索引；ALL 是全表扫描
-- key: 实际使用的索引名，NULL 表示未用索引
-- rows: 预估扫描行数，越小越好
-- Extra: Using index（覆盖索引） > Using where > Using filesort（需优化）
-- 2. optimizer_trace 查看优化器决策（为什么放弃索引）
SET optimizer_trace = 'enabled=on';
SELECT * FROM orders WHERE status = 1;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace = 'enabled=off';
总结
高频失效 & 最值得记住的三条：
- 函数/表达式作用在索引列上 → 把运算移到等号右侧的常量
- 联合索引不满足最左前缀 → 建索引时按查询频率排列列顺序
- 字符串列忘加引号 → 隐式类型转换，养成类型匹配的习惯
本博客所有文章除特别声明外，均采用
CC BY-NC-SA 4.0
许可协议，转载请注明出处！