SQL性能调优

1.  概要说明

数据库系统的显著特点需要保存大量历史记录，系统内存在许多历史记录表，因此常常出现系统运行一段时间，表记录数达到一定数量后，系统响应明显变慢的现象。为避免这种情况的出现，在有完备的数据库对象设计下，还在考虑到系统稳定情况下数据的变化情况，针对这种变化，在编写SQL语句必须遵循一定的优化规则,并制定完备的数据管理机制。

2.  调优目的

利用Oracle的内部优化器工作机制，合理改进查询语句的组织方式，提高数据库系统的响应速度，实现前端画面及平台应用的快速响应能力；同时统一开发人员的数据库开发编码的统一、规范。

3.  调优原则

充分利用表索引，避免进行全表扫描；充分利用SGA共享池缓存机制，提前Oracle工作效率，充分利用结构化编程方式，提高查询的复用能力。

4.  调优方法
4.1  书写方式
4.1.1  相同功能、性能的SQL语句

ORACLE采用共享内存SGA的机制，因此ORACLE会对每个不同写法的SQL进行分析，并且占用共享内存;如果书写格式完全相同，则ORACLE只分析一次，遇到相同书写格式的SQL，会直接从共享内存中获取结果集;这样便能减少共享池的开销以及代码的复用。

处理方法：保证书写格式相同，包括大小写，空格位置，表别名等一致；将一些通用的SQL语句作为公共函数由其它函数调用的方式使用。

4.1.2  动态SQL

动态SQL采用变量动态绑定的方式，避免重复解析。

4.2  连接方式与顺序
4.2.1  表名顺序

多表查询时需要选择最有效率的表名顺序（基于规则的优化器有效），ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名；因此写在最后的那张基础表最先被处理，即选择记录数最少的表作为基础表，首先,扫描FROM子句中最右的那个表，并对记录进行派序,然后扫描FROM子句中最后第二个表,最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。

4.2.2  查询条件顺序

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

4.3  语法和语义
4.3.1  SELECT子句中避免使用 ‘ * ‘

使用’*’ ,Oracle便会查询数据字典，依次解析各列名，应明确指定各列的名称，这样也便于理解。

4.3.2  使用表的别名(Alias)

多表查询时，使用表的别名，同样可以避免解析，避免歧义，提前效率。

4.4  函数和表达式
4.4.1  用>=替代>

如果DEPTNO上有一个索引,
高效:
   SELECT *
   FROM EMP
   WHERE DEPTNO >=4
   低效:
   SELECT *
   FROM EMP
   WHERE DEPTNO >3
      两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.

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

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

4.4.3  用TRUNCATE替代DELETE

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)
而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.
 TRUNCATE只在删除全表或分区适用,TRUNCATE是DDL不是DML

4.5  常用关键字优先级
4.5.1  用EXISTS替代IN

用IN将启用全表扫描，使用EXISTS将利用索引，提高查询效率。

4.5.2  用NOT EXISTS替代NOT IN

用NOT IN将启用全表扫描，使用NOT EXISTS将利用索引，提高查询效率。

4.5.3  用表连接替换EXISTS
4.5.4  用EXISTS替换DISTINCT
4.5.5  避免在索引列上使用IS NULL和IS NOT NULL

如果索引包含任何可以为空的列，ORACLE将无法使用该索引，举例如下：

低效: (索引失效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE >=0;

4.5.6  用UNION-ALL 替换UNION

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

4.5.7  用UNION替换OR (适用于索引列)

通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.

 

4.5.8  用WHERE替代ORDER BY

ORDER BY 子句只在两种严格的条件下使用索引.
ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
ORDER BY中所有的列必须定义为非空.
WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.

4.5.9  用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子句中.

4.5.10  GROUP BY的使用

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.

4.6  全局参数
4.7  其它因素
4.7.1  减少访问数据库的次数

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

? 解析SQL语句

? 估算索引的利用率

? 绑定变量

? 读数据块等等

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

4.7.2  常使用COMMIT

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

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

ü  被程序语句获得的锁

ü   redo log buffer 中的空间

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

 

4.8  常见优化工具介绍
4.8.1  用Explain Plan分析SQL语句

EXPLAIN PLAN 是一个很好的分析SQL语句的工具, 它甚至可以在不执行SQL的情况下分析语句. 通过分析, 我们就可以知道ORACLE是怎么样连接表, 使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称.

4.8.2  SQL PLUS的TRACE

 

5.  常用术语
5.1  全表扫描

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

5.2  通过索引访问（ROWID ）

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

5.3  共享SQL

? Oracle提供对执行过的SQL语句进行高速缓冲的机制。被解析过并且确定了执行路径的SQL语句存放在SGA的共享池中。

? Oracle执行一个SQL语句之前每次先从SGA共享池中查找是否有缓冲的SQL语句，如果有则直接执行该SQL语句。

? 可以通过适当调整SGA共享池大小来达到提高Oracle执行性能的目的。

 

5.4  ORACLE优化器

? RULE（基于规则）

? COST（基于成本）

? CHOOSE（选择性）