SQL 21日自学通(V3.0)(PDF格式)-第41部分

快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！





　　　　　　准　SQL　的扩展之一　



　　　l　　其它的扩展如　SYBASE　或　MICROSOFT　SERVER　　的　TRANSCAT　SQL　它将在第　19　



　　　　　　天提到　



　　　l　　第　20　　天　回到　ORACLE　中学习　SQL*PLUS　　它可以让你使用高级命令来与数据库　



　　　　　　进行会话　　　它也可以让你以一种让人满意的方法来格式化查询所生成的报表　　　　　　　　　　　　　　　你　



　　　　　　可以将它与　PL/SQL　协同工作　



　　　l　　第　21　　天　则介绍了关系数据库用户常见的逻辑错误和错误信息　　我们对这些错误　



　　　　　　提供了比较好的解决方法以及如何避免这些错误的技巧　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　307　


…………………………………………………………Page　308……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　　笨猪　



第　15　天　　　　对　SQL　语句优化以提高其性能　



　　　对　SQL　语句的流化是在设计和调试数据库时影响基应用性能的重要部分　　　　　　　　不管数据库　



规划的如何合理或数据设计考虑的如何健全　　　　　　　　你都不会对你的查询返回数据的及时性感到　



满意　　即便是错误也是如此　　那么你的客户呢　　如果你不遵循一些基本的指导方针　　那么　



你的客户也不会感到满意　　所以你的领导对你也不会满意　



目标　



　　　你已经知道了在关系数据库中的主要　SQL　组件并且知道了如何与数据库进行通信　　现　



在是将你的知识应用到实际生活中以提高性能的时候了　　　　　　　　　今天的目标是告诉你一些提高流　



与　SQL　语句性能的方法　　在今天中你将学习到以下内容　



　　　l　　明白　SQL　语句流的概念　



　　　l　　明白批装入和事务过程的区别以及它们对数据库性能的影响　



　　　l　　能够对查询的条件进行操作纵以加快数据获得　



　　　l　　熟悉一些影响数据库调试阶段和整个过程的性能底层元素　



　　　这里有一个类似的比喻可以帮助你明白　SQL　语句流的含义　　　　　　迅游运动员必须在尽可能　



短的时间之内完成项目　　　　　否则他就有可能被淘汰　　那么这些游泳者必须具有足够的技术　



并且发挥他的全部的身体的固定能力　　　　　　　以使得他们可以以鱼一样的速度来在水中运行　　而　



且在他的每一次划水和呼吸时都必须如此　　　　　　　游泳者需要在每一时刻都保持他身体的流线型　



以减少水的阻力　



　　　你的　SQL　查询也与此相似　　你应该总是准确地知道你的目标是什么并尽可能地用最快　



的方法来实现这一目标　　　　　你要将大部分时间花在计划上　　　　　少部分时间会花在稍后对它的调　



整上　　你的目标应该总是获得正确的数据和用尽可能少的时间　　　　　　　　　最终用户等待一个缓慢查　



询的感觉就像一个饥肠辘辘的人等一份久候不至的大餐一样　　　　　　　　　　虽然你可以将大多数的查询　



用几种方法来输写　　但对查询中元素的安排的不同导致的最终时间差异可能有几秒　　　　　　　　　　　　几分　



钟　　甚至是几个小时　　　SQL　　语句的流化过程可以找到在你的查询中排列这些元素的最优结　



果　



　　　除了流化你的　SQL　语句以外　　　你还应该考虑到一些其他的影响你的数据库性能的因　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　308　


…………………………………………………………Page　309……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　　　　笨猪　



素　　例如　　在数据库中的并发事务控制　　　　　　　　　　　　　　　表的索引以及对数据库的深层调试等等　



　　　　注　　在今天的例子中我们使用的是　Personal　　ORACLE　　7　　　　　　　　　　　　其工具可以在　ORACLE　　7。3　



关系数据库管理系统中找到　　但今天所讨论的概念不局限于　ORACLE　　　　　　　　　　　　　　　　　　　　　　　　　它们也可以应用到　



其它的数据库管理系统中　



让你的　SQL　语句更易读　



　　　　尽管实际上易读性不会影响　SQL　语句的性能　　　　　　　　　　　　　　　　　好的程序员会习惯于调用易读的代码　



当你在　WHERE　　　　　子句中存在多个条件的时候尤为重要　　任何人读到这个子句的时候都可以　



确切地知道表是否已经被正确地归并了　　　　　　　　　　　　　　　　　并且也可以确切地知道条件的次序　



　　　　试着读下边的语句　



　　　　SQL》SELECT　　　　EMPLOYEE_TBL。EMPLOYEE_ID　　　　　　　　　EMPLOYEE_TBL。NAME　



EMPLOYEE_PAY_TBL。SALARY　　　　　　EMPLOYEE_PAY_TBL。HIRE_DATE　



　　　　FROM　EMPLOYEE_TBL　　　　　EMPLOYEE_PAY_TBL　



　　　　WHERE　　　　EMPLOYEE_TBL。EMPLOYEE_ID=EMPLOYEE_PAY_TBL。EMPLOYEE_ID　



AND　　　　EMPLOYEE_PAY_TBL。SALARY》30000　　　　　　OR　　　（EMPLOYEE_PAY_TBL。SALARY　



BETWEEN　　25000　　AND　　30000　　AND　　EMPLOYEE_PAY_TBL。HIRE_DATE　　《　　SYSDATE　　



365）　



　　　　下边是相同的查询的更易读的写法　



　　　　SQL》　SELECT　E。EMPLOYEE_ID；　E。NAME；　P。SALARY；　P。HIRE_DATE　



　　　　　　　　2　　　　FROM　　EMPLOYEE_TBL　　E；　



　　　　　　　　3　　　　　　　　　　　　　　EMPLOYEE_PAY_TBL　　P　



　　　　　　　　4　　　　WHERE　　E。EMPLOYEE_ID　　=　　P。EMPLOYEE_ID　



　　　　　　　　5　　　　　　　　AND　　P。SALARY　　》　　30000　



　　　　　　　　6　　　　　　　　　　OR　　（P。SALARY　　BETWEEN　　25000　　AND　　30000　



　　　　　　　　7　　　　　　　　AND　　P。HIRE_DATE　　《　　SYSDATE　　　365）；　



注　　　注意在上边的查询中使用了表的别名　　在第　2　　行语句中　EMPLOYEE_TBL　　被赋予了简　



单的别名　E　　　　　在第　3　行中　EMPLOYEE_PAY　被赋予了别名　P　　　　　　　　　　　　你可以看到在第　4　　　　　　　　5　6　　行　



中　E　和　P　　已经取代了表的名字　　别名需要输入的字符量比输入全名时要少许多　　更重要的　



是　　使用别句以后查询变得更有组织和易读性　　　　　　　　　　　　　　　　　　　而使用表的命名则引出不必要的混乱　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　309　


…………………………………………………………Page　310……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　笨猪　



　　　这两个查询是一样的　　　　　但是第二个显然是更易读的　　　　　它非常具有结构性　　　　　那就是　　查　



询的各个成份被回车和回车合理地分开了　　　　　　　　你可以很容易地找到哪里是查询的　SELECT　　　　部　



分　　在　SELECT　　子句中　和都有哪些表被访问　　在　FROM　　子句中　　　哪些是需要指定的条　



件　　在　WHERE　子句中　



全表扫描　



　　　当数据库服务为执行某一个　SQL　语句需要对表中的每一个记录进行检查时就会发生全　



表扫描　　它通常在执行　SELECT　　语句时发生　　但有时也会在更新和删除记录时发生　　　　　　　　全表　



扫描通常是因为在　WHERE　　　子句中使用了索引中没有的字段时发生　　它就像对一本书一页　



一页地来看以找到所需内容一样　　在大多数情况下　　　　　　　　我们使用索引　



　　　我们通常通过对经常在　WHERE　　　子句中使用的字段建立索引来避免全表扫描　　　　　　　　　索引所　



提供的找数据的方法与通过目录找书中的指定内容方法一样　　　　　　　　　　　使用索引可以提高数据的访　



问速度　



　　　尽管程序员们并不赞成使用全表扫描　　但是有时使用它也是适当的　　　　　　　　　　　例如　



　　　l　　你选择了一个表中的大多数行的时候　



　　　l　　你在对表中的每一行记录进行更新的时候　



　　　l　　表非常小的时候　



　　　对于头两种情况索引的效率是非常低的　　　　　　　　因为数据库服务程序不得不频繁地读表和索　



引内容　　也就是说索引只有在你所要找的数据只在表中所占比率很小的时候才会非常地有　



效　　通常不会超过表中全部数据量的　10％到　15％　



　　　此外　　最好在大型表中使用索引　　　　　　当你设计表和索引的时候你要考虑表的大小　　合适　



的索引应该是建立在对数据的熟悉上　　知道那一列数据是最经常引用的　　　　　　　　　　　　如果想让索引工　



作得好你需要做一些试验　



注　　当说到　　大表　　的时候　　这里的大是相对而言的　　　　　　一个表比某个表相比可以说很大　



　　　而它与另一个表相比时却又很小　　　　　　　表的大小的概念是与数据库中其它表的大小　　　　　　　　可用　



　　　的磁盘空间　可用的磁盘的数量以及类似的因素相关的　很明显　2GB　的表很大而　16KB　



　　　的表是小的　　如果一个数据库中表的平均大小是　100MB　　　　　　　那么一个　500MB　　的表就是　



　　　大的　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　310　


…………………………………………………………Page　311……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　　笨猪　



加入一个新的索引　



　　　你经常会发现一些　SQL　语句运行的时间长得不合情理　　　　　　　尽管其它的语句运行的性能看　



起来是可以接受的　　例如　　　　当数据的检索条件改变或表的结构改变以后　



　　　当我们加入一个　WINDOWS　　的应用前端时我们也会发现速度的下降　　对于这种情况你　



首先要检查的是所用的目标表是否存在索引　　然后大多数情况下我们会发现表是有索引的　



但是在　WHERE　子句中所使用的新条件没有索引　　看一下　SQL　语句中的　WHERE　子句　　　　　　我　



们要问的是　　　　是否可以加入其它的索引　　　　　　　如果是在下列条件下　　那么答案是肯定的　



　　　l　　最大的限制条件返回表的数据库小于表总数据量的　10％　



　　　l　　最大的限制条件在　SQL　语句中是经常使用的　



　　　l　　条件列的索相将会返回一个唯一的值　



　　　l　　列经常被　ORDER　BY　或　GROUP　BY　子句所引用　



　　　也可以使用复合索引　　　　复合索引是基于表中两个或更多列的索引　　如果在　SQL　语句中　



经常将两列一起使用时这种索引会比单列索引更有效　　　　　　　　　　如果在一起的索引列经常是分开使　



用的　　特别是在其它的查询中　　　　　那么单列索引则是更合适的　　　　　　所以你要经过试验来判断在　



你的数据库中使用哪一种索引会是更合适的　



在查询中各个元素的布局　



　　　在你的查询中最好的元素布局　　　　　　尤其是在　WHERE　子句中　　是根据解释器处理　SQL　语　



句的步骤和次序而定的　　在条件中安排被索引过的列　　　　　　　　　这样的条件将会查找最少的记录　



　　　你不一定非要在　WHERE　　子句中使用已经被索引过的列　　但是显然这样做会更有效　



试着调整　SQL　语句以使它返回的记录数最少　　在一个表中返回记录数最少的条件就是最大　



的限制条件　　在通常的语句中　　　　　　你应该把最大的条件限制语句放在　WHERE　　　　　　子句的最后　



　ORACLE　查询优化会对　WHERE　子句从后向前读　　所以它会最先处理我们放置的条件语　



句　



　　　当优化器首先读到最大条件限制语句以后　　　　　　　　它就将为以后的条件所提供的结果集缩减　



至最小了　　　下一个条件将不再搜索整个表　　　　　　而是搜索经过最大条件限制过的子集　　所以　



数据的返回就会更快　　在复杂查询中的多个查询　　子查询　　计算以及使用逻辑条件　　AND　



OR　NOT　　中最大限制条件可能并不清晰　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　311　


…………………………………………………………Page　312……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　　　　　　笨猪　



　　　　　技巧　　请查看你的数据库文档资料　　　　　　　　　　　　　　　　　　　　　　　看一看解释器是如何处理你的　SQL　语句的　



　　　　　下边的测试是我们对用两种不同的方法来查询相同的内容所耗用时间的差异　　该例子　



使用　ORACLE　7　关系数据库系统　　切记　　　　　　　　　　　　　　　　　　　　　在解释器中的优化是从后向前进行的　



　　　　　在创造一个　SELECT　　　　　　　　　　　语句之前　　对于每一个条件我们都选择了独立的行　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　下边是不同　



的条件给出的数值　



　　　　　Condition　　　　　　　　　　　　　　　　　　　　　　　　Distinct　Values　



　　　　　calc_ytd　=　'…2109490。8'　　　　　　　　　　13；000　＋　



　　　　　dt_stmp　=　'01…SEP…96'　　　　　　　　　　　　15　



　　　　　output_cd　=　'001'　　　　　　　　　　　　　　　　13　



　　　　　activity_cd　=　'IN'　　　　　　　　　　　　　　　10　



　　　　　status_cd　=　'A'　　　　　　　　　　　　　　　　　　4　



　　　　　function_cd　=　'060'　　　　　　　　　　　　　　　　6　



注　　　　最大限制条件就是最显著的值　



下边的例子中在　WHERE　子句中使用了最大限制条件放在最前　



INPUT　



　　　　　　　　　　SQL》　SET　TIMING　ON　



　　　　　　　　　　　　　　2　　　　SELECT　　COUNT（*）　



　　　　　　　　　　　　　　3　　　　FROM　　FACT_TABLE　



　　　　　　　　　　　　　　4　　　　WHERE　　CALC_YTD　　=　　'…2109490。8'　



　　　　　　　　　　　　　　5　　　　　　　　AND　　DT_STMP　　=　　'01…SEP…96'　



　　　　　　　　　　　　　　6　　　　　　　　AND　　OUTPUT_CD　　=　　'001'　



　　　　　　　　　　　　　　7　　　　　　　　AND　　ACTIVITY_CD　　=　　'IN'　



　　　　　　　　　　　　　　8　　　　　　　　AND　　STATUS_CD　　=　　'A'　



　　　　　　　　　　　　　　9　　　　　　　　AND　　FUNCTION_CD　　=　　'060'；　



OUTPUT　



　　　　　　　　　　COUNT（*）　



　　　　　　　　　　　　　　　　　　　　　　　　8　



　　　　　　　　　　1　row　selected。　



　　　　　　　　　　Elapsed：　　　　00：00：15。37　



INPUT/OUTPUT　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　312　


…………………………………………………………Page　313……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　　　　笨猪　



　　　　SQL》　SET　TIMING　ON　



　　　　　　　　2　　　　SELECT　　COUNT（*）　



　　　　　　　　3　　　　FROM　　FACT_TABLE　



　　　　　　　　4　　　　WHERE　　FUNCTION_CD　　=　　'060'　



　　　　　　　　5　　　　　　　　AND　　STATUS_CD　　=　　'A'　



　　　　　　　　6　　　　　　　　AND　　ACTIVITY_CD　　=　　'IN'　



　　　　　　　　7　　　　　　　　AND　　OUTPUT_CD　　=　　'001'　



　　　　　　　　8　　　　　　　　AND　　DT_STMP　　=　　'01…SEP…96'　



　　　　　　　　9　　　　　　　　AND　　CALC_YTD　　=　　'…2109490。8'；　



OUTPUT　



　　　　　　　　COUNT（*）　



　　　　　　　　　　　　　　　　　　　　　　8　



　　　　　　　　　1　row　selected。　



　　　　　　　　Elapsed：　　　　00：00：01。80　



分析　



　　　　注意所使用的时间的不同　　　　　　　　　　　　　　只要简单地改变所给出的统计的次序　　第二个查询比第一　



个查询快了　14　秒　　　　　　　　那么可以设想一下当查询的结构设计不好的时候耗用几个小时的情形　



过程　



　　　　如果所使用的查询有规律可循　　　　　　　　　　　　　　　　　那么你可以试着使用过程　　　　　　　　　　　　　　过程可以调用很大的一组　



SQL　的语句　　参见第　13　天　　　　　　　　　高级　SQL　主题　



　　　　过程是被数据库的引擎编译后运行的　　　　　　　　　　　　　　　　　　　与　SQL　语句不同　　　　　　　　数据库引擎在执行过程的时　



候不需要进行优化　　　　　　　　　　　过程相对于独立的多个　SQL　语句　　　　　　　　　　　　　　　它对于用户来说更容易使用而对于　



数据库来说更为有效　



避免使用　OR　



　　　　如果可能的话应该在查询尽量避免使用逻辑操作符　OR　　　　　　　　　　　　　　　　　　　　　　　　　　OR　　会不可避免的根据表的大　



小降低查询的速度　　我们发现　IN　通常比　OR　要快　　当然　　优化器的文档中并不是这样说的　



可是　　　　下边的例子中使用了多个　OR　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　313　


…………………………………………………………Page　314……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　　　　　　笨猪　



　　　　　INPUT　



　　　　　　　　　　SQL》　SELECT　*　



　　　　　　　　　　　　　　2　　　　FROM　　FACT_TABLE　



　　　　　　　　　　　　　　3　　　　WHERE　　STATUS_CD　　=　　'A'　



　　　　　　　　　　　　　　4　　　　　　　　　　OR　　STATUS_CD　　=　　'B'　



　　　　　　　　　　　　　　5　　　　　　　　　　OR　　STATUS_CD　　=　　'C'　



　　　　　　　　　　　　　　6　　　　　　　　　　OR　　STATUS_CD　　=　　'D'　



　　　　　　　　　　　　　　7　　　　　　　　　　OR　　STATUS_CD　　=　　'E'　



　　　　　　　　　　　　　　8　　　　　　　　　　OR　　STATUS_CD　　=　　'F'　



　　　　　　　　　　　　　　9　　　　ORDER　　BY　　STATUS_CD；　



　　　　　　　　　　下边是使用子串和　IN　写成的相同的查询　



　　　　　INPUT　



　　　　　　　　　　SQL》　SELECT　*　



　　　　　　　　　　　　　　2　　　　FROM　　FACT_TABLE　



　　　　　　　　　　　　　　3　　　　WHERE　　STATUS_CD　　IN　　（'A'；'B'；'C'；'D'；'E'；'F'）　



　　　　　　　　　　　　　　4　　　　ORDER　　BY　　STATUS_CD；　



分析　



　　　　　你可以自己进行一些类似的测试　　　　　　　　　　　　　　　　　　　　　　　　尽管书中的代码是最为标准和直接的　　　　　　　　　　　　　　　　　　　　　　　　　　但是你会发　



现你经常可以得到你自己的结论　　特别是在性能方面　



　　　　　下边是使用子串和　IN　　的又一个例子　　注意　　　　　　　　　　　　　　　　　　　　　　　　　　第一个查询结合使用了　LIKE　和　OR　



INPUT　



　　　　　SQL》　SELECT　*　



　　　　　　　　　2　　　　FROM　　FACT_TABLE　



　　　　　　　　　3　　　　WHERE　　PROD_CD　　LIKE　　'AB％'　



　　　　　　　　　4　　　　　　　　　　OR　　PROD_CD　　LIKE　　'AC％'　



　　　　　　　　　5　　　　　　　　　　OR　　PROD_CD　　LIKE　　'BB％'　



　　　　　　　　　6　　　　　　　　　　OR　　PROD_CD　　LIKE　　'BC％'　



　　　　　　　　　7　　　　　　　　　　OR　　PROD_CD　　LIKE　　'CC％'　



　　　　　　　　　8　　　　ORDER　　BY　　PROD_CD；　



　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　314　


…………………………………………………………Page　315……………………………………………………………

SQL　21　　日自学通（V1。0）　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　翻译人　　笨猪　



　　　SQL》　SELECT　*　



　　　　　　　2　　　　FROM　　FACT_TABLE　



　　　　　　　3　　　　WHERE　　SUBSTR（PROD_CD；1；2）　　IN　　（'AB'；'AC'；'BB'；'BC'；'CC'）　



　　　　　　　4　　　　ORDER　　BY　　PROD_CD；　



　　　第二个例子不仅避免的使用　OR　　　　　　　　而且也避免了使用　LIKE　与　OR　　的联合操作　　你可以　



试一下这个例子　　　　　　你会看到对于你的数据它们在实际运行时性能上的不同　



OLAP　与　OLTP　的比较　



　　　当你在调试一个数据库的时候　　　　　　　　　你首先要决定的是它应该经常由谁来使用　　　　　　　　　　　　在线的分　



析处理　　　OLAP　　的数据库是一个对最终用户的查询进行统计和汇总的系统　　　　　　　　　　　　　　　　　在这种环境　



下返回的数据经常用与统计报告给决策管理过程提供帮助　　而在线事务过程　　OLTP　　　　　　　　　　　　　　　　　　　的数　



据库则是一个将主要的功能提供给为最终用户输入服务的环境的系统　　包括用户日复一日　



的查询　　OLTP　　系统经常用在以日为基本单位在数据库中操作数据的使用场合　　　　　　　　　　　　　　　　　　数据仓库　



与　DSS　　可以从在线的事务处理数据库中得到它们所需的数据　　有时也可以从其它的　OLAP　



数据库