什么情况下MySQL的索引会失效

索引查询失效的几个情况：

在某些情况下我们会发现一个问题，明明这个字段添加了索引，但是奇怪的是是查询的时候索引并没有生效，下面就这几种索引失效的情况做以总结。假如我们创建了一个 test表及相关索引。

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `gender` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_age_gender` (`age`,`gender`) USING BTREE,
  KEY `index_name` (`name`(191)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

1、使用like关键字时

当使用like关键字时，如果查询条件以%开头，索引无效；当like前缀没有%，后缀有%时，索引依然有效。

	EXPLAIN SELECT * FROM test WHERE `name` LIKE "%";
	EXPLAIN SELECT * FROM test WHERE `name` LIKE "%1";

	EXPLAIN SELECT * FROM test WHERE `name` LIKE "1%";
	EXPLAIN SELECT * FROM test WHERE `name` LIKE "1";

2、使用or关键字时

当使用or关键字时，or语句前后没有同时使用索引或当or关键字左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。

id为主键索引，gender没有索引

EXPLAIN SELECT * FROM test WHERE (id = 1 OR gender = 1);

id和name均有索引

EXPLAIN SELECT * FROM test WHERE (id = 1 OR `name` = '测试2');

3、组合索引

使用组合索引时，如果查询条件不包括该组合索引全部字段或查询条件不是该组合索引左边第一个字段时，索引失效。

gender为组合索引右边的字段

EXPLAIN SELECT * FROM test WHERE gender = 1;

age为组合索引左边的字段

EXPLAIN SELECT * FROM test WHERE age = 20;
EXPLAIN SELECT * FROM test WHERE age = 20 AND gender = 1;

4、数据类型

数据类型出现隐式转化。如某个索引字段的数据类型为varchar，查询内容为123，如不加引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

参数为：123

EXPLAIN SELECT * FROM test WHERE name = 123;

参数为：“123”

EXPLAIN SELECT * FROM test WHERE name = '123';

5、在索引字段上使用not及运算符

not不会用到索引的，因此对它的处理只会产生全表扫描。

EXPLAIN SELECT * FROM test WHERE `name` IS NOT NULL;

运算符同样不会用到索引

EXPLAIN SELECT * FROM test WHERE id = id + 1

6、对索引字段使用函数

对索引字段进行计算操作、字段上使用函数，索引失效。

EXPLAIN SELECT * FROM test WHERE ABS(id) = 1;

7、全表扫描的速度大于索引速度

全表扫描的速度大于索引速度时，索引失效。如表内数据极少。