Mysql中,exists与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)就可以了。这样就降低了内层循环的读取次数。