索引失效

发表于 2021-04-11 更新于 2024-12-24 分类于开发，数据库， MySQL 阅读次数：

索引是数据库中提高查询性能必不可少的一环，当SQL语句中使用不当时会导致索引失效，现在把知道的导致索引失效的原因记录下来。

不使用索引

表中建立了索引，但是完全不会使用索引的情况

当没有查询条件时，是获取全表的数据，因此是直接走的全表扫描
1
select * from test
提示不使用索引，当SQL语句中明确指定不使用某个列上的索引的话，数据库也是不会使用该索引的，idx_col指代某个建立了索引的索引列
1
select * from test ignore index(idx_col)
查询条件没有建立索引
查询条件没有使用到索引列

索引失效

表中建立了索引，查询条件中也有索引列，但是索引依然失效的几种情况

（重要）数据库引擎cost成本分析走索引的成本更高，认为全表扫描好过使用索引，可能出现的场景：表的数据量过少、索引建立不合理导致通过索引查询出来的数据太多

索引列上做操作

索引列进行了数学运算
1
select * from test where id + 1 = 1997

索引列使用了函数

1	select * from test where name = upper(name)

查询条件中，索引列的类型和传入的参数类型不一致，需要类型转换，例如：索引列id类型是int，但传入的参数类型是char；字符串条件没有使用引号
1
2
select * from test where id = '1';
select * from test where name = 999;
复合索引未符合最左匹配原则，例如：符合索引由A、B、C三个列组成，但查询时where条件中没有A列的条件
1
select * from test where b = param_b and c = param_c
索引列中的模糊搜索like以通配符*%*开头，类似于查字典中按偏旁查找但是不记得第一个偏旁一样，是无从下手找不到的
1
select * from test where name like '%abc'
相反如果是以通配符*%*结尾是会走索引的
1
select * from test where name like 'abc%'
使用了不等于（!=、<>），MySQL中使用不等于（!=、<>）时无法使用索引，但覆盖索引除外
1
2
select * from test where age != 20;
select * from test where age <> 20;
IS NULL、IS NOT NULL条件

IS NULL、IS NOT NULL都是可以走索引的，主要和数据库引擎分析得出的成本有关，成本低就走，成本高就不走。

注：MySQL中的二级索引是会记录空值的，空值放在B+树的最左侧，
查询条件中包含or（尽量少用）
- or左右两则的索引列相同（命中同一个索引列），在较早期的版本是不走索引的，但是在新的版本（8.0）是走索引的（索引合并）
  1
  select * from test where id = 199 or id = 200
- or左右两则的索引列不相同或者有一则不是索引列，是不会走索引的
  1
  2
  select * from test where key1 = 199 or key2 = 'demo';
  select * from test where key1 = 199 or col = 'demo'
IN条件

在MySQL4.0以下是不走索引的，在4.1版本以上才走索引，但是如果IN后面跟的条件太多可能会不走索引，用的时候最后看一下执行计划
1
2
select * from test where id in ('1', '2'); #走索引
select * from test where id in ('1', '2', '3'......); #条件太多可能不走索引
NOT IN条件

查询提条件中包含NOT IN是不会走索引的，可以这样理解：不知道一条数据是不是NOT IN条件，只能查出来再判断，因此是不走索引的。

总结

MySQL下索引失效的情况比较复杂，特别是查询条件中的or、in、not in、exists、not exists，因此用explain看一下执行计划比较稳当。上面写的可能也不是全对的，后面再深入了解一下，回头再完善。