MySQL细数发生索引失效的情况

目录
  • 索引的存储结构
  • 不合理的模糊查询条件
  • 对索引使用函数
  • 对索引进行表达式计算
  • 对索引使用隐式转换
  • 联合索引非最左匹配
  • where子句中的or
  • 总结

索引的存储结构

首先了解一下索引的存储结构，知道了索引的存储结构，才方便我们更好地理解索引失效的问题。

索引的存储结构跟MySQL的存储引擎有关，存储引擎的不同采用的结构也会不同。

MySQL默认的存储引擎InnoDB采用B+Tree作为索引的数据结构，在创建表时，InnoDB会默认创建一个主键索引，这是一个聚簇索引，其他索引都属于二级索引。

MyISAM存储引擎在创建表时，默认是用的是B+树索引。

虽然和InnoDB一样都支持B+树索引，但是他们存储数据的方式不同；

InnoDB是聚簇索引（B+树索引的叶子结点保存数据本身）

MyISAM是非聚集索引（B+树的叶子结点保存数据的物理地址）

如下图所示：

InnoDB存储引擎可以分为【聚簇索引】和【二级索引】，它们的区别在于聚簇索引的叶子结点存放的是实际数据，所有完整的数据都存放在聚簇索引的叶子结点，二级索引的叶子结点存放的是主键值。

在使用二级索引字段作为查询条件，查询数据在聚簇索引上的时候，

会先根据条件在二级索引上找到对应的叶子结点得到主键值，

再根据主键值去聚簇索引上找到对应的叶子结点然后查询到对应的数据，

这个过程叫回表

使用二级索引作为查询条件，查询的数据在二级索引的叶子结点上的时候，那么只需找到二级索引的B+树对应的叶子结点，读取数据，这个过程叫覆盖索引

上面这些查询条件都用到了索引列，但并不表示用到索引列索引就一定会生效，我们再来看一看索引失效的情况

不合理的模糊查询条件

使用左或左右模糊查询的时候，也就是like "%张"或like "%张%"这两种模糊查询方式都会导致索引失效

因为B+树是根据索引值进行排列的，前缀不确定的时候可能是，“小张”，"二张"之类的所有的情况，就只能通过全表扫描的方式来查询

对索引使用函数

例如：SELECT * FROM sys_user WHERE LENGTH(user_id) = 3 ;

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，所以使用函数的时候就不会走索引了

不过从MySQL8.0开始，索引特性增加了函数索引，也就是针对该函数计算后的值建立一个索引，这样就可以通过扫描索引来查询数据了；

alter table t_user add key idx_name_length ((length(name)));

对索引进行表达式计算

例如：select * from sys_user where user_id+1 =3;

但是如果是SELECT * FROM sys_user WHERE user_id = 1+1 ;这样的不在索引字段上进行计算，就又会走索引了

原因跟对索引使用函数差不多，索引保存的是索引字段的原始值，而不是运算后的值，所以无法走索引

对索引使用隐式转换

这里的phone字段是二级索引，且是varchar类型的

使用整型作为查询参数的时候，执行计划中type为ALL，也就是通过全表扫描查询的，但如果是字符串类型，还是走索引查询的

我们再看一个例子

这里user_id是bigint类型，但是使用字符串作为查询参数还是走了索引的

为什么第一个例子导致了索引失效，而第二个不会呢？

这里就要了解一下MySQL的字符转换规则了，看是数字转字符串，还是字符串转数字

我们可以用select "10">9来测试一下

如果是数字转字符串，那么就相当于select "10">"9"结果应该是0

如果是字符串转数字，那么就相当于select 10>9，结果是1

在MySQL中的执行结果如下：

这就说明，MySQL在遇到数字与字符串的比较的时候，会自动把字符串转换为数字，然后进行比较

也就是说，在第一个例子中

SELECT * FROM sys_user WHERE phone = 18200000000 ;

相当于

SELECT * FROM sys_user WHERE CAST(phone AS UNSIGNED) = 18200000000 ;

这就在索引字段上使用了函数，所以导致索引失效

而在第二个例子中

SELECT * FROM sys_user WHERE user_id = "1" ;

相当于

SELECT * FROM sys_user WHERE user_id = CAST("1" AS UNSIGNED) ;

函数式作用在查询参数上的，并没有作用在索引字段上，所以还是走索引的

联合索引非最左匹配

多个普通字段组合在一起创建的索引叫做联合索引（组合索引）

在使用联合索引的时候，一定要注意顺序问题，联合索引的使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引匹配。

例如，创建了一个(a,b,c)联合索引，那么如果查询条件是一下几种，就可以匹配上联合索引

where a = 1
where a = 1 and b = 2
where a = 1 and b = 2 and c = 3

需要注意的是，因为有查询优化器，所以a字段在where子句中的顺序不重要

但是必须要有a字段，如果像下面几种，因为不符合最左匹配原则，就无法匹配上联合索引，联合索引就会失效：

where b = 2
where c = 3
where b = 2 and c = 3

还有一个比较特殊的查询条件：where a = 1 and c = 3

在MySQL5.5的话，前面的a 会走索引，在联合索引找到主键值，然后回表，到主键索引读取数据行，然后在比对c字段的值

在MySQL5.6之后，有一个索引下推的功能，

下推就是将部分上层(服务层)负责的事情，交给了下层(引擎层)处理

存储引擎直接在联合索引里按照c=3过滤，按照过滤后的数据在进行回表扫描，减少了回表的次数，从而提升了性能

在执行计划中Extra = Using index condition就表示使用了索引下推

联合索引不遵循最左匹配原则的原因：在联合索引中，数据按照第一列索引进行排序，第一列数据相同时，才会按照第二列进行排序，以此类推，所以直接使用第二列进行查询的时候，联合索引就会失效

where子句中的or

where子句中or的条件列有不是索引列会导致索引失效

例如：下图中id是索引列，email不是索引列，从执行计划来看，进行了全文扫描并没有使用到索引

因为or关键字只满足一个条件就可以，因此只要有一个列不是索引列，其他索引列也就没有意义了，就会进行全表扫描

在email列上建立索引之后，可以看到执行计划中使用到了两个索引

type = index_merge表示对id 和email都进行了扫描，然后进行了合并

总结

导致索引失效的情况有：

不合理的使用模糊查询：like "%张"或like %张%

对索引列使用函数

对索引使用表达式计算

对索引使用隐式转换，这三个都是引起了索引列值的变化导致索引失效

联合索引非最左匹配

where子句中or条件列没有使用索引

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

赞 (0)

相关推荐

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

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

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

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

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

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

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

  • 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 索引失效

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

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

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

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

  • 分享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细数发生索引失效的情况

    目录 索引的存储结构 不合理的模糊查询条件 对索引使用函数 对索引进行表达式计算 对索引使用隐式转换 联合索引非最左匹配 where子句中的or 总结 索引的存储结构 首先了解一下索引的存储结构,知道了索引的存储结构,才方便我们更好地理解索引失效的问题. 索引的存储结构跟MySQL的存储引擎有关,存储引擎的不同采用的结构也会不同. MySQL默认的存储引擎InnoDB采用B+Tree作为索引的数据结构,在创建表时,InnoDB会默认创建一个主键索引,这是一个聚簇索引,其他索引都属于二级索引. M

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

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

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

  • sql索引失效的情况以及超详细解决方法

    sql索引失效的情况以及超详细解决方法

    目录 前言 数据准备 1.查询条件中有or,即使有部分条件带索引也会失效 2.like查询是以%开头 3.如果列类型是字符串,那在查询条件中需要将数据用引号引用起来,否则不走索引 4.索引列上参与计算会导致索引失效 5.违背最左匹配原则 6.如果mysql估计全表扫描要比使用索引要快,会不适用索引 7.other 补充:索引注意事项 总结 前言 大家都知道,一条查询语句走了索引和没走索引的查询效率是非常大的,在我们建好了表,建好了索引后,但是一些不好的sql会导致我们的索引失效,下面介绍一下索引

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

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

  • 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,

  • 关于Mysql5.7及8.0版本索引失效情况汇总

    目录 一个独立索引 多个独立索引 总结 TIPS: 没有特殊说明,测试环境均为MySQL8.0,早期版本可能会有更多情况导致索引失效.8.0失效的情况,早期版本也失效:8.0不失效的情况,早期版本可能失效. 所有测试默认不考虑表为空的情况,特殊情况文中会有说明. 本文只介绍Innodb引擎下的索引失效情况. -- 创建测试表 DROP TABLE IF EXISTS `test_idx`; CREATE TABLE `test_idx` ( `id` int(11) NOT NULL AUTO_

  • 这种sql写法真的会导致索引失效吗

    前言 网上经常能看到一些文章总结在 mysql 中不能命中索引的各种情况,其中有一种说法就是指使用了 or 的语句都不能命中索引. 这种说法其实是不够正确的,正确的结论应该是,从 mysql5.0 后,如果在 or 连接的字段上都有独立的索引的话,是可以命中索引的,这里就是用到了 index_merge 特性. 在 mysql5.0 版本以前一条 sql 只能选择使用一个索引,而且如果 sql 中使用了 or 关键字,那么已有的索引就会失效,会走全表扫描.因为无论走哪个索引,mysql 都不能一

  • 为什么Mysql 数据库表中有索引还是查询慢

    为什么Mysql 数据库表中有索引还是查询慢

    目录 前言: 1.字段类型不匹配导致的索引失效 2.被索引字段使用了表达式计算 3.被索引字段使用了内置函数 4.like 使用了 %X 模糊匹配 5.索引字段不是联合索引字段的最左字段 6.or 分割的条件 7.in.not in 可能会导致索引失效 总结 前言: 问题分析: 在进行数据库查询的时候,我们都知道索引可以加快数据查询的效率.但是在实际的业务场景下,经常会遇到即使在表中增加了索引,但是同样还是会出现数据查询慢的问题.这就需要具体分析数据查询慢的具体原因到底是什么了. 首先需要进行确

  • mysql IS NULL使用索引案例讲解

    mysql IS NULL使用索引案例讲解

    简介 mysql的sql查询语句中使用is null.is not null.!=对索引并没有任何影响,并不会因为where条件中使用了is null.is not null.!=这些判断条件导致索引失效而全表扫描. mysql官方文档也已经明确说明is null并不会影响索引的使用. MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value.

随机推荐