索引失效的情况

为了更好的举例，新建一张学生表，并添加id为主键索引，name为普通索引，(name,age)为组合索引

CREATE TABLE `student` (
  `id` int NOT NULL COMMENT 'id',
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE,
  KEY `idx_name_age` (`name`,`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

插入数据

INSERT INTO `student` VALUES (1, '张三', 18, '2021-12-23 17:12:44');
INSERT INTO `student` VALUES (2, '李四', 20, '2021-12-22 17:12:48');

1.在where子句中，如果在OR前的条件列是索引列，而在OR后的条件列不是索引列，索引会失效

例：

explain SELECT * FROM `student` where id =1 

此时命中主键索引，当查询语句带有or后

explain SELECT * FROM `student` where id =1 or birthday = "2021-12-23"

发现此时type=ALL,全表扫描，未命中索引
总结：查询条件中带有or,除非所有的查询条件都建有索引，否则索引失效
总结：查询条件中带有or,除非所有的查询条件都建有索引，否则索引失效

2. ‘like %xx’ 或 'like %xx%'会造成索引失效

例

explain select * from student where name = "张三"

非模糊查询，此时命中name索引，当使用模糊查询后

explain select * from student where name like "%三"

发现此时type=ALL，全表扫描，未命中索引

3. 在查询条件中对索引列做了计算、函数、类型转换操作，都会导致索引失效

例

explain select * from student where id-1 = 1

查询条件为id，但是并没有命中主键索引，因为在索引列上参与了计算
正例

select * from student where id = 2

总结：将参与计算的数值先算好，再查询

4. 违背最左匹配原则

什么是最左匹配原则？
比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：
where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；
需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。
但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:
where b=2；
where c=3；
where b=2 and c=3；
上面这些查询条件之所以会失效，是因为(a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。
值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。也就是：

select * from t where a=1 and b>1 and c =1; 

这样a,b可以用到（a,b,c），c索引用不到。
但是如果是建立(a,c,b)联合索引，则a,b,c都可以使用索引，因为优化器会自动改写为最优查询语句。
对于 >=、<=，例如

select * from t_table where a >= 1 and b = 2

a 和 b 字段都用到了联合索引进行索引查询。
对于between，例如

SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2

MySQL 中，BETWEEN 包含了 value1 和 value2 边界值，类似于 >= and =<。而有的数据库则不包含 value1 和 value2 边界值（类似于 > and <）。
这里我们只讨论 MySQL。由于 MySQL 的 BETWEEN 包含 value1 和 value2 边界值，所以这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。
例

explain select * from student where age = 18

age的索引是和建立再(name,age)组合索引的基础上，当查询条件中没有第一个组合索引的字段(name)会导致索引失效
正例

explain select * from student where age =18 and name ="张三"

此时才会命中name和(name,age)这个索引

5. 如果列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则不走索引

例

explain select * from student where name = "张三"

此时命中name索引，当数据未携带引号后

explain select * from student where name = 2222

此时未命中name索引，全表扫描