Java 面试宝典：MySQL 索引失效的场景有哪些？

回答

在数据库表加索引是为了快速定位到记录，提高查询效率。但在某些场景下，虽然加了索引，SQL 执行中却没有走索引，而是全表扫描，这个过程称为索引失效。

从前面的文章可知 MySQL InnoDB 引擎的索引结构是 B+ 树。

知道 MySQL 索引的结构吗？

什么是聚簇索引和二级索引

也正是因为 InnoDB 的 B+ 树索引结构会出现如下索引失效场景。

1 非等值条件查询如 like

select * from t_user where name like '%a%';

优化措施：对于 like 模糊匹配场景，通常结合实际业务改造为 like 'a%'

2 联合索引非最左前缀查询

select * from t_user where age = 10 and name = 'aa';

优化措施：调整 SQLwhere字段顺序来匹配联合索引的字段顺序以满足最左前缀（备注：Server 层优化器在某些场景会优化 SQL 查询以使其走联合索引，具体可通过explain查看执行计划）。

3 索引列参与计算或函数操作

select * from t_user where CONCAT(name, '张') = 'aa张'; // 函数
select * from t_user where age + 1 = 10; // 计算

优化措施：在内存中计算好预期的值或在 SQL 语句条件的右侧进行参数值计算，不对索引列字段操作。

4 OR 连接不同的索引列

select * from t_user where age = 10 or name = 'aa';

优化措施：使用 OR 关键字，需对条件字段都添加索引。

5 数据类型不一致（类型隐式转换）

select * from t_user where age = '20';// 走索引
select * from t_user where name = 20;// 为 name 字段加索引，仍然不会走索引

优化措施：根据字段类型书写 SQL，避免类型转换。

原因是 MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。可用

select '10' > 9;来验证。

6、非条件判断如 is not null & not in & not exists

select * from t_user where name is not null;
select * from t_user where age not in (10,20);

优化措施：结合业务场景，改造为同等语义 SQL。

扩展

MySQL 性能分析神器 explain

利用 explain 可以获取 SQL 的执行计划。

explain 字段说明