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字段从小到大排序；

分析以下情况，索引是否会失效以及失效的原因：

条件只包含b字段

select * from test where  b=2;

索引失效：

显然，走的时候全文扫描，并没有使用索引。因为只看b字段的索引，是2,4,1,3,4,5，并不能利用索引的有序性快速定位。

对a字段范围查询：

select * from test where  a>1 and b=2;

索引失效：

可以看到，索引并没有完全失效，而是先利用索引定位到a的位置。因为这里的key_len是4，而联合索引的key_len是8。

对a字段等值查询，b字段范围查询：

索引失效：

可以看到是using index并且key_len是8，也就是两个字段的索引都用到了，这也对应着联合索引排列的规律：a字段相同的情况下，b字段有序排列。

以上几种情况可以总结为：不符合最左前缀匹配原则导致索引失效。

最左匹配前缀保证可以利用到索引排序的有序性，而把等值查询放在前面，范围查询放在后面，是利用了[前缀字段相等的情况下，后面的索引字段有序]这个特性，是特殊意义下的最左前缀匹配原则。

2、再来看看哪些情况会破坏索引的有序性。

- 对索引字段做函数操作

对索引字段做函数操作，比如y=f(x),并不能保证得到的y的值依然是有序的，在这种弄个情况下，优化器会放弃树的搜索功能，但是不排除优化器在发现该索引树比主键索引小很多的情况下，选择扫描这个索引。

- 隐式类型转换

在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。隐式类型转换的本质是对索引字段使用了CAST()函数，原理同上。

- 隐式字符编码转换

字符串编码转换的本质是使用了CONVERT() 函数。

3、总结

索引失效的原因是优化器发现不能利用索引的有序性，因此在使用索引时，要尽量满足最左前缀匹配原则、范围查询放在最后、不使用%like、 %like%等模糊查询，就是在最大程度利用索引的有序性；但是在某些情况下，优化器只是放弃索引树的搜索功能，可能还是会选择扫描这个索引。

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

赞 (0)

相关推荐

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

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

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

  • mysql回表致索引失效案例讲解

    mysql回表致索引失效案例讲解

    简介 mysql的innodb引擎查询记录时在无法使用索引覆盖的场景下,需要做回表操作获取记录的所需字段. mysql执行sql前会执行sql优化.索引选择等操作,mysql会预估各个索引所需要的查询代价以及不走索引所需要的查询代价,从中选择一个mysql认为代价最小的方式进行sql查询操作.而在回表数据量比较大时,经常会出现mysql对回表操作查询代价预估代价过大而导致索引使用错误的情况. 案例 示例如下,在5.6版本的mysql.1CPU2G内存的Linux环境下,新建一个测试表,并创建将近

  • 浅谈MySql整型索引和字符串索引失效或隐式转换问题

    目录 问题概述 问题重现 问题引申 结论 问题概述 今天在上班时,DBA突然找出来一段sql,表示该sql存在隐式转换,不走索引.经过我们的查看后,发现是类型varchar的字段, 我们使用条件传入了数值型的值,由于担心违反保密协议,在此就不贴图了,由我重现一下类似情况给大家看一下. 问题重现 首先我们先创建一张用户表test_user,其中USER_ID为了效果我们设置为varchar类型且加上唯一索引. CREATE TABLE test_user ( ID int(11) NOT NULL

  • MySQL隐式类型转换导致索引失效的解决

    目录 问题 复现 隐式转换 总结 参考 问题 在工作中发现,有一个接口只执行一条SQL查询语句,并且SQL明明使用了主键列,但是速度很慢. 在MySQL中EXPLAINN后发现,执行时并没有使用主键索引,而是进行了全表扫描. 复现 数据表DDL如下,使用 user_id 作为主键索引: CREATE TABLE `user_message` ( `user_id` varchar(50) NOT NULL COMMENT '用户ID', `msg_id` int(11) NOT NULL COM

  • mysql索引失效的十大问题小结

    mysql索引失效的十大问题小结

    目录 背景 一.查询条件包含or,可能导致索引失效 二.如何字段类型是字符串,where时一定用引号括起来,否则索引失效 三.like通配符可能导致索引失效. 四.联合索引,查询时的条件列不是联合索引中的第一个列,索引失效. 五.在索引列上使用mysql的内置函数,索引失效. 六.对索引列运算(如,+.-.*./),索引失效. 七.索引字段上使用(!= 或者 < >,not in)时,可能会导致索引失效. 八.索引字段上使用is null, is not null,可能导致索引失效. 九.左连

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

    解决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哪些情况会导致索引失效

    浅谈mysql哪些情况会导致索引失效

    下面有一些培训教学机构的口诀和我个人的一些总结: 为了讲解以下索引内容,我们先建立一个临时的表 test02 CREATE TABLE `sys_user` ( `id` varchar(64) NOT NULL COMMENT '主键', `name` varchar(64) DEFAULT NULL COMMENT '名字', `age` int(64) DEFAULT NULL COMMENT '年龄', `pos` varchar(64) DEFAULT NULL COMMENT '职位

  • MySQL索引失效原理

    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字段从小到大排序: 分析以下情况,

  • 分享15个Mysql索引失效的场景

    分享15个Mysql索引失效的场景

    目录 背景 数据库及索引准备 创建表结构 初始化数据 数据库版本及执行计划 1 联合索引不满足最左匹配原则 2 使用了select * 3 索引列参与运算 4 索引列参使用了函数 5 错误的Like使用 6 类型隐式转换 7.使用OR操作 8 两列做比较 9 不等于比较 10 is not null 11 not in和not exists 12 order by导致索引失效 13 参数不同导致索引失效 14 其他 小结 背景 无论你是技术大佬,还是刚入行的小白,时不时都会踩到Mysql数据库不

  • MySQL索引失效场景及解决方案

    MySQL索引失效场景及解决方案

    目录 一.前言 二.最左前缀匹配原则 三.MySQL逻辑架构和优化器 四.索引失效场景以及为何会失效 五.总结 一.前言 在对SQL语句进行索引查询时会遇到索引失效的时候,对于该语句的可行性以及性能效率方面有至关重要的影响,本篇剖析索引为何失效,有哪些情况会导致索引失效以及对于索引失效时的优化解决方案,其中着重介绍最左前缀匹配原则.MySQL逻辑架构和优化器.索引失效场景以及为何会失效. 二.最左前缀匹配原则 之前有写了一篇关于MySQL添加索引特点及优化问题方面的文章,下面将介绍索引失效的相关

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

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

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

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

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

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

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

  • MySQL索引失效的典型案例

    典型案例 有两张表,表结构如下: CREATE TABLE `student_info` (   `id` int(11) NOT NULL,   `name` varchar(10) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `idx_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 CREATE TABLE `student_score` (   `id` int(11) NOT NULL,

  • MySQL索引失效之隐式转换的问题

    目录 常见索引失效: 一.常见索引失效场景 1.条件字段函数操作 2.条件字段运算操作 3.隐式类型转换 4.隐式字符编码转换 二.类型转换 1.字符串转整型 2.时间类型转换 常见索引失效: 1. 条件索引字段"不干净":函数操作.运算操作 2. 隐式类型转换:字符串转数值:其他类型转换 3. 隐式字符编码转换:按字符编码数据长度大的方向转换,避免数据截取 一.常见索引失效场景 root@test 10:50 > show create table t_num\G ******

随机推荐