数据库优化总结

一、

二、

三、

四、

五、

六、

七、

八、

九、

十、

十一、

十二、

十三、

十四、

十五、

十六、

十七、

十八、 前言 ................................................................................................................... 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 E

WHERE 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，并保存子查询使用索引

对于NOT EXISTS和NOT IN一定要选用NOT EXISTS，还可使用（外连接＋判断为空）

七、 使用索引

对于数据量大的表一定要建立适当的索引，正确的使用索引可以级大的提高数据检索效率，在写SQL语句时要考虑表索引的使用，但索引的缺点一是增加物理空间的占用，二是对表的增、删、改会增加一次索引的增、删、改操作，所以对于大表要建立并使用索引，但不能过多

使用索引要注意以下几点：

1． 要按索引列构造SQL语句的where条件才能使用到索引

2． 使用不等于操作符则不能使用索引

例：select col1,col2 from table1 where col1 <> ‘AA’

如果列col1是索引则此写法不能使用索引，同样还有not in (‘AA’)

那出现这种问题如何解决呢？

那就要改变这种写法，比如判断的条件值有‘AA’，‘BB’，‘CC’

则条件改写成col1 = ‘BB’ or col1 = ‘CC’

3． 使用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 = 88383

Col1是索引列，类型是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_dczrq

Create 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乖乖使用了函数索引，看来它还是听话的?

12．

对于数据量比较大并且频繁使用的表的索引需要定期进行重建rebuiled

八、 UNION与UNION ALL的使用

这两个操作符均是对结果集的操作，合并两个结果集，不同的是UNION操作会将两个结算集中的重复记录去除掉之后再合，而UNION ALL不管你记录啥样就是合

因此UNION会比UNION ALL增加大量的计算，所以不是必要的情况下一定不要使用UNION，而使用UNION ALL

九、 驱动表的选择

如果两个表关联（指内联接的通用写法），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 clmb20

Group by cjxsdm

Having cjxsdm = ’09-23F’

此种语句一定要避免，要改成如下写法

select cjxsdm,count(njsje) from clmb20

where cjxsdm = ’09-23F’

Group 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)) count1 From 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 nvehicleid =:1 and crepairstatus < '4' )

分析一下这个语句，是从视图vd_dsvcb20c_all中取出满足条件的最小ID值，然后按这个ID再取出vd_dsvcb20c_all中所有记录，经解释分析此语句消耗资源非常多，两个视图均没有使用到索引

可改成分两个语句取出结果

第一步，先取出满足条件的最小ID

select min ( id ) into :ID from vd_dsvcb20c_all where nvehicleid =:1 and crepairstatus < '4' 并加了个索引，如果原语句不动，只是加索引，则外面的语句还是不能使用到索引，原因不明 第二步，然后再按这个ID取出对应的所有记录 select denterdate , nmileage from vd_dsvcb20c_all where id =:ID

十七、 SQL语句中参数的使用

1．使用查询语句时，查询语句中所使用到的变量条件最好直接使用变量，不要经

过计算

例如:

通常对于模糊查询我们可能会使用如下形式

Select * from mdac22 a

Where a.cjxsdm like v_jxsdm||’%’;

如果这个语句慢，可以改成如下形式

V_jxsdm_01 := v_jxsdm||’%’;

Select * from mdac22 a

Where a.cjxsdm like v_jxsdm_01;

2．

十八、 解释计划

使用