索引失效杂症

2024-05-12 16:08
文章标签 索引 失效 杂症

本文主要是介绍索引失效杂症，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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


作者：捡田螺的小男孩
链接：https://juejin.cn/post/6844904015872917517
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

这篇关于索引失效杂症的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/983096。

相关文章

MySQL进阶之路索引失效的11种情况详析

MySQL进阶之路索引失效的11种情况详析

《MySQL进阶之路索引失效的11种情况详析》：本文主要介绍MySQL查询优化中的11种常见情况,包括索引的使用和优化策略,通过这些策略,开发者可以显著提升查询性能,需要的朋友可以参考下... 目录前言图示1. 使用不等式操作符（!=, <, >）2. 使用 OR 连接多个条件3. 对索引字段进行计算操作4
阅读更多...
Goland debug失效详细解决步骤(合集)

Goland debug失效详细解决步骤(合集)

《Golanddebug失效详细解决步骤(合集)》今天用Goland开发时,打断点,以debug方式运行,发现程序并没有断住,程序跳过了断点,直接运行结束,网上搜寻了大量文章,最后得以解决,特此在这... 目录Bug：Goland debug失效详细解决步骤【合集】情况一：Go或Goland架构不对情况二：
阅读更多...
Java实现Elasticsearch查询当前索引全部数据的完整代码

Java实现Elasticsearch查询当前索引全部数据的完整代码

《Java实现Elasticsearch查询当前索引全部数据的完整代码》：本文主要介绍如何在Java中实现查询Elasticsearch索引中指定条件下的全部数据,通过设置滚动查询参数（scrol... 目录需求背景通常情况Java 实现查询 Elasticsearch 全部数据写在最后需求背景通常情况下
阅读更多...
Pandas中多重索引技巧的实现

Pandas中多重索引技巧的实现

《Pandas中多重索引技巧的实现》Pandas中的多重索引功能强大,适用于处理多维数据,本文就来介绍一下多重索引技巧,具有一定的参考价值,感兴趣的可以了解一下... 目录1.多重索引概述2.多重索引的基本操作2.1 选择和切片多重索引2.2 交换层级与重设索引3.多重索引的高级操作3.1 多重索引的分组聚
阅读更多...
mysql外键创建不成功/失效如何处理

mysql外键创建不成功/失效如何处理

《mysql外键创建不成功/失效如何处理》文章介绍了在MySQL5.5.40版本中,创建带有外键约束的`stu`和`grade`表时遇到的问题,发现`grade`表的`id`字段没有随着`studen... 当前mysql版本：SELECT VERSION();结果为：5.5.40。在复习mysql外键约
阅读更多...
Spring常见错误之Web嵌套对象校验失效解决办法

Spring常见错误之Web嵌套对象校验失效解决办法

《Spring常见错误之Web嵌套对象校验失效解决办法》：本文主要介绍Spring常见错误之Web嵌套对象校验失效解决的相关资料,通过在Phone对象上添加@Valid注解,问题得以解决,需要的朋... 目录问题复现案例解析问题修正总结  问题复现当开发一个学籍管理系统时，我们会提供了一个 API 接口去
阅读更多...
oracle数据库索引失效的问题及解决

oracle数据库索引失效的问题及解决

《oracle数据库索引失效的问题及解决》本文总结了在Oracle数据库中索引失效的一些常见场景,包括使用isnull、isnotnull、!=、、、函数处理、like前置%查询以及范围索引和等值索引... 目录oracle数据库索引失效问题场景环境索引失效情况及验证结论一结论二结论三结论四结论五总结ora
阅读更多...
Python中列表的高级索引技巧分享

Python中列表的高级索引技巧分享

《Python中列表的高级索引技巧分享》列表是Python中最常用的数据结构之一,它允许你存储多个元素,并且可以通过索引来访问这些元素,本文将带你深入了解Python列表的高级索引技巧,希望对... 目录1.基本索引2.切片3.负数索引切片4.步长5.多维列表6.列表解析7.切片赋值8.删除元素9.反转列表
阅读更多...
SpringBoot嵌套事务详解及失效解决方案

SpringBoot嵌套事务详解及失效解决方案

《SpringBoot嵌套事务详解及失效解决方案》在复杂的业务场景中,嵌套事务可以帮助我们更加精细地控制数据的一致性,然而,在SpringBoot中,如果嵌套事务的配置不当,可能会导致事务不生效的问题... 目录什么是嵌套事务？嵌套事务失效的原因核心问题：嵌套事务的解决方案方案一：将嵌套事务方法提取到独立类
阅读更多...
MySQL的索引失效的原因实例及解决方案

MySQL的索引失效的原因实例及解决方案

《MySQL的索引失效的原因实例及解决方案》这篇文章主要讨论了MySQL索引失效的常见原因及其解决方案,它涵盖了数据类型不匹配、隐式转换、函数或表达式、范围查询、LIKE查询、OR条件、全表扫描、索引... 目录1. 数据类型不匹配2. 隐式转换3. 函数或表达式4. 范围查询之后的列5. like 查询6
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2