`

in和exists区别

阅读更多
引子

in和exists的讨论从未间断过。之前有“今年是龙年大哥”的有数据有真相的测试博文,现在有程序员老鸟写sql语句的经验之谈上的疯狂讨论。关于exists和in,就是很少人站出来,直白地分析二者本质上的差别,这方面的文章大都是用晦涩的文字表述,或者直接给结论——什么情况下用exists,什么情况下用in,而不给出原理。结果时至今日,还有许多人认为exists一定比in性能高。下面鄙人用JAVA的伪代码,从理论上分析exists和in的时间复杂度

 

 

学生信息表(student_id 学生id, name 学生名称)

student(student_id,name) 

 

学生总分表

score(student_id,total)

 

现在查询出总分(total)超过90分的学生信息。

 

一、粗略的时间复杂度估算

 

1 exists方式

select * from student a where exists (select 1 from score b where b.total>90 and b.student_id = a.student_id);

 

Java代码 复制代码 收藏代码
  1. List<Map<String,String>> studentList = select * from student ;   
  2. for(i=0;i<studentList.size();i++){   
  3.     String _student_id = studentList.get(i).get("student_id");   
  4.     if(exists("select 1 from score  where total>90 and student_id = " + _student_id )){//建立有索引,这执行很快,O(1)时间   
  5.         studentRow = studentList.get(i)   
  6.         println(studentList.get(i));   
  7.     }   
  8. }  
List<Map<String,String>> studentList = select * from student ;
for(i=0;i<studentList.size();i++){
	String _student_id = studentList.get(i).get("student_id");
	if(exists("select 1 from score  where total>90 and student_id = " + _student_id )){//建立有索引,这执行很快,O(1)时间
		studentRow = studentList.get(i)
		println(studentList.get(i));
	}
}
 

 

时间复杂度为studentList.size() * 1

 

2 in方式

select * from student where student_id in (select student_id from score where total>90);

 

Java代码 复制代码 收藏代码
  1. List<Map<String,String>> scoreList = select student_id from score where total>90;   
  2. for(i=0;i<scoreList.size();i++){   
  3.     String _student_id = scoreList.get(i).get("student_id ");   
  4.     String studentRow = select * from student where studentId=_student_id;//建立有索引,这执行很快O(1)时间   
  5.     if(null != studentRow {   
  6.         println(studentRow);   
  7.     }   
  8. }  
List<Map<String,String>> scoreList = select student_id from score where total>90;
for(i=0;i<scoreList.size();i++){
	String _student_id = scoreList.get(i).get("student_id ");
	String studentRow = select * from student where studentId=_student_id;//建立有索引,这执行很快O(1)时间
	if(null != studentRow {
		println(studentRow);
	}
}
 

 

时间复杂度为scoreList.size() * 1

 

根据时间复杂度,

exists的耗费的时间,与主表student的记录数成正比,student 表越大,exists耗费时间越长;

in耗费的时间,与子查询的记录数成正比,记录数越多,in耗费时间越长。

 

也就是说,理论上,注意是理论上,

如果子查询的结果集很大,即是scoreList.size()很大,可能就不适合用in。

如果主查询的表记录数很大,即使studentList.size()很大,而子查询的结果很小,可能就不适合用exists。

对比子查询结果集的大小scoreList.size()和主表student表的大小studentList.size(),相信大家能比较简单地对in和exists做出初步选择

 

二、 细致的时间复杂度估算

上面的伪代码是粗略的估算。这里说细致一些。

 

1. 上面的两段伪代码中O(1)时间的部分,因为实际情况中未必使用到索引,所以未必为O(1)。

 

2. exists伪代码的第一句List<Map<String,String>> studentList = select * from student ;必然是全表扫描,算上这一句的,exists伪代码的时间复杂度就是,

studentList.size() * 1+studentTable.size() = 2*studentTable.size().

 

in伪代码的第一句,List<Map<String,String>> scoreList = select student_id from score where score>90;实际情况中,子查询未必是全表扫描。

如果是子查询是全表扫描,那么in的时间复杂度为

scoreList.size() * 1+scoreTable.size() 

如果使用到索引,不是全表扫描,那么in的时间复杂度为

scoreList.size() *1 +  scoreList.size() = 2*scoreList.size()

 

3. 综合1,2

exists:

studentTable.size() * Time(一条exists语句的执行时间)+studentTable.size()*Time(顺序扫描出一条记录的时间)

注释:studentTable就是主表。

 

in(子查询索引扫描):

scoreList.size() *Time(一条select语句的执行时间) +  scoreList.size()*Time(索引扫描出一条记录的时间)

注释: scoreList就是子查询的结果集。一条select 语句就是主表做in判断的select语句

 

Java代码 复制代码 收藏代码
  1. select * from student where studentId=_student_id  
 select * from student where studentId=_student_id

in(子查询全表扫描):

 

scoreList.size() *Time(一条select语句的执行时间) +  scoreTable.size()*Time(顺序扫描出一条记录的时间)

4 简化

现在简化对比in 和 exists的时间复杂度,二者的表达式有乘法和加法,我们只保留乘法。

Time_Exists = studentTable.size() * Time(一条exists语句的执行时间)

Time_IN = scoreList.size() *Time(一条select语句的执行时间) 

通常情况下,我们不允许TABLE ACCESS FULL的出现,该加索引的地方加索引,因此进一步简化为

Time_Exists = studentTable.size(),即是主表大小

Time_IN = scoreList.size() ,即是子查询结果集大小

 

数据量大,在决定该使用exsits和in的时候,我们只需要根据主表记录数和子查询结果集记录数就可做出初步选择。主表记录数多,我们就该有限不考虑用exists;子查询结果集记录数多,我们就该优先不考虑用in。如果子查询结果多,主表记录数多,用哪个呢?那就看实际数据了,要测试具体的时间。

 

5 结论

显然,细致分析之后,我们不能很快就下结论孰快孰慢了,索引的情况增加了分析的步骤。特别地,如果in伪代码中每条语句都用到了索引,子查询结果集合很小,另一方面主查询表很大,那么我们可以马上确定用in了。觉得exists一定比in快的同学,现在需要思考下了。

 

三、结论

实际上,一切还是看具体的存储过程以及看测试结果。理论和实际总会有差距,数据量,索引,硬件,ORACLE版本等等都会对结果产生影响。我们要具体问题具体分析。首先,我们可以套用上面两段伪代码去做估算,某些情况下还是可以估算得出来的孰快孰慢。其次,如果数据量大的话,就必须看执行计划,进一步,如果可以的话,就直接执行sql语句查看耗费时间。有时候执行计划还真的对EXISTS,IN有区别对待,这时候估算的思想就要用上。

 

我建议大家不要去纠结in、exists究竟用谁好。数据量不大,in、exists根本无区别,数据量大的时候,你说能不去看看执行计划吗?

 

值得注意的是,据说oracle11g在CBO的情况下,ORACLE会根据数据,对IN,EXISTS做出最佳的选择,而不管你写SQL是IN或者EXISTS。细心想想这也是合理的,IN,EXISTS所表达出的要做的事情是一样的,数据库为什么要区分对待呢?性能的问题交给数据库自己判断好了,不要麻烦开发人员。这也是我建议大家不要纠结in和exists区别的一个原因。

 

四、后记

后面我看到了一篇文章,同样比较简单易明,应该描述得比我准确,摘录下来,与大家共享 :)

from: 

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:953229842074

分享到:
评论

相关推荐

    in和exists的区别

    在Oracle数据库中,"IN"和"EXISTS"都是用于查询某个集合的元素是否存在于另一个集合中的关键字。然而,它们在处理数据时的效率和适用场景有所不同,这主要取决于涉及的数据量以及表之间的关联。 首先,让我们来看看...

    sql in,exists,not in,not exists区别

    IN、EXISTS、NOT IN、NOT EXISTS 是 SQL 中四种常用的条件判断运算符,它们之间的区别主要体现在使用场景、执行效率和语法结构上。 IN IN 是一种条件判断运算符,用于判断某个值是否存在于一个列表中。其基本语法...

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

    标题和描述均聚焦于SQL查询语句中"IN"与"EXISTS"的区别及执行效率问题,这是一个在数据库操作中非常关键的话题,尤其对于优化查询性能有着不可忽视的作用。下面,我们将深入探讨这两种语句的不同之处及其对执行效率...

    in和exists性能解析

    在数据库查询语言中,`IN` 和 `EXISTS` 子句是两种常见的用于关联两个表的方法,它们各自有其独特的性能特点和适用场景。本文将深入解析Oracle中`IN`与`EXISTS`的性能差异,以及如何根据具体需求选择最合适的查询...

    简述Oracle中in和exists的不同

    且看接下来的具体分析:in其实是将外表和内表进行hash join,exists是先对外表进行loop操作,然后每次loop后再对内表进行查询。 如果两张表大小差不多,那么exists和in的效率差不多。 例如: 一张大表为A,一张小表B...

    oracle中exists_和in的效率问题详解

    Oracle 中 EXISTS 和 IN 的效率问题详解 EXISTS 和 IN 都是 Oracle 中的集合操作符,但它们在使用和执行效率上有所不同。本文将深入探讨 EXISTS 和 IN 的使用场景、执行机制和效率问题。 EXISTS 的使用场景和机制 ...

    SQL里的EXISTS与IN

    根据给定的信息,本文将详细解析`EXISTS`与`IN`的区别以及如何在SQL优化过程中将`IN`转换为`EXISTS`来提高查询性能。 ### SQL优化需求背景 在系统开发与维护的过程中,经常会出现SQL查询效率低下、响应时间过长等...

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

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

    SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句.txt

    SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句.txt欢迎下载!

    PostgreSQL IN vs EXISTS vs ANYALL vs JOIN性能分析

    PostgreSQL作为一种强大的开源关系数据库系统,它支持多种SQL操作,其中包括IN、EXISTS、ANY/ALL和JOIN等操作符。这些操作符在不同的业务场景下有着不同的表现和性能影响。在实际的数据库操作中,选择合适的操作符是...

    sql case when exists not exists in not in

    在SQL查询中,`CASE WHEN`、`EXISTS`、`NOT EXISTS`以及`IN`和`NOT IN`是常用的操作符,它们用于处理复杂的条件判断和数据筛选。这些概念对于理解和编写高效的SQL语句至关重要,尤其是在数据分析和数据库管理中。 `...

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

    - 在某些情况下,可以考虑使用 `EXISTS` 或 `NOT EXISTS` 来替代 `IN` 和 `NOT IN`,以提高查询效率。 #### 三、左连接、右连接与全连接 除了上述几种查询方式之外,SQL 还提供了不同的连接类型来处理不同情况下...

    in与exists之争(11g).sql

    in与exists之争(11g).sql

    sql exists和not exists用法

    这是因为`IN`和`NOT IN`需要将主查询中的每一行都与子查询结果进行比较,而`EXISTS`和`NOT EXISTS`则只需判断子查询是否返回至少一行数据即可,无需获取所有数据行。 **性能对比:** - **IN 和 NOT IN**:需要将主...

    EXISTS_和_NOT_EXISTS

    - 效率:在某些情况下,`EXISTS` 和 `NOT EXISTS` 可能会提供相似的性能,但通常 `IN` 和 `NOT IN` 比较操作符在处理大量数据时更有效。然而,当涉及到子查询时,`EXISTS` 和 `NOT EXISTS` 可能更合适,因为它们只...

    MySQL中的in,exists,not in,not exists查询过程对比及结论

    MySQL优化之in,exists,not in,not exists的区别in与existsin查询过程结论:exists查询过程:结论:not in与not existsnot in查询过程:结论:not exists查询过程:结论: 首先我们使用两个用户表作为实例 insert ...

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

    在查询中,我们可以使用EXISTS和NOT EXISTS来代替IN和NOT IN。例如,我们要查询Sendorder表中的冗余数据(没有和reg_person或worksite相连的数据): ```sql select Sendorder.id, Sendorder.reads, Sendorder....

    Oracle In和exists not in和not exists的比较分析

    在Oracle数据库中,`IN`、`EXISTS`、`NOT IN` 和 `NOT EXISTS` 是四个常用的子查询操作符,它们在SQL查询语句中扮演着不同的角色,且各有其性能特点。以下是对这些操作符的详细分析和比较。 1. `IN` 操作符: `IN` ...

Global site tag (gtag.js) - Google Analytics