《死磕MySQL系列》为什么MySQL字符串不加引号索引失效？

5.范围条件之后的索引会失效

创建索引为idx_user_name_age_sex

执行语句select * from user where name = ‘kaka’ and age > 11 and sex = 1;

上面这条sql语句只会命中name和age索引，sex索引会失效。

复合索引失效需要查看key_len的长度即可。

总结：%在后边会命令索引，当使用了覆盖索引时任何查询方式都可命中索引。

以上就是咔咔关于索引失效会出现的原因总结，在很多文章中没有标注MySQL版本，所以你有可能会看到is null 、or索引会失效的结论。

二、从规则方面说明索引失效的原因

================

问题的答案就是第3点，两边类型不一致导致索引失效。

下图是表结构，目前这个表存在两个索引，一个主键索引，一个普通索引phone。

分别执行以下两条SQL语句

explain select * from evt_sms where phone = 13020733815;

explain select * from evt_sms where phone = ‘13020733815’;

从上图可看出，执行第一条SQL没有使用到索引，第二条SQL却使用到了索引。

不错，你也发现了两条SQL的不同，第二条SQL跟第一条SQL逻辑一致，不同的是一个查询条件有引号，一个没有。

问题：为什么逻辑相同的SQL却是用不了索引

选择索引是优化器大哥的工作，大哥做事肯定轮不到咱们去教，因为大哥有自己的一套规则。

对于优化器来说，如果等号两边的数据类型不一致，则会发生隐式转换。

例如，explain select * from evt_sms where phone = 13020733815;这条SQL语句就会变为explain select * from evt_sms where cast(phone as signed int) = 13020733815;

由于对索引列进行了函数操作，从而导致索引失效。

问题：为什么会把左侧的列转为int类型呢？

优化器大哥就是根据这个规则进行判断，是把字符串转为数字，还是把数字转为字符串。

若返回1，则把字符串转为数字。

若返回0，则把数字转为字符串。

问题：select * from evt_sms where id = "193014410456945216"这条SQL语句能用上索引吗？

如果你忘记了表结构，可以翻到文章开头再看下表evt_sms的索引。

可以知道列id添加了主键索引，类型为int类型。

根据规则得到，MySQL8.0以上的版本是将字符串转为数字。

所以说，函数操作的是等号右边的数据，跟索引列没有关系，所以可以用上索引。

那么来到数据库验证一下结论，你答对了吗？

三、从索引结构说明索引失效原因

===============

有这样一个需求，要统计每年双11注册用户数量。

可以看到在evt_sms表中是没有给create_time创建索引的，于是你会执行alter table evt_sms add index idx_ctime(create_time)，给create_time添加上索引。

接着你就执行了下面的SQL语句。

explain select count(*) from evt_sms where month(create_time) = 11;

上线没一会数据库出现了大量的慢查询，导致非常多的SQL返回失败。

此时公司大牛肯定会直接指出问题，索引列进行函数操作。

问题：为什么索引列使用函数就用不上索引了呢？

你现在看到的create_time索引结构图。

若此时执行的是where create_time = ‘2021-11-16’，那么MySQL就会非常快地等位到对应位置，并返回结果。

但是，做了函数操作，例如month(2021-11-16)得到的值是11。

当MySQL拿到返回的这个11时，在索引结构中根据就不知道怎么办。MySQL之所以能使用快速定位，是因为B+树的有序性。

而使用了函数对索引列进行操作后就会破坏索引的有序性，因此优化器大哥会选择执行代价最低的索引来继续执行。

四、结论

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

结语

小编也是很有感触，如果一直都是在中小公司，没有接触过大型的互联网架构设计的话，只靠自己看书去提升可能一辈子都很难达到高级架构师的技术和认知高度。向厉害的人去学习是最有效减少时间摸索、精力浪费的方式。

我们选择的这个行业就一直要持续的学习，又很吃青春饭。

虽然大家可能经常见到说程序员年薪几十万，但这样的人毕竟不是大部份，要么是有名校光环，要么是在阿里华为这样的大企业。年龄一大，更有可能被裁。

送给每一位想学习Java小伙伴，用来提升自己。

本文到这里就结束了，喜欢的朋友可以帮忙点赞和评论一下，感谢支持！
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
的大企业。年龄一大，更有可能被裁。

送给每一位想学习Java小伙伴，用来提升自己。

[外链图片转存中…(img-MObyWfPI-1713516538000)]

本文到这里就结束了，喜欢的朋友可以帮忙点赞和评论一下，感谢支持！
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！