MySQL索引失效的几种情况

MySQL索引失效的几种情况

1、全值匹配我最爱 （这种情况下索引不会失效）
全值匹配意思就是联立的复合索引的顺序和个数要和检索的条件顺序和个数相同。

2、最佳左前缀法则（重要）
最佳左前缀法则是指，如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

CREATE TABLE `sys_user` (
  `id` varchar(64) NOT NULL COMMENT '主键',
  `name` varchar(64) DEFAULT NULL COMMENT '名字',
  `age` int(64) DEFAULT NULL COMMENT '年龄',
  `pos` varchar(64) DEFAULT NULL COMMENT '职位',
  PRIMARY KEY (`id`),
  KEY `idx_sys_user_nameAgePos` (`name`,`age`,`pos`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

（1）EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java';
（2）EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22 ;
（3）EXPLAIN SELECT * FROM sys_user WHERE name='小明'  AND pos ='java';

（违反了最左左前缀原则，因为复合索引最左边的是name，当检索条件name没在前面索引将失效，第一种情况满足了全值匹配，第二种满足了两个字段name和age，第三种因为只满足了name，所以索引只用到name。）

3、不在索引列上做任何操作（计算、函数（自动或手动）类型转换），会使索引失效转为全表扫描

4、存储引擎不能使用索引中范围条件右边的列

5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致），减少select * 的使用

6、mysql在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

7、is null，is not null 也无法使用索引

8、like以通配符开头（’%abc…’）mysql索引会失效变成全表扫描的操作，（%写右边则可以避免索引失效，如果业务实在需要’%abc…%'则可以用覆盖索引避免索引失效）

9、字符串不加单引号索引会失效

10、少用or,用它连接时会索引失效