MySQL索引失效问题

索引在我们使用MySQL数据库时可以极大的提高查询效率，然而，有时候因为使用上的一些瑕疵就会导致索引的失效，无法达到我们使用索引的预期效果，今天介绍几种MySQL中几种常见的索引失效的原因，可以在以后的工作中尽可能避免因索引失效带来的坑。

一、 被索引字段，发生了隐式类型转换

MySQL在sql执行过程中，会将sql语句中与字段原类型不匹配的值，进行一个类型转换

看个例子说明，我们创建一个user表，并且添加一个主键id索引，两个二级索引age和phone

CREATE TABLE `t_user`(
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `age` int(10) NOT NULL,
  `phone` varchar(30) NOT NULL,
  PRIMARY KEY(`id`),
  KEY `idx_age` (`age`),
  KEY `ids_phone` (`phone`)
) ENGINE=INNODB

#插入一条数据
insert into t_user (`name`,`age`,`phone`) values('zhangsan','20','13300001111')

我们执行一个查询sql看一下结果

select * from t_user where phone = 13300001111;

我们可以看到虽然查询语句中的phone的值是一个数字类型的值，与phone的字符串类型不匹配，依然可以查到我们想要的结果，但是在sql执行过程中并没有使用到索引。

我们可以通过MySQL的explain关键字来分析SQL语句执行的细节。在explain的分析结果中有一条结果是key，这代表的是使用的哪个索引，我们可以看到key的值是null说明这条SQL语句在执行过程中并没有用到索引。

我们将SQL语句修改一下，将phone字段的值修改成一个字符串，再来执行一下，分析一下SQL语句执行的细节。

select * from t_user where phone = "13300001111";

我们可以看到修改后的SQL语句在执行过程中使用到了索引，这个是因为SQL语句中的数据类型与phone字段本事的类型一致，就不需要进行类型转换，是可以使用到索引的，所以代表使用索引的key是idx_phone

通过这个例子我们可以知道，在SQL语句中被索引字段与所对应值的类型不匹配时，在SQL语句执行过程中，会进行隐式类型转换，会导致这个索引变得失效。

二、被索引字段使用了表达式计算

还是使用刚刚的数据表，我们再来看一个例子，来查询年龄超过18岁又刚好满2年的人

select * from t_user where age -2 = 18;

在这个sql中age字段用到了表达式计算，执行会发现是可以正常执行的，但是这是一种错误的示范，我们用explain关键字分析这条SQL的时候，会发现这个查询并没有使用我们添加的age字段的索引。

然后我们换一种写法，让age直接等于20

select * from t_user where age = 20;

再来使用explain关键字分析SQL执行过程，会发现key值变成了idx_age

这个例子说明了，SQL查询语句中，如果被索引字段进行了表达式计算，也会引起索引的失效。

三、被索引字段使用了函数

还是使用刚刚的t_user表，我们来查询电话以133开头的用户

select * from t_user where left(phone,3) = '133';

执行SQL我们可以看到是正确的。

使用explain查看一下SQL执行情况。

可以看到key值为null并没有使用到我们添加的索引，所以以上是个错误示范，我们修改一下SQL再来看一下执行情况。

select * from t_user where phone like = '133%';

修改后的SQL中索引字段没有用到函数，key值为idx_phone正确的使用到了我们添加的索引。

当被索引字段使用到了函数，这个索引字段上的索引也会失效。

小结

以上三种索引失效的情况可以归于一类，进行一下总结，被索引字段的隐式转换、被索引字段的表达式计算、被索引字段使用函数，都会引起索引字段对应的索引发生失效，这是因为索引的使用是依赖于B-tree索引树的遍历，而索引树的遍历是依赖于索引树底层叶子节点的有序性，当被索引字段进行了隐式类型转换、表达式计算或函数计算后，有可能这个字段新的排列顺序和原来在索引树的叶子节点层的排列顺序不一样了，这就破坏了索引树叶子节点层的有序性，当SQL语句被执行时，MySQL数据库的SQL语句执行器就无法判断原来的索引树是否还能被检索使用，所以就是SQL执行器不使用该索引了，而我们看到的就是我们期望使用的索引失效了。

四、在like关键字后使用左模糊匹配’%##’

还是使用刚刚的数据表，我们新增一列address，并建立idx_address索引。

CREATE TABLE `t_user`(
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `age` int(10) NOT NULL,
  `phone` varchar(30) NOT NULL,
   `address` varchar(255) NOT NULL，
  PRIMARY KEY(`id`),
  KEY `idx_age` (`age`),
  KEY `idx_phone` (`phone`),
  KEY `idx_address` (`address`)
) ENGINE=INNODB

然后执行SQL，查询用户地址中包含“海淀区”关键字的用户

通过explain关键字查看SQL执行情况发现address添加的索引并没有使用，我们修改SQL，去掉右模糊，只使用左模糊查询，然后分析SQL执行情况，发现结果还是一样的，没有使用索引。

我们再来看看使用like右模糊匹配的上去看语句的explain结果：

我们发现key值为idx_address，右匹配魔术查询是使用到了我们为address字段添加的的索引。

说明like关键字的左右模糊匹配和左模糊匹配都会造成索引的失效，只有like关键字的右模糊匹配可以使用到索引。

五、被使用的索引字段，不是联合索引的最左字段

我们修改一下刚刚的t_user表，表中除了主键索引外，我们再添加一个联合索引idx_age_phone，在这个联合索引中包含了age字段和phone字段，并且age字段在最左边。我们来查找年龄等于20的用户，通过explain关键字分析SQL可以看到使用到了联合索引，如下图：

然后我们再来查询手机号等于13300001111的用户，通过explain关键字分析，可以看到key值为null并没有使用到我们添加的联合索引：

如果数据表中有联合索引，但SQL查询中使用的查询字段不是联合索引的最左字段时，联合索引不会被使用的。

小结

总结一下第四种情况和第五种情况，like关键字后使用了左模糊匹配或者使用了左右模糊匹配时，索引不会被SQL执行器使用，SQL查询字段不是联合索引的最左字段时，联合索引也不会被SQL执行器使用。这其中的原因是，MySQL中的索引索引树检索遵循最左匹配原则，B-tree索引树的叶子节点的有序性，也是建立在最左匹配的基础上的，如果直接使用索引键的中部或者后部进行SQL查询，由于违背了最左匹配原则，MySQL的SQL执行器无法利用这个索引树进行检索，所以给我们的直观感受就是索引失效了。

补充：

前面的这五种导致索引失败的情况，然后查询的过程中发生了索引覆盖，也就是不需要回表时，索引树还是会被使用的，例如执行一下SQL查询地址中包含海淀区的用户的id和地址字段，通过explain关键字分析SQL语句。

select id,address from t_user where address like '%海淀区%';

我们发现key值为idx_address，虽然遇到了like左右模糊匹配的情况，但是idx_address索引依然被使用了。这里虽然使用了索引，但是使用索引树的时间复杂度不是O(logN)了，而是O(N)。

更多Java相关文章资料，可以关注公众号故里学Java，回复资源包获取