MySQL查询语句中的IN 和Exists 对比分析

共计 3158 个字符，预计需要花费 8 分钟才能阅读完成。

背景介绍

最近在写 SQL 语句时，对选择 IN 还是 Exists 犹豫不决，于是把两种方法的 SQL 都写出来对比一下执行效率，发现 IN 的查询效率比 Exists 高了很多，于是想当然的认为 IN 的效率比 Exists 好，但本着寻根究底的原则，我想知道这个结论是否适用所有场景，以及为什么会出现这个结果。

网上查了一下相关资料，大体可以归纳为：外部表小，内部表大时，适用 Exists；外部表大，内部表小时，适用 IN。那我就困惑了，因为我的 SQL 语句里面，外表只有 1W 级别的数据，内表有 30W 级别的数据，按网上的说法应该是 Exists 的效率会比 IN 高的，但我的结果刚好相反！

“没有调查就没有发言权”！于是我开始研究 IN 和 Exists 的实际执行过程，从实践的角度出发，在根本上去寻找原因，于是有了这篇博文分享。

实验数据

我的实验数据包括两张表：t_author 表 和 t_poetry 表。
对应表的数据量：
t_author 表，13355 条记录；
t_poetry 表，289917 条记录。
对应的表结构如下：
CREATE TABLE t_poetry (
id bigint(20) NOT NULL AUTO_INCREMENT,
poetry_id bigint(20) NOT NULL COMMENT ‘ 诗词 id’,
poetry_name varchar(200) NOT NULL COMMENT ‘ 诗词名称 ’,
<font color=red> author_id bigint(20) NOT NULL COMMENT ‘ 作者 id'</font>
PRIMARY KEY (id),
<font color=red>
UNIQUE KEY pid_idx (poetry_id) USING BTREE,
KEY aid_idx (author_id) USING BTREE</font>
) ENGINE=InnoDB AUTO_INCREMENT=291270 DEFAULT CHARSET=utf8mb4

CREATE TABLE t_author (
id int(15) NOT NULL AUTO_INCREMENT,
<font color=red> author_id bigint(20) NOT NULL,</font>
author_name varchar(32) NOT NULL,
dynasty varchar(16) NOT NULL,
poetry_num int(8) NOT NULL DEFAULT ‘0’
PRIMARY KEY (id),
<font color=red>UNIQUE KEY authorid_idx (author_id) USING BTREE</font>
) ENGINE=InnoDB AUTO_INCREMENT=13339 DEFAULT CHARSET=utf8mb4

执行计划分析

IN 执行过程

sql 示例：select * from tabA where tabA.x in (select x from tabB where y>0);
其执行计划：
（1）执行 tabB 表的子查询，得到结果集 B，可以使用到 tabB 表的索引 y；
（2）执行 tabA 表的查询，查询条件是 tabA.x 在结果集 B 里面，可以使用到 tabA 表的索引 x。

Exists 执行过程

sql 示例：select from tabA where exists (select from tabB where y>0);
其执行计划：
（1）先将 tabA 表所有记录取到。
（2）逐行针对 tabA 表的记录，去关联 tabB 表，判断 tabB 表的子查询是否有返回数据，5.5 之后的版本使用 Block Nested Loop(Block 嵌套循环)。
（3）如果子查询有返回数据，则将 tabA 当前记录返回到结果集。
tabA 相当于取全表数据遍历，tabB 可以使用到索引。

实验过程

实验针对相同结果集的 IN 和 Exists 的 SQL 语句进行分析。
包含 IN 的 SQL 语句：
select from t_author ta where author_id in
(select author_id from t_poetry tp where tp.poetry_id>3650);
包含 Exists 的 SQL 语句：
select from t_author ta where exists
(select * from t_poetry tp where tp.poetry_id>3650 and tp.author_id=ta.author_id);

第一次实验

数据情况

t_author 表，13355 条记录；t_poetry 表，子查询筛选结果集 where poetry_id>293650，121 条记录；

执行结果

使用 exists 耗时 0.94S，使用 in 耗时 0.03S，<font color=red>IN 效率高于 Exists</font>。

原因分析

对 t_poetry 表的子查询结果集很小，且两者在 t_poetry 表都能使用索引，对 t_poetry 子查询的消耗基本一致。两者区别在于，使用 in 时，t_author 表能使用索引:

使用 exists 时，t_author 表全表扫描:

在子查询结果集较小时，查询耗时主要表现在对 t_author 表的遍历上。

第二次实验

数据情况

t_author 表，13355 条记录；t_poetry 表，子查询筛选结果集 where poetry_id>3650，287838 条记录；

执行时间

使用 exists 耗时 0.12S，使用 in 耗时 0.48S，<font color=red>Exists IN</font>。

原因分析

两者的索引使用情况跟第一次实验是一致的，唯一区别是子查询筛选结果集的大小不同，但实验结果已经跟第一次的不同了。这种情况下子查询结果集很大，我们看看 MySQL 的查询计划：
使用 in 时，由于子查询结果集很大，对 t_author 和 t_poetry 表都接近于全表扫描，此时对 t_author 表的遍历耗时差异对整体效率影响可以忽略，执行计划里多了一行 <auto_key>，在接近全表扫描的情况下，mysql 优化器选择了 auto_key 来遍历 t_author 表：

使用 exists 时，数据量的变化没有带来执行计划的改变，但由于子查询结果集很大，5.5 以后的 MySQL 版本在 exists 匹配查询结果时使用的是 Block Nested-Loop（Block 嵌套循环，引入 join buffer，类似于缓存功能）开始对查询效率产生显著影响，尤其针对 <font color=red> 子查询结果集很大 </font> 的情况下能显著改善查询匹配效率：

实验结论

根据上述两个实验及实验结果，我们可以较清晰的理解 IN 和 Exists 的执行过程，并归纳出 IN 和 Exists 的适用场景：

• IN 查询在内部表和外部表上都可以使用到索引；
• Exists 查询仅在内部表上可以使用到索引；
• 当 子查询结果集 很大，而外部表较小的时候，Exists 的 Block Nested Loop(Block 嵌套循环)的作用开始显现，并弥补外部表无法用到索引的缺陷，查询效率会优于 IN。
• 当 子查询结果集 较小，而外部表很大的时候，Exists 的 Block 嵌套循环优化效果不明显，IN 的外表索引优势占主要作用，此时 IN 的查询效率会优于 Exists。
• 网上的说法不准确，即表的规模不是看内部表和外部表，而是外部表和子查询结果集。
• 最后一点，也是最重要的一点：世间没有绝对的真理，掌握事物的本质，针对不同的场景进行实践验证才是最可靠有效的方法。

实验过程中发现的问题补充

仅对不同数据集情况下的上述 exists 语句分析时发现，数据集越大，消耗的时间反而变小，觉得很奇怪。
具体查询条件为：
where tp.poetry_id>3650，耗时 0.13S
where tp.poetry_id>293650，耗时 0.46S
可能原因：条件值大，查询越靠后，需要遍历的记录越多，造成最终消耗越多的时间。这个解释有待进一步验证后再补充。