关于SQL优化的一些思想和原则

转载地址 ：http://oracle.chinaitlab.com/PLSQL/858345_3.html

一、优化的目的
　　对应用程序的优化通常可分为两个方面：源代码优化和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)。如果一个执行计划使用的资源多，我们就说使用执行计划的代价大。以执行计划的代价大小作为衡量标准，优化器选择代价最小的执行计划作为真正执行该查询的执行计划，并抛弃其它的执行计划。
　　四、关于ORACLE优化器
　　在任何可能的时候都会对表达式进行评估，并且把特定的语法结构转换成等价的结构，这么做的原因是 ：要么结果表达式能够比源表达式具有更快的速度；要么源表达式只是结果表达式的一个等价语义结构。不同的SQL结构有时具有同样的操作（例如：= ANY (subquery) and IN (subquery)），ORACLE会把他们映射到一个单一的语义结构。下面我们介绍常量优化和一些常用的操作符优化的知识。

　　1、常量优化：
　　常量的计算是在语句被优化时一次性完成，而不是在每次执行时。下面是检索月薪大于2000的的表达式：

       sal>24000/12

       sal>2000

       sal*12>24000
 
　　如果SQL语句包括第一种情况，优化器会简单地把它转变成第二种。
　　优化器不会简化跨越比较符的表达式，例如第三条语句，鉴于此，应尽量写用常量跟字段比较检索的表达式，而不要将字段置于表达式当中。否则没有办法优化，比如如果sal上有索引，第一和第二就可以使用，第三就难以使用。
　　2、操作符优化：
　　优化器把使用LIKE操作符和一个没有通配符的表达式组成的检索表达式转换为一个“=”操作符表达式。例如：优化器会把表达式ename LIKE 'SMITH'转换为ename = 'SMITH'。优化器只能转换涉及到可变长数据类型的表达式，前一个例子中，如果ENAME字段的类型是CHAR(10)， 那么优化器将不做任何转换。一般来讲LIKE比较难以优化。
　　IN 操作符优化：
　　优化器把使用IN比较符的检索表达式替换为等价的使用“=”和“OR”操作符的检索表达式。
　　例如，优化器会把表达式ename IN ('SMITH','KING','JONES')替换为：ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES‘。
　　ANY和SOME 操作符优化:
　　优化器将跟随值列表的ANY和SOME检索条件用等价的同等操作符和“OR”组成的表达式替换。例如，优化器将如下所示的第一条语句用第二条语句替换：

        sal>ANY(:first_sal,:secound_sal)

        sal>:first_sal or

        sal>:second_sal
　　优化器将跟随子查询的ANY和SOME检索条件转换成由“EXISTS”和一个相应的子查询组成的检索表达式。例如，优化器将如下所示的第一条语句用第二条语句替换：
 

        x>ANY(SELECT sal FROM emp WHERE job='ANALYST')

        EXISTS(SELECT sal FROM emp WHERE job ='ANALYST' AND x>sal)
　　ALL操作符优化：
　　优化器将跟随值列表的ALL操作符用等价的“=”和“AND”组成的表达式替换。例如：sal > ALL (:first_sal, :second_sal)表达式会被替换为：sal > :first_sal AND sal > :second_sal 。

　　对于跟随子查询的ALL表达式，优化器用ANY和另外一个合适的比较符组成的表达式替换。例如x > ALL (SELECT sal FROM emp WHERE deptno = 10) 替换为：NOT (x <= ANY (SELECT sal FROM emp WHERE deptno = 10)) 。接下来优化器会把第二个表达式适用ANY表达式的转换规则转换为下面的表达式： 

        not exists (select sal from emp where deptno=10 and x<=sal)
　　BETWEEN 操作符优化：
　　优化器总是用“>=”和“<=”比较符来等价的代替BETWEEN操作符。
　　例如：优化器会把表达式sal BETWEEN 2000 AND 3000用sal >= 2000 AND sal <= 3000来代替。
　　NOT 操作符优化：
　　优化器总是试图简化检索条件以消除“NOT”逻辑操作符的影响，这将涉及到“NOT”操作符的消除以及代以相应的比较运算符。例如，优化器将下面的第一条语句用第二条语句代替：

       not deptno =(select deptno from emp where ename='TAYLOR')

       deptno<>(select deptno from emp where ename='TAYLOR')
　　通常情况下一个含有NOT操作符的语句有很多不同的写法，优化器的转换原则是使“NOT”操作符后边的子句尽可能的简单，即使可能会使结果表达式包含了更多的“NOT”操作符。例如，优化器将如下所示的第一条语句用第二条语句代替：

        not (sal<1000 or comm is null)

        not sal<1000 and comm is not null sal>1000 and comm is not null
　　关于SQL优化的知识就介绍到这里，谢谢各位的支持！