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

目录
  • 前言:
  • 1.最佳左前缀法则
  • 3.计算、函数、类型转换(自动或手动)导致索引失效
  • 4.范围条件右边的列索引失效
  • 5.不等于(!= 或者<>)导致索引失效
  • 6.is null可以使用索引,is not null无法使用索引
  • 7.like以通配符%开头索引失效
  • 8.OR 前后只要存在非索引的列,都会导致索引失效
  • 9.数据库和表的字符集统一使用utf8mb4
  • 总结

前言:

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法,并且加快查询的速度, 因此索引对查询的速度有着至关重要的影响。

  • 使用索引可以快速地定位表中的某条记录,从而提高数据库查询的速度,提高数据库的性能。
  • 如果查询时没有使用索引,查询语句就会扫描表中的所有记录。在数据量大的情况下,这样查询的速度会很慢。

大多数情况下都(默认)采用B+ 树来构建索引。只是空间列类型的索引使R- 树,并且MEMORY 表还支持hash 索引。其实,用不用索引最终都是优化器说了算

优化器是基于什么的优化器? 基于cost开销(CostBaseOptimizer) ,它不是基于规则( Rule-BasedOptimizer),也不是基于语义。怎么样开销小就怎么来。另外, SQL 语句是否使用索引,跟数据库版本、数据量、数据选择度都有关系

1.最佳左前缀法则

拓展: Alibaba Java 开发手册》 索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。

2.主键插入顺序

如果此时再插入一条主键值为 9 的记录,那它插入的位置就如下图:

可这个数据页已经满了,再插进来咋办呢?我们需要把当前 页面分裂 成两个页面,把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么?意味着: 性能损耗 !所以如果我们想尽量 避免这样无谓的性能损耗,最好让插入的记录的 主键值依次递增 ,这样就不会发生这样的性能损耗了。

所以我们建议:让主键具有 AUTO_INCREMENT ,让存储引擎自己为表生成主键, 在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小,顺序写入,减少页分裂。

3.计算、函数、类型转换(自动或手动)导致索引失效

4.范围条件右边的列索引失效

例子:

#创建一个联合索引, 注意字段的顺序
create index idx_age_classid_name on student(age,classid,name);
#执行计划
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student .age = 30 AND student .classId > 20 AND student .name = 'abc' ;  

#再创建一个联合索引,与上面的索引对比字段顺序变了
create index idx_age_name_classid on student(age,name,classid); 

#再执行一模一样的执行计划
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student .age = 30 AND student .classId > 20 AND student .name = 'abc' ;

看到两个执行计划虽然都用到了索引,但是:

  • 第一个没用全,只用到了联合索引“idx_age_classid_name” 的age和classid。
  • 第二个把联合索引“idx_age_name_classid”的age,name和classid都用上了。

5.不等于(!= 或者<>)导致索引失效

6.is null可以使用索引,is not null无法使用索引

7.like以通配符%开头索引失效

拓展: Alibaba 《Java 开发手册》 【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。

8.OR 前后只要存在非索引的列,都会导致索引失效

9.数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3 版本以上支持 ) 兼容性更好,统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。

总结

到此这篇关于mysql索引失效的常见九种原因的文章就介绍到这了,更多相关mysql索引失效原因内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

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

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

  • 解决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 联合索引生效的条件及索引失效的条件

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

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

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

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

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

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

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

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

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

  • 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索引失效的五种情况分析

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

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

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

随机推荐