ORACLE-SQL性能优化

SQL语句优化是提高性能的重要环节

n  开发人员不能只注重功能的实现，不管性能如何

n  开发人员不能把Oracle当成一个黑盒子，必须了解其结构、处理SQL和数据的方法

n  必需遵守既定的开发规范

访问数据表的方式

①全表扫描

全表扫描就是顺序地访问表中每条记录。Oracle采用一次读入多个数据块(database block)的方式优化全表扫描。

②通过ROWID访问表

ROWID包含了表中记录的物理位置信息。可以采用基于ROWID的访问方式情况提高访问表的效率。Oracle采用索引实现了数据和存放数据的物理位置(ROWID)之间的联系通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能的提高。

SQL Tunning 的重点

n  SQL: insert, update, delete, select;

n  主要关注的是select

n  关注的是：如何用最小的硬件资源消耗、最少的响应时间定位数据位置

SQL Tunning Tips

sql 语句的编写原则和优化

随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于大量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。

sql 语句的编写原则和优化

n  在编写SQL语句时我们应清楚优化器根据何种原则来使用索引，这有助于写出高性能的SQL语句。

n  SQL语句的编写原则和SQL语句的优化，请跟我一起学习以下几方面：

1.不要让Oracle做得太多；

2.给优化器更明确的命令；

3.减少访问次数；

4.细节上的影响；

一、不要让Oracle做得太多

1、避免复杂的多表关联，如：

select …from user_files uf, df_money_files dm, cw_charge_record cc

where uf.user_no = dm.user_no

and dm.user_no = cc.user_no

and ……

and not exists(select …)

2、避免使用“*”

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用‘*’是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将‘*’依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意

味着将耗费更多的时间；

3、避免使用耗费资源的操作

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.例如,一个UNION查询,其中每个查询都带有GROUP BY子句, GROUP BY会触发嵌入排序(NESTED SORT) ;这样, 每个查询需要执行一次排序, 然后在执行UNION时, 又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行. 嵌入的排序的深度会大大影响查询的效率.通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写.

4、用EXISTS替换DISTINCT，例如:

低效:

    SELECT DISTINCT DEPT_NO,DEPT_NAME

    FROM DEPT D,EMP E

    WHERE D.DEPT_NO = E.DEPT_NO

高效:

    SELECT DEPT_NO,DEPT_NAME

    FROM DEPT D

    WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

用UNION-ALL 替换UNION ( if possible)

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

低效：

　　 SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

        UNION

        SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

高效:

        SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

        UNION ALL

        SELECT ACCT_NUM, BALANCE_AMT

        FROM DEBIT_TRANSACTIONS

        WHERE TRAN_DATE = ’31-DEC-95’

二、给优化器更明确的命令

                   1、避免在索引列上使用计算函数

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

低效：

SELECT … FROM DEPT WHERE SAL * 12 > 25000;

高效:

SELECT … FROM DEPT WHERE SAL > 25000/12;

2、避免使用前置通配符

WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用.

SELECT USER_NO,USER_NAME,ADDRESS FROM USER_FILES WHERE USER_NO LIKE '%109204421';

                 在这种情况下，ORACLE将使用全表扫描.

         在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：

select * from employee where last_name like 'c%';

                   3、避免在索引列上使用NOT

通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响.当ORACLE遇到NOT,他就会停止使用索引转而执行全表扫描.举例:

低效: (这里,不使用索引)

   SELECT … FROM DEPT WHERE DEPT_CODE NOT = 0;

高效: (这里,使用了索引)

   SELECT … FROM DEPT WHERE DEPT_CODE > 0;

                   4、避免在索引列上使用 IS NULL和IS NOT NULL

         避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.　如果至少有一个列不为空，则记录存在于索引中．如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

三、减少访问次数

         当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.类比，工程实施。

四、细节上的影响

         1、WHERE子句中的连接顺序

           ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理, 当在WHERE子句中有多个表联接时，WHERE子句中排在最后的表应当是返回行数可能最少的表，有过滤条件的子句应放在WHERE子句中的最后。

n  如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：

select * from emp e,dept d where d.deptno >10 and e.deptno =30 ;

n  如果dept表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多。

select * from emp e,dept d where e.deptno =30 and d.deptno >10 ;

2、WHERE子句 ——函数、表达式使用

最好不要在WHERE子句中使用函或表达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以后使用合理的索引

3、Order by语句

n  ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

n  仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

4、用>= 替代 >

如果DEPTNO上有一个索引。

         高效:

          SELECT * ROM EMP HERE DEPTNO >=4

         低效:

        SELECT * ROM EMP HERE DEPTNO >3

5、通过使用>=、<=等，避免使用NOT命令

例子：select * from employee where salary <> 3000;

对这个查询，可以改写为不使用NOT：

select * from employee where salary<3000 or salary>3000;

         虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。