常用SQL优化技巧

优化模式包括Rule, Choose, First Rows, All Rows这四种方式:

Rule: 即RBO方式;
Choose: Oracle默认使用的便是这种方式。指的是当一个表或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。为了避免那些不必要的全表扫描（full table scan），你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器
First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。(CBO优化方式,提供一个最快的反应时间,根据系统的需求,使用情况)
All Rows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。 (CBO优化方式,提供最大的吞吐量,就是使执行总量达到最大) .

正确使用索引：

索引是一种特殊的表，存放索引键值和ROWID,用来提高检索表数据的效率。通过索引查找数据时先通过关键字的值找到对应的索引条目，然后根据ROWID来快速的定位表的记录，所以通过索引查找时有额外的I/O操作，如果要检索的字段已经包含在索引字段中，则不需要再通过ROWID到表中找出对应行的数据，减少I/O量。通常通过索引查询数据比全表扫描要快，但检索的数据量超过一定的比例时（通常为表的记录数10%），索引查找会比全表扫描的效率更低，所以不能简单的以是否使用了索引来判断SQL语句性能好坏，要具体的分析。

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

索引需要空间存储;
对表进行INSERT 、DELETE 、UPDATE操作时，索引也会被修改，有额外的磁盘I/O .
所以索引不是越多越好，关键是能否正确的使用,以下是使用索引的一些原则:

（1）唯一索引比非唯一索引的效率高（索引列和常量比较时），在唯一索引和非唯一索引都存在的情况下，ORACLE会优先选择唯一索引，两个都是非唯一索引的情况下，ORACLE优先选择可选择性高的索引;

（2）在索引列进行计算将不能使用索引;

如：

select * from staff_member where salary*2<10000;

select * from staff_member where upper(first_name)=‘TERRY’;

（3）参数值和索引列数据类型不一致将不会使用索引

如：

select * from gsm_user where date14=20110338010203;

注：date14为char(14);

（4）索引中不存储NULL值(复合索引每列均为NULL时),使用NULL将不会使用索引

如：

select * from staff_member　where address is [not] null；

（5）在索引列上使用<>(!=)和 like和not 将不会使用索引

如：select * from staff_member where dept_no<>2001;

select * from staff_member where first_name like ‘%DON’;

注: select * from staff_member where first_name like ‘DON%’;

当前面没有%时，将使用索引，ORACLE内部转换为范围操作

（6）OR不能使用索引，可以用UNION (ALL)替换OR

如：select * from table1 where is_valid = 1 and (user_id = :b0 or dest_user_id = :b0)

注：即使user_id和dest_user_id上都有索引,可能都不会被使用

可以改成:

Select * from table1 where is_valid = 1 and user_id = :b0

Union all

Select * from table1 where is_valid = 1 and dest_user_id = :b0

建立索引常用的规则如下：

表的主键、外键必须有索引；
数据量超过300的表应该有索引；
经常与其他表进行连接的表，在连接字段上应该建立索引；
经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
索引应该建在选择性高的字段上；
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
频繁进行数据操作的表，不要建立太多的索引；
删除无用的索引，避免对执行计划造成负面影响；

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

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

首先，扫描第一个表（FROM子句中最后的那个表）并对记录进行排序，

然后扫描第二个表（FROM子句中最后第二个表），

最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。

例如：

表 TAB1 16，384 条记录
表 TAB2 1 条记录

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

select count（*） from tab1，tab2 执行时间0.96秒

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

select count（*） from tab2，tab1 执行时间26.09秒

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

2. 用>=替代>

如果在ID列上建有索引，则语句SELECT * FROM EMPLOYEE WHERE ID >= 9 要比语句SELECT * FROM EMPLOYEE WHERE ID > 8高效。这是由于前者DBMS将直接跳到第一个ID等于9的记录而后者将首先定位到8的记录并且向前扫描到第一个DEPT大于9的记录。特别对于在同一个ID上有多条记录的情况。

3. WHERE子句中的连接顺序

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

例如：

（低效，执行时间156.3秒）

SELECT … FROM EMP E
WHERE SAL > 50000
AND    JOB = ‘MANAGER’
AND    25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);
(高效,执行时间10.6秒)
SELECT … FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO)
AND    SAL > 50000
AND    JOB = ‘MANAGER’;

4. SELECT子句中避免使用 ‘ * ’

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

5. 减少访问数据库的次数

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

例如，以下有三种方法可以检索出雇员号等于0342或0291的职员。

方法1 （最低效）

            SELECT EMP_NAME , SALARY , GRADE FROM EMP
            WHERE EMP_NO = 342;
            SELECT EMP_NAME , SALARY , GRADE
            FROM EMP
            WHERE EMP_NO = 291;
方法2 （次低效）

       DECLARE CURSOR C1 (E_NO NUMBER) IS
            SELECT EMP_NAME,SALARY,GRADE
            FROM EMP
            WHERE EMP_NO = E_NO;
            BEGIN
            OPEN C1(342);
            FETCH C1 INTO …,..,.. ;
            OPEN C1(291);
       FETCH C1 INTO …,..,.. ;
        CLOSE C1;
            END;
方法3 （高效）

    SELECT A.EMP_NAME , A.SALARY , A.GRADE,
            B.EMP_NAME , B.SALARY , B.GRADE
    FROM EMP A,EMP B
    WHERE A.EMP_NO = 342
    AND   B.EMP_NO = 291;

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

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

例如：

            SELECT COUNT(*)，SUM(SAL)  FROM　EMP
            WHERE DEPT_NO = 0020
            AND ENAME LIKE　‘SMITH%’;
            SELECT COUNT(*)，SUM(SAL) FROM　EMP
            WHERE DEPT_NO = 0030
            AND ENAME LIKE　‘SMITH%’;
　         你可以用DECODE函数高效地得到相同结果

SELECT COUNT(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
    FROM EMP WHERE ENAME LIKE ‘SMITH%’;
　类似的，DECODE函数也可以运用于GROUP BY 和ORDER BY子句中。

7. 尽量用ROWID进行表访问

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

DELETE FROM EMP E
WHERE E.ROWID >

(SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

8. 用TRUNCATE替代DELETE

当删除表中的记录时，在通常情况下，回滚段（rollback segments）用来存放可以被恢复的信息。如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态（准确地说是恢复到执行删除命令之前的状况）

而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。

（注:TRUNCATE只在删除全表适用，TRUNCATE是DDL不是DML）

9. 合理使用COMMIT

当语句COMMIT后,LGWR会把redo log buffer中的数据写入日志文件，同时释放以下资源:

回滚段上用于恢复数据的信息.
被程序语句获得的锁
redo log buffer 中的空间
ORACLE为管理上述3种资源中的内部花费

因为写日志文件会产生物理I/O,所以如果太频繁的COMMIT会导致物理I/O增大，同时会产生　log file sync等待，但长时间不提交将带来更多的性能问题。建议小于3秒的事务可以一次提交，大于3秒的操作尽可能3秒左右提交一次。实际应用中使用COMMIT时必须保证事务的完整性。

10. 用Where子句替换HAVING子句

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

例如：

　低效：

            SELECT REGION，AVG(LOG_SIZE) FROM LOCATION
            GROUP BY REGION
            HAVING REGION REGION != ‘SYDNEY’
            AND REGION != ‘PERTH’
　         高效

            SELECT REGION，AVG(LOG_SIZE) FROM LOCATION
            WHERE REGION REGION != ‘SYDNEY’
            AND REGION != ‘PERTH’
            GROUP BY REGION
            （注：HAVING 中的条件一般用于对一些集合函数的比较，如COUNT（）等等。除此而外，一般的条件应该写在WHERE子句中）

11. 减少对表的查询

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

例如：

　低效

SELECT TAB_NAME FROM TABLES
WHERE TAB_NAME =

( SELECT TAB_NAME
                                FROM TAB_COLUMNS
                                WHERE VERSION = 604)
          AND　DB_VER= ( SELECT DB_VER
                           FROM TAB_COLUMNS
                           WHERE VERSION = 604)

高效

        SELECT TAB_NAME       FROM TABLES
          WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER)
                   FROM TAB_COLUMNS
                   WHERE VERSION = 604)
            Update 多个Column 例子：

　低效：

        UPDATE EMP
           SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
              SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
           WHERE EMP_DEPT = 0020;
　高效：

          UPDATE EMP
          SET (EMP_CAT, SAL_RANGE)
          = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
          FROM EMP_CATEGORIES)
           WHERE EMP_DEPT = 0020;

12. 通过内部函数提高SQL效率

13. 使用表的别名（Alias）

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

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

14. 用EXISTS替代IN

在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS（或NOT EXISTS）通常将提高查询的效率。

低效：

SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘MELB’)

高效：

SELECT * FROM EMP (基础表)
WHERE EMPNO > 0
AND EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB’)

（注：相对来说，用NOT EXISTS替换NOT IN 将更显著地提高效率，下一节中将指出）

15. 用NOT EXISTS替代NOT IN

在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的（因为它对子查询中的表执行了一个全表遍历）。为了避免使用NOT IN ，我们可以把它改写成外连接（Outer Joins）或NOT EXISTS.

例如：

SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO
FROM DEPT
WHERE DEPT_CAT=’A’);
　为了提高效率。改写为：

　（方法一：高效）

SELECT ….
FROM EMP A,DEPT B
WHERE A.DEPT_NO = B.DEPT(+)
AND B.DEPT_NO IS NULL
AND B.DEPT_CAT(+) = ‘A’
　（方法二：最高效）

SELECT ….
FROM EMP E
WHERE NOT EXISTS (SELECT ‘X’
                    FROM DEPT D
                    WHERE D.DEPT_NO = E.DEPT_NO
                    AND DEPT_CAT = ‘A’);

16. 用表连接替换EXISTS

　通常来说，采用表连接的方式比EXISTS更有效率

      SELECT ENAME
      FROM EMP E
      WHERE EXISTS (SELECT ‘X’
                      FROM DEPT
                      WHERE DEPT_NO = E.DEPT_NO
                      AND DEPT_CAT = ‘A’);
　（更高效）

      SELECT ENAME
      FROM DEPT D,EMP E
      WHERE E.DEPT_NO = D.DEPT_NO
      AND DEPT_CAT = ‘A’ ;
　（译者按：在RBO的情况下，前者的执行路径包括FILTER，后者使用NESTED LOOP）

17. 用EXISTS替换DISTINCT

　当提交一个包含一对多表信息（比如部门表和雇员表）的查询时，避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换

　例如：

　低效：

   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);
　EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。

18. 用UNION-ALL 替换UNION

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

如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高.但需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此大家还是要从业务需求分析使用UNION ALL的可行性.

19. 避免使用耗费资源的操作

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY,GROUP BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能,如果可能，应尽量避免使用或改写，以下是ORACLE执行查询时的一些内部操作(从执行计划中可以看到),有”SORT”关键字的都是需要排序的操作：

ORACLE Clause	内部操作
ORDER BY	SORT ORDER BY
UNION	UNION-ALL
MINUS	MINUS
INTERSECT	INTERSECT
DISTINCT,MINUS,INTERSECT,UNION	SORT UNIQUE
MIN,MAX,COUNT	SORT AGGREGATE
GROUP BY	SORT GROUP BY
ROWNUM	COUNT or COUNT STOPKEY
QUERIES INVOLVING JOIONS	SORT JOIN,MERGE JOIN,NESTED LOOPS
CONNECT BY	CONNECT BY

20. 避免不必要的类型转换

需要注意的是，尽量避免潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致全表扫描。

例2：表tab1中的列col1是字符型（char)，则以下语句存在类型转换：

select col1,col2 from tab1 where col1>10，

应该写为： select col1,col2 from tab1 where col1>'10'。

21. 增加查询的范围限制

增加查询的范围限制，避免全范围的搜索。

例3：以下查询表record 中时间ActionTime小于2001年3月1日的数据：

select * from record where ActionTime < to_date ('20010301' ,'yyyymm')

查询计划表明，上面的查询对表进行全表扫描，如果我们知道表中的最早的数据为2001年1月1日，那么，可以增加一个最小时间，使查询在一个完整的范围之内。修改如下： select * from record where

ActionTime < to_date ('20010301' ,'yyyymm')

and ActionTime > to_date ('20010101' ,'yyyymm')

后一种SQL语句将利用上ActionTime字段上的索引，从而提高查询效率。

22. 尽量去掉"IN"、"OR"

含有"IN"、"OR"的Where子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。

例4： select count(*) from stuff where id_no in('0','1')（23秒）

可以考虑将or子句分开：

select count(*) from stuff where id_no='0'

select count(*) from stuff where id_no='1'

exist -- ？

然后再做一个简单的加法，与原来的SQL语句相比，查询速度更快。

23. 尽量去掉 "<>"

尽量去掉 "<>"，避免全表扫描，如果数据是枚举值，且取值范围固定，则修改为"OR"方式。

例5：

UPDATESERVICEINFOSETSTATE=0 WHERESTATE<>0;

以上语句由于其中包含了"<>"，执行计划中用了全表扫描（TABLE ACCESS FULL），没有用到state字段上的索引。实际应用中，由于业务逻辑的限制，字段state为枚举值，只能等于0，1或2，而且，值等于=1，2的很少，因此可以去掉"<>"，利用索引来提高效率。

修改为：UPDATE SERVICEINFO SET STATE=0 WHERE STATE = 1 OR STATE = 2 。进一步的修改可以参考第4种方法。

24. 去掉Where子句中的IS NULL和IS NOT NULL

Where字句中的IS NULL和IS NOT NULL将不会使用索引而是进行全表搜索，因此需要通过改变查询方式，分情况讨论等方法，去掉Where子句中的IS NULL和IS NOT NULL。

25. like子句尽量前端匹配

因为like参数使用的非常频繁，因此如果能够对like子句使用索引，将很高的提高查询的效率。

例6：select * from city where name like ‘%S%’[u1]

以上查询的执行计划用了全表扫描（TABLE ACCESS FULL），如果能够修改为：

select * from city where name like ‘S%’

那么查询的执行计划将会变成（INDEX RANGE SCAN），成功的利用了name字段的索引。这意味着Oracle SQL优化器会识别出用于索引的like子句，只要该查询的匹配端是具体值。因此我们在做like查询时，应该尽量使查询的匹配端是具体值，即使用like ‘S%’。

26. 用Case语句合并多重扫描

我们常常必须基于多组数据表计算不同的聚集。例如下例通过三个独立查询：

例8：1）select count(*) from emp where sal<1000;

2）select count(*) from emp where sal between 1000 and 5000;

3）select count(*) from emp where sal>5000;

这样我们需要进行三次全表查询，但是如果我们使用case语句：

select

count (sale when sal <1000

then 1 else null end) count_poor,

count (sale when between 1000 and 5000

then 1 else null end) count_blue_collar,

count (sale when sal >5000

then 1 else null end) count_poor

from emp;

这样查询的结果一样，但是执行计划只进行了一次全表查询。

27. 使用分区索引

在用分析命令对分区索引进行分析时，每一个分区的数据值的范围信息会放入Oracle的数据字典中。Oracle可以利用这个信息来提取出那些只与SQL查询相关的数据分区。

例如，假设你已经定义了一个分区索引，并且某个SQL语句需要在一个索引分区中进行一次索引扫描。Oracle会仅仅访问这个索引分区，而且会在这个分区上调用一个此索引范围的快速全扫描。因为不需要访问整个索引，所以提高了查询的速度。

28. 决定使用全表扫描还是使用索引

和所有的秘笈一样，最后一招都会又回到起点，最后我们来讨论一下是否需要建立索引，也许进行全表扫描更快。在大多数情况下，全表扫描可能会导致更多的物理磁盘输入输出，但是全表扫描有时又可能会因为高度并行化的存在而执行的更快。如果查询的表完全没有顺序，那么一个要返回记录数小于10％的查询可能会读取表中大部分的数据块，这样使用索引会使查询效率提高很多。但是如果表非常有顺序，那么如果查询的记录数大于40％时，可能使用全表扫描更快。因此，有一个索引范围扫描的总体原则是：

1）对于原始排序的表，仅读取少于表记录数40％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的40％的查询应该使用全表扫描。

2）对于未排序的表，仅读取少于表记录数7％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的7％的查询应该使用全表扫描。

发表于 2019-07-16 15:34 Mecell 阅读(222) 评论(0) 编辑收藏举报

刷新页面返回顶部