其它MySQL 面试系列：
MySQL 面试系列：如何选择普通索引和唯一索引?
MySQL 面试系列：一条 select 语句在 MySQL 是这样执行的?
MySQL 面试系列：MySQL查询如何进行优化?
MySQL 面试系列：MySQL 常见的开放性问题
MySQL 面试系列：MySQL 性能优化 & 分布式
MySQL 面试系列：MySQL 命令和内置函数
MySQL 面试系列：MySQL 中日志的面试题总结
MySQL 面试系列：MySQL 中锁的面试题总结
MySQL 面试系列：MySQL 事务的面试题总结
MySQL 面试系列：MySQL 索引的面试题总结
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';
image
从上图可看出，执行第一条 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，则把数字转为字符串。
网友评论