MYSQL 索引失效的十个场景（一）

一、查询条件包含or，可能导致索引失效

新建一个student表，它有一个普通索引userId，结构如下：

CREATE TABLE `student` (
  `id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `score` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_name_IDX` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

• 执行查询sql(未包含or)，它是会走索引的，如下图所示： 

explain select * from student s where name ='liuliu' 

•  执行查询sql(包含or,且score未添加索引)，它就不走索引，如下图所示：

explain select * from student s where name ='liuliu' or score =80

分析&结论：

• 对于or+score没有索引的这种情况，假设它走了name的索引，但是走到score查询条件时，它还得全表扫描，假如走索引也就是需要三步过程：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，就只需一步；mysql的查询优化器，处于效率与成本考虑，遇到这种情况，明显全表扫描更合理。

注意: 如果or条件的列都加了索引，索引有可能会走，也有可能不会走。

 二、字段类型不匹配，可能导致索引失效

• 执行查询sql(name类型是vachar，传入是数值类型)

分析与结论：

• 类型不匹配，MySQL会做隐式的类型转换，索引失效

 三、like通配符（前缀模糊匹配）可能导致索引失效

并不是用了like通配符，索引一定失效，而是like查询是以%开头（前缀模糊匹配），才会导致索引失效。

• like查询是以%开头

explain select * from student s where name like '%zhang%'

 

•  like查询是不以%开头

explain select * from student s where name like 'zhang%'

四、联合索引，查询时的条件列不包含联合索引中的第一个列，索引失效 

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `score` decimal(10,2) DEFAULT NULL,
  `subject` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '科目',
  `create_time` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `student_name_IDX` (`name`) USING BTREE,
  KEY `student_subject_IDX` (`subject`,`score`) USING BTREE,
  KEY `student_create_time_IDX` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

表结构student有一联合索引： `student_subject_IDX` (`subject`,`score`)

• 包含联合索引的第一列：走联合索引

explain select * from student s where score >80 and subject ='英语'

• 不包含联合索引的第一列：不会走联合索引

explain select * from student s where score >80 

 

分析与结论：

• 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。

• 联合索引不满足最左原则，索引一般会失效。

 五、在索引列上使用mysql的内置函数，索引失效。

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `score` decimal(10,2) DEFAULT NULL,
  `subject` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '科目',
  `create_time` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `student_name_IDX` (`name`) USING BTREE,
  KEY `student_subject_IDX` (`subject`,`score`) USING BTREE,
  KEY `student_create_time_IDX` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

表结构student有一索引：`student_create_time_IDX` (`create_time`)

• 不使用mysql函数：会走索引

explain select * from student s where create_time='2023-02-02'

• 使用mysql函数：不会走索引

explain select * from student s where date(create_time)='2023-02-02'

注意：从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据