mysql索引失效的几种情况分析

1、最佳左前缀原则——如果索引了多列，要遵守最左前缀原则。指的是查询要从索引的最左前列开始并且不跳过索引中的列。

前提条件：表中已添加复合索引（username,password,age）

分析：该查询缺少username，查询条件复合索引最左侧username缺少，违反了最佳左前缀原则，导致索引失效，变为ALL，全表扫描

分析：查询条件缺少username，password，查询条件复合索引最左侧username，password缺少，违反了最佳左前缀原则，导致索引失效，变为ALL，全表扫描

分析：该查询只有一个username条件，根据最佳左前缀原则索引能够被使用到，但是是部分使用

2、不在索引列上做任何操作（计算，函数，（自动或者手动）类型装换），会导致索引失效而导致全表扫描

分析：第一个图索引列不使用函数，遵循左前缀原则，能够使用索引。第二张图索引列上使用了函数，即使遵循左前缀原则，索引还是失效

3、存储引擎不能使用索引中范围条件右边的列，范围之后索引失效。（< ,> between and）

分析：图一索引全部使用到。图二索引使用到username和age，但是username是使用索引检索，而age着重索引排序，这时age为范围查找，password索引将失效

4、mysql使用不等于(!= 或者<>)的时候，无法使用索引，会导致索引失效

5、mysql中使用is not null 或者 is null会导致无法使用索引

分析：对username列做了普通索引，查询带is not null，结果索引不生效

6、mysql中like查询是以%开头，索引会失效变成全表扫描，覆盖索引。

分析：对username列做了普通索引，以%开头进行查询，结果索引失效被覆盖

7、mysql中，字符串不加单引号索引会失效。正确写法：select * from t_user where username = 'lujin';

8、mysql中，如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

9、如果mysql使用全表扫描要比使用索引快,则不会使用到索引

总结

到此这篇关于mysql索引失效情况分析的文章就介绍到这了,更多相关mysql索引失效内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

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

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

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

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

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

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

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

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

  • Mysql 5.6 "隐式转换"导致的索引失效和数据不准确的问题

    Mysql 5.6 "隐式转换"导致的索引失效和数据不准确的问题

    背景 在一次进行SQl查询时,我试着对where条件中vachar类型的字段去掉单引号查询,这个时候发现这条本应该很快的语句竟然很慢.这个varchar字段有一个复合索引.其中的总条数有58989,甚至不加单引号查出来的数据不是我们想要的数据. 使用的是mysql 5.6版本,innoDB引擎 实际情况如下 下面我们来看一下执行的结果 在上面的描述中我们还得注意就是,你的where条件的字符串不加单引号必须是全数字.不然就会报错 还有可能查出来的数据不是我们想要的数据.如下图 分析 从执行结果来

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

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

  • MySQL索引失效的几种情况详析

    MySQL索引失效的几种情况详析

    1.前导模糊查询不能利用索引(like '%XX'或者like '%XX%') 假如有这样一列code的值为'AAA','AAB','BAA','BAB' ,如果where code like '%AB'条件,由于前面是 模糊的,所以不能利用索引的顺序,必须一个个去找,看是否满足条件.这样会导致全索引扫描或者全表扫 描.如果是这样的条件where code like 'A % ',就可以查找CODE中A开头的CODE的位置,当碰到B开头的 数据时,就可以停止查找了,因为后面的数据一定不满足要求.

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

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

  • 导致MySQL索引失效的一些常见写法总结

    前言 最近一直忙着处理原来老项目遗留的一些SQL优化问题,由于当初表的设计以及字段设计的问题,随着业务的增长,出现了大量的慢SQL,导致MySQL的CPU资源飙升,基于此,给大家简单分享下这些比较使用的易于学习和使用的经验. 这次的话简单说下如何防止你的索引失效. 再说之前我先根据我最近的经验说下我对索引的看法,我觉得并不是所以的表都需要去建立索引,对于一些业务数据,可能量比较大了,查询数据已经有了一点压力,那么最简单.快速的办法就是建立合适的索引,但是有些业务可能表里就没多少数据,或者表的使用

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

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

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

    mysql索引失效的几种情况分析

    1.最佳左前缀原则--如果索引了多列,要遵守最左前缀原则.指的是查询要从索引的最左前列开始并且不跳过索引中的列. 前提条件:表中已添加复合索引(username,password,age) 分析:该查询缺少username,查询条件复合索引最左侧username缺少,违反了最佳左前缀原则,导致索引失效,变为ALL,全表扫描 分析:查询条件缺少username,password,查询条件复合索引最左侧username,password缺少,违反了最佳左前缀原则,导致索引失效,变为ALL,全表扫描

  • 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索引优化之不适合构建索引及索引失效的几种情况详解

    MySQL索引优化之不适合构建索引及索引失效的几种情况详解

    目录 结论 不建议建立索引的场景 索引失效的场景 小结 结论 具体案例下文有详尽描述 不适合建立索引的场景: 数据量比较小的表不建议建立索引 有大量重复数据的字段上不建议建立索引(类似:性别字段) 需要进行频繁更新的表不建议建立索引 where.group by.order by后面的没有使用到的字段不建立索引 不要定义冗余索引 索引失效的场景: 过滤条件使用不等于(!=.<>) 过滤条件使用is not null 在索引字段上使用函数或进行计算 在使用联合索引的时候,需要满足“最佳左前缀法则

  • SQL索引失效的11种情况详析

    目录 索引失效案例 [1]. 全值匹配 [2]. 最佳左前缀法则 [3]. 主键插入顺序 [4]. 计算.函数.类型转换(自动或手动)导致索引失效 [5]. 类型转换导致索引失效 [6]. 范围条件右边的列索引失效 [7]. 不等于(!= 或者<>)索引失效 [8]. is null可以使用索引,is not null无法使用索引 [9]. like以通配符%开头索引失效 [10]. OR 前后存在非索引的列,索引失效 [11]. 数据库和表的字符集统一使用utf8mb4 总结 数据库调优的大

  • 哪些情况会导致 MySQL 索引失效

    哪些情况会导致 MySQL 索引失效

    目录 前言 创建测试表和数据 索引失效情况1:非最左匹配 索引失效情况2:错误模糊查询 索引失效情况3:列运算 索引失效情况4:使用函数 索引失效情况5:类型转换 索引失效情况6:使用 is not null 总结 前言 为了验证 MySQL 中哪些情况下会导致索引失效,我们可以借助 explain 执行计划来分析索引失效的具体场景. explain 使用如下,只需要在查询的 SQL 前面添加上 explain 关键字即可,如下图所示: 而以上查询结果的列中,我们最主要观察 key 这一列,ke

  • mysql索引失效的常见九种原因图文详解

    mysql索引失效的常见九种原因图文详解

    目录 前言: 1.最佳左前缀法则 3.计算.函数.类型转换(自动或手动)导致索引失效 4.范围条件右边的列索引失效 5.不等于(!= 或者<>)导致索引失效 6.is null可以使用索引,is not null无法使用索引 7.like以通配符%开头索引失效 8.OR 前后只要存在非索引的列,都会导致索引失效 9.数据库和表的字符集统一使用utf8mb4 总结 前言: MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引.索引提供了高效访问数据的方法,并且加快查询的速度, 因此索引

随机推荐