Mysql中，exist与in的执行流程

一、in执行流程

对于 in 查询来说，会先执行子查询，如上边的 t2 表，然后把查询得到的结果和外表 t1 做笛卡尔积，再通过条件进行筛选（这里的条件就是指 name 是否相等），把每个符合条件的数据都加入到结果集中。

select * from t1 where name in (select name from t2);

伪代码如下

for(x in A){
    for(y in B){
     if(condition is true) {result.add();}
    }
}

二、exists执行流程

对于 exists 来说，是先查询遍历外表 t1 ，然后每次遍历时，再检查在内表是否符合匹配条件，即检查是否存在 name 相等的数据。

select * from t1 where name exists (select 1 from t2);

伪代码如下

for(x in A){
  if(exists condition is true){result.add();}
}

三、join 的三种嵌套循环连接

1.简单嵌套循环连接，Simple Nested-Loop Join ，简称 SNLJ

join 即是 inner join ，内连接，它是一个笛卡尔积，即利用双层循环遍历两张表。

我们知道，一般在 sql 中都会以小表作为驱动表。所以，对于 A，B 两张表，若A的结果集较少，则把它放在外层循环，作为驱动表。自然，B 就在内层循环，作为被驱动表。

简单嵌套循环，就是最简单的一种情况，没有做任何优化。

因此，复杂度也是最高的，O(mn)。伪代码如下：

for(id1 in A){
    for(id2 in B){
        if(id1==id2){
            result.add();
        }
    }
}

2.索引嵌套循环连接，Index Nested-Loop Join ，简称 INLJ

看名字也能看出来了，这是通过索引进行匹配的。外层表直接和内层表的索引进行匹配，这样就不需要遍历整个内层表了。利用索引，减少了外层表和内层表的匹配次数。

所以，此种情况要求内层表的列要有索引。

伪代码如下：

for(id1 in A){
    if(id1 matched B.id){
        result.add();
    }
}

3、块索引嵌套连接，Block Nested-Loop Join ，简称 BNLJ

块索引嵌套连接，是通过缓存外层表的数据到 join buffer 中，然后 buffer 中的数据批量和内层表数据进行匹配，从而减少内层循环的次数。

以外层循环100次为例，正常情况下需要在内层循环读取外层数据100次。如果以每10条数据存入缓存buffer中，并传递给内层循环，则内层循环只需要读取10次(100/10)就可以了。这样就降低了内层循环的读取次数。