MySQL索引失效的9种情况（针对InnoDB存储引擎）

article/2025/9/30 0:47:43

前言

MySQL中提高查询性能的最有效的方式之一就是对数据表合理的设计索引，优秀的索引的设计方案很大程度上可以提高查询的性能。

因此，索引对查询的速度有着至关重要的影响。

为了尽量的使优化器用到我们的索引方案，我们要尽量避免一些导致索引失效的情况，本篇文章将向大家介绍导致索引失效的9种情况！！

…

测试用例：存在表student，表中含有50万条记录。表结构如下：

CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`) #设置主键，同时设置主键索引
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

本篇文章 EXPLAIN工具来分析查询语句，不知道这个工具的小伙伴可以自行先了解一下。

当然，本篇文章也会向大家简单介绍一下。

索引失效的9种情况

首先使用如下命令来查看当前表中存在的索引。

SHOW INDEX FROM student; #查看student表中的所有索引

运行结果如下：
在这里插入图片描述

其中，table字段表示当前索引所属数据表，key_name字段表示索引名称，column_name表示当前索引作用的字段名称。

EXPLAIN 工具的介绍

MySQL为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，大家看懂EXPLAIN语句的各个输出项，可以有针对性的提升我们查询语句的性能。

它能做的最核心的两个作用是：哪些索引被实际使用 和 每张表有多少行记录被优化器查询。

//语法格式
EXPLAIN SELECT语句

版本情况：MySQL 5.6.3以前只能 EXPLAIN SELECT ；MYSQL 5.6.3以后就可以 EXPLAIN SELECT，UPDATE，DELETE

若我们希望查看某个查询语句的执行计划，则可以在具体的查询语句前加一个EXPLAIN关键字，如下：

EXPLAIN SELECT * FROM student;

在这里插入图片描述

这里我们只介绍几个重要的字段

  • type ：MySQL对某个表的执行查询时的访问方法，也称访问类型。完整的访问类型如下：system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ，index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。越靠近前面的类型说明这条查询的效率越高
  • possible_keys：keys也称index，这个字段就是我们这条查询可能用到的所有索引，都会列在这个字段上
  • key：表示当前查询所使用的索引，若为NULL则表示没有使用索引
  • key_len：实际使用到的索引长度(即：字节数)，在联合索引里面，命中一次key_len加一次长度。越长代表精度越高，效果越好

如果想了解关于EXPLAIN更详细的介绍，大家可以参考以下EXPLAIN的官方网址

5.7版本链接: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
8.0版本链接: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

下面将介绍关于MySQL索引失效的9种情况：

注：以下所有测试都是基于MySQL8.0.26，存储引擎是InnoDB

1.最佳左前缀原则

在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

口诀：带头大哥不能死，中间兄弟不能短。大致意思就是假设我们创建联合索引，索引的顺序是name，age，classid三个字段。那么我们的执行器会先按照name字段排序，当出现name重复的时候，再按age字段排序，age重复再按照classid排序…最后再生成二级索引。
在这里插入图片描述

所以，当我们匹配索引时，也是按照这个顺序，即先按照name字段查找，当匹配到name字段时，再按照age字段来匹配，age字段若匹配到22时，最后在匹配classid字段的值。所以不能直接越过某一个字段而查找下一个字段。也不能直接越过第一个开始的字段。也就是上面的那个口诀。

假设有如下SQL语句：

SELECT * FROM student WHERE age = 22 AND classid = 1;

上面的SQL语句就不符合最左前缀法则，因为查询语句是按照age和classid来查找元素的。但是我们的索引必须要先确定name字段，才能知道下一步去哪里查询。上面的SQL语句明显不符合我们刚才创建的索引的要求，所以这条SQL不会走索引逻辑。即索引失效了。

在这里插入图片描述

可以看到，possible_keys和key字段都为NULL

正确的做法是：不能缺少第一个匹配的字段，并且不能越过某一个中间字段

EXPLAIN SELECT * FROM student WHERE name = 'abc' AND age = 22 AND classid = 1;

在这里插入图片描述

由上图可以看到，key字段使用到了我们设置的索引idx_name_age_classid

问题：那么请问如下SQL语句是否能正确触发idx_name_age_classid索引呢？

EXPLAIN SELECT * FROM student WHERE age = 22 AND name = 'abc' AND classid = 1;

2.类型转换导致索引失效

当我们发生类型转换时，对应的索引也会失效。

先针对name字段创建二级索引，代码如下：

CREATE INDEX idx_name ON student(name);

在这里插入图片描述

执行如下代码：

# 未使用到索引
EXPLAIN SELECT * FROM student WHERE name = 123;

上述SQL语句中，where条件的name字段进行了自动类型转换（name字段是varchar类型）。执行结果如下：
在这里插入图片描述

可见我们的possible_keys字段存在我们刚才创建的索引，但是key字段还是为NULL，即表示没有使用任何索引。

解决办法：尽量避免使用自动类型转换

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

当我们使用联合索引时，当联合索引的字段采用了范围匹配，那么该字段的右侧字段将会失效

假设存在联合索引idx_age_classid_name，其字段顺序为age、classid、name。
在这里插入图片描述

若执行如下SQL语句，name字段将会失效，即不会在索引中被使用来查找。

EXPLAIN SELECT * FROM student WHERE student.age = 'abc' AND student.classId > 20 AND student.name = 22;

由于索引的顺序是age、classid、name，然后classid使用了范围条件，那么name字段将会失效。执行结果如下：
在这里插入图片描述

根据key_len字段，我们就可以看出，没有使用到name字段，因为name字段所占字节数必然大于10的长度，name所占字节至少为60（utf8中，一个字符占3个字节，name字段为20个字符长度，即20*3）

结论：因为classid用上了范围查找，在范围查找的索引后面的索引就失效了。

解决方案：范围条件导致的索引失效，可以考虑把确定的索引放在前面，不确定的索引放在最后。

所以，针对idx_age_classid_name索引，我们可以修改为idx_age_name_classid，即根据age、name、classid这样的顺序创建索引

CREATE INDEX idx_age_name_classid ON student(age,name,classid);

索引修改后的执行结果如下：
在这里插入图片描述

可见，对于这三个字段（age，name，classid），我们的索引全部都匹配到了

Tip：应用开发中范围查询，例如： 金额查询，日期查询往往都是范围查询。创建联合索引时考虑放在后面。

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

  • 为name字段创建索引
CREATE INDEX idx_name ON student(NAME);
  • 查看索引是否失效
EXPLAIN SELECT * FROM student WHERE student.name != 'abc';

返回结果：
在这里插入图片描述

对于<>也是一样的，同样会导致索引失效，这里不做演示，大家自行尝试。

5. IS NULL可以使用索引，IS NOT NULL无法使用索引

  • 前提：表中还是存在名称为idx_name的索引
# IS NOT NULL 索引失效
EXPLAIN SELECT * FROM student WHERE name IS NOT NULL;

上面这条SQL语句在设置了索引的字段上使用了IS NOT NULL 判断，那么idx_name索引将会失效，运行结果如下：
在这里插入图片描述

下面我们再使用IS NULL 来判断一下
在这里插入图片描述

可以发现，key字段使用了idx_name索引，IS NULL 判断不会导致索引的失效

Tip：最好在设计数据表的时候就将字段设置为 NOT NULL 约束，比如你可以将INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串。

拓展：同理，在查询中使用not like 也无法使用索引，会导致全表扫描。

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

凡是对某个索引字段采用like匹配时，若匹配的字符以%开头，那么这个索引字段将会失效。只有"%"不在第一个位置，索引才会起作用。
在这里插入图片描述

为什么like以%开头会导致索引失效？

其原因就是因为如果匹配的字符以%开头，那么表示可以匹配任意字符，而idx_name索引结构本身是按照name字段进行排序的，匹配任意字符也就使这个排序根本派不上用场，因为无论是否排序，都需要全表扫描。这个时候我们的查询优化器就认为，反正都需要扫描全表，那么还不如直接扫，就不会使用这个索引。

下面是like匹配时不使用%开头的查询计划：（可以根据索引，先匹配abc，然后再匹配任意字符，节约查询成本）
在这里插入图片描述

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

7.OR 前后存在非索引的列，索引失效

在WHERE子句中，如果在OR前的条件列设置了索引，而在OR后的条件列没有设置索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引，否则就不能使用该索引。

原因就是OR的含义就是两者之间满足一个即可，因此，如果只有一个列使用索引是毫无意义的。因为另外一个列还是会全表扫描，这个时候，查询优化器就会认为，反正都需要全表扫描，那还不如两个字段一起全部扫描。因此，使用索引的条件列将会失效

  • 假设当前存在以name字段创建的二级索引idx_name，执行如下代码：
EXPLAIN SELECT * FROM student WHERE name like 'abc%' OR age = 11;

在这里插入图片描述

可以发现，以上SQL查询不会使用索引。

若OR关键字两边的字段都有索引，那么这个查询语句才会走索引

  • 为age字段创建索引
CREATE INDEX idx_age ON student(age);

再次执行上面的SQL代码：
在这里插入图片描述

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

当索引列使用了计算、函数或者类型转换时，那么这个索引列将会失效。

#使用left()函数导致idx_name索引失效
EXPLAIN SELECT * FROM student WHERE LEFT(student.name,3) = 'abc';#针对stuno字段设置索引
CREATE INDEX idx_sno ON student(stuno);
#使用计算导致idx_sno索引失效（stuno + 1 = 900001）
EXPLAIN SELECT id, stuno, NAME FROM student WHERE stuno + 1 = 900001;#使用自动类型转换导致索引失效（name字段是varchar类型）
EXPLAIN SELECT * FROM student WHERE student.name = 123;

以上三种情况，大家可以复制SQL语句去尝试一下。

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

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

总结

  • 对于单列索引，尽量选择针对当前query过滤性更好的索引。（过滤：就是针对这个字段，可以过滤掉的数据，若某一个字段过滤的数据越多，表示它越适合设置为索引。假设设置sex字段为索引，它就只能过滤50%的数据）
  • 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引。（前面先尽可能过滤掉大部分数据，减轻后面索引的过滤压力）
  • 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。（因为范围条件右侧的索引列将失效）

写在最后

索引在数据库中的重要性可想而知，针对索引失效的情况 ，我们一定要避而远之，关于索引失效的这几种情况，大家可以去尝试一下。表中的数据，如果有需要的也可以私信我。

以上就是本篇文章的所有内容，如果对你有所帮助，希望点赞+收藏+关注支持一下。后续会继续更新关于MySQL方面的知识。

我是胡亦，一名热爱分享技术干货的博主。

我们下次再见！！🙋‍♂️🙋‍♂️


http://chatgpt.dhexx.cn/article/gctAt7ZW.shtml

相关文章

Mysql：索引失效的几种可能原因

Mysql：索引失效的几种可能原因

最近闲来无事&#xff0c;发现自己mysql的知识点忘得差不多了&#xff0c;打算重新拾起来捯饬捯饬&#xff0c;个人觉得其中最重要的知识点还是mysql的优化问题&#xff0c;毕竟mysql使用方面都是很简单的东西&#xff0c;打算从索引方面开始学习吧。 有时候我们会发现&#xf…
阅读更多...
详解MySQL索引失效

详解MySQL索引失效

目录 B树结构 测试数据 索引失效的情况 没有用到索引 违反左前缀原则 范围查询断索引 like需要分情况 结果数据超过半数 B树结构 索引失效的根本原因其实就是违反了B树的结构特性&#xff0c;查找的时候没办法在B树上继续走下去&#xff0c;所以首先我们来回顾一下B树…
阅读更多...
MySQL索引失效的场景，什么情况下会造成MySQL索引失效

MySQL索引失效的场景，什么情况下会造成MySQL索引失效

目录 一、准备工作&#xff0c;准备sql 二、索引是什么、使用explain关键字来解析sql使用索引情况 三、索引的使用与失效场景 1.使用全部复合索引&#xff08;索引正常使用&#xff09; 2.最佳左前缀法则 3.在索引列上做任何操作&#xff08;计算、函数、自动或手动类型转…
阅读更多...
Mysql索引失效的总结

Mysql索引失效的总结

背景 最近生产爆出一条慢sql&#xff0c;原因是用了or和!&#xff0c;导致索引失效。于是&#xff0c;总结了索引失效的十大杂症&#xff0c;希望对大家有帮助&#xff0c;加油。 一、查询条件包含or&#xff0c;可能导致索引失效 新建一个user表&#xff0c;它有一个普通索…
阅读更多...
Mysql索引失效的常见原因如何用好索引

Mysql索引失效的常见原因如何用好索引

本篇文章主要介绍了索引失效的常见原因和如何用好索引&#xff0c;并以案例的形式进行了说明 1 结论 1.1 索引失效的常见原因 1.2 索引失效的常见误区 1.3 索引设计的几个建议 2 准备工作 先查看我本地mysql数据库的版本: select VERSION();我这里的版本是5.5.25&#xff0…
阅读更多...
mysql索引失效情况

mysql索引失效情况

1、最佳左前缀原则——如果索引了多列&#xff0c;要遵守最左前缀原则。指的是查询要从索引的最左前列开始并且不跳过索引中的列。 前提条件&#xff1a;表中已添加复合索引&#xff08;username,password,age&#xff09; 分析&#xff1a;该查询缺少username&#xff0c;查询…
阅读更多...
MySQL中索引失效有哪些？

MySQL中索引失效有哪些？

前言 在学习MySQL时&#xff0c;如果我们想提高一条语句查询速度&#xff0c;通常都会想对字段建立索引。 但是索引并不是万能的。建立了索引&#xff0c;并不意味着任何查询语句都能走索引扫描。 稍不注意&#xff0c;可能你写的查询语句会导致索引失效&#xff0c;从而走了…
阅读更多...
MySQL之索引失效情况

MySQL之索引失效情况

文章目录 1 MySQL索引1.1 简介1.1.1 索引基础 1.2 SQL优化1.2.1 查看执行计划1.2.2 show profile分析1.2.3 trace 2 索引失效2.1 准备工作2.1.1 创建user表2.1.2 插入数据2.1.3 查看数据库版本 2.2 联合索引2.2.1 联合索引中索引有效2.2.2 联合索引中索引失效2.2.3 MySQL 8中索…
阅读更多...
Mysql索引失效

Mysql索引失效

一.前言 针对Mysql数据库索引失效问题网上&#xff0c;有很多见解和答案。最近面试也是时常会被问道的一个知识点&#xff0c;简单的整理一下,省的以后忘记。 二.导致索引失效的几个情况 1.模糊查询:like %&#xff1b;like %%通配符放在开头会导致索引失效。会进行全表扫描…
阅读更多...
详解MySQL索引失效的几种情况

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

MySQL索引是提高查询效率的重要手段。索引失效会导致查询效率下降&#xff0c;甚至全表扫描&#xff0c;影响数据库性能。以下是可能导致MySQL索引失效的情况&#xff1a; 1. 使用or操作符 当where语句中使用or操作符并且or两边的条件涉及到至少两个字段时&#xff0c;MySQL无…
阅读更多...
mysql索引失效的常见9种原因详解

mysql索引失效的常见9种原因详解

目录 前言&#xff1a; 1.最佳左前缀法则 2.主键插入顺序 3.计算、函数、类型转换(自动或手动)导致索引失效 4.范围条件右边的列索引失效 5.不等于(! 或者<>)导致索引失效 6.is null可以使用索引&#xff0c;is not null无法使用索引 7.like以通配符%开头索引失效…
阅读更多...
【第三篇】MySQL 索引失效的常见原因【重点】

【第三篇】MySQL 索引失效的常见原因【重点】

1.1 概述 有时候不知道小伙伴有没有跟我一样的情况&#xff0c;明明已经建立了索引&#xff0c;但是通过explain发现语句并没有使用上索引&#xff0c;这可能是某些原因导致了我们的索引失效。所以本篇主要介绍的是索引失效的常见原因和如何用好索引&#xff0c;给有需要的朋友…
阅读更多...
你的凭据不工作，之前用于连接到（服务器IP）的凭据无法工作，请输入新的凭据（中英文均有）

你的凭据不工作，之前用于连接到（服务器IP）的凭据无法工作，请输入新的凭据（中英文均有）

之前windows还能够访问远程桌面&#xff0c;更新了系统发现又不行了&#xff0c;所以记录一下&#xff0c;解决方案&#xff1a; 在搜索里面输入gpedit.msc&#xff0c;进入到本地组策略编辑器&#xff1a; 中文版&#xff0c;把蓝色选中的策略改为&#xff08;经典&#xff…
阅读更多...
远程桌面提示:你的凭据不工作

远程桌面提示:你的凭据不工作

<<记录>> 远程连接服务器的时候&#xff0c;一直提示“你的凭据不工作”&#xff0c;连接失败。在网上找了很多办法&#xff0c;在注册表里设置凭据&#xff0c;或是添加windows新建凭证管理器等都不行。 如下图所示&#xff1a; 解决办法&#xff1a; 在输入用户…
阅读更多...
Win10远程连接凭据不工作问题

Win10远程连接凭据不工作问题

Win10远程连接凭据不工作问题 今天为了远程连接可谓是煞费苦心&#xff0c;不过功夫不负有心人&#xff0c;问题终于解决了&#xff0c;不过在解决问题的过程中确实踩了无数的坑&#xff01;&#xff01;&#xff01;&#xff01; 话不多说&#xff0c;直接上干货。 当远程连接…
阅读更多...
远程桌面失败：你的凭据不工作

远程桌面失败：你的凭据不工作

方法一&#xff1a; 运行WinR&#xff0c;输入secpol.msc或是gpedit.msc: 然后按照以下操作&#xff0c;将默认设置“仅来宾—本地用户以来宾身份验证”&#xff0c;更改为“经典&#xff1a;本地用户以自己的身份验证”。 方法二&#xff1a; 更改远程桌面服务端的一个远程服…
阅读更多...
win10远程桌面连接凭据怎么设置_win10系统连接远程桌面提示你的凭据不工作的解决方法...

win10远程桌面连接凭据怎么设置_win10系统连接远程桌面提示你的凭据不工作的解决方法...

无论谁在使用电脑的时候都可能会发现连接远程桌面提示你的凭据不工作的问题&#xff0c;连接远程桌面提示你的凭据不工作让用户们很苦恼&#xff0c;这是怎么回事呢&#xff0c;连接远程桌面提示你的凭据不工作有什么简便的处理方式呢&#xff0c;其实只要依照在Windows10系统桌…
阅读更多...
win10 win11 远程连接 凭据不工作 无法建立连接

win10 win11 远程连接 凭据不工作 无法建立连接

Windows 远程连接 远程连接个人或者学校电脑&#xff08;Windows系统&#xff09;时经常遇到无法连接的情况。本文结合两种情况给出相应解决方法。 No.1 问题描述 无法建立连接、未知连接错误 解决方法 查看目标ipv4地址是否正确&#xff0c;在windows&#xff08;WinR&#…
阅读更多...
《远程连接》用户凭据不工作—解决办法！！

《远程连接》用户凭据不工作—解决办法！！

《远程连接》用户凭据不工作—解决办法&#xff01;&#xff01; 搜了很多教程发现并不管用&#xff0c;最后仔细观察才发现根本原因是什么&#xff01;&#xff01; 因为我两个电脑登的Microsoft不是同一个账户&#xff0c;所以用户名一直输入错误&#xff01;&#xff01;&…
阅读更多...
远程计算机凭据不对,三种办法助你解决远程桌面提示“您的凭据不工作”的问题...

远程计算机凭据不对,三种办法助你解决远程桌面提示“您的凭据不工作”的问题...

随着电脑的使用率越来越高&#xff0c;我们可能会遇到远程桌面提示“您的凭据不工作”的问题&#xff0c;要怎么处理呢&#xff1f;详细解决方法如下&#xff1a; 方法一&#xff1a;组策略编辑器设置身份验证 1、打开组策略编辑器。 找到开始--- 运行-- 输入 gpedit.msc&#…
阅读更多...
推荐文章

Copyright @ 2022~2023