━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
sqlssqqll语言艺术内容介绍本书分为12๐章，每一章包含许多原则或准则，并通过举例的方式对原则ท进行解释说明。这些例子大多来自于实际案例，对九种sql经典查询场景以及其性能影响讨论，非常便于实践，为你的实际工作提出了具体建议。本书适合sql数据库开者、软件架构师，也适合dba，尤其是数据库应用维护人员阅读。资深sql专家stéphanefa肉lt倾力打造《软件架构设计》作者温昱最新译作巧妙借鉴《孙子兵法》的智慧结晶传授25๓年的sql性能与调校经验深入探讨九种常见查询方案及其性能ม前言过去，“信息技术it”的名字还不如今天这般耀眼，被称为ฦ“电子数据处理”。其实，尽管当今新潮技术层出不穷，数据处理依然处于我们系统的核心地位，而且需管理的数据量的增长度似乎ๆ比处理器的增长度还快。今天，最重要的集团数据都被保存在数据库中，通过sql语言来访问。sql语言虽有缺点，但非常流行，它从1้9๗80年代早期开始被广泛接受，随后就所向无敌了。如今，年轻开者在接受面试时，没有谁不宣称自己能熟练应用sql的。sql作为ฦ数据库访问语言，已成为ฦ任何基础it课程的必备部分。开者宣传自己熟ງ练掌握sql，其实前提是“熟练掌握”的定义是“能够获得功能上正确的结果”。然而，全世界ศ的企业如今都面临数据量的爆炸式增长，所以仅做到“功能正确”是不够的，还必须ี足够快，所以数据库性能ม成了许多公司头疼的问题。有趣的是，尽管每个人都认可性能问题源自代码，但普遍接受的事实则是开者的要关注点应该是功能正确。人们认为：为了便于维护，代码中ณ的数据库访问部ຖ分应该尽量简单；“拙劣的sql”应该交给资深的dba去摆弄，他们还会调整几个“有魔力”的数据库参数，于是度就快了——如果数据库还不够快，似乎就该升级硬件了。往往就是这样，那些所谓的“常识”和“可靠方แ法”最终却是极端有害的。先写低效的代码、后由专家调优，这种做法实际上是自找麻烦。本书认为，先要关注性能的就是开者，而且sql问题绝不仅仅只包含正确编写几个查询这么简单。开者角度看到的性能问题和dba从调优角度看到的大相径庭。对dbaທ而言，他尽量从现有的硬件如处理器和存储子系统和特定版本的dbms获得最高性能，他可能有些sql技能ม并能调优一个性能极差的sql语句。但对开者而言，他编写的代码可能要运行5๓到10年，这些代码将经历一代代的硬件，以及dbms各种重要版
----ๅ---ๅ----ๅ--ๅ-----ๅ-ๅ--ๅ-ๅ-ๅpage2๐-ๅ--ๅ-----ๅ----ๅ-------ๅ----ๅ
本升级例如支持互联网访问、支持网格，不一而足。所以，代码必须ี从一开始就快、健全。很多开者仅仅是“知道”sql而已，他们没有深刻理解sql及关系理论，实在令人遗憾。为何写作本书sql书主要分为ฦ三种类型：讲授具体sql方แ言的逻辑和语法的书、讲授高级技术及解决问题方แ法的书、专家与资深dbຘa所需的性能和调优的书。一方面，书籍要讲述如何写sql代码；另一方面，要讲如何诊断ษ和修改拙劣的sql代码。在本书中，我不再为新手从头讲解如何写出优秀的sql代码，而是以越单个sql语句的方式看待sql代码，无疑ທ这更加重要。教授语言使用就够难了，那么本书是怎样讲述如何高效使用sql语言的呢？sql的简单性具有欺骗性，它能支持的情况组合的数目几乎是无限的。最初，我觉得sql和国际象棋很相似，后来，我悟到明国际象棋是为了教授战争之道。于是，每当出现sql性能难题的时候，我都自然而然地将之视为ฦ要和一行行数据组成的军队作战。最终，我找到了向开者传授如何有效使用数据库的方法，这就像教军官如何指挥战争。知识、技能、天赋缺一不可。天赋不能传授，只能培养。从写就了《孙子兵法》的孙子到如今的将军，绝大多数战略๓家都相信这一点，于是他们尽量以简单的格言或规则的方式表达沙场经验，并希望这样能指导真实的战争。我将这种方แ法用于战争之ใ外的许多领域，本书借鉴了孙子兵法的方法和书的题目。许多知名it专家冠以科学家称号，而我认为ฦ“艺术”比“科学”更能反映it活动所需的才能、经验和创造力注1。很可能ม是由于我偏爱“艺术”的原因，“科学”派并不赞成我的观点，他们声称每个ฐsql问题都可通过严格分析和参考丰富的经验数据来解决。然而，我不认为这两ä种观点有什么不一致。明确的科学方แ法有助于摆脱单个具体问题的限制，毕竟，sql开必须考虑数据的变化，其中有很大的不确定性。某些表的数据量出乎意料地增长将会如何？同时，用户数量也倍增了，又将会如何？希望数据在线保存好几年将会如何？如此一来，运行在硬件之上的这些程序的行为是否会完全不同？架构级的选择是在赌未来，当然需要明确可靠的理论知识——但这是进一步运用艺术的先决条件。第一次世界ศ大战联军总司令ferdinandfo9๗checນolesupérieuredeguerre的一次演讲中说：战争的艺术和其他艺术一样，有它的历史和原则——否则，就不能ม成其为艺术。本书不是cookbook，不会列出一串问题然后给出“处方”。本书的目标重在帮助开者和他们的经理提出犀利的问题。阅读和理解了本书之ใ后，你并不是永不再写出丑陋缓慢的查询了——有时这是必须ี的——但希望你是故意而为之、且有充足的理由á。目标读者本书的目标读者是：有丰富经验的sql数据库开者他们的经理数据库占重要地位的系统的软件架构师我希望一些dbຘa、尤其是数据库应用维护人员也能喜欢本书。不过，他们不是本书的主ว要目标读者。本书假定
----------ๅ-----ๅ-ๅ-------page3---ๅ----ๅ-ๅ-------ๅ--------ๅ
本书假定你已精通sql语言。这里所说的“精通”不是指在你大学里学了sql10่1并拿来aທ+的成绩，当然也并非指你是国际公认的sql专家，而是指你必须具有使用sql开数据库应用的经验、必须考虑索引、必须不把5000行的表当大表。本书的目标不是讲解连接、外连接、索引的基础知识，阅读本书过程中，如果你觉得某个ฐsql结构还显神秘,并影响了整段代码的理解，可先阅读几本其他sql书。另外，我假定读者至少熟悉一种编程语言,并了解计算机程序设计的基本原则。性能ม已很差ๆ、用户已๐抱怨、你已在解决性能问题的前线，这就是本书的假定。本书内容我现sql和战争如此相像，以至于我几乎沿用了《孙子兵法》的大纲,并保持了大部分题目名称注2๐。本书分为ฦ1้2章，每一章包含许多原则ท或准则，并通过举ะ例的方式对原则ท进行解释说明，这些例子大多来自于实际案例。第1章，制定计划：为性能ม而设计讨论如何设计高性能ม数据库第2章，动战争：高效访问数据库解释如何进行程序设计才能高效访问数据库第3章，战术部署：建立索引揭示为何建立索ิ引，如何建立索ิ引第4章，机动灵活：思考sql语句解释如何设计sql语句第5章，了如指掌：理解物理实现揭示物理实现如何影响性能第6章，锦囊妙计：认识经典sql模式包括经典的sql模式、以及如何处理第7章，变换战术：处理层次结构说明如何处理层次数据第8章，孰优孰劣：认识困难，处理困难指出如何认识和处理比较棘手的情况第9章，多条战线：处理并讲解如何处理并第10章，集中兵力：应付大数据量讲解如何应付大数据量第11章，精于计谋：挽救响应时间分享一些技巧，以挽救设计糟糕的数据库的性能第12章，明察秋毫：监控性能收尾，解释如何定义和监控性能ม本书约定本书使用了如下印刷็惯例：等宽courier表示sql及编程语言的关键字，或表示table、索引或字段的名称，抑或表示函数、代码及命令
-----ๅ--------ๅ---ๅ------ๅ-paທge4-ๅ--ๅ-------ๅ-----------ๅ--ๅ
输出。等宽黑体courier表示ิ必须ี由用户逐字键入的命令等文本。此风格仅用于同时包含输入、输出的代码示ิ例。等宽斜ฒ体cນourier表示这些文本，应该被用户的值代替。总结：箴言，概括重要的sql原则。注意提示、建议、一般性注解。为相关主ว题有用的附加信息。代码示ิ例本书是为了帮助你完成工ื作的。总的来说，你可以将本书的代码用于你的程序和文档，但是，若要大规模复制代码，则必须联系o'reilly申ã请授权。例如：编程当中ณ用了本书的几段代码，无需授权；但出售或分o'reilly书籍中案例的cd-rom光盘，需要授权。再如：回答问题时，引用了本书或其中的代码示例，无需授权；但在你的产品文档中大量使用本书代码，需要授权。o'reilly公司感谢但不强制ๆ归属声明。归属声明通常包括书名、作者、出版商、isbn。例如“theartofsqlbystéphanefa肉lt9ithpeterrobsoncນopyrightc20่06o'ูreillymediaທ,ไ0-59๗6๔-00894-5”。如果你对代码示例的使用出了上述范围，请通过permissions@oreilly联系出版商。评论与提问我们已尽力核验本书所的信息，尽管如此，仍不能保证本书完全没有瑕疵，而网络世界ศ的变化之ใ快，也使得本书永不过时的保证成为不可能。如果读者现本书内容上的错误，不管是赘字、错字、语意不清，甚至是技术错误，我们都竭诚虚心接受读者指教。如果您有任何问题，请按照以下的联系方式与我们联系。o'reillymedia,in9high9๗aynorth色bastopol,ไ9theusor9ternationalorlocal707๕829-ๅ01้04๒fax致谢本书原版用英语写作，英语既不是我的家乡话，又不是我所在国家的语言，所以写这样一本书要求极度乐观回想起来几近疯狂。幸运的是，peterrobson不仅为ฦ本书贡献了他在sql和数据库设计方面的知识，也贡献了持续的热情来修改我冗长的句子、调整副词位置、斟酌替换词汇。peterrobຘson和我在好几个大会上都碰过面，我们都是演讲者。jonaທthaທngenni9athangennick是o'reilly出版的sqlpo9athan是个非常尊重作者的编辑。由于他的专业、他对细节的关注、他的犀利视角，使本书的质量大大提升。同时，jonathan也使本书的语言更具“中大西洋”风味peter和我现，虽然我们保证按美国英语拼写，但还远远不够。
--ๅ-ๅ-----ๅ----------ๅ---ๅ--page5--ๅ----ๅ--ๅ-----ๅ-ๅ----ๅ-----
我还要感谢很多人，他们来自三个不同的大陆，阅读了本书全部ຖ或部分草稿并坦诚地提出意见。他们是：philippebertolino、ra9il9๗s、timgorman、jeaທn-paulmartin、sanjaທymishra、anthony摸linaທro、tiongsoohua。我特别感激laທrry，因为本书的思想最初来自于我们的e-ๅmail讨论。我也要感谢o'reilly的许多人，他们使本书得以出版。他们是：mar9o、jamiepeppard、mikekohnke、ron逼lodeau、jessamynread、andre9๗savikaທs。感谢naທn9haທrdt卓越的手稿编辑工作。特别ี感谢艳n-ๅarzeldurelle-marc慷慨第12章用到的图片。感谢paulmcນ9horter授权我们将他的战争图用于第6章。最后，感谢rogermaທn色r和steelbusinessiefing的职员，他们为peter和我了位于伦敦的办公室还有大量咖啡。感谢qianlenaashley了本书开始引用的《孙子兵法》的中文原文。作者介绍stéphaທnefa肉lt从1้98๖3年开始接触关系数据库。oracle法国成立早期他即加入此前是短暂的ibm经历和渥太华大学任教生涯，并在不久之ใ后对性能和调优产生了兴趣。19๗88年他离开了oracle，此后一年间，他进行调整，并研究过运筹学。之后，他重操旧业，一直从事数据库咨询工作，并于199๗8年创น办了肉gh色a公司肉gh色a。stéphaທnefaທ肉lt出版了fortraທnstru9umériques一书法语，dunod出版社ุ，198๖6,与didiersi摸n合作，并在ora9e和色lect分别为英国和北美oracle用户组杂志以及oracle杂志在线版上表了许多文章。他还是美国、英国、挪威等众多用户组大会的演讲者。peterrobson毕业于达拉谟大学地质专业1้968年，然后在爱丁堡大学任教，并于1975๓年获得地质学研究型硕士学位。在希腊ຘ度过了一段地质学家生涯之后，他开始在纽卡斯尔大学专攻地质和医学数据库。他使用数据库始于1้9๗77年，1981年开始使用关系数据库，1985年开始使用oraທcle，这期间担任过开工程师、数据架构师、数据库管理员等角色。1้980่年，peter参加了英国地质普查，负责指导使用关系数据库管理系统。他擅长sql系统，以及从组织级到部门级的数据建模。peter多次出席英国、欧洲、北美的oracle数据库大会，在许多数据库专业杂志上表过文章。他现任英国oracle用户组委员会主任，可通过peterrobson@justsql联系他。查询的识别
----ๅ----ๅ----ๅ----ๅ-ๅ------ๅpage6----ๅ--ๅ-ๅ-ๅ----ๅ-ๅ----ๅ------
有经验的朋友都知道，把关键系统从开环境切换到生产环境是一场战役，一场甚嚣尘上的战役。通常，在“攻击起日d-day”的前几周，性能测试会显示新系统达不到เ预期要求。于是，找专家，调优sql语句，召集数据库管理员和系统管理员不断开会讨论对策。最后，性能ม总算与以前的系统大致相当了尽管新系统用的是价格翻倍的硬件。人们常常使用战术，而忽略了战略๓。战略要求从大局上把握整个架构与设计。和战争一样，战略的基本原则并不多，且经常被忽视。架构错误的代价非常高，sql程序员必须准备充分，明确目标，了解如何实现目标。在本章中ณ，我们讨论编写高效访问数据库的程序需要实现哪些关键目标。查询的识别queryidentifi9๗qquueerryyiiddeennttiiffiiaaທttiioonn数个世纪以来，将军通过辨别军装颜色和旗帜等来判断各部队的位置，以此检查激战中部队行进情况。同样，当一些进程消耗了过多的cpu资源时，通常也可以确定是由á哪些正被执行的sql语句造成的。但是，要确定是应用的哪部ຖ分提交了这些sql语句却困难得多，特别是复杂的大型系统包含动态建立的查询的时候。尽管许多产品良好的监控工具，但要确定一小段sql语句与整个ฐ系统的关系，有时却非常困难。因此，要养成为程序和关键模块加注释的习惯，在sql中ณ插入注释有助于辨别ี查询在程序中的位置。例如：9色lectblah这些注释在查错时非常有用。另外，注释也有助于判断单独应用对服务器造成的负载有多大；例如我们希望本地应用承担更多工作，需要判断ษ当前硬件是否能承受突高负载，这时注释特别有用。有些产品还了专门的记录功能registrationfaທcilities，将你从“为每个ฐ语句加注释”的乏味工作中解放出来。例如oraທ9_info包，它支持4๒8个字符的模块名称摸dulename、3๑2个字符的动作名称aທ9ame和64个字符的客户信息，这些字段的内容可由á我们定制。在oracle环境下，你可以利用这个ฐ程序包记录哪个应用正在执行，以及它在何时正在做什么。因为应用是通过“oraclev$动态视图”能ม显示目前内存中生的情况向程序包传递信息的，于是我们可以轻易地掌握这些信息。总结：易识别的语句有助于定位性能问题。保持数据库连接稳定stabledataba色9sssttaabbຘlleeddaaທttaທabbaas色eoonnnneettiioonnss建立一个新的数据库连接，既快又方便，但这其中ณ往往掩藏着重复建立数据库连接带来的巨大开销。所以，管理数据库连接必须ี非常小心。允许多重连接——可能ม就藏在你的应用中ณ——的后果可能很严å重，下面即是一例。
----ๅ---ๅ-----ๅ----ๅ----ๅ---paທge7๕----ๅ--ๅ-----------ๅ------
不久前，我遇到一个应用，要处理很多小的文本文件。这些文本文件最大的也不过一百行，每一行包含要加载的数据及数据库等信息。此例中ณ固然只有一个数据库实例，但即使有上百个，这里所说明的原理也是适用的。处理每个文件的代码如下：openthefileuntiltheendoffileisreachedreadaro99ecttothe色rverspe9色rtthedaທtadis9ectclo色thefile上述处理工作令人满意，但当大量小文件都在极短的时间内到达时，可能应用程序来不及处理，于是积压大量待处理文件，花费时间相当可观。我用c语言编了个简单的程序来模拟上述情况，以说明频繁的数据库连接和中断所造成的系统性能ม下降问题。表2-1列ต出了模拟的结果。注意产生表2-1结果的程序使用了常规的in色rt语句。顺便提一下，直接加载dire9g的技术会更快。表2-1้：连接／中断ษ性能测试结果测试结果依次对每一行作连接／中ณ断74行秒连接一次，所有行逐个ฐ插入1้681行秒连接一次，以10行为一数组插入5๓914๒行秒连接一次，以1้00่行为一数组插入919๗0行秒
----ๅ---ๅ---ๅ---ๅ--ๅ----ๅ---ๅ-page8---ๅ----ๅ-----ๅ-------ๅ----ๅ
此例说明了尽量减少分别连接数据库次数的重要性。对比表中前后两ä次针ฤ对相同数据库的插入操作，明显现性能有显着提升。其实还可以做进一步的优化。因为ฦ数据库实例的数量势必有限，所以可以建立一组处理程序handler分别负责一个数据库连接，每个数据库只连接一次，使性能进一步提高。正如表2-1้所示，仅连接数据库一次或很少次的简单技巧ู，再加上一点额外工作，就能让效率提升200倍以上。当然，在上述改进的基础上，再将欲更新า的数据填入数组，这样就尽可能减少了程序和数据库核心间的交互次数，从而使性能产生了另一次飞跃。这种每次插入几行数据的做法，可以使数据的总处理能力又增加了5倍。表2-1中ณ的结果显示改进后的性能几乎是最初ม的1200่倍。为何有如此大的性能ม提升？第一个原因，也是最大的原因，在于数据库连接是很“重”的操作，消耗资源很多。在常见的客户服务器模式中现在仍广为使用，简单的连接操作背后潜藏着如下事实：先，客户端与远程服务器的监听程序listenerprogram建立联系；接着，监听程序要么创建一个进程或线程来执行数据库核心程序，要么เ直接或间接地把客户请求传递给已存在的服务器进程，这取决于此服务器是否为ฦ共享服务器。除了这些系统操作创น建进程或线程并开始执行之外，数据库系统还必须为每次色ssion建立新环境，以跟踪它的行为ฦ。建立新色ssion前，dbms还要检查密码是否与保存的加密的账户密码相符。或许，dbຘms还要执行登录触器logontrigger，还要初始化存储过程和程序包如果它们是第一次被调用。上面这些还不包括客户端进程和服务器进程之间要完成的握手协议。正因为ฦ如此，连接池9g等保持永久数据库连接的技术对性能才如此重要。第二个原因，你的程序甚至包括存储过程和数据库之间的交互也有开销。即使数据库连结已๐经建立且仍未中断ษ，程序和dbms核心之间的上下文切换9text9๗itcນh也有代价。因此，如果dbms支持数据通过数组传递，应毫不犹豫地使用它。如果该数组接口是隐式的aທpi内部使用，但你不能使用，那么明智的做法是检查它的默认大小并根据具体需要修改它。当然，任何逐行处理的方式都面临上下文切换的问题，并对性能ม产生严重影响——本章后面还会多次涉及此问题。总结：数据库连接和交互好似万里长城——长度越长，传递消息越耗时。战略๓优先于战术strategybeforetacticsssttrraatteeggyybຘbeeffoorreettaattiiss战略决定战术，反之则谬也。思考如何处理数据时，有经验的开者不会着眼于细微步骤，而是着眼于最终结果。要获得想要的结果，最显而易见的方法是按照业务规则规定的顺序按部就班地处理，但这不是最有效的方แ法——接下来的例子将显示，刻意关注业务处理流程可能ม会使
--ๅ--ๅ----ๅ---ๅ---------ๅ---page9๗-ๅ--------ๅ-------ๅ--ๅ-ๅ-ๅ---
我们错失最有效的解决方案。几年前，有人给了我一个存储过程，让我“尝试”着进行一下优化。为什么说是“尝试”呢？因为该存储过程已经被优化两次了，一次是由原开者，另一次是由一个自称oracນle专家的人。但尽管如此，这个存储过程的执行仍会花上20分钟，使用者无法接受。此存储过程的目的，是根据现有库存和各地订单，计算出总厂需要订购的原料数量。大体上，它就是把不同数据源的几个ฐ相同的表聚合aggregate到เ一个ฐ主表mastertaທble中ณ。先，将每个数据源的数据插入主表；接着，对主ว表中的各项数据进行合计并更新；最后，将与合计结果无关的数据从表中删除。针ฤ对每个数据源，重复执行上述步骤。所有sql语句都不是特别复杂，也没有哪个ฐ单独的sql语句特别ี低效。为了理解这个ฐ存储过程，我花了大半天时间，终于现了问题：为什么该过程要用这么เ多步骤呢？在from子句中加上包含union的子查询，就能得到所有数据源的聚合aggregation。一条色lect语句，只需一步就得到了结果集，而之前要通过插入目标表targettable得到结果集。优化后，性能的提升非常惊人——从20分钟็减至2๐0秒；当然，之ใ后我花了一些时间验证了结果集，与未优化前完全相同。想要获得上述的大幅提高性能，无需特别ี技能，仅要求站在局外思考thinkoutsidethebox的能力。之ใ前两ä次优化因“太关注问题本身”而收到了干扰。我们需要大胆的思维，站得远一些，试着从大局的角度看待问题。要问自己一些关键的问题：写存储过程之前，我们已有哪些数据？我们希望存储过程返回什么เ结果？再辅以大胆的思维，思考这些问题的答案，就能得到一个ฐ性能大幅提升的处理方式了。总结：考虑解决方案的细节之ใ前，先站得远一些，把握大局。先定义问题，再解决问题probຘlemdefinitionbeforesolutionpprroobຘblleemmddeeffiinniittiioonnbbeeffoorreessoolluuttiioonn一知半解是危险的。人们常在听说了新า技术或特殊技术之后——有时的确很吸引人——试图采用它作为新的解决方แ案。普通开者和设计师通常会立即采纳这些新“解决方แ案”，直到后来才现它们会产生许多后续问题。现成的解决方案中，非规范化设计引人注目。设计伊始，非规范化设计的拥护者就提出此方แ案，为了寻求“性能”而无视最终将会面临的升级恶魔——而事实上，在开周期早ຉ期，改进设计或学习如何使用join也是一个不错的选择。作为非规范化设计的一种手段，物化视图materiaທlizedvie9常被认为是灵丹ล妙药。物化视图有时被称为快照snapshot，这个更加平常的词更形象地反映了可悲的事实：物化视图是某时间点的数据副本。在没有其他办法时，这个理论上遭到质疑ທ的技术也未尝不值得一试，借用卡夫卡franzkaທfka的一句名言：“逻辑诚可贵，生存价更高。”然而，绝大部分问题都可借助传统技术巧妙解决。先，应学会充分利用简单、传统的技术。只有完全掌握了这些技术，才能ม正确评价它们的局限性，最终现它相当于新技术的潜在优势如果有的话。所有技术方案，都只是我们达到目标的手段。没有经验的开者误把新技术本身当成了目标。
---ๅ---------ๅ---ๅ------ๅ--page1้0-ๅ------ๅ------------ๅ--ๅ--
对于热衷于技术、过于看重技术的人来说，此问题就更为严å重。总结：先打基础，再赶时髦：摆弄新工具之前，先把手艺学好。直接操作实际数据operationsaທgainstaທctuaທldataooppeerraattiioonnssaaggaaiinnssttaທattuuaallddaທattaa许多开者喜欢建立临时工作表temporary9๗orktable，把后续处理使用的大量数据放入其中ณ，然后开始“正式”工ื作。这种方法广受质疑，反映了“跳出业务流程细节考虑问题”的能力不足。记住，永久表permaທnenttable可以设置非常复杂的存储选项ำ在第5๓章将讨论一些存储选项的设置，而临时表不能。临时表的索引如果有的话可能不是最优的，因此，查询临时表的语句效率比永久表的差ๆ。另外，查询之前必然先为ฦ临时表填入数据，这自然也多了一笔额外的开销。就算使用临时表有充足理由，若数据量大，也绝不能把永久ื表当作临时工作表来用。问题之一在于统计信息的自动收集：若没有实时收集要求，dbms通常会在不活动或活动少时进行统计信息收集，而这时作为临时工作表可能为空，从而使优化器收到了完全错误的信息。这些不正确且有偏差的统计信息可能造成执行计划exe9๗完全不合理，导致性能下降。所以，如果一定要用临时表，应确保数据库知道哪些表是临时的。总结：暂时工ื作表意味着以不太合理的方แ式存储更多信息。sql用ssqqll处理集合色tpro9sqls色ettpprrooeessssiinnggiinnssqqllsql完全基于集合色t来处理数据。对大部ຖ分更新或删除操作而言——如果不是针对整个表的话——你必须先精确定义แ出要处理的记录的集合。这定义了该处理的粒度granularity，可能ม是对大量记录的粗粒度操作，有可能ม是只影响少数记录的细粒度操作。将一次“大批量数据的处理”分割成多次“小块处理”是个坏主意，除非对数据库的修改太昂贵，否则不要使用，因为这种方法极其低效：1占用过多的空间保存原始数据，以备事务transa9๗回滚rollback之ใ需；2万一修改失败，回滚消耗过长的实践。许多人认为，进行大规模修改操作时，应在操作数据的代码中有规律地多安排些mit命令。其实，严格从实践角度来讲，“从头开始重做”比“确定失败生的时间和位置，接着已๐提交部分重做”要容易得多、简单得多、也快得多。处理数据时，应适应数据库的物理实现。考虑事务失败时回滚所需日志的大小，如果要为undo保存的数据量确实巨大，或许应该考虑数据修改的频率问题。也就是说，将大规模的“每月更新”，改为ฦ规模不大的“每周更新”，甚至改为规模更小的“每日更新”，或许是个有效方案。总结：几千个语句，借助游标cursor不断循环，很慢。换成几个语句，处理同样的数据，还是较慢。换成一个语句，解决上述问题，最好。
--------ๅ-----ๅ----ๅ---ๅ---page11้--ๅ----ๅ--ๅ----ๅ---ๅ-----ๅ---
sql动作丰富的ssqqll语句aທ9-pa9tsaattiioonn--ppaທakkeeddssqqllssttaatteemmeennttsssql不是过程性语言pro9guaທge，尽管也可以将过程逻辑procedurallogic用于sql，但必须小心。混淆声明性处理de9๗g和过程逻辑，最常见的例子出现在需要从数据库中提取数据、然后处理数据、然后再插入到数据库时。在一个程序或程序中的一个ฐ函数接收到特定输入值后，如下情况太常见了：用输入值从数据库中ณ检索到一个ฐ或多个另外的数据值，然后，借助循环或条件逻辑通常是ifthenel色将一些语句组织起来，对数据库进行操作。大多数情况下，造成上述错误做法的原因有三：根深蒂固的坏习惯、sql知识的缺乏、盲从功能需求规格说明。其实，许多复杂操作往往可由一条sql语句完成。因此，如果用户了一些数据值，尽量不要将操作分解为多条提取中间结果的语句。避免在sql中引入“过程逻辑procedurallogic”的主要原因有二。数据库访问，总会跨多个软件层，甚至包括网络访问。即使没有网络访问，也会涉及进程间通讯；额外的存取访问意味着更多的函数调用、更大的带宽，以及更长的等待时间。一旦这些调用要重复多次，其对性能的影响就非常可观了。在sql中引入过程逻辑，意味着性能和维护问题应由你的程序承担。大多数据库系统都了成熟ງ的算法，来处理join等操作，来优化查询以获得更高的效率。基于开销的优化器cost-bຘa色doptimizer，cbຘo是很复杂的软件，它早已不像刚推出时那样没什么用了，而在大部ຖ分情况下都是非常出色的成熟产品了，优秀的cbo查询优化的效率极高。然而，cbຘo所能改变的只有sql语句。如果在一条单独的sql语句中ณ完成尽可能多的操作，那么เ性能优化可以还由dbms核心负责，你的程序可以充分利用dbms的所有升级。也就是说，未来大部分维护工作从程序间接转移给了dbms供货商。当然，“避免在sql中引入过程逻辑”规则ท也有例外。有时过程逻辑确实能ม加快处理度，庞大的sql语句未必总是高效。然而，过程逻辑及其之后的处理相同数据的语句，可以编写到เ一个单独的sql语句中，cbo就是这么做的，从而获得最高效的执行方式。总结：尽可能多地把事情交给数据库优化器来处理。充分利用每次数据库访问profitabledataba色aທes色spprrooffiittaທaທbຘblleeddaທattaabbaas色eaaeesss色ess如果计划逛好几家商店，你会先决定在每家店买哪些东西。从这一刻起，就要计划按何种顺序购物才能少走冤枉路。每逛一家店，计划东西购买完毕，才逛下一家。这是常识，但其中蕴含的道理许多数据库应用却不懂得。要从一个表中提取多段信息时，采用多次数据库访问的做法非常糟糕，即使多段信息看似“无关”但事实上往往并非如此。例如，如果需要多个ฐ字段的数据，千万不要逐个ฐ字段地提取，而应
---ๅ--ๅ----------ๅ--------ๅpaທge12๐------ๅ----ๅ--ๅ-------ๅ----
一次操作全部ຖ完成。很不幸，面向对象oo的最佳实践提倡为ฦ每个ฐ属性定义一个get方法。不要把oo方法与关系数据库处理混为一谈。混淆关系和面向对象的概念，以及将表等同于类、字段等同于属性，都是致命的错误。总结：在合理范围内，利ำ用每次数据库访问完成尽量多的工作。dbms接近ddbbຘmmss核心9ellloos色enneessssttootthheeddbbmmskeerrnneell代码的执行越接近dbms核心，则执行度越快。数据库真正强大之处就在于此，例如，有些数据库管理产品支持扩展，你可以用c等较底层的语言为它编写新功能。用含有指针操作的底层语言有个缺点，即一旦指针处理出错会影响内存。仅影响到一个用户已很糟糕，何况数据库服务器就像“服务器”名字所指的一样出了问题会影响众多“用户”——服务器内存出了问题，所有使用这些数据的无辜的应用程序都会受影响。因此，dbms核心采取了负责任的做法，在沙箱sandbox环境中执行程序代码，这样，即使出了问题也不会影响到数据。例如，ora99๗和它自身之间实现了一套复杂的通信机制，此机制在某些方แ面很像控制数据库连结的方แ法，以管理两ä个或多个ฐ服务器上的数据库实例之ใ间的通信。到底采用plsql存储过程还是外部c函数，应综合比较后决定。如果精心编写外部c函数获得的好处过了建立外部ຖ环境和上下文切换9๗g的成本，就应采用外部函数。但需要处理一个ฐ大数据量的表的每一行时，不要使用外部ຖ函数。这需要平衡考虑，解决问题时应完全了解备选策略的后果。如要使用函数，始终应选dbms自带的函数。这不仅仅是为了避免无谓的重复劳动，还因为自带函数在执行时比任何第三方开的代码更接近数据库核心，相应地其效率也会高出许多。下面这个简单例子是用oracນlesql编写的，显示了使用oracle函数所获得的效率。假设手工输入的文本数据可能包含多个ฐ相邻的“空格”，我们需要一个函数将多个ฐ空格替换为一个ฐ空格。如果不采用oraທcນledaທtaທbaທ色10g开始的正规表达式regularexpression，函数代码将会是这样：99gi女ar9๗var9๗gvar9number:๘=lengthp_string;i逼nary_integer:=1้;j逼nary_integer;begin9hilei0loopv_string:=substrv_string,1,ไi||ltrimsubstrv_string,i+1;๙i:๘=instrv_string,ไ'';endloop;retur女_ຕstring;end;
----ๅ--------ๅ-----ๅ----ๅ--page14--ๅ--ๅ-ๅ-----ๅ--ๅ----ๅ---ๅ----
还有第三种方法：99๗gi女ar9varcນhar2isv_stringvar9g;len1number;๙len2๐numbຘer;bຘeginlen1:=lengthp_string;v_ຕstring:=replaທ9g,'',ไ''ู;len2:=lengthv_string;9hilelen2色lectsqueeze1้'azerythgfrdtr'2๐fromdual3azerythgfrdtrelap色d:00:00:0่000่sql色lectsqueeze2'ูazerythgfrdtr'ู2fromdual3๑azerythgfrdtrelaທp色d:00่:0่0:00่01sql色lectsqueeze3'azerythgfrdtr'ู2fromdual3azerythgfrdtrelap色d:0่0:๘00:000่0
---------ๅ--ๅ-------ๅ-----paທge15๓-ๅ-----------ๅ----ๅ-ๅ------
那么，如果每天要调用该空格替换操作几千次呢？我们构造一个接近现实负载的环境，下面的代码将建立一个ฐ用于测试的表并填入随机数据，已๐检测上面三个函数是否有性能差异：createtaທblesqueezablerandom_ຕtextvarchar2๐50de9ary_integer;j逼nary_ຕinteger;k逼nary_integer;v_stringvare1้,100่;v_ຕstring:=dbຘms_randomstring'ูu',5๓0;9๗hilej9๗hen9๗stillsomethingel色el色
-----------ๅ-----ๅ-----ๅ--page1้9---ๅ----ๅ-ๅ-------ๅ--------ๅ
end数值或日期的比较则简单明了。操作字符串ธ可以用oracle的greatest或least，或者mysql的str9色rt语句增加过程逻辑，具体办法是多重in色rt及条件in色rt注3，并借助merge语句。如果dbms了这样语句，毫不犹豫地使用它。也就是说，有许多逻辑可以放入sql语句中；虽然仅执行多条语句中的一条这种逻辑价值不大，但如果设法利用caທ色、merge或类似功能ม将多条语句合并成一条，价值可就大了。总结：只要有可能，应尽量把条件逻辑放到sql语句中，而不是sql的宿主ว语言中。一次完成多个ฐ更新multipleupdatesatoncemmuullttiipplleeuuppddaatteessaattoonnee我的基本主张是：如果每次更新的是彼此无关的记录，对一张表连续进行多次update操作还可以接受；否则，就应该把它们合并成一个ฐupdaທte操作。例如，下面是来自实际应用的一些代码注4๒：updatetbຘo_i女oice_extracນtor色tpgaທ_ຕstaທtus=0่pdatetbo_i女oice_ຕextractor色trd_status=09hererd_staທtusin1,3andi女_type=0;๙两个连续的更新是对同一个表进行的。但它们是否将访问相同的记录呢？不得而知。问题是，搜索条件的效率有多高？任何名为ฦtype或status的字段，其值的分布通常是杂乱ກ无章的，所以上面两个updaທte语句极可能对同一个表连续进行两次完整扫描：一个ฐupdate有效地利用了索ิ引，而第二个update不可避免地进行全表扫描；或者，幸运的话，两ä次update都有效地利用了索引。无论如何，把这两个update合并到เ一起，几乎不会有损失，只会有好处：updatetbo_ຕi女oice_extraທctor色tpgaທ_staທtus=9๗1then09๗hen3then0el色pga_ຕstatus
--------ๅ---ๅ---ๅ------ๅ---page20--ๅ-ๅ----ๅ-----ๅ---ๅ----ๅ--ๅ--
end,rd_staທtus=91then09hen3then0el色rd_statusendsin1้,3๑andi女_type=0;上例中ณ，可能出现重复更新相同字段为相同内容的情况，这的确增加了一小点儿开销。但在多数情况下，一个ฐupdate会比多个update快得多。注意上例中的“逻辑logic”，我们通过ca色语句实现了隐式的条件逻辑impli9๗allogic，来处理那ว些符合更新条件的数据记录，并且更新า条件可以有多条。总结：有可能的话，用一个语句处理多个更新；尽量减少对同一个表的重复访问。慎用自定义แ函数carefulu色ofu色r-9๗rittenfun9s将自定义แ函数u色r-9๗rittenfun9嵌到sql语句后，它可能ม被调用相当多次。如果在色lect语句的选出项列表中使用自定义函数，则每返回一行数据就会调用一次该函数。如果自定义函数出现在9here子句中ณ，则每一行数据要成功通过过滤条件都会调用一次该函数；如果此时其他过滤条件的筛选能力不够强，自定义函数被调用的次数就非常可观了。如果自定义函数内部ຖ还要执行一个查询，会生什么情况呢？每次函数调用都将执行此内部查询。实际上，这和关联子查询cນorrelaທtedsubquery效果相同，只不过自定义函数的方式阻碍了基于开销的优化器cost-ๅba色doptimizer，cbo对整个查询的优化效果，因为“子查询”隐藏在函数中ณ，数据库优化器鞭长莫及。下面举例说明将sql语句隐藏在自定义函数中的危险性。表flights描述商务航班，有航班号、起飞时间、到เ达时间及机场iata代码注5等字段。iaທta代码均为ฦ三个字母，有9000่多个ฐ，它们的解释保存在参照ั表中，包含城市名称若一个城市有多个机场则应为ฦ机场名称、国家名称等。显然，显示航班信息时，应该包含目的城市的机场名称，而不是简单的iaທta代码。在此就遇到เ了现代软件工程中的矛盾之ใ一。被认为是“优良传统”的模块化编程一般情况下非常适用，但对数据库编程而言，代码是开者和数据库引擎的共享活动sharedactivity，模块化要求并不明确。例如，我们可以遵循模块化原则编写一个小函数来查找iaທta代码，并返回完整的机场名称：99๗airport_9๗9vaທrchaທr2๐is
--ๅ----ๅ--------ๅ--------ๅ-page21้--ๅ-------ๅ---ๅ---------ๅ--
9amevar9๗amefromiata_aທirport_codes9herecode=iata_ຕ9ame;๙end;๙对于不熟悉oracle语法的读者，在此做个说明，以下查询中ณtruncsysdate的返回值为“今天的00:00aທm”，日期计算以天为单位；所以起飞时间的条件是指今天8๖:3๑0am至4:0่0pm之间。调用aທirport_city函数的查询可以非常简单，例如：色le9umber,to_ຕchardeparture_time,'ูhh24๒:mi'departure,airport_cityaທrrival"to"fromflights9heredeparture_ຕtimebet9eentrunre_ຕtime这个ฐ查询的执行度令人满意；在我机器上的随机样本中，返回77๕行数据只用了018๖秒多次执行的平均值，用户对这样的度肯定满意统计数据表明，此处理访问了30่3个ฐ数据块，53๑个ฐ是从磁盘读出的——而且每行数据有个递归调用。我们还可以用join来重写这段代码，作为查找函数的替代方案，当然它看起来会稍微复杂些：色le9umbຘer,ไto_ຕcharfdeparture_time,'hh24:mi'depaທrture,acity"ิto"fromflightsf,iata_airport_codesa9herea9ddeparture_timebet9eentrunre_time
---ๅ-ๅ-----------ๅ----ๅ----ๅpage2๐2------ๅ----ๅ-ๅ--------ๅ----
这个查询只用了005๓秒统计数据同前，但没有递归调用。对于执行时间不到02秒的查询来说，度快了3倍似乎无关紧ู要，但在大型系统中，这些查询每天经常执行数十万次——假设以上查询每天只执行五万次，于是查询的总耗时为25๓小时。若不使用上述查找函数lookupfun9则只需要不到42分钟，度提高过300%，这对大数据量的系统意义แ重大，最终带来经济上的节约。通常，使用查找函数会使批处理程序的性能极差ๆ。而且查询时间的增加，会使同一台机器支持的并用户数减少，我们将在第9章对此展开讨论。总结：优化器对自定义函数的代码无能ม为力。sql简洁的ssqqllsuinctsql熟练的开者使用尽可能少的sql语句完成尽可能多的事情。相反，拙劣的开者则ท倾向于严å格遵循已๐制ๆ订好的各功能步骤，下面是个真实的例子：--getthestartoftheaountingperiod色le9๗todtperstafromtperrslt9herefiscal_year=to_charparam_dta,ไ'ูyyyy'andrslt_period='1้'ู||to_chaທrparam_dta,'mm';--gettheendoftheperiodoutofcນlosure色le9todtperclosurefromtperrslt9herefiscal_ຕyear=to_cນharparam_dta,ไ'yyyy'aທndrslt_ຕperiod='ู9'||to_charparam_dtaທ,'mm';就算度可以接受，这也是段极糟的代码。很不幸，性能专家经常遇到这种糟糕的代码。既然两个ฐ值来自于同一表，为什么要分别用两个不同的语句呢？下面用oraທcle的bulkcollecນt子句，一次性将两个值放到数组中，这很容易实现，关键在于对rslt_period进行orderby操作，如下所示ิ：色lectclosure_datebulk9todtperstaທarrayfromtperrslt9๗herefiscal_year=to_charparaທm_dta,ไ'yyyy'ูandrslt_periodin'1้'||to_ຕcharpaທram_dta,'mm'ู,
-----ๅ----ๅ---ๅ--ๅ-------ๅ--paທge23----ๅ----ๅ----ๅ-ๅ-----ๅ-----
'9'ู||to_charparam_dta,'ูmm'orderbyrslt_period;于是，这两个ฐ日期被分别保存在数组的第一个和第二个ฐ位置。其中，bulkcollect是plsql语言特有的，但任何支持显式或隐式数组提取的语言都可如法炮制。其实甚至数组都是不必要的，用以下的小技巧注6，这两ä个值就可以被提取到两个变量中ณ：色lectmaxdecodesubຘstrrslt_period,1้,ไ1,--checນkthefirstcharacter'1้',closure_daທte,-ๅ-ifit's'1'returnthedate9e9aທntto_date'14๒1010่66๔','ddmmyyyy',--other9i色somethingoldmaxdecodesubstrrslt_period,ไ1้,1,'9',cນlosure_ຕdate,--thedaທte9e9antto_date'14101066'ู,'ddmmyyyy'ู,intodtpersta,dtperclosurefromtperrslt9herefiscaທl_ຕyear=to_charpaທraທm_dta,'yyyy'andrslt_periodin'1้'||to_charparaທm_dta,'mm'ู,'9'||to_chaທrparam_dta,ไ'ูmm';在这个例子中，预ไ期返回值为两ä行数据，所以问题是：如何把原本属于一个字段的两行数据，以一行数据两ä个字段的方式检索出来正如数组提取的例子一样。为此，我们检查rslt_period字段，两行数据的rslt_period字段有不同值；如果找到เ需要的记录，就返回要找的日期；否则ท，就返回一个ฐ在任何情况下都远比我们所需日期要早的日期此处选了哈斯丁之役battleofhastings的日期。只要每次取出最大值，就可以确保获得需要的日期。这是个ฐ非常实用的技巧，也可以应用在字符或数值数据，第1้1้章会有更详细的说明。总结：sql是声明性语言de9guage，所以设法使你的代码越业务过程的规格说明。sqlssqqll的进攻式编程offensive9g9ithsql一般的建议是进行防御式编程9sively，在开始处理之前先检查所有参数的合法性。但实际上，对数据库编程而言，尽量同时做几件事情的进攻式编程有切实的优势。有个很好的例子：进行一连串ธ检查，每当其中一个检查所要求的条件不符时就产生异常。信用卡付款的处理中就涉及类似步骤。例如，检查所提交的客户身份和卡号是否有效，以及两ä者是否匹配；检查信用卡是否过期；最后，检查当前的支付额是否过了信用额度。如果通过了所
----------ๅ--ๅ-ๅ--------ๅ--page2๐4---ๅ-ๅ----------ๅ-----ๅ----
有检查，支付操作才继续进行。为了完成上述功能ม，不熟练的开者会写出下列ต语句，并检查其返回结果：色le9๗tfromcustomers9herecustomer_ຕid=provided_ຕid接