SQL语句优化总结.doc

数据库优化总结一、前言1二、在SELECT子句中避免使用1三、删除重复记录1四、计算记录条数2五、减少对表的查询2六、EXISTS 与IN使用2七、使用索引3八、UNION与UNION ALL的使用4九、驱动表的选择5十、Where语句中条件的顺序5十一、使用where代替having语句5十二、使用表的别名5十三、表记录删除6十四、Decode函数使用6十五、尽量少用子查询6十六、SQL语句拆分6十七、SQL语句中参数的使用7十八、解释计划7一、 前言下面是对使用ORACLE的SQL语句的一些优化总结，借鉴了一些资料和自己的经验总结，只是部分优化方法，甚至可能还会有不准确的地方，希望看官给予补充我修正，以提高大家写SQL语句的水平。二、 在SELECT子句中避免使用当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 * 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将* 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.三、 删除重复记录最高效的删除重复记录方法 ( 因为使用了ROWID)DELETE FROM EMP EWHERE E.ROWID (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO)Oracle的ORWID用来唯一标识表中的一条记录,是这条数据在数据库中存放的物理地址四、 计算记录条数计算记录数一般count(*),count(1),count(索引)三种写法，使用count(索引)方法是最快的，另两种哪个快说法不一，但通过实际测试差不太多五、 减少对表的查询在写SQL语句时要减少对表的查询，即能通过对表一次查询取得结果就不要查两次。例1：Select * from clmb01c Where cjxsdm = (select max(cjxsdm) from clmb10)And csqdh = (select min(csqdh) from clmb10)Where id = 833997可改成Select * from clmb01c Where (cjxsdm, csqdh)= (select max(cjxsdm),min(csqdh) from clmb10) where id = 833997;更新也一样Update clmb01c set (cjxsdm,csqdh) = (select max(cjxsdm),min(csqdh) from clmb10)Where id = 833997;当然此例还有其它写法，在此拿出只是说明观点例2：Update clmb01c set cdjzt = 1 where id = 833997;Select nhjje into n_hjje from clmb01c where id = 833997;以上两语句可合成一个Update clmb01c set cdjzt = 1 where id = 833997 returning nhjje into n_hjje;六、 EXISTS 与IN使用对于这两个操作符，通常认为EXISTS会比IN效率高，特别是EXISTS的子查询语句可用到索引时会比IN效率高Oracle在处理这两个操作时处理如下：IN：Oracle将IN后面的子查询语句调入到内存，然后排序，如果记录多会非常占用系统资源EXISTS：在每次查询时调用一次子查询语句，所以就要保证子查询语句能使用索引，否则就会全表扫描，如果记录数很多那是很可怕的事情，会产生迪卡尔乘积结论：记录小的时候用哪个都对效率没有多大影响，对于记录大时一定要使用EXISTS，并保存子查询使用索引对于NOTEXISTS和NOTIN一定要选用NOTEXISTS，还可使用（外连接判断为空）七、 使用索引对于数据量大的表一定要建立适当的索引，正确的使用索引可以级大的提高数据检索效率，在写SQL语句时要考虑表索引的使用，但索引的缺点一是增加物理空间的占用，二是对表的增、删、改会增加一次索引的增、删、改操作，所以对于大表要建立并使用索引，但不能过多使用索引要注意以下几点：1 要按索引列构造SQL语句的where条件才能使用到索引2 使用不等于操作符则不能使用索引例：select col1,col2 from table1 where col1 AA 如果列col1是索引则此写法不能使用索引，同样还有not in (AA)那出现这种问题如何解决呢？那就要改变这种写法，比如判断的条件值有AA，BB，CC则条件改写成col1 = BB or col1 = CC3 使用is NULL 则不能使用索引如上例：where col1 is NULL 不会使用索引对于索引列要避免出现NULL值4 LIKE操作符的使用对于LIKE使用时后面的条件前面不能有通配符，否则不能使用索引select col1,col2 from table1 where col1 like AA%col1是字符型索引列，上面的语句会用到索引，但下面的语句不会用到索引select col1,col2 from table1 where col1 like %AA%所以在使用时要注意5 不能对索引列合并计算使用例：SELECT NVL(SUM(NVL(NYFPSL, 0), 0) FROM SALR02 WHERE CNF | CYF = 200608因为SALR02表的索引是CNF，CYF，所以CNF | CYF会造成全表扫描应写成SELECT NVL(SUM(NVL(NYFPSL, 0), 0) FROM SALR02 WHERE CNF = 2006 and CYF = 08如果是年份和月份是一个变量则写成SELECT NVL(SUM(NVL(NYFPSL, 0), 0) FROM SALR02 WHERE CNF = substrb(c_jhny,1,4) and CYF = substrb(c_jhny,5,2)6 对索引列使用函数也会造成系统不能使用索引例：select col1,col2 from table1 where substrb(col1,1,2) = AA索引列是col1，这种写法也是不能使用索引的需要改成where col1 like AA%其它情况就要按具体函数具体分析7 比较不匹配的数据类型时也会影响索引的使用例：select col1,col2 from table1 where col1 = 88383Col1是索引列，类型是varchar2(10)，此种情况oracle是允许的，只是oracle在处理时会自动在列col1上加个函数使条件变成where to_number(col1) = 88383这样就不能使用索引了8 避免使用OR条件在SQL语句条件如果对于索引列使用OR条件则系统不会使用索引例：select cjxsdm from clmb01c where cjxsdm = 01-01F or csqdh = 200601010003不管怎么建索引你都用不上了，如果表记录多那你就想别的办法吧9 可以强制使用索引例：select /*+INDEX(table1 IK_col1)*/ col1,col2 from tabl1 ;Table1是表名，IK_col1是索引名强制使用并不表示最优，根据具体情况定10 对于某些日期型字段常用字符型日期作为条件查询，这种情况可建立函数索引如表table1中字段dczrq是日期型date，建立函数索引IK_dczrqCreate index IK_dczrq on table1(to_char(dczrq,yyyymmdd)11 强制使用索引对于有些表你给它建立了索引但是ORACLE却不使用怎么办？实际ORACLE是按照自己的判断来确定如何从表中检索数据，它认为哪种检索效率高就使用哪种方式（基于选择的优化方式），但也有些情况它选择的不正确，不是我们所希望的检索数据方式（使用索引），那么我们就要强制让ORACLE使用索引。例：Select * from mdac24 where to_char(djdrq,yyyy/mm/dd) = 2007/01/15这个语句执行很慢，怎么办？通常想法是加索引，所以我就加了个索引（是函数索引），索引名IK_MDAC241索引列to_char(djdrq,yyyy/mm/dd)，这样就应该快了吧！但在执行时还是慢，看执行计划原来它没有使用索引（不听话了）。我强制让ORACLE使用索引，形式如下： Select /*+INDEX(mdac24 IK_MDAC241)*/ * from mdac24 where to_char(djdrq,yyyy/mm/dd) = 2007/01/15这回效率有明显提高，看看执行计划ORACLE乖乖使用了函数索引，看来它还是听话的J12对于数据量比较大并且频繁使用的表的索引需要定期进行重建rebuiled八、 UNION与UNION ALL的使用这两个操作符均是对结果集的操作，合并两个结果集，不同的是UNION操作会将两个结算集中的重复记录去除掉之后再合，而UNION ALL不管你记录啥样就是合因此UNION会比UNIONALL增加大量的计算，所以不是必要的情况下一定不要使用UNION，而使用UNIONALL九、 驱动表的选择如果两个表关联（指内联接的通用写法），oracle会首先选择一个表检索，然后再按条件关联另一个表，oracle按自下而上的顺序，即最后一个表作为驱动表，按此原理就要将能返回最小记录数的表作为驱动表例：select * from clmb01c a,clmb01d b where a.id = b.id Oracle在处理此语句时将先取出clmb01d记录，然后再匹配clmb01c记录另一写法select * from clmb01d a,clmb01c b where a.id = b.id这样的写法会先取clmb01c的记录，然后再匹配clmb01d记录因为clmb01c的记录比clmb01d的记录少，所以后一种写法会比较高效十、 Where语句中条件的顺序ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前， 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾此说法有待考证，暂时放这，等我找有说服力的例子十一、 使用where代替having语句对于有些SQL语句中having条件如果可以改成where 里去写，则要放到where里去写，因为使用having是在检索出所有结果集之后才对数据进行过滤，如果将条件放到where里就会返回少的结果集，那多好!例：select cjxsdm,count(njsje) from clmb20Group by cjxsdmHaving cjxsdm = 09-23F此种语句一定要避免，要改成如下写法select cjxsdm,count(njsje) from clmb20where cjxsdm = 09-23FGroup by cjxsdm十二、 使用表的别名在写SQL语句时最好使用表的别名，特别是多表联接时，因为表的别名会减少oracle对SQL语句的解析时间例：select cjxsdm,csqdh,cgzdm from clmb01c,clmb01d where clmb01c.id = clmb01d.id前两个字段是clmb01c表的，后面的字段是clmb01d表的，oracle要从数据字典里去判断哪个字段是哪个表，如果改成如下写法则会省了这一步select a.cjxsdm,a.csqdh,b.cgzdm from clmb01c a,clmb01d b where a.id = b.id十三、 表记录删除如果删除表中记录使用delete语句，但要删除表中全部记录还可使用truncate语句，truncate语句会比delete语句效率高，并且删除的更干净，但truncate语句一定要慎用，因为他是DDL语句，执行完truncate之后会自动提交事务，所以一定要小心噢十四、 Decode函数使用合理使用Decode函数会减少对表的检索例：select count(*) from clmb01c where cjxsdm = 01-01F and cshbs = 0;Select count(*) from clmb01c where cjxsdm = 01-01F and cshbs = 1;此两个语句会对表clmb01c执行两次表的扫描，使用decode函数可改成一次表扫描Select count(decode(cshbs,0,1,null) count0,count(decode(cshbs,1,1,null) count1From clmb01c where cjxsdm = 01-01F and cshbs in (0,1)十五、 尽量少用子查询特别是相关子查询。因为这样会导致效率下降。一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行例：Select a.njsje,(select b.nfpsl from mdac22 b where b.cjxsdm = a.cjxsdm ) nfpsl from clmb01c a 这样写法当然是不好啦，可改成关联，怎么写自己看吧十六、 SQL语句拆分对于某些情况，我们需要从多个表关联取出某些数据，通常习惯于写一个SQL语句，然后取出相关数据。有时将SQL语句分解，可能从效率上会有很大改观示例：select denterdate , nmileage from vd_dsvcb20c_all where id =( select min ( id ) from vd_dsvcb20c_all where nvehic