MySQL索引失效场景

like左侧模糊查询

失效: like '%abc' 模糊最左侧查询

生效: like 'a%'

OR查询

一旦使用or，除非SQL语句中全部查询字段使用索引，否则不生效

索引:

indx_a(a); indx_b(b);

生效:

select * from table where a = 'a' or b = 'b';

失效:

select * from table where a = 'a' or c = 'c';

联合索引不遵循最左侧匹配原则

索引 indx_abc(name, code, prop);

生效:

select * from table where name = 'tom';

select * from table where name = 'tom' and code = '1515';

select * from table where name = 'tom' and code = '1515' and prop = '属性';
-- 只会走 name 的索引
select * from table where name = 'tom' and  prop = '属性';

失效:

select * from table where code = '115';

select * from table where name = 'tom' and prop = '属性';

负查询

使用这些筛选条件的时候，索引会失效

NOT、!=、<>、NOT IN、NOT LIKE

字段二次处理

索引字段引用了函数或者发生了运算

SELECT * FROM table WHERE a + 5 = 10;

SELECT * FROM table where CONCAT(b, "_suffix") = "value";

索引效率低下

如果MySQL觉得走索引的速度更慢，将不会走索引