共计 2604 个字符，预计需要花费 7 分钟才能阅读完成。
群里一个小伙伴在问为什么 MySQL 字符串不加单引号会导致索引生效，这个问题预计很多人都晓得答案。没错，是因为 MySQL 外部进行了隐式转换。
本期文章就聊聊什么是隐式转换，为什么会产生隐式转换。
系列文章
字符串能够这样加索引，你知吗？《死磕 MySQL 系列 七》
无奈复现的“慢”SQL《死磕 MySQL 系列 八》
什么？还在用 delete 删除数据《死磕 MySQL 系列 九》
MySQL 统计总数就用 count(*)，别花里胡哨的《死磕 MySQL 系列 十》
文章总目录
一、几大索引生效起因
你必定在网上看到过十分多对于索引生效起因的文章，然而肯定要本人亲手尝试一下，因为版本不同引发的后果不会统一。
1. 带头大哥不能死
这局经典语句是说创立索引要合乎最左侧准则。
例如表构造为 u_id，u_name，u_age，u_sex，u_phone，u_time
创立索引为 idx_user_name_age_sex 。
查问条件必须带上 u_name 这一列。
2. 不在索引列上做任何操作
不在索引列上做任何计算、函数、主动或者手动的类型转换，否则会进行全表扫描。简而言之不要在索引列上做任何操作。
3. 俩边类型不等
例如建设了索引 idx_user_name，name 字段类型为 varchar
在查问时应用 where name = kaka，这样的查问形式会间接造成索引生效。
正确的用法为 where name = “kaka”。
4. 不适当的 like 查问会导致索引生效
创立索引为 idx_user_name
执行语句为 select * from user where name like “kaka%”; 能够命中索引。
执行语句为 select name from user where name like “%kaka”; 能够应用到索引（仅在 8.0 以上版本）。
执行语句为 select * from user where name like ”%kaka”; 会间接导致索引生效
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 + 树的有序性。
而应用了函数对索引列进行操作后就会毁坏索引的有序性，因而优化器大哥会抉择执行代价最低的索引来继续执行。
四、论断
本期文章给大家介绍了两个案例，一个隐式转换，一个对索引列进行函数操作。
两种状况的实质是一样的，都是在索引列上进行了函数操作，导致全表扫描。
相似于这两种状况的还是字符集问题，不过个别这个问题会会很少产生，如有新业务须要新创建表，都会设置为之前的字符集。
两张表的字符集不同在进行 join 时也会导致隐式字符集转换，导致索引生效。
保持学习、保持写作、保持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮忙，我是咔咔，下期见。
