MySQL索引失效原因以及SQL查询语句不走索引原因详解

目录
  • 前言
  • 1. 隐式的类型转换,索引失效
  • 2. 查询条件包含 or,可能导致索引失效
  • 3. like 通配符可能导致索引失效
  • 4. 查询条件不满足联合索引的最左匹配原则
  • 5. 在索引列login_time上使用 mysql 的内置函数
  • 6. 对索引列age进行列运算(如,+、-、*、/), 索引不生效
  • 7. 索引字段age上使用(!= 或者 < >, not in),索引可能失效
  • 8. 索引字段上使用 is null, is not null,索引可能失效 (查询结果行数)
  • 9. 左右join连接,关联的字段编码格式不一样
  • 10. 索引自身失效
  • 总结

前言

日常工作中索引失效原因很多,这个需要平时的日积月累,不断学习,才能更正确的发挥索引的作用,下面简单总结一些索引失效原因。

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

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

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

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

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

select * from test where mul=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查询语句不走索引原因的文章就介绍到这了,更多相关MySQL索引失效原因内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 解决mysql模糊查询索引失效问题的几种方法

    我们在使用like %通配符时常常会引起索引失效的问题. 这里,我们讨论一下like使用%的几种情况: 下列例子用到的索引(VC_STUDENT_NAME) 一.like 'xx%' EXPLAIN select * from t_student where VC_STUDENT_NAME like '王%' 我们发现使用%不放在开头的时候,索引是有效的 二.like '%xx' EXPLAIN select * from t_student where VC_STUDENT_NAME like

  • MySQL索引失效的几种情况汇总

    一.索引不存储null值 更准确的说,单列索引不存储null值,复合索引不存储全为null的值.索引不能存储Null,所以对这列采用is null条件时,因为索引上根本 没Null值,不能利用到索引,只能全表扫描. 为什么索引列不能存Null值? 将索引列值进行建树,其中必然涉及到诸多的比较操作.Null值的特殊性就在于参与的运算大多取值为null. 这样的话,null值实际上是不能参与进建索引的过程.也就是说,null值不会像其他取值一样出现在索引树的叶子节点上. 二.不适合键值较少的列(重复

  • Mysql索引会失效的几种情况分析

    索引并不是时时都会生效的,比如以下几种情况,将导致索引失效: 1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引 2.对于多列索引,不是使用的第一部分,则不会使用索引 3.like查询是以%开头 4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引 5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引 此外,查看索引的使用情况show status li

  • mysql 联合索引生效的条件及索引失效的条件

    目录 1.联合索引失效的条件 2.索引失效的条件 1.联合索引失效的条件 联合索引又叫复合索引.两个或更多个列上的索引被称作复合索引. 对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分.例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效. 利用索引中的附加列,您可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单

  • MySQL中有哪些情况下数据库索引会失效详析

    前言 要想分析MySQL查询语句中的相关信息,如是全表查询还是部分查询,就要用到explain. 索引的优点 大大减少了服务器需要扫描的数据量 可以帮助服务器避免排序或减少使用临时表排序 索引可以随机I/O变为顺序I/O 索引的缺点 需要占用磁盘空间,因此冗余低效的索引将占用大量的磁盘空间 降低DML性能,对于数据的任意增删改都需要调整对应的索引,甚至出现索引分裂 索引会产生相应的碎片,产生维护开销 一.explain 用法:explain +查询语句. id:查询语句的序列号,上面图片中只有一

  • mysql索引失效的五种情况分析

    索引并不是时时都会生效的,比如以下几种情况,将导致索引失效: 如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引 2.对于多列索引,不是使用的第一部分,则不会使用索引 3.like查询是以%开头 4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引 5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引 此外,查看索引的使用情况 show status lik

  • MySQL索引失效原理

    目录 1.索引失效原因 2.再来看看哪些情况会破坏索引的有序性. - 对索引字段做函数操作 - 隐式类型转换 - 隐式字符编码转换 3.总结 1.索引失效原因 首先看看哪些情况下,将会导致查找不能利用索引的有序性. 假设一个表test中有a,b,c,d四个字段,c是主键. 在a,b字段上建立联合索引(a,b):CREATE index idx_a_b on test(a,b); B+树联合索引.JPG 可以得到的规律是:优先按a字段从小到大排序,a字段相等的按b字段从小到大排序: 分析以下情况,

  • MySQL中索引失效的常见场景与规避方法

    前言 之前有看过许多类似的文章内容,提到过一些sql语句的使用不当会导致MySQL的索引失效.还有一些MySQL"军规"或者规范写明了某些sql不能这么写,否则索引失效. 绝大部分的内容笔者是认可的,不过部分举例中笔者认为用词太绝对了,并没有说明其中的原由,很多人不知道为什么.所以笔者绝对再整理一遍MySQL中索引失效的常见场景,并分析其中的原由供大家参考. 当然请记住,explain是一个好习惯! MySQL索引失效的常见场景 在验证下面的场景时,请准备足够多的数据量,因为数据量少时

  • MySQL导致索引失效的几种情况

    目录 一.准备工作 二.索引失效规则 1.优先使用联合索引 2.最左匹配原则 3.范围条件右边的列索引失效 4.计算.函数导致索引失效 5.类型转换导致索引失效 6.不等于(!= 或者<>)索引失效 7.is null可以使用索引,is not null无法使用索引 8.like以%开头,索引失效 9.OR前后存在非索引的列,索引失效 10.字符集不统一 三.建议 一.准备工作 首先准备两张表用于演示: CREATE TABLE `student_info` ( `id` int NOT NU

  • mysql的in会不会让索引失效?

    mysql的in会让索引失效吗?不会! 看结果: mysql> desc select * from tb_province where name in ('lily3', 'lily2', 'lily1'); +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_t

随机推荐