SQL语言艺术(PDF格式)-第2部分

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




第一个原因，也是最大的原因，在于数据库连接是很“重”的操作，消耗资源很多。　



在常见的客户/服务器模式中（现在仍广为使用），简单的连接操作背后潜藏着如下事实：首先，　

客户端与远程服务器的监听程序（listener　program）建立联系；接着，监听程序要么创建一个　

进程或线程来执行数据库核心程序，要么直接或间接地把客户请求传递给已存在的服务器进程，　

这取决于此服务器是否为共享服务器。　



除了这些系统操作（创建进程或线程并开始执行）之外，数据库系统还必须为每　



次session建立新环境，以跟踪它的行为。建立新session前，DBMS还要检查密码是否与保存　

的加密的账户密码相符。或许，DBMS还要执行登录触发器（logon　trigger），还要初始化存储　

过程和程序包（如果它们是第一次被调用）。上面这些还不包括客户端进程和服务器进程之间要　

完成的握手协议。正因为如此，连接池（connection　pooling）等保持永久数据库连接的技术对　

性能才如此重要。　



第二个原因，你的程序（甚至包括存储过程）和数据库之间的交互也有开销。　



即使数据库连结已经建立且仍未中断，程序和　DBMS　核心之间的上下文切换（context　switch）　

也有代价。因此，如果　DBMS　支持数据通过数组传递，应毫不犹豫地使用它。如果该数组接　

口是隐式的（API内部使用，但你不能使用），那么明智的做法是检查它的默认大小并根据具体　

需要修改它。当然，任何逐行处理的方式都面临上下文切换的问题，并对性能产生严重影响—　

—本章后面还会多次涉及此问题。　



总结：数据库连接和交互好似万里长城——长度越长，传递消息越耗时。　



战略优先于战术　



Strategy　Before　Tactics　

SSttrraatteeggyy　BBeeffoorree　TTaaccttiiccss　



战略决定战术，反之则谬也。思考如何处理数据时，有经验的开发者不会着眼于细微步骤，而　

是着眼于最终结果。要获得想要的结果，最显而易见的方法是按照业务规则规定的顺序按部就　

班地处理，但这不是最有效的方法——接下来的例子将显示，刻意关注业务处理流程可能会使　


…………………………………………………………Page　9……………………………………………………………

我们错失最有效的解决方案。　



几年前，有人给了我一个存储过程，让我“尝试”着进行一下优化。为什么说是“尝试”呢？因为　

该存储过程已经被优化两次了，一次是由原开发者，另一次是由一个自称Oracle　专家的人。但　

尽管如此，这个存储过程的执行仍会花上20分钟，使用者无法接受。　



此存储过程的目的，是根据现有库存和各地订单，计算出总厂需要订购的原料数量。大体上，　

它就是把不同数据源的几个相同的表聚合（aggregate）到一个主表（master　table）中。首先，　

将每个数据源的数据插入主表；接着，对主表中的各项数据进行合计并更新；最后，将与合计　

结果无关的数据从表中删除。针对每个数据源，重复执行上述步骤。所有　SQL　语句都不是特　

别复杂，也没有哪个单独的SQL语句特别低效。　



为了理解这个存储过程，我花了大半天时间，终于发现了问题：为什么该过程要用这么多步骤　

呢？在from子句中加上包含　union　的子查询，就能得到所有数据源的聚合（aggregation）。一条　

select　语句，只需一步就得到了结果集，而之前要通过插入目标表（target　table）得到结果集。　

优化后，性能的提升非常惊人——从　20　分钟减至　20　秒；当然，之后我花了一些时间验证了　

结果集，与未优化前完全相同。　



想要获得上述的大幅提高性能，无需特别技能，仅要求站在局外思考（think　outside　thebox）的　

能力。之前两次优化因“太关注问题本身”而收到了干扰。我们需要大胆的思维，站得远一些，　

试着从大局的角度看待问题。要问自己一些关键的问题：写存储过程之前，我们已有哪些数据？　

我们希望存储过程返回什么结果？再辅以大胆的思维，思考这些问题的答案，就能得到一个性　

能大幅提升的处理方式了。　



总结：考虑解决方案的细节之前，先站得远一些，把握大局。　



先定义问题，再解决问题　



Problem　Definition　Before　Solution　

PPrroobblleemm　DDeeffiinniittiioonn　BBeeffoorree　SSoolluuttiioonn　



一知半解是危险的。人们常在听说了新技术或特殊技术之后——有时的确很吸引人——试图采　

用它作为新的解决方案。普通开发者和设计师通常会立即采纳这些新“解决方案”，直到后来才　

发现它们会产生许多后续问题。　



现成的解决方案中，非规范化设计引人注目。设计伊始，非规范化设计的拥护者就提出此方案，　

为了寻求“性能”而无视最终将会面临的升级恶魔——而事实上，在开发周期早期，改进设计（或　

学习如何使用join）也是一个不错的选择。作为非规范化设计的一种手段，物化视图（materialized　

view）常被认为是灵丹妙药。物化视图有时被称为快照（snapshot），这个更加平常的词更形象　

地反映了可悲的事实：物化视图是某时间点的数据副本。在没有其他办法时，这个理论上遭到　

质疑的技术也未尝不值得一试，借用卡夫卡（Franz　Kafka）的一句名言：“逻辑诚可贵，生存价　

更高。”　



然而，绝大部分问题都可借助传统技术巧妙解决。首先，应学会充分利用简单、传统的技术。　

只有完全掌握了这些技术，才能正确评价它们的局限性，最终发现它相当于新技术的潜在优势　

（如果有的话）。　



所有技术方案，都只是我们达到目标的手段。没有经验的开发者误把新技术本身当成了目标。　


…………………………………………………………Page　10……………………………………………………………

对于热衷于技术、过于看重技术的人来说，此问题就更为严重。　



总结：先打基础，再赶时髦：摆弄新工具之前，先把手艺学好。　



直接操作实际数据　



OperationsAgainst　Actual　Data　

OOppeerraattiioonnssAAggaaiinnsstt　AAccttuuaall　DDaattaa　



许多开发者喜欢建立临时工作表（temporaryworktable），把后续处理使用的大量数据放入其中，　

然后开始“正式”工作。这种方法广受质疑，反映了“跳出业务流程细节考虑问题”的能力不足。　

记住，永久表（permanent　table）可以设置非常复杂的存储选项（在第5章将讨论一些存储选项　

的设置），而临时表不能。临时表的索引（如果有的话）可能不是最优的，因此，查询临时表的　

语句效率比永久表的差。另外，查询之前必然先为临时表填入数据，这自然也多了一笔额外的　

开销。　



就算使用临时表有充足理由，若数据量大，也绝不能把永久表当作临时工作表来用。问题之一　

在于统计信息的自动收集：若没有实时收集要求，DBMS通常会在不活动或活动少时进行统计　

信息收集，而这时作为临时工作表可能为空，从而使优化器收到了完全错误的信息。这些不正　

确且有偏差的统计信息可能造成执行计划（execution　plan）完全不合理，导致性能下降。所以，　

如果一定要用临时表，应确保数据库知道哪些表是临时的。　



总结：暂时工作表意味着以不太合理的方式存储更多信息。　



　　SQL　

用SSQQLL处理集合　



Set　Processing　in　SQL　

SSeett　PPrroocceessssiinngg　iinn　SSQQLL　



SQL　完全基于集合（Set）来处理数据。对大部分更新或删除操作而言　——　如果不是针对整　

个表的话　——　你必须先精确定义出要处理的记录的集合。这定义了该处理的粒度　

（granularity），可能是对大量记录的粗粒度操作，有可能是只影响少数记录的细粒度操作。　



将一次“大批量数据的处理”分割成多次“小块处理”是个坏主意，除非对数据库的修改太昂贵，　

否则不要使用，因为这种方法极其低效：　



（1）占用过多的空间保存原始数据，以备事务（transaction）回滚（rollback）之需；　



（2）万一修改失败，回滚消耗过长的实践。　



许多人认为，进行大规模修改操作时，应在操作数据的代码中有规律地多安排些mit命令。　

其实，严格从实践角度来讲，“从头开始重做”比“确定失败发生的时间和位置，接着已提交部分　

重做”要容易得多、简单得多、也快得多。　



处理数据时，应适应数据库的物理实现。考虑事务失败时回滚所需日志的大小，如果要为undo　

保存的数据量确实巨大，或许应该考虑数据修改的频率问题。也就是说，将大规模的“每月更新”，　

改为规模不大的“每周更新”，甚至改为规模更小的“每日更新”，或许是个有效方案。　



总结：几千个语句，借助游标（cursor）不断循环，很慢。换成几个语句，处理同样的数据，　

还是较慢。换成一个语句，解决上述问题，最好。　


…………………………………………………………Page　11……………………………………………………………

　　　　　　　　SQL　

动作丰富的SSQQLL语句　



Action…Packed　SQL　Statements　

AAccttiioonn……PPaacckkeedd　SSQQLL　SSttaatteemmeennttss　



SQL　不是过程性语言（procedurallanguage），尽管也可以将过程逻辑（procedurallogic）用于SQL，　

但必须小心。混淆声明性处理（declarative　processing）和过程逻辑，最常见的例子出现在需要　

从数据库中提取数据、然后处理数据、然后再插入到数据库时。在一个程序（或程序中的一个　

函数）接收到特定输入值后，如下情况太常见了：用输入值从数据库中检索到一个或多个另外　

的数据值，然后，借助循环或条件逻辑（通常是　if。。。then　。。。else）将一些语句组织起来，对数　

据库进行操作。大多数情况下，造成上述错误做法的原因有三：根深蒂固的坏习惯、SQL知识　

的缺乏、盲从功能需求规格说明。其实，许多复杂操作往往可由一条　SQL　语句完成。因此，　

如果用户提供了一些数据值，尽量不要将操作分解为多条提取中间结果的语句。　



避免在　SQL　中引入“过程逻辑（procedurallogic）”的主要原因有二。　



数据库访问，总会跨多个软件层，甚至包括网络访问。　



即使没有网络访问，也会涉及进程间通讯；额外的存取访问意味着更多的函数调用、更大的带　

宽，以及更长的等待时间。一旦这些调用要重复多次，其对性能的影响就非常可观了。　



在SQL中引入过程逻辑，意味着性能和维护问题应由你的程序承担。　



大多数据库系统都提供了成熟的算法，来处理join等操作，来优化查询以获得更高的效率。基于　

开销的优化器（cost…basedoptimizer，CBO）是很复杂的软件，它早已不像刚推出时那样没什么　

用了，而在大部分情况下都是非常出色的成熟产品了，优秀的CBO　查询优化的效率极高。然而，　

CBO　所能改变的只有　SQL　语句。如果在一条单独的SQL语句中完成尽可能多的操作，那么性　

能优化可以还由　DBMS　核心负责，你的程序可以充分利用DBMS的所有升级。也就是说，未　

来大部分维护工作从程序间接转移给了DBMS　供货商。　



当然，“避免在　SQL　中引入过程逻辑”规则也有例外。有时过程逻辑确实能加快处理速度，庞　

大的SQL语句未必总是高效。然而，过程逻辑及其之后的处理相同数据的语句，可以编写到一　

个单独的　SQL　语句中，CBO　就是这么做的，从而获得最高效的执行方式。　



总结：尽可能多地把事情交给数据库优化器来处理。　



充分利用每次数据库访问　



Profitable　Database　Accesses　

PPrrooffiittaabbllee　DDaattaabbaassee　AAcccceesssseess　



如果计划逛好几家商店，你会首先决定在每家店买哪些东西。从这一刻起，就要计划按何种顺　

序购物才能少走冤枉路。每逛一家店，计划东西购买完毕，才逛下一家。这是常识，但其中蕴　

含的道理许多数据库应用却不懂得。　



要从一个表中提取多段信息时，采用多次数据库访问的做法非常糟糕，即使多段信息看似“无关”　

（但事实上往往并非如此）。例如，如果需要多个字段的数据，千万不要逐个字段地提取，而应　


…………………………………………………………Page　12……………………………………………………………

一次操作全部完成。　



很不幸，面向对象（OO）的最佳实践提倡为每个属性定义一个get方法。不要把　OO　方法与关　

系数据库处理混为一谈。混淆关系和面向对象的概念，以及将表等同于类、字段等同于属性，　

都是致命的错误。　



总结：在合理范围内，利用每次数据库访问完成尽量多的工作。　



　　　　DBMS　

接近DDBBMMSS核心　



Closeness　to　the　DBMS　Kernel　

CClloosseenneessss　ttoo　tthhee　DDBBMMSS　KKeerrnneell　



代码的执行越接近DBMS　核心，则执行速度越快。数据库真正强大之处就在于此，例如，有些　

数据库管理产品支持扩展，你可以用Ｃ等较底层的语言为它编写新功能。用含有指针操作的底　

层语言有个缺点，即一旦指针处理出错会影响内存。仅影响到一个用户已很糟糕，何况数据库　

服务器（就像“服务器”名字所指的一样）出了问题会影响众多“用户”——服务器内存出了问题，　

所有使用这些数据的无辜的应用程序都会受影响。因此，DBMS　核心采取了负责任的做法，在　

沙箱（sandbox）环境中执行程序代码，这样，即使出了问题也不会影响到数据。例如，Oracle　在　

外部函数（external　function）和它自身之间实现了一套复杂的通信机制，此机制在某些方面很　

像控制数据库连结的方法，以管理两个（或多个）服务器上的数据库实例之间的通信。到底采　

用PL/SQL　存储过程还是外部　C　函数，应综合比较后决定。如果精心编写外部　C　函数获得的　

好处超过了建立外部环境和上下文切换（context…switching）的成本，就应采用外部函数。但需　

要处理一个大数据量的表的每一行时，不要使用外部函数。这需要平衡考虑，解决问题时应完　

全了解备选策略的后果。　



如要使用函数，始终应首选DBMS自带的函数。这不仅仅是为了避免无谓的重复劳动，还因为　

自带函数在执行时比任何第三方开发的代码更接近数据库核心，相应地其效率也会高出许多。　



下面这个简单例子是用　Oracle　SQL编写的，显示了　使用Oracle　函数所获得的效率。假设手工　

输入的文本数据可能包含多个相邻的“空格”，我们需要一个函数将多个空格　



替换为一个空格。如果不采用OracleDatabase　10g　开始提供的正规表达式（regularexpression），　

函数代码将会是这样：　

　　　createor　replace　function　squeeze1（p_string　invarchar2）　

　　　returnvarchar2　

　　　is　

　　　v_stringvarchar2（512）：=''；　

　　　c_char　char（1）；　

　　　n_len　　number　：=length（p_string）；　

　　　i　　　　binary_integer　：=1；　

　　　j　　　　binary_integer；　

　　　begin　

　　　while　（i0）　

　　　　　　　　loop　

　　　　　　　　v_string：=substr（v_string；　1；i）　

　　　　　　　　｜｜ltrim（substr（v_string；　i＋1））；　

　　　　　　　　i：=instr（v_string；　'　'）；　

　　　　　　　　end　loop；　

　　　　　　　　returnv_string；　

　　　　　　　　end；　

　　　　　　　　/　


…………………………………………………………Page　14……………………………………………………………

还有第三种方法：　

　　　　　　　　createor　replace　function　squeeze3（p_string　invarchar2）　

　　　　　　returnvarchar2　

　　　　　　is　

　　　　　　v_stringvarchar2（512）：=p_string；　

　　　　　　len1　　number；　

　　　　　　len2　　number；　

　　　　　　begin　

　　　　　　len1　：=length（p_string）；　

　　　　　　v_string：=replace（p_string；　'　'；''）；　

　　　　　　len2　：=　length（v_string）；　

　　　　　　while　（len2　select　squeeze1（'azeryt　hgfrdt　r'）　

　　　　2　from　dual　

　　　　3　/　

　　　　azeryt　hgfrdtr　



　　　　Elapsed：　00：00：00。00　



　　　　SQL》select　squeeze2（'azeryt　hgfrdt　r'）　

　　　　2　from　dual　

　　　　3　/　

　　　　azeryt　hgfrdtr　



　　　　Elapsed：　00：00：00。01　



　　　　SQL》select　squeeze3（'azeryt　hgfrdt　r'）　

　　　　2　from　dual　

　　　　3　/　

　　　　azeryt　hgfrdtr　



　　　　Elapsed：　00：00：00。00　


…………………………………………………………Page　15……………………………………………………………

那么，如果每天要调用该空格替换操作几千次呢？我们构造一个接近现实负载的环境，下面的　

代码将建立一个用于测试的表并填入随机数据，已检测上面三个函数是否有性能差异：　

　　　createtable　squeezable（random_text　varchar2（50））　

　　　/　



　　　declare　



　　　i　　　　　　binary_integer；　



　　　j　　　　　　binary_integer；　

　　　k　　　　　　　binary_integer；　

　　　v_string　varchar2（50）；　

　　　begin　

　　　foriin1。。10000　

　　　loop　

　　　j：=dbms_random。value（1；　100）；　

　　　v_string：=dbms_random。string（'U'；　50）；　

　　　while　（j