一、查询条件包含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 开始,索引特性增加了函数索引,即可以针对函数计算后的值建立一个索引,也就是说该索引的值是函数计算后的值,所以就可以通过扫描索引来查询数据