Mysql索引失效情况最全总结

Mysql索引失效的几种情况（概览）

我们在使用MySQL数据库时索引在可以极大的提高查询效率，然而，有时候我们用的不恰当时候反而导致索引失效。如果mysql估计使用全表扫描要比使用索引快,则不使用索引（数据量较小或其他）

1. 模糊查询时like后面后面紧跟着%，例如：‘like %###’；
2. or语句前后没有同时使用索引；
3. 组合索引中不是使用第一列索引；
4. 在索引列上使用“IS NULL”或“IS NOT NULL”操作；
5. 在索引字段上使用“not”，“<>”，“!=”
6. 被索引字段，发生了隐式类型转换（例如：索引字段（phone）是varchar 写sql时候写成 phone=13782011111正确应是phone=‘13782011111’
7. 被索引字段使用了表达式计算
8. 被索引字段使用了函数

接下来我们创建一个user表，来解释以上索引失效的具体情况

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `age` int(10) NOT NULL,
  `phone` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`),
  KEY `ids_phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
#插入一条数据
INSERT INTO `ruoyi`.`t_user`(`id`, `name`, `age`, `phone`) VALUES (1, 'lisi', 20, '18382011111');
INSERT INTO `ruoyi`.`t_user`(`id`, `name`, `age`, `phone`) VALUES (2, '张三', 25, '18600010000');

1、like关键字后使用左模糊匹配’%##’

1.1、放在最左边索引失效

1.2、放在最右面就不会出现索引失效

2、or语句前后没有同时使用索引

当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

2.1、 or左右只有一个索引时候索引失效

2.2、or左右查询字段均为索引时

3、组合索引中不是使用第一列索

###3.1、修改我们的表，表中除了主键索引外，我们再添加一个联合索引id_age_phone，在这个联合索引中包含了age字段和phone字段，并且age字段在最左边。
不符合最左原则索引失效

age放在最左边的时候索引生效

4、在索引列上使用“IS NULL”或“IS NOT NULL”操作

user表name字段加上索引

5、在索引字段上使用“not”，“<>”，“!=”

6、被索引字段，发生了隐式类型转换

phone字段是varchar类型，我们可以看到虽然查询语句中的phone的值是一个数字类型的值，与phone的字符串类型不匹配，依然可以查到我们想要的结果，但是在sql执行过程中并没有使用到索引。

只有这样写的时候索引才生效

7、被索引字段使用了表达式计算

8、被索引字段使用了函数