MYSQL索引失效场景

MYSQL索引失效场景

1、不满足最佳左前缀原则：where条件中要有索引第一个字段，否则该索引失效

如create index idx_table1_abc on table1(a,b,c)给a、b、c字段组合成索引，select * from table1 where b = ‘2’ and c = ‘3’;
如果没有用a字段查询，则即便使用b或c字段查询，索引idx_table1_abc 失效。

2、满足了最佳左前缀原则，但组合索引中间断了，即没有用到中间字段，那么后面的字段就没法使用索引

注意：索引还是使用的，但只对前面的字段生效
如：如create index idx_table1_abc on table1(a,b,c)给a、b、c字段组合成索引，select * from table1 where a = ‘1’ and c = ‘3’;
此时a有索引，而c字段索引无效。因为c字段前面还有一个字段b，但b字段没有用到查询中。

3、范围搜索（如between、like等）后面的条件，无法使用索引。

如：如create index idx_table1_abc on table1(a,b,c)给a、b、c字段组合成索引，select * from table1 where a = ‘1’ and b = ‘2’ and d between ‘2020-01-01’ and
‘2020-05-24’ and c = ‘3’;
此时，a、b字段索引生效，而c字段索引失效。

4、使用!=条件。

5、使用is null 或者is not null条件。

6、使用like时，如果通配符出现在最左边，则索引失效。（'aaa%'通配符在右边，索引会生效）。

解决’%aaa%'索引生效的方法是使用覆盖索引（也叫索引覆盖）。
覆盖索引的含义：select中的字段必须是加有索引的，如果即便只有有一个字段没有加索引，覆盖索引也会失效。因为覆盖索引直接从索引表中获取字段数据，而不经过数据表。
所以没有加有索引的字段就不会在索引表中存在，而却要强制获取它的数据的话，就得扫描数据表。
如：如create index idx_table1_abc on table1(a,b,c)给a、b、c字段组合成索引，select a,b from table1 where a like ‘%1%’;即可。
如果实际业务中要查出其它字段（没加有索引），则可以这样select * from table1 t where t.id in (select id rom table1 where a like ‘%1%’)即可（in语句不会导致索引失效）

7、不能在索引字段做计算、函数、类型（自动或手动）转换等等操作。如：DATE_FORMAT(r.eventtime,’%Y’) = '2020’这样会导致eventtime字段的索引失效。

8、字符串类型不加单引号会导致索引失效。如 a = 1

因为底层会把1隐式转成字符串’1’，则索引字段是不允许做操作的，否则索引失效，具体见第7步。

9、少用or，用它来做连接时会导致索引失效。如(a = ‘1’ or b = ‘2’)

后续更新：MYSQL性能优化