mysql索引失效的情况

1. 使用or操作符

当where语句中使用or操作符，MySQL无法使用索引，会转向全表扫描。因此，应尽量避免使用or操作符。

原因：因为MySQL中的索引是根据某个字段进行排序建立的。当使用or操作符，说明有两个条件其中某个条件成立即可，而我们使用某个索引时只能判断出对应字段的条件是否成立，即使不成立，另一个条件成立时该记录也符合我们要查询的结果。所以使用索引无法做出判断。

例：

-- id为主键索引,age为普通索引

EXPLAIN select * from `user` where id>3 or age<33

 可以看出type为ALL：全表扫描

2. 复合索引最左原则

-- 建立联合索引:age_name

EXPLAIN select * from `user` where name=22 and age=33

 可以看出type为ref：使用了二级索引；（当使用二级索引列于常数进行等值比较时，访问方法为ref）

不使用第一列:

EXPLAIN select * from `user` where name=22

 可以看出type为ALL：全表扫描

3. like查询

如果使用了like且以%开头，则索引会失效。

原因：模糊查询一般用在字符串的字段上，而字符串的排序规则为按字母字典序排序，如果以%开头，表示前面的字符取啥都行，则无法使用索引。

例：

EXPLAIN select * from `user` where name like "22%" 

 可以看出type为range：使用二级索引进行范围查询。

EXPLAIN select * from `user` where name like "%22" 

 可以看出type为ALL：全表扫描

4. 索引列上使用函数

原因：因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。

EXPLAIN select * from `user` where length(name)=22

 可以看出type为ALL：全表扫描

5. 隐式类型转换

隐式类型转换会导致索引失效，比如当字段类型为字符串且建有索引，而查询条件类型为数值时，会将字符串类型隐式转换为浮点型，此时索引会失效。

原因：字符串类型转换为浮点数会使用cast函数，此时索引列上使用函数，导致索引失效。

explain SELECT * FROM `user` where name=33;

 可以看出type为ALL：全表扫描

6. 对索引进行表达式计算

原因：因为索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值，所以无法走索引，只能通过把索引字段的取值取出来，然后进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

explain SELECT * FROM `user` where age+1=33;

 可以看出type为ALL：全表扫描

 explain SELECT * FROM `user` where age=33;