备案控制台
开发者社区 数据库 文章 正文

索引失效的十大杂症

2023-11-06 30
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群版 2核4GB 100GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 索引失效的十大杂症

背景：最近生产爆出一条慢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和场景，具体分析 ，而不是 墨守成规，认定哪个情景一定索引失效。


文章标签：
云数据库 RDS MySQL 版
索引
关系型数据库
MySQL
SQL
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
讓丄帝愛伱
目录
相关文章
源码星辰
|
2月前
|
SQL Oracle 关系型数据库
分析索引失效的几种情况
联合索引 is not null 只要在建立的索引列（不分先后）都会走, in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,或者=一个值； 当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,以上两种情况索引都会走。其他情况不会走。
源码星辰
66 1
源码星辰
|
2月前
|
SQL Oracle 关系型数据库
索引失效的情况分析
大家都知道，一条查询语句走了索引和没走索引的查询效率是非常大的，在我们建好了表，建好了索引后，但是一些不好的sql会导致我们的索引失效，下面介绍一下索引失效的几种情况
源码星辰
22 0
我是廖志伟
|
2月前
|
存储 关系型数据库 MySQL
提高查询性能的秘密：深入剖析聚集、辅助、覆盖和联合索引
提高查询性能的秘密：深入剖析聚集、辅助、覆盖和联合索引
我是廖志伟
49 0
菜菜的后端私房菜
|
2月前
|
SQL 存储 关系型数据库
深分页怎么导致索引失效了？提供6种优化的方案！
深分页怎么导致索引失效了？提供6种优化的方案！
菜菜的后端私房菜
18 1
讓丄帝愛伱
|
8月前
|
存储 SQL Oracle
Oracle优化避免索引失效
Oracle优化避免索引失效
讓丄帝愛伱
238 0
CBeann
|
数据库 索引
MysSQL索引会失效的几种情况分析
MysSQL索引会失效的几种情况分析
CBeann
131 0
MysSQL索引会失效的几种情况分析
一个风轻云淡
|
存储 SQL 搜索推荐
索引失效案例
索引失效案例
一个风轻云淡
127 0
索引失效案例
Jasonakeke
|
存储 关系型数据库 MySQL
33. 分别谈谈联合索引生效和失效的条件
33. 分别谈谈联合索引生效和失效的条件
Jasonakeke
179 0
33. 分别谈谈联合索引生效和失效的条件
我是咔咔
|
SQL 关系型数据库 MySQL
【MySQL优化】避免索引失效的十个关键点，你都知道那些？
【MySQL优化】避免索引失效的十个关键点，你都知道那些？
我是咔咔
364 1
游客swjl7c2lz5qeo
|
SQL 关系型数据库 MySQL
聊聊索引失效的10种场景，太坑了
聊聊索引失效的10种场景，太坑了
游客swjl7c2lz5qeo
126 0
聊聊索引失效的10种场景，太坑了