# 2759174行数据
SELECT COUNT(*) FROM tb_data t1;
*# 7262行数据
SELECT COUNT(*) FROM tb_task t2;
# 执行时间为44.88s
SELECT SQL_NO_CACHE t1.id FROM tb_data t1 WHERE t1.task_id IN (SELECT t2.id FROM tb_task t2);
# 执行时间为28.93s
SELECT SQL_NO_CACHE t1.id FROM tb_data t1 WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);
这里涉及到IN和EXISTS的区别。 如果你试图在网上找出答案，你会发现所有的博客都是写着： 如果两个表中一个表大，另一个是表小，那么IN适合于外表大而子查询表小的情况；EXISTS适合于外表小而子查询表大的情况。 但是，这个说法正确吗？继续往下看！！！
按照我上面测试的情况。 t1表有两百多万行数据，t2表只有7千行数据。它们关联关系为t1.task_id = t2.id，我在使用IN时，t2表是子查询表，并且是小表，按理来说在这种情况下使用IN应该是更加合理的方式。 然后实际情况是使用IN需要44.88s，使用EXISTS需要28.93s，这个是怎么回事？
EXISTS和IN的介绍
我们先对EXISTS和IN做一个简单的介绍。
exists
exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录行时（无论记录行是的多少，只要能返回），条件就为真，返回当前loop到的这条记录；反之，如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false
如下：
select * from user where exists (select 1);
对user表的记录逐条取出，由于子条件中的select 1永远能返回记录行，那么user表的所有记录都将被加入结果集，所以与select * from user;是一样的。
又如下：
select * from user where exists (select * from user where user_id = 0);
可以知道对user表进行loop时，检查条件语句(select * from user where user_id = 0),由于user_id永远不为0，所以条件语句永远返回空集，条件永远为false，那么user表的所有记录都将被丢弃。
总结：如果A表有n条记录，那么exists查询就是将这n条记录逐条取出，然后判断n遍exists条件。
in
in查询相当于多个or条件的叠加，这个比较好理解，比如下面的查询：
select * from user where user_id in (1, 2, 3);
等效于
select * from user where user_id = 1 or user_id = 2 or user_id = 3;
总结：in查询就是先将子查询条件的记录全都查出来，假设结果集为B，共有m条记录，然后再将子查询条件的结果集分解成m个，再进行m次查询。
使用上的区别
in查询的子条件返回结果必须只有一个字段，例如
select * from user where user_id in (select id from B);
不能是
select * from user where user_id in (select id, age from B);
而exists就没有这个限制。
EXISTS和IN的性能分析
为了便于分析，我把实际上的例子简化一下。
SELECT t1.id FROM tb_data t1 WHERE t1.task_id IN (SELECT t2.id FROM tb_task t2);
SELECT t1.id FROM tb_data t1 WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);
简化后：
# 查询1
SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
# 查询2
SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);
in
假设B表的所有id为(1,2,3)，查询1可以转换为： SELECT * FROM A WHERE A.id = 1 OR A.id = 2 OR A.id = 3; 这里主要是用到了A的索引，B表如何对查询影响不大。
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表如何对查询的效率影响不大。
实际情况
1）SELECT t1.id FROM tb_data t1 WHERE t1.task_id IN (SELECT t2.id FROM tb_task t2); 它使用的索引情况如下：
使用了t1（A）表索引
2）SELECT t1.id FROM tb_data t1 WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);
使用了t2（B）表索引
结论
MySQL中的in语句是把外表和内表作join连接，而exists语句是对外表作nest loop循环，每次loop循环再对内表进行查询。
通过以上分析，很容易得出下面的结论： 1、如果查询的两个表大小相当，那么用in和exists差别不大。 2、如果两个表中一个表大，另一个是表小，那么IN适合于外表大而子查询表小的情况。 3、如果两个表中一个表大，另一个是表小，EXISTS适合于外表小而子查询表大的情况。
SQL join原理
MySQL是只支持一种Join算法Nested-Loop Join(嵌套循环连接)，并不支持哈希连接和合并连接，不过在mysql中包含了多种变种，能够帮助MySQL提高join执行的效率。
1、Simple Nested-Loop Join
这个算法相对来说就是很简单了，从驱动表中取出R1匹配S表所有列，然后R2，R3,直到将R表中的所有数据匹配完，然后合并数据，可以看到这种算法要对S表进行RN次访问，虽然简单，但是相对来说开销还是太大了。
2、Index Nested-Loop Join
索引嵌套联系由于非驱动表上有索引，所以比较的时候不再需要一条条记录进行比较，而可以通过索引来减少比较，从而加速查询。这也就是平时我们在做关联查询的时候必须要求关联字段有索引的一个主要原因。
这种算法在链接查询的时候，驱动表会根据关联字段的索引进行查找，当在索引上找到了符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表。至于驱动表的选择，MySQL优化器一般情况下是会选择记录数少的作为驱动表，但是当SQL特别复杂的时候不排除会出现错误选择。
在索引嵌套链接的方式下，如果非驱动表的关联键是主键的话，这样来说性能就会非常的高，如果不是主键的话，关联起来如果返回的行数很多的话，效率就会特别的低，因为要多次的回表操作。先关联索引，然后根据二级索引的主键ID进行回表的操作。这样来说的话性能相对就会很差。
3、Block Nested-Loop Join
在有索引的情况下，MySQL会尝试去使用Index Nested-Loop Join算法，在有些情况下，可能Join的列就是没有索引，那么这时MySQL的选择绝对不会是最先介绍的Simple Nested-Loop Join算法，而是会优先使用Block Nested-Loop Join的算法。
Block Nested-Loop Join对比Simple Nested-Loop Join多了一个中间处理的过程，也就是join buffer，使用join buffer将驱动表的查询JOIN相关列都给缓冲到了JOIN BUFFER当中，然后批量与非驱动表进行比较，这也来实现的话，可以将多次比较合并到一次，降低了非驱动表的访问频率。也就是只需要访问一次S表。这样来说的话，就不会出现多次访问非驱动表的情况了，也只有这种情况下才会访问join buffer。
在MySQL当中，我们可以通过参数join_buffer_size来设置join buffer的值，然后再进行操作。默认情况下join_buffer_size=256K，在查找的时候MySQL会将所有的需要的列缓存到join buffer当中，包括select的列，而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。
