SQL优化法则小记

SQL优化技巧

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

oracle的解析器按照从右到左的顺序处理 from 子句中的表名,from子句中写在最后的表(基础表 driving table)将被最先处理,在 from 子句中包含多个表的情况下, 你必须选择记录条数最少的表作为基础表。如果有 3 个以上的表连接查询, 那就需 要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

2.where子句中的连接顺序:

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

3.select子句中避免使用 ‘ * ‘:

oracle在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数 据字典完成的, 这意味着将耗费更多的时间.

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

oracle在内部执行了许多工作: 解析 SQL 语句, 估算索引的利用率, 绑定变量,读数据块等

5.在 SQL*Plus , SQL*Forms 和 Pro*C 中重新设置arraysize参数, 可以增加每 次数据库访问的检索数据量 ,建议值为200

6.使用 decode函数来减少处理时间:

使用 decode函数可以避免重复扫描相同记录或重复连接相同的表.

7.删除重复记录:

最高效的删除重复记录方法 ( 因为使用了 rowid)例子:

delete from emp e where

e.rowid > (select min(x.rowid)

from emp x where x.emp_no=e.emp_no);

8.整合简单,无关联的数据库访问:

如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之 间没有关系)

9.用truncate替代delete:

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息.

如果你没有commit事务,oracle会将数据恢复到删除之前的状态(准确 地说是恢复到执行删除命令之前的状况) 而当运用truncate时, 回滚段不再存放 任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时 间也会很短. (译者按: truncate只在删除全表适用,truncate 是 DDL 不是 DML) .

10.尽量多使用 commit:

只要有可能,在程序中尽量多使用 commit, 这样程序的性能得到提高,

需求也会因为 commit所释放的资源而减少:

commit所释放的资源:

a. 回滚段上用于恢复数据的信息.

b. 被程序语句获得的锁

c. redo log buffer 中的空间

d. oracle为管理上述 3 种资源中的内部花费

11.用 where子句替换having子句: 避免使用 having子句, having只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过 where子句限制记录的数目,那就能减 少这方面的开销. (非 oracle 中)on、where、having 这三个都可以加条件的子句中, on 是最先执行,where 次之,having 最后,因为 on 是先把不符合条件的记录过滤后 才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的,where 也应该比 having 快点的,因为它过滤数据后才进行 sum,在两个表联接时才用 on 的,所以在一个表的时候,就剩下 where 跟 having 比较了。在这单表查询统计的情 况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样的,只是 where 可以使用 rushmore 技术,而 having 就不能,在速度上后者要慢如果要涉及到 计算的字段,就表示在没计算之前,这个字段的值是不确定的,根据上篇写的工作 流程,where 的作用时间是在计算之前就完成的,而 having 就是在计算后才起作用 的,所以在这种情况下,两者的结果会不同。在多表联接查询时,on 比 where 更早 起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由 where 进行过滤,然后再计算,计算完后再由 having 进行过滤。由此可见,要想过 滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定,放在那里.

12.减少对表的查询:

在含有子查询的 SQL 语句中,要特别注意减少对表的查询.例子:

select tablename from tables

where (tab_name,db_ver)=(select tab_name,db_ver from tab_colimns

where version =604)

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

复杂的 SQL 往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际 工作中是非常有意义的 .

14.使用表的别名(Alias):

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

15.用 exists替代 in、用 not exists 替代not in:

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接,在这种情况下, 使用exists(或not exists )通常将提高查询的效率. 在子查询中,not in子句将执行一个内部的排序和合并. 无论在哪种情况下,not in都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用 not in ,我们可以把它改写成 外连接(Outer Joins)或not exists。

例子:

(高效)

select # from emp where

empno>0 and exists

(select 'x' from dept where dept.deptno =emp.deptno and loc='melb')

(低效)

select * from emp where empno>0 and deptno in

(select deptno dept where loc='melb')

16.识别'低效执行'的 SQL 语句: 虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决 问题始终是一个最好的方法:

select executions , disk_reads, buffer_gets,

round((buffer_gets-disk_reads)/buffer_gets,2) hit_radio,

round(disk_reads/executions,2) reads_per_run,

sql_text from v$sqlarea where

executions>0 and buffer_gets > 0 and

(buffer_gets-disk_reads)/buffer_gets < 0.8

order by 4 desc;

17.用索引提高效率:

索引是表的一个概念部分,用来提高检索数据的效率,oracle使用了一个复杂的自 平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当oracle找出执行查询 和 update 语句的最佳路径时, oracle优化器将使用索引. 同样在联结多个表时使用 索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一 性验证.。那些 long或long raw数据类型, 你可以索引几乎所有的列. 通常, 在大 型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效 率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需 要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本 身也会被修改. 这意味着每条记录的 insert delete update将为此多付出 4 , 5 次 的磁盘 I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询 反应时间变慢.。定期的重构索引是有必要的: alter index <indexname> rebuild <tablespacename>

18.用 exists替换 distinct: 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在select子句中 使用 distinct. 一般可以考虑用 EXIST 替换, EXISTS 使查询更为迅速,因为RDBMS核 心模块将在子查询的条件一旦满足后,立刻返回结果.

例子:

(低效):

select distinct dept_no,dept_name from dept d , emp e where

d.dept_no = e.dept_no

(高效):

select dept_no,dept_name

from dept d

where

exists ( select ‘x' from emp e where e.dept_no = d.dept_no);

19.sql 语句用大写的;因为 oracle 总是先解析 sql 语句,把小写的字母转换成大 写的再执行

20.在 java 代码中尽量少用连接符“+”连接字符串!

21.避免在索引列上使用 not通常, 我们要避免在索引列上使用 not, not会产生在和在索引列上使用函数相同的影响. 当 oracle”遇到”not,他就会停止使用索引转而执行全表扫描.

22.避免在索引列上使用计算:

where子句中,如果索引列是函数的一部分.优化器将不使用索引而使用全表扫描. 举例:

低效:

select … from dept where sal * 12 > 25000;

高效:

select … from dept where sal > 25000/12;

23.用>=替代>

高效: select * from emp where deptno >=4

低效: select * from emp where deptno >3

两者的区别在于, 前者 DBMS 将直接跳到第一个 dept等于 4 的记录而后者将首先定 位到 deptno =3 的记录并且向前扫描到第一个 dept大于 3 的记录.

24.用 union替换 or(适用于索引列) 通常情况下, 用 union 替换 where子句中的 or将会起到较好的效果. 对索引列使用or将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有 被索引, 查询效率可能会因为你没有选择 or而降低. 在下面的例子中, loc_id和 region上都建有索引.

高效:

select loc_id , loc_desc , region from location

where loc_id = 10

union

select loc_id , loc_desc , region

from location

where region = “melbourne”

低效:

select loc_id , loc_desc , region from location

where loc_id = 10 or region = “melbourne”

如果你坚持要用 or, 那就需要返回记录最少的索引列写在最前面.

25.用 in来替换or 这是一条简单易记的规则,但是实际的执行效果还须检验,两者的执行路径似乎是相同的.

低效:

select…. from location where loc_id = 10 or loc_id = 20 or loc_id = 30

高效

select… from location where loc_in in (10,20,30);

26.避免在索引列上使用is null和is not null避免在索引中使用任何可以为空的列,oracle将无法使用该索引.对于单列索引, 如果列包含空值,索引中将不存在此记录. 对于复合索引,如果每个列都为空,索 引中同样不存在此记录. 如果至少有一个列不为空,则记录存在于索引中.举例: 如 果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的 A,B 值为 (123,null) , oracle将不接受下一条具有相同 A,B 值(123,null)的记录(插入). 然而如果所有的索引列都为空,oracle将认为整个键值为空而空不等于空. 因此你可以插 入 1000 条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以 where子句中对索引列进行空值比较将使 oracle停用该索引.

低效: (索引失效)

select … from department where dept_code is not null;

高效: (索引有效)

select … from department where dept_code >=0;

27.总是使用索引的第一个列:

如果索引是建立在多个列上, 只有在它的第一个列(leading column)被 where 子句引 用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则,当仅引用索引的 第二个列时,优化器使用了全表扫描而忽略了索引.

28.用 union-all替换 union( 如果有可能的话): 当 SQL 语句需要 union 两个查询结果集合时,这两个结果集合会以 union-all的方 式被合并, 然后在输出最终结果前进行排序. 如果用 union-all替代union, 这样排 序就不是必要了. 效率就会因此得到提高. 需要注意的是, union-all 将重复输出两 个结果集合中相同记录. 因此各位还是要从业务需求分析使用 union-all 的可行性. union将对结果集合排序,这个操作会使用到 sort_area_size 这块内存. 对于这块 内存的优化也是相当重要的. 下面的 SQL 可以用来查询排序的消耗量.

低效:

select acct_num, balance_amt

from debit_transactions

where tran_date = '31-dec-95'

union

select acct_num, balance_amt

from debit_transactions

where tran_date = '31-dec-95'

高效:

select acct_num, balance_amt

from debit_transactions

where tran_date = '31-dec-95'

union all

select acct_num, balance_amt

from debit_transactions

where tran_date = '31-dec-95'

29.用 where 替代order by:

order by 子句只在两种严格的条件下使用索引.

order by 中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.

order by中所有的列必须定义为非空.

where 子句使用的索引和order by子句中所使用的索引不能并列.

例如:

表 dept包含以下列:

dept_code pk not null dept_desc not null dept_type null

低效:

(索引不被使用)

select dept_code from dept order by dept_type

高效:

(使用索引)

select dept_code from dept where dept_type > 0

30.避免改变索引列的类型:

当比较不同数据类型的数据时, ORACLE 自动对列进行简单的类型转换. 假设 EMPNO 是一个数值类型的索引列.

select … from emp where empno = ‘123'

实际上,经过 ORACLE 类型转换, 语句转化为:

select … from emp where empno = to_number(‘123')

幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.

现在,假设 emp_type 是一个字符类型的索引列.

select … from emp where emp_type = 123

这个语句被oracle转换为:

select … from emp whereto_number(emp_type)=123

因为内部发生的类型转换, 这个索引将不会被用到!

为了避免oracle对你的SQL进 行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, oracle会优先转换数值类型到字符类型

31.需要当心的 where子句: 某些 select语句中的 where子句不使用索引.

这里有一些例子. 在下面的例子里,

(1)‘!=' 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而 不能告诉你什么不存在于表中.

(2) ‘||'是字符连接函数. 就象其他函数那样, 停用了 索引.

(3) ‘+'是数学函数. 就象其他数学函数那样, 停用了索引.

(4)相同的索引列不能 互相比较,这将会启用全表扫描.

32.

a. 如果检索数据量超过 30%的表中记录数.使用索引将没有显著的效率提高.

b. 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!

33.避免使用耗费资源的操作: 带有 distinct,union,minus,intersect,order by 的 SQL 语句会启动 SQL 引擎 执行耗费资源的排序(SORT)功能. distinct 需要一次排序操作, 而其他的至少需要执 行两次排序. 通常, 带有union,minus,intersect 的 SQL 语句都可以用其他方式重 写. 如果你的数据库的 sort_area_size 调配得好, 使用union,minus,intersect 也是可以考虑的, 毕竟它们的可读性很强

34.优化 group by: 提高 group by 语句的效率, 可以通过将不需要的记录在group by 之前过滤掉.下 面两个查询返回相同结果但第二个明显就快了许多.

低效:

select job , avg(sal) from emp

group by job

having job = ‘president' or job = ‘manager'

高效:

select job , avg(sal) from

emp where job = ‘president'

or job = ‘manager'

group by job

原文发布于微信公众号 - 数据库SQL(SQLdba)

原文发表时间:2016-07-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏HTML5学堂

操作符与数据类型转换

上一期堡堡给大家讲解了关于JS的基础语法,虽然是一些非常基础的知识,但是它对大家的后期学习奠定了一定的基础。知识像一张网,基础越扎实,网住的鱼就越多,要告诉大家...

2778
来自专栏python3

python3--基础数据类型

切片就是通过索引(索引:索引:步长)截取字符串的一段,形成新的字符串(原则就是顾头不顾尾)

542
来自专栏Java成神之路

Oracle学习笔记_02_基本SQL

        SQL 语言大小写不敏感。         SQL 可以写在一行或者多行 关键字不能被缩写也不能分行         各子句一般要分行写。 ...

672
来自专栏MasiMaro 的技术博文

地址、指针与引用

计算机本身是不认识程序中给的变量名,不管我们以何种方式给变量命名,最终都会转化为相应的地址,编译器会生成一些符号常量并且与对应的地址相关联,以达到访问变量的目的...

521
来自专栏前端架构

js易错笔试题——javascript的坑若干!

执行的时候有个变量查找的过程,如果在当前函数体内没找到,就会到定义的函数体的外层函数中去寻找,一直向上到全局对象中寻找,还是找不到就会报TypeError错误

432
来自专栏Linyb极客之路

MySql查询性能优化

在访问数据库时,应该只请求需要的行和列。请求多余的行和列会消耗MySql服务器的CPU和内存资源,并增加网络开销。 例如在处理分页时,应该使用LIMIT限制My...

904
来自专栏有趣的Python和你

Python数据结构列表字典元组和集合

1016
来自专栏阮一峰的网络日志

理解字节序

1. 计算机硬件有两种储存数据的方式:大端字节序(big endian)和小端字节序(little endian)。 举例来说,数值0x2211使用两个字节储存...

2837
来自专栏菜鸟计划

javascript 基本概念

一、在HTML中使用javascript 1.直接是用<script></script>标签。 2.外部引入 <script type="javascript"...

2513
来自专栏程序员互动联盟

【编程基础】C语言位

位制就是为了减小存储大小,把一个char, unsigned int, int的存储空间进行拆分后,对每个进行操作。 给出一段代码: #include <st...

2987

扫描关注云+社区