SQL代码性能优化

SQL代码性能优化
在传统的数据库优化理论中存在着一些有争议的问题，例如：
传统上认为，数据库通过使用索引就一定可以更快地遍历表，而且在实际的数据库系统中的优化器都是根据定义的索引来提高性能。
但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出高性能的SQL语句。
研究SQL调整的目标就是要确定执行计划对于语句是最优的。最直接的是采用set timing on命令来检验SQL执行的速度，并且计算查询需要花费的时间。
除了分析与试验相结合的考证这些有争议的数据库优化理论与方法外，在实际的运用中我进一步总结出了一些新的优化技巧和方法，总结如下：
一、避免索引失效
如果对where子句的写法不注意，即使某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。
1. IS NULL 与 IS NOT NULL
不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
2. 联接列
对于有联接的列，即使最后的联接值为一个静态值，优化器还是不会使用索引的。我们一起来看一个例子，假定有一个职工表（employee），对于一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME），现在要查询一个叫比尔.克林顿（Bill Cliton）的职工。
下面是一个采用联接查询的SQL语句，
select * from employee
where first_name||''||last_name ='Beill Cliton';
上面这条语句完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，系统优化器对基于last_name创建的索引没有使用。
当采用下面这种SQL语句的编写时，Oracle系统就可以采用基于last_name创建的索引，从而提高检索速度。
Select * from employee
Where first_name ='Beill' and last_name ='Cliton';
3. 搜索词的词首出现通配符（%）
避免使用在搜索词的词首出现通配符（%）的like语句。
如果要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句：
select * from employee where last_name like '%cliton%';
这里由于通配符（%）在搜寻词首出现，所以Oracle系统不能使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用：
select * from employee where last_name like 'c%';
4. Order by语句
Order by语句决定了Oracle如何将返回的查询结果排序。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。
5. NOT
在进行查询时经常需要在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，同时也可以使用and（与）、or（或）以及not（非）。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子：
where not (status ='VALID')
如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于（<>）运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例：
where status <>'INVALID';
再看下面这个例子：
Select * from employee where salary<>3000;
对这个查询，可以改写为不使用NOT：
Select * from employee where salary<3000 or salary>3000;
虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询由于使用了NOT运算符所以不能使用索引。
二、保证索引的高效
1. 等式比较和范围比较
当WHERE子句中有的索引列非等式表达式时，ORACLE是不能合并它们的，ORACLE不得不采取逐一比较的方式。
举例：
DEPT_NO上有一个非唯一性索引，EMP_CAT也有一个非唯一性索引。
select ENAME from EMP
where DEPT_NO > 20 and EMP_CAT = ‘A';
这里只有EMP_CAT索引被用到，然后所有的记录将逐条与DEPT_NO条件进行比较。执行路径如下：
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON CAT_IDX
2.避免在索引列上使用计算．
WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。
低效：
select … from DEPT where SAL * 12 > 25000;
高效：
select … from DEPT where SAL > 25000/12;
3.用 >= 替代 >
如果DEPT_NO上有一个索引。
高效：
select * from EMP where DEPT_NO >=4
低效：
select * from EMP where DEPT_NO >3
两者的区别在于：前者DBMS将直接跳到第一个 DEPT_NO 等于4的记录而后者将首先定位到 DEPT_NO=3 的记录并且向前扫描到第一个DEPT_NO大于3的记录。
4.避免在索引列上使用IS NULL和IS NOT NULL
应该避免在索引中使用任何可以为空的列，因为这样ORACLE将不会使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。而对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。
举例：
如果唯一性索引建立在表的A列和B列上，并且表中存在一条记录的A,B值为(123,null)，ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入)。然而如果所有的索引列都为空，ORACLE将认为整个键值为空，根据数据库的空不等于空（null <> null）约定，新插入是合法的。 因此甚至可以插入1000条具有相同键值的记录，当然它们都必须是空！
因为索引列中是不存在空值的，所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。
低效：(索引失效)
select … from DEPARTMENT
where DEPT_CODE is not null;
高效：(索引有效)
select … from DEPARTMENT
where DEPT_CODE >=0;
5.总是使用索引的第一个列
如果索引是建立在多个列上的，那么，只有在它的第一个列(leading column)被where子句引用时，优化器才会选择使用该索引。
SQL> create table multiindexusage ( inda number , indb number , descr varchar2(10));
Table created.
SQL> create index multindex on multiindexusage(inda,indb);
Index created.
SQL> set autotrace traceonly
SQL> select * from multiindexusage where inda = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MULTIINDEXUSAGE'
2 1 INDEX (RANGE SCAN) OF 'MULTINDEX' (NON-UNIQUE)
SQL> select * from multiindexusage where indb = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE'
很明显，当仅引用索引的第二个列时，优化器使用了全表扫描而忽略了索引。
6.避免改变索引列的类型.
当比较不同数据类型的数据时，ORACLE自动对列进行简单的类型转换。假设 EMP_NO是一个数值类型的索引列。
select … from EMP where EMP_NO = ‘123'
实际上，经过ORACLE类型转换，语句转化为：
select … from EMP where EMP_NO = to_number(‘123')
幸运的是类型转换没有发生在索引列上，索引的用途没有被改变。
现在，假设EMP_TYPE是一个字符类型的索引列：
select … from EMP where EMP_TYPE = 123
这个语句被ORACLE转换为：
select … from EMP where to_number(EMP_TYPE)=123
因为内部发生的类型转换作用在索引列上，这个索引将不会被使用！
为了避免ORACLE对SQL进行隐式的类型转换，最好把类型转换用显式表现出来。注意当字符和数值比较时，ORACLE会优先转换数值类型到字符类型。
三、选择最有效率的表名顺序
由于ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
例如:
表 TAB1 16384 条记录
表 TAB2 1 条记录
选择TAB2作为基础表 (最好的方法)
select count(*) from tab1,tab2 执行时间0.96秒
选择TAB2作为基础表 (不佳的方法)
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
当然，如果使用的是CBO (COST BASED OPTIMIZER)模式，优化器会检查SQL语句中的每个表的物理大小，索引的状态，然后选用花费最低的执行路径。但是如果在RBO (RULE BASED OPTIMIZER)模式下，并且所有的连接条件都有索引对应，在这种情况下，必须人为指定基础表来提高效率。因为所有操作都是要耗费系统资源和时间的，所以最好的方法是在RBO模式下人为的指定表名的顺序，而不需要优化器进行额外的工作。
四、WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析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';
五、SELECT子句中避免使用‘*’
当想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用’*’是一个方便的方法。然而这是一个非常低效的方法。在ORACLE的解析过程中，会将'*'依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
因此，对于需要列出所有的COLUMN的情况：
select * from table_name;
比起
select column1,column2,…,columnN from table_name;
要慢的多。
六、用表连接替换EXISTS
通常来说，采用表连接的方式比EXISTS更有效率。因为Oracle优化器在见到EXISTS的时候，会在SQL的执行路径上包含NESTED LOOP，而使用表连接的时候则会在SQL的执行路径上包括FILTER，可见效率的差别有多大。
使用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' ;
七、用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换
例如：
低效：
select distinct DEPT_NO, DEPT_NAME
from DEPT d, EMP e
where d.DEPT_NO = e.DEPT_NO
高效：
select DEPT_NO, DEPT_NAME
from DEPT d
where exists ( select ‘X'
from EMP e
where e.DEPT_NO = d.DEPT_NO);
EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。
八、尽量多使用COMMIT
在保证事务完整性的基础上，只要有可能，在程序中应尽量多使用COMMIT。这样，不再使用的资源会被尽快释放出来，系统的资源负担会减轻许多：
一旦执行COMMIT操作之后，系统所释放的资源如下：
a. 回滚段上用于恢复数据的信息。
b. 被程序语句获得的锁。
c. redo log buffer 中的空间。
d. ORACLE为管理上述3种资源而导致的内部花费。