SQL开发老手的经验总结

1.在查询时尽量少用格式转换

2.Select子句中避免使用 “ * ”

当你想在select子句中列出所有的column时,使用动态SQL列引用 ‘’ 是一个方便的方法。不幸的是，这是一个非常低效的方法。 实际上，ORACLE在解析的过程中，会将 '’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。

3.减少访问数据库的次数
当执行每条SQL语句时，ORACLE在内部执行了许多工作:解析SQL语句、估算索引的利用率、绑定变量、读数据块等等。由此可见，减少访问数据库的次数，就能实际上减少ORACLE的工作量。
举例：我要查找编号为0001、0002学生的信息。
（低效）
select name,age,gender,address from t_student where id = ‘0001’;
select name,age,gender,address from t_student where id = ‘0002’;
（高效）
select a.name,a.age,a.gender,a.address,b.name,b.age,b.gender,b.address from
t_student a,t_student b where a.id = ‘0001’ and b.id = ‘0002’;

4.使用decode函数来减少处理时间 (CASE WHEN)
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。
举例：
（低效）
select count(), sum(banace) from table1 where dept_id = ‘0001’ and name like ‘anger%’;
select count(), sum(banace) from table1 where dept_id = ‘0002’ and name like ‘anger%’;
（高效）
Select count(decode(dept_id,‘0001’,‘XYZ’,null)) count_01,
count(decode(dept_id,‘0002’,‘XYZ’,null)) count_02,
sum(decode(dept_id,‘0001’,dept_id,null)) sum_01,
sum(decode(dept_id,‘0002’,dept_id,null)) sum_02
from table1
where name like ‘anger%’;

5.整合简单,无关联的数据库访问
如果你有几个简单的数据库查询语句，你可以把它们整合到一个查询中(即使它们之间没有关系)
举例：
（低效）
select name from table1 where id = ‘0001’;
select name from table2 where id = ‘0001’;
select name from table3 where id = ‘0001’;
（高效）
select t1.name, t2.name, t3.name
from table1 t1, table2 t2, table3 t3
where t1.id(+) = ‘0001’ and t2.id(+) = ‘0001’ and t3.id(+) = ‘0001’
【注：上面例子虽然高效，但是可读性差，需要量情而定！】

6.尽量不要使用having子句，可以考虑用where替换
having只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序，总计等操作。 如果能通过where子句限制记录的数目，那就能减少这方面的开销。

7.尽量用表的别名
当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

8.用exists替代in
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。 在这种情况下，使用exists(或not exists)通常将提高查询的效率。
举例：
（低效）
select … from table1 t1 where t1.id > 10 and pno in (select no from table2 where name like ‘www%’);
（高效）
select … from table1 t1 where t1.id > 10 and exists (select 1 from table2 t2 where t1.pno = t2.no and name like ‘www%’);

9.用not exists替代not in
在子查询中，not in子句将执行一个内部的排序和合并。 无论在哪种情况下，not in都是最低效的 (因为它不能应用表的索引，对子查询中的表执行了一个全表遍历)。为了避免使用not in，我们可以把它改写成外连接(Outer Joins)或not exists。

10.用exists替换distinct
当提交一个包含一对多表信息的查询时,避免在select子句中使用distinct. 一般可以考虑用exists替换
举例：
（低效）
select distinct d.dept_no, d.dept_name from t_dept d, t_emp e where d.dept_no = e.dept_no;
（高效）
select d.dept_no, d.dept_name from t_dept d where exists (select 1 from t_emp where d.dept_no = e.dept_no);
exists使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.

11. 用表连接替换exists
    通常来说，采用表连接的方式比exists更有效率。
    举例：
    （低效）
    select ename from emp e where exists (select 1 from dept where dept_no = e.dept_no and dept_cat = ‘W’);
    （高效）
    select ename from dept d, emp e where e.dept_no = d.dept_no and dept_cat = ‘W’;

12.避免在索引列上使用is null和is not null
判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。
推荐方案：
用其它相同功能的操作运算代替，如 a is not null 改为 a>0 或a>’'等。
不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。
建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象。

13.复杂的sql，去看下它的执行计划
看下它的执行计划，这样有利于你分析知道自己的sql效率如何。这样避免大数据量访问时系统负载过重

14.利用索引，避免大表FULL TABLE SCAN

15.合理使用临时表

16.避免写过于复杂的sql

17.在不影响业务的前提下减小事务的粒度

18.避免用<> 操作符（不等于）
不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。
推荐方案：用其它相同功能的操作运算代替，如
　　a<>0 改为 a>0 or a<0
　　a<>’’ 改为 a>’’
　　
19.> 及 < 操作符（大于或小于操作符）的优化
大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

20.避免使用前置通配符
WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用，在这种情况下，ORACLE将使用全表扫描。

21.union操作符
union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表union。如：
select * from gc_dfys
union
select * from ls_jg_dfys
这个SQL在运行时先取出两个表的结果，再用排序控件进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。
推荐方案：采用union ALL操作符替代union，因为union ALL操作只是简单的将两个结果合并后就返回。
select * from gc_dfys
union all
select * from ls_jg_dfys

22.大数据表删除，用truncate table代替delete（实际项目经验）

23.合理使用索引
在OLTP应用中一张表的索引不要太多。数据重复量大的列不要建立二叉树索引，可以采用位图索引；组合索引的列顺序尽量与查询条件列顺序保持一致；对于数据操作频繁的表，索引需要定期重建，以减少失效的索引和碎片。

24.尽量少嵌套子查询
这种查询会消耗大量的CPU资源；对于有比较多or运算的查询，建议分成多个查询，用union all联结起来。

25.尽量多用commit语句提交事务
可以及时释放资源、解锁、释放日志空间、减少管理花费；在频繁的、性能要求比较高的数据操作中，尽量避免远程访问，如数据库链等，访问频繁的表可以常驻内存：alter table．．．cache；

26.使用索引时至少要包含组合索引的第一列
如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。

27.避免在索引列上使用函数
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描。

28.避免出现索引列自动转换
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换。
假设EMP_TYPE是一个字符类型的索引列：
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE USER_NO = 109204421
这个语句被ORACLE转换为:
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE TO_NUMBER(USER_NO) = 109204421
因为内部发生的类型转换, 这个索引将不会被用到。

29.使用fetch first N rows only
当查询返回结果集较多时，而我们却不需要这么多，那么可以采用fetch first N rows only，N为大于0的整数，这样即使你做了全表扫描，但是我只会取前100行数据，会大大降低执行时间。

30.对于复杂的sql语句开发，先对业务进行分析和拆解，得出sql语句的编写顺序，尽量缩小结果集，尽量不要用大表进行关联。对于一些辅助的输出字段，等最后结果（通常最后结果集很小）出来之后再关联得到。

31.对于步骤较多的sql语句，增加一些注释。

SQL语句优化过程

• 定位有问题的语句
• 检查执行计划
• 检查执行过程中优化器的统计信息
• 分析相关表的记录数、索引情况
• 改写SQL语句、使用HINT、调整索引、表分析
• 有些SQL语句不具备优化的可能，需要优化处理方式
• 达到最佳执行计划
  什么是好的SQL语句

• 尽量简单，模块化
• 易读、易维护
• 节省资源(内存、CPU、扫描的数据块要少、少排序)
• 不造成死锁

From 互联网+个人工作经验总结