常见的MySQL索引失效案例总结与分析

常见的MySQL索引失效案例总结与分析

薛定谔的汪
  2023-03-01 16:39:03 2023-03-01 16:39:03 Created   2026-03-27 14:22:51 2026-03-27 14:22:51 Updated    

MySQL索引失效？10大高频场景+避坑指南，看完再也不踩坑

作为后端开发者，我们每天都在和MySQL打交道，索引更是优化SQL性能的“神器”——合理的索引能让查询速度提升10倍、100倍，而一旦索引失效，原本毫秒级的查询可能会变成秒级、甚至分钟级，直接拖垮整个系统。

但实际开发中，我经常遇到这样的困惑：明明给字段建了索引，查询还是走全表扫描？最近项目中生产数据整理、迁移就遇到，明明测试环境都ok为什么到生产就不行了？其实这都是索引失效在“搞鬼”。今天就结合生产环境中的真实案例，梳理MySQL索引失效的10大高频场景，拆解失效原因，给出避坑技巧，帮你避开99%的索引踩坑问题。

一、先明确：什么是索引失效？

索引失效，简单来说就是：MySQL优化器在执行查询时，判断使用索引的效率，不如全表扫描高，因此主动放弃使用索引，转而执行全表扫描。这种情况在高并发、大数据量场景下，会直接导致查询性能暴跌，甚至引发系统瓶颈。

索引失效不是索引本身的问题，更多是我们的SQL写法、索引设计，或者数据分布导致的。下面这10个场景，是生产中最常出现的，一定要重点记牢。

二、10大高频索引失效场景（附案例+原因）

场景1：违反最左前缀法则（最常考、最易踩坑）

这是最常见的索引失效场景，尤其在使用联合索引时，几乎每个开发者都踩过这个坑。

核心规则：联合索引（a,b,c）的查询，必须从左到右依次使用索引列，不能跳过、不能打乱顺序，否则会导致索引失效或部分失效。

案例（联合索引：idx_a_b_c(a,b,c)）：

1
2
3
4
5
6
7
8
9
10
11
-- 有效：严格遵循最左前缀，使用全部索引
select * from user where a = 1 and b = 2 and c = 3;

-- 有效：使用索引a、b（部分生效）
select * from user where a = 1 and b = 2;

-- 失效：跳过a，直接查b、c，索引完全失效，走全表扫描
select * from user where b = 2 and c = 3;

-- 部分失效：只使用索引a，b、c失效
select * from user where a = 1 and c = 3;

避坑技巧：联合索引的设计要贴合实际查询场景，查询时尽量从左到右使用索引列，避免跳过左侧列。

场景2：索引列上做计算、函数、类型转换

核心原则：只要在索引列上做了“动手脚”（计算、函数调用、类型转换），MySQL就无法直接使用索引，只能放弃索引走全表扫描。

案例（索引：idx_age(age)、idx_name(name)、idx_create_time(create_time)）：

1
2
3
4
5
6
7
8
9
10
11
-- 失效：索引列age做了加法计算
select * from user where age + 1 = 10;

-- 失效：索引列name使用了left函数
select * from user where left(name, 2) = '张';

-- 失效：索引列create_time使用了date函数
select * from user where date(create_time) = '2025-01-01';

-- 失效：隐式类型转换（索引列name是varchar，查询用数字）
select * from user where name = 123;

避坑技巧：将计算、函数操作移到等号右侧，避免在索引列上操作；字符串查询必须加引号，避免隐式类型转换。

优化后示例：

1
2
3
4
5
6
7
8
-- 优化：计算移到右侧
select * from user where age = 10 - 1;

-- 优化：函数移到右侧（或提前计算）
select * from user where name like '张%';

-- 优化：避免date函数，用范围查询
select * from user where create_time between '2025-01-01 00:00:00' and '2025-01-01 23:59:59';

场景3：模糊查询like以%开头

模糊查询是业务中常用的场景，但如果使用不当，很容易导致索引失效。

核心规则：like查询中，%在前面（%xx）会导致索引失效；%在后面（xx%）则不会，能正常使用索引。

案例（索引：idx_name(name)）：

1
2
3
4
5
6
7
8
-- 失效：%在前面，无法使用索引，全表扫描
select * from user where name like '%张三';

-- 有效：%在后面，正常使用索引
select * from user where name like '张三%';

-- 失效：前后都有%，同样无法使用索引
select * from user where name like '%张三%';

避坑技巧：尽量避免%在前面的模糊查询；如果必须使用，可考虑使用全文索引（FULLTEXT）替代，或提前对数据进行预处理。

场景4：使用!=、<>、not in、not exists

核心原因：MySQL优化器认为，使用范围否定（!=、<>、not in等）时，符合条件的数据量可能较多，全表扫描的效率比使用索引更高，因此会放弃索引。

案例（索引：idx_age(age)、idx_id(id)）：

1
2
3
4
5
6
7
8
-- 失效：使用!=
select * from user where age != 18;

-- 失效：使用not in
select * from user where id not in (1,2,3);

-- 失效：使用not exists
select * from user u where not exists (select 1 from order o where o.user_id = u.id);

避坑技巧：尽量用范围查询替代否定查询；如果必须使用not in，可考虑用left join替代，提升性能。

优化示例：

1
2
3
4
5
-- 优化：用范围查询替代!=
select * from user where age < 18 or age > 18;

-- 优化：用left join替代not in
select u.* from user u left join order o on u.id = o.user_id where o.user_id is null;

场景5：is null / is not null 有时失效

这个场景不是绝对的，索引是否失效，取决于数据分布：

  • 如果索引列的NULL值很少，MySQL会使用索引；
  • 如果索引列的NULL值很多，MySQL认为全表扫描更快，会放弃索引。

案例（索引：idx_email(email)）：

1
2
3
4
5
-- 可能失效：如果email字段NULL值较多
select * from user where email is null;

-- 可能失效：同理，NULL值较多时
select * from user where email is not null;

避坑技巧：尽量避免索引列存储NULL值，可用默认值（如空字符串’’）替代；如果必须存储NULL，可结合数据分布，判断是否需要调整查询方式。

场景6：or连接的条件有一方没有索引

核心规则：or连接的多个条件中，只要有一个条件对应的字段没有索引，那么整个查询的索引都会失效，MySQL会走全表扫描。

案例（索引：idx_name(name)，age无索引）：

1
2
-- 失效：age无索引，即使name有索引，整个索引也会失效
select * from user where name = '张三' or age = 18;

避坑技巧：or连接的所有条件字段，都要建立索引；如果部分字段无法建索引，可考虑用union替代or，提升性能。

优化示例：

1
2
3
4
-- 优化：用union替代or
select * from user where name = '张三'
union
select * from user where age = 18;

场景7：order by / group by 违反最左前缀

联合索引不仅能优化查询，还能优化排序和分组，但如果order by、group by的字段不遵循最左前缀法则，就无法使用索引排序，会产生filesort（文件排序），性能下降。

案例（联合索引：idx_a_b_c(a,b,c)）：

1
2
3
4
5
6
7
8
-- 失效：order by违反最左前缀，产生filesort
select * from user order by b;

-- 失效：group by违反最左前缀，产生filesort
select a, count(*) from user group by c;

-- 有效：遵循最左前缀，使用索引排序
select * from user where a = 1 order by b;

避坑技巧：order by、group by的字段，要和联合索引的最左前缀保持一致；如果无法保持，可考虑调整索引设计，或避免使用order by/group by（如业务允许）。

场景8：MySQL优化器判断：全表扫描更快

这是一个容易被忽略的场景：即使你写的SQL符合索引使用规则，但MySQL优化器会根据数据量、数据分布，判断“使用索引”和“全表扫描”的效率，最终选择效率更高的方式。

常见情况：

  • 表数据量极小（如只有几十条数据），全表扫描比走索引更快；
  • 索引列重复值极高（如性别、状态字段，只有0和1两个值），使用索引过滤后，仍需扫描大部分数据，优化器会放弃索引。

避坑技巧：这种情况无需强行优化，MySQL会自动选择最优方案；但如果是大数据量、重复值低的字段，索引仍需正常使用。

场景9：join关联字段类型/字符集不一致

多表join关联时，如果关联字段的类型、字符集不一致，会导致隐式类型转换，进而导致索引失效。

案例（user表：user_id int，索引idx_user_id；order表：user_id varchar，无索引）：

1
2
-- 失效：user_id类型不一致（int vs varchar），隐式转换，索引失效
select * from user u join `order` o on u.user_id = o.user_id;

避坑技巧：多表join的关联字段，必须保证类型、字符集完全一致；同时给关联字段建立索引，提升join效率。

场景10：使用覆盖索引时，查询列超出覆盖范围

覆盖索引是指：索引包含了查询所需的所有字段，MySQL无需回表，直接通过索引就能获取数据，效率极高。但如果查询列超出了覆盖索引的范围，就会导致索引失效（或回表查询）。

案例（联合索引：idx_name_age(name, age)，覆盖name和age字段）：

1
2
3
4
5
-- 有效：查询列name、age，属于覆盖索引范围，无需回表
select name, age from user where name = '张三';

-- 失效：查询列包含id（不在覆盖索引中），需回表，索引效率下降（或失效）
select id, name, age from user where name = '张三';

避坑技巧：设计覆盖索引时，结合实际查询场景，包含常用的查询列；查询时尽量只查需要的字段，避免select *。

四、总结

MySQL索引失效，本质是“SQL写法”“索引设计”“数据分布”三者不匹配，导致MySQL优化器放弃使用索引。记住以上10大场景和避坑技巧，能帮你避开大部分索引踩坑问题。

最后提醒两点：

  1. 索引不是越多越好，过多的索引会影响插入、更新、删除的性能，按需建立即可；
  2. 写完SQL后，养成用explain查看执行计划的习惯，提前发现索引失效问题，避免线上踩坑。

希望这篇文章能帮你彻底搞懂MySQL索引失效，写出更高效的SQL，提升系统性能～

  • Title: 常见的MySQL索引失效案例总结与分析
  • Author: 薛定谔的汪
  • Created at : 2023-03-01 16:39:03
  • Updated at : 2026-03-27 14:22:51
  • Link: https://www.zhengyk.cn/2023/03/01/mysql/index_failure/
  • License: This work is licensed under CC BY-NC-SA 4.0.
On this page
常见的MySQL索引失效案例总结与分析
  1. MySQL索引失效？10大高频场景+避坑指南，看完再也不踩坑
    1. 一、先明确：什么是索引失效？
    2. 二、10大高频索引失效场景（附案例+原因）
      1. 场景1：违反最左前缀法则（最常考、最易踩坑）
      2. 场景2：索引列上做计算、函数、类型转换
      3. 场景3：模糊查询like以%开头
      4. 场景4：使用!=、<>、not in、not exists
      5. 场景5：is null / is not null 有时失效
      6. 场景6：or连接的条件有一方没有索引
      7. 场景7：order by / group by 违反最左前缀
      8. 场景8：MySQL优化器判断：全表扫描更快
      9. 场景9：join关联字段类型/字符集不一致
      10. 场景10：使用覆盖索引时，查询列超出覆盖范围
    3. 四、总结