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

回答

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

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

也正是因为 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 字段说明


字段	含义
id	序列号（一组数字）表示查询中执行 select 子句或操作表的顺序。
select_type	查询类型或者是其他操作类型。
table	正在访问的表名。
partitions	匹配的分区信息。
type	访问方式，这是最重要的字段。表示关联类型或访问类型，从最好到最差的类型为 const、eq_ref、ref、range、index 和 ALL。
possible_keys	显示可能应用的索引，一个或多个。
key	实际使用到的索引，如果为 NULL，则没有使用索引。
key_len	表示索引中使用的字节数，计算公式：key_len = 字段定义长度 * 字符集长度 + 是否默认为 NULL + 是否是变长字段。
ref	显示索引的哪一列被使用，展示的就是与索引列作等值匹配的值。
rows	根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数，这是一个重要字段。
filtered	表示符合查询条件的数据百分比
Extra	附加信息，这是另一个重要字段。相关重要信息如下：\n * Using index：表示 MySQL 将使用覆盖索引，以避免回表 * Using where：表示 where 条件的部分字段没有索引，需在存储引擎检索之后 server 层再进行过滤 * Using index condition：表示用到索引下推（ICP） * Using filesort：表示 MySQL 需要额外的一次传递，以找出如何按排序顺序检索行 * Using temporary：表示对查询结果排序时会使用一个临时表 * Using temporary，Using filesort：表示把 join 关联结果全部查找出来，存放在临时表中，等所有的关联都结束后，再在内存中对数据行进行排序

Java 面试宝典是大明哥全力打造的 Java 精品面试题，它是一份靠谱、强大、详细、经典的 Java 后端面试宝典。它不仅仅只是一道道面试题，而是一套完整的 Java 知识体系，一套你 Java 知识点的扫盲贴。

它的内容包括：

大厂真题：Java 面试宝典里面的题目都是最近几年的高频的大厂面试真题。
原创内容：Java 面试宝典内容全部都是大明哥原创，内容全面且通俗易懂，回答部分可以直接作为面试回答内容。
持续更新：一次购买，永久有效。大明哥会持续更新 3+ 年，累计更新 1000+，宝典会不断迭代更新，保证最新、最全面。
覆盖全面：本宝典累计更新 1000+，从 Java 入门到 Java 架构的高频面试题，实现 360° 全覆盖。
不止面试：内容包含面试题解析、内容详解、知识扩展，它不仅仅只是一份面试题，更是一套完整的 Java 知识体系。
宝典详情：https://www.yuque.com/chenssy/sike-java/xvlo920axlp7sf4k
宝典总览：https://www.yuque.com/chenssy/sike-java/yogsehzntzgp4ly1
宝典进展：https://www.yuque.com/chenssy/sike-java/en9ned7loo47z5aw

目前 Java 面试宝典累计更新 400+ 道，总字数 42w+。大明哥还在持续更新中，下图是大明哥在 2024-12 月份的更新情况：

想了解详情的小伙伴，扫描下面二维码加大明哥微信【daming091】咨询

同时，大明哥也整理一套目前市面最常见的热点面试题。微信搜[大明哥聊 Java]或扫描下方二维码关注大明哥的原创公众号[大明哥聊 Java] ，回复【面试题】即可免费领取。

阅读全文