企业应用开发中OracleSQL优化.docVIP

企业应用开发中Oracle的SQL优化 　　摘 要：企业应用开发中的性能优化是非常重要的一环，其中SQL优化又是重中之重。重点对企业应用开发中SQL优化的几种类型进行了分析，并列举了相关优化实例，以期为企业开发人员提供帮助。 　　关键词：企业开发;Oracle;SQL优化 　　DOI：10.11907/rjdk.143523 　　中图分类号：TP302 　　文献标识码：A 文章编号：1672-7800（2014）012-0023-02 　　作者简介：陈冠星（1984-），男，广东揭阳人，硕士，海南软件职业技术学院网络工程系助教，研究方向为数据库、网络技术与物联网。 　　0 引言 　　 在企业应用开发中，许多初级程序员在编写程序时往往只注重程序逻辑的正确性，却忽略了程序性能的优化，特别是在系统开发初始阶段，由于数据量小，更加体现不出性能的优劣。然而一旦进入性能测试阶段，各种问题都会凸显出来，在性能调优过程中，SQL优化是重中之重。特别是在金融行业应用中，业务数据量呈指数级增长，因此SQL脚本的调优尤为重要。通过对易出现问题之处进行分析，从而提高SQL语句的执行效率，提高企业开发效率。 　　1 SQL优化分类 　　 SQL语句优化可以分几种不同的情况： 　　 （1）针对特别关键字的优化。比如union改为union all的优化，in改为exists和not in改为not exists的优化。 　　 （2）容易被忽略的优化。比如where子句左边不使用函数以及避免自动类型转换。 　　 （3）减少结果集的优化。比如增加驱动表的过滤条件，去掉不必要的连接表，减少中间结果集，避免CARTESIAN连接和减少外连接的优化。 　　 （4）数据库表的优化。比如delete操作和外键索引的优化，该优化其实可以通过将数据库中所有外键表中未建索引的列进行整理，为需要创建索引的列创建索引。 　　 （5）应用程序中代码的优化。比如合理使用bind变量、with as的使用及谨慎使用hint。 　　 （6）业务逻辑的优化。比如慎用逐单处理模式。 　　2 SQL优化实例 　　2.1 Union amp; Union All 　　 Union会把结果集中相同的记录合并成一条记录，相当于做了一次distinct，而union all则不会对结果集进行处理。在复杂的查询中，如果子查询包含了多个不同的子集，使用两者的输出结果虽然相同，但是union会多出排序和去除重复记录的操作，因此在SQL子句中通常要使用union all连接不同数据集。 　　2.2 in/exists和not in/not exists 　　 通常的观点总是认为使用exists（或not exists）将提高查询效率，一般推荐使用exists代替inlt;supgt;[1]lt;/supgt;。其实并非所有的exists（或not exists）都会比in（或not in）执行速度快，这要根据执行计划及数据分布决定哪种更好。 　　 Oracle的优化器分为CBO和RBO，鉴于Oracle从10G开始就已经放弃了RBO，所以这里只讨论CBO模式下的对比：对于in和exists，两者效率一致;对于not in和not exists，not in的效率较低。 　　2.3 where子句左边不使用函数 　　 在编写SQL时很容易忽略这一点，比如在判断当前时间距离记录插入时间insert_time是否超出5分钟，比较直观的写法是： 　　 Select * from table_a a where 24 * 60 * （sysdate - a.insert_time） lt; 5; 　　 insert_time列上已创建索引，但是在索引列上使用函数会造成索引不被使用lt;supgt;[2]lt;/supgt;。因此该SQL可优化为： 　　 select * from table_a a where a.insert_time gt; sysdate - 5/（24*60）; 　　2.4 避免自动类型转换 　　 在SQL编写过程中，有的脚本虽然看起来简单，但是执行时并不会使用看起来应该被使用的索引。自动类型转换就是其中的陷阱之一，会导致左边的数据类型被自动转换成右边的数据类型，从而使得左值上的索引失效，降低查询效率。 　　 比如表table_a中type的数据类型为char（1）， type为索引列。 　　 select * from table_a where type=2; 　　 系统会自动将select * from table_a where to_number（type）=2 　　2.5 增加驱动表过滤条件 　　 驱动