写出高效率的SQL语句

写出高效率的SQL语句

序言

SQL编写的运行效率对系统最终的性能有起到决定性作用，所以在日常编写的时候我们都应该注意对SQL的优化，这里我提供一些建议希望大家参考。

目录

序言 (2)

目录 (3)

1.Where子句中的连接顺序 (4)

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

3.减少访问数据库的次数 (4)

4.使用Decode函数来减少处理时间 (5)

5.整合简单,无关联的数据库访问 (5)

6.删除重复记录 (6)

7.尽量不要使用having子句，可以考虑用where替换 (6)

8.尽量用表的别名 (6)

9.用exists替代in (6)

10.用not exists替代not in (7)

11.用exists替换distinct (7)

12.用表连接替换exists (7)

13.避免在索引列上使用is null和is not null (8)

14.复杂的sql，去看下它的执行计划 (8)

1.Where子句中的连接顺序

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

举例：

(低效)

select ... from table1 t1 where t1.sal > 300 and t1.jobtype = '0001' and 20 < (select count(*) from table1 t2 where t2.pno = t1.tno;

(高效)

select ... from table1 t1 where 20 < (select count(*) from table1 t2 where t2.pno = t1.tno and t1.sal > 300 and t1.jobtype = '0001';

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 https://www.wendangku.net/doc/4c5543393.html,,a.age,a.gender,a.address,https://www.wendangku.net/doc/4c5543393.html,,b.age,b.gender,b.address from

t_student a,t_student b where a.id = '0001' and b.id = '0002';

4.使用Decode函数来减少处理时间

使用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 https://www.wendangku.net/doc/4c5543393.html,, https://www.wendangku.net/doc/4c5543393.html,, https://www.wendangku.net/doc/4c5543393.html,

from table1 t1, table2 t2, table3 t3

where t1.id(+) = '0001' and t2.id(+) = '0001' and t3.id(+) = '0001'

【注：上面例子虽然高效，但是可读性差，需要量情而定！】

6.删除重复记录

最高效的删除重复记录方法 ( 因为使用了ROWID)

举例：

delete from table1 t1

where t1.rowid > (select min(t2.rowid) from table1 t2 where t1.id = t2.id);

7.尽量不要使用having子句，可以考虑用where替换

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

8.尽量用表的别名

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

9.用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%');

10.用not exists替代not in

在子查询中，not in子句将执行一个内部的排序和合并。无论在哪种情况下，not in都是最低效的(因为它对子查询中的表执行了一个全表遍历)。

为了避免使用not in，我们可以把它改写成外连接(Outer Joins)或not exists。

11.用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核心模块将在子查询的条件一旦满足后,立刻返回结果.

12.用表连接替换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';

13.避免在索引列上使用is null和is not null

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录；对于复合索引，如果每个列都为空，索引中同样不存在此记录；如果至少有一个列不为空，则记录存在于索引中。

举例:

如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null)，ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入)，

然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空。

因此你可以插入1000 条具有相同键值的记录,当然它们都是空!

因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。

14.复杂的sql，去看下它的执行计划

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