查询优化

一、SQL语句优化:

1: exists 快于in

2: 数据量小时连接查询快于子查询,数据量大时子查询快于连接查询

3: select (*) 快于 select count(col)

4: 连接查询快于from多个表查询

如果查询结果来源于多个表中则用连接查询，如果最后结果来源于一个中则推荐用子查询

二、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. 

三、如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   

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

 

select * from tba left join tbb on tba .id=tbb.id and  tbb.name = 'aaa'
select * from tba left join tbb on tba .id=tbb.id where tbb.name = 'aaa'

 

on 后的条件是连接的时候 过滤之用
由于是left join ,所以在on后面写第一个表的过滤条件都是没有用的

 

结果仍然存在不一致的可能的
跟实验数据有关(试验)

 

如果不是正式数据

你先执行： delete from tbb ;

然后再比较一下结果就可以看出来了

结果会是：

select * from tba left join tbb on tba .id=tbb.id and  tbb.name = 'aaa'
--返回tba所有记录

select * from tba left join tbb on tba .id=tbb.id where tbb.name = 'aaa'
-- 没有任何记录返回

 

on 和where
几乎不可能写出一致的来
2种写法就是2种逻辑

 

基本上not in 的写法都能用 A left join B on A.id=B.id where B.id is null 来取代
建立id索引后，效率有的时候能提升千百倍以上

 

//======

别名不可用在where group 中 可以用在order 中 这里的别名特指字段的别名

rownum只能是小于 不能是大于

====

 select
         *
     from
         ( select
             row_.*,
             rownum rownum_
         from
             ( select
                 czy0_.czgh as czgh21_,
                 czy0_.CHARGE_BZ as CHARGE2_21_,
                 czy0_.czmm as czmm21_,
                 czy0_.czqx as czqx21_,
                 czy0_.ksbm as ksbm21_,
                 czy0_.KSBM_JC as KSBM6_21_,
                 czy0_.srf as srf21_,
                 czy0_.xm as xm21_,
                 czy0_.yfbh as yfbh21_,
                 czy0_.ysbh as ysbh21_
             from
                 Czy czy0_ ) row_
         where
             rownum <= 10
         )
     where
         rownum_ > 5

评论

1 楼 jyjava 2012-01-17