in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当,那么用in和exists差别不大。如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:
例如:表A(小表),表B(大表)
1:
select * from A where cc in (select cc from B)
效率低,用到了A表上cc列的索引;
select * from A where exists(select cc from B where cc=A.cc)
效率高,用到了B表上cc列的索引。
相反的
2:
select * from B where cc in (select cc from A)
效率高,用到了B表上cc列的索引;
select * from B where exists(select cc from A where cc=B.cc)
SELECT * FROM t_order torder WHERE EXISTS(SELECT 1 FROM t_customer tcustomer WHERE tcustomer.customer_no = torder.customer_no AND tcustomer.country = 'US');
首先要看是否使用了索引,这是前提;其次是要看表的大小,要小表驱动大表的才是优先考虑的,这种效率较高。小表驱动大表是通过减少表连接创建的次数,加快查询速度;小表可以粗略认为时数据行数小的表(实际应该是两个表按照各自的条件过滤,过滤完成之后,计算参与连接的各个字段的总数据量,数据量小是“小表”,应该作为驱动表)。比如下面两个SQL:select * from A where cc in (select cc from B); select * from A where exists (select cc from B where B.cc = A.cc)当A表小于B表时,使用exists,因为exists的实现相当于外部循环,逻辑类似于以下伪代码:for i in A
for j in B
if j.cc == i.cc
do ...当B表小于A表时,使用in,因为in的实现逻辑类似于以下伪代码:for i in B
for j in A
if j.cc == i.cc
do ...
例如:表A(小表),表B(大表)
1:
效率低,用到了A表上cc列的索引;
效率高,用到了B表上cc列的索引。
相反的
2:
效率高,用到了B表上cc列的索引;
效率低,用到了A表上cc列的索引。
EXISTS 用于测试子查询是否至少返回一行。如果子查询返回至少一行,条件为真。
在大多数情况下,EXISTS 的性能通常优于 IN,因为 EXISTS 只需要检查是否存在符合子查询条件的行,而 IN 需要获取子查询中的所有结果并与外部查询的值进行比较。
具体哪个更高效取决于多个因素,包括数据的分布、索引的存在以及查询的复杂性。通常,如果外部查询的数据集小而且可以有效利用索引,EXISTS 会更快。然而,如果外部查询的数据集大,IN 可能会更快,因为它不需要为外部查询的每一行检索子查询结果。为了准确比较,你可能需要在实际数据库上运行查询分析工具来查看哪个更快。
IN子查询
在MySQL中,当使用IN子查询时,主查询(外表)中的每一行都会与子查询(内表)的结果集进行比较。先执行子查询生成一个临时表,然后主查询取出对应的字段值,系统会遍历子查询结果集,检查这个字段值是否存在于子查询结果集中。如果存在,则该行满足条件,会被加入到最终的查询结果中。例如:
在这个例子中,对于t_order表中的每一行,MySQL会查看t_customer表中是否存在与其customer_no相匹配的记录。如果t_customer表中有任何行的customer_no与t_order表中当前行的customer_no相同,那么这一行就会被包含在最终查询结果中。IN子查询的效率通常在子查询结果集较小的情况下较高,因为它需要处理并可能缓存整个子查询结果。EXISTS子查询EXISTS子查询则是用于判断关联性,它并不关心子查询返回的具体数据值,而只关注是否存在匹配的行。对于主查询表中的每一行,执行内部的EXISTS子查询。当EXISTS子查询找到一行或多行符合WHERE条件的记录时,立即返回真(TRUE)。这个TRUE值会导致外层查询的那一行被纳入最终结果中,因为WHERE EXISTS条件为真。一旦EXISTS子查询找到匹配项,它就不需要继续查找剩余的记录了,即实现了所谓的“短路”或“早期终结”。例如:
在这个例子中,只要t_customer表中存在至少一条记录,其customer_no与t_order表中的当前行customer_no相符,MySQL就认为EXISTS条件为真,并将当前的t_order表行作为结果返回。无论t_customer表有多少其他相关记录,都不再影响此条目是否被选中。EXISTS在子查询表大但只需验证是否存在对应关系时更高效,它支持“短路”机制,一旦找到匹配项就结束子查询,不必遍历完整个子查询表。
结论
MySQL中的IN语句是把外表和内表作HASH连接,而EXISTS语句是对外表作LOOP循环,每次LOOP循环再对内表进行查询,单纯的理解EXISTS比IN语句的效率要高的说法其实是不准确的,要区分情景:如果查询的两不表大小相当,那么用EXISTS和IN差别不大。如果两个表中一个较小,一个是大表,则子查询表大的用EXISTS,子查询表小的用 IN。
select * from A where exists (select cc from B where B.cc = A.cc)当A表小于B表时,使用exists,因为exists的实现相当于外部循环,逻辑类似于以下伪代码:for i in A
for j in B
if j.cc == i.cc
do ...当B表小于A表时,使用in,因为in的实现逻辑类似于以下伪代码:for i in B
for j in A
if j.cc == i.cc
do ...