mysql优化：exists、 in、not exists、not in

1、exists与in，not exists与not in的区

-- in 
SELECT
 A.*
FROM
 A
WHERE
A.id
IN 
(SELECT B.id FROM B WHERE B.id = A.id)

-- exists
SELECT
 A.*
FROM
 A
WHERE
exists
(SELECT B.id FROM B WHERE B.id = A.id)

①in 与 exists 区别

当A表数据大于B表数据时，选择in比exists执行效率要高。

相反，A表数据小于B表数据时，选择exists比较高效

IN会走索引，但是当IN的取值范围较大时会导致索引失效，走全表扫描。

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

sql 如下，

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

伪代码如下：

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

这里的 condition 其实就是对比两张表中的 name 是否相同。

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

sql 如下，

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

伪代码如下：

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

对应于此例，就是从 id 为 1001 开始遍历 t1 表 ，然后遍历时检查 t2 中是否有相等的 name 。

如 id=1001时，张三存在于 t2 表中，则返回 true，把 t1 中张三的这条记录加入到结果集，继续下次循环。 id=1002 时，李四不在 t2 表中，则返回 false，不做任何操作，继续下次循环。直到遍历完整个 t1 表。

②not exists 与not in 区别

not in 会使索引失效，无论在哪种情况not exists 都比 not in 高效。