但使书种多,会有岁稔时。这篇文章主要讲述为什么MySQL字符串不加引号索引失效?《死磕MySQL系列 十一》相关的知识,希望能为你提供帮助。
群里一个小伙伴在问为什么mysql字符串不加单引号会导致索引失效,这个问题估计很多人都知道答案。没错,是因为MySQL内部进行了隐式转换。
本期文章就聊聊什么是隐式转换,为什么会发生隐式转换。
文章图片
从上图可看出,执行第一条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,则把字符串转为数字。
【为什么MySQL字符串不加引号索引失效(《死磕MySQL系列 十一》)】
若返回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+树的有序性。
而使用了函数对索引列进行操作后就会破坏索引的有序性,因此优化器大哥会选择执行代价最低的索引来继续执行。
四、结论本期文章给大家介绍了两个案例,一个隐式转换,一个对索引列进行函数操作。
两种情况的本质是一样的,都是在索引列上进行了函数操作,导致全表扫描。
类似于这两种情况的还是字符集问题,不过一般这个问题会会很少发生,如有新业务需要新创建表,都会设置为之前的字符集。
两张表的字符集不同在进行join时也会导致隐式字符集转换,导致索引失效。
“坚持学习、坚持写作、坚持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮助,我是咔咔,下期见。
”
推荐阅读
- 面试官(Spring是如何把Bean注册到IOC容器中的())
- vscode开发插件推荐 #yyds干货盘点#
- #yyds干货盘点# 常用的Logic Controllers组件简介
- SpringCloud升级之路2020.0.x版-36. 验证断路器正确性
- #yyds干货盘点# .NET6新东西--隐式命名空间引用
- #yyds干货盘点# 一文弄懂二叉树的三种遍历方式
- 一文讲透自适应熔断的原理和实现
- #yyds干货盘点#怎么自学 Python,大概要多久()
- #yyds干货盘点# Windows下Python-openCV学习-------图像处理基本