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觉得走索引的速度更慢,将不会走索引