更多相关内容可查看
索引失效情况
范围查询
联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效。
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字段的单列索引。
- 当根据phone字段进行等值匹配查询时, 索引生效。
explain select * from tb_user where phone = '17799990015';
- 当根据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,二叉树)
数据库-索引语法(增删查)
数据库-索引分类(主键索引、唯一索引、普通索引、全文索引)
数据库-索引使用(验证索引效率、单列索引与联合索引、最左前缀法则)
索引失效情况