sql语句优化分享

sql语句优化分享

这是查询学生数据的逻辑，逻辑比较有点乱，这个查询跑30分钟也不会出结果，一执行CPU立马100%,虽然是个虚似机，但也不至于这种查询也对付不了，肯定有优化的地方。
  
 SELECT  *        FROM 学生表 WITH(NOLOCK) WHERE
         (FromSys IS NULL OR
         (
            (FromSys<>'A' AND FromSys<>'B' AND FromSys<>'C') OR
(
 (FromSys='A' OR FromSys='B' OR FromSys='C')
   AND FromSysID NOT IN(SELECT OriginID FROM 学生表 WITH(NOLOCK) WHERE DataFlag=0)
 )
)
     )

 
     上面的代码看起来有点乱，其实整理一下，条件就是三类：
    1：FromSys IS NULL。
    2：FromSys<>'A' AND FromSys<>'B' AND FromSys<>'C'。
    3：(FromSys='A' OR FromSys='B' OR FromSys='C')  AND FromSysID NOT IN(SELECT OriginID FROM 学生表 WITH(NOLOCK) WHERE DataFlag=0)
 )

    优化点如下：
    第一：把上面的第一个和第二个条件or换成union all，主要是因为过多的or查询，有可能会引起表扫描，致使性能下降,这里就不做比较了。
    第二，把第三个or里面的not in 用left join 代替，这种写法还要感谢我上家公司的DBA，他们教会我不少SQL知识，嘿嘿。
          left join sql:
         
SELECT COUNT(*) FROM (  
        SELECT  *
        FROM 学生表 WITH(NOLOCK) WHERE  
     FromSys IN ('A','B','C' )
   
       ) AS tem LEFT JOIN dbo.学生表 s2 ON tem.FromSysID=s2.OriginID AND s2.DataFlag=0
       WHERE s2.FromSysID IS null

                下面是left join的执行计划图，很清晰，很简单。

        
 
         not in sql:
           
SELECT COUNT(*) FROM  学生表 WITH(NOLOCK) WHERE 
        (FromSys='A' OR FromSys='B' OR FromSys='C')
       AND FromSysID NOT IN(SELECT OriginID FROM 学生表 WITH(NOLOCK) WHERE DataFlag=0)
 
          下面是not in的执行计划图，复杂得多，而且有多个嵌套查询。
         
    第三：创建相应索引，其实对于非专业DBA，有时不太容易去优化SQL语句，但目前sql 2008有一个特别简单的功能，可以从预估执行计划中得出需要创建的索引，我们参考一下就行：
 
                                 
  
       第四：如果一个条件中过多的or,例如:FromSys='A' OR FromSys='B' OR FromSys='C',可以用in来代码,这样代码会精简一些。
 
      效果：最终使执行30分钟都无结果的操作，控制下几秒内完成，不可思议啊

补充:

1.多where，少having
where用来过滤行，having用来过滤组

2.多union all，少union
union删除了重复的行，因此花费了一些时间

3.多Exists，少in
Exists只检查存在性，性能比in强很多，有些朋友不会用Exists，就举个例子
例，想要得到有电话号码的人的基本信息，table2有冗余信息
select * from table1;--(id,name,age)
select * from table2;--(id,phone)
in：
select * from table1 t1 where t1.id in (select t2.id from table2 t2 where t1.id=t2.id);
Exists：
select * from table1 t1 where Exists (select 1 from table2 t2 where t1.id=t2.id);

4.使用绑定变量
Oracle数据库教程软件会缓存已经执行的sql语句，复用该语句可以减少执行时间。
复用是有条件的，sql语句必须相同
问：怎样算不同？
答：随便什么不同都算不同，不管什么空格啊，大小写什么的，都是不同的
想要复用语句，建议使用PreparedStatement
将语句写成如下形式：
insert into XXX(pk_id,column1) values(?,?);
update XXX set column1=? where pk_id=?;
delete from XXX where pk_id=?;
select pk_id,column1 from XXX where pk_id=?;

5.少用*
很多朋友很喜欢用*，比如：select * from XXX;
一般来说，并不需要所有的数据，只需要一些，有的仅仅需要1个2个，
拿5W的数据量，10个属性来测试:
(这里的时间指的是PL/SQL Developer显示所有数据的时间)
使用select * from XXX;平均需要20秒，
使用select column1,column2 from XXX;平均需要12秒
(我的机子不是很好。。。)
对于开发来说，这一条是个灾难，知道是一回事，做就是另一回事了

6.分页sql
一般的分页sql如下所示：
sql1:select * from (select t.*,rownum rn from XXX t)where rn>0 and rn <10;
sql2:select * from (select t.*,rownum rn from XXX t where rownum <10)where rn>0;
乍看一下没什么区别，实际上区别很大...125万条数据测试，
sql1平均需要1.25秒(咋这么准呢？ )
sql2平均需要... 0.07秒
原因在于，子查询中，sql2排除了10以外的所有数据
当然了，如果查询最后10条，那效率是一样的

7.能用一句sql，千万别用2句sql
不解释
（补充内容）
第6条指的是是oracle数据库。
第7条本来不准备解释的，也有这么多质疑的，说明追寻真理的朋友还是相当多的。
我认为数据库当掉确实很有可能，我没想到是我的问题。

以上各条只针对sql如何优化，至于开发效率低，可读性等等，并不在考虑范围内

（补充内容2）第6条，如果有分页需要排序，必须再包一层，结果为
select * from (select t.*, rownum rn from (select * from XXX order by value desc) t where rownum <= 10 ) where rn > 0;

（补充内容3）第3条，用in的朋友注意了，当参数超过1000个，数据库就挂了。（oracle 10g数据库）