in子查询、exists子查询、连接,效率的探讨

in可以分为三类: 

1、形如select  *  from  t1  where  f1  in  ('a','b'),应该和select  *  from  t1  where  f1  ='a'  or  f1='b'  或者  select  *  from  t1  where  f1  ='a'  union  all  select  *  from  t1  f1='b'比较效率,搂主可能指的不是这一类,这里不做讨论。 

2、形如select  *  from  t1  where  f1  in  (select  f1  from  t2  where  t2.fx='x'),其中子查询的where  里的条件不受外层查询的影响,这类查询一般情况下,自动优化会转成exist语句,也就是效率和exist一样。 

3、形如select  *  from  t1  where  f1  in  (select  f1  from  t2  where  t2.fx=t1.fx),其中子查询的where  里的条件受外层查询的影响,这类查询的效率要看相关条件涉及的字段的索引情况和数据量多少,一般认为效率不如exists。   

除了第一类in语句都是可以转化成exists  语句的,一般编程习惯应该是用exists而不用in. 

 

===========================================

 

in  和  join  的可比性不大,join应该和子查询比 

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

 

如A,B两个表, 

当只显示一个表的数据如A,关系条件只一个如ID时,使用IN更快: 

select  *  from  A  where  id  in  (select  id  from  B)  

 

当只显示一个表的数据如A,关系条件不只一个如ID,col1时,使用IN就不方便了,可以使用EXISTS: 

select  *  from  A  where  exists  (select  1  from  B  where  id  =  A.id  and  col1  =  A.col1) 

 

当只显示两个表的数据时,使用IN,EXISTS都不合适,要使用连接: 

select  *  from  A  left  join  B  on  id  =  A.id   

 

所以使用何种方式,要根据要求来定。 

=================================

 

SQL Server 中Inner join 和where的效率差异

 

今天,手头上正在作的一个项目,在生成报表时,客户感觉太慢,于是,各处检查,看可否提示效率。界面上的都改进了,提升不大。如是在SQL 语句上下功夫。(我这人比较懒,对简单的语句和查询都没有经过仔细优化的,一般只对姚使用left join,outer join,group by 以及carsor的语句会仔细写并用数据库理论考虑和检查---因为这种语句一般测试时如果发现错误,检查和调试很麻烦)

先在网上Google搜索“Join 与 Where 效率”以及察看SQL Server 帮助文档,希望能获得“捷径”些的优化思路。

搜索的结果是,各大论坛,包括MSDN上很多人提出了这个问题,但回答是众说纷纭。总体上总结出来时说:对小数据量(<N万)的来说效率几乎无差异,更有说法说Inner join 和Where只是SQL标准不同,在查询分析器中SQL Server查询分析器是将Where直接转换为Join后查询的。

还是自己来做试验吧。

如是有了如下比较结果(均在查询分析器中查询和计时):

语句(1)
declare @operatorName nvarchar(50)
set @operatorName = '%'

 select distinct item.*  from item , customer_item , customer_operator ,operator
where item.itemcode = customer_item.itemCode
and customer_item.customerCode =  customer_operator.customerCode
and customer_operator.operatorId =  customer_operator.operatorId
and operator.operatorName like @operatorName
and item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0
查询结果,74行,共时间0:00:04

语句(2)
declare @operatorName nvarchar(50)

set @operatorName = '%'

 select distinct item.*  from item inner join  customer_item
on  item.itemcode = customer_item.itemCode
inner join customer_operator on customer_item.customerCode = customer_operator.customerCode
inner join operator on customer_operator.operatorId = operator.operatorId
where  operator.operatorName like @operatorName
and item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0
共74行,时间0:00:01

后检查发现语句(1)中有一个重复自查询条件 :customer_operator.operatorId =  customer_operator.operatorId
将其叶加到语句2中,语句(3)
declare @operatorName nvarchar(50)

set @operatorName = '%'

 select distinct item.*  from item inner join  customer_item
on  item.itemcode = customer_item.itemCode
inner join customer_operator on customer_item.customerCode = customer_operator.customerCode
inner join operator on customer_operator.operatorId = operator.operatorId
where  operator.operatorName like @operatorName
and item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0
and customer_operator.operatorId =  customer_operator.operatorId

所用时间和结果都为74行,时间0:00:01。

将语句(1)中的去掉该条件后成为语句(4)
declare @operatorName nvarchar(50)
set @operatorName = '%'

 select distinct item.*  from item , customer_item , customer_operator ,operator
where item.itemcode = customer_item.itemCode
and customer_item.customerCode =  customer_operator.customerCode
--and customer_operator.operatorId =  customer_operator.operatorId
and operator.operatorName like @operatorName
and item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0

时间和结果为74行,时间0:00:01。

终于发现了些他们的差异。

结论:
         尽量使用Join 而不是Where来列出关联条件,特别是多个表联合的时候。
原因是:
            (1)在效率上,Where可能具有和Inner join一样的效率。但基本可以肯定的(通过SQLServer帮助和其它资料,以及本测试)是Join的效率不比Where差。
            (2)使用Join可以帮助检查语句中的无效或者误写的关联条件

 

 

=======================

以上整理,个人结论如下:

用where进行多表连接的比较时,先全部进行迪卡尔积后,最后再统一过滤。

而inner join是一边Descartes迪卡尔积,同时这滤,所以临时记录会少一些。

这一点在多个表联络合表现比较突出。

inner join是sql国际准标,有些db如sql2005会尽可能的进行编译运行优化,将where转化为inner join

 

=============================

JOIN的效率是很低,特别是ON后的字段不是关键子或者索引时
给你个建议:
先把你的查询语句保存成SQL文件

打开企业管理器—>打开你要查询的数据库—>点击上面的运行向导—>管理—>索引优化向导—>优化模式选择彻底—>选择我的工作负荷文件(打开你的查询文件)—>执行优化—>在建议的列上加索引
执行优化向导2到4遍至性能提升为0%为止
此时看你的查询语句执行速度又多么快的提升
================================

  • 0
    点赞
  • 0
    评论
  • 3
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值