参考http://zjwlonger.iteye.com/blog/1921763,https://blog.csdn.net/u011627980/article/details/50370566
说一说,我们工作和面试最常被提问的也是最常用的操作—sql优化,老鸟和新手的最大差别就是在优化这方面,又要找工作了不得不整理一下知识重新出发。
1.系统要求进行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
2.分析一下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 是把外表和内表作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 where exists(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 where exists(select cc from A where cc=B.cc)
效率低,用到了A表上cc列的索引。
not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;
而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大,用not exists都比not in要快。
in 与 =的区别
select name from student where name in ('zhang','wang','li','zhao');
与
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'
的结果是相同的。
3.在Oracle SQL中取数据时有时要用到in 和 exists 那么他们有什么区别呢?
1 性能上的比较
比如Select * from T1 where x in ( select y from T2 )
执行的过程相当于:
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
相对的
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 不可避免的要被完全扫描一遍
分别适用在什么情况?
以子查询 ( select y from T2 )为考虑方向
如果子查询的结果集很大需要消耗很多时间,但是T1比较小执行( select null from t2 where y = x.x )非常快,那么exists就比较适合用在这里
相对应得子查询的结果集比较小的时候就应该使用in.
微信公众号:程序国度
我的GitHub
QQ:2541692705
邮箱:loyalwilliams@163.com
微信扫一扫关注我,没事可以玩玩小游戏
我想去流浪,我想去读书,若有机会,江湖再见
扫一扫,领取红包,就当奖励你我付出的努力