欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

11条有效的SQL优化技巧

程序员文章站 2022-05-13 16:31:49
1. 选择最有效率的表名顺序(只在基于规则的优化器中有效) SQL 的解析器按照从右到左的顺序处理 FROM 子句中的表名,因此 FROM 子句中写在最后的表(我们称之为基础表)...

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)通常将提高查询的效率.