深入解析 MySQL 索引失效的原因与优化策略

在日常开发中，索引是提升 MySQL 查询性能的利器，但如果使用不当，索引可能会失效，导致查询效率大打折扣。本文将详细解析 MySQL 索引失效的各种场景，并提供优化建议，帮助大家避免踩坑。

一、什么是索引失效？

索引失效是指 SQL 查询未能利用到已有的索引，而是进行了全表扫描或其他低效的查询操作。全表扫描意味着数据库需要逐行扫描数据，导致性能下降。

二、常见索引失效场景

1. 不满足最左前缀原则

在联合索引中，必须从最左边的字段开始匹配。未满足最左前缀原则时，索引将失效。

示例

已建立联合索引 (code, age, name)：

• 有效：

  explain select * from user where code='101';
  explain select * from user where code='101' and age=21;
  explain select * from user where code='101' and age=21 and name='zs';
  

  这些查询遵循最左匹配原则，因此索引有效。

• 失效：

  explain select * from user where age=21;
  explain select * from user where name='zs';
  explain select * from user where age=21 and name='zs';
  

  查询未包含联合索引的第一个字段 code，索引失效。

优化建议

确保查询条件包含联合索引的最左字段。

2. SELECT * 影响索引使用

使用 SELECT * 时，大概率会查询非索引列，导致索引失效。如果查询的列仅包含索引字段，则可以利用覆盖索引，提升查询效率。

示例

• 失效：

  explain select * from user where name='ls';
  

• 优化：

  explain select code, name from user where name='ls';
  

  查询仅涉及索引列，走覆盖索引。

优化建议

只查询必要的列，避免使用 SELECT *。

3. 索引列上有计算

在查询条件中对索引列进行计算或表达式处理，会导致索引失效。

示例

• 失效：

  explain select * from user where id+1=2;
  

• 优化：

  explain select * from user where id=1;
  

  避免在查询条件中对索引字段进行计算。

优化建议

将计算移至程序端或通过增加虚拟列避免直接计算。

4. 索引列使用函数

查询条件中对索引列使用函数，会导致索引失效。

示例

• 失效：

  explain select * from user where substr(height, 1, 2)='17';
  

• 优化：

  explain select * from user where height like '17%';
  

  使用范围查询替代函数操作。

优化建议

避免在查询条件中使用函数。

5. 字段类型不一致

查询条件中的参数类型与字段类型不一致，会导致索引失效。

示例

• 失效：

  explain select * from user where code=101;
  

  code 是 varchar 类型，但查询条件使用了 int 类型。

• 优化：

  explain select * from user where code='101';
  

  确保查询参数与字段类型一致。

优化建议

使用与字段类型一致的参数，避免 MySQL 的隐式类型转换。

6. LIKE 查询包含 % 在左边

在 LIKE 查询中，% 出现在左边时，索引会失效。

示例

• 有效：

  explain select * from user where code like '10%';
  

• 失效：

  explain select * from user where code like '%10';
  explain select * from user where code like '%10%';
  

优化建议

避免 % 出现在查询条件的左侧；如果必须使用，可尝试全文索引。

7. 列对比

当查询条件中两个字段进行对比时，索引会失效。

示例

• 失效：

  explain select * from user where id=height;
  

优化建议

尽量避免字段间直接对比；如果必要，可考虑在程序端处理。

8. 使用 OR 关键字

如果 OR 关键字的两侧字段未全部建立索引，则所有字段的索引都会失效。

示例

• 失效：

  explain select * from user where id=1 or address='成都';
  

• 优化：

  explain select * from user where id=1 or (address='成都' and address_index_exists);
  

优化建议

确保 OR 关键字两侧的字段均有索引，或者将查询拆分为多个子查询。

9. NOT IN 和 NOT EXISTS

这两种条件会导致索引失效，尤其是在普通索引字段上。

示例

• 失效：

  explain select * from user where height not in (173, 174, 175);
  explain select * from user where not exists (select 1 from other_table where ...);
  

优化建议

改用 NOT EXISTS 或左连接的方式替代 NOT IN。

10. ORDER BY 和 LIMIT 使用不当

排序操作可能导致索引失效，尤其是未遵循最左前缀原则时。

示例

• 失效：

  explain select * from user order by age limit 100;
  

• 优化：

  explain select * from user where code='101' order by age limit 100;
  

优化建议

确保排序字段满足联合索引的最左前缀原则。

三、如何避免索引失效？

1. 遵循最左前缀原则。
2. 查询条件中避免对索引列使用计算、函数、% 左匹配等操作。
3. 保证查询参数类型与字段类型一致。
4. 优化 OR 查询，确保两侧字段均有索引。
5. 尽量减少使用 NOT IN 和 NOT EXISTS。
6. 尽量使用覆盖索引，避免 SELECT *。

四、总结

MySQL 索引是提高查询性能的重要工具，但使用不当会导致失效，甚至拖累性能。理解索引的原理和失效原因，编写符合规则的高效 SQL，是提升系统性能的关键。

希望本文能为您排查和优化索引提供帮助！如果您有其他疑问，欢迎交流探讨！