本文共 2240 字，大约阅读时间需要 7 分钟。

MySQL 索引失效场景分析

在数据库开发过程中，索引失效是一个常见但却不容忽视的问题。无论是技术大牛还是刚入行的开发者，都可能会遇到索引未生效的情况。以下将详细分析15种常见的索引失效场景，并通过实际案例帮助开发者避免踩坑。

1. 联合索引不满足最左匹配原则

联合索引的核心原则是“最左匹配”。在查询条件中，如果没有包含联合索引的最左侧字段，索引将失效。

示例：

explain select * from t_user where username = 'Tom2' and age = 12;

解释：此查询未包含联合索引的最左侧字段id_no，因此索引失效，导致全表扫描。

2. 使用 select *

在阿里巴巴开发手册中，禁止使用select *，这是为了避免全表扫描带来的性能问题。明确列出需要查询的字段可以更好地利用索引。

示例：

explain select id_no, username, age from t_user where username = 'Tom2';

解释：明确列出字段后，索引生效，且覆盖了所有查询字段。

3. 索引列参与运算

当索引字段参与计算或运算时，索引失效，数据库需要进行全表扫描。

示例：

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

解释：id字段有索引，但由于参与计算，优化器放弃了索引，导致全表扫描。

4. 索引列参与函数处理

函数处理会破坏索引的结构，导致优化器无法利用索引进行快速定位。

示例：

explain select * from t_user where substr(id_no, 1, 3) = '100';

解释：id_no字段参与函数处理，索引无法有效匹配，导致全表扫描。

5. 错误的 like 使用

当 like 模糊匹配时，若占位符出现在首部，索引无法有效匹配，导致失效。

示例：

explain select * from t_user where id_no like '%00%';

解释：like 模糊匹配但占位符在首部，索引无法利用，导致全表扫描。

6. 参数类型与字段类型不匹配

字段与参数类型不一致会导致隐式转换，破坏索引结构。

示例：

explain select * from t_user where id_no = 1002;

解释：id_no字段为varchar，参数为整数，隐式转换导致索引失效。

7. 查询条件使用 or

当 or 操作涉及未索引字段时，优化器可能放弃索引，导致全表扫描。

示例：

explain select * from t_user where id = 2 or username = 'Tom2';

解释：id索引存在，但由于 or 条件，优化器选择全表扫描。

8. 两列进行比较

即使两列都有索引，直接比较两列也会导致索引失效。

示例：

explain select * from t_user where id > age;

解释：id和age都有索引，但直接比较导致优化器放弃索引。

9. 不等比较

部分不等比较操作（如 !=）在某些情况下会导致索引失效。

示例：

explain select * from t_user where create_time != '2022-02-27 09:04:23';

解释：在大量数据中，!= 比较可能导致全表扫描。

10. is not null 查询

is not null 查询通常不会走索引，而 is null 查询会走索引。

示例：

explain select * from t_user where id_no is not null;

解释：is not null 查询不走索引，而 is null 查询则会走。

11. not in 和 not exists 查询

not in 和 not exists 查询在某些情况下不会走索引。

示例：

explain select * from t_user where id_no not in ('1002', '1003');

解释：如果主键索引，会走索引，否则可能失效。

12. order by 导致索引失效

order by 和 limit 组合时，通常不会走索引。

示例：

explain select * from t_user order by id_no limit 10;

解释：order by 加 limit 会导致全表扫描。

13. 参数不同导致索引失效

不同参数条件下，优化器可能选择全表扫描。

示例：

explain select * from t_user where create_time > '2023-02-24 09:04:23';

解释：某些参数条件下，优化器选择全表扫描。

14. 其他优化器策略

优化器在某些情况下会选择全表扫描，即使索引存在。

示例：

explain select * from t_user where create_time > '2022-02-27 09:04:23';

解释：参数条件可能导致优化器选择全表扫描。

总结

以上15种索引失效场景涵盖了大多数常见情况。索引失效通常与查询条件、参数类型、优化器策略等因素有关。建议在实际开发中，通过explain工具分析每条查询的执行计划，确保索引的有效利用。

转载地址：http://jbbfk.baihongyu.com/