由Laravel 的 whereHas 引申出 exists 与 in 的区别

sanlanlan 2019-7-22 标签: mysql 浏览:340 评论:0


lavavel 中 whereHas 表示 关联关系上筛选,找出符合条件的,解析出来的SQL 是 exists.

如下实验: 
 
CREATE TABLE `article` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `user_id` int(11) NOT NULL, 
  PRIMARY KEY (`id`), 
  KEY `user_id` (`user_id`) 
)
 
CREATE TABLE `user` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `name` varchar(50) NOT NULL, 
  PRIMARY KEY (`id`) 

 

 
//In关键字原理 
SELECT * FROM `user`   
    WHERE id in (SELECT user_id FROM `article`) 
 
in()语句只会执行一次,它查出article表中的所有user_id字段并且缓存起来,之后,检查user表的id是否和article表中的user_id相等,如果相等则加入结果期,直到遍历完user的所有记录。 
 
in的查询过程类似于以下过程 
 
$result = []; 
$users = "SELECT * FROM `user`"; 
$articles = "SELECT user_id FROM `article`"; 
for($i = 0;$i < $users.length;$i++){ 
    for($j = 0;$j < $articles.length;$j++){ 
        // 在内存中操作 
        if($users[$i].id == $articles[$j].user_id){ 
            $result[] = $users[$i]; 
            break; 
        } 
    } 
 

 
可以看出,当article表数据很大的时候不适合用in,因为它最多会将article表数据全部遍历一次。 
 
如:user表有10000条记录,article表有1000000条记录,那么最多有可能遍历10000*1000000次,效率很差. 
 
再如:user表有10000条记录,article表有100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少,效率大大提升. 
 
//exists关键字原理 
SELECT * FROM `user`  
    WHERE exists (SELECT * FROM `article` WHERE user.id = article.user_id) 
 
在这里,exists语句会执行user.length次,它并不会去缓存exists的结果集,因为这个结果集并不重要,你只需要返回真假即可。 
 
exists的查询过程类似于以下过程: 

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

 
可以看出,当article表比user表大很多的时候,使用exists是再恰当不过了,它没有那么多遍历操作,只需要再执行一次查询就行。 
 
如:user表有10000条记录,article表有1000000条记录,那么exists()会执行10000次去判断user表中的id是否与article表中的user_id相等. 
 
如:user表有10000条记录,article表有100000000条记录,那么exists()还是执行10000次,因为它只执行user.length次,可见B表数据越多,越适合exists()发挥效果. 
 
但是:user表有10000条记录,article表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快. 
 
 
总结:“外层查询表小于子查询表,则用exists,外层查询表大于子查询表,则用in,如果外层和子查询表差不多,则爱用哪个用哪个。” 
总结:in(子表) 子表数据大于父表,不适合用in,用exists 

本文相关标签: mysql

发表评论: