`

[转]exists真的就比in的效率高吗?

 
阅读更多

转:http://blog.csdn.net/jwisdom/article/details/1803577

http://www.cnblogs.com/ndxsdhy/archive/2010/12/11/1902904.html

 

系统要求进行SQL优化,对效率比较低的SQL进行优化,使其运行效率更高,其中要求对SQL中的部分in/not in修改为exists/not exists

 

修改方法如下:

in的SQL语句

SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime 
FROM tab_oa_pub WHERE is_check=1 and 
category_id in (select id from tab_oa_pub_cate where no='1') 
order by begintime desc

修改为exists的SQL语句
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime 
FROM tab_oa_pub WHERE is_check=1 and 
exists (select id from tab_oa_pub_cate where tab_oa_pub.category_id=convert(int,no) and no='1') 
order by begintime desc

 

分析一下exists真的就比in的效率高吗?

 

     我们先讨论IN和EXISTS。
     select * from t1 where x in ( select y from t2 )
     事实上可以理解为:
     select * 
       from t1, ( select distinct y from t2 ) t2
      where t1.x = t2.y;
     ——如果你有一定的SQL优化经验,从这句很自然的可以想到t2绝对不能是个大表,因为需要对t2进行全表的“唯一排序”,如果t2很大这个排序的性能是不可忍受的。但是t1可以很大,为什么呢?最通俗的理解就是因为t1.x=t2.y可以走索引。但这并不是一个很好的解释。试想,如果t1.x和t2.y都有索引,我们知道索引是种有序的结构,因此t1和t2之间最佳的方案是走merge join。另外,如果t2.y上有索引,对t2的排序性能也有很大提高。
     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
     ——这个更容易理解,t1永远是个表扫描!因此t1绝对不能是个大表,而t2可以很大,因为y=x.x可以走t2.y的索引。
     综合以上对IN/EXISTS的讨论,我们可以得出一个基本通用的结论:IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

我们要根据实际的情况做相应的优化,不能绝对的说谁的效率高谁的效率低,所有的事都是相对的 

--------------------------

in 和exists

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 whereexists(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 whereexists(select cc from A where cc=B.cc)

 

效率低,用到了A 表上cc 列的索引。

 

 

not in 和not exists

如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;

而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists 都比not in 要快。

分享到:
评论

相关推荐

    in和exists的区别与执行效率问题解析

    - 同样地,当`sysobjects`作为条件时,虽然`EXISTS`比`IN`多预读了15次,但总体上`EXISTS`依然表现出了更高的效率,这进一步证明了在大多数情况下`EXISTS`的优越性。 ### 使用场景建议 - **单表数据查询**:当仅...

    经典SQL查询总结关于Exists,not Exists.in ,not in效率的说明。

    ### 经典SQL查询总结关于Exists, not Exists, IN, not IN 效率的说明 在数据库查询操作中,存在着多种方法来实现相似的功能,但不同的实现方式在性能上可能会有显著差异。本文将深入探讨 SQL 中 `EXISTS`, `NOT ...

    “exists”和“in”的效率问题

    ### "Exists"与"In"的效率问题详解 #### 引言 在数据库查询语言SQL中,“Exists”与“In”是两种常用的子查询方法,它们在实际应用中各有优势与局限。本文将深入探讨这两种方法的工作原理、应用场景以及性能差异,...

    SQL里的EXISTS与IN

    通常情况下,`EXISTS` 比 `IN` 性能更好,特别是在子查询结果较大的情况下。这是因为`IN`需要先执行子查询获取所有结果,然后再与外部查询中的每一行进行比较;而`EXISTS`只需要找到一个匹配的结果即可返回`TRUE`,...

    简述Oracle中in和exists的不同

    一直以来,大家认为exists比in速度快,其实是不准确的。且看接下来的具体分析:in其实是将外表和内表进行hash join,exists是先对外表进行loop操作,然后每次loop后再对内表进行查询。 如果两张表大小差不多,那么...

    sql语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句

    很明显使用NOT EXISTS的效率高多了。 使用EXISTS和NOT EXISTS的优点 使用EXISTS和NOT EXISTS可以提高查询的效率,避免了使用NOT IN和IN的低效率。同时,EXISTS和NOT EXISTS也可以使查询语句变得更加简洁和易于理解...

    sql exists和not exists用法

    相较于`IN`、`NOT IN`等操作,`EXISTS`与`NOT EXISTS`具有更高的效率,尤其是在处理大型数据集时。 #### EXISTS 介绍 `EXISTS`关键字用于检查子查询是否至少返回一行数据。如果子查询返回至少一行数据,则`EXISTS`...

    SQL中EXISTS与IN

    - **效率优势**:`EXISTS`的一个重要优点是效率较高。因为`EXISTS`只需要判断子查询是否有结果,而不需要实际返回数据,所以在处理大型数据集时通常比`IN`更快。 **示例应用** 1. **相关子查询示例**: ```sql ...

    exists SQL用法详解、exists和not exists的常用示例

    这两个查询都用于去除`a`表中与`b`表关联的重复记录,但`EXISTS`版本在子查询满足条件时立即停止,因此效率更高。 总之,`EXISTS`和`NOT EXISTS`是SQL中强大的工具,它们用于基于子查询是否有返回结果来决定主查询...

    mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录

    MySQL中的`NOT IN`, `LEFT JOIN`, `IS NULL`, 和 `NOT EXISTS` 是四种不同的SQL查询方式,它们在特定情况下可以实现相似的功能,但实际执行效率可能会有很大差异。本文主要探讨这四种方法在处理大数据量时的性能表现...

    oracle数据库关于exists使用

    - `EXISTS` 主要用于检测子查询是否有结果返回,通常效率较高,因为一旦找到匹配项就会停止搜索。 - `IN` 用于测试主查询中的值是否存在于子查询结果集中。在某些情况下,它的性能可能不如 `EXISTS`。 **2.2 Exists...

    搞懂mysql的exists

    这种特性使得`EXISTS`在处理大数据量时效率较高,因为它一旦找到匹配的行就会停止执行,不需要获取所有数据。 三、`EXISTS`与`IN`的比较 1. `IN`通常用于比较单个列值是否在某个列表中,例如: ```sql SELECT * ...

    SQL复习-EXISTS谓词

    反之,如果子查询返回很多行,EXISTS通常效率更高。 - EXISTS通常更适合于不关心具体匹配值,只关心是否有匹配的情况。 2. EXISTS vs JOIN: - JOIN操作符用于合并两个或多个表的数据,而EXISTS则不涉及实际的...

    SQL SERVER中关于exists 和 in的简单分析

    `NOT EXISTS` 可以利用索引进行优化,因为它仅需知道子查询是否有匹配项,一旦找到,就不再继续检查其他行,因此在大多数情况下,对于大数据集,`NOT EXISTS` 的效率较高。 2. **IN 子句** `IN` 子句用于判断某个...

    关于in and inner/outer join and exists

    如果子查询返回至少一行数据,`EXISTS` 就为真,否则为假。它通常与 NOT EXISTS 结合使用,用于优化查询性能。 ```sql SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.common_column = ...

    in exist not_in

    因此,在大多数情况下,NOT EXISTS 语句的效率比 NOT IN 语句高。 例如,如果我们有两个表 A 和 B,我们可以使用以下语句: SELECT * FROM A WHERE cc NOT IN (SELECT cc FROM B) 这条语句的效率可能不高,因为它...

    exists的使用(查询选择了所有课程的学生信息)归纳.pdf

    Exists 语句的效率取决于主查询和子查询的大小,如果主查询的结果集小于子查询的结果集,那么 EXISTS 语句的效率将很高。反之,如果主查询的结果集大于子查询的结果集,那么 IN 语句的效率将更高。 在实际应用中,...

    SQL查询中in和exists的区别分析

    反之,如果B表的记录数远大于A表,`EXISTS` 的效率更高,因为它避免了大量不必要的比较。然而,这并不是绝对的,实际性能还取决于表结构、索引的存在以及数据库的优化策略。 在插入记录时,为了避免插入重复数据,...

    影响sql语句查询效率的因素

    通过合理处理NULL值、精确使用比较运算符、明智选择LIKE语句、谨慎使用`ORDER BY`子句以及优选`NOT EXISTS`而非`NOT IN`,可以显著提升查询效率,从而改善整体系统性能。在实践中,持续监控和调整查询策略,结合索引...

Global site tag (gtag.js) - Google Analytics