数据库-索引使用(索引失效情况)

在这里插入图片描述


更多相关内容可查看

索引失效情况

范围查询

联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效。

explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';


当范围查询使用> 或 < 时,走联合索引了,但是索引的长度为49,就说明范围查询右边的status字段是没有走索引的。

explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';


当范围查询使用>= 或 <= 时,走联合索引了,但是索引的长度为54,就说明所有的字段都是走索引的。

所以,在业务允许的情况下,尽可能的使用类似于 >= 或 <= 这类的范围查询,而避免使用 > 或 < 。

索引列运算

不要在索引列上进行运算操作, 索引将失效。

在tb_user表中,除了前面介绍的联合索引之外,还有一个索引,是phone字段的单列索引。

  1. 当根据phone字段进行等值匹配查询时, 索引生效。
explain select * from tb_user where phone = '17799990015';

  1. 当根据phone字段进行函数运算操作之后,索引失效。
explain  select  *  from  tb_user  where  substring(phone,10,2) = '15';

值的类型和索引列类型不同

字符串类型字段使用时,不加引号,索引将失效。

接下来,我们通过两组示例,来看看对于字符串类型的字段,加单引号与不加单引号的区别:

explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select * from tb_user where profession = '软件工程' and age = 31 and status = 0;

explain select * from tb_user where phone = '17799990015';
explain select * from tb_user where phone = 17799990015;

经过上面两组示例,我们会明显的发现,如果字符串不加单引号,对于查询结果,没什么影响,但是数据库存在隐式类型转换,索引将失效。

模糊查询

如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。

接下来,我们来看一下这三条SQL语句的执行效果,查看一下其执行计划:
由于下面查询语句中,都是根据profession字段查询,符合最左前缀法则,联合索引是可以生效的,我们主要看一下,模糊查询时,%加在关键字之前,和加在关键字之后的影响。

explain  select  *  from  tb_user  where  profession like '软件%';
explain  select  *  from  tb_user  where  profession like '%工程';
explain  select  *  from  tb_user  where  profession like '%工%';

经过上述的测试,我们发现,在like模糊查询中,在关键字后面加%,索引可以生效。而如果在关键字前面加了%,索引将会失效。

or连接条件

用or分割开的条件, 如果or关键字左右的条件, 其中有任何一个条件没有使用索引, 索引都会失效

explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;


由于age没有索引,所以即使id、phone有索引,索引也会失效。所以需要针对于age也要建立索引。
然后,我们可以对age字段建立索引。

 create index idx_user_age on tb_user(age);

建立了索引之后,我们再次执行上述的SQL语句,看看前后执行计划的变化。

最终,我们发现,当or连接的条件,左右两侧字段都有索引时,索引才会生效。

数据分布影响

如果MySQL评估使用索引比全表更慢,则不使用索引。

select * from tb_user where phone >= '17799990005';
select * from tb_user where phone >= '17799990015';


经过测试我们发现,相同的SQL语句,只是传入的字段值不同,最终的执行计划也完全不一样,这是为什么呢?

就是因为MySQL在查询时,会评估使用索引的效率与走全表扫描的效率,如果走全表扫描更快,则放弃索引,走全表扫描。 因为索引是用来索引少量数据的,如果通过索引查询返回大批量的数据,则还不如走全表扫描来的快,此时索引就会失效。

接下来,我们再来看看 is null 与 is not null 操作是否走索引。
执行如下两条语句 :

explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;


接下来,我们做一个操作将profession字段值全部更新为null。
然后,再次执行上述的两条SQL,查看SQL语句的执行计划。
最终我们看到,一模一样的SQL语句,先后执行了两次,结果查询计划是不一样的,为什么会出现这种现象,这是和数据库的数据分布有关系。

查询时MySQL会评估,走索引快,还是全表扫描快,如果全表扫描更快,则放弃索引走全表扫描。 因此,is null 、is not null是否走索引,得具体情况具体分析,并不是固定的。

索引失效解决方案?

  • 重新建立索引:如果索引失效是由于数据量增加导致的,可以尝试重新建立索引,以提高检索效率。
  • 优化查询语句:检查查询语句是否存在不必要的操作或者索引未被使用的情况,可以通过优化查询语句来提高索引的效率。
  • 添加新索引:根据实际情况,可以考虑添加新的索引来优化查询效率。
  • 修改表结构:考虑修改表结构来优化索引的使用,比如添加联合索引等。
  • 使用缓存:可以考虑使用缓存来减少数据库查询次数,从而减少索引失效的可能性。
  • 定期维护:定期对数据库进行维护,包括索引重建、统计信息更新等,以保持索引的有效性。

本篇小结

有小伙伴对其他数据库内容感兴趣,可以通过以下链接进行查看
数据库-索引(基础篇)
数据库-索引结构(B-Tree,B+Tree,Hash,二叉树)
数据库-索引语法(增删查)
数据库-索引分类(主键索引、唯一索引、普通索引、全文索引)
数据库-索引使用(验证索引效率、单列索引与联合索引、最左前缀法则)
索引失效情况

  • 35
    点赞
  • 12
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

来一杯龙舌兰

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值