目录
联合索引不满足最左匹配原则
联合索引遵从最左匹配原则,在联合索引中,最左侧的字段优先匹配,因此,在创建联合索引时,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是有优化器的,当优化器在某些情况下认为全表扫描比索引快,那就不会走索引了