MySQL中in与exists的使用及区别介绍

站长资源 2024/11/24 佚名

2 0 1

先放一段代码

for(int i=0;i<1000;i++){
 for(int j=0;j<5;j++){
 System.out.println("hello");
 }
}

 for(int i=0;i<5;i++){
 for(int j=0;j<1000;j++){
 System.out.println("hello");
 }
}

分析以上代码可以看到两行代码除了循环的次序不一致意外，其他并无区别，在实际执行时两者所消耗的时间和空间应该也是一致的。但是这仅仅是在Java中，现在我们转化一下情景，最外层循环是数据库中的连接操作，内层循环为查找操作，那么现在两次的结果将相差巨大。

之所以出现这样的原因是数据库的特点决定的，数据库中相比较于查询操作而言，建立连接是更消耗资源的。第一段代码建立了1000次连接，每一次连接却只做了5次查询，显然是很浪费的。

因此在我们对数据库进行操作时需要遵循的操作应当是小表驱动大表(小的数据集驱动大的数据集)。

in与exists

表结构

tbl_emp为员工表，deptld为部门id。tbl_dept为部门表。员工表中含有客人，其deptld字段为-1

mysql> desc tbl_emp;
+--------+-------------+------+-----+---------+----------------+
| Field | Type  | Null | Key | Default | Extra   |
+--------+-------------+------+-----+---------+----------------+
| id  | int(11)  | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES |  | NULL |    |
| deptld | int(11)  | YES | MUL | NULL |    |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc tbl_dept;
+----------+-------------+------+-----+---------+----------------+
| Field | Type  | Null | Key | Default | Extra   |
+----------+-------------+------+-----+---------+----------------+
| id  | int(11)  | NO | PRI | NULL | auto_increment |
| deptName | varchar(30) | YES | MUL | NULL |    |
| locAdd | varchar(40) | YES |  | NULL |    |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

我们知道对于一个公司而言相对于部门来说员工数要多得多。现在我们有这样一个需求：查询属于这个公司的员工（不含来访客人），我们可以用以下代码解决

利用in

# 先查询部门表中所有的id，然后拿员工表中的deptld字段与之对比，若含有则保留。

mysql> select * from tbl_emp a where a.deptld in (select id from tbl_dept);

in关键字就像or的拼接，例如上述sql雨中子查询查出的结果为1，2，3。则sql语句等价于以下形式

mysql> select * from tbl_emp a where a.deptld=1 or a.deptld=2 or a.deptld=3

总的来说in关键字就是将子查询中的所有结果都查出来，假设结果集为B，共有m条记录，然后在将子查询条件的结果集分解成m个，再进行m次查询。可以看到这里主要是用到了A的索引，B表如何对查询影响不大

利用exists

mysql> select * from tbl_emp a where exists (select 1 from tbl_dept b where a.deptld = b.id );

exits：将主查询的数据放到子查询中做条件验证，根据验证结果（True或False）来判断是否保留主查询中的记录。

for (i = 0; i < count(A); i++) { //遍历A的总记录数
　　a = get_record(A, i); //从A表逐条获取记录
　　if (B.id = a[id]) //如果子条件成立
　　　　result[] = a;
}
return result;

可以看到：exists主要是用到了B表的索引，A表如何对查询的效率影响不大

结论

mysql> select * from tbl_emp a where a.deptld in (select id from tbl_dept);

若tbl_dept的记录数少余tbl_emp则使用in效率比较高

mysql> select * from tbl_emp a where exists (select 1 from tbl_dept b where a.deptld = b.id );

若tbl_dept的记录数多余tbl_emp则使用in效率比较高

下面给大家介绍IN与EXISTS的区别

1、IN查询分析

SELECT * FROM A WHERE id IN (SELECT id FROM B);

等价于：1、SELECT id FROM B ----->先执行in中的查询

2、SELECT * FROM A WHERE A.id = B.id

以上in()中的查询只执行一次，它查询出B中的所有的id并缓存起来，然后检查A表中查询出的id在缓存中是否存在，如果存在则将A的查询数据加入到结果集中，直到遍历完A表中所有的结果集为止。

以下用遍历结果集的方式来分析IN查询

通过以上程序可以看出，当B表的数据较大时不适合使用in()查询，因为它会将B表中的数据全部遍历一次

例如：

1、A表中有100条记录，B表中有1000条记录，那么最多可能遍历100*1000次，效率很差

2、A表中有1000条记录，B表中有100条记录，那么最多可遍历1000*100此，内循环次数减少，效率大大提升

结论：IN()查询适合B表数据比A表数据小的情况，IN()查询是从缓存中取数据

2、EXISTS查询分析

语法：SELECT 字段 FROM table WHERE EXISTS（subquery）;

SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE B.id = A.id);

以上查询等价于：

SELECT * FROM A;
SELECT I FROM B WHERE B.id = A.id;

EXISTS()查询会执行SELECT * FROM A查询，执行A.length次，并不会将EXISTS()查询结果结果进行缓存，因为EXISTS()查询返回一个布尔值true或flase，它只在乎EXISTS()的查询中是否有记录，与具体的结果集无关。

EXISTS()查询是将主查询的结果集放到子查询中做验证，根据验证结果是true或false来决定主查询数据结果是否得以保存。

总结

以上所述是小编给大家介绍的MySQL中in与exists的使用及区别介绍，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！

sql中in和exists,sql中exists与in区别

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！

评论“MySQL中in与exists的使用及区别介绍”

暂无评论...

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

暴雪近日发布了《魔兽世界》10.2.6 更新内容，新游玩模式《强袭风暴》即将于3月21 日在亚服上线，届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。

艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕，并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时，他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中，玩家将会进入一个全新的海盗主题大逃杀式限时活动，其中包含极高的风险和史诗级的奖励。
《强袭风暴》不是普通的战场，作为一个独立于主游戏之外的活动，玩家可以用大逃杀的风格来体验《魔兽世界》，不分职业、不分装备（除了你在赛局中捡到的），光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式，玩家在加入海盗主题的预赛大厅区域前，可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹，《巨龙崛起》和《魔兽世界：巫妖王之怒经典版》的玩家都可以获得奖励。

更新日志

2024年11月24日

MySQL中in与exists的使用及区别介绍

浅谈mysql使用limit分页优化方案的实现

MySQL对于各种锁的概念理解

评论“MySQL中in与exists的使用及区别介绍”

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

更新日志

友情链接