sql——SQL优化

1、使用别名

对下列语句进行优化。

SELECT EMPLOYEE_TBL.EMPLOYEE_ID,EMPLOYEE_TBL.NAME,EMPLOYEE_PAY_TBL.SALARY,EMPLOYEE_PAY_TBL.HIRE_DATE
FROM EMPLOYEE_TBL,EMPLOYEE_PAY_TBL
WHERE EMPLOYEE_TBL.EMPLOYEE_ID=EMPLOYEE_PAY_TBL.EMPLOYEE_ID
AND EMPLOYEE_PAY_TBL.SALARY>30000 
OR (EMPLOYEE_PAY_TBL.SALARY BETWEEN 25000 AND 30000 
AND EMPLOYEE_PAY_TBL.HIRE_DATE < SYSDATE -365);

        优化后的语句：将EMPLOYEE_TBL简化别名E，EMPLOYEE_PAY_TBL简化别名P，别名的输入要比全名少许多。使用别名后，查询更加有组织和易读性。

select E.EMPLOYEE_ID,E.NAME,P.SALARY,P.HIRE_DATE
FROM EMPLOYEE_TBL E,EMPLOYEE_PAY_TBL P
WHERE E.EMPLOYEE_ID = P.EMPLOYEE_ID
AND P.SALARY>30000
OR(P.SALARY BETWEEN 25000 AND 30000
AND P.HIRE_DATE<SYSDATE-365);

2、全表扫描

        当数据库服务为执行某一个 SQL 语句需要对表中的每一个记录进行检查时就会发生全表扫描。一般发生在执行 SELECT 语句时，有时也会在更新和删除记录时发生。全表扫描是因为在where子句中，使用了索引没有的字段，使用合适的索引可以提高我们的查询速度。

适合使用全表扫描的情况：

        需要选择表中的大多数行的时候。

        在对表中的每一行数据进行更新的时候。

        表非常小的时候。

3、创建索引

        SQL 语句运行的时间长得不合情理，尽管其它的语句运行的性能看起来是可以接受的。加入一个 WINDOWS 的应用前端时，当数据的检索条件改变或表的结构改变以后，我们会发现表原本有索引，在where子句中新的条件没有索引，这时我们可以创建一个新的索引。如果SQL查询的时候，经常将两列内容一起进行查询，我们可以使用复合索引查询速度更快。

4、避免使用or

        可能的话应该在查询尽量避免使用逻辑操作符 OR，OR 会不可避免的根据表的大小降低查询的速度。可以将or或or like用in替代降低查询时间。

select * from fact_table
where status_cd ='A'
or status_cd ='B'
or status_cd ='C' 
or status_cd ='D'
or status_cd ='E'
or status_cd ='F'
order by status_cd;

select * from fact_table
where status_cd in('A','B','C','D','E','F')
order by status_cd;

select * from fact_table
where prod_cd like 'AB%'
or prod_cd like 'AC%'
or prod_cd like 'BB%' 
or prod_cd like 'BC%'
or prod_cd like 'CC%'
order by prod_cd;

select * from fact_table
where substr(prod_cd,1,2)in('AB%','AC%','BB%','BC%','CC%')
order by prod_cd;

5、OLAP和OLTP

OLAP在线分析处理：数据库是一个对最终用户的查询进行统计和汇总的系统。

        OLAP 系统是将较多的空间用于对数据的分类和排序而把较少的空间用于撤消区域的代表。它需要较大的空间用以进行分类。

OLTP在线事务处理：数据库则是一个将主要的功能提供给为最终用户输入服务的环境的系统。以日为基本单位进行。数据仓库和DSS可以从在线的事务处理数据库中的到所需的数据。

        在事务处理数据库中最明显的例子就是 ROLLBACK 语句，需要撤消的内容的量与尺寸是与当前有多少用户在访问数据库相关的。与在每一个事务中进行的工作一样，最好的办法是在一个事务处理的环境中有多个 ROLLBACK命令。

        在事务处理环境中另一个涉及的问题是事务历史记录的完整性，它在每一个事务结束后都会写出。SQL SERVER使用的是 DUMP DEVICE，ORACLE 使用 ARCHIVELOG 模式的数据库。

6、批量载入与事务批处理

        批量载入就是在一次事务中完成对数据库所进行的任务。

        批处理任务大量地占用资源， 如果批处理任务进行下去，由于资源的不足，用户的进程可能会在最后被迫退出这副图。

        批处理进程的另外一个问题是当另一个用户访问它所访问的表时可能会死锁，如果一个表被锁住了，用户将会被拒绝访问直到批处理进程解除对该表的锁定。批处理进程应该是系统处理最佳的时候发生，不要让用户与批处理进程同时进行。

7、删除索引优化数据载入

        索引在需要进行大批量的插入操作时，会降低执行速度，可以先将索引删除，在插入操作完成后再重新创建索引。

为了让数据占总表比重相当的批量载入达到最大效率，使用下边的方法来使索引失去作用。

（1）删除适当的索引。

（2）装入或更新表的数据。

（3）重新生成表的索引。

8、使用commit

        在数据库中，有一个区域是用以存储全部的写到实际表中之前的事务数据的，ORACLE 将这一区域叫做 ROLLBACK 段，COMMIT 命令更新后，ROLLBACK段的内容被删除。

9、内置调整工具

        ORACLE有两个流行的工具用以管理 SQL 语句的性能，这两个工具是 EXPLAIN PLAN和TKPROF。EXPLAIN PLAN 可以在 SQL 语句执行时检查它的访问路径。TKPROF 可以给出每个 SQL 语句在执行时所需的时间，ORACLE 公司也提供了其它的工具用以对 SQL 语句和数据库进行分析，可以使用 SQL*PLUS 中的命令 SET TIMING ON进行测试一个SQL语句的运行时间。

        SYBASER 的 SQL SERVER 也有用于诊断 SQL 的工具。SET SHOWPLAN ON，SET STATISTIC IO ON 和 SET STATISTICS TIME ON这些 SET 命令，可以在查询执行时显示每一步的输出内容，查询的读写要求和一些常用的 SQL 语句分析用的信息。