数据库查询中的exists与in分析比较

最近在用Laravel的ORM写数据库查询时，用到了whereHas的写法，这个写法的背后实现的SQL语句就是select * from `table1` where exists(select * from `table2` where table1.id = table2.table1_id),于是就查清楚了一下exists的用法和特点，在这里记录一下。

exists的用法

以下图的两张表关系作为分析：

20170626149840899878645.jpg

exists表示存在，它常常和子查询配合使用。例如对于上图，写下下面的SQL语句

SELECT * FROM `user`
WHERE exists (SELECT * FROM `order` WHERE user.id = order.user_id)

在这里，exists用于检查查询子查询语句是否会至少返回一行数据，如果有返回数据，则返回值是true，反之是false。

放子查询返回为true时，则外层的查询语句将进行查询，反之，外层查询语句将不进行查询或者查不出任何记录。

因此：上面的SQL所实现的意义在于，搜索出所有下过单的会员。

exists和in的区别和使用场景

除了exitst，in的使用也可以实现上面语句的效果。如下：

SELECT * FROM `user` WHERE id in (SELECT user_id FROM `order`)

那么，它们之间有什么区别呢。

• in()语句只会执行一次，它查出order表中的所有user_id字段并且缓存起来，之后，检查user表的id是否和order表中的user_id相当，如果相等则加入结果期，直到遍历完user的所有记录。用程序来表示如下：

$result = [];
$users = "SELECT * FROM `user`";
$orders = "SELECT user_id FROM `order`";
for($i = 0;$i < $users.length;$i++){
    for($j = 0;$j < $orders.length;$j++){
        // 此过程为内存操作，不涉及数据库查询。
        if($users[$i].id == $orders[$j].user_id){
            $result[] = $users[$i];
            break;
        }
    }
}

从上面的程序可以明显的看出，当order表数据很大的时候不适合用in,因为最多会将order表数据全部遍历一遍。

如：user表有10000条记录,order表有1000000条记录,那么最多有可能遍历10000*1000000次,这样的话效率就很差了。

• 再看exists，它的查询过程类似如下：

$result = [];
$users = "SELECT * FROM `user`";
for($i=0;$i<$users.length;$i++){
    if(exists($users[$i].id)){// 执行SELECT * FROM `order` WHERE user.id = order.user_id
        $result[] = $users[$i];
    }
}

显而易见：当order表比user表大很多的时候，使用exists是再恰当不过了，它没有那么多遍历操作,只需要再执行一次查询就行。查询次数的多少完全取决于user表的记录条数。

但是：如果说user表有10000条记录,order表有100条记录,那么exists()还是执行10000次,反而不如使用in()遍历10000*100次,因为in()是在内存里遍历,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能比较大,而操作内存的话会比较快.

因此，可以得出总结：

若外层查询表小于子查询表，则用exists。

若外层查询表(记录条数很多)远大于子查询表，则考虑用in。