mysql 索引欣失效的情况

一、准备工作

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类型，结果能查正确数据，索引没有失效

数据库字段类型那么多，是不是其它类型也存在这个问题呢，我们可以一一测试下，具体细节不在展示，以下汇总结果

在mysql 数据库中，从上面的统计结论可以看出，对于数字类型（具体类型如上表）的索引字段，传入参数为varchar类型，索引依然有效，反之则无效

3、计算、函数导致索引失效

       例如

       

4 、is null可以使用索引，is not null无法使用索引 

    结果如下

   

 

5、like以通配符%开头索引失效 

  

   结尾会生效

 6.OR 前后只要存在非索引的列，都会导致索引失效

      school 是地索引列，sex 不是索引列，结果如下

 7、数据库和表的字符集统一使用utf8mb4

   统一使用utf8mb4( 5.5.3 版本以上支持 ) 兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。