高效的SQL语句

高效的SQL语句,飞狼,影响数据库性能的三个指标,要确定影响性能瓶颈：CPU内存I/O,SQL优化的作用,SQL语句是用户操作数据库的唯一途径。SQL优化是代码级的优化。SQL语句有时候能够影响数据库性能的70-80%。SQL语句的不同写法，性能上差距非常大。SQL语句简单，但是精通难。,SQL语句的处理流程共享SQL语句使用内部函数提高SQL效率避免使用和！=避免使用ISNULL和ISNOTNULLUNION和UNIONALLEXISTS关键字与IN关键字TRUNCATE和DELETE,第一部分,打开游标|-查看共享SQL区，是否有相同的SQL语句|分析|定义-|-绑定变量|捆绑-|并行处理|执行查询|返回结果|关闭游标,SQL语句的处理流程,分析：到SHARED_POOL中查找是否有相同的SQL语句分析语法语义分析视图转换表达式转换选择优化器选择连接方式选择连接顺序选择数据搜索路径,SQL语句的处理流程,执行：主要在于使用UPDATE和DELETE语句时，必须将行锁定，以免其他用户修改。Oracle先从数据库缓冲区中寻找是否存在所要的数据块，如果存在，就直接读或修改，否则从物理文件中读到数据库缓冲区中。,SQL语句的处理流程,返回结果：对SELECT语句需要返回结果的语句，首先看是否需要排序，需要，则排序后返回给用户，然后根据内存的大小不同，可以一次取出一行数据，一可以一次取一组数据。这时，可能要用到数据结构中的外部排序，并归排序等算法，所以如内存允许的话，尽量大会提高性能的。,SQL语句的处理流程,共享SQL：Oracle内存中有一个区叫SHARED_POOL，这个区的主要作用就是将SQL语句存放在这个区内，当客户发出一个新的SQL语句，数据库引擎首先会到这个区查找是否有相同的SQL，如果有，则避免了解析、分析索引、制定执行计划等一系列的动作，所以在开发的过程中要尽量使用共享的SQL语句。,共享SQL语句,字符要相同：两条SQL语句的大小写要完全一致意义要相同：两个用户访问的对象名称一样，但是如果一个访问的是自己的视图，另一个访问的是一个公有同义词，则无法复用。变量要相同：,共享SQL语句,selectclientnamefromclent_info与selectCLIENTNAMEfromclient_info无法复用,select.fromclientwhereclitno=:v_client_no与select.fromclientwhereclitno=:p_client_no无法复用,尽量使用Oracle已经提供的函数和方法，避免不必要的重复性开发：使用DECODE函数使用DECODE函数代替复杂的if.else判断使用MERGE关键字,使用内部函数提高SQL效率,和！=和!=都代表不等于的概念，在SQL开发的过程中尽量不要使用和！=，这个关键字会造成索引失效，使查询效率降低。使用表sta_client_info（客户信息表）进行测试，其中在clitno（客户编号）列上建立索引。,避免使用和！=,使用=查询条件的执行计划：,避免使用和！=,SQLconncdbeff/cdbeffkmdevConnected.SQLsetautotracetraceonlySQLselect*fromsta_client_infowhereclitno=1000;norowsselectedExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=1Bytes=89)10TABLEACCESS(BYINDEXROWID)OFSTA_CLIENT_INFO(Cost=2Card=1Bytes=89)21INDEX(RANGESCAN)OFIDX_CL_CLITNO(NON-UNIQUE)(Cost=1Card=1),使用或者！=查询条件的查询计划：,避免使用和！=,SQLselect*fromsta_client_infowhereclitno1000;17485rowsselected.ExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE(Cost=31Card=17484Bytes=1556076)10TABLEACCESS(FULL)OFSTA_CLIENT_INFO(Cost=31Card=17484Bytes=1556076),使用and关键字代替和！=：,避免使用和！=,SQLselect*fromsta_client_infowhereclitno1000andclitnoselect*fromsta_client_infowhereclitname=Jhon2;norowsselectedExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=1Bytes=89)10TABLEACCESS(BYINDEXROWID)OFSTA_CLIENT_INFO(Cost=2Card=1Bytes=89)21INDEX(RANGESCAN)OFIDX_CLIENT_NAME(NON-UNIQUE)(Cost=1Card=1),使用ISNULL的方式查询：,避免使用ISNULL和ISNOTNULL关键字,SQLselect*fromsta_client_infowhereclitnameisnull;ExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE(Cost=31Card=1Bytes=89)10TABLEACCESS(FULL)OFSTA_CLIENT_INFO(Cost=31Card=1Bytes=89),UNION关键字：UNION是两个集合的并，在集合上相当于AUB，过滤重复数据。UNIONALL关键字：UNIONALL是两个集合的加，在集合运算上是A+B，不过滤重复数据。在实际开发过程中要尽量使用UNIONALL代替UNION。,UNION和UNIONALL关键字,IN关键字：IN是对结果值进行比较，判断一个字段是否存在于几个值的范围中。EXISTS关键字：EXISTS检查是否有结果，判断是否有记录，返回的是一个布尔型（TRUE/FALSE）。,EXISTS关键字与IN关键字,IN关键字：EXISTS关键字：,下面两条SQL语句是等价的,SELECTproj_no,proj_nameFROMCDBPJ_PROJ;,Select*fromsta_client_infoc-sta_client_info为客户表wherec.clitnoin(selectclitnofromsta_cont_info-sta_cont_info为合同表)；,Select*fromsta_client_infoclwhereexists(selectxfromsta_cont_infocontwherecl.clitno=cont.clitno)；,IN关键字：上面的查询等价于：,EXISTS关键字与IN关键字,Select*fromsta_client_infoc-sta_client_info为客户表wherec.clitnoin(selectclitnofromsta_cont_info-sta_cont_info为合同表)；,Selectc.*fromsta_client_infocl,(selectclitnofromsta_cont_info)contwherecl.clitno=cont.clitno,EXISTS关键字：上面的查询等价于：,EXISTS关键字与IN关键字,Select*fromsta_client_infoclwhereexists(selectxfromsta_cont_infocontwherecl.clitno=cont.clitno)；,forvin(select*fromsta_client_info)loopifexists(selectxfromsta_cont_infoctwherect.clitno=v.clitno)thenoutputtherecord;endif;endloop;,IN关键字：从上面的结果可以看出来IN关键字实际上是将查询结果当作一张表，然后两张表连接查询出结果。EXISTS关键字：从上面等价结果上可以看出来，EXISTS关键字总是对外面的表进行全扫描。,EXISTS关键字与IN关键字,从而：当子查询的查询结果比较小的时候使用IN关键字是比较合理的。当外部表比较小的时候（sta_client_info），使用EXISTS比较合理（因为小表的数据都被cache到缓存中），同时内部表要有索引。当内外两张表都很大的时候，查询效率则与索引和其他因素有关，不能一定说谁效率更高。,EXISTS关键字与IN关键字,TRUNCATE关键字：通过释放存储表数据所用的数据块来删除数据，并且只在事务日志中记录块的释放。DELETE关键字：DELETE语句每次删除一行，并在事务日志中为所删除的每行做记录，并使用回滚段。结论：在整表数据删除的时候，使用TRUNCATE的效率要远远高于DELETE。,TRUNCATE关键字和DELETE关键字,TRUNCATE的缺点：首先TRUNCATE不占用回滚段，即一旦执行，无法UNDO；其次DELETE删除数据，日志中记录的是一条一条删除语句，而TRUNCATE的操作在日志中不产生记录，无法进行基于此的数据恢复。,TRUNCATE关键字和DELETE关键字,尽量多的使用COMMIT（保持数据完整性下）在程序的编码中尽量避免使用*避免隐性转换索引列上=代替避免索引列上的计算选择有效的表名顺序WHERE子句的连接顺序使用WHERE代替HAVING减少数据库的访问次数,第二部分,COMMIT所释放的资源：回滚段上用于恢复数据的信息。被程序语句获得的锁。redologbuffer中的空间。,尽量多的使用COMMIT,COMMIT动作：当界面出现COMMIT动作时，后台数据未必已经从databuffer进入到数据文件，而是确保了sql动作记录在了logfiles中。当进行大数据量的操作（几万条到几十万条）的时候，不要将数据在内存中全部处理完毕再插入到数据文件中，而是在确保数据完整性的基础上分批处理（几千条数据作为一组）。但是确保数据的完整性。在使用LOOP循环时不要在把COMMIT写在LOOP里面造成每条数据处理完毕都要COMMIT。,尽量多的使用COMMIT,*在数据库中的解析：在开发中，尤其是在批量数据导入导出，经常用到*，Oracle在解析的过程中，会将*解析成每个列，这个工作是要重新从数据字典中查询获得，这就意味着解析过程将消耗过多的时间，这个时间节省并不明显，但是当程序中包含大量的*时，效应就想当明显了。,在程序的编码中尽量避免使用*,隐性转换：这是最常犯的错误之一，其查询是不影响结果的，却影响效率，在查询的过程中由于数据类型的不符，造成隐性地调用to_char或者to_number函数，例如表CONT_INFO：,避免隐性转换,查询合同名称为1000的合同信息，没有隐性转换的查询：,避免隐性转换,SQLselect*fromcont_infowherecont_name=1000;norowsselectedExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(BYINDEXROWID)OFCONT_INFO21INDEX(RANGESCAN)OFIDX_CONT_NAME(NON-UNIQUE),查询合同名称为1000的合同信息，存在隐性转换的查询：,SQLselect*fromcont_infowherecont_name=1000;norowsselectedExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OFCONT_INFO,避免隐性转换,结论：第二个查询条件使用了：而cont_name字段的数据类型为字符串（varchar2(50)），则在解析过程中后台做了隐性转换，造成索引失效：,避免隐性转换,cont_name=1000,cont_name=to_char(1000),低效：select*fromempwheredeptno3高效：select*fromempwheredeptno=4两者的区别在于，前者dbms将直接跳到第一个deptno等于4的记录，而后者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的。,索引列上=代替,在索引上使用计算的查询：,避免索引列上的计算,SQLselect*fromempwheresal*12select*fromempwheresalselectcount(*)fromcont_info1,client_info;COUNT(*)-31488480Executedin11.219seconds,SQLselectcount(*)fromclient_info,cont_info;COUNT(*)-31488480Executedin48.407seconds,WHERE子句：ORACLE采用自下而上的WHERE语句，表之间的连接应该写在其他WHERE条件之前，能过滤掉大多数据的条件应该写在WHERE条件之后。,WHERE子句的连接顺序,对比两条SQL语句：,WHERE子句中的连接顺序,SQLselectcount(*)fromcont_info1cont,client_infocl2wherecl.lastmoditimeselectcount(*)fromcont_info1cont,client_infocl2wherecl.clittype=新客户and3cl.lastmoditimeselectcount(clitname),clittypefromclient_info2groupbyclittypehavingclittype=老客户;ExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE10FILTER21SORT(GROUPBY)32TABLEACCESS(FULL)OFCLIENT_INFO,对比用WHER子句：,使用WHERE子句来代替HAVING子句,SQLselectcount(clitname),clittypefromclient_info2whereclittype=老客户3groupbyclittype;ExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE10SORT(GROUPBY)21TABLEACCESS(FULL)OFCLIENT_INFO,SQL解析过程：每条SQL语句在数据库中都要进行解析（如果两条SQL语句不一样的话），估算索引，绑定变量、制定执行计划、核对权限、查找对象所在数据块、将数据块CACHE到内存中尽量发挥每条SQL语句的最大能量，避免重复地从数据库中获取数据。,减少对数据库的访问次数,例如客户表CLIENT_INFO（clitno为主键）：,减少对数据库的访问次数,低效的会话：-第一条SQL语句selectclitname,clittypefromclient_infowhereclitno=005237-第二条SQL语句selectclitname,clittypefromclient_infowhereclitno=005231,高效的会话：selectcl1.clitname,cl2.clitnamefromclient_infocl1,client_infocl2wherecl1.clitno=005237andcl1.clitno=005231,尽量将SQL语句封装在PROCEDURE中使用表连接代替EXISTS使用函数索引使用APPEND关键字和NOLOGGING关键字批量绑定表的连接方式,第三部分,将SQL语句封装在PROCEDURE中：在使用SQL语句时，首先会在对其进行语义分析、分析索引、制定执行计划而使用PROCEDURE则略去了语义分析等大部分重复性的工作，更重要的是SQL语句需要客户端与服务器端交互，而PROCEDURE是运行在服务器端，最后只将结果回显给客户端。,尽量将SQL语句封装在PROCEDURE中,使用EXISTS关键字：,使用表连接来代替EXISTS,SQLselectcount(cl.clitno)fromclient_infocl2whereexists3(4selectxfromcont_info1contwherecont.clitno=cl.clitno5)6;COUNT(CL.CLITNO)-123360Executedin2.109seconds,是用表连接：,使用表连接来代替EXISTS,SQLselectcount(cl.clitno)fromclient_infocl,2cont_info1cont3wherecl.clitno=cont.clitno4;COUNT(CL.CLITNO)-484448Executedin0.5seconds,最常见于对日期的查询：查询语句的时候，利用索引可以避免全表搜索，加快查询速度，但是如果where子句是这样写的话：whereto_char(birth_day,YYYY-MM-DD)=1978-11-10，而建立的索引只是针对birth_day本身，那么这个索引对于查询是没有任何效果的，因为查询是在函数运算过后再开始比较的。,使用函数索引,使用函数索引：,使用函数索引,SQLcreateindexidx_lastmoditimeonsta_client_info(trunc(lastmoditime,dd);Indexcreated.SQLselect*fromsta_client_infowheretrunc(lastmoditime,dd)=trunc(sysdate,dd);norowsselectedExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=175Bytes=15575)10TABLEACCESS(BYINDEXROWID)OFSTA_CLIENT_INFO(Cost=2Card=175Bytes=15575)21INDEX(RANGESCAN)OFIDX_LASTMODITIME(NON-UNIQUE)(Cost=1Card=70),APPEND提示：每个数据块都有一个高水准线，代表着该数据块数据量曾经达到的最高位置，在使用APPEND提示以后，新的数据会直接插入到高水准线以上的空间，而不用到高水准线下去查找剩余空间，可以极大地提高数据的插入速度。但是同样带来了浪费空间、产生大量碎片的负面影响。,批量数据插入的时候使用APPEND提示,使用APPEND提示：,批量数据插入的时候使用APPEND提示,SQLinsertintotesting2select*fromclient_info;279760rowsinsertedExecutedin6.188seconds,SQLinsert/*+Append*/intotesting12select*fromclient_info;279760rowsinsertedExecutedin1.656seconds,NOLOGGING关键字：在Oracle中有很多语法都NOLOGGING这个关键字，代表的时执行这条SQL语句的时候不记录日志，这样就减少了REDOBUFFER与REDOLOGFILES的交互，实现了速度的提升，但是由于不记录日志，在恢复的时候就无法实现基于时间点的恢复。,APPEND配合NOLOGGING,APPEND配合NOLOGGING,APPEND配合NOLOGGING,SQLinsert/*+Append*/intotesting12select*fromclient_infonologging3;279760rowsinsertedExecutedin11.734seconds,SQLinsert/*+Append*/intotesting2select*fromclient_info3;279760rowsinsertedExecutedin22.11seconds,批量绑定原理：有些情况下可以使用变量批量绑定的方法来实现代码的优化，批量绑定变量的原理就是使用集合变量，将变量批量赋值，在赋值以后将数据批量插入到目的表格中。,批量绑定,没有使用批量绑定的运行测试结果：,批量绑定,-未使用批量绑定begindbms_output.put_line(begin:|to_char(sysdate,yyyyhh24:mi:ss);forvin(select*fromemp)loopinsertintodemovalues(v.ename);endloop;dbms_output.put_line(end:|to_char(sysdate,yyyyhh24:mi:ss);end;begin:200619:12:30end:200619:13:48-十次平均值插入表格时间为18.21秒,使用批量绑定的运行测试结果：,批量绑定,/*使用批量绑定*/declaretypenewtypeistableofdemo.ename%typeINDEXBYBINARY_INTEGER;shapenewtype;inumber;jnumber;begindbms_output.put_line(beginforall:|to_char(sysdate,yyyyhh24:mi:ss);forvin(selectrownum,enamefromemp)loopshape(v.rownum):=v.ename;endloop;selectcount(*)intoifromemp;foralljin1.iinsertintodemovalues(shape(j);commit;dbms_output.put_line(endforall|to_char(sysdate,yyyyhh24:mi:ss);end;-经过计算插入表格平均时间约为6秒,从而：在主机环境、数据库参数一样的情况下，使用批量绑定的编程方式效率要远高于非批量绑定的使用。,批量绑定,NESTLOOPS：嵌套循环连接*SORTMERGE：排序合并连接*HASHJOIN：哈希连接*CLUSTERJOIN：集群连接CARTESIAN：笛卡尔连接IDEXJOIN：索引连接,表的连接方式,驱动表Oracle选择一张表作为连接的驱动表，即第一个记录源通常称为外部表（OuterTable），或者驱动表。内部表相对于驱动表来说，由驱动表进行驱动连接的表或者数据源被成为内部表（InnerTable）。,表的连接方式名词解释,NESTLOOPS：嵌套循环连接扫描一张表，每读到一条记录，就根据索引去另一张表里查找。在关联查询的过程中，Oracle会持续提取驱动表中其他符合条件的记录与内部表关联查询。这两个过程是并行进行的，因此嵌套循环连接返回前几条记录的速度是非常快的。,表的连接方式,NESTLOOPS的连接过程：一个是外部循环，提取驱动表中符合条件的每条记录。另外一个是内部循环，根据外循环中提取的每条记录对内部表进行连接查询相应的记录。由于这两个循环是嵌套进行的，故此种连接方法称为嵌套循环连接,表的连接方式,NESTEDLOOP,NESTLOOPS：,表的连接方式,SQLselect/*+use_nl(s,c)*/s.clitno,c.contnofromclient_infos,cont_infocwheres.clitno=c.clitnoandc.=1000;norowsselectedExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE(Cost=3Card=1Bytes=3638)10NESTEDLOOPS(Cost=3Card=1Bytes=3638)21TABLEACCESS(BYINDEXROWID)OFCONT_INFO(Cost=2Card=1Bytes=3265)32INDEX(UNIQUESCAN)OFSYS_C0038012(UNIQUE)(Cost=1Card=100)41TABLEACCESS(BYINDEXROWID)OFCLIENT_INFO(Cost=1Card=1Bytes=373)54INDEX(UNIQUESCAN)OFSYS_C0038011(UNIQUE),NESTLOOPS：优化器将作为CONT_INFO驱动表，根据唯一性索引SYS_C0038012返回符合contno=1000的数据，然后再与被驱动的表CLIENT_INFO关联查询出相应的clitno以及Contno，表CLIENT_INFO上也有唯一性索引SYS_C0038011，故能很多快地查询出对应的结果。,表的连接方式,SORTMERGE：排序合并连接在排序合并连接中是没有驱动表的概念的，两个互相连接的表按连接列的值先排序，排序完后形成的结果集再互相进行合并连接提取符合条件的记录。相比嵌套循环连接，排序合并连接比较适用于返回大数据量的结果。,表的连接方式,SORTMERGE：,表的连接方式,SQLselect/*+use_merge(s,c)*/*fromclient_infos,cont_infocwheres.clitno=c.clitno;30278rowsselected.ExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE(Cost=1959Card=70980Bytes=258225240)10MERGEJOIN(Cost=1959Card=70980Bytes=258225240)21TABLEACCESS(BYINDEXROWID)OFCONT_INFO(Cost=826Card=70980Bytes=231749700)32INDEX(FULLSCAN)OFIDX_CONT_CLITNO(NON-UNIQUE)(Cost=26Card=70980)41SORT(JOIN)(Cost=1133Card=20420Bytes=7616660)54TABLEACCESS(FULL)OFCLIENT_INFO(Cost=26Card=20420Bytes=7616660),SORTMERGE：对连接的每个表做tableaccessfull；对tableaccessfull的结果进行排序；进行mergejoin对排序结果进行合并。可以看出来SORTMERGE的主要性能消耗是在内存上。,表的连接方式,HASHJOIN：哈希连接是CBO做大数据集连接时的常用方式。优化器扫描小表（或数据源）做为驱动表，利用连接键（也就是根据连接字段计算hash值）在内存中建立hash表，然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。,表的连接方式,HASHJOIN：HASHJOIN是Oracle现在比较推荐的方法，其原理实际上就是两个集合中的元