피를 토하다: 인덱스 실패의 10가지 시나리오!

오늘 우리는 데이터베이스에 대한 주제를 계속하고 색인 생성과 관련된 문제에 대해 더 이야기하겠습니다. 색인 생성은 모두가 더 우려하는 공개 주제이고 실제로 많은 함정이 있기 때문입니다.

실제 업무에서 다음 두 가지 상황을 겪어보셨는지 모르겠습니다.

• 특정 필드에 인덱스를 추가한 것은 당연하지만 실제로는 적용되지 않습니다.

• 인덱스가 적용되는 경우도 있고 적용되지 않는 경우도 있습니다.

오늘은 이전에 함정에 빠졌거나 앞으로 함정에 빠지게 될 친구들에게 참고가 될 mysql 데이터베이스 인덱스 실패의 10가지 시나리오에 대해 이야기하겠습니다. 그림

1. 준비

소위 공허한 말, 이런 인덱스 실패 장면을 직접 버린다면 전혀 설득력이 없을 수도 있습니다.

그래서 저는 최대한 합리적이고 증거에 기반해 테이블과 데이터를 구축하고 그 효과를 단계별로 보여 주기로 결정했습니다.

이 글을 인내심있게 읽으시면 많은 것을 얻으실 수 있으실 거라 믿습니다.

1.1 사용자 테이블 만들기

id, 코드, 나이, 이름, 키 필드가 포함된 사용자 테이블을 만듭니다.

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

또한 3개의 인덱스가 생성되었습니다:

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

1.2 데이터 삽입

시연의 편의를 위해 특별히 사용자 테이블에 3개의 데이터를 삽입했습니다:

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

Stephen Chow와 Jay Chou는 나의 우상입니다. , 나는 한때 여기에서 나르시스트적이었고 그것들을 나와 함께 모았습니다. 하하하.

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 | 이 독보적인 인덱스 최적화의 검, 사용법을 정말로 알고 계십니까?"를 읽어보실 수 있습니다. 》에 좀 더 자세히 소개되어 있습니다.

2. 가장 왼쪽 일치 원칙이 충족되지 않습니다

코드, 연령, 이름의 세 가지 필드에 대한 공동 인덱스를 이미 구축했습니다: 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='周星驰';

실행 결과:

쿼리 조건의 원래 순서는 코드, 나이, 이름이지만 여기서는 코드와 이름만 깨졌고 age 필드가 누락된 경우 코드 필드의 인덱스도 사용할 수 있습니다.

이것을 보면 여러분이 똑똑하고 이런 패턴을 발견했는지 궁금합니다. 이 4개의 SQL에는 모두 코드 필드가 있는데, 이는 가장 왼쪽 필드인 인덱스 필드의 첫 번째 필드입니다. 이 필드가 존재하는 한 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. 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转换成哪种字符串，用哪个索引快速查值?

感兴趣的小伙伴可以再看看官方文档：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

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字段遵循了最左匹配原则，但由于一个字段是用的升序，另一个字段用的降序，最终会导致索引失效。

好了今天分享的内容就先到这里，我们下期再见。