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

一、优化的目的

对应用程序的优化通常可分为两个方面：源代码优化和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)。如果一个执行计划使用的资源多，我们就说使用执行计划的代价大。以执行计划的代价大小作为衡量标准，优化器选择代价最小的执行计划作为真正执行该查询的执行计划，并抛弃其它的执行计划。

#p#

四、关于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, :second_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优化的知识就介绍到这里，谢谢各位的支持！

【编辑推荐】

1. 提高SQL执行效率的几点建议
2. SQL Server视图管理中的四个限制条件
3. SQL Server数据库多种方式查找重复记录
4. 使用SQL Trace来实现SQL Server的跟踪操作
5. CTE和WITH AS短语结合使用提高SQL查询性能