MySQL索引失效场景

MySQL的版本

# 8.0.25
SELECT VERSION();

建立一张电影表，id为自增主键，在ranks上建一个索引，在type，type，name上建立一个联合索引。

DROP TABLE IF EXISTS `movie`;

CREATE TABLE `movie` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `code` varchar(50) DEFAULT NULL COMMENT '电影编号',
  `type` int DEFAULT NULL COMMENT '电影类型',
  `name` varchar(50) DEFAULT NULL COMMENT '电影名称',
  `ranks` int DEFAULT NULL COMMENT '排名',
  `remark` varchar(200) DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`id`),
  KEY `idx_code_type_name` (`code`,`type`,`name`),
  KEY `idx_ranks` (`ranks`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

这个表插入几条数据：

LOCK TABLES `movie` WRITE;
INSERT INTO `movie` (`id`, `code`, `type`, `name`, `ranks`, `remark`)
VALUES
    (1,'101',1,'流浪地球',9,'再次打开了一扇门'),
    (2,'202',2,'满想红',3,'真的想红啊'),
    (3,'303',30,'深海',8,'国漫崛起');
UNLOCK TABLES;

通过explain查看哪些情况会走索引

1. 最左匹配原则

# 走联合索引
explain select * from movie where code = '101' and type = 1 and name = '流浪地球';
# 走联合索引
explain select * from movie where code = '101' and type = 1;
# 走联合索引
explain select * from movie where code = '101';
# 走联合索引 但只走了code， 没用到name
explain select * from movie where code = '101' and name = '流浪地球';
# 走联合索引 但只走了code， 没用到name
explain select * from movie where name = '流浪地球' and code = '101';
# 不走索引
explain select * from movie where type = 1 and name = '流浪地球';

2. 使用select *

# 不走索引 全表扫描
explain select * from movie;
# 走联合索引 覆盖索引
explain select code,type,name from movie;
# 走ranks普通索引 代价最小 
explain select id from movie;

3. order by

# 不走索引 ranks的索引和code，type，name不在一个索引中
explain select code,type,name,ranks from movie order by ranks;
# 走普通索引
explain select ranks from movie order by ranks;
# 不走索引
explain select * from movie order by ranks;

4. 索引列上有了计算/函数

# 走普通索引
explain select * from movie where ranks = 1;
# 不走索引
explain select * from movie where ranks + 1 = 1;
# 
explain select * from movie where ifnull(ranks, 0) = 1;

5. 字段类型不同

# 走普通索引
explain select * from movie where ranks = 9;
# 走普通索引 隐式类型转换 varchar转int
explain select * from movie where ranks = '9';
# 走联合索引
explain select * from movie where code = '101';
# 不走索引 类型不一致 code是varchar 101是int
explain select * from movie where code = 101;

6. like

# 不走索引
explain select * from movie where code like '%1';
# 不走索引
explain select * from movie where code like '%1%';
# 走联合索引
explain select * from movie where code like '1%';
# 走联合索引 覆盖索引
explain select code,type,name from movie where code like '%1';
# 走联合索引 覆盖索引
explain select code,type,name from movie where code like '%1%';
# 走联合索引 覆盖索引
explain select code,type,name from movie where code like '1%';

7. 列对比

# 不走索引
explain select * from movie where id = ranks;
# 走普通索引
explain select ranks from movie where id = ranks;
# 走联合索引
explain select code, type, name from movie where id = ranks;

8. 使用or

# MySQL 5.7 不走索引
# MySQL 8.0 走索引 id和ranks都有索引
explain select * from movie where id = 1 or ranks = 9;
# 不走索引
explain select * from movie where id = 1 or remark = '备注';

9. 使用 !=

# MySQL 5.7 中 返回结果集大小大于20%不走索引 否则走
# MySQL 8.0 走索引
explain select * from movie where ranks != 1;
# MySQL 5.7 中 返回结果集大小大于20%不走索引 否则走
explain select * from movie where ranks > 1;

最后编辑于：2023.03.28 22:29:29