执行语句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获取)
2021年Java中高级面试必备知识点总结
在这个部分总结了2019年到目前为止Java常见面试问题,取其面试核心编写成这份文档笔记,从中分析面试官的心理,摸清面试官的“套路”,可以说搞定90%以上的Java中高级面试没一点难度。
本节总结的内容涵盖了:消息队列、Redis缓存、分库分表、读写分离、设计高并发系统、分布式系统、高可用系统、SpringCloud微服务架构等一系列互联网主流高级技术的知识点。
目录:
(上述只是一个整体目录大纲,每个点里面都有如下所示的详细内容,从面试问题——分析面试官心理——剖析面试题——完美解答的一个过程)
部分内容:
对于每一个做技术的来说,学习是不能停止的,小编把2019年到目前为止Java的核心知识提炼出来了,无论你现在是处于什么阶段,如你所见,这份文档的内容无论是对于你找面试工作还是提升技术广度深度都是完美的。
不想被后浪淘汰的话,赶紧搞起来吧,高清完整版一共是888页,需要的话可以点赞+关注
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!
,学习是不能停止的,小编把2019年到目前为止Java的核心知识提炼出来了,无论你现在是处于什么阶段,如你所见,这份文档的内容无论是对于你找面试工作还是提升技术广度深度都是完美的。
不想被后浪淘汰的话,赶紧搞起来吧,高清完整版一共是888页,需要的话可以点赞+关注
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!