8.3 SQL优化

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

  在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。例如，假设在SQL语句的WHERE子句中写的SQL代码不合理，就会造成优化器忽略索引而使用全表扫描，类似这样的语句就是所谓的劣质SQL语句。因此，在编写SQL语句时应清楚优化器根据何种原则来使用索引，这有助于写出高性能的SQL语句。

8.3.1 不要让Oracle做得太多

  Oracle做得越多，性能损耗就越大。编写SQL时，应该尽力避免这种损耗，常见方法有以下五种。

  • 避免复杂的多表关联

  先来看以下代码：

  1. SELECT …
  3. FROM user_files uf, df_money_files dm, cw_charge_record cc
  5. WHERE uf.user_no = dm.user_no
  7. AND dm.user_no = cc.user_no
  9. AND …
  11. AND NOT EXISTS(SELECT …)

  像这样烦琐的SQL语句是很难优化的，因此在编写SQL时，要避免复杂的多表关联。

  • 避免使用“*”

  当使用SELECT语句查询所有列时，使用SELECT 是一个方便的方法，但实际上这是一个非常低效的方法。因为Oracle在解析的过程中，会将“”依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。因此建议查询时只提取要使用的列，并且使用别名，使用别名能够加快解析速度。

  • 避免使用耗费资源的操作。

  带有DISTINCT、UNION、MINUS、INTERSECT、ORDER BY的SQL语句，会启动SQL引擎去执行耗费资源的排序功能。其中， DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序。

  例如，有一个UNION查询，并且连接的每个查询都带有GROUP BY子句，而GROUP BY会触发嵌入排序（NESTED SORT）。这样，每个查询都需要执行一次排序，然后执行UNION时又会再执行唯一排序（SORT UNIQUE），而且唯一排序只能在前面的嵌入排序结束后才能开始执行，因此嵌入排序的深度会大大影响查询的效率。

  一般来讲，带有UNION、MINUS、INTERSECT的SQL语句都应该改写为其他形式。

  • 用EXISTS替换DISTINCT

  当提交一个包含一对多表信息的查询时（比如部门表和雇员表），避免在SELECT子句中使用DISTINCT。一般可以考虑用EXISTS替换，EXISTS会使查询更为迅速，因为RDBMS核心模块将在子查询的条件满足后，立刻返回结果。

  低效：

  1. SELECT DISTINCT DEPT_NO, DEPT_NAME
  3. FROM DEPT D, EMP E
  5. WHERE D.DEPT_NO = E.DEPT_NO

  高效：

  1. SELECT DEPT_NO, DEPT_NAME
  3. FROM DEPT D
  5. WHERE EXISTS (SELECT ‘X’
  7. ​                FROM EMP E
  9. ​                WHERE E.DEPT_NO = D.DEPT_NO);
  • 用UNION ALL替换UNION

  当SQL 语句使用UNION拼接两个查询结果集合时，这两个结果集合会先以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。如果用UNION ALL替代UNION，这样排序就不是必要的了，效率就会因此得到提高。

  低效：

​ 

  1. SELECT ACCT_NUM, BALANCE_AMT
  3. FROM DEBIT_TRANSACTIONS
  5. WHERE TRAN_DATE = '21-DEC-85'
  7. UNION
  9. SELECT ACCT_NUM, BALANCE_AMT
  11. FROM DEBIT_TRANSACTIONS
  13. WHERE TRAN_DATE = '21-DEC-85'

  高效：

  1. SELECT ACCT_NUM, BALANCE_AMT
  3. FROM DEBIT_TRANSACTIONS
  5. WHERE TRAN_DATE = '21-DEC-85'
  7. UNION ALL
  9. SELECT ACCT_NUM, BALANCE_AMT
  11. FROM DEBIT_TRANSACTIONS
  13. WHERE TRAN_DATE = '21-DEC-85'

8.3.2 给优化器更明确的命令

  发送给优化器的命令越明确，执行效率就越高。优化器是如何选择并执行命令的呢？可参考以下六点。

  • 自动选择索引

  如果表中有两个或两个以上索引，并且其中有一个唯一性索引，其他是非唯一性的，此时，Oracle将使用唯一性索引而完全忽略非唯一性索引。

  例如：

  1. SELECT ENAME
  3. FROM EMP
  5. WHERE EMPNO = 2326  
  7. AND DEPTNO = 20 ;

  假定只有EMPNO上的索引是唯一性的，那么该唯一性索引将用来检索记录。

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

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

  低效：

​ 

  1. SELECT …
  3. FROM DEPT
  5. WHERE SAL* 12 > 25000;

  高效：

​ 

  1. SELECT …
  3. FROM DEPT
  5. WHERE SAL> 25000/12;
  • 避免在索引列上使用NOT

  与第（2）点相同，在索引列中使用NOT也会使得优化器进行全表扫描，即当Oracle遇到NOT时，就会停止使用索引转而执行全表扫描。

  低效：

​ 

  1. SELECT …
  3. FROM DEPT
  5. WHERE NOT DEPT_CODE = 0;

  高效：

  1. SELECT …
  3. FROM DEPT
  5. WHERE DEPT_CODE > 0;
  • 避免使用前置通配符

  在WHERE子句中，如果索引列所对应的值的第一个字符以通配符开始，索引将被忽略。

  例如：

​ 

  1. SELECT USER_NO, USER_NAME, ADDRESS
  3. FROM USER_FILES
  5. WHERE USER_NO LIKE  '%109204421';

  在这种情况下，Oracle将进行全表扫描。

  • 避免使用IS NULL

  如果在WHERE子句中使用IS NULL或IS NOT NULL，那么优化器也是会忽略索引的。

  • 避免出现索引列自动转换

  当比较不同数据类型的数据时，Oracle会自动对列进行简单的类型转换。

  如下，假设USER_NO是一个字符类型的索引列。

  1. SELECT USER_NO,USER_NAME,ADDRESS
  3. FROM USER_FILES
  5. WHERE USER_NO = 109204421

  这个语句会被Oracle转换为下面的语句：

​ 

  1. SELECT USER_NO,USER_NAME,ADDRESS
  3. FROM USER_FILES
  5. WHERE TO_NUMBER(USER_NO) = 109204421

  内部发生了类型转换，因此这个索引将不会被用到。为了避免Oracle对所编写的SQL语句进行隐式的类型转换，最好把类型转换显式表现出来。并且注意，当字符和数值进行比较时，Oracle会优先将数值类型转换为字符类型。

8.3.3 减少访问次数

  当执行每条SQL语句时，Oracle都在内部执行了许多工作，如解析SQL语句、估算索引的利用率、绑定变量、读数据块等。由此可见，减少访问数据库的次数，就能减少Oracle的实际工作量。例如，可以使用DECODE函数避免重复扫描相同记录或重复连接相同的表，如下面的SQL语句：

​ 

  1. SELECT  COUNT(*)，SUM(SAL)
  3. FROM EMP
  5. WHERE DEPT_NO = 0020
  7. AND ENAME LIKE ‘SMITH%’;
  9.    SELECT COUNT(*)，SUM(SAL)
  11.    FROM EMP
  13.    WHERE DEPT_NO = 0030
  15. ​               AND ENAME LIKE　‘SMITH%’;

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

​ 

  1. SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
  3. ​        COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
  5. ​        SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
  7. ​        SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
  9. FROM EMP 
  11. WHERE ENAME LIKE ‘SMITH%’;

8.3.4 细节上的影响

  WHERE子句中的连接顺序、ORDER BY中是否存在非索引项、用WHERE替换HAVING语句、用>=替代>等细节，都可能影响Oracle的性能。

  • WHERE子句中的连接顺序

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

  例如，假设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，就需要将最精确的部分放在WHERE子句的最后：

​ 

  1. SELECT * FROM emp e, dept d
  3. WHERE d.deptno>10 AND e.deptno=30 ;

  如果dept表返回的记录数较多，那么上面的查询语句会比下面的查询语句快得多：

​ 

  1. SELECT * FROM emp e,dept d
  3. WHERE e.deptno=30 AND d.deptno>10 ;
  • ORDER BY子句

  ORDER BY语句对要排序的列没有什么特别的限制，也可以将函数加入列中。但是如果在ORDER BY语句中存在非索引项或列的表达式计算，都将降低查询速度。

  SQL优化的细节较多，除了本节介绍的这些知识以外，还需要在平日的学习工作中多总结、多积累。