浅谈mysql哪些情况会导致索引失效

索引在我们使用MySQL数据库时可以极大的提高查询效率,然而,有时候因为使用上的一些瑕疵就会导致索引的失效,无法达到我们使用索引的预期效果,本文就介绍一下几种索引失效的方法

下面有一些培训教学机构的口诀和我个人的一些总结:
为了讲解以下索引内容,我们先建立一个临时的表 test02

?

1

2

3

4

5

6

7

8

create table `sys_user` (

`id` varchar(64) not null comment '主键',

`name` varchar(64) default null comment '名字',

`age` int(64) default null comment '年龄',

`pos` varchar(64) default null comment '职位',

primary key (`id`),

key `idx_sys_user_nameagepos` (`name`,`age`,`pos`)

) engine=innodb default charset=utf8mb4 comment='用户表';

这个表有四个字段 主键 、名字、年龄、职位

下面我们来讲解第一个口诀:
1.全值匹配我最爱
2.最佳左前缀法则(重要)
全值匹配意思就是联立的复合索引的顺序和个数要和检索的条件顺序和个数相同。
最佳左前缀法则是指,如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
下面我们给这个表建立一个复合索引

?

1

alter table sys_user add index idx_sys_user_nameagepos(name,age,pos);

以下是我们的检索语句:

?

1

select * from sys_user where name='小明' and age = 22 and pos ='java';

浅谈mysql哪些情况会导致索引失效

我们通过在检索语句前面加关键字 exlain,可以知道是否使用的索引

?

1

2

3

(1)explain select * from sys_user where name='小明' and age = 22 and pos ='java';

(2)explain select * from sys_user where name='小明' and age = 22 ;

(3)explain select * from sys_user where name='小明' and pos ='java';

浅谈mysql哪些情况会导致索引失效

浅谈mysql哪些情况会导致索引失效

浅谈mysql哪些情况会导致索引失效

通过展示的结果我们可以知道,第一个复合索引的三个字段我们都用了,第二个复合索引我们只用到两个字段,第三个复合索引我们只用到一个字段。三个语句我们都用到索引,显然第一种是最优的。

我们再看看哪种情况会失效:

?

1

2

3

(4)explain select * from sys_user where age = 22;

(5)explain select * from sys_user where pos ='java';

(6)explain select * from sys_user where age = 22 and pos ='java';

浅谈mysql哪些情况会导致索引失效

浅谈mysql哪些情况会导致索引失效

浅谈mysql哪些情况会导致索引失效

以上三种情况都变成了全表扫描,原因是违反了最左左前缀原则,因为复合索引最左边的是name,当检索条件name没在前面索引将失效,第一种情况满足了全值匹配,第二种满足了两个字段name和age,第三种因为只满足了name,所以索引只用到name。

3.不在索引列上做任何操作(计算、函数(自动或手动)类型转换),会使索引失效转为全表扫描

?

1

(7)explain select * from sys_user where left(name,1)='小明';

浅谈mysql哪些情况会导致索引失效

第七种情况失效是因为索引列做了计算或者函数的操作,导致了全表扫描。

4.存储引擎不能使用索引中范围条件右边的列
可能大家关看上面的文字不知道是什么意思,下面我们执行一下查询语句就清楚了

?

1

(8)explain select * from sys_user where name='小明' and age < 22 and pos ='java';

浅谈mysql哪些情况会导致索引失效

从上图我们可以知道type变成了范围级别,也就是说age<22之后的pos字段的索引失效了。

5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致),减少select * 的使用
这个就是字面意思,查询具体的字段比查询*效率更高,下面我们坐一下对比

?

1

2

(9)explain select * from sys_user where name='小明' and age =22 and pos ='java';

(10)explain select name,age,pos from sys_user where name='小明' and age =22 and pos ='java';

浅谈mysql哪些情况会导致索引失效

浅谈mysql哪些情况会导致索引失效

6.mysql在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
(!= 或者<>)通常会匹配到大量数据,当使用索引的花销大于全表扫描时,mysql则会放弃使用索引而选择全表扫描

?

1

(11)explain select * from sys_user where name !='小明'

浅谈mysql哪些情况会导致索引失效

结果显示索引失效导致了全表扫描

7.is null,is not null 也无法使用索引
is null,is not null 通常会匹配到大量数据,当使用索引的花销大于全表扫描时,mysql则会放弃使用索引而选择全表扫描

?

1

(12)explain select * from sys_user where name is not null

浅谈mysql哪些情况会导致索引失效

8.like以通配符开头('%abc…')mysql索引会失效变成全表扫描的操作,(%写右边则可以避免索引失效,如果业务实在需要'%abc…%'则可以用覆盖索引避免索引失效)

?

1

2

3

(13)explain select * from sys_user where name like '%明%'

(14)explain select * from sys_user where name like '明%'

(15)explain select name,age,pos from sys_user where name like '%明%'

浅谈mysql哪些情况会导致索引失效

浅谈mysql哪些情况会导致索引失效

浅谈mysql哪些情况会导致索引失效

从上面的结果,第一种索引失效,第二种只写右边的%则可以避免索引失效,第三种如果业务实在需要‘%abc…%'这种sql,则可以用覆盖索引解决索引失效的问题

9.字符串不加单引号索引会失效

?

1

(16)explain select * from sys_user where name=222;

浅谈mysql哪些情况会导致索引失效

因为检索字符串是必须加单引号,上面用用了222是int类型,mysql在检索的时候会判断name是varchar的类型会将222转换为'222'进行检索,索引列发生了类型转换,故索引失效。

10.少用or,用它连接时会索引失效

?

1

(16)explain select * from sys_user where name='小明' or age = 22;

浅谈mysql哪些情况会导致索引失效

到此这篇关于浅谈mysql哪些情况会导致索引失效的文章就介绍到这了,更多相关mysql 索引失效内容请搜索钦钦技术栈以前的文章或继续浏览下面的相关文章希望大家以后多多支持钦钦技术栈!

原文链接:https://blog.csdn.net/qq_40976527/article/details/105017541

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/4203/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年 7月 21日 3:35:21
下一篇 2022年 7月 21日 3:35:42

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!