|
锁定老帖子 主题：in 和 exists的区别 用数据说话
该帖已经被评为良好帖
|
|
|---|---|
| 作者 | 正文 |
|
发表时间：2012-05-30
楼主很认真，值得尊敬。
|
| 返回顶楼 | |
|
发表时间：2012-05-30
liguocai2009 写道 引用 举个例子: 例如：表A（小表），表B（大表），CC列上有索引 在外表小，内表大的情况下用in(Hash Join A和B两个表).效率就会很低，比如: select * from A where cc in (select cc from B) 这个时候查询用到了A表上cc列的索引，但是因为A是小表，而且大表B上的索引也没有充分利用，因此效率不高 而如果用了exist，我们发现走的是对A，B两个表的nested Loop,对于遍历A的每一行,都会对大表B进行一次查询(可能对B表的查询结果不是太多.) select * from A where exists(select cc from B where cc=A.cc) 这样效率会高些，因为用到了B表(大表)上cc列的索引。 not in 和not exists 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引； 而not extsts 的子查询依然能用到表上的索引。 所以无论那个表大，用not exists都比not in要快。 内表外表我不明白是什么意思，楼主的贴我没看明白。我只是看明白另外一篇讨论（如上），这篇从索引角度分析了exists和in的效率，很好懂，然而对下面的我就看不懂了为什么了，有人能解释一下吗？(我连什么叫外表，驱动表，内表的定义，为什么这么叫都不知道。。。) 引用 简单的说 in 是先内表，后外表，exist是先外表，后内表。 几个判断依据： 1.是否存在排序，排序都是耗资源的 2.是否走索引 这里讲了in和exist的执行顺序是不一样的。有人能用java程序写下exists和in的实现代码吗？ 为什么顺序不一样，导致效率是不一样的？ 如果外表是m行，内表是n行，不管先外后内，还是先内后外，结果不也是要循环m*n次吗？ 楼主能够抽点时间解答我的疑问吗？ |
| 返回顶楼 | |
|
发表时间：2012-05-30
liguocai2009 写道 liguocai2009 写道 引用 举个例子: 例如：表A（小表），表B（大表），CC列上有索引 在外表小，内表大的情况下用in(Hash Join A和B两个表).效率就会很低，比如: select * from A where cc in (select cc from B) 这个时候查询用到了A表上cc列的索引，但是因为A是小表，而且大表B上的索引也没有充分利用，因此效率不高 而如果用了exist，我们发现走的是对A，B两个表的nested Loop,对于遍历A的每一行,都会对大表B进行一次查询(可能对B表的查询结果不是太多.) select * from A where exists(select cc from B where cc=A.cc) 这样效率会高些，因为用到了B表(大表)上cc列的索引。 not in 和not exists 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引； 而not extsts 的子查询依然能用到表上的索引。 所以无论那个表大，用not exists都比not in要快。 内表外表我不明白是什么意思，楼主的贴我没看明白。我只是看明白另外一篇讨论（如上），这篇从索引角度分析了exists和in的效率，很好懂，然而对下面的我就看不懂了为什么了，有人能解释一下吗？(我连什么叫外表，驱动表，内表的定义，为什么这么叫都不知道。。。) 引用 简单的说 in 是先内表，后外表，exist是先外表，后内表。 几个判断依据： 1.是否存在排序，排序都是耗资源的 2.是否走索引 这里讲了in和exist的执行顺序是不一样的。有人能用java程序写下exists和in的实现代码吗？ 为什么顺序不一样，导致效率是不一样的？ 如果外表是m行，内表是n行，不管先外后内，还是先内后外，结果不也是要循环m*n次吗？ 楼主能够抽点时间解答我的疑问吗？ 效率高低要看执行计划！ 执行计划说了算！ 你写的sql可能是A样子，但执行计划可能把它解释为B样子。 |
| 返回顶楼 | |
|
发表时间：2012-05-30
引用 简单的说 in 是先内表，后外表，exist是先外表，后内表。 几个判断依据： 1.是否存在排序，排序都是耗资源的 2.是否走索引 SQL1: select * from A where cc in (select cc from B) ; SQL2: select * from A where exists(select cc from B where cc=A.cc) ; 这里讲了in和exist的执行顺序是不一样的。有人能用java程序写下exists和in的实现代码吗？ 我想问的问题是: 为什么顺序不一样，导致效率是不一样的？ 如果外表是m行，内表是n行，不管先外后内，还是先内后外，结果不也是要循环m*n次吗？ 为什么效率会不一样? |
| 返回顶楼 | |
|
发表时间：2012-05-30
最后修改：2012-05-30
liguocai2009 写道 引用 简单的说 in 是先内表，后外表，exist是先外表，后内表。 几个判断依据： 1.是否存在排序，排序都是耗资源的 2.是否走索引 SQL1: select * from A where cc in (select cc from B) ; SQL2: select * from A where exists(select cc from B where cc=A.cc) ; 这里讲了in和exist的执行顺序是不一样的。有人能用java程序写下exists和in的实现代码吗？ 我想问的问题是: 为什么顺序不一样，导致效率是不一样的？ 如果外表是m行，内表是n行，不管先外后内，还是先内后外，结果不也是要循环m*n次吗？ 为什么效率会不一样? 通过执行计划 看 外表 和 内表 是否走索引 in 和 exists只是一个关键字 如果外表是m行，内表是n行，不管先外后内，还是先内后外，结果不也是要循环m*n次吗？ 为什么效率会不一样? 因为如果n表有索引 那么可以通过索引来获取数据 复杂度可以是o(logn) |
| 返回顶楼 | |
|
发表时间：2012-05-30
5、 not in 不走索引的，因此不能用
6、 not exists走索引的。 这个和索引也有关? |
| 返回顶楼 | |
|
发表时间：2012-05-31
第二个用例你用了group By,你要不用group by呢？
select count(*) from base_customer_sign where signCustomerUuid in (select uuid from base_customer where trueName like 'user%' <Strong>group by</Strong> uuid) |
| 返回顶楼 | |
|
发表时间：2012-05-31
你的优化模式是choose,这种模式可以在RBO和CBO之间选择，你的执行计划中，没有成本的数据，执行中应该是选择RBO了，10g之后都是使用CBO，RBO已经被淘汰了，你的测试结果没有意义，建议使用CBO测试吧。
|
| 返回顶楼 | |
|
发表时间：2012-05-31
hudingchen 写道 你的优化模式是choose,这种模式可以在RBO和CBO之间选择，你的执行计划中，没有成本的数据，执行中应该是选择RBO了，10g之后都是使用CBO，RBO已经被淘汰了，你的测试结果没有意义，建议使用CBO测试吧。
因为我用的是oracle9i 所以直接使用oracle9i测试了！ 有时间用CBO测试下！ |
| 返回顶楼 | |
|
发表时间：2012-06-01
liguocai2009 写道 引用 举个例子: 例如：表A（小表），表B（大表），CC列上有索引 在外表小，内表大的情况下用in(Hash Join A和B两个表).效率就会很低，比如: select * from A where cc in (select cc from B) 这个时候查询用到了A表上cc列的索引，但是因为A是小表，而且大表B上的索引也没有充分利用，因此效率不高 而如果用了exist，我们发现走的是对A，B两个表的nested Loop,对于遍历A的每一行,都会对大表B进行一次查询(可能对B表的查询结果不是太多.) select * from A where exists(select cc from B where cc=A.cc) 这样效率会高些，因为用到了B表(大表)上cc列的索引。 not in 和not exists 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引； 而not extsts 的子查询依然能用到表上的索引。 所以无论那个表大，用not exists都比not in要快。 内表外表我不明白是什么意思，楼主的贴我没看明白。我只是看明白另外一篇讨论（如上），这篇从索引角度分析了exists和in的效率，很好懂，然而对下面的我就看不懂了为什么了，有人能解释一下吗？(我连什么叫外表，驱动表，内表的定义，为什么这么叫都不知道。。。) 引用 简单的说 in 是先内表，后外表，exist是先外表，后内表。 几个判断依据： 1.是否存在排序，排序都是耗资源的 2.是否走索引 这里讲了in和exist的执行顺序是不一样的。有人能用java程序写下exists和in的实现代码吗？ 为什么顺序不一样，导致效率是不一样的？ 如果外表是m行，内表是n行，不管先外后内，还是先内后外，结果不也是要循环m*n次吗？ 顺序不一样？ in和exist 不都是先执行 子查询里，然后在判断是否有数据，在执行外面的查询。 m*n我觉得也是，除非有索引吧，不过oracle好像默认B树索引，所以应该不是m*n的全表查询，应该看代码情况 |
| 返回顶楼 | |
