MySQL学习笔记（十四）索引失效有哪些情况？_mysql order by 索引失效

1.表和数据

CREATE TABLE `t_user` (
  `id` bigint(32) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(40) DEFAULT NULL COMMENT '用户名',
  `user_code` varchar(40) DEFAULT NULL COMMENT '用户编号',
  `phone` varchar(11) DEFAULT NULL COMMENT '电话',
  `age` tinyint(3) DEFAULT NULL COMMENT '年龄',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` varchar(32) DEFAULT NULL COMMENT '更新时间',
  `address` varchar(100) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_update_time` (`update_time`),
  KEY `idx_phone` (`phone`) USING BTREE,
  KEY `idx_name_code_add` (`user_name`,`user_code`,`address`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='用户表';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('1', '张三', '10021', '18500123322', '22', '2022-12-03 17:17:08', '2022-12-04 17:17:08', '天津市滨海区水利路11号');
INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('2', '张龙', '10022', '18500123323', '23', '2022-12-03 17:17:08', '2022-12-01 17:17:08', '北京市海淀区五道口33号');
INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('3', '张哥', '10023', '18500123324', '24', '2022-11-03 17:17:08', '2022-12-02 17:17:08', '北京市海淀区五道口33号');
INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('4', '张明', '10024', '18500123325', '25', '2022-10-03 17:17:08', '2022-12-03 17:17:08', '北京市海淀区五道口33号');
INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('5', '王子', '10025', '18500123326', '26', '2022-12-01 17:17:08', '2022-12-04 17:17:08', '北京市海淀区五道口33号');
INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('6', '李明', '10026', '18500123327', '33', '2022-12-02 17:17:08', '2022-11-05 17:17:08', '北京市海淀区五道口33号');
INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('7', '陈哥', '10027', '18500123328', '44', '2022-08-03 17:17:08', '2022-10-06 17:17:08', '北京市海淀区五道口33号');
INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('8', '刘志', '10028', '18500123329', '55', '2022-07-03 17:17:08', '2022-09-07 17:17:08', '北京市海淀区五道口33号');
INSERT INTO `eom`.`t_user` (`id`, `user_name`, `user_code`, `phone`, `age`, `create_time`, `update_time`, `address`) VALUES ('9', '黄强', '10029', '18500123333', '66', '2022-06-03 17:17:08', '2022-08-08 17:17:08', '北京市海淀区五道口33号');


1
2
3
4
5
6
7
8
9
10
11

2.key_len计算规则

EXPLAIN select * from t_user where user_name = '张龙' 
1

user_name 类型为varchar(40)，字符集为utf8，也就是使用3个字节来表示一个完整的UTF-8。此时如果这个字段命中索引，key_len = 40* 3 =120；
由于该字段类型varchar为变长数据类型，需要再额外添加2个字节。此时，key_len = 120 + 2 = 122；
由于该字段允许为NULL（default NULL），需要再添加1个字节。此时，key_len = 122 + 1 = 123；

3.最左匹配原则

1

3.1.不命中的情况

EXPLAIN SELECT * FROM t_user WHERE user_code = '10022' AND address = '北京市海淀区五道口33号'  
1

条件里面没有添加user_name，无论剩余2个条件是否添加，或者顺序是否按照索引，只要user_name 没有就无法命中索引，这就是最左匹配

注意这里有个情况，就是覆盖索引的情况，例如上面的sql不用select*，改为下面的

EXPLAIN SELECT user_name,user_code,address FROM t_user WHERE user_code = '10022' AND address = '北京市海淀区五道口33号'  
1

可以看出，就会命中索引，此时命中的是覆盖索引

3.2.命中的情况

• 按照索引顺序3个添加都加上，此时看key_len的长度是3个字段都命中了

EXPLAIN select * from t_user where user_name = '张龙' and user_code = '10022' and address = '北京市'
1

• 按照索引顺序只添加前面2个条件，只命中了前面2个字段

EXPLAIN select * from t_user where user_name = '张龙' and user_code = '10022' 
1

• 把顺序打乱，发现还是会命中索引，而且3个字段都命中了，这是因为MySQL优化器会把顺序优化成索引的顺序

EXPLAIN SELECT * FROM t_user WHERE address = '北京市海淀区五道口33号' AND user_code = '10022' AND user_name = '张龙'
1

总结，只要有user_name字段就会命中索引，不管后面那2个条件是否有，或者是否按照索引顺序，都会命中索引的，只不过key_len长度不同

4.索引列参与运算

EXPLAIN SELECT * FROM t_user where id + 1 = 2
1

如图主键id+1=2就不会命中索引，这种情况改成where id = 2 - 1就会命中主键索引

5.索引列使用函数

EXPLAIN SELECT * FROM t_user where SUBSTR(phone,1,3) = '100';
1

如图phone这个字段有索引，如果使用函数的话就会失效，全表扫描

6.like %xxx%

EXPLAIN SELECT * FROM t_user where phone like '%100%';
1

如图like模糊匹配左右2边都是% 会索引失效，但是如果改成100%就会命中索引

7.字段类型转换

EXPLAIN SELECT * FROM t_user where phone = 100
1

如图，phone是varchar类型，但是where 条件是=100，变成int型了，phone就会转化成int型，字段的类型就变了，就变成全表扫描了

但是有种情况是例外，就是int型=字符串，此时MySQL会把’1’转换成int型，所以就会命中索引

EXPLAIN SELECT * FROM t_user where id = '1'
1

8.日期类型like

EXPLAIN SELECT * FROM t_user where create_time like '2022-12-03%'
1

日期类型，datetime，date如果使用like是不会走索引的，因为字段类型不一样，这实际上跟上面的字段转换是同一个问题

9.or操作

EXPLAIN SELECT * FROM t_user where phone = '111' or address = '222'
1

如图，or前后有2个条件，phone 是有索引，address 没有所索引，这会导致索引失效，全表扫描

而如果or前后都是索引字段，phone和update_time 都是索引字段，就会命中索引，如下

EXPLAIN SELECT * FROM t_user where phone = '111' or update_time = '222'
1

10.is not null

EXPLAIN SELECT * FROM t_user where phone is not null
1

is not null不会走索引，is null可以命中索引

11.not in

EXPLAIN SELECT * FROM t_user where phone not in ('11','22')
1

in 会走索引，not in 不会走索引

但是有个例外情况就是如果换成主键id,就会走索引

EXPLAIN SELECT * FROM t_user where id not in (1,2)
1

12.order by

EXPLAIN SELECT * FROM t_user ORDER BY phone
1

order by 普通索引会失效

这里如果不用select * ，直接查字段会走覆盖索引

EXPLAIN SELECT phone FROM t_user ORDER BY phone
1

但是如果换成order by id，主键索引会命中

EXPLAIN SELECT * FROM t_user ORDER BY id
1

13.不等于

EXPLAIN SELECT * FROM t_user where  phone != '11'
1

EXPLAIN SELECT * FROM t_user where  phone <> '11'
1

不等于不会走索引，但是主键索引，和覆盖索引会走索引，这2个情况是例外

14.其他情况

当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。也就是说，当Mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描。某些场景下即便强制SQL语句走索引，也同样会失效。

类似的问题，在进行范围查询（比如>、< 、>=、<=、in等条件）时往往会出现上述情况，而上面提到的临界值根据场景不同也会有所不同。

15.索引的规范使用

索引是提高数据库查询性能的一个重要方法。

使用索引用可快速找出某个列中包含特定值的行。不使用索引，必须从第一条记录开始读，可能要读完整个表，才能找出相关的行。

使用索引就像查字典一样，我们可以根据拼音、笔画、偏旁部首等排序的目录(索引)，快速查找到需要的字。

下面总结了一些规范建议，可以用来参考，并非绝对真理。

• 单表的索引数建议不超过5个，组合索引的字段原则上不超过3个。

• 尽量不要在较长字符串的字段上建立索引，可以设置索引字段前缀长度。

• 选择在查询过滤中使用率较高，如where，orderby，group by的列建立索引。

• 不要在区分度不高的列上建立索引，比如性别等，利用不了索引性能。

• 不要在经常更新的列上建立索引，数据更新也会更新索引，影响数据库性能。

• 建立组合索引时，区分度最高，或者查询频率最高的，放在最左侧。

• 合理利用覆盖索引来满足查询要求，避免回表查询，减少I/O开销。

• 删除不再使用、少使用、或者重复的索引，减少数据更新的开销。

• 利用explain来判断查询语句，是使用了索引，还是走了全表扫描。