java八股文面试[数据库]——索引哪些情况下会失效

1. 查询条件包含 or，会导致索引失效。

2. 隐式类型转换，会导致索引失效，例如 id字段类型是varchar，我们 where id = 1，这样就会触发隐式类型转换

3. like 通配符会导致索引失效，注意:”ABC%” 不会失效，会走 range 索引，”% ABC” 索引会失效

4. 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。

5. 对索引字段进行函数运算。

6. 对索引列运算（如，+、-、*、/），索引失效。

7. 索引字段上使用（!= 或者 < >，not in）时，会导致索引失效。

8. 索引字段上使用 is null， is not null，not exist 可能导致索引失效。

9. 相 join 的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循环计算

10. mysql 估计使用全表扫描要比使用索引快，则不使用索引。

知识速记：or like null  不等于 联合最左 类型 函数 运算  编码 全表

类型转换

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

select * from t_user where phone = 1300000001;

这是因为 phone 字段为字符串，所以 MySQL 要会自动把字符串转为数字，所以这条语句相当于：

select * from t_user where CAST(phone AS signed int) = 1300000001;

可以看到，CAST 函数是作用在了 phone 字段，而 phone 字段是索引，也就是对索引使用了函数！而前面我们也说了，对索引使用函数是会导致索引失效的。

例子二中的查询语句，我跟大家说了是会走索引扫描：

//例子二的查询语句
select * from t_user where id = "1";

这时因为字符串部分是输入参数，也就需要将字符串转为数字，所以这条语句相当于：

select * from t_user where id = CAST("1" AS signed int);

可以看到，索引字段并没有用任何函数，CAST 函数是用在了输入参数，因此是可以走索引扫描的。

联合索引使用了select *

KEY `union_idx` (`id_no`,`username`,`age`)

比如，在上面的联合索引中，如果查询条件是age或username，当使用了select * ，肯定是不会走索引的。

但如果希望根据username查询出id_no、username、age这三个结果（均为索引字段），明确查询结果字段，是可以走覆盖索引的：

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

explain结果：

无论查询条件是username还是age，都走了索引，根据key_len可以看出使用了索引的所有列。

第二种索引失效场景：在联合索引下，尽量使用明确的查询列来趋向于走覆盖索引；

如果or两边同时使用“>”和“<”，则索引也会失效：

explain select * from t_user where id  > 1 or id  < 80;

explain结果：

查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效；

or两边为“>”和“<”范围查询时，索引失效。

两列做比较

如果两个列数据都有索引，但在查询条件中对两列数据进行了对比操作，则会导致索引失效。

这里举个不恰当的示例，比如age小于id这样的两列（真实场景可能是两列同维度的数据比较，这里迁就现有表结构）：

explain select * from t_user where id > age;

explain结果：

这里虽然id有索引，age也可以创建索引，但当两列做比较时，索引还是会失效的。

两列数据做比较，即便两列都创建了索引，索引也会失效。

不等于比较

示例：

explain select * from t_user where id_no <> '1002';

explain结果：

当查询条件为字符串时，使用”<>“或”!=“作为条件查询，有可能不走索引，但也不全是。

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

上述SQL中，由于“2022-02-27 09:56:42”是存储过程在同一秒生成的，大量数据是这个时间。执行之后会发现，当查询结果集占比比较小时，会走索引，占比比较大时不会走索引。此处与结果集与总体的占比有关。

需要注意的是：上述语句如果是id进行不等操作，则正常走索引。

explain select * from t_user where id != 2;

explain结果：

查询条件使用不等进行比较时，需要慎重，普通索引会查询结果集占比较大时索引会失效。

is not null

示例：

explain select * from t_user where id_no is not null;

explain结果：

查询条件使用is null时正常走索引，使用is not null时，不走索引。

not in和not exists

在日常中使用比较多的范围查询有in、exists、not in、not exists、between and等。

explain select * from t_user where id in (2,3);
​
explain select * from t_user where id_no in ('1001','1002');
​
explain select * from t_user u1 where exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);
​
explain select * from t_user where id_no between '1002' and '1003';

上述四种语句执行时都会正常走索引，具体的explain结果就不再展示。主要看不走索引的情况：

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

explain结果：

当使用not in时，不走索引？把条件列换成主键试试：

explain select * from t_user where id not in (2,3);

explain结果：

如果是主键，则正常走索引。

查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效。

再来看看not exists：

explain select * from t_user u1 where not exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);

explain结果：

当查询条件使用not exists时，不走索引。

查询条件使用not exists时，索引失效。

order by导致索引失效

示例：

explain select * from t_user order by id_no ;

explain结果：

其实这种情况的索引失效很容易理解，毕竟需要对全表数据进行排序处理。

那么，添加删limit关键字是否就走索引了呢？

explain select * from t_user order by id_no limit 10;

explain结果：

结果依旧不走索引。在网络上看到有说如果order by条件满足最左匹配则会正常走索引， 在当前8.0.18版本中并未出现。所以，在基于order by和limit进行使用时，要特别留意。是否走索引不仅涉及到数据库版本，还要看Mysql优化器是如何处理的。

这里还有一个特例，就是主键使用order by时，可以正常走索引。

explain select * from t_user order by id desc;

explain结果：

可以看出针对主键，还是order by可以正常走索引。

另外，笔者测试如下SQL语句：

explain select id from t_user order by age;
explain select id , username from t_user order by age;
explain select id_no from t_user order by id_no;

上述三条SQL语句都是走索引的，也就是说覆盖索引的场景也是可以正常走索引的。

现在将id和id_no组合起来进行order by：

explain select * from t_user order by id,id_no desc;
explain select * from t_user order by id,id_no desc limit 10;
explain select * from t_user order by id_no desc,username desc;

explain结果：

上述两个SQL语句，都未走索引。

当查询条件涉及到order by、limit等条件时，是否走索引情况比较复杂，而且与Mysql版本有关，通常普通索引，如果未使用limit，则不会走索引。order by多个索引字段时，可能不会走索引。其他情况，建议在使用时进行expain验证。

知识来源：马士兵教育

盘一盘常见的6种索引失效情况 - 知乎

15个必知的Mysql索引失效场景，别再踩坑了！ - 知乎