当前位置:首页 > 开发 > 数据库 > 正文

ORACLE 复习笔记之SQL语句的优化

发表于: 2014-07-29   作者:eksliang   来源:转载   浏览:
摘要: 转载请出自出处:http://eksliang.iteye.com/blog/2097999   SQL语句的优化总结如下   sql语句的优化可以按照如下六个步骤进行: 合理使用索引 避免或者简化排序 消除对大表的扫描 避免复杂的通配符匹配 调整子查询的性能 EXISTS和IN运算符 下面我就按照上面这六个步骤分别进行总结:

转载请出自出处:http://eksliang.iteye.com/blog/2097999

  SQL语句的优化总结如下

 

sql语句的优化可以按照如下六个步骤进行:

  1. 合理使用索引
  2. 避免或者简化排序
  3. 消除对大表的扫描
  4. 避免复杂的通配符匹配
  5. 调整子查询的性能
  6. EXISTS和IN运算符

下面我就按照上面这六个步骤分别进行总结:

  • 1-------------------------------------合理使用索引

  首先要明确创建索引的根本目的是:提高查询效率;

  使用原则如下:

1.1、在条件表达式中经常用到的不同值较多的列上建立索引

1.2、在不值少的列上不要创建索引,或者位图索引

      例如:在员工表的性别列上,只有“男”和“女”两个不同值,如果建立索引,不但不会提高性能

1.3、在经常进行连接,但是没有指定为外键的列上建立索引

      根据我的经验,在实际开发中,数据库的设计是不会建立索引的,都是通过在外键上面建立索引进行关联,这样性能更好;

1.4.在频繁进行排序或分组的列上建立索引

1.5.如果待排序的列有多个,可以建立组合索引

         例如:order by D,E 那要注意:在建立索引时,注意D列和E列和排序后面的顺序要相同,不然不管是那种数据库都不会用到这个索引;

1.6.如果条件表达式中经常对某个列应用某个函数后指定查询条件,则应建立函数索引

备注:对于Oracle中怎样建立索引,有多少索引,以及应用场景,在接下来的帖子中我会加进来

 

  • 使用索引需要注意的地方

 下面情况的SQL,不会用到列上的索引,应尽量避免使用:

 1.  存在数据类型的隐形转换时,例如

 

select * from emp where empno = ‘7369’;

 

    注意上面的empno列是number类型的,这样转化就不会用到这个索引

 

2. 列上有数学运算时,例如

 

Select * from emp where sal*2 <1000;

 

  这是因为在索引上只有sal的值,没有sal*2的值,应该改成

 

select * from emp where sal<1000/2 ;

 

 

 3.使用不等于(<>)运算时 例如

 

 

Select * from emp where deptno <>10;

 

 4.使用substr字符串函数时,例如

 

 

Select * from emp where substr(ename,1,3)=‘SMI’;
 5.‘%’通配符在第一个字符时,例如

 

 

Select * from emp where ename like’%th’;

 

 6.字符串连接( || )时,例如

 

 

Select * from emp where ename || ‘abc’ = ‘SMITHabc’;

 

 

 

 

  • 2-------------------------------------避免或者简化排序

2.1 应当简化或避免对大型表进行重复的排序,以下情况,oracle是会默认对他排序的情况

  • SQL中包含Group By子句
  • SQL中包含Order By子句
  • SQL中包含Distinct子句
  • SQL中包含Minus或Union子句
  • in子句中的SQL子查询

2.2 以下情况不能有效地利用索引

1.待排序列没有全部建立索引,例如

 

order by D,E 而在表中只在D列上创建了索引
Group by 或者 Order by 子句中列的顺序与索引的列的顺序不同
 2.连接查询时排序的列来自不同的表(索引不能跨表)

 

 避免或简化排序的总结如下:为了避免不必要的排序,就要正确地增建索引,合理地合并数据库表。如果排序不可避免,那么应当试图简化它,如缩小排序的列的范围等

 

 

  • 3-------------------------------------消除对大表的扫描

3.1. 在连接查询中,对表的顺序存取可能对查询效率产生致命的影响。避免这种情况的主要方法就是对连接的列进行索引。例如有两个表,学生表(学号、姓名、年龄…)和选课表(学号、课程号、成绩)。如果两个表要做连接,就要在”学号”这个连接字段上建立索引。

 

 

3.2 使用并集来避免顺序存取。尽管在所有的检查列上都有索引,但某些形式的 where 子句强迫数据库使用顺序存取。例如

 

Select * from abc where a>10 or b<10;

 

       尽管我们在B和C列上都建立了索引,但是在上面语句中,优化器还是使用顺序存取方式扫描整个表。因为这个语句要检索的是分离的行的集合,可改为如下语句

 

 

Select * from abc where a>10
Union
Select * from abc where b<10

 

 

 

  • 4-------------------------------------避免困难的通配符匹配

复杂的通配可能导致耗时的查询。例如

 

Select * from customer where zipcode like ’98_ _ _’;

 

 即使在zipcode字段上建立了索引,在这种情况下也还是采用顺序扫描的方式。应改成

 

 

Select * from customer 
where zipcode >=’98000’ and zipcode <‘ 99000’

 

 

 

 

  • 5-------------------------------------调整子查询的性能

子查询包括两种,关联子查询、非关联子查询,下面分别对这两种进行分析

5.1 非关联子查询

       非关联子查询时子查询只会执行一次,而且结果集是已经排序完毕的,并保存在一个Oracle的临时段中,其中的每一个记录在返回时都会被父查询所引用。在子查询返回大量记录的情况下,将这些结果集排序,以及将临时数据段进行排序会增加大量的系统开销。例如

 

select emp_name from emp_number where emp_id in (select emp_id from emp_func);

 

 

 

5.2 关联子查询

解释一下,什么是关联子查询,就是子查询中的条件用到了父查询中的列,例如:

select emp_name from emp_number where emp_id in (select emp_id from emp_func where emp_number.emp_id = emp_func.emp_id);

 

      对返回到父查询的记录来说,子查询会每行执行一次。因此必须保证子查询尽可能用到索引。关联子查询的系统开销更高。

 对于子查询我的应用原则就是:查询嵌套的层次越多,效率越低,因此应当尽量避免子查询。如果子查询不可避免,那以要在子查询中过滤掉尽可能多的行。

 

  • 6-------------------------------------EXISTS和IN运算符

6.1 带IN的关联子查询是多余的,因为IN子句和子查询中相关操作的功能是一样的。例如

Select emp_name from emp_member where emp_id in (select emp_id from emp_func where emp_member.emp_id = emp_func.emp_id);

 

 

6.2 为非关联子查询指定EXISTS子句是不适当的,因为这样会产生笛卡尔积)。例如

Select emp_name from emp_member
Where exists(select emp_id from emp_func);

 

 

6.3 尽量不要使用NOT IN 子句,虽然使用MINUS子句要进行两次查询,使用MINUS子句依然比 NOT IN 子句快,这种情况你应该这么写

Select emp_name from emp_member where emp_id 
in (select  emp_id from emp_member 
minus 
Select emp_id from emp_func where func_id like ’81%’ );

 

 

 

 

最后测试索引的性能,下面这个例子是在网上看到的,我直接拿过来了,我觉得非常经典

1.新建dumpy表,然后向dumpy表中添加1000万行记录,其中ID列是有序的整数,Name是随机的字符串,Rand列是随机的数值

测试环境Oracle,创建表的语法如下:

create table dumpy
(
   id       number(10),
   name     varchar2(10),
   rand     number(10,2)
);

 

添加数据的语法如下:

declare
  recordcount integer := 10000000;  -- 1000万条记录
begin
  for i in 1..recordcount loop
    insert into dumpy(id, name, rand)
    values ( i, dbms_random.string('x', 8),
             abs(dbms_random.random) / 100.0);
    if mod(i, 1000) = 0 then
      commit;                          -- 每插入1000条提交
    end if;
  end loop;
end;

 数据添加成功后,查询Rand值介于1000到2000之间的行。添加索引,比较优化前后查询速度的变化。

  提示:执行命令 SET TIMING ON,可以显示每次语句执行时间

 

 

 

 

 

    

 

      

ORACLE 复习笔记之SQL语句的优化

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
sql语句的优化 1.在where子句中使用 is null 或 is not null 时,oracle优化器就不能使用索引了. 2.
最近做查询时,写的一条查询语句用了两个IN,导致tuexdo服务积压了不少,用户没骂就不错了。最后经
一、问题的提出 在应用系统开发初期,由于开发数据库数据比较少,对于查询SQL语句,复杂视图的的编
→SQL 支持下列类别的命令: 数据定义语言(DDL)、数据操纵语言(DML)、事务控制语言(TCL)、数据
1.查看Oracle系统是否采用归档模式: col name format a30; select dbid,name,log_mode from v$data
程序开发,特别是web开发,对性能的要求比较高,在线列表信息要快又要占用减少服务器资源,在大众化的网
--SQL Server 强大的分区技术(使用语句检测和优化数据库 (MSSQL个人笔记之数据库优化之路 三) /**
sql语句优化 性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,
大量插入数据时SQL语句的优化 1)对于MyISAM类型的表,可以通过以下方式快速地导入大量数据: 这两
Sql效能优化总结 前一段时间公司系统遇到了严重的系统效能问题,经过一段时间的努力,现在已缓解了
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号