程序SQL语句优化.docx

程序SQL语句优化.docx

《程序SQL语句优化.docx》由会员分享，可在线阅读，更多相关《程序SQL语句优化.docx（16页珍藏版）》请在冰点文库上搜索。

程序SQL语句优化

程序SQL语句优化

1）选用适合的ORACLE优化器

ORACLE的优化器共有3种：

⏹RULE（基于规则）

⏹COST（基于成本）

⏹CHOOSE（选择性）

设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS。

为了使用基于成本的优化器（CBO，Cost-BasedOptimizer），必须经常运行analyze命令，以增加数据库中的对象统计信息（objectstatistics）的准确性。

如果数据库的优化器模式设置为选择性（CHOOSE），那么实际的优化器模式将和是否运行过analyze命令有关.如果table已经被analyze过，优化器模式将自动成为CBO，反之，数据库将采用RULE形式的优化器。

在缺省情况下，ORACLE采用CHOOSE优化器，为了避免那些不必要的全表扫描（fulltablescan），必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。

2）访问Table的方式

ORACLE采用两种访问表中记录的方式：

⏹全表扫描。

全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块（databaseblock）的方式优化全表扫描。

⏹通过ROWID访问表。

可以采用基于ROWID的访问方式情况，提高访问表的效率，ROWID包含了表中记录的物理位置信息..ORACLE采用索引（INDEX）实现了数据和存放数据的物理位置（ROWID）之间的联系。

通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。

4）选择最有效率的表名顺序（只在基于规则的优化器中有效）

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表drivingtable）将被最先处理.在FROM子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时，会运用排序及合并的方式连接它们。

首先，扫描第一个表（FROM子句中最后的那个表）并对记录进行派序，然后扫描第二个表（FROM子句中最后第二个表），最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。

例如：

表TAB116，384条记录表TAB21条记录

选择TAB2作为基础表（最好的方法）

selectcount（*）fromtab1，tab2执行时间0.96秒

选择TAB1作为基础表（不佳的方法）

selectcount（*）fromtab2，tab1执行时间26.09秒

如果有3个以上的表连接查询，那就需要选择交叉表（intersectiontable）作为基础表，交叉表是指那个被其他表所引用的表.

例如：

EMP表描述了LOCATION表和CATEGORY表的交集.

SELECT*FROMLOCATIONL，CATEGORYC，EMPEWHEREE.EMP_NOBETWEEN1000AND2000ANDE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCN

将比下列SQL更有效率

SELECT*FROMEMPE，LOCATIONL，CATEGORYCWHEREE.CAT_NO=C.CAT_NO

ANDE.LOCN=L.LOCNANDE.EMP_NOBETWEEN1000AND2000

5）WHERE子句中的连接顺序．

ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

例如：

（低效，执行时间156.3秒）

SELECT…FROMEMPEWHERESAL>50000ANDJOB=‘MANAGER’

AND25<（SELECTCOUNT（*）FROMEMPWHEREMGR=E.EMPNO）；

（高效，执行时间10.6秒）

SELECT…FROMEMPEWHERE25<（SELECTCOUNT（*）FROMEMP

WHEREMGR=E.EMPNO）ANDSAL>50000ANDJOB=‘MANAGER’；

6）减少访问数据库的次数

当执行每条SQL语句时，ORACLE在内部执行了许多工作：

解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。

由此可见，减少访问数据库的次数，就能实际上减少ORACLE的工作量。

例如，

以下有二种方法可以检索出雇员号等于0342或0291的职员.

方法1（低效）

SELECTEMP_NAME，SALARY，GRADEFROMEMPWHEREEMP_NO=342；

SELECTEMP_NAME，SALARY，GRADEFROMEMPWHEREEMP_NO=291；

方法2（高效）

SELECTA.EMP_NAME，A.SALARY，A.GRADE，B.EMP_NAME，B.SALARY，B.GRADEFROMEMPA，EMPBWHEREA.EMP_NO=342ANDB.EMP_NO=291；

8.使用DECODE函数来减少处理时间

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

例如：

SELECTCOUNT（*），SUM（SAL）FROM　EMPWHEREDEPT_NO=0020

ANDENAMELIKE　’SMITH%’；

SELECTCOUNT（*），SUM（SAL）FROM　EMPWHEREDEPT_NO=0030

ANDENAMELIKE　’SMITH%’；

可以用DECODE函数高效地得到相同结果

SELECTCOUNT（DECODE（DEPT_NO，0020，’X’，NULL））D0020_COUNT，

COUNT（DECODE（DEPT_NO，0030，’X’，NULL））D0030_COUNT，

SUM（DECODE（DEPT_NO，0020，SAL，NULL））D0020_SAL，

SUM（DECODE（DEPT_NO，0030，SAL，NULL））D0030_SAL

FROMEMPWHEREENAMELIKE‘SMITH%’；

类似的，DECODE函数也可以运用于GROUPBY和ORDERBY子句中。

9.整合简单，无关联的数据库访问

如果有几个简单的数据库查询语句，可以把它们整合到一个查询中（即使它们之间没有关系）例如：

SELECTNAMEFROMEMPWHEREEMP_NO=1234；

SELECTNAMEFROMDPTWHEREDPT_NO=10；

SELECTNAMEFROMCATWHERECAT_TYPE=‘RD’；

上面的3个查询可以被合并成一个：

SELECTE.NAME，D.NAME，C.NAMEFROMCATC，DPTD，EMPE，DUALX

WHERENVL（‘X’，X.DUMMY）=NVL（‘X’，E.ROWID（+））

ANDNVL（‘X’，X.DUMMY）=NVL（‘X’，D.ROWID（+））

ANDNVL（‘X’，X.DUMMY）=NVL（‘X’，C.ROWID（+））

ANDE.EMP_NO（+）=1234ANDD.DEPT_NO（+）=10ANDC.CAT_TYPE（+）=‘RD’：

10.删除重复记录

最高效的删除重复记录方法（因为使用了ROWID）

DELETEFROMEMPEWHEREE.ROWID>（SELECTMIN（X.ROWID）FROMEMPX

WHEREX.EMP_NO=E.EMP_NO）；

11.用TRUNCATE替代DELETE

当删除表中的记录时，在通常情况下，回滚段（rollbacksegments）用来存放可以被恢复的信息。

如果没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态（准确地说是恢复到执行删除命令之前的状况）而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息。

当命令运行后，数据不能被恢复.因此很少的资源被调用，执行时间也会很短。

12.尽量多使用COMMIT

只要有可能，在程序中尽量多使用COMMIT，这样程序的性能得到提高，需求也会因为COMMIT所释放的资源而减少。

COMMIT所释放的资源：

a.回滚段上用于恢复数据的信息.

b.被程序语句获得的锁

c.redologbuffer中的空间

d.ORACLE为管理上述3种资源中的内部花费

13.计算记录条数

和一般的观点相反，count（*）比count

（1）稍快，当然如果可以通过索引检索，对索引列的计数仍旧是最快的.例如COUNT（EMPNO）

14.用Where子句替换HAVING子句

避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序，总计等操作.如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销.

例如：

低效：

SELECTREGION，AVG（LOG_SIZE）FROMLOCATIONGROUPBYREGION

HAVINGREGIONREGION!

=‘SYDNEY’ANDREGION!

=‘PERTH’

高效

SELECTREGION，AVG（LOG_SIZE）FROMLOCATION

WHEREREGIONREGION!

=‘SYDNEY’ANDREGION!

=‘PERTH’GROUPBYREGION

15.减少对表的查询

在含有子查询的SQL语句中，要特别注意减少对表的查询。

例如：

低效

SELECTTAB_NAMEROMTABLESWHERETAB_NAME=（SELECTTAB_NAME

FROMTAB_COLUMNSWHEREVERSION=604）AND　DB_VER=（SELECTDB_VER

FROMTAB_COLUMNSWHEREVERSION=604）

高效

SELECTTAB_NAMEFROMTABLESWHERE（TAB_NAME，DB_VER）

=（SELECTTAB_NAME，DB_VER）FROMTAB_COLUMNSWHEREVERSION=604）

Update多个Column例子：

低效：

UPDATEEMPSETEMP_CAT=（SELECTMAX（CATEGORY）FROMEMP_CATEGORIES），

SAL_RANGE=（SELECTMAX（SAL_RANGE）FROMEMP_CATEGORIES）

WHEREEMP_DEPT=0020；

高效：

UPDATEEMPSET（EMP_CAT，SAL_RANGE）

=（SELECTMAX（CATEGORY），MAX（SAL_RANGE）FROMEMP_CATEGORIES）

WHEREEMP_DEPT=0020；

17.使用表的别名（Alias）

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上.这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

（Column歧义指的是由于SQL中不同的表具有相同的Column名，当SQL语句中出现这个Column时，SQL解析器无法判断这个Column的归属）

18.用EXISTS替代IN

在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。

在这种情况下，使用EXISTS（或NOTEXISTS）通常将提高查询的效率。

低效：

SELECT*FROMEMP（基础表）WHEREEMPNO>0ANDDEPTNOIN（SELECTDEPTNO

FROMDEPTWHERELOC=‘MELB’）

高效：

SELECT*FROMEMP（基础表）WHEREEMPNO>0ANDEXISTS（SELECT‘X’

FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=‘MELB’）

19.用NOTEXISTS替代NOTIN

在子查询中，NOTIN子句将执行一个内部的排序和合并。

无论在哪种情况下，NOTIN都是最低效的（因为它对子查询中的表执行了一个全表遍历）。

为了避免使用NOTIN，我们可以把它改写成外连接（OuterJoins）或NOTEXISTS。

例如：

SELECT…FROMEMPWHEREDEPT_NONOTIN（SELECTDEPT_NOFROMDEPT

WHEREDEPT_CAT=‘A’）；

为了提高效率.改写为：

（方法一：

高效）

SELECT….FROMEMPA，DEPTBWHEREA.DEPT_NO=B.DEPT（+）

ANDB.DEPT_NOISNULLANDB.DEPT_CAT（+）=‘A’

（方法二：

最高效）

SELECT….FROMEMPEWHERENOTEXISTS（SELECT‘X’FROMDEPTD

WHERED.DEPT_NO=E.DEPT_NOANDDEPT_CAT=‘A’）；

25.用索引提高效率

索引是表的一个概念部分，用来提高检索数据的效率。

实际上，ORACLE使用了一个复杂的自平衡B-tree结构。

通常，通过索引查询数据比全表扫描要快。

当ORACLE找出执行查询和Update语句的最佳路径时，ORACLE优化器将使用索引。

同样在联结多个表时使用索引也可以提高效率。

另一个使用索引的好处是，它提供了主键（primarykey）的唯一性验证。

除了那些LONG或LONGRAW数据类型，可以索引几乎所有的列。

通常，在大型表中使用索引特别有效。

当然，也会发现，在扫描小表时，使用索引同样能提高效率。

虽然使用索引能得到查询效率的提高，但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。

这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5次的磁盘I/O。

因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。

定期的重构索引是有必要的。

ALTERINDEXREBUILD

27.基础表的选择

基础表（DrivingTable）是指被最先访问的表（通常以全表扫描的方式被访问）。

根据优化器的不同，SQL语句中基础表的选择是不一样的。

如果使用的是CBO（COSTBASEDOPTIMIZER），优化器会检查SQL语句中的每个表的物理大小，索引的状态，然后选用花费最低的执行路径.如果用RBO（RULEBASEDOPTIMIZER），并且所有的连接条件都有索引对应，在这种情况下，基础表就是FROM子句中列在最后的那个表。

举例：

SELECTA.NAME，B.MANAGERFROMORKERA，LODGINGB

WHERE　A.LODGING=B.LODING；

由于LODGING表的LODING列上有一个索引，而且WORKER表中没有相比较的索引，WORKER表将被作为查询中的基础表。

32.避免在索引列上使用计算。

WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。

举例：

低效：

SELECT…ROMDEPTWHERESAL*12>25000；

高效：

SELECT…FROMDEPTWHERESAL>25000/12；

34.避免在索引列上使用NOT

通常，我们要避免在索引列上使用NOT，NOT会产生在和在索引列上使用函数相同的影响。

当ORACLE遇到NOT，他就会停止使用索引转而执行全表扫描。

举例：

低效：

（这里，不使用索引）

SELECT…FROMDEPTWHEREDEPT_CODENOT=0；

高效：

（这里，使用了索引）

SELECT…FROMDEPTWHEREDEPT_CODE>0；

35.用>=替代>

如果DEPTNO上有一个索引，

高效：

SELECT*FROMEMPWHEREDEPTNO>=4

低效：

SELECT*FROMEMPWHEREDEPTNO>3

两者的区别在于，前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.

36.用UNION替换OR（适用于索引列）

通常情况下，用UNION替换WHERE子句中的OR将会起到较好的效果。

对索引列使用OR将造成全表扫描。

注意，以上规则只针对多个索引列有效。

如果有column没有被索引，查询效率可能会因为没有选择OR而降低。

在下面的例子中，LOC_ID和REGION上都建有索引。

高效：

SELECTLOC_ID，LOC_DESC，REGIONFROMLOCATIONWHERELOC_ID=10

UNION

SELECTLOC_ID，LOC_DESC，REGIONFROMLOCATION

WHEREREGION=“MELBOURNE”

低效：

SELECTLOC_ID，LOC_DESC，REGIONFROMLOCATION

WHERELOC_ID=10ORREGION=“MELBOURNE”

如果坚持要用OR，那就需要返回记录最少的索引列写在最前面。

注意：

WHEREKEY1=10（返回最少记录）

ORKEY2=20（返回最多记录）

ORACLE内部将以上转换为WHEREKEY1=10AND（（NOTKEY1=10）ANDKEY2=20）

37.用IN来替换OR

下面的查询可以被更有效率的语句替换：

低效：

SELECT….FROMLOCATIONWHERELOC_ID=10ORLOC_ID=20ORLOC_ID=30

高效

SELECT…FROMLOCATIONWHERELOC_IDIN（10，20，30）；

38.避免在索引列上使用ISNULL和ISNOTNULL

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。

对于单列索引，如果列包含空值，索引中将不存在此记录。

对于复合索引，如果每个列都为空，索引中同样不存在此记录。

如果至少有一个列不为空，则记录存在于索引中。

举例：

如果唯一性索引建立在表的A列和B列上，并且表中存在一条记录的A，B值为（123，null），ORACLE将不接受下一条具有相同A，B值（123，null）的记录（插入）。

然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空。

因此可以插入1000条具有相同键值的记录，当然它们都是空!

因为空值不存在于索引列中，所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。

举例：

低效：

（索引失效）

SELECT…FROMDEPARTMENTWHEREDEPT_CODEISNOTNULL；

高效：

（索引有效）

SELECT…FROMDEPARTMENTWHEREDEPT_CODE>=0；

41.用UNION-ALL替换UNION（如果有可能的话）

当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。

如果用UNIONALL替代UNION，这样排序就不是必要了.效率就会因此得到提高。

举例：

低效：

SELECTACCT_NUM，BALANCE_AMTFROMDEBIT_TRANSACTIONS

WHERETRAN_DATE=‘31-DEC-95’

UNION

SELECTACCT_NUM，BALANCE_AMTFROMDEBIT_TRANSACTIONS

WHERETRAN_DATE=‘31-DEC-95’

高效：

SELECTACCT_NUM，BALANCE_AMTFROMDEBIT_TRANSACTIONS

WHERETRAN_DATE=‘31-DEC-95’

UNIONALL

SELECTACCT_NUM，BALANCE_AMTFROMDEBIT_TRANSACTIONS

WHERETRAN_DATE=‘31-DEC-95’

需要注意的是，UNIONALL将重复输出两个结果集合中相同记录。

因此还是要从业务需求分析使用UNIONALL的可行性。

NION将对结果集合排序，这个操作会使用到SORT_AREA_SIZE这块内存.对于这块内存的优化也是相当重要的。

下面的SQL可以用来查询排序的消耗量

Selectsubstr（name，1，25）"SortAreaName"，substr（value，1，15）"Value"fromv$sysstat

wherenamelike‘sort%’

43.用WHERE替代ORDERBY

ORDERBY子句只在两种严格的条件下使用索引。

ORDERBY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。

ORDERBY中所有的列必须定义为非空。

WHERE子句使用的索引和ORDERBY子句中所使用的索引不能并列。

例如：

表DEPT包含以下列：

DEPT_CODEPKNOTNULL

DEPT_DESCNOTNULL

DEPT_TYPENULL

非唯一性的索引（DEPT_TYPE）

低效：

（索引不被使用）

SELECTDEPT_CODEFROMDEPTORDERBYDEPT_TYPE

高效：

（使用索引）

SELECTDEPT_CODEFROMDEPTWHEREDEPT_TYPE>0

45.需要当心的WHERE子句

某些SELECT语句中的WHERE子句不使用索引。

这里有一些例子。

在下面的例子里，’!

=‘将不