关于SQL文优化问题总结.doc

关于SQL文优化问题总结【摘 要】实际系统中遇到性能问题是非常常见的，性能优化有许多方面，其中包括硬件方面，软件方面，包括服务器端，客户端等等。本文重点基于ORACLE分析了影响SQL文性能的原因，然后列举了几点性能优化的对策，希望给开发人员在编码时提供帮助，给SQL的性能问题调查者提供一个方向。 【关键词】ORACLE；性能问题；SQL文优化 一、问题提出 之前有个项目，其中的批处理定期调用一个存储过程，在测试环境中运行没有问题，但是正式运行出现了错误，执行存储过程时出现了错误：提示是表空间不足。为了解决这个问题，笔者对SQL文的性能优化进行了学习和研究。 二、问题调查与解决 由于该存储过程内容比较多，大概有3000多行，也不能判断那部分出了问题，首先在可能出现问题的地方追加了LOG信息。由于测试环境中该问题不能再现，所以代码更新到了实际环境中进行运行，通过LOG发现是在执行某个SQL文时出的错误，这个SQL文涉及到了10多个表，而其中表中的数据量比较大。执行时用到的临时表空间高达40G，后来通过调查对SQL的进行了调整，只是修改了WHERE条件中其中两个条件的顺序，这个问题就解决了。 三、SQL文性能原因分析 （1）在大记录集上进行高成本操作，如使用了引起排序的谓词等。（2）过多的I/O操作（含物理I/O与逻辑I/O），最典型的就是未建立恰当的索引，导致对查询表进行全表扫描。减少访问数据库的次数，就能实际上减少ORACLE的工作量。（3）处理了太多的无用记录，如在多表连接时过滤条件位置不当导致中间结果集包含了太多的无用记录。（4）未充分利用数据库提供的功能，如查询的并行化处理等。 四、SQL文性能优化总结 （1）建立恰当的索引。对经常进行排序和连接操作的字段建立索引。（2）避免使用”*”，SQL文中引用”*”，使用起来的确非常方便，但是效率非常低，主要是ORACLE在解析的过程中，会将”*”一次转化成所有的列名，这个工作是通过查询数据字典完成的。这就意味着消耗更多的时间。（3）尽量避免多表关联。（4）避免使用消耗资源的操作，带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL语句会启动SQL引擎执行消耗资源的排序功能。DISTINCT需要一次排序操作，其他的至少需要执行二次排序。通常带有执行UNION，MINUS，INTERSECT的SQL语句都可以通过其他方式回避。例如：SELECT DISTINCT A.NO，A.NAME FROM A，B WHERE A.NO=B.NO可以替换为效率更高的EXISTS来实现，SELECT A.NO，A.NAME FROM A WHERE EXISTS（SELECT 1 FROM B WHRE B.NO=A.NO）。（5）避免在索引列上使用函数。例如： SELECT NO FROM A WHERE A.SCORE * 2180可以修改为SELECT NO FROM A WHERE A.SCORE180/2。（6）避免在索引列上使用NOT。NOT会产生和在索引列上使用函数相同的影响。当ORACLE遇到NOT时，他就会停止使用索引转而执行全表扫描。（7）避免在索引列上使用IS NULL，IS NOT NULL。（8）减少对表的查询。在含有自查询的语句中，要特别注意减少对表的查询。（9）注意WHERE字句的连接顺序。ORACLE原则上采用自下而上的顺序解析WHERE子句，根据据这个原理，当在WHERE子句中有多个表联接时，WHERE子句中排在最后的表应当是返回行数可能最少的表，有过滤条件的子句应放在WHERE子句的最后。（10）使用表的别名（Alias）：当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上.这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。（11）用EXISTS替代IN、用NOT EXISTS替代NOT IN。在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接，在这种情况下，使用EXISTS（或NOT EXISTS）通常将提高查询的效率。在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的（因为它对子查询中的表执行了一个全表遍历）。为了避免使用NOT IN，我们可以把它改写成外连接（Outer Joins）或NOT EXISTS。（12）sql语句用大写的。因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行。（13）用=替代。高效：SELECT*FROM EMP WHERE DEPTNO=4；低效：SELECT*FROM EMP WHERE DEPTNO3。两者的区别在于，前者DBMS将直接跳到第一个DEP