基于Oracle的SQL_查询优化研究

Oracle数据库原理与应用基于Oracle的SQL查询优化研究学院计算机工程学院专业计算机科学与技术___年级班别10级本科二班学号2010404010207学生姓名杨开发教师姓名李祥琴2013年12月5日JINGCHUUNIVERSITYOFTECHNOLOGY摘要.............................................................................................................................................................1关键词：ORACLE，SQL，优化.............................................................................................................11引言............................................................................................................................................................22影响SQL性能的原因...............................................................................................................................23SQL优化的一般步骤................................................................................................................................24SQL语句的优化.......................................................................................................................................34.1优化排序操作..................................................................................................................................34.1.1排序过程分析........................................................................................................................34.1.2SQL中引起排序的操作......................................................................................................44.1.3如何避免排序.......................................................................................................................44.2优化I/O...........................................................................................................................................54.2.1应用基于函数的索引...........................................................................................................54.2.2应用物化视图和查询重写...................................................................................................64.2.3将频繁访问的小表读入CACHE........................................................................................74.3多表连接优化..................................................................................................................................84.3.1消除外部连接.......................................................................................................................84.3.2谓词前推，优化中间结果...................................................................................................85结束语......................................................................................................................................................116致谢.................................................................................................................................................12参考文献.......................................................................................................................................................131摘要：数据库性能问题一直是决策者及技术人员共同关注的焦点，影响数据库性能的一个重要因素就是SQL查询语句的低效率。论文首先分析了导致SQL查询语句性能低下的四个常见原因以及SQL调优的一般步骤，然后分别针对如何降低I/O操作、在查询语句中如何避免对查询结果的高成本操作以及在多表连接时如何提高查询效率进行了分析。关键词：ORACLE，SQL，优化21引言随着网络应用不断发展，系统性能也越来越引起决策者的重视。影响系统性能的因素很多，低效的SQL语句就是其中一个不可忽视的重要原因。论文首先分析导致SQL性能低下的常见原因，然后分析SQL优化应遵循的一般步骤，最后从如何降低I/O、避免对查询结果的高成本操作和多表连接中如何提高SQL性能进行了研究。鉴于目前ORACLE在数据库市场上的主导地位，论文将只针对ORACLE进行讨论。2影响SQL性能的原因影响SQL性能的因素很多，如初始化参数设置不合理、导入了不准确的系统及模式统计数据从而影响优化程序(CBO)的正确判断等，这些往往和DBA密切相关。纯粹从SQL语句出发，笔者认为影响SQL性能不外乎以下四个重要原因：(1)在大记录集上进行高成本操作，如使用了引起排序的谓词等。(2)过多的I/O操作(含物理I/O与逻辑I/O)，最典型的就是未建立恰当的索引，导致对查询表进行全表扫描。(3)处理了太多的无用记录，如在多表连接时过滤条件位置不当导致中间结果集包含了太多的无用记录。(4)未充分利用数据库提供的功能，如查询的并行化处理等。第(4)个原因处理起来相对简单。论文将针对前三个原因论述如何提高SQL查询语句的性能。3SQL优化的一般步骤SQL优化一般需经过发现问题、分析问题、提出解决措施、应用措施、测试性能几个步骤，如图1所示。“发现问题就是解决问题的一半”，因此在SQL调优过程中，定位问题SQL是非常重要的一步，一般可借助于ORACLE自带的性能优化工具如STATSPACK、TKPROF、AUTOTRACE等辅助用户进行，同时还应该重视动态性能视图如V$SQL、V$MYSTAT、V$SYSSTAT等的研究。3图1SQL优化的一般步骤4SQL语句的优化4.1优化排序操作排序的成本十分高昂，当在查询语句中使用了引起结果集排序的谓词时，SQL性能必然受到影响。4.1.1排序过程分析当待排序数据集不是太大时，服务器在内存(排序区)完成排序操作，如果排序需要更多的内存空间，服务器将进行如下处理：(1)将数据分成多个小的集合，对每一集合进行排序。(2)服务器向磁盘申请临时空间，将排好序的中间结果写入临时段，再对另外的集合进行排序。(3)在所有的集合均排好序后，服务器再将它们进行合并得到最终的结果，如果排序区尺寸太小，合并无法一次完成时，将分多次进行。YN设置系统性能目标定位问题SQL分析SQL提出性能优化措施实施措施测试性能达标?结束4从上述分析可知，排序是一种十分昂贵的操作，它消耗大量的CPU时间和内存，触发磁盘分页和交换操作，因此只要有可能，我们就应该在SQL语句中尽量避免排序操作。4.1.2SQL中引起排序的操作SQL查询语句中引起排序的操作大致有：ORDERBY和GROUPBY从句；DISTINCT修饰符；UNION、INTERSECT、MINUS集合操作符；多表连接时的排序合并连接(SORTMERGEJOIN)等。4.1.3如何避免排序1)建立恰当的索引对经常进行排序和连接操作的字段建立索引。在建立索引后，当服务器向这些字段发出排序请求时，将直接引用索引而不进行排序操作；当进行等值连接查询操作时，若建立连接的字段未建立索引，服务器进行的是排序合并连接(SORTMERGEJOIN)，连接操作的过程如下：对进行连接的两个或多个表分别进行全扫描；对每一个表中的行集分别进行全排序；合并排序结果。如果建立连接的字段已建立索引，服务器进行嵌套循环连接(NESTEDLOOPJOINS)，该连接方式不需要任何排序，其过程如下：对驱动表进行全表扫描；对返回的每一行利用连接字段值实施索引惟一扫描；利用从索引扫描中返回的ROWID值在从表中定位记录；合并主、从表中的匹配记录。因此，建立索引可避免多数排序操作。2)用UNIIONALL替换UNIONUNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。大部分应用中是不会产生重复记录的，最5常见的是过程表与历史表UNION。因此，采用UNIONALL操作符替代UNION，因为UNIONALL操作只是简单的将两个结果合并后就返回。4.2优化I/O过多的I/O操作会占用CPU时间、消耗大量内存和占用过多的栓锁，因此有必要对SQL的I/O进行优化。优化I/O的最有效方式就是用索引扫描代替全表扫描。4.2.1应用基于函数的索引基于函数的索引(FUNCTIONBASEDINDEX，简记为FBI)提供了索引计算列并在查询中使用这些索引的能力。FBI的实质是对查询所需中间结果进行预处理。如果一个FBI与查询语句中的内嵌函数完全匹配，CBO在生成查询计划时，将自动启用索引范