sql 优化(一)(转)

同事给的一篇关于SQL优化的总结文章，拿过来大家共享之。

一．   目的

数据库参数进行优化所获得的性能提升全部加起来只占数据库应用系统性能提升的40%左右，其余60%的系统性能提升全部来自对应用程序的优化。许多优化专家甚至认为对应用程序的优化可以得到80%的系统性能提升。因此可以肯定，通过优化应用程序来对数据库系统进行优化能获得更大的收益。

对应用程序的优化通常可分为两个方面: 源代码的优化和SQL语句的优化。由于涉及到对程序逻辑的改变，源代码的优化在时间成本和风险上代价很高(尤其是对正在使用中的系统进行优化) 。另一方面，源代码的优化对数据库系统性能的提升收效有限，因为应用程序对数据库的操作最终要表现为SQL语句对数据库的操作。

      对SQL语句进行优化有以下一些直接原因：

1. SQL语句是对数据库(数据) 进行操作的惟一途径，应用程序的执行最终要归结为SQL语句的执行，SQL语句的效率对数据库系统的性能起到了决定性的作用。

2. SQL语句消耗了70%～90%的数据库资源。

3. SQL语句独立于程序设计逻辑，对SQL语句进行优化不会影响程序逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低。

4. SQL语句可以有不同的写法，不同的写法在性能上的差异可能很大。

5. SQL语句易学，难精通。SQL语句的性能往往同实际运行系统的数据库结构、记录数量等有关，不存在普遍适用的规律来提升性能。

二. 优化数据库的思想：

 1、关键字段建立索引。

2、使用存储过程，它使SQL变得更加灵活和高效。

3、备份数据库和清除垃圾数据。

4、SQL语句语法的优化。

5、清理删除日志。

三．SQL语句优化的原则：

   不要以为只有SELECT语句是查询。实际上，带有任何WHERE条件的 DML(INSERT、UPDATE、DELETE)语句中都包含查询要求，在后面的文章中，当说到查询时，不一定只是指SELECT语句，也有可能指 DML语句中的查询部分。

    我们知道，SQL语句同其它语言(如C语言)的语句不一样，它是非过程化(non-procedural)的语句，即当你要取数据时，不需要告诉数据库通过何种途径去取数据，如到底是通过索引取数据，还是应该将表中的每行数据都取出来，然后再通过一一比较的方式取数据(即全表扫描)。

    为了实现一个查询，内核必须为每个查询定制一个查询策略，或为取出符合条件的数据生成一个执行计划(execution plan)。典型的，对于同一个查询，可能有几个执行计划都符合要求，都能得到符合条件的数据。例如，参与连接的表可以有多种不同的连接方法，这取决于连接条件和优化器采用的连接方法。为了在多个执行计划中选择最优的执行计划，优化器必须使用一些实际的指标来衡量每个执行计划使用的资源(I/0次数、 CPU等)，这些资源也就是我们所说的代价(cost)。如果一个执行计划使用的资源多，我们就说使用执行计划的代价大。以执行计划的代价大小作为衡量标 准，优化器选择代价最小的执行计划作为真正执行该查询的执行计划，并抛弃其它的执行计划。

    3.1 选择最有效率的表名顺序

    在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

基础表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问).
例如:
表 TAB1 16,384 条记录
表 TAB2 1 条记录
选择TAB2作为基础表 (最好的方法)
select count(*) from tab1,tab2 执行时间0.96秒 oracle测试标准
选择TAB1作为基础表 (不佳的方法)
select count(*) from tab2,tab1 执行时间26.09秒
如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
例如:
EMP表描述了LOCATION表和CATEGORY表的交集.
SELECT *
FROM LOCATION L ,
CATEGORY C,
EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN

将比下列SQL更有效率
SELECT *
FROM EMP E ,
LOCATION L ,
CATEGORY C
WHERE E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000
3.3 WHERE子句中的连接顺序

采用自下而上的顺序解析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’;

3.2  SELECT子句中避免使用 ‘ * ‘

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

3.3 尽量多使用COMMIT

    只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间
3.4  计算记录条数

和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)

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

3.6通过内部函数提高SQL效率

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

3.8用EXISTS替代IN

3.9用NOT EXISTS替代NOT IN

3.10 用表连接替换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’ ;