SQL语句优化总结

1、数据库优化总结一、冃ij旨1二、在select子句屮避免使用1三、删除重复记录1四、计算记录条数2五、减少对表的查询2六、exists与in使用2七、使用索引3八、union 与 union all 的使用4九、驱动表的选择5十、where语句 中条件的川页序5h 、使用 where代替having 语句5十二、使用表的别名5十三、表记录删除6十四、decode函数使用6十五、尽量少用子查询6十六、sql语句拆分6十七、sql语句屮参数的使用7十八、解释计划7、 刖舌下面是对使用oracle的sql语句的一些优化总结，借鉴了一些资料和自己的经验总 结，只是部分优化方法，甚至可能还会有不准确的地

2、方，希望看官给予补充我修正，以提高 大家写sql语句的水平。二、在select子句中避免使用“ * '当你想在select子句中列岀所有的column吋,使用动态sql列引用 是一个方便的方法不幸的是，这是一个非常低效的方法.实际上,oracle在解 析的过程中，会将'*'依次转换成所有的列名，这个工作是通过查询数据字 典完成的，这意味着将耗费更多的时间.三. 删除重复记录最高效的删除重复记录方法（因为使用了 rowid）delete from emp ewhere e. rowid > (select min(x. rowid)from emp xwhere x

3、.emp_no = e. emp_no)oracle的orw1d用来唯一标识表中的一条记录，是这条数据在数据库中存放的物 理地址四、计算记录条数计算记录数一般count (*), count (1), count (索引)三种写法,使用count (索 引)方法是最快的，另两种哪个快说法不一，但通过实际测试差不太多五、减少对表的查蓬在写sql语句吋要减少对表的查询，即能通过对表一次查询取得结果就不要查两次。 例1：select * from clmbolcwhere cjxsdm = (select max(cjxsdm) from clmblo)and csqdh = (select min

4、(csqdh) from clmblo)where id = 833997可改成select * from clmbolcwhere (cjxsdm, csqdh)= (select max(cjxsdm),min(csqdh) from clmblo) where id = 833997;更新也一样update clmbolc set (cjxsdm,csqdh) = (select max(cjxsdm),min(csqdh) from clmblo)where id = 833997;当然此例还有其它写法，在此拿出只是说明观点例2：update clmbolc set cdjzt 二 t

5、 where id = 833997;select nhjje into n_hjje from clmbolc where id = 833997;以上两语句可合成一个update clmbolc set cdjzt 二 t where id = 833997 returning nhjje into n_hjje;六、exists与in使用对于这两个操作符，通常认为exists会比in效率高，特别是exists的子查询语句 可用到索引时会比in效率高oracle在处理这两个操作时处理如下：in： oracle将in后面的子查询语句调入到内存，然后排序，如果记录多会非常占用系 统资源exis

6、ts：在每次查询时调用一次子查询语句，所以就要保证子查询语句能使用索引， 否则就会全表扫描，如果记录数很多那是很可怕的事情，会产生迪卡尔乘积 结论：记录小的时候用哪个都对效率没有多大影响，对于记录大时一定要使用exists, 并保存子查询使用索引对于not exists和not in定要选用not exists，还可使用(外连接+判断为 空)七、使用索引对于数据量大的表一定要建立适当的索引，正确的使用索引可以级大的提高数据检索效 率，在写sql语句时要考虑表索引的使用，但索引的缺点一是增加物理空间的占用，二是 对表的增、删、改会增加一次索引的增、删、改操作，所以对于大表要建立并使用索引，但 不

7、能过多使用索引要注意以下儿点：1要按索引列构造sql语句的where条件才能使用到索引2. 使用不等于操作符则不能使用索引例：select coll,col2 from table 1 where coll <> 'aa'如果列coll是索引则此写法不能使用索引，同样还有notincaaj那出现这种问题如何解决呢？那就要改变这种写法，比如判断的条件值有'aa', 'bb', 'cc'则条件改写成coll =or coll = 83. 使用is null则不能使用索引如上例：where coll is null不会使用索

8、引对于索引列要避免出现null值4. like操作符的使用对于like使用时后而的条件前而不能有通配符，否则不能使用索引select coll,col2 from table 1 where coll like 'aa%'coll是字符型索引列，上面的语句会用到索引，但下而的语句不会用到索引select coll,col2 from table 1 where coll like '%aa%所以在使用时要注意5. 不能对索引列合并计算使用例：select nvl(sum(nvl(nyfpsl, 0), 0) from salr02 where cnf | cyf = &

9、lt;200608,因为salr02表的索引是cnf, cyf,所以cnf | cyf会造成全表扫描应写成select nvl(sum(nvl(nyfpsl, 0), 0) from salr02 where cnf = <2006, and cyf = 98'如果是年份和月份是一个变量则写成select nvl(sum(nvl(nyfpsl, 0), 0) from salr02 where cnf = substrb(cjhny,l,4) and cyf = substrb(cjhny,5,2)6. 对索引列使用函数也会造成系统不能使用索引例：select coll,col2

10、 from table 1 where substrb(col 1,1,2)= 'aa'索引列是coll,这种写法也是不能使用索引的需要改成 where coll like 'aa%'其它情况就要按具体函数具体分析7. 比较不匹配的数据类型时也会影响索引的使用例：select coll,col2 from table 1 where coll = 88383coll是索引列,类型是varchar2( 10),此种情况oracle是允许的，只是oracle在处 理时会自动在列coll上加个函数使条件变成where to_number(coll) = 88383 这

11、样就不能使用索引了8. 避免使用or条件在sql语句条件如果对于索引列使用or条件则系统不会使用索引例：select cjxsdm from clmbolc where cjxsdm = '01-01f or csqdh = '200601010003' 不管怎么建索引你都用不上了，如果表记录多那你就想别的办法吧9. 可以强制使用索引例：select /*+index(table 1 ik_coll )*/ col 1 ,col2 from tabll ;table 1是表名，ik_coll是索引名强制使用并不表示最优，根据具体情况定10. 对于某些日期型字段常用字符型

12、fi期作为条件查询，这种情况可建立函数索引 如表tablel中字段dezrq是日期型date,建立函数索引ik_dczrqcreate index ik_dczrq on table 1 (to_char(dczrq/yyyymmdd,)11. 强制使用索引对于有些表你给它建立了索引但是oracle却不使用怎么办？实际oracle是按 照自己的判断来确定如何从表中检索数据，它认为哪种检索效率高就使用哪种方式(基 于选择的优化方式)，但也有些情况它选择的不正确，不是我们所希望的检索数据方式 (使用索引)，那么我们就要强制让oracle使用索引。例：select * from mdac24 whe

13、re to_char(djdrq,yyyy/mm/dd,) = *2007/01/15'这个语句执行很慢，怎么办？通常想法是加索引，所以我就加了个索引(是函数索 引)，索引名ik_mdac241索引列lo_char(djdrq,，yyyy/mm/dd) 这样就应该快了吧！ 但在执行时还是慢，看执行计划原来它没有使用索引(不听话了)。我强制i± oracle 使用索引，形式如下：select /*+index(mdac24 ik_mdac241)*/* from mdac24 whereto_char(djdrq,yyyy/mm/dd,) = '2007/01/15，这

14、回效率有明显提高，看看执行计划oracle乖乖使用了函数索引，看来它还是 听话的©12.对于数据量比较大并且频繁使用的表的索引需要定期进行重建rebuiled八、union与union all的使用这两个操作符均是对结果集的操作，合并两个结果集，不同的是union操作会将两个 结算集中的重复记录去除掉之后再合，而union all不管你记录啥样就是合因此union会比union all增加大量的计算，所以不是必要的情况下一定不要使用 union,而使用 union all九、驱动表的选择如果两个表关联(指内联接的通用写法)，oracle会首先选择一个表检索，然后再按条 件关联另一个表

15、，oracle按自下而上的顺序，即最后一个表作为驱动表，按此原理就要将能 返回最小记录数的表作为驱动表例：select * from clmbolc a,clmb01d b where a.id = b.idoracle在处理此语句时将先取出clmbold记录，然后再匹配clmbolc记录另一写法 select * from clmbold a,clmb01c b where a.id = b.id这样的写法会先収clmbolc的记录，然后再匹配clmbold记录 因为clmbolc的记录比clmbold的记录少，所以后一种写法会比较高效十、where语句中条件的顺序oracle采用自下而上的

16、顺序解析where子句，根据这个原理，表之间的连接必须 写在其他where条件之前，那些可以过滤掉最大数量记录的条件必须写在where子句 的末尾 此说法有待考证，暂时放这，等我找有说服力的例子十一、 使用where代替having语句对于有些sql语句中having条件如果可以改成where里去写，则要放到where里去写, 因为使用having是在检索出所有结果集z后才对数据进行过滤，如果将条件放到where里 就会返回少的结果集，那多好！例:select cjxsdm,count(nj sje) from clmb20group by cjxsdmhaving cjxsdm = ,09-

17、23f,此种语句一定要避免，要改成如下写法select cjxsdm,count(njsje) from clmb20where cjxsdm = '09-23f'group by cjxsdm十二、使用表的别名在写sql语句时最好使用表的别名，特别是多表联接时，因为表的别名会减少oracle 对sql语句的解析吋间例：select cjxsdm,csqdh,cgzdm from clmbolc,clmbold where clmbolc.id = clmbold.id 前两个字段是clmbolc表的，后面的字段是clmbold表的，oracle要从数据字典里去判 断哪个字段是

18、哪个表，如果改成如下写法则会省了这一步select a.cjxsdm,a.csqdh,b.cgzdm from clmbolc a,clmbold b where a.id = b.id十三、表记录删除如果删除表中记录使用delete语句，但要删除表中全部记录还可使用truncate语句， truncate语句会比delete语句效率高，并且删除的更干净，但truncate语句一定要慎用，因 为他是ddl语句，执行完truncate之后会自动提交事务，所以一定要小心噢十四、decode函数使用合理使用decode函数会减少对表的检索例：select count(*) from clmbolc

19、where cjxsdm = pl-oif and cshbs = 'o'select count(*) from clmbolc where cjxsdm = 01-oif and cshbs = t;此两个语句会对表clmbolc执行两次表的扫描，使用decode函数可改成一次表扫描select count(decode(cshbs/0l,null) counto,counl(decode(cshbs/1 ,null) countlfrom clmbolc where cjxsdm = '01-01f' and cshbs in ('o',t

20、)十五、尽量少用子查询特别是相关子查询。因为这样会导致效率下降。一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询 屮的列值改变z后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因 此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多 的行例：select a.njsje,(select b.nfpsl from mdac22 b where b.cjxsdm = a.cjxsdm ) nfpsl from clmbolc a这样写法当然是不好啦，可改成关联，怎么写自己看吧十六、sql语句拆分对于某些情况，我们需要从多个表关联取出某些数据，通常习惯于写一个sql语 句，然后取出相关数据。有时将sql语句分解，可能从效率上会有很大改观 示例：select denterdate , nmileage from vd_dsvcb20c_all where id =( select min ( id ) from vd_dsvc