in和exists区别

EXISTS的执行流程       
select * from t1 where exists ( select null from t2 where y = x )
可以理解为:
   for x in ( select * from t1 )
   loop
      if ( exists ( select null from t2 where y = x.x )
      then
         OUTPUT THE RECORD
      end if
   end loop
对于in 和 exists的性能区别:
   如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。
   其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了
                           
另外IN时不对NULL进行处理
如:
select 1 from dual where null  in (0,1,2,null)

为空

 

2.NOT IN 与NOT EXISTS:       
NOT EXISTS的执行流程
select .....
  from rollup R
where not exists ( select 'Found' from title T
                             where R.source_id = T.Title_ID);
可以理解为:
for x in ( select * from rollup )
      loop
          if ( not exists ( that query ) ) then
                 OUTPUT
          end if;
       end;

注意:NOT EXISTS 与 NOT IN 不能完全互相替换,看具体的需求。如果选择的列可以为空,则不能被替换。

例如下面语句,看他们的区别:
select x,y from t;
x              y
------         ------
1              3
3        1
1        2
1        1
3        1
5
select * from t where  x not in (select y from t t2  )
no rows
       
select * from t where  not exists (select null from t t2
                                                  where t2.y=t.x )
x       y
------  ------
5       NULL
所以要具体需求来决定

对于not in 和 not exists的性能区别:
   not in 只有当子查询中,select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大,子查询中的表小但是记录多,则应当使用not in,并使用anti hash join.
   如果主查询表中记录少,子查询表中记录多,并有索引,可以使用not exists,另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null
NOT IN 在基于成本的应用中较好

比如:
select .....
from rollup R
where not exists ( select 'Found' from title T
                           where R.source_id = T.Title_ID);

改成(佳)

select ......
from title T, rollup R
where R.source_id = T.Title_id(+)
    and T.Title_id is null;
                                 
或者(佳)
sql> select /*+ HASH_AJ */ ...
        from rollup R
        where ource_id NOT IN ( select ource_id
                                               from title T
                                              where ource_id IS NOT NULL )

<think>好的,我现在要仔细分析用户关于MySQL中INEXISTS区别的问题。首先,我需要回忆这两个关键字的基本用法它们在SQL查询中的作用。 我记得IN用于子查询时,通常用来检查某个列的值是否存在于子查询返回的结果集中。例如,SELECT * FROM A WHERE id IN (SELECT id FROM B)。这种情况下,子查询会先执行,得到一个结果集,然后主查询再根据这个结果集进行过滤。而EXISTS则是用来测试子查询是否有返回任何行,如果有,则EXISTS返回TRUE,否则FALSE。比如,SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE A.id = B.id)。这里,EXISTS子查询通常会与外部查询的列相关联,形成相关子查询。 接下来,我需要考虑它们的执行机制有什么不同。IN的执行过程可能是先执行子查询,将结果缓存起来,然后主查询再去检查每个值是否在这个缓存的结果集中。当子查询的结果集很大时,这可能会导致性能问题,因为需要存储大量数据。而EXISTS则是对于外部查询的每一行,执行一次子查询,判断是否存在匹配的记录。如果外部查询的结果集很大,这可能会导致子查询被多次执行,影响效率。不过,如果子查询中的表能够有效利用索引,特别是关联字段上有索引的话,EXISTS可能会更快,因为它一旦找到匹配项就会停止搜索。 然后是关于NULL值的处理。记得在IN子句中,如果子查询返回NULL,那么IN的结果可能会受到影响,因为NULL任何值的比较都是未知的。例如,如果子查询返回的结果包含NULL,那么x IN (NULL, 1, 2)实际上等同于x=1 OR x=2,因为x=NULL的结果是UNKNOWN,不会被包含。而EXISTS则只关心是否有行存在,子查询中的NULL不会影响EXISTS的结果,只要至少有一行满足条件,EXISTS就返回TRUE。 接下来是适用场景的分析。当子查询结果集较小,而外部查询结果集较大时,IN可能更高效,因为子查询只需要执行一次,结果被缓存,之后外部查询的每条记录只需要在缓存中查找。相反,当外部查询结果集较小,而子查询结果集较大时,使用EXISTS可能更好,因为它可以利用索引,每次检查只执行必要的查找,避免处理整个子查询结果集。例如,如果外部查询的结果只有几行,而子查询的表有索引,那么EXISTS可能更快。 另外,还要考虑两个操作符在逻辑上的不同。IN是直接比较值,而EXISTS是检查是否存在满足条件的行。有时候两者可以互换,但具体要看查询的需求。例如,使用IN时子查询返回的是具体的值,而EXISTS的子查询通常使用关联条件来连接内外表,可能更灵活。 然后需要思考如何用例子来说明两者的区别。例如,当子查询包含重复值或NULL时,结果可能会有何不同。或者当使用关联子查询时,EXISTS的效率如何变化。此外,可能还需要讨论索引的影响,比如在子查询的关联字段上是否有索引,会显著影响EXISTS的性能。 可能还需要提到在MySQL中优化器如何处理这两个操作符。有时候优化器可能会将INEXISTS转换为相同的执行计划,特别是在简单的情况下。但在复杂的查询中,两者的执行计划可能不同,导致性能差异。因此,了解它们的底层机制有助于写出更高效的SQL语句。 最后,我需要总结这些点,确保结构清晰,分点列出区别,包括执行机制、NULL处理、适用场景、性能差异等。同时,要使用用户容易理解的语言,避免过于技术化的术语,但也要准确无误。可能还需要给出使用建议,帮助用户根据具体情况选择IN还是EXISTS。</think>MySQL中INEXISTS区别主要体现在以下五个方面,通过具体示例帮助你理解: --- ### 一、执行机制不同 1. **IN 子查询** - 先执行子查询,生成临时结果集 - 再用外层查询的字段与结果集逐个比对 ```sql SELECT * FROM users WHERE id IN (SELECT user_id FROM orders) ``` *执行顺序:先查出所有订单的user_id → 再比对users表* 2. **EXISTS 子查询** - 对外层查询的每一条记录,执行一次子查询 - 子查询通过关联条件与外部表连接 ```sql SELECT * FROM users u WHERE EXISTS ( SELECT 1 FROM orders WHERE user_id = u.id ) ``` *执行顺序:对每个用户 → 检查是否存在对应订单* --- ### 二、NULL值处理 ```sql SELECT * FROM table WHERE col IN (SELECT nullable_col FROM other_table) ``` - 如果子查询返回NULL,`col IN (NULL,1,2)` 等价于 `col=1 OR col=2` - 而EXISTS不关心具体值,只要子查询返回至少一行即为真 --- ### 三、性能差异场景 | 场景 | 推荐使用 | 原因 | |--------------------|--------|--------------------------------------------------------------------| | 外层表大,子查询结果集小 | IN | 子查询结果缓存后快速比对 | | 外层表小,子查询表大 | EXISTS | 通过索引快速定位(需关联字段有索引) | | 子查询需要关联外部表 | EXISTS | 天然支持关联查询,IN需通过JOIN实现 | --- ### 四、索引利用对比 ```sql -- EXISTS示例(高效场景) SELECT * FROM products p WHERE EXISTS ( SELECT 1 FROM inventory WHERE product_id = p.id AND quantity > 0 -- inventory表在product_id上有索引时效率高 ) -- 等效的IN写法(可能低效) SELECT * FROM products WHERE id IN ( SELECT product_id FROM inventory WHERE quantity > 0 -- 需要先执行整个子查询 ) ``` --- ### 五、使用建议 1. **优先EXISTS的情况** - 需要关联外部表的列时 - 子查询表有合适索引 - 外层结果集较小 2. **优先IN的情况** - 子查询结果集很小(<总数据量5%) - 不需要关联外部表 - 外层结果集很大且子查询可缓存 3. **特殊注意** - MySQL 8.0+ 对两者优化差距缩小 - 实际应通过`EXPLAIN`分析执行计划 - `NOT EXISTS`通常比`NOT IN`更高效(特别是涉及NULL时) --- **总结选择策略**: - 关联查询用EXISTS - 静态列表匹配用IN - 不确定时用`EXPLAIN`查看执行计划 - 大数据量时先测试两种写法的性能差异
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值