常见的mysql索引失效的场景

目录

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

索引列参与了运算

索引列使用了函数

like进行左匹配使用

发生了类型转换

使用了or

进行了两列的比较

进行不等于比较

is not null

not in和not exists

order by

范围导致索引失效

其他优化策略

---

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

联合索引遵从最左匹配原则，在联合索引中，最左侧的字段优先匹配，因此，在创建联合索引时，where语句中使用最频繁的字段应该放在联合索引的最左侧

而查询时，如果想要查询条件走索引，就必须满足：最左边的字段要出现在查询条件中        

比如，我们创建一个联合索引

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

select * from t_user where message = '1002' and username = 'admin';  这是走索引的

select * from t_user where username = 'admin';  这是不走索引的

索引列参与了运算

假设我们还有一个字段id，也有索引，我们执行如下SQL

select username, age from user where id + 1 = 2;

可以看到，id这一列参与了运算，那么，这条SQL也不会走id的索引

当然，这种情况不止是不走索引，还会增加DB的负担，DB需要扫描全表的id字段，并且进行计算，然后和参数值进行比较，所以如果有这样的要求，建议想办法先计算出一个准确的结果

索引列使用了函数

如果执行如下SQL，索引也会失效

select * from user where SUBSTR(id,1,3) = '999';

这种情况与上面的情况一样，DB都要扫描全表，然后进行截取，再和条件对比

当然，这里只是举例这个函数，并非只有这个函数，像CONCAT等类似的函数，也都会出现类似的情况

like进行左匹配使用

select username, age from user where username like '%dmin';

这种不走索引的情况很容易理解，索引本身就相当于目录，从左到右挨个排查，最左侧使用了占位符，那当然会失效

发生了类型转换

select username, age from user where tag = 22;

假设tag的类型是varchar，我们在SQL语句里是个int，那就会导致全表扫描

不过这儿有一个特例

select username, age from user where tag = '22';

如果tag是一个int，我们用了引号表示是个字符串，mysql会把字符串转为int，它依旧会走索引

使用了or

假设id字段有索引，age字段没有索引，执行如下SQL，是不会走索引的

select username from user where id = 1 or age = 20;

这儿啊，不要想当然的认为id有索引啊，为什么会失效？

你得这么想，如果单独使用age字段，明显走全表，那都走了全表了，id走索引有啥用呢？

所以，如果用or，那就两个都加索引，否则索引也会失效

不过，这儿也要注意啊，如果or两边同时使用 >和 <，索引也会失效的

进行了两列的比较

比id和age都有数据，但是在查询中对两列的数据做了比较，也会导致索引失效

select * from user where id > age;

进行不等于比较

select * from user where id <> 2;

以为id是个主键，所以还是会走索引的

但是如果是这个SQL

select * from user where message = 'hello';

当结果集比较小的时候，会走索引，但如果结果集很大，就不会走索引

is not null

is null会正常走索引，而is not null不会正常走索引

not in和not exists

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

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

order by

其实用order by的情况下索引失效很好理解，毕竟需要对全表的数据进行处理，就算加了limit也不好使（但是之后的版本不好说）

不过，如果是对主键进行一个order by，那就好使了

 但是如果只是普通索引，那也不行

如果查询条件包含了索引列，那么order by索引列也是可以走索引的

这块比较复杂，建议使用前，先explain一下

范围导致索引失效

比如我要查询一年前的数据，一年前就只有几条数据，那么走索引

如果我查询今天之前的数据，有很多条，那就不会走

这就是因为DB发现全表扫描比走索引效率更高，那就放弃了索引

当查询条件为大于等于、in等范围查询时，根据查询结果占全表数据比例的不同，优化器有可能会放弃索引，进行全表扫描

其他优化策略

mysql是有优化器的，当优化器在某些情况下认为全表扫描比索引快，那就不会走索引了