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

2021年11月26日 阅读数:3
这篇文章主要向大家介绍为何MySQL字符串不加引号索引失效?《死磕MySQL系列 十一》,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

群里一个小伙伴在问为何MySQL字符串不加单引号会致使索引失效,这个问题估计不少人都知道答案。没错,是由于MySQL内部进行了隐式转换。sql

本期文章就聊聊什么是隐式转换,为何会发生隐式转换。数据库

系列文章

字符串能够这样加索引,你知吗?《死磕MySQL系列 七》app

没法复现的“慢”SQL《死磕MySQL系列 八》函数

什么?还在用delete删除数据《死磕MySQL系列 九》学习

MySQL统计总数就用count(*),别花里胡哨的《死磕MySQL系列 十》优化

文章总目录url

1、几大索引失效缘由

你确定在网上看到过很是多关于索引失效缘由的文章,可是必定要本身亲手尝试一下,由于版本不一样引起的结果不会一致。spa

1.带头大哥不能死.net

这局经典语句是说建立索引要符合最左侧原则。3d

例如表结构为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索引会失效的结论。

2、从规则方面说明索引失效的缘由

问题的答案就是第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以上的版本是将字符串转为数字。

因此说,函数操做的是等号右边的数据,跟索引列没有关系,因此能够用上索引。

那么来到数据库验证一下结论,你答对了吗?

3、从索引结构说明索引失效缘由

有这样一个需求,要统计每一年双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+树的有序性。

而使用了函数对索引列进行操做后就会破坏索引的有序性,所以优化器大哥会选择执行代价最低的索引来继续执行。

4、结论

本期文章给你们介绍了两个案例,一个隐式转换,一个对索引列进行函数操做。

两种状况的本质是同样的,都是在索引列上进行了函数操做,致使全表扫描。

相似于这两种状况的仍是字符集问题,不过通常这个问题会会不多发生,若有新业务须要新建立表,都会设置为以前的字符集。

两张表的字符集不一样在进行join时也会致使隐式字符集转换,致使索引失效。

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