一、准备工作
1、新建一张表,并建立两个多个索引
脚本如下
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`school` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`grade` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `age` (`age`) USING BTREE,
KEY `school` (`school`) USING BTREE,
KEY `name_grade` (`name`,`grade`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
其实中 age、school、name_grade为索引
加两个数据
INSERT INTO `t_user` VALUES ('1', '张三', '清华', '22', '一年级');
INSERT INTO `t_user` VALUES ('2', '李四', '北大', '24', '二年级');
二、具体情况
1. 用 != 或者 <> 作为索引字段判断条件时 导致索引失效
如:
改为 = 判断,索引立即生效
2、索引字段类型在使用时与其真正字段类型不一致时,索引失效
本例子中的mobile 字段类型varchar类型,如果查询语句这么写,
发现结果也是正确的
分析语句发现
此语句却没有真正走索引
如果电话加上引号,再分析下语句
结果就走索引了
这证明 如果数据库类型是varchar , 索引字段类型传入参数是long类型 结果是能查询出正确数据,只是索引失效了,如果反之索引也会失效吗,我们测试下看看结果
以number字段为例,它在数据存的是long为类型,传入参数类型为 varchar类型,分析语句如下
结果显示走索引了,据此我们可以得出结论
如果数据库类型是varchar , 索引字段类型传入参数是long类型 结果是能查询出正确数据,只是索引失效了, 反之,
如果数据库字段类型是long类型,索引字段传入参数是varchar类型,结果能查正确数据,索引没有失效
数据库字段类型那么多,是不是其它类型也存在这个问题呢,我们可以一一测试下,具体细节不在展示,以下汇总结果
索引字段类型 | 查询传参类型 | 是否走索引 |
varchar | long | 否 |
varchar | int | 否 |
varchar | tinyint | 否 |
varchar | double | 否 |
long | varchar | 是 |
int | varchar | 是 |
tinyint | varchar | 是 |
double | varchar | 是 |
decimal | varchar | 是 |
在mysql 数据库中,从上面的统计结论可以看出,对于数字类型(具体类型如上表)的索引字段,传入参数为varchar类型,索引依然有效,反之则无效
3、计算、函数导致索引失效
例如
4 、is null可以使用索引,is not null无法使用索引
结果如下
5、like以通配符%开头索引失效
结尾会生效
6.OR 前后只要存在非索引的列,都会导致索引失效
school 是地索引列,sex 不是索引列,结果如下
7、数据库和表的字符集统一使用utf8mb4
统一使用utf8mb4( 5.5.3 版本以上支持 ) 兼容性更好,统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。