高效的SQL语句

1、高效的SQL语句飞狼 影响数据库性能的三个指标影响数据库性能的三个指标要确定影响性能瓶颈： CPU 内存 I/OSQL优化的作用优化的作用 SQL语句是用户操作数据库的唯一途径。 SQL优化是代码级的优化。 SQL语句有时候能够影响数据库性能的70-80%。 SQL语句的不同写法，性能上差距非常大。 SQL语句简单，但是精通难。 SQL语句的处理流程 共享SQL语句 使用内部函数提高SQL效率 避免使用和！= 避免使用IS NULL和IS NOT NULL UNION和UNION ALL EXISTS关键字与IN关键字 TRUNCATE和DELETE第一部分第一部分 打开游标 | -查看共享S

2、QL区，是否有相同的SQL语句 | | | 分析 | | | 定义 - | | - 绑定变量 | | | 捆绑 - - | | 并行处理 | 执行查询 | 返回结果 | 关闭游标SQL语句的处理流程语句的处理流程分析：分析： 到SHARED_POOL中查找是否有相同的SQL语句 分析语法 语义分析 视图转换 表达式转换 选择优化器 选择连接方式 选择连接顺序 选择数据搜索路径SQL语句的处理流程语句的处理流程 执行 ： 主要在于使用UPDATE和DELETE语句时，必须将行锁定，以免其他用户修改。Oracle先从数据库缓冲区中寻找是否存在所要的数据块，如果存在，就直接读或修改，否则从物理文件中

3、读到数据库缓冲区中。 SQL语句的处理流程语句的处理流程 返回结果 ： 对SELECT 语句需要返回结果的语句，首先看是否需要排序，需要，则排序后返回给用户，然后根据内存的大小不同，可以一次取出一行数据，一可以一次取一组数据。这时，可能要用到数据结构中的外部排序，并归排序等算法，所以如内存允许的话，尽量大会提高性能的 。SQL语句的处理流程语句的处理流程 共享SQL： Oracle内存中有一个区叫SHARED_POOL，这个区的主要作用就是将SQL语句存放在这个区内，当客户发出一个新的SQL语句，数据库引擎首先会到这个区查找是否有相同的SQL，如果有，则避免了解析、分析索引、制定执行计划等一系

4、列的动作，所以在开发的过程中要尽量使用共享的SQL语句。共享共享SQL语句语句 字符要相同： 两条SQL语句的大小写要完全一致 意义要相同： 两个用户访问的对象名称一样，但是如果一个访问的是自己的视图，另一个访问的是一个公有同义词，则无法复用。 变量要相同：共享共享SQL语句语句select clientname from clent_info 与 select CLIENTNAME from client_info 无法复用select . from client where clitno=:v_client_no 与select . from client where clitno=:p_

5、client_no 无法复用 尽量使用Oracle已经提供的函数和方法，避免不必要的重复性开发： 使用DECODE函数 使用DECODE函数代替复杂的if.else判断 使用MERGE关键字使用内部函数提高使用内部函数提高SQL效率效率 和！= 和! =都代表不等于的概念，在SQL开发的过程中尽量不要使用和！=，这个关键字会造成索引失效，使查询效率降低。 使用表sta_client_info（客户信息表）进行测试，其中在clitno（客户编号）列上建立索引。避免使用避免使用和！和！= 使用=查询条件的执行计划：避免使用避免使用和！和！=SQL conn cdbeff/cdbeffkmdevCo

6、nnected.SQL set autotrace traceonlySQL select * from sta_client_info where clitno=1000;no rows selectedExecution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=89) 1 0 TABLE ACCESS (BY INDEX ROWID) OF STA_CLIENT_INFO (Cost=2 Card=1 Bytes=89) 2 1 INDEX (RANGE SCAN) OF IDX_CL_CLITNO (N

7、ON-UNIQUE) (Cost =1 Card=1) 使用或者！=查询条件的查询计划：避免使用避免使用和！和！=SQL select * from sta_client_info where clitno1000;17485 rows selected.Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=17484 Bytes= 1556076) 1 0 TABLE ACCESS (FULL) OF STA_CLIENT_INFO (Cost=31 Card=174 84 Bytes=1556076) 使用and

8、关键字代替和！=： 避免使用避免使用和！和！=SQL select * from sta_client_info where clitno1000 and clitno1000;no rows selectedExecution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=89) 1 0 FILTER 2 1 TABLE ACCESS (BY INDEX ROWID) OF STA_CLIENT_INFO (Cost =2 Card=1 Bytes=89) 3 2 INDEX (RANGE SCAN) OF ID

9、X_CL_CLITNO (NON-UNIQUE) (Co st=1 Card=1) 从测试结果可以得出： 与！=关键字会在SQL查询过程中造成索引失效，在此时尽量使用and关键字来等价转换其相应的查询条件。避免使用避免使用和！和！= NULL和IS NOT NULL关键字： NULL在数据库中代表的是“无”，就是什么都没有。NULL值不存储在索引中，因此在索引列上带IS NULL 条件的查询不会使用索引，而是使用Table Access Full 操作解析查询语句，IS NOT NULL同理。 避免使用避免使用IS NULL 和和IS NOT NULL关键字关键字 使用=的方式查询：避免使用避

10、免使用IS NULL 和和IS NOT NULL关键字关键字SQL select * from sta_client_info where clitname =Jhon 2 ;no rows selectedExecution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=89) 1 0 TABLE ACCESS (BY INDEX ROWID) OF STA_CLIENT_INFO (Cost=2 Card=1 Bytes=89) 2 1 INDEX (RANGE SCAN) OF IDX_CLIENT_NAME

11、 (NON-UNIQUE) (Co st=1 Card=1) 使用IS NULL的方式查询：避免使用避免使用IS NULL 和和IS NOT NULL关键字关键字SQL select * from sta_client_info where clitname is null;Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=1 Bytes=89) 1 0 TABLE ACCESS (FULL) OF STA_CLIENT_INFO (Cost=31 Card=1 B ytes=89) UNION关键字： UNIO

12、N是两个集合的并，在集合上相当于AUB，过滤重复数据。 UNION ALL关键字： UNION ALL是两个集合的加，在集合运算上是A+B，不过滤重复数据。 在实际开发过程中要尽量使用UNION ALL代替UNION。UNION和和UNION ALL关键字关键字 IN关键字： IN是对结果值进行比较，判断一个字段是否存在于几个值的范围中。 EXISTS关键字： EXISTS检查是否有结果，判断是否有记录，返回的是一个布尔型（TRUE/FALSE）。 EXISTS关键字与关键字与IN关键字关键字 IN关键字： EXISTS关键字：下面两条下面两条SQL语句是等价的语句是等价的SELECT pro

13、j_no,proj_name FROM CDBPJ_PROJ ;Select * from sta_client_info c -sta_client_info为客户表where c.clitno in ( select clitno from sta_cont_info -sta_cont_info为合同表 )；Select * from sta_client_info clwhere exists( select x from sta_cont_info cont where cl.clitno=cont.clitno )； IN关键字： 上面的查询等价于：EXISTS关键字与关键字与IN

14、关键字关键字Select * from sta_client_info c -sta_client_info为客户表where c.clitno in ( select clitno from sta_cont_info -sta_cont_info为合同表 )； Select c.* from sta_client_info cl, (select clitno from sta_cont_info) cont where cl.clitno=cont.clitno EXISTS关键字： 上面的查询等价于：EXISTS关键字与关键字与IN关键字关键字Select * from sta_cli

15、ent_info clwhere exists( select x from sta_cont_info cont where cl.clitno=cont.clitno )； for v in (select * from sta_client_info) loop if exists(select x from sta_cont_info ct where ct.clitno=v.clitno) then output the record; end if; end loop; IN关键字： 从上面的结果可以看出来IN关键字实际上是将查询结果当作一张表，然后两张表连接查询出结果。 EXIS

16、TS关键字： 从上面等价结果上可以看出来，EXISTS关键字总是对外面的表进行全扫描。EXISTS关键字与关键字与IN关键字关键字从而： 当子查询的查询结果比较小的时候使用IN关键字是比较合理的。 当外部表比较小的时候（sta_client_info），使用EXISTS比较合理（因为小表的数据都被cache到缓存中），同时内部表要有索引。 当内外两张表都很大的时候，查询效率则与索引和其他因素有关，不能一定说谁效率更高。EXISTS关键字与关键字与IN关键字关键字 TRUNCATE关键字： 通过释放存储表数据所用的数据块来删除数据，并且只在事务日志中记录块的释放。 DELETE关键字： DELE

17、TE 语句每次删除一行，并在事务日志中为所删除的每行做记录，并使用回滚段。 结论：在整表数据删除的时候，使用TRUNCATE的效率要远远高于DELETE。TRUNCATE关键字和关键字和DELETE关关键字键字 TRUNCATE的缺点： 首先TRUNCATE不占用回滚段，即一旦执行，无法UNDO；其次DELETE删除数据，日志中记录的是一条一条删除语句，而TRUNCATE的操作在日志中不产生记录，无法进行基于此的数据恢复。TRUNCATE关键字和关键字和DELETE关关键字键字 尽量多的使用COMMIT（保持数据完整性下） 在程序的编码中尽量避免使用 * 避免隐性转换 索引列上=代替 避免索引

18、列上的计算 选择有效的表名顺序 WHERE子句的连接顺序 使用WHERE代替HAVING 减少数据库的访问次数第二部分第二部分COMMIT所释放的资源： 回滚段上用于恢复数据的信息。 被程序语句获得的锁。 redo log buffer 中的空间。 尽量多的使用尽量多的使用COMMIT COMMIT动作： 当界面出现COMMIT动作时，后台数据未必已经从data buffer进入到数据文件，而是确保了sql动作记录在了log files中。 当进行大数据量的操作（几万条到几十万条）的时候，不要将数据在内存中全部处理完毕再插入到数据文件中，而是在确保数据完整性的基础上分批处理（几千条数据作为一组

19、）。但是确保数据的完整性。 在使用LOOP循环时不要在把COMMIT写在LOOP里面造成每条数据处理完毕都要COMMIT。尽量多的使用尽量多的使用COMMIT * 在数据库中的解析： 在开发中，尤其是在批量数据导入导出，经常用到 * ，Oracle在解析的过程中，会将 * 解析成每个列，这个工作是要重新从数据字典中查询获得，这就意味着解析过程将消耗过多的时间，这个时间节省并不明显，但是当程序中包含大量的 * 时，效应就想当明显了。在程序的编码中尽量避免使用在程序的编码中尽量避免使用* 隐性转换： 这是最常犯的错误之一，其查询是不影响结果的，却影响效率，在查询的过程中由于数据类型的不符，造成隐性

20、地调用to_char或者to_number函数，例如表CONT_INFO： 合同信息表CONT_INFO避免隐性转换避免隐性转换列名数据类型长度索引名称Cont_nonumber12Cont_namevarchar250Idx_cont_nameCont_descvarchar2200 查询合同名称为1000的合同信息，没有隐性转换的查询：避免隐性转换避免隐性转换SQL select * from cont_info where cont_name=1000;no rows selectedExecution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE

21、1 0 TABLE ACCESS (BY INDEX ROWID) OF CONT_INFO 2 1 INDEX (RANGE SCAN) OF IDX_CONT_NAME (NON-UNIQUE) 查询合同名称为1000的合同信息，存在隐性转换的查询：SQL select * from cont_info where cont_name=1000;no rows selectedExecution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF CONT_INFO避免隐性转换避免隐性转换 结论： 第二个

22、查询条件使用了： 而cont_name字段的数据类型为字符串（varchar2(50)），则在解析过程中后台做了隐性转换，造成索引失效：避免隐性转换避免隐性转换cont_name=1000cont_name=to_char(1000) 低效：select * from emp where deptno 3 高效：select * from emp where deptno =4 两者的区别在于，前者dbms将直接跳到第一个deptno等于4的记录，而后者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的 。索引列上索引列上= 代替代替 在索引上使用计算的查询：避免索引

23、列上的计算避免索引列上的计算SQL select * from emp where sal*12 select * from emp where sal select count(*) from cont_info1,client_info; COUNT(*)- 31488480Executed in 11.219 secondsSQL select count(*) from client_info,cont_info; COUNT(*)- 31488480Executed in 48.407 seconds WHERE子句： ORACLE采用自下而上的WHERE语句，表之间的连接应该写在其

24、他WHERE条件之前，能过滤掉大多数据的条件应该写在WHERE条件之后。 WHERE子句的连接顺序子句的连接顺序 对比两条SQL语句：WHERE子句中的连接顺序子句中的连接顺序SQL select count(*) from cont_info1 cont ,client_info cl 2 where cl.lastmoditime select count(*) from cont_info1 cont ,client_info cl 2 where cl.clittype=新客户 and 3 cl.lastmoditime select count(clitname),clittype

25、from client_info 2 group by clittype having clittype=老客户;Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 FILTER 2 1 SORT (GROUP BY) 3 2 TABLE ACCESS (FULL) OF CLIENT_INFO 对比用WHER子句：使用使用WHERE子句来代替子句来代替HAVING子句子句SQL select count(clitname),clittype from client_info 2 where clittype=老客户 3 group

26、by clittype ;Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (GROUP BY) 2 1 TABLE ACCESS (FULL) OF CLIENT_INFO SQL解析过程： 每条SQL语句在数据库中都要进行解析（如果两条SQL语句不一样的话），估算索引，绑定变量、制定执行计划、核对权限、查找对象所在数据块、将数据块CACHE到内存中尽量发挥每条SQL语句的最大能量，避免重复地从数据库中获取数据。减少对数据库的访问次数减少对数据库的访问次数 例如客户表CLIENT_INFO（clitno为主键）：减少对数

27、据库的访问次数减少对数据库的访问次数低效的会话低效的会话： -第一条SQL语句 select clitname,clittype from client_info where clitno=005237 -第二条SQL语句 select clitname,clittype from client_info where clitno=005231高效的会话：高效的会话：select cl1.clitname , cl2.clitname from client_info cl1 , client_info cl2 where cl1.clitno=005237 and cl1.clitno=00

28、5231 尽量将SQL语句封装在PROCEDURE中 使用表连接代替EXISTS 使用函数索引 使用APPEND关键字和NOLOGGING关键字 批量绑定 表的连接方式第三部分第三部分 将SQL语句封装在PROCEDURE中： 在使用SQL语句时，首先会在对其进行语义分析、分析索引、制定执行计划而使用PROCEDURE则略去了语义分析等大部分重复性的工作，更重要的是SQL语句需要客户端与服务器端交互，而PROCEDURE是运行在服务器端，最后只将结果回显给客户端。尽量将尽量将SQL语句封装在语句封装在PROCEDURE中中 使用EXISTS关键字：使用表连接来代替使用表连接来代替EXISTSS

29、QL select count(cl.clitno) from client_info cl 2 where exists 3 ( 4 select x from cont_info1 cont where cont.clitno=cl.clitno 5 ) 6 ;COUNT(CL.CLITNO)- 123360Executed in 2.109 seconds 是用表连接：使用表连接来代替使用表连接来代替EXISTSSQL select count(cl.clitno) from client_info cl, 2 cont_info1 cont 3 where cl.clitno=cont

30、.clitno 4 ;COUNT(CL.CLITNO)- 484448Executed in 0.5 seconds 最常见于对日期的查询： 查询语句的时候，利用索引可以避免全表搜索，加快查询速度，但是如果where子句是这样写的话：where to_char(birth_day,YYYY-MM-DD) = 1978-11-10 ，而建立的索引只是针对birth_day本身，那么这个索引对于查询是没有任何效果的，因为查询是在函数运算过后再开始比较的 。使用函数索引使用函数索引 使用函数索引：使用函数索引使用函数索引SQL create index idx_lastmoditime on sta

31、_client_info (trunc(lastmoditime,dd);Index created.SQL select * from sta_client_info where trunc(lastmoditime,dd)=trunc(sysdate,dd);no rows selectedExecution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=175 Bytes=155 75) 1 0 TABLE ACCESS (BY INDEX ROWID) OF STA_CLIENT_INFO (Cost=2 Card=175

32、 Bytes=15575) 2 1 INDEX (RANGE SCAN) OF IDX_LASTMODITIME (NON-UNIQUE) (C ost=1 Card=70) APPEND提示： 每个数据块都有一个高水准线，代表着该数据块数据量曾经达到的最高位置，在使用APPEND提示以后，新的数据会直接插入到高水准线以上的空间，而不用到高水准线下去查找剩余空间，可以极大地提高数据的插入速度。但是同样带来了浪费空间、产生大量碎片的负面影响。批量数据插入的时候使用批量数据插入的时候使用APPEND提示提示 使用APPEND提示：批量数据插入的时候使用批量数据插入的时候使用APPEND提示提示SQ

33、L insert into testing 2 select * from client_info;279760 rows insertedExecuted in 6.188 secondsSQL insert /*+Append*/ into testing1 2 select * from client_info;279760 rows insertedExecuted in 1.656 seconds NOLOGGING关键字： 在Oracle中有很多语法都NOLOGGING这个关键字，代表的时执行这条SQL语句的时候不记录日志，这样就减少了REDO BUFFER与REDO LOGFIL

34、ES的交互，实现了速度的提升，但是由于不记录日志，在恢复的时候就无法实现基于时间点的恢复。APPEND配合配合NOLOGGING APPEND配合NOLOGGINGAPPEND配合配合NOLOGGINGSQL insert /*+Append*/ into testing1 2 select * from client_info nologging 3 ;279760 rows insertedExecuted in 11.734 secondsSQL insert /*+Append*/ into testing 2 select * from client_info 3 ;279760 r

35、ows insertedExecuted in 22.11 seconds 批量绑定原理： 有些情况下可以使用变量批量绑定的方法来实现代码的优化，批量绑定变量的原理就是使用集合变量，将变量批量赋值，在赋值以后将数据批量插入到目的表格中。 批量绑定批量绑定 没有使用批量绑定的运行测试结果：批量绑定批量绑定-未使用批量绑定begin dbms_output.put_line(begin:|to_char(sysdate,yyyy hh24:mi:ss); for v in (select * from emp) loop insert into demo values(v.ename); end

36、loop; dbms_output.put_line(end:|to_char(sysdate,yyyy hh24:mi:ss); end;begin:2006 19:12:30end:2006 19:13:48-十次平均值插入表格时间为18.21秒 使用批量绑定的运行测试结果：批量绑定批量绑定/*使用批量绑定*/declare type newtype is table of demo.ename%type INDEX BY BINARY_INTEGER; shape newtype; i number; j number; begin dbms_output.put_line(beginf

37、orall:|to_char(sysdate,yyyy hh24:mi:ss); for v in (select rownum,ename from emp) loop shape(v.rownum):=v.ename; end loop; select count(*) into i from emp; forall j in 1.i insert into demo values(shape(j); commit; dbms_output.put_line(endforall|to_char(sysdate,yyyy hh24:mi:ss); end;-经过计算插入表格平均时间约为6秒

38、从而： 在主机环境、数据库参数一样的情况下，使用批量绑定的编程方式效率要远高于非批量绑定的使用。批量绑定批量绑定 NESTLOOPS： 嵌套循环连接 * SORT MERGE： 排序合并连接 * HASH JOIN： 哈希连接 * CLUSTER JOIN：集群连接 CARTESIAN： 笛卡尔连接 IDEX JOIN： 索引连接表的连接方式表的连接方式 驱动表 Oracle选择一张表作为连接的驱动表，即第一个记录源通常称为外部表外部表（Outer Table），或者驱动表驱动表。 内部表 相对于驱动表来说，由驱动表进行驱动连接的表或者数据源被成为 内部表内部表（Inner Table）。表的

39、连接方式表的连接方式名词解释名词解释 NESTLOOPS： 嵌套循环连接 扫描一张表，每读到一条记录，就根据索引去另一张表里查找。在关联查询的过程中，Oracle会持续提取驱动表中其他符合条件的记录与内部表关联查询。这两个过程是并行进行的，因此嵌套循环连接返回前几条记录的速度是非常快的。 表的连接方式表的连接方式 NESTLOOPS的连接过程： 一个是外部循环，提取驱动表中符合条件的每条记录。另外一个是内部循环，根据外循环中提取的每条记录对内部表进行连接查询相应的记录。由于这两个循环是嵌套进行的，故此种连接方法称为嵌套循环连接 表的连接方式表的连接方式NESTED LOOP NESTLOOPS

40、：表的连接方式表的连接方式SQL select /*+use_nl(s,c) */ s.clitno,c.contno from client_info s,cont_info c where s.clitno=c.clitno and c.=1000;no rows selectedExecution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=3638) 1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=3638) 2 1 TABLE ACCESS (BY INDEX ROWID)

41、OF CONT_INFO (Cost=2 Ca rd=1 Bytes=3265) 3 2 INDEX (UNIQUE SCAN) OF SYS_C0038012 (UNIQUE) (Cost=1 Card=100) 4 1 TABLE ACCESS (BY INDEX ROWID) OF CLIENT_INFO (Cost=1 C ard=1 Bytes=373) 5 4 INDEX (UNIQUE SCAN) OF SYS_C0038011 (UNIQUE) NESTLOOPS： 优化器将作为CONT_INFO驱动表，根据唯一性索引SYS_C0038012返回符合contno=1000的数据

42、，然后再与被驱动的表CLIENT_INFO关联查询出相应的clitno以及Contno，表CLIENT_INFO上也有唯一性索引SYS_C0038011，故能很多快地查询出对应的结果。表的连接方式表的连接方式 SORT MERGE： 排序合并连接 在排序合并连接中是没有驱动表的概念的，两个互相连接的表按连接列的值先排序，排序完后形成的结果集再互相进行合并连接提取符合条件的记录。相比嵌套循环连接，排序合并连接比较适用于返回大数据量的结果。 表的连接方式表的连接方式 SORT MERGE：表的连接方式表的连接方式SQL select /*+use_merge(s,c) */ * from clie

43、nt_info s,cont_info c where s.clitno=c.clitno;30278 rows selected.Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1959 Card=70980 Byte s=258225240) 1 0 MERGE JOIN (Cost=1959 Card=70980 Bytes=258225240) 2 1 TABLE ACCESS (BY INDEX ROWID) OF CONT_INFO (Cost=826 Card=70980 Bytes=231749700) 3 2

44、 INDEX (FULL SCAN) OF IDX_CONT_CLITNO (NON-UNIQUE) (C ost=26 Card=70980) 4 1 SORT (JOIN) (Cost=1133 Card=20420 Bytes=7616660) 5 4 TABLE ACCESS (FULL) OF CLIENT_INFO (Cost=26 Card=204 20 Bytes=7616660) SORT MERGE：对连接的每个表做table access full；对table access full的结果进行排序；进行merge join对排序结果进行合并 。可以看出来SORT MERGE的主要性能消耗是在内存上。表的连接方式表的连接方式 HASH JOIN： 哈希连接 是CBO 做大数据集连接时的常用方式。优化器扫描小表（或数据源）做为驱动表，利用连接键（也就是根据连接字段计算hash 值）在内存中建立