MySQL是如何优化in子查询的？

系统管理员 2024-02-20 11:45 61阅读 0赞

##  ##

**目录**

前言

一、普通in子查询

二、物化表

三、SQL优化

四、IN语句的优化方式

1. 使用子查询代替IN查询

2. 使用JOIN代替IN查询

3. 使用EXISTS代替IN查询

4. 使用索引优化IN查询

5. 优化查询语句

总结

--------------------

## 前言 ##

对于很多的开发小伙伴来说，在MySQL中进行in子查询是一个非常常见的操作。

虽然也有很多人说，尽量少用in子查询，in的数量过多会影响查询性能。

但其实MySQL做了不少的优化手段来保证in子查询的性能，大家也能在实际的业务中感受到in子查询的速度也没那么慢。

那今天就带大家了解一下，MySQL到底是怎么来优化in子查询的。

--------------------

## **一、**普通in子查询 ##

首先，我们看一下MySQL是如何执行一个普通的in子查询的。

以一个简单的子查询为例：

select  * from user where name in ('b','f','g')

用户表B+Tree 树的数据结构，name为二级索引。

![3b6165a9f88f492ca46c935ab6e4d5da.png][]

在这个图里，最上层的是根节点，中间的是非叶子节点，最下面的是叶子节点。

对于一个普通的二级索引来说，叶子节点存储的是索引key和主键id。

需要注意的是，二级索引在叶子节点中是按照key的顺序从小到大排序的，但是对应的主键id可不一定。

可能与大家想象的不同，MySQL在执行in子查询时，会把in语句中的条件当作一个个的区间，比如：

\['b','b'\]，\['f','f'\]，\['g','g'\]

然后MySQL在二级索引树上，会先查询\['b','b'\]这个区间，比如首先查询到第一个数据页中符合条件的第一条数据(b,2)，获取到主键id=2之后，去聚簇索引回表查询所需的数据（因为我们使用的select \*，需要获取到所有的列值）。

然后查询第三个数据页中符合条件的第三条数据(f，7)，

不断的重复上面的动作。。。。

最后获取到一个结果集，返回到Server，再由Server返回到客户端。

看到这里大家是否可以感觉到，这样查询数据也太麻烦了，特别是当in子查询的条件越来越多时，如何保证性能呢？

下面，我们一起来看一下，MySQL是如何优化in子查询的。

## 二、物化表 ##

首先，为了演示我们建两张表user和course，并建立两个二级索引idx\_user\_name和idx\_course\_name。

CREATE TABLE `user` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(45) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `name_UNIQUE` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

CREATE TABLE `course` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(45) NOT NULL,
      `user_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_userId` (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

下面以一个简单的子查询为例：在course表中查询name='中文'的user\_id，并作为user表id的查询条件。

select * from user where id in (select  user_id from course where name='中文')

对于一个这样普通的子查询来说，MySQL使用了一种叫作物化表的方式来提升性能。

什么意思呢？

就是将子查询的结果集去重后放入到一个临时表中，临时表的列就是子查询的结果集中的列。

去重的目的是为了让临时表尽可能的精简，因为在临时表中重复的列并没有什么意义。

当结果集比较小时，MySQL会为临时表使用memory引擎，并且为临时表中的列建立哈希索引。哈希索引的查询时间复杂度是O(1)，查询速度是非常快的。

但是如果结果集比较大时，MySQL就会将临时表定义为InnoDB类型表，并且建立B+树索引，就像一个普通的表一样使用。

话说回来，将子查询转换为临时表以后，其实查询就变成了两张表的连接查询，也就是两个表的内连接。

一旦转换为内连接就好办了，经典的“小表驱动大表”的优化准则就可以派上用场了。

我们看一下MySQL优化器对上面的SQL优化后的结果：

select * from user u inner join course c on u.id=c.user_id where c.name='中文'

可以看到，MySQL将其转换为了内连接，并且以user为驱动表，course为被驱动表的方式进行了查询。

由于user表和course表上都有索引，那么此时这个sql的执行速度还是相当可以的。

以下是SQL语句的执行计划

![50d8bd700bb644e39281f556c6bae667.png][]

## 三、SQL优化 ##

下图是生产环境的慢SQL截图

![2888f87a48cc41a2abdd0b3dae94c1ae.png][] 执行该SQL语句，耗时3.742923秒

select a.cid, a.aid, a.create_time, a.channel_type, a.user_id, a.event_type, a.ip from testing_put_user_event_type a 
    where a.create_time BETWEEN '2023-11-01 00:00:00' AND '2023-11-02 23:59:59' AND a.keep_type = 0 AND
    (a.user_id in (164238,423998,500373,548715,589846,616448,699795,920701,988476,993033,1215905,1540914)) and event_type=6

我们使用子查询代替IN查询，SQL耗时17ms，优化后的效果非常明显。

![0d90eaa5797548afa425f57eee6e138f.png][]

## 四、IN语句的优化方式 ##

### 1. 使用子查询代替IN查询 ###

IN查询在MySQL中是一个非常慢的查询方式，因为它需要对每个值进行比较。可以使用子查询来代替IN查询，子查询可以将结果缓存到内存中，从而提高查询效率。

### 2. 使用JOIN代替IN查询 ###

使用JOIN代替IN查询也是一种优化方式。JOIN可以将两个或多个表连接起来，使查询更加高效。在使用JOIN时，需要注意查询语句的编写，避免出现笛卡尔积等问题。

### 3. 使用EXISTS代替IN查询 ###

使用EXISTS代替IN查询也是一种优化方式。EXISTS只需要判断是否存在相关记录，而不需要返回具体的值，因此查询效率更高。

### 4. 使用索引优化IN查询 ###

在MySQL中，索引是一种非常重要的优化方式。可以使用索引来优化IN查询，在使用索引时，需要注意索引的创建和使用，避免出现索引失效等问题。

### 5. 优化查询语句 ###

在使用IN查询时，还可以通过优化查询语句来可以使用EXPLAIN命令来查看查询语句的执行计划，找出可能存在的性能瓶颈，从而进行优化。

## 总结 ##

无论MySQL采用了哪种优化方法，只要知道了其实现的大致原理，对于使用者来说，就有了对应的优化思路。

特别建议大家写完SQL以后，习惯性的使用explain分析一下是否命中了索引，扫描的行数是否过多。

只有不断的实操，优化SQL的能力才会不断提升。

[B+ Tree Visualization][B_ Tree Visualization]

[3b6165a9f88f492ca46c935ab6e4d5da.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/a1bc223784c74369aa916efaa3aad9c3.png
[50d8bd700bb644e39281f556c6bae667.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/acbd6f0f94e249fdb3d9d0cde6cc3274.png
[2888f87a48cc41a2abdd0b3dae94c1ae.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/d3e2c5c6bc22485ba23512687af66816.png
[0d90eaa5797548afa425f57eee6e138f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/fcdb1f2b176643958b2edc843bd8a624.png
[B_ Tree Visualization]: https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html