mysql索引失效

目录

什么是索引失效

索引失效的几个场景

1、使用了!=

2、使用了is null/is not null时可能使索引失效

3、使用了or，就要保证查询条件的每个字段都单独建立了索引，否则索引失效；

4、对索引列进行了函数操作

5、对索引列进行了表达式操作

6、mysql认为全表扫描比较快，不会使用索引

7、联合索引中，没有符合最左原则

8、查询条件使用了左模糊查询

9、索引列使用了not in

10、索引字段的值和字段类型不一致，导致类型隐式转换

---

什么是索引失效

索引失效是指查询条件中包含索引的列，但是查询没有使用到索引，而是扫描全表。

索引失效的几个场景

创建歌曲表作为案例表，表中给字段name和singer建立了联合索引。

CREATE TABLE `song` (
  `id` varchar(40) NOT NULL COMMENT '歌曲编号',
  `name` varchar(40) NOT NULL COMMENT '歌曲名',
  `singer` varchar(40) NOT NULL COMMENT '歌手',
  `url` varchar(40) NOT NULL DEFAULT '' COMMENT '歌曲文件URL',
  `is_exist` tinyint(4) NOT NULL DEFAULT 0 COMMENT '歌曲文件是否存在',
  `note` varchar(40) NOT NULL DEFAULT '' COMMENT '描述信息',
  `last_update_time` timestamp NULL DEFAULT current_timestamp() COMMENT 'last_update',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `index_1` (`name`,`singer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1、使用了!=

explain select * from song where name = "爱的供养"; -- 使用了索引
explain select * from song where name != "爱的供养"; -- 未使用索引

2、使用了is null/is not null时可能使索引失效

explain select * from song where name is not null; -- 没使用索引

3、使用了or，就要保证查询条件的每个字段都单独建立了索引，否则索引失效；

给singer列加上索引之后，以下两条语句均使用上了索引。（测试完成后，删除了singer的索引，防止影响后面的测试）

explain select * from song where name = "爱的供养" or singer = "周杰伦"; -- 使用了索引
explain select * from song where name = "爱的供养" and singer = "周杰伦"; -- 不使用索引

4、对索引列进行了函数操作

explain select * from baoshi where round(category_id) = 7; -- 没有使用索引

5、对索引列进行了表达式操作

explain select * from song where id + 1= 2; -- 没有使用索引，全表扫描

6、mysql认为全表扫描比较快，不会使用索引

7、联合索引中，没有符合最左原则

即使用左边的索引列，如通过singer查询不会使用索引

explain select * from song where singer= "周杰伦"; -- 全表扫描，不走索引

8、查询条件使用了左模糊查询

explain select * from song where name like "%的葬礼"; -- 不走索引
explain select * from song where name like "玫瑰花%"; -- 走索引

9、索引列使用了not in

以下条件的值取的是前三条数据的id

explain select * from song 
where id in ('20210522153649', '20210522153812', '20210522153941'); -- 走索引

explain select * from song 
where id not in ('20210522153649', '20210522153812', '20210522153941'); -- 不走索引

10、索引字段的值和字段类型不一致，导致类型隐式转换

如下，主键为varchar类型，通过整数值查询不会走索引

explain select * from song where id = 20210522153649; -- 不走索引