首页 > 资讯 > 数据库 >mysql索引失效的十大问题小结

235

分享到

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

2024-04-02 19:04:59 235人浏览薄情痞子

摘要

目录背景一、查询条件包含or，可能导致索引失效二、如何字段类型是字符串，where时一定用引号括起来，否则索引失效三、like通配符可能导致索引失效。四、联合索引，查询时的

背景
一、查询条件包含or，可能导致索引失效
二、如何字段类型是字符串，where时一定用引号括起来，否则索引失效
三、like通配符可能导致索引失效。
四、联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
五、在索引列上使用Mysql的内置函数，索引失效。
六、对索引列运算（如，+、-、*、/），索引失效。
七、索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。
八、索引字段上使用is null， is not null，可能导致索引失效。
九、左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。
十、mysql估计使用全表扫描要比使用索引快,则不使用索引。
总结

背景

最近生产爆出一条慢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;

执行一条查询sql，它是会走索引的，如下图所示：

把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和场景，具体分析 ，而不是按部就班，墨守成规，认定哪个情景一定索引失效。

到此这篇关于mysql索引失效的十大问题的文章就介绍到这了,更多相关mysql索引失效的十大问题内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！

您可能感兴趣的文档:

点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: mysql索引失效的十大问题小结

本文链接: https://www.lsjlt.com/news/135079.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档

去做题

猜你喜欢

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

目录背景一、查询条件包含or，可能导致索引失效二、如何字段类型是字符串，where时一定用引号括起来，否则索引失效三、like通配符可能导致索引失效。四、联合索引，查询时的...

99+

2024-04-02
MySQL索引失效的几种情况小结

目录1.最左前缀原则2. 计算、函数使索引失效3. 类型转换导致索引失效4. 不等于（<>或!=）导致索引失效5. is not null /is null可能不走索引，也可以走索引6. 模糊匹配Like以%...

99+

2023-03-20

MySQL索引失效数据库索引失效
MySQL索引失效问题怎么解决

MySQL索引失效问题可能是由于索引选择不当、数据分布不均匀、查询条件不符合索引规则等原因造成的。解决这些问题可以尝试以下方法： ...

99+

2024-04-09

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

目录常见索引失效：一、常见索引失效场景1、条件字段函数操作2、条件字段运算操作3、隐式类型转换4、隐式字符编码转换二、类型转换1、字符串转整型2、时间类型转换常见索引失效： 1. 条...

99+

2024-04-02
MySQL之主键索引排序失效问题

目录主键索引排序失效现在初始化几行数据查一下所有记录查看一下执行计划总结主键索引排序失效环境：mysql8 有一张用户信息表user_info，建表DDL如下： CREATE TABLE `user_info` ( ...

99+

2022-12-27

MySQL主键索引主键索引排序失效 MySQL索引失效
mysql中like索引失效问题怎么解决

在MySQL中，LIKE索引失效的问题通常是由以下几个原因造成的：通配符在开头：如果LIKE操作符的通配符（%）在搜索模式的开...

99+

2024-02-29

mysql
MySQL索引失效十种场景与优化方案

目录1 数据准备1.1 新建数据表1.2 新增100万条数据2 基础知识2.1 explain type2.2 explain Extra3 索引失效场景3.1 查询类型错误3.1....

99+

2023-05-19

MySQL索引失效场景 MySQL索引优化 MySQL索引失效与优化
mysql索引失效的原因

小编给大家分享一下mysql索引失效的原因，希望大家阅读完这篇文章后大所收获，下面让我们一起去探讨吧！索引对于MySQL而言，是非常重要的篇章。索引知识点也巨多，要想掌握透彻，需要逐个知识点一一击破，今天来...

99+

2024-04-02
MySQL索引失效后隐式转换的问题这么解决

MySQL索引失效后隐式转换的问题这么解决，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。常见索引失效：条件索引字段"不干净"：函数操作、运算操作隐式...

99+

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

目录问题概述问题重现问题引申结论问题概述今天在上班时，DBA突然找出来一段sql，表示该sql存在隐式转换，不走索引。经过我们的查看后，发现是类型varchar的字段，我们使用条...

99+

2024-04-02
解决mysql模糊查询索引失效问题的几种方法

我们在使用like %通配符时常常会引起索引失效的问题。这里，我们讨论一下like使用%的几种情况：下列例子用到的索引(VC_STUDENT_NAME) 一、like ‘xx...

99+

2024-04-02
mysql 联合索引生效的条件及索引失效的条件

目录1.联合索引失效的条件 2.索引失效的条件 1.联合索引失效的条件联合索引又叫复合索引。两个或更多个列上的索引被称作复合索引。对于复合索引：Mysql从左到右的使用索引中的...

99+

2024-04-02
Mysql索引失效的解决方法

小编给大家分享一下Mysql索引失效的解决方法，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！背景6千万数据量的数据表出现了一个满...

99+

2024-04-02
mysql索引失效的几种情况

常见情况：1、使用函数或运算；2、隐式类型转换；3、使用不等于（!=或）；4、使用like操作符，并以通配符开头；5、or条件；6、null值；7、索引选择性低；8、复合索引的最左前缀原...

99+

2024-02-22

mysql 索引失效 mysql索引隐式类型转换
MySql索引失效的示例分析

这篇文章主要介绍了MySql索引失效的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 1、使用MySq...

99+

2024-04-02
如何解决MySql整型索引和字符串索引失效或隐式转换问题

这篇文章主要为大家展示了“如何解决MySql整型索引和字符串索引失效或隐式转换问题”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“如何解决MySql整型索引和字符串索引失效或隐式转换问题”这篇文章...

99+

2023-06-25
浅谈MySQL的B树索引与索引优化小结

MySQL的MyISAM、InnoDB引擎默认均使用B+树索引（查询时都显示为“BTREE”），本文讨论两个问题：为什么MySQL等主流数据库选择B+树的索引结构？如何基于索引结构，理解常见的...

99+

2024-04-02
mysql引发索引失效的情况有哪些

这篇文章主要讲解了“mysql引发索引失效的情况有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“mysql引发索引失效的情况有哪些”吧！1、在查询条件中计算索引列的使用函数或操作。若已建...

99+

2023-06-20
mysql索引失效的现象有哪些

本篇内容介绍了“mysql索引失效的现象有哪些”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读...

99+

2024-04-02
MySQL索引失效的原因有哪些

MySQL索引失效的原因可能包括以下几点：数据量过大：当数据量过大时，即使使用了索引，也可能因为需要扫描大量数据而导致索引失效...

99+

2024-04-09

mysql