Mysql索引失效的总结

article/2025/9/30 0:45:58

背景

最近生产爆出一条慢sql，原因是用了or和!=，导致索引失效。于是，总结了索引失效的十大杂症，希望对大家有帮助，加油。

一、查询条件包含or，可能导致索引失效

新建一个user表，它有一个普通索引userId，结构如下：

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`userId` int(11) NOT NULL,`age` int(11) NOT NULL,`name` varchar(255) NOT NULL,PRIMARY KEY (`id`),KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  1. 执行一条查询sql，它是会走索引的，如下图所示：
  2. 把or条件+没有索引的age加上，并不会走索引，如图：

分析&结论：

  • 对于or+没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程： 全表扫描+索引扫描+合并
  • 如果它一开始就走全表扫描，直接一遍扫描就完事。
  • mysql是有优化器的，处于效率与成本，遇到or条件，索引可能失效，看起来也合情合理。

注意: 如果or条件的列都加了索引，索引可能会走的，大家可以自己试一试。

二、如何字段类型是字符串，where时一定用引号括起来，否则索引失效

假设demo表结构如下：

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`userId` varchar(32) NOT NULL,`name` varchar(255) NOT NULL,PRIMARY KEY (`id`),KEY `idx_userId` (`userId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

userId为字符串类型，是B+树的普通索引，如果查询条件传了一个数字过去，它是不走索引的，如图所示：

如果给数字加上’’,也就是传一个字符串呢，当然是走索引，如下图：

分析与结论：

为什么第一条语句未加单引号就不走索引了呢？
这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。

三、like通配符可能导致索引失效。

并不是用了like通配符，索引一定失效，而是like查询是以%开头，才会导致索引失效。

表结构：

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`userId` varchar(32) NOT NULL,`name` varchar(255) NOT NULL,PRIMARY KEY (`id`),KEY `idx_userId` (`userId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

like查询以%开头，索引失效，如图：

把%放后面，发现索引还是正常走的，如下：

把%加回来，改为只查索引的字段（覆盖索引），发现还是走索引，惊不惊喜，意不意外

结论：

like查询以%开头，会导致索引失效。可以有两种方式优化：

  • 使用覆盖索引
  • 把%放后面

附: 索引包含所有满足查询需要的数据的索引，称为覆盖索引(Covering Index)。

四、联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。

表结构：（有一个联合索引 idx_userid_age，userId在前，age在后）

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`userId` int(11) NOT NULL,`age` int(11) DEFAULT NULL,`name` varchar(255) NOT NULL,PRIMARY KEY (`id`),KEY `idx_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

在联合索引中，查询条件满足最左匹配原则时，索引是正常生效的。请看demo：

如果条件列不是联合索引中的第一个列，索引失效，如下：

分析与结论：

  • 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。
  • 联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的。

五、在索引列上使用mysql的内置函数，索引失效。

表结构：

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`userId` varchar(32) NOT NULL,`loginTime` datetime NOT NULL,PRIMARY KEY (`id`),KEY `idx_userId` (`userId`) USING BTREE,KEY `idx_login_time` (`loginTime`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

虽然loginTime加了索引，但是因为使用了mysql的内置函数Date_ADD()，索引直接GG，如图：

六、对索引列运算（如，+、-、*、/），索引失效。

表结构：

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`userId` varchar(32) NOT NULL,`age` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

虽然age加了索引，但是因为它进行运算，索引直接迷路了。。。
如图：

七、索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。

表结构：

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`userId` int(11) NOT NULL,`age` int(11) DEFAULT NULL,`name` varchar(255) NOT NULL,PRIMARY KEY (`id`),KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

虽然age加了索引，但是使用了！= 或者 < >，not in这些时，索引如同虚设。如下：

八、索引字段上使用is null， is not null，可能导致索引失效。

表结构:

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`card` varchar(255) DEFAULT NULL,`name` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_name` (`name`) USING BTREE,KEY `idx_card` (`card`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

单个name字段加上索引，并查询name为非空的语句，其实会走索引的，如下:

单个card字段加上索引，并查询name为非空的语句，其实会走索引的，如下:

但是它两用or连接起来，索引就失效了，如下：

九、左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

新建两个表，一个user，一个user_job

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,`age` int(11) NOT NULL,PRIMARY KEY (`id`),KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;CREATE TABLE `user_job` (`id` int(11) NOT NULL,`userId` int(11) NOT NULL,`job` varchar(255) DEFAULT NULL,`name` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

user 表的name字段编码是utf8mb4，而user_job表的name字段编码为utf8。

执行左外连接查询,user_job表还是走全表扫描，如下：

如果把它们改为name字段编码一致，还是会走索引。

十、mysql估计使用全表扫描要比使用索引快,则不使用索引。

  • 当表的索引被查询，会使用最好的索引，除非优化器使用全表扫描更有效。优化器优化成全表扫描取决与使用最好索引查出来的数据是否超过表的30%的数据。
  • 不要给’性别’等增加索引。如果某个数据列里包含了均是"0/1"或“Y/N”等值，即包含着许多重复的值，就算为它建立了索引，索引效果不会太好，还可能导致全表扫描。

Mysql出于效率与成本考虑，估算全表扫描与使用索引，哪个执行快。这跟它的优化器有关，来看一下它的逻辑架构图吧（图片来源网上）

总结

总结了索引失效的十大杂症，在这里来个首尾呼应吧，分析一下我们生产的那条慢sql。
模拟的表结构与肇事sql如下：

CREATE TABLE `user_session` (`user_id` varchar(32) CHARACTER SET utf8mb4 NOT NULL,`device_id` varchar(64) NOT NULL,`status` varchar(2) NOT NULL,`create_time` datetime NOT NULL,`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`user_id`,`device_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

explain 
update user_session set status =1
where  (`user_id` = '1' and `device_id`!='2')
or (`user_id` != '1' and `device_id`='2')

分析：

  • 执行的sql，使用了or条件，因为组合主键(user_id,device_id)，看起来像是每一列都加了索引，索引会生效。
  • 但是出现!=,可能导致索引失效。也就是or+!=两大综合症，导致了慢更新sql。

解决方案:

那么，怎么解决呢？我们是把 or条件拆掉，分成两条执行。同时给 device_id加一个普通索引。

最后，总结了索引失效的十大杂症，希望大家在工作学习中，参考这十大杂症，多点结合执行计划 expain和场景，具体分析 ，而不是按部就班，墨守成规，认定哪个情景一定索引失效。


http://chatgpt.dhexx.cn/article/8NxDPHu1.shtml

相关文章

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&#…
阅读更多...
【系统相关】远程连接，凭据不工作

【系统相关】远程连接，凭据不工作

前言&#xff1a; 在换电脑之前&#xff0c;我的电脑局域网远程配置成功很简单&#xff0c;但是换了电脑之后&#xff0c;按照常规配置就出现了问题&#xff0c;凭据不工作。先讲述常规配置&#xff0c;以win7为例。被远程电脑为B&#xff0c;远程操作电脑为A。 常规配置&#…
阅读更多...
远程计算机登录时显示凭据不对,连接到远程计算机时凭据不工作

远程计算机登录时显示凭据不对,连接到远程计算机时凭据不工作

在域环境中&#xff0c;如果使用Vista及以上的操作系统&#xff0c;默认情况下&#xff0c;在连接到远程计算机时无法保存凭据。 当我们使用远程桌面连接时&#xff0c;即使已经勾选了“记住我的凭证”。 但是下次登录依然无法保存凭据&#xff0c;会提示“您的凭据不工作” 此…
阅读更多...
Win10 远程桌面 你的凭据不工作

Win10 远程桌面 你的凭据不工作

1.问题描述如图所示 2.问题可能原因 目标机器的访问权限设置为仅来宾身份&#xff0c;应该设置为经典模式。 3.解决问题 具体设置目标机器步骤&#xff1a; 开始-->运行->gpedit.msc->计算机配置->Windows设置->安全设置->本地策略->安全选项->网络…
阅读更多...
WIN间远程桌面连接：你的凭据不工作解决(亲测有效)

WIN间远程桌面连接：你的凭据不工作解决(亲测有效)

WIN间远程桌面连接&#xff1a;你的凭据不工作解决(亲测有效) 注意&#xff1a;需要windows专业版设备 被连接端设置 设置 -》 系统 -》远程桌面 -》启用远程桌面&#xff0c;并记录电脑名称&#xff0c;控制中心 -》系统和安全 -》允许远程访问&#xff0c;设置如下&#xf…
阅读更多...
推荐文章

Copyright @ 2022~2023