索引失效的10中场景

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 索引失效的10中场景

前言

不知道你在实际工作中,有没有遇到过下面的这两种情况:

  • 明明在某个字段上加了索引,但实际上并没有生效。
  • 索引有时候生效了,有时候没有生效。

今天就跟大家一起聊聊,mysql数据库索引失效的10种场景,给曾经踩过坑,或者即将要踩坑的朋友们一个参考。

1. 准备工作

所谓空口无凭,如果我直接把索引失效的这些场景丢出来,可能没有任何说服力。

所以,我决定建表和造数据,给大家一步步演示效果,尽量做到有理有据。

我相信,如果大家耐心的看完这篇文章,一定会有很多收获的。

1.1 创建user表

创建一张user表,表中包含:idcodeagenameheight字段。

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int DEFAULT '0',
  `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `height` int DEFAULT '0',
  `address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code_age_name` (`code`,`age`,`name`),
  KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

此外,还创建了三个索引:

  • id: 数据库的主键
  • idx_code_age_name: 由code、age和name三个字段组成的联合索引。
  • idx_height:普通索引

1.2 插入数据

为了方便给大家做演示,我特意向user表中插入了3条数据:

INSERT INTO sue.user (id, code, age, name, height,address) VALUES (1, '101', 21, '周星驰', 175,'香港');
INSERT INTO sue.user (id, code, age, name, height,address) VALUES (2, '102', 18, '周杰伦', 173,'台湾');
INSERT INTO sue.user (id, code, age, name, height,address) VALUES (3, '103', 23, '苏三', 174,'成都');

周星驰和周杰伦是我偶像,在这里自恋了一次,把他们和我放到一起了。哈哈哈。

1.3 查看数据库版本

为了防止以后出现不必要的误会,在这里有必要查一下当前数据库的版本。不说版本就直接给结论,是耍流氓,哈哈哈。

select version();

查出当前的mysql版本号为:8.0.21

1.4 查看执行计划

在mysql中,如果你想查看某条sql语句是否使用了索引,或者已建好的索引是否失效,可以通过explain关键字,查看该sql语句的执行计划,来判断索引使用情况。

例如:

explain select * from user where id=1;

执行结果:

 

从图中可以看出,由于id字段是主键,该sql语句用到了主键索引

当然,如果你想更深入的了解explain关键字的用法,可以看看我的另一篇文章《explain | 索引优化的这把绝世好剑,你真的会用吗?-CSDN博客》,里面更为详细的介绍。

2. 不满足最左匹配原则

之前我已经给code、age和name这3个字段建好联合索引:idx_code_age_name。

该索引字段的顺序是:

  • code
  • age
  • name

如果在使用联合索引时,没注意最左前缀原则,很有可能导致索引失效喔,不信我们一起往下看。

2.1 哪些情况索引有效?

先看看哪些情况下,能走索引。

explain select * from user
where code='101';
explain select * from user
where code='101' and age=21 
explain select * from user
where code='101' and age=21 and name='周星驰';

执行结果:

 

上面三种情况,sql都能正常走索引。

其实还有一种比较特殊的场景:

explain select * from user
where code = '101'  and name='周星驰';

执行结果:

 

查询条件原本的顺序是:code、age、name,但这里只有code和name中间断层了,掉了age字段,这种情况也能走code字段上的索引。

看到这里,不知道聪明的你,有没有发现这样一个规律:

这4条sql中都有code字段,它是索引字段中的第一个字段,也就是最左边的字段。只要有这个字段在,该sql已经就能走索引。

这就是我们所说的最左匹配原则

2.2 哪些情况索引失效?

前面我已经介绍过,建立了联合索引后,在查询条件中有哪些情况索引是有效的。

接下来,我们重点看看哪些情况下索引会失效。

explain select * from user
where age=21;
explain select * from user
where name='周星驰';
explain select * from user
where age=21 and name='周星驰';

执行结果:

 

从图中看出这3种情况下索引确实失效了。

说明以上3种情况不满足最左匹配原则,说白了是因为查询条件中,没有包含给定字段最左边的索引字段,即字段code。

3. 使用了select *

在《阿里巴巴开发手册》中明确说过,查询sql中禁止使用select *

那么,你知道为什么吗?

废话不多说,按照国际惯例先上一条sql:

explain 
select * from user where name='苏三';

执行结果:

 

在该sql中用了select *,从执行结果看,走了全表扫描,没有用到任何索引,查询效率是非常低的。

如果查询的时候,只查我们真正需要的列,而不查所有列,结果会怎么样?

非常快速的将上面的sql改成只查了code和name列,太easy了:

explain 
select code,name from user 
where name='苏三';

执行结果:

 

从图中执行结果不难看出,该sql语句这次走了全索引扫描,比全表扫描效率更高。

其实这里用到了:覆盖索引

如果select语句中的查询列,都是索引列,那么这些列被称为覆盖索引。这种情况下,查询的相关字段都能走索引,索引查询效率相对来说更高一些。

而使用select *查询所有列的数据,大概率会查询非索引列的数据,非索引列不会走索引,查询效率非常低。

4. 索引列上有计算

介绍本章节内容前,先跟大家一起回顾一下,根据id查询数据的sql语句:

explain select * from user where id=1;

执行结果:

 

从图中可以看出,由于id字段是主键,该sql语句用到了主键索引

但如果id列上面有计算,比如:

explain select * from user where id+1=2;

执行结果:

 

从上图中的执行结果,能够非常清楚的看出,该id字段的主键索引,在有计算的情况下失效了。

5. 索引列用了函数

有时候我们在某条sql语句的查询条件中,需要使用函数,比如:截取某个字段的长度。

假如现在有个需求:想查出所有身高是17开头的人,如果sql语句写成这样:

explain select * from user  where height=17;

该sql语句确实用到了普通索引:

 

但该sql语句肯定是有问题的,因为它只能查出身高正好等于17的,但对于174这种情况,它没办法查出来。

为了满足上面的要求,我们需要把sql语句稍稍改造了一下:

explain select * from user  where SUBSTR(height,1,2)=17;

这时需要用到SUBSTR函数,用它截取了height字段的前面两位字符,从第一个字符开始。

执行结果:

 

你有没有发现,在使用该函数之后,该sql语句竟然走了全表扫描,索引失效了。

6. 字段类型不同

在sql语句中因为字段类型不同,而导致索引失效的问题,很容易遇到,可能是我们日常工作中最容易忽略的问题。

到底怎么回事呢?

请大家注意观察一下t_user表中的code字段,它是varchar字符类型的。

在sql语句中查询数据时,查询条件我们可以写成这样:

explain 
select * from user where code="101";

执行结果:

 

从上图中看到,该code字段走了索引。

温馨提醒一下,查询字符字段时,用双引号和单引号'都可以。

但如果你在写sql时,不小心把引号弄掉了,把sql语句变成了:

explain 
select * from user where code=101;

执行结果:

 

你会惊奇的发现,该sql语句竟然变成了全表扫描。因为少写了引号,这种小小的失误,竟然让code字段上的索引失效了。

这时你心里可能有一万个为什么,其中有一个肯定是:为什么索引会失效呢?

答:因为code字段的类型是varchar,而传参的类型是int,两种类型不同。

此外,还有一个有趣的现象,如果int类型的height字段,在查询时加了引号条件,却还可以走索引:

explain select * from user 
where height='175';

执行结果:

 

从图中看出该sql语句确实走了索引。int类型的参数,不管在查询时加没加引号,都能走索引。

这是变魔术吗?这不科学呀。

答:mysql发现如果是int类型字段作为查询条件时,它会自动将该字段的传参进行隐式转换,把字符串转换成int类型。

mysql会把上面列子中的字符串175,转换成数字175,所以仍然能走索引。

接下来,看一个更有趣的sql语句:

select 1 + '1';

它的执行结果是2,还是11呢?

好吧,不卖关子了,直接公布答案执行结果是2。

mysql自动把字符串1,转换成了int类型的1,然后变成了:1+1=2。

但如果你确实想拼接字符串该怎么办?

答:可以使用concat关键字。

具体拼接sql如下:

select concat(1,'1');

接下来,关键问题来了:为什么字符串类型的字段,传入了int类型的参数时索引会失效呢?

答:根据mysql官网上解释,字符串'1'、' 1 '、'1a'都能转换成int类型的1,也就是说可能会出现多个字符串,对应一个int类型参数的情况。那么,mysql怎么知道该把int类型的1转换成哪种字符串,用哪个索引快速查值?

感兴趣的小伙伴可以再看看官方文档:MySQL :: MySQL 8.0 Reference Manual :: 14.3 Type Conversion in Expression Evaluation

7. like左边包含%

模糊查询,在我们日常的工作中,使用频率还是比较高的。

比如现在有个需求:想查询姓李的同学有哪些?

使用like语句可以很快的实现:

select * from user where name like '李%';

但如果like用的不好,就可能会出现性能问题,因为有时候它的索引会失效。

不信,我们一起往下看。

目前like查询主要有三种情况:

  • like '%a'
  • like 'a%'
  • like '%a%'

假如现在有个需求:想查出所有code是10开头的用户。

这个需求太简单了吧,sql语句如下:

explain select * from user
where code like '10%';

执行结果:

 

图中看出这种%10右边时走了索引。

而如果把需求改了:想出现出所有code是1结尾的用户。

查询sql语句改为:

explain select * from user
where code like '%1';

执行结果:

 

从图中看出这种%1左边时,code字段上索引失效了,该sql变成了全表扫描。

此外,如果出现以下sql:

explain select * from user
where code like '%1%';

该sql语句的索引也会失效。

下面用一句话总结一下规律:当like语句中的%,出现在查询条件的左边时,索引会失效。

那么,为什么会出现这种现象呢?

答:其实很好理解,索引就像字典中的目录。一般目录是按字母或者拼音从小到大,从左到右排序,是有顺序的。

我们在查目录时,通常会先从左边第一个字母进行匹对,如果相同,再匹对左边第二个字母,如果再相同匹对其他的字母,以此类推。

通过这种方式我们能快速锁定一个具体的目录,或者缩小目录的范围。

但如果你硬要跟目录的设计反着来,先从字典目录右边匹配第一个字母,这画面你可以自行脑补一下,你眼中可能只剩下绝望了,哈哈。

8. 列对比

上面的内容都是常规需求,接下来,来点不一样的。

假如我们现在有这样一个需求:过滤出表中某两列值相同的记录。比如user表中id字段和height字段,查询出这两个字段中值相同的记录。

这个需求很简单,sql可以这样写:

explain select * from user 
where id=height

执行结果:

 

意不意外,惊不惊喜?索引失效了。

为什么会出现这种结果?

id字段本身是有主键索引的,同时height字段也建了普通索引的,并且两个字段都是int类型,类型是一样的。

但如果把两个单独建了索引的列,用来做列对比时索引会失效。

感兴趣的朋友可以找我私聊。

9. 使用or关键字

我们平时在写查询sql时,使用or关键字的场景非常多,但如果你稍不注意,就可能让已有的索引失效。

不信一起往下面看。

某天你遇到这样一个需求:想查一下id=1或者height=175的用户。

你三下五除二就把sql写好了:

explain select * from user 
where id=1 or height='175';

执行结果:

 

没错,这次确实走了索引,恭喜被你蒙对了,因为刚好id和height字段都建了索引。

但接下来的一个夜黑风高的晚上,需求改了:除了前面的查询条件之后,还想加一个address='成都'。

这还不简单,sql走起:

explain select * from user 
where id=1 or height='175' or address='成都';

执行结果:

 

结果悲剧了,之前的索引都失效了。

你可能一脸懵逼,为什么?我做了什么?

答:因为你最后加的address字段没有加索引,从而导致其他字段的索引都失效了。

注意:如果使用了or关键字,那么它前面和后面的字段都要加索引,不然所有的索引都会失效,这是一个大坑。

10. not in和not exists

在我们日常工作中用得也比较多的,还有范围查询,常见的有:

  • in
  • exists
  • not in
  • not exists
  • between and

今天重点聊聊前面四种。

10.1 in关键字

假如我们想查出height在某些范围之内的用户,这时sql语句可以这样写:

explain select * from user
where height in (173,174,175,176);

执行结果:

 

从图中可以看出,sql语句中用in关键字是走了索引的。

10.2 exists关键字

有时候使用in关键字时性能不好,这时就能用exists关键字优化sql了,该关键字能达到in关键字相同的效果:

explain select * from user  t1
where  exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)

执行结果:

 

从图中可以看出,用exists关键字同样走了索引。

10.3 not in关键字

上面演示的两个例子是正向的范围,即在某些范围之内。

那么反向的范围,即不在某些范围之内,能走索引不?

话不多说,先看看使用not in的情况:

explain select * from user
where height not in (173,174,175,176);

执行结果:

 

你没看错,索引失效了。

看如果现在需求改了:想查一下id不等于1、2、3的用户有哪些,这时sql语句可以改成这样:

explain select * from user
where id  not in (173,174,175,176);

执行结果:

 

你可能会惊奇的发现,主键字段中使用not in关键字查询数据范围,任然可以走索引。而普通索引字段使用了not in关键字查询数据范围,索引会失效。

10.4 not exists关键字

除此之外,如果sql语句中使用not exists时,索引也会失效。具体sql语句如下:

explain select * from user  t1
where  not exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)

执行结果:

 

从图中看出sql语句中使用not exists关键后,t1表走了全表扫描,并没有走索引。

11. order by的坑

在sql语句中,对查询结果进行排序是非常常见的需求,一般情况下我们用关键字:order by就能搞定。

但我始终觉得order by挺难用的,它跟where或者limit关键字有很多千丝万缕的联系,一不小心就会出问题。

Let go

11.1 哪些情况走索引?

首先当然要温柔一点,一起看看order by的哪些情况可以走索引。

我之前说过,在code、age和name这3个字段上,已经建了联合索引:idx_code_age_name。

11.1.1 满足最左匹配原则

order by后面的条件,也要遵循联合索引的最左匹配原则。具体有以下sql:

explain select * from user
order by code limit 100;
 
explain select * from user
order by code,age limit 100;
 
explain select * from user
order by code,age,name limit 100;

执行结果:

 

从图中看出这3条sql都能够正常走索引。

除了遵循最左匹配原则之外,有个非常关键的地方是,后面还是加了limit关键字,如果不加它索引会失效。

11.1.2 配合where一起使用

order by还能配合where一起遵循最左匹配原则。

explain select * from user
where code='101'
order by age;

执行结果:

 

code是联合索引的第一个字段,在where中使用了,而age是联合索引的第二个字段,在order by中接着使用。

假如中间断层了,sql语句变成这样,执行结果会是什么呢?

explain select * from user
where code='101'
order by name;

执行结果:

 

虽说name是联合索引的第三个字段,但根据最左匹配原则,该sql语句依然能走索引,因为最左边的第一个字段code,在where中使用了。只不过order by的时候,排序效率比较低,需要走一次filesort排序罢了。

11.1.3 相同的排序

order by后面如果包含了联合索引的多个排序字段,只要它们的排序规律是相同的(要么同时升序,要么同时降序),也可以走索引。

具体sql如下:

explain select * from user
order by code desc,age desc limit 100;

执行结果:

 

该示例中order by后面的code和age字段都用了降序,所以依然走了索引。

11.1.4 两者都有

如果某个联合索引字段,在where和order by中都有,结果会怎么样?

explain select * from user
where code='101'
order by code, name;

执行结果:

 

code字段在where和order by中都有,对于这种情况,从图中的结果看出,还是能走了索引的。

11.2 哪些情况不走索引?

前面介绍的都是正面的用法,是为了让大家更容易接受下面反面的用法。

好了,接下来,重点聊聊order by的哪些情况下不走索引?

11.2.1 没加where或limit

如果order by语句中没有加where或limit关键字,该sql语句将不会走索引。

explain select * from user
order by code, name;

执行结果:

 

从图中看出索引真的失效了。

11.2.2 对不同的索引做order by

前面介绍的基本都是联合索引,这一个索引的情况。但如果对多个索引进行order by,结果会怎么样呢?

explain select * from user
order by code, height limit 100;

执行结果:

 

从图中看出索引也失效了。

11.2.3 不满足最左匹配原则

前面已经介绍过,order by如果满足最左匹配原则,还是会走索引。下面看看,不满足最左匹配原则的情况:

explain select * from user
order by name limit 100;

执行结果:

 

name字段是联合索引的第三个字段,从图中看出如果order by不满足最左匹配原则,确实不会走索引。

11.2.4 不同的排序

前面已经介绍过,如果order by后面有一个联合索引的多个字段,它们具有相同排序规则,那么会走索引。

但如果它们有不同的排序规则呢?

explain select * from user
order by code asc,age desc limit 100;

执行结果:

 

从图中看出,尽管order by后面的code和age字段遵循了最左匹配原则,但由于一个字段是用的升序,另一个字段用的降序,最终会导致索引失效。

原文:

https://www.cnblogs.com/12lisu/p/15786013.html


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
12天前
|
SQL Oracle 关系型数据库
分析索引失效的几种情况
联合索引 is not null 只要在建立的索引列(不分先后)都会走, in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null(但必须在所有列 都满足is null的时候),或者=一个值; 当建立索引的第一位置是=一个值时,其他索引列可以是任何情况(包括is null =一个值),以上两种情况索引都会走。其他情况不会走。
51 1
|
12天前
|
SQL Oracle 关系型数据库
索引失效的情况分析
大家都知道,一条查询语句走了索引和没走索引的查询效率是非常大的,在我们建好了表,建好了索引后,但是一些不好的sql会导致我们的索引失效,下面介绍一下索引失效的几种情况
16 0
|
12天前
|
SQL 关系型数据库 MySQL
14. 什么情况下索引会失效 ?
了解 MySQL 索引失效的情况对优化 SQL 查询至关重要。避免在列上使用函数、运算、!=、not in、OR 和 %value% LIKE 操作,以保持索引有效性。使用组合索引代替多个单列索引,防止范围查询后的列无法使用索引。注意,NULL 值、列类型不匹配和隐式转换也可能导致索引失效。
20 0
|
6月前
|
存储 SQL Oracle
Oracle优化避免索引失效
Oracle优化避免索引失效
211 0
|
9月前
|
存储 关系型数据库 MySQL
教你优雅的实现索引失效
教你优雅的实现索引失效
61 0
|
10月前
|
关系型数据库 MySQL 索引
索引失效的情况
索引失效的情况
58 0
|
10月前
|
存储 SQL 关系型数据库
Mysql索引失效情况
官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往是存储在磁盘上的文件中的(可能存储在单独的索引文件中,也可能和数据一起存储在数据文件中)。 我们通常所说的索引,包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等,没有特别说明,默认都是使用B+树结构组织(多路搜索树,并不一定是二叉的)的索引。
55 0
|
10月前
|
关系型数据库 MySQL 索引
MySQL索引失效的场景
MySQL中索引可以失效的场景有很多,下面列举一些常见的场景,并提供相应的示例代码。
61 0
|
11月前
|
SQL 关系型数据库 MySQL
详解MySQL索引失效
B+树结构 索引失效的根本原因其实就是违反了B+树的结构特性,查找的时候没办法在B+树上继续走下去,所以首先我们来回顾一下B+树的数据结构。 如果对B树、B+树不熟悉的可以看一下博主之前的文章,详细介绍了这两种数据结构:数据结构(8)树形结构——B树、B+树(含完整建树过程)_b+树构造过程__BugMan的博客-CSDN博客 B+树是一棵N叉树,遵循每个节点遵循左<根<右,然后叶节点上是一条分支上的所有数据,且为了方便范围查询,叶子节点用指针连接。
96 0
|
数据库 索引
MysSQL索引会失效的几种情况分析
MysSQL索引会失效的几种情况分析
127 0
MysSQL索引会失效的几种情况分析