1 例子

有两个表需要关联查询，表的情况如下：

# 2759174行数据SELECTCOUNT(*)FROM tb_data t1;# 7262行数据SELECTCOUNT(*)FROM tb_task t2;# 执行时间为44.88sSELECT SQL_NO_CACHE t1.idFROM tb_data t1WHERE t1.task_idIN(SELECT t2.idFROM tb_task t2);# 执行时间为28.93sSELECT SQL_NO_CACHE t1.idFROM tb_data t1WHEREEXISTS(SELECT*FROM tb_task t2WHERE t1.task_id= t2.id);

有些地方会说：如果两个表中一个表大，另一个是表小，那么IN适合于外表大而子查询表小的情况；EXISTS适合于外表小而子查询表大的情况。
但是我们根据上面的例子可以发现并不满足这个规律。 t1表有两百多万行数据，t2表只有7千行数据。它们关联关系为t1.task_id =t2.id，我在使用IN时，t2表是子查询表，并且是小表，按理来说在这种情况下使用IN应该是更加合理的方式。
然后实际情况是使用IN需要44.88s，使用EXISTS需要28.93s

2 EXISTS和IN的介绍

2.1 exists

exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录行时（无论记录行是的多少，只要能返回），条件就为真，返回当前loop到的这条记录；反之，如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false
如下：

select*fromuserwhereexists(select1);

对user表的记录逐条取出，由于子条件中的select 1永远能返回记录行，那么user表的所有记录都将被加入结果集，所以与select * from user;是一样的。

又如下：

select*fromuserwhereexists(select*fromuserwhere user_id=0);

可以知道对user表进行loop时，检查条件语句(select * from user where user_id = 0),由于user_id永远不为0，所以条件语句永远返回空集，条件永远为false，那么user表的所有记录都将被丢弃。、

总结：如果A表有n条记录，那么exists查询就是将这n条记录逐条取出，然后判断n遍exists条件。

2.2 in

in查询相当于多个or条件的叠加，这个比较好理解，比如下面的查询：

select*fromuserwhere user_idin(1,2,3);

等效于

select*fromuserwhere user_id=1or user_id=2or user_id=3;

总结：in查询就是先将子查询条件的记录全都查出来，假设结果集为B，共有m条记录，然后再将子查询条件的结果集分解成m个，再进行m次查询。

2.3 使用上的区别

in查询的子条件返回结果必须只有一个字段，例如

select*fromuserwhere user_idin(select idfrom B);

不能是

select*fromuserwhere user_idin(select id, agefrom B);

而exists就没有这个限制。

3 EXISTS和IN的性能分析

为了便于分析，我把实际上的例子简化一下。
实际：

SELECT t1.idFROM tb_data t1WHERE t1.task_idIN(SELECT t2.idFROM tb_task t2);SELECT t1.idFROM tb_data t1WHEREEXISTS(SELECT*FROM tb_task t2WHERE t1.task_id= t2.id);

简化后：

SELECT*FROM AWHERE A.idIN(SELECT idFROM B);SELECT*FROM AWHEREEXISTS(SELECT*from BWHERE B.id= A.id);

（1） in
假设B表的所有id为(1,2,3)，查询1可以转换为：

SELECT*FROM AWHERE A.id=1OR A.id=2OR A.id=3;

这里主要是用到了A的索引，B表如何对查询影响不大。
（2）exists
查询2可以转化以下伪代码：

for(i=0; i<count(A); i++) {
    a= get_record(A, i);#从A表逐条获取记录if(B.id= a[id]) {#如果子条件成立
        result[]= a;
    }
}return result;

这里主要用到了B表的索引，A表如何对查询的效率影响不大。

（3）实际情况
1）SELECTt1.id FROM tb_data t1 WHERE t1.task_id IN (SELECTt2.id FROM tb_task t2);
它使用的索引情况如下：

使用了t1（A）表索引

2）SELECTt1.id FROM tb_data t1 WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id =t2.id);

使用了t2（B）表索引

4 总结

《高性能MySQL》书上说，MySQL会把in的查询语句改成exists再去执行（实际上我们在没有索引情况下，他们的执行过程确实是一致的）

在《MySQL技术内幕:SQL编程》这本书中说：确实有很多DBA认为EXISTS比IN的执行效率更高，可能是当时优化器还不是很稳定和足够优秀，但是目前绝大数的情况下，IN和EXISTS都具有相同的执行计划。

1）IN查询在内部表和外部表上都可以使用到索引。
2）Exists查询仅在内部表上可以使用到索引。
3）当子查询结果集很大，而外部表较小的时候，Exists的Block Nested Loop(Block 嵌套循环)的作用开始显现，并弥补外部表无法用到索引的缺陷，查询效率会优于IN。
4）当子查询结果集较小，而外部表很大的时候，Exists的Block嵌套循环优化效果不明显，IN 的外表索引优势占主要作用，此时IN的查询效率会优于Exists。
5）表的规模不是看内部表和外部表，而是外部表和子查询结果集。

参考：
https://cloud.tencent.com/developer/article/1144244
https://cloud.tencent.com/developer/article/1144253