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

群里一个小伙伴在问为什么 MySQL 字符串不加单引号会导致索引失效，这个问题估计很多人都知道答案。没错，是因为 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 时也会导致隐式字符集转换，导致索引失效。

坚持学习、坚持写作、坚持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮助，我是咔咔，下期见。