MySQL索引失效原因及SQL查询语句不走索引原因是什么

网友投稿 1222 2023-11-08

MySQL索引失效原因及SQL查询语句不走索引原因是什么

这篇“MySQL索引失效原因及SQL查询语句不走索引原因是什么”文章的知识点大部分人都不太理解,所以小编给大家总结了以下内容,内容详细,步骤清晰,具有一定的借鉴价值,希望大家阅读完这篇文章能有所收获,下面我们一起来看看这篇“MySQL索引失效原因及SQL查询语句不走索引原因是什么”文章吧。

MySQL索引失效原因及SQL查询语句不走索引原因是什么

1. 隐式的类型转换,索引失效

select * from test where num=13911111111# 失效,num字段是varchar类型,没有加引号

假设某手机号列创建时是num varchar(15)

如果上面的手机号没有加引号,查询的时候是字符串跟数字的比较,它们类型不匹配,MySQL 会做隐式的类型转换,把它们转换为浮点数再做比较。隐式的类型转换,索引会失效。

2. 查询条件包含 or,可能导致索引失效

select * from test wheremul=1 or noidx=2# 可能失效,当mul设为索引列而noidx不是索引列时

索引+or+无索引的列:会先走索引列,但无索引的列会进行全表扫描,所以还不如不走索引,直接都全表扫描完事。如果or前后都有索引,那么可能走索引,也可能不走索引。

如果它一开始就走全表扫描,直接一遍扫描就完事。Mysql 优化器出于效率与成本考虑,遇到 or 条件,让索引失效,看起来也合情合理。

用or连接的两个含null索引字段,不走索引。但是,单个索引含null字段,是走索引的。

注意:如果 or 条件的列都加了索引,索引可能会走也可能不走,平时大家使用的时候,还是要注意一下这个 or,学会用 explain 分析。遇到不走索引的时候,考虑拆开两条 SQL。

3. like 通配符可能导致索引失效

并不是用了 like 通配符,索引一定会失效,而是 like 查询是以 % 开头,才会导致索引失效。

4. 查询条件不满足联合索引的最左匹配原则

MySQl 建立联合索引时,会遵循最左前缀匹配的原则,即最左优先。如果你建立一个(a,b,c)的联合索引,相当于建立了 (a)、(a,b)、(a,b,c) 三个索引。

5. 在索引列login_time上使用 mysql 的内置函数

select * from user where DATE_ADD(login_time,INTERVAL 1 DAY) = 2022-11-08 00:00:00# 失效 select * from user where login_time = DATE_ADD(2022-11-08 00:00:00,INTERVAL 1 DAY); # 有效

6. 对索引列age进行列运算(如,+、-、*、/), 索引不生效

select * from user where age-1 = 39# 失效

7. 索引字段age上使用(!= 或者 < >, not in),索引可能失效

select * from user where age != 18# 有可能失效

其实这个也是跟 mySQL优化器有关,如果优化器觉得即使走了索引,还是需要扫描很多很多行的哈,它觉得不划算,不如直接不走索引。平时我们用!= 或者 < >,not in 的时候,要留点心眼。

8. 索引字段上使用 is null, is not null,索引可能失效 (查询结果行数)

很多时候,是因为数据量问题,导致了 MySQL 优化器放弃走索引。同时,平时我们用 explain 分析 SQL 的时候,如果 type=range, 要注意一下哈,因为这个可能因为数据量问题,导致索引无效。

9. 左右join连接,关联的字段编码格式不一样

如user 表的 name 字段编码是 utf8mb4,而 user_job 表的 name 字段编码为 utf8。

10. 索引自身失效

虽然索引有自我维护的能力,但数据表内容修改和更新频繁的情况下,也有可能索引失效,此时需要删除索引,重新建立索引。

总结

关于索引失效原因有很多,以上也只是简单介绍了一下,具体失效原因,还得去自己分析,具体方法就是SQL的执行计划 EXPLAIN 关键字了。

Mysql提供了这个关键字让我们优化索引,使查询更快,分析优化器的表连接,使它采用最优的顺序。使用这个 explain 关键字可以查看查询语句是否走索引了以及走了哪个索引。# 命令行执行以下语句即可查看查询语句是否走了索引,在查询语句最前面加上 explain 即可 mysql> explain select * from sampleInfo where agents = "XXX中心有限公司";

如下图的 key 即表示该语句使用了索引 agents 。如果下图 key 那里的为NULL或者 type 那里为ALL,则表示该语句没有走索引,需要进行优化了。

以上就是关于“MySQL索引失效原因及SQL查询语句不走索引原因是什么”这篇文章的内容,相信大家都有了一定的了解,希望小编分享的内容对大家有帮助,若想了解更多相关的知识内容,请关注行业资讯频道。

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇: 分布式存储产品: 解锁未来数据存储新纪元
下一篇:ACID事务处理限制:如何克服数据库交易困境?
相关文章