java linest_不要再纠结in和exists——JAVA伪代码直白分析二者时间复杂度

> 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语句

select * from student where

studentId=_student_id

in(子查询全表扫描):

scoreList.size()

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

4 简化

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

Time_Exists = 主表记录数 *

Time(一条exists语句的执行时间)

Time_IN

= 子查询结果集记录数

*Time(一条select语句的执行时间)

数据量大，在决定该使用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区别的一个原因。

四、后记

1. on 2012-10-15 09:00

后面我看到了一篇文章，同样比较简单易明，对in和exists的对比和我一致，但是我必须承认，老外的描述得比我更好一些(不知道是不是英语更适合表达技术的原因，呵呵)，所以摘录下来，与大家共享

：)

from:

can you give me some example at which situation IN is better than

exist, and vice versa.

You Asked

Tom:

can you give me some example at which situation

IN is better than exist, and vice versa. and we said...

Well, the two are processed very very differently.

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select * from t1, ( select distinct y from t2 ) t2

where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or

sorted) and then joined to the original table -- typically.

As opposed to select * from t1 where exists ( select null from t2 where y = x

)

That is processed more like:

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

It always results in a full scan of T1 whereas the first query can

make use of an index on T1(x).

So, when is where exists appropriate and in appropriate?

Lets say the result of the subquery

( select y from T2 )

is "huge" and takes a long time. But the table T1 is relatively

small and executing ( select null from t2 where y = x.x ) is very very fast (nice index

on t2(y)). Then the exists will be faster as the time to full scan T1 and do the index

probe into T2 could be less then the time to simply full scan T2 to build the subquery we

need to distinct on.

Lets say the result of the subquery is small -- then IN is typicaly

more appropriate.

If both the subquery and the outer table are huge -- either might

work as well as the other -- depends on the indexes and other factors.

2. on 2012-10-15 09:22

from

ITPUB

一个比较厉害的人写的帖子，里面就提供了in比exists快的一个例子。

他说：

in适合内外表都很大的情况，exists适合外表结果集很小的情况。

exists 和 in 使用一例

其实意思是内外表都很大，连接后比较小，in会快一些。

EXISTS、IN对比 写道

EXISTS、IN、NOT EXISTS、NOT IN的区别：

in适合内外表都很大的情况，exists适合外表结果集很小的情况。

exists 和 in 使用一例 =========================================================== 今天市场报告有个sql及慢，运行需要20多分钟，如下：

update p_container_decl cd

set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate

where exists(

select 1

from (

select tc.decl_no,tc.goods_no

from p_transfer_cont tc,P_AFFIRM_DO ad

where tc.GOODS_DECL_NO = ad.DECL_NO

and ad.DECL_NO = 'sssssssssssssssss'

) a

where a.decl_no = cd.decl_no

and a.goods_no = cd.goods_no

)

上面涉及的3个表的记录数都不小，均在百万左右。根据这种情况，我想到了前不久看的tom的一篇文章，说的是exists和in的区别，

in 是把外表和那表作hash join，而exists是对外表作loop，每次loop再对那表进行查询。

这样的话，in适合内外表都很大的情况，exists适合外表结果集很小的情况。

而我目前的情况适合用in来作查询，于是我改写了sql，如下：

update p_container_decl cd

set cd.ANNUL_FLAG='0001',ANNUL_DATE = sysdate

where (decl_no,goods_no) in

(

select tc.decl_no,tc.goods_no

from p_transfer_cont tc,P_AFFIRM_DO ad

where tc.GOODS_DECL_NO = ad.DECL_NO

and ad.DECL_NO = ‘ssssssssssss’

) 让市场人员测试，结果运行时间在1分钟内。问题解决了，看来exists和in确实是要根据表的数据量来决定使用。