持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第14天，点击查看活动详情

前言

有时候你可能会发现我们明明已经给表设置了索引，但是在查询的时候仍然很慢，很有可能是因为索引没有生效，索引并不是时时都会生效的，有些时候索引他是不会生效的！只有我们正确的使用其才会生效，以下介绍了一些可能会造成索引失效的特殊情况，希望大家在平时开发和面试的时候能够注意到！如有错误或未考虑完全的地方，望不吝赐教。

为了方便后面的示例演示，本文以user表为示例，结构如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '姓名',
  `age` int(5) DEFAULT NULL COMMENT '年龄',
  `sex` varchar(255) DEFAULT NULL COMMENT '性别',
  `class` int(2) DEFAULT NULL COMMENT '年级',
  `phone` varchar(11) DEFAULT NULL COMMENT '手机号',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_age` (`age`),
  KEY `idx_class_sex` (`class`,`sex`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;

like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

组合索引包含从左到右的字段使用索引，不包含左边的字段索引失效。

or前后条件都包含索引则走索引，or前后有一个不包含索引索引失效。

要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。

数据类型不匹配，导致索引失效。

如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引。

is null可以使用索引，is not null无法使用索引。

组合索引范围搜索，范围条件右边的列索引失效。

不等于导致索引失效，不等于的情况包括（!= 、<>、not in）。

添加索引的字段上使用函数或者计算，导致索引失效。

常见的函数有ABS,UPPER,DATE,DAY,YEAR等。

当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

总结

为了方便记，可以将以上内容总结为7个字：模型数空运最快。

• 模：代表模糊查询。使用like关键宇的时候，要是以%开头，那索引就会失效。
• 型：代表数据类型。如果数据类型错误了，那索引也会失效。
• 数：代表函数的意思。对索引的字段使用内部函数，那索引也会失效。这种情況呢应该建立基于函数的索引。
• 空：代表Null的意思。索引不存储空值，如果不限制索引列是not Null，数据库会认为索引列有可能存在空值，所以也不会按照索引进行计算。
• 运：是运算的意思。对索引列进行加、减、乘、除等运算，会导致索引失效。
• 最：是最左原则的意思。在复合索引中，索引列的顺序非常重要，如果不是按照索引列最左列，开始进行查找，则无法使用索引。
• 快：全表扫描更快的意思。如果数据库预计使用全表扫描比使用索引更快，那就不会使用索引。

以上就是mysql索引失效的情况等总结了，欢迎大家补充！