求SQL数据库论文
摘要数据库性能一直是决策者和技术人员关注的焦点，而影响数据库性能的一个重要因素就是SQL查询语句的低效。本文首先分析了导致SQL查询语句性能低下的四种常见原因和SQL调优的一般步骤，然后分析了如何减少I/O操作，如何避免查询语句中对查询结果的高成本操作，以及如何提高多个表连接时的查询效率。
关键词甲骨文；；SQL优化；连接
1简介
随着网络应用的不断发展，系统性能越来越受到决策者的关注。影响系统性能的因素有很多，低效的SQL语句是不可忽视的重要原因之一。本文首先分析了导致SQL性能低下的常见原因，然后分析了SQL调优应该遵循的一般步骤，最后研究了如何减少I/O、避免查询结果的高成本操作以及如何在多表连接中提高SQL性能。鉴于ORACLE目前在数据库市场的主导地位，本文将只讨论ORACLE。
影响SQL性能的2个因素
影响SQL性能的因素有很多，比如初始化参数不合理，系统和模式统计不准确，影响优化器(CBO)的正确判断等等。这些因素往往与DBA密切相关。从SQL语句的角度来看，作者认为有四个重要原因影响SQL的性能:
(1)对大型记录集执行高成本操作，比如使用导致排序的谓词。
(2)I/O操作过多(包括物理I/O和逻辑I/O)，最典型的就是没有建立合适的索引，导致查询表的全表扫描。
(3)处理了太多无用的记录。比如多个表连接时，过滤条件位置不当导致中间结果集中无用记录过多。
(4)数据库提供的功能没有得到充分利用，如查询的并行处理。
第四个原因处理起来相对简单。本文将针对前三个原因讨论如何提高SQL查询语句的性能。
3 SQL优化的一般步骤
SQL优化一般需要经过发现问题、分析问题、提出解决方案、应用措施、测试性能等几个步骤，如图1所示。“找到问题就是解决了一半”，所以定位问题SQL是SQL调优过程中非常重要的一步，一般可以借助ORACLE自带的性能优化工具如STATSPACK、TKPROF、AUTOTRACE等来进行。同时要注意V$SQL、V$MYSTAT、V$SYSSTAT等动态性能视图的研究。
图1 SQL优化的一般步骤
4 SQL语句的优化
4.1优化排序操作
排序的成本非常高，当导致结果集被排序的谓词被用在查询语句中时，SQL性能必然会受到影响。
4.1.1排序过程分析
当要排序的数据集不太大时，服务器在内存(排序区)中完成排序操作。如果排序需要更多的内存空间，服务器将执行以下操作:
(1)将数据分成几个小集合，并对每个集合进行排序。
(2)服务器从磁盘申请临时空间，将排序后的中间结果写入临时段，然后对其他集合进行排序。
(3)所有集合排序后，服务器会进行合并，得到最终结果。如果排序区域太小，无法一次合并，将分多次进行。
从上面的分析我们可以看出，排序是一个开销非常大的操作，会消耗大量的CPU时间和内存，并触发磁盘分页和交换操作，所以在SQL语句中要尽量避免排序操作。
4.1.2 SQL中导致排序的操作
SQL查询语句中引起排序的操作大致有:ORDER BY和GROUP BY子句；独特的修饰语；并集、交集、减集运算符；多个表连接时的排序合并连接等。
4.1.3如何避免排序
1)建立合适的索引。
频繁排序和连接的索引字段。索引建立后，当服务器向这些字段发送排序请求时，将直接引用索引，不进行排序；执行等联接查询时，如果连接字段的索引没有建立，服务器执行排序合并联接，联接操作的过程如下:
对两个或两个以上相连的表格分别进行全扫描；
分别对每个表格中的所有行集合进行排序；
合并排序结果。
如果用于建立连接的字段已经被索引，则服务器执行嵌套循环连接，这不需要任何排序，并且过程如下:
全面扫描驾驶台；
使用连接字段值对每个返回行进行索引唯一扫描；
使用索引扫描返回的ROWID值在从表中定位记录；
合并主表和从表中的匹配记录。
因此，建立索引可以避免大多数排序操作。
2)用UNIION ALL替换UNION。
UNION在表链接后会过滤掉重复的记录，所以表链接后会对生成的结果集进行排序，删除重复的记录并返回结果。在大多数应用中，不会产生重复记录，最常见的是过程表和历史表并集。因此，使用UNION ALL操作符而不是UNION，因为UNION ALL操作只是组合两个结果和返回。
4.2优化I/O
过多的I/O操作会占用CPU时间，消耗大量内存，占用过多闩锁，所以需要对SQL的I/O进行优化。优化I/O最有效的方法是使用索引扫描，而不是全表扫描。
基于函数的索引的应用
基于函数的索引(美国联邦调查局)提供了索引计算列并在查询中使用这些索引的能力。美国联邦调查局的本质是对查询所需的中间结果进行预处理。如果美国联邦调查局与查询语句中的嵌入函数完全匹配，CBO将在生成查询计划时自动启用索引范围扫描来替换全表扫描。检查以下代码片段，并使用AUTOTRACE观察创建美国联邦调查局前后执行计划的变化。
select * from EMP where upper(ename)= ' SCOTT '
在创建美国联邦调查局之前，它显然是全表扫描。
执行计划
……
“雇员”的1 0表访问(完全)(成本=2卡=1字节=22)
idle & gt对EMPLOYEES(UPPER(FIRST_NAME))创建索引EMP _ UPPER _ FIRST _ NAME
索引已创建。
再次运行相同的查询，
执行计划
……
“雇员”的1 0表访问(按索引ROWID)(成本=1卡=1字节=22)
2 1 ' EMP _ UPPER _ FIRST _ NAME '(非唯一)的索引(范围扫描)(成本=1卡=1)
这个简单的例子充分说明了美国联邦调查局在SQL查询优化中的作用。美国联邦调查局使用的功能可以是用户自己创建的功能。函数越复杂，基于该函数创建美国联邦调查局对SQL查询性能的优化效果越明显。
4.2.2应用物化视图和查询重写
物化视图是预先计算好的结果集，通常包含聚集、多表连接等复杂操作。物化视图在数据库中自动维护，并根据用户的要求进行刷新。查询重写机制是用数据库中的一个替代对象(如物化视图)将用户提交的查询重写为一个完全不同但功能等价的查询。查询重写对用户是透明的，用户完全按照常规编写查询语句访问数据库，优化程序(CBO)自动决定是否重写用户提交的查询。查询重写是一种非常有效的提高查询性能的方法，特别是对于数据仓库环境下的汇总、多表连接等高成本操作。
下面是一个非常简单的例子，展示了物化视图和查询重写在优化SQL查询性能中的作用。
选择部门、部门编号、部门名称、计数(*)
来自员工，部门
其中雇员部门编号=部门部门编号
按部门分组部门编号，部门名称
查询计划和主要统计如下:
实施计划:
-
……
2 1散列连接(成本=5张卡=14字节=224)
“部门”的3 2表访问(完全)(成本=2卡=4字节=52)
“EMP”的4 ^ 2表访问(完全)(成本=2张卡=14字节=42)
主要统计数据:
-
305次递归调用
46次一致获得
创建实体化视图EMP_DEPT:
创建实体化视图emp_dept立即构建
按需刷新
启用查询重写
如同
选择部门、部门编号、部门名称、计数(*)
来自员工，部门
其中雇员部门编号=部门部门编号
按部门分组部门编号，部门名称
/
再次执行查询。执行计划和主要统计如下:
实施计划:
-
……
' EMP_DEPT '的1 0表访问(完全)(成本=2卡=327字节=11445)
主要统计数据:
-
79次递归调用
28个一致的获得
可以看出，物化视图建立之前，先进行两个表的全表扫描，然后进行哈希连接，再进行分组排序和选择操作；物化视图建立后，CBO自动将上述复杂操作转化为物化视图EMP_DEPT的全扫描，相关统计数据也有了很大的提升，递归调用从305个减少到79个，逻辑I/O(CONSISTENT GETS)从46个减少到28个。
4.2.3将频繁访问的小表读入缓存。
逻辑I/o总是比物理I/O快。如果数据库中有应用程序频繁访问的小表，可以将这些表强制读入KEEP池以避免物理I/O。
4.3多表连接优化
多表连接是查询复杂度的最大体现，多表连接操作往往会消耗大量的CPU时间和内存，因此多表连接查询性能优化往往是SQL优化的重点和难点。
4.3.1消除外部连接
通过消除外部连接，不仅查询更容易阅读，而且性能通常也可以得到提高。一般的想法是有以下形式的查询:
SELECT …，外部联接表。圆柱
FROM SOME_TABLE，OUTER_JOINED_TO_TABLE
其中…=OUTER_JOINED_TO_TABLE(+)
可以转换成以下形式的查询:
SELECT …，(SELECT COLUMN FROM OUTER _ JOINED _ TO _ TABLE WHERE…)FROM SOME _ TABLE；
4.3.2谓词被向前推，以优化中间结果。
多表连接性能低，大多是因为连接操作和过滤操作顺序不合理。在编写多表连接查询时，大多数用户总是在应用过滤条件之前进行连接操作，导致服务器做了过多的无用功。针对这类问题，其优化思路是将过滤谓词尽可能向前推，使不符合条件的记录提前被过滤掉，只连接少数符合条件的记录，这样可以使SQL查询的效率提高一倍。
标准连接查询如下:
Select a.prod_name，sum(b.sale_quant)，
sum(c.sale_quant)，sum(d.sale_quant)
从产品a、电话销售b、在线销售c、商店销售d
其中a .生产标识=b .生产标识，a .生产标识=c .生产标识
并且a.prod_id=d.prod_id和a.order _ date & gt系统日期-90
按a.prod_id分组；
启用嵌入式视图并设置条件a . order _ date >；Sysdate-90前移，优化后的代码如下:
从产品a中选择a.prod_name，b.tele_sale_sum，c.online_sale_sum，d.store_sale_sum
(select sum(sal _ quant)tele_sale _ sum from product，tele _ sale
其中product.order _ date & gtsysdate-90和product . prod _ id = tele _ sale . prod _ id)b，
(select sum(sal _ quant)online _ sale _ sum
从产品，电话销售
其中product.order _ date & gtsysdate-90和product . prod _ id = online _ sale . prod _ id)c，
(select sum(sal _ quant)store _ sale _ sum
从产品，商店_销售
其中product.order _ date & gtsysdate-90和product . prod _ id = store _ sale . prod _ id)d，
其中a.prod_id=b.prod_id并且
a.prod_id=c.prod_id和a . prod _ id = d . prod _ id；
5结束语
SQL语言在数据库应用中起着非常重要的作用，它的性能直接影响到整个信息系统的可用性。本文从影响SQL性能的三个主要方面分析了如何优化SQL查询的I/O，避免高成本的排序操作，优化多表连接。需要强调的是，理解SQL语句所解决的问题比SQL调优本身更重要，所以SQL调优需要系统分析师、开发人员和数据库管理员的密切合作。
参考
托马斯·凯特。有效的Oracle设计:设计和构建高性能的Oracle应用程序[M]，McGral- Hill公司，2003年
[2]凯文·罗尼，乔治·科赫，甲骨文9i:完整参考[M]，麦格拉-希尔公司，2002
[3] Oracle9i SQL参考发布2(9.2)[OL/M]，2002.10。/技术/
[4] Oracle9i数据仓库指南release 2(9.2) [OL/M]，2002.03 ./技术/
[5]Alexey Danchenkov，Donald Burleson，Oracle调优:权威参考[OL/M]，Rampant Techpress，2006年。
[6] Oracle9i数据库概念第2版(9.2) [OL/M]，2002.08 ./技术/
[7] Oracle9i提供的plsql包和类型参考release 2(9.2) [OL/M]，2002.12。/技术/