MySQL索引失效

​ 简单介绍下几种MySQL索引失效的常见情况。

1.数据准备

​ 首先准备一张数据表user_info并建立索引

`CREATE TABLE `user_info` ( 
`id` int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', 
`number` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '编号', 
`username` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名', 
`age` int(11) DEFAULT NULL COMMENT '年龄', 
`birthday` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '生日', 
PRIMARY KEY (`id`), KEY `union_idx` (`number`,`username`,`age`), 
KEY `create_time_idx` (`birthday`) );`

该表包含3个索引：

主键：id

联合索引：number、username、age

普通索引：birthday

然后插入一些数据

INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '1244', 'Mercury', 23, '2000-01-01 00:00:00'); 
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '3546', 'Diana', 12, '2011-01-01 00:00:00'); 
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '1124', 'Mars', 77, '1946-01-01 00:00:00'); 
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '6426', 'Saturn', 32, '1991-01-01 00:00:00');
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '3525', 'Eureka', 32, '1991-01-01 00:00:00');
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '5245', 'Mercury1', 23, '2000-01-01 00:00:00'); 
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '3235246', 'Diana1', 12, '2011-01-01 00:00:00'); 
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '6346', 'Mars1', 77, '1946-01-01 00:00:00'); 
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '623461', 'Saturn1', 32, '1991-01-01 00:00:00');
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '235', 'Eureka1', 32, '1991-01-01 00:00:00');

INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '11244', 'Mercury3', 23, '2000-01-01 00:00:00'); 
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '13546', 'Diana3', 12, '2011-01-01 00:00:00'); 
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '112244', 'Mars3', 77, '1946-01-01 00:00:00'); 
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '643126', 'Saturn3', 32, '1991-01-01 00:00:00');
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '35215', 'Eureka3', 32, '1991-01-01 00:00:00');
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '52145', 'Mercury4', 23, '2000-01-01 00:00:00'); 
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '32235246', 'Diana4', 12, '2011-01-01 00:00:00'); 
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '6332446', 'Mars4', 77, '1946-01-01 00:00:00'); 
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '6231461', 'Saturn4', 32, '1991-01-01 00:00:00');
INSERT INTO user_info (id, number, username, age, birthday) VALUES (null, '231115', 'Eureka4', 32, '1991-01-01 00:00:00');

注：测试MySQL版本为8.0.28

2.案例测试

2.1 联合索引不满足最左匹配原则

​ 最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左列开始匹配。如本例中联合索引（number，username，age），若想查询走该索引，查询条件中应出现最左边的列，即number。

测试1：

explain select * from user_info where number = '1244';

运行结果：

key为“union_idx”说明查询走了联合索引。

测试2：

explain select * from user_info where number = '1244' and age = 23;

测试2结果中‘key_len’与测试1相同，说明该查询虽然走了联合索引，但因未满足最左匹配原则（查询条件中未出现username），导致username之后的联合索引失效。若number使用范围查询如number>‘1244’，后面的查询条件即使有username也不会生效，这里不做测试。

但是where后面查询列出现顺序不会影响索引，如

测试3：

explain select * from user_info where username = 'Mercury' and number = '1244';
explain select * from user_info where number = '1244' and username = 'Mercury';

上面两条语句‘ken_len’相同

image-20230531203957562

2.2 索引列使用数学运算

测试4：

explain select * from user_info where id + 1 = 2;

查询类型为全表扫面，并未使用索引

2.3 隐式类型转换

测试5：

explain select * from user_info where number = 1244;

测试5

number字段为varchar类型，而查询条件为int，类型不匹配导致索引失效。

2.4模糊查询以%开头

测试6：

explain select * from user_info where number like '%2';

测试6

2.5 使用or

测试7：

explain select * from user_info where id = 1 or age = 23;

测试7

age列无索引，导致前面id列索引失效。使用or时切记两边查询条件都要有索引。

2.6索引列使用函数

测试8：

explain select * from user_info where SUBSTR(number, 2,3) = ‘12’;

测试8

2.7两列作比较或者运算

测试9：

explain select * from user_info where id < age;

explain select * from user_info where id + age = 25;

测试9

2.8其他

​ 使用不等于<>，not in， not exists， is not null 以及MySQL优化器认为走全表扫描效率更高的查询。好累啊不想做测试了，开摆。