11条有效的SQL优化技巧

1. 选择最有效率的表名顺序(只在基于规则的优化器中有效)

SQL 的解析器按照从右到左的顺序处理 FROM 子句中的表名,因此 FROM 子句中写在最后的表(我们称之为基础表)将被最先处理. 在 FROM 子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当 SQL 处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM 子句中最后的那个表)并对记录进行派序,然后扫描第二个表

(FROM 子句中倒数第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记

录进行合并.

例如:

表 TAB1 16,384 条记录

表 TAB2 1 条记录

选择 TAB2 作为基础表 (最好的方法)

select count(*) from tab1,tab2执行时间 0.96 秒

选择 TAB2 作为基础表 (不佳的方法)

select count(*) from tab2,tab1 执行时间 26.09 秒

如果有 3 个以上的表连接查询, 那就需要选择交叉表作为基础表, 交叉表是指那个被其他

表所引用的表.

例如:

SC 表描述了 STUDENT 表和 COURCE 表的交集.

SELECT *

FROM STUDENT S ,

COURCE C,

SC

WHERE SC.SNO BETWEEN 95001 AND 95005

AND SC.CNO = C.CNO

AND SC.SNO = S.SNO

将比下列 SQL 更有效率

SELECT *

FROM SC

STUDENT S ,

COURCE C,

WHERE SC.CNO = C.CNO

AND SC.SNO = S.SNO

AND SC.SNO BETWEEN 95001 AND 95005

2 .WHERE 子句中的连接顺序.

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

(低效，若 95%以上都大于十岁，一半人数男性，这样在先被执行的 student.sage 只排除了5%的数据)

SELECT …

FROM STUDENT

WHERE STUDENT.SSEX=’男’

STUDENT.SAGE>10

(高效，同上，先被执行的 student.ssex 可以直接排除 50%的数据，效率要高于上面写法)

SELECT …

FROM STUDENT

WHERE STUDENT.SAGE>10

AND STUDENT.SSEX=’男’

3 .SELECT 子句中避免使用 ‘ * ‘

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

4 .减少访问数据库的次数

当执行每条 SQL 语句时, ORACLE 在内部执行了许多工作: 解析 SQL 语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少 SQL 的工作量.

如我们要做一次操作

效率最低的，将一次操作编程两条 SQL 语句

次低的，在一个 SQL 语句中定义变量、循环等等

高效率，只是使用 SELECT 基本语句

5. 用 TRUNCATE 替代 DELETE

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有 COMMIT 事务,SQL 会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)而当运用 TRUNCATE 时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.

6 .计算记录条数时，如果可以通过索引检索 , 对索引列的计数仍旧是最快的 . 例如

COUNT(SNO)(sno 为索引列)

7 .用 Where 子句替换 HAVING 子句避免使用 HAVING 子句,

HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作.如果能通过 WHERE 子句限制记录的数目,那就能减少这方面的开销.(即如果能不用 having 就尽量不要使用 having，必要的情况如条件中有聚合函数，那就只把聚合函数的条件写进having)

8 .减少对表的查询

在含有子查询的 SQL 语句中,要特别注意减少对表的查询.即如果不用子查询就尽量少使用子查询，或一个 SQL 能 使用两次子查询就可以获得结果，如果用三次子查询就会调高消耗。

9 .通过内部函数提高 SQL 效率

同一个结果，如果使用了内部函数，效率要高于人为在 SQL 中进行计算。

10 .使用表的别名

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

11 .用 EXISTS 替代 IN

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况

下, 使用 EXISTS(或 NOTEXISTS)通常将提高查询的效率.