SQL优化

概要
SQL语句优化的过程简介
常用SQL优化
常用SQL书写原则

SQL语句优化的过程
1.定位有问题的语句
2.检查执行计划
3.检查执行过程中优化器的统计信息
4.分析相关表的记录数、索引情况
5.改写SQL语句、表分析
6.有些SQL语句不具备优化的可能，需要优化处理方式
7.达到最佳执行计划

常用SQL优化
1.优化查询SQL字段的选择，避免用*
2.使用DECODE函数减少处理步骤
3.减少对表的查询操作
4.用EXISTS替代IN、用NOT EXISTS替代NOT IN
5.选择最有效率的表名顺序
6. Where子句中的连接顺序
7.避免在索引列上使用计算
8.优化GROUP BY
9. Instr替换 like
10.通过内部函数提高SQL效率
11.使用表的别名
12. SQL语句用大写的
13.用>=替代>
14.用IN来替换OR
15.总是使用索引的第一个列
16.避免改变索引列的类型


1.优化查询SQL字段的选择
指定仅仅需要的列名与使用*对比：
时间：170/170 
IO耗费： 4534430/269550 可见大幅降低I/O从而降低响应时间！
 
总结：Oracle在解析的过程中, 会将‘*’依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。在执行查询SQL的时候如果不是所有字段都需要,尽量不要用*代替所有列名
2.使用DECODE函数减少处理步骤
总结：使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表，尽量使用DECODE函数来减少处理步骤
3.减少对表的查询操作
总结：在含有子查询的SQL语句中，要注意减少对表的查询操作。
4.用EXISTS替代IN、用NOT EXISTS替代NOT IN
在子查询中，NOT IN子句将执行一个内部的排序和合并，对子查询中的表执行一个全表遍历，因此是非常低效的。
为了避免使用NOT IN，可以把它改写成NOT EXISTS
5.选择最有效率的表名顺序
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理 ，选择记录条数最少的表作为基础表。

当ORACLE处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行处理，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

只在基于规则的优化器中有效

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

7.避免在索引列上使用计算
WHERE子句中，避免在列上使用函数。（优化器将不能使用索引而使用全表扫描)

8.优化GROUP BY
提高GROUP BY语句的效率，可以通过将不需要的记录在GROUP BY之前过滤掉
9.Instr替换 like
尽可能使用instr 替换掉 like,因为instr 效率更高，并能利用索引。

Instr(SPEC,’8GB’)>0 相当于 SPEC like ‘%8GB%’
Instr(SPEC,’8GB’)=1 相当于 SPEC like ‘8GB%’
Instr(SPEC,’8GB’)=0 相当于 SPEC NOT Like ‘%8GB%’

10.通过内部函数提高SQL效率
复杂的SQL往往牺牲了执行效率。能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的复杂的SQL往往牺牲了执行效率。
例如：求平均完成时间AVG函数替代 SUM(总时间)/COUNT(人数)
11.使用表的别名
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误
12. SQL语句用大写的
SQL语句用大写的；因为Oracle总是先解析SQL语句，把小写的字母转换成大写的再执行
13.用>=替代>
低效: SELECT * FROM EMP WHERE DEPTNO >3
高效：SELECT * FROM EMP WHERE DEPTNO >=4 
两者的区别在于，前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录
14.用IN来替换OR
低效: SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30
高效：SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);
15.总是使用索引的第一个列
如果索引是建立在多个列上，只有在它的第一个列(leading column)被where子句引用时，优化器才会选择使用该索引。这也是一条简单而重要的规则，当仅引用索引的第二个列时，优化器使用了全表扫描而忽略了索引
16.避免改变索引列的类型
当比较不同数据类型的数据时， ORACLE自动对列进行简单的类型转换。 假设 EMPNO是一个数值类型的索引列：SELECT … FROM EMP WHERE EMPNO = ‘123'。 实际上,经过Oracle类型转换, 语句转化为: SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123') 。
类型转换没有发生在索引列上,索引的用途没有被改变。假设EMP_TYPE是一个字符类型的索引列：SELECT … FROM EMP WHERE EMP_TYPE = 123 。

这个语句被Oracle转换为: SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123。因为内部发生的类型转换， 这个索引将不会被用到! 为了避免Oracle对你的SQL进行隐式的类型转换，最好把类型转换用显式表现出来。注意当字符和数值比较时，Oracle会优先转换数值类型到字符类 型

常用SQL书写原则
1.减少访问数据库的次数。
2.整合简单,无关联的数据库访问。
3.删除重复记录用ROWID。
4.用TRUNCATE替代DELETE
5.活用COMMIT

1.减少访问数据库的次数
Oracle在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等
2.整合简单,无关联的数据库访问
如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)。
3.删除重复记录
最高效的删除重复记录方法 使用了ROWID： 
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

4.使用TRUNCATE代替DELETE（如果可以）
Delete表中记录的时候，Oracle会在Rollback段中保存删除信息以备恢复。Truncate删除表中记录的时候不保存删除信息，不能恢复。因此Truncate删除记录比Delete快，而且占用资源少。

删除表中记录的时候，如果不需要恢复的情况之下应该尽量使用Truncate而不是Delete。

Truncate仅适用于删除全表的记录。
语法如下：
  TRUNCATE TABLE table_name [DROP|REUSE STORAGE]
  DROP STORAGE为默认的方式，表示收回被删除的表空间
  REUSER STORAGE表示保留被删除的空间以供该表的新数据使用

语法如下：
  TRUNCATE TABLE table_name [DROP|REUSE STORAGE]
  DROP STORAGE为默认的方式，表示收回被删除的表空间
  REUSER STORAGE表示保留被删除的空间以供该表的新数据使用

5.活用COMMIT
只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少。
COMMIT所释放的资源:
回滚段上用于恢复数据的信息.
被程序语句获得的锁
redo log buffer 中的空间
ORACLE为管理上述3种资源中的内部花费