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

背景介绍

最近在写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),
unique key pid_idx (poetry_id) using btree,
key aid_idx (author_id) using btree
) engine=innodb auto_increment=291270 default charset=utf8mb4

create table t_author (
id int(15) not null auto_increment,
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
) 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，in 效率高于exists

原因分析

对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，exists效率高于 in。

原因分析

两者的索引使用情况跟第一次实验是一致的，唯一区别是子查询筛选结果集的大小不同，但实验结果已经跟第一次的不同了。这种情况下子查询结果集很大，我们看看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

可能原因：条件值大，查询越靠后，需要遍历的记录越多，造成最终消耗越多的时间。这个解释有待进一步验证后再补充。