《高效的SQL语句》由会员分享，可在线阅读，更多相关《高效的SQL语句（79页珍藏版）》请在金锄头文库上搜索。
1、高效的SQL语句飞狼影响数据库性能的三个指标要确定影响性能瓶颈： CPU 内存 I/OSQL优化的作用 SQL语句是用户操作数据库的唯一途径。 SQL优化是代码级的优化。 SQL语句有时候能够影响数据库性能的70-80%。 SQL语句的不同写法，性能上差距非常大。 SQL语句简单，但是精通难。 SQL语句的处理流程 共享SQL语句 使用内部函数提高SQL效率 避免使用和！= 避免使用ISNULL和ISNOTNULL UNION和UNIONALL EXISTS关键字与IN关键字 TRUNCATE和DELETE第一部分 打开游标|-查看共享SQL区，是否有相同的SQL语句|分析|定义-|-绑定变量
2、|捆绑-|并行处理|执行查询|返回结果|关闭游标SQL语句的处理流程分析： 到SHARED_POOL中查找是否有相同的SQL语句 分析语法 语义分析 视图转换 表达式转换 选择优化器 选择连接方式 选择连接顺序 选择数据搜索路径SQL语句的处理流程 执行：主要在于使用UPDATE和DELETE语句时，必须将行锁定，以免其他用户修改。Oracle先从数据库缓冲区中寻找是否存在所要的数据块，如果存在，就直接读或修改，否则从物理文件中读到数据库缓冲区中。SQL语句的处理流程 返回结果：对SELECT语句需要返回结果的语句，首先看是否需要排序，需要，则排序后返回给用户，然后根据内存的大小不同，可以一次
3、取出一行数据，一可以一次取一组数据。这时，可能要用到数据结构中的外部排序，并归排序等算法，所以如内存允许的话，尽量大会提高性能的。SQL语句的处理流程 共享SQL：Oracle内存中有一个区叫SHARED_POOL，这个区的主要作用就是将SQL语句存放在这个区内，当客户发出一个新的SQL语句，数据库引擎首先会到这个区查找是否有相同的SQL，如果有，则避免了解析、分析索引、制定执行计划等一系列的动作，所以在开发的过程中要尽量使用共享的SQL语句。共享SQL语句 字符要相同：两条SQL语句的大小写要完全一致 意义要相同：两个用户访问的对象名称一样，但是如果一个访问的是自己的视图，另一个访问的是一个
4、公有同义词，则无法复用。 变量要相同：共享SQL语句selectclientnamefromclent_info与select CLIENTNAMEfromclient_info无法复用select.fromclientwhereclitno=:v_client_no与select.fromclientwhereclitno=:p_client_no无法复用尽量使用Oracle已经提供的函数和方法，避免不必要的重复性开发： 使用DECODE函数使用DECODE函数代替复杂的if.else判断 使用MERGE关键字使用内部函数提高SQL效率 和！=和!=都代表不等于的概念，在SQL开发的过程中尽
5、量不要使用和！=，这个关键字会造成索引失效，使查询效率降低。 使用表sta_client_info（客户信息表）进行测试，其中在clitno（客户编号）列上建立索引。避免使用和！= 使用=查询条件的执行计划：避免使用和！=SQLconncdbeff/cdbeffkmdevConnected.SQLsetautotracetraceonlySQLselect*fromsta_client_infowhereclitno=1000;norowsselectedExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=1Bytes=89)10
6、TABLEACCESS(BYINDEXROWID)OFSTA_CLIENT_INFO(Cost=2Card=1Bytes=89)21INDEX (RANGE SCAN)OFIDX_CL_CLITNO(NON-UNIQUE)(Cost=1Card=1) 使用或者！=查询条件的查询计划：避免使用和！=SQLselect*fromsta_client_infowhereclitno1000;17485rowsselected.ExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE(Cost=31Card=17484Bytes=1556076)10TABLE AC
7、CESS (FULL)OFSTA_CLIENT_INFO(Cost=31Card=17484Bytes=1556076) 使用and关键字代替和！=：避免使用和！=SQLselect*fromsta_client_infowhereclitno1000andclitno1000;norowsselectedExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=1Bytes=89)10FILTER21TABLEACCESS(BYINDEXROWID)OFSTA_CLIENT_INFO(Cost=2Card=1Bytes=89)32IN
8、DEX (RANGE SCAN)OFIDX_CL_CLITNO(NON-UNIQUE)(Cost=1Card=1) 从测试结果可以得出：与！=关键字会在SQL查询过程中造成索引失效，在此时尽量使用and关键字来等价转换其相应的查询条件。避免使用和！= NULL和ISNOTNULL关键字：NULL在数据库中代表的是“无”，就是什么都没有。NULL值不存储在索引中，因此在索引列上带ISNULL条件的查询不会使用索引，而是使用TableAccessFull操作解析查询语句，ISNOTNULL同理。避免使用IS NULL 和IS NOT NULL关键字 使用=的方式查询：避免使用IS NULL 和IS
9、 NOT NULL关键字SQLselect*fromsta_client_infowhereclitname=Jhon2;norowsselectedExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=1Bytes=89)10TABLEACCESS(BYINDEXROWID)OFSTA_CLIENT_INFO(Cost=2Card=1Bytes=89)21INDEX (RANGE SCAN)OFIDX_CLIENT_NAME(NON-UNIQUE)(Cost=1Card=1) 使用ISNULL的方式查询：避免使用IS NULL 和
10、IS NOT NULL关键字SQLselect*fromsta_client_infowhereclitnameisnull;ExecutionPlan-0SELECTSTATEMENTOptimizer=CHOOSE(Cost=31Card=1Bytes=89)10TABLE ACCESS (FULL)OFSTA_CLIENT_INFO(Cost=31Card=1Bytes=89) UNION关键字：UNION是两个集合的并，在集合上相当于AUB，过滤重复数据。 UNIONALL关键字：UNIONALL是两个集合的加，在集合运算上是A+B，不过滤重复数据。 在实际开发过程中要尽量使用UNIO
11、NALL代替UNION。UNION和UNION ALL关键字 IN关键字：IN是对结果值进行比较，判断一个字段是否存在于几个值的范围中。 EXISTS关键字：EXISTS检查是否有结果，判断是否有记录，返回的是一个布尔型（TRUE/FALSE）。EXISTS关键字与IN关键字 IN关键字： EXISTS关键字：下面两条SQL语句是等价的SELECT proj_no,proj_name FROM CDBPJ_PROJ ;Select *fromsta_client_infoc-sta_client_info为客户表wherec.clitnoin(selectclitnofromsta_cont_
12、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,(se
13、lectclitnofromsta_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)thenoutputth
14、erecord;endif;endloop; IN关键字：从上面的结果可以看出来IN关键字实际上是将查询结果当作一张表，然后两张表连接查询出结果。 EXISTS关键字：从上面等价结果上可以看出来，EXISTS关键字总是对外面的表进行全扫描。EXISTS关键字与IN关键字从而： 当子查询的查询结果比较小的时候使用IN关键字是比较合理的。 当外部表比较小的时候（sta_client_info），使用EXISTS比较合理（因为小表的数据都被cache到缓存中），同时内部表要有索引。 当内外两张表都很大的时候，查询效率则与索引和其他因素有关，不能一定说谁效率更高。EXISTS关键字与IN关键字 TRU
15、NCATE关键字：通过释放存储表数据所用的数据块来删除数据，并且只在事务日志中记录块的释放。 DELETE关键字：DELETE语句每次删除一行，并在事务日志中为所删除的每行做记录，并使用回滚段。 结论：在整表数据删除的时候，使用TRUNCATE的效率要远远高于DELETE。TRUNCATE关键字和DELETE关键字 TRUNCATE的缺点：首先TRUNCATE不占用回滚段，即一旦执行，无法UNDO；其次DELETE删除数据，日志中记录的是一条一条删除语句，而TRUNCATE的操作在日志中不产生记录，无法进行基于此的数据恢复。TRUNCATE关键字和DELETE关键字 尽量多的使用COMMIT（
16、保持数据完整性下） 在程序的编码中尽量避免使用* 避免隐性转换 索引列上=代替 避免索引列上的计算 选择有效的表名顺序 WHERE子句的连接顺序 使用WHERE代替HAVING 减少数据库的访问次数第二部分COMMIT所释放的资源： 回滚段上用于恢复数据的信息。 被程序语句获得的锁。 redologbuffer中的空间。尽量多的使用COMMITCOMMIT动作： 当界面出现COMMIT动作时，后台数据未必已经从databuffer进入到数据文件，而是确保了sql动作记录在了logfiles中。 当进行大数据量的操作（几万条到几十万条）的时候，不要将数据在内存中全部处理完毕再插入到数据文件中，而是在确保数据完整性的基础上分批处理（几千条数据作为一组）。但是确保数据的完整性。 在使用LOOP循环时不要在把COMMIT写在LOOP里面造成每条数据处理完毕都要COMMIT。尽量多的使用COMMIT *在数据库中的解析：在开发中，尤其是在批量数据导入导出，经常用到*，Oracle在解析的过程中，会将*解析成每个列，这个工作是要重新从数据字典中查询获得，这就意味着解析过程将消耗过多的时间，这个时间
