━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
sqlssqqll语言艺术内容介绍本书分为12章，每一章包含许多原则或准则，并通过举例的方式对原则ท进行解释说明。这些例子大多来自于实际案例，对九种sql经典查询场景以及其性能影响讨论，非常便于实践，为你的实际工作提出了具体建议。本书适合sql数据库开者、软件架构师，也适合dbaທ，尤其是数据库应用维护人员阅读。资深sql专家stéphanefa肉lt倾力打造《软件架构设计》作者温昱最新译作巧妙借鉴《孙子兵法》的智慧结晶传授25年的sql性能ม与调校经验深入探讨九种常见查询方案及其性能ม前言过去，“信息技术it”的名字还不如今天这般耀眼，被称为“电子数据处理”。其实，尽管当今新潮技术层出不穷，数据处理依然处于我们系统的核心地位，而且需管理的数据量的增长度似乎ๆ比处理器的增长度还快。今天，最重要的集团数据都被保存在数据库中，通过sql语言来访问。sql语言虽有缺点，但非常流行，它从19๗80่年代早ຉ期开始被广泛接受，随后就所向无敌了。如今，年轻开者在接受面试时，没有谁不宣称自己้能熟ງ练应用sql的。sql作为ฦ数据库访问语言，已๐成为ฦ任何基础it课程的必备部分。开者宣传自己熟练掌握sql，其实前提是“熟练掌握”的定义是“能够获得功能上正确的结果”。然而，全世界ศ的企业如今都面临ภ数据量的爆炸式增长，所以仅做到“功能正确”是不够的，还必须足够快，所以数据库性能成了许多公司头疼的问题。有趣的是，尽管每个ฐ人都认可性能ม问题源自代码，但普遍接受的事实则是开者的要关注点应该是功能ม正确。人们认为：为了便于维护，代码中ณ的数据库访问部分应该尽量简单；“拙劣的sql”应该交给资深的dba去摆弄，他们还会调整几个“有魔力”的数据库参数，于是度就快了——如果数据库还不够快，似乎就该升级硬件了。往往就是这样，那些所谓的“常识”和“可靠方法”最终却是极端有害的。先写低效的代码、后由专家调优，这种做法实际上是自找麻烦。本书认为，先要关注性能的就是开者，而且sql问题绝不仅仅只包含正确编写几个查询这么简单。开者角度看到的性能ม问题和dba从调优角度看到的大相径庭。对dba而言，他尽量从现有的硬件如处理器和存储子系统和特定版本的dbms获得最高性能，他可能ม有些sql技能ม并能调优一个性能极差的sql语句。但对开者而言，他编写的代码可能要运行5到10่年，这些代码将经历一代代的硬件，以及dbຘms各种重要版
-ๅ---ๅ----ๅ-----ๅ--ๅ-ๅ------ๅ-page2---ๅ---ๅ--ๅ-ๅ--ๅ-ๅ---------ๅ--ๅ
本升级例如支持互联网访问、支持网格，不一而足。所以，代码必须从一开始就快、健全。很多开者仅仅是“知道”sql而已，他们没有深刻理解sql及关系理论，实在令人遗憾ย。为ฦ何写作本书sql书主要分为ฦ三种类型：讲授具体sql方แ言的逻辑和语法的书、讲授高级技术及解决问题方法的书、专家与资深dba所需的性能和调优的书。一方面，书籍要讲述如何写sql代码；另一方面，要讲如何诊断和修改拙劣的sql代码。在本书中，我不再为ฦ新手从头讲解如何写出优秀的sql代码，而是以越单个sql语句的方式看待sql代码，无疑这更加重要。教授语言使用就够难了，那么本书是怎样讲述如何高效使用sql语言的呢？sql的简单性具有欺骗性，它能支持的情况组合的数目几乎是无限的。最初ม，我觉得sql和国际象棋很相似，后来，我悟到明国际象棋是为了教授战争之道。于是，每当出现sql性能难题的时候，我都自然而然地将之视为要和一行行数据组成的军队作战。最终，我找到เ了向开者传授如何有效使用数据库的方แ法，这就像教军官如何指挥战争。知识、技能、天赋缺一不可。天赋不能传授，只能ม培养。从写就了《孙子兵法》的孙子到如今的将军，绝大多数战略家都相信这一点，于是他们尽量以简单的格言或规则的方式表达沙场经验，并希望这样能指导真实的战争。我将这种方法用于战争之ใ外的许多领域，本书借鉴了孙子兵法的方แ法和书的题目。许多知名it专家冠以科学家称号，而我认为ฦ“艺术”比“科学”更能反映it活动所需的才能、经验和创造力注1。很可能ม是由于我偏爱“艺术”的原因，“科学”派并不赞成我的观点，他们声称每个ฐsql问题都可通过严格分析和参考丰富的经验数据来解决。然而，我不认为这两ä种观点有什么เ不一致。明确的科学方法有助于摆脱单个具体问题的限制，毕竟，sql开必须考虑数据的变化，其中有很大的不确定性。某些表的数据量出乎意料地增长将会如何？同时，用户数量也倍增了，又将会如何？希望数据在线保存好几年将会如何？如此一来，运行在硬件之上的这些程序的行为是否会完全不同？架构级的选择是在赌未来，当然需要明确可靠的理论知识——但这是进一步运用艺术的先决条件。第一次世界ศ大战联军总司令ferdinandfo9checolesupérieuredeguerre的一次演讲中说：战争的艺术和其他艺术一样，有它的历史和原则——否则，就不能ม成其为ฦ艺术。本书不是cookbook，不会列出一串问题然后给出“处方”。本书的目标重在帮助开者和他们的经理提出犀利的问题。阅读和理解了本书之后，你并不是永不再写出丑陋缓慢的查询了——有时这是必须的——但希望你是故意而为之ใ、且有充足的理由。目标读者本书的目标读者是：有丰ถ富经验的sql数据库开者他们的经理数据库占重要地位的系统的软件架构师我希望一些dba、尤其是数据库应用维护人员也能喜欢本书。不过，他们不是本书的主要目标读者。本书假定
-ๅ--ๅ----ๅ--ๅ--ๅ-ๅ---ๅ------ๅ--page3๑-----ๅ--ๅ-ๅ------ๅ---------ๅ
本书假定你已๐精通sql语言。这里所说的“精通”不是指在你大学里学了sql10่1并拿来a+的成绩，当然也并非指你是国际公认的sql专家，而是指你必须ี具有使用sql开数据库应用的经验、必须考虑索引、必须不把5๓0่00行的表当大表。本书的目标不是讲解连接、外连接、索引的基础知识，阅读本书过程中，如果你觉得某个ฐsql结构还显神秘,并影响了整段代码的理解，可先阅读几本其他sql书。另外，我假定读者至少熟悉一种编程语言,ไ并了解计算机程序设计的基本原则。性能ม已很差、用户已抱怨、你已在解决性能ม问题的前线，这就是本书的假定。本书内容我现sql和战争如此相像，以至于我几乎ๆ沿用了《孙子兵法》的大纲,并保持了大部分题目名称注2๐。本书分为1้2章，每一章包含许多原则ท或准则，并通过举例的方式对原则ท进行解释说明，这些例子大多来自于实际案例。第1้章，制定计划：为ฦ性能ม而设计讨论如何设计高性能数据库第2章，动战争：高效访问数据库解释如何进行程序设计才能ม高效访问数据库第3๑章，战术部ຖ署：建立索引揭示为何建立索ิ引，如何建立索引第4章，机动灵活：思考sql语句解释如何设计sql语句第5章，了如指掌：理解物理实现揭示物理实现如何影响性能第6章，锦囊妙计：认识经典sql模式包括经典的sql模式、以及如何处理第7๕章，变换战术：处理层次结构说明如何处理层次数据第8๖章，孰优孰劣：认识困难，处理困难指出如何认识和处理比较棘手的情况第9章，多条战线：处理并讲解如何处理并第10章，集中兵力：应付大数据量讲解如何应付大数据量第11章，精于计谋：挽救响应时间分享一些技巧，以挽救设计糟糕的数据库的性能第1้2章，明察秋毫：监控性能ม收尾，解释如何定义แ和监控性能本书约定本书使用了如下印刷็惯例：等宽cນourier表示ิsql及编程语言的关键字，或表示ิtaທbຘle、索引或字段的名称，抑或表示函数、代码及命令
----ๅ-------ๅ--ๅ----ๅ--ๅ--ๅ-ๅ-page4---ๅ----------ๅ--ๅ--ๅ----ๅ--ๅ
输出。等宽黑体cນourier表示ิ必须由用户逐字键入的命令等文本。此风格仅用于同时包含输入、输出的代码示例。等宽斜体cນourier表示这些文本，应该被用户的值代替。总结：箴言，概ฐ括重要的sql原则。注意提示、建议、一般性注解。为ฦ相关主题有用的附加信息。代码示ิ例本书是为了帮助你完成工ื作的。总的来说，你可以将本书的代码用于你的程序和文档，但是，若要大规模复制代码，则必须联系o'reilly申请授权。例如：编程当中用了本书的几段代码，无需授权；但出售或分o'ูreilly书籍中案例的cd-rom光盘，需要授权。再如：回答问题时，引用了本书或其中的代码示例，无需授权；但在你的产品文档中ณ大量使用本书代码，需要授权。o'reilly公司感谢但不强制ๆ归属声明。归属声明通常包括书名、作者、出版商、isbຘn。例如“theartofsqlbຘystéphaທnefaທ肉lt9๗ithpeterrobຘsoncopyrightc200่6o'reillymediaທ,0-596-00่894๒-5๓”。如果你对代码示例的使用出了上述范围，请通过permissions@oreilly联系出版商。评论与提问我们已尽力核验本书所的信息，尽管如此，仍不能保证本书完全没有瑕疵，而网络世界ศ的变化之快，也使得本书永不过时的保证成为不可能。如果读者现本书内容上的错误，不管是赘字、错字、语意不清，甚至是技术错误，我们都竭诚虚心接受读者指教。如果您有任何问题，请按照以下的联系方式与我们联系。o'reillymedia,in9high9aທynorth色bastopol,9theusor9๗ternationalorlocaທl707๕829-01้04๒fax致谢本书原版用英语写作，英语既ຂ不是我的家乡话，又不是我所在国家的语言，所以写这样一本书要求极度乐观回想起来几近疯狂。幸运的是，peterrobson不仅为本书贡献了他在sql和数据库设计方แ面的知识，也贡献了持续的热情来修改我冗长的句子、调整副词位置、斟酌替换词汇。peterrobson和我在好几个大会上都碰过面，我们都是演讲者。jonathangenni9athangennick是o'reilly出版的sqlpo9athaທn是个非常尊重作者的编辑。由于他的专业、他对细节的关注、他的犀利视角，使本书的质量大大提升。同时，jonathaທn也使本书的语言更具“中大西洋”风味peter和我现，虽然我们保证按美国英语拼写，但还远远不够。
---------ๅ-------ๅ--ๅ--ๅ---page5--------ๅ----------ๅ--ๅ---
我还要感谢很多人，他们来自三个不同的大陆，阅读了本书全部或部ຖ分草稿并坦诚地提出意见。他们是：philippebຘertolino、ra9il9s、timgorman、jeaທn-ๅpaulmaທrtin、saທnjaທymishra、anthony摸linaro、tiongsoohua。我特别感激laທrry，因为ฦ本书的思想最初来自于我们的e-mail讨论。我也要感谢o'reilly的许多人，他们使本书得以出版。他们是：mar9o、jaທmiepeppard、mikekohnke、ron逼lodeaທu、jessamynread、andre9๗savikaທs。感谢nan9hardt卓越的手稿编辑工作。特别感谢艳n-arzeldurelle-maທrc慷慨第1้2章用到的图片。感谢paທulmcນ9horter授权我们将他的战争图用于第6๔章。最后，感谢rogermaທn色r和steelbusinessiefing的职员，他们为ฦpeter和我了位于伦敦的办公室还有大量咖啡。感谢qianlenaທashley了本书开始引用的《孙子兵法》的中文原文。作者介绍stéphanefa肉lt从1983年开始接触关系数据库。oracນle法国成立早期他即加入此前是短暂的ibm经历和渥太华大学任教生涯，并在不久之后对性能和调优产生了兴趣。19๗88๖年他离开了oraທcle，此后一年间，他进行调整，并研究过运筹学。之后，他重操旧业，一直从事数据库咨询工作，并于199๗8年创น办了肉gh色a公司肉gh色a。stéphanefa肉lt出版了fortraທnstru9umériques一书法语，dunod出版社ุ，198๖6,ไ与didiersi摸n合作，并在oraທ9e和色lect分别为ฦ英国和北美oracle用户组杂志以及oracນle杂志在线版上表了许多文章。他还是美国、英国、挪威等众多用户组大会的演讲者。peterrobson毕业于达拉谟大学地质专业19๗68๖年，然后在爱丁堡大学任教，并于197๕5年获得地质学研究型硕士学位。在希腊度过了一段地质学家生涯之ใ后，他开始在纽卡斯尔大学专攻地质和医学数据库。他使用数据库始于1้9๗77年，19๗8๖1年开始使用关系数据库，1985年开始使用oracle，这期间担任过开工程师、数据架构师、数据库管理员等角色。1้98๖0่年，peter参加了英国地质普查，负责指导使用关系数据库管理系统。他擅长sql系统，以及从组织级到部门级的数据建模。peter多次出席英国、欧洲、北美的oracle数据库大会，在许多数据库专业杂志上表过文章。他现任英国oracນle用户组委员会主任，可通过peterrobson@justsql联系他。查询的识别ี
-ๅ-------ๅ------ๅ---ๅ---ๅ---page6๔-ๅ------ๅ---------ๅ---ๅ-ๅ--ๅ-
有经验的朋友都知道，把关键系统从开环境切换到生产环境是一场战役，一场甚嚣尘上的战役。通常，在“攻击起日d-day”的前几周，性能测试会显示新系统达不到预期要求。于是，找专家，调优sql语句，召集数据库管理员和系统管理员不断ษ开会讨论对策。最后，性能ม总算与以前的系统大致相当了尽管新系统用的是价格翻倍的硬件。人们常常使用战术，而忽略了战略。战略要求从大局上把握整个架构与设计。和战争一样，战略的基本原则并不多，且经常被忽视。架构错误的代价非常高，sql程序员必须准备充分，明确目标，了解如何实现目标。在本章中ณ，我们讨论编写高效访问数据库的程序需要实现哪些关键目标。查询的识别queryidentifi9๗qquueerryyiiddeennttiiffiiaattiioonn数个世纪以来，将军通过辨别军装颜色和旗帜等来判断各部ຖ队的位置，以此检查激战中部队行进情况。同样，当一些进程消耗了过多的cນpu资源时，通常也可以确定是由哪些正被执行的sql语句造成的。但是，要确定是应用的哪部分提交了这些sql语句却困难得多，特别ี是复杂的大型系统包含动态建立的查询的时候。尽管许多产品良好的监控工具，但要确定一小段sql语句与整个ฐ系统的关系，有时却非常困难。因此，要养成为程序和关键模块加注释的习惯，在sql中插入注释有助于辨别查询在程序中的位置。例如：9色lectbຘlah这些注释在查错时非常有用。另外，注释也有助于判断ษ单独应用对服务器造成的负载有多大；例如我们希望本地应用承担更多工作，需要判断ษ当前硬件是否能承受突高负载，这时注释特别ี有用。有些产品还了专门的记录功能registrationfacilities，将你从“为每个语句加注释”的乏味工ื作中ณ解放出来。例如oraທ9_ຕinfo包，它支持4๒8个ฐ字符的模块名称摸dulename、3๑2๐个字符的动作名称aທ9ame和64个ฐ字符的客户信息，这些字段的内容可由我们定制。在oracle环境下，你可以利ำ用这个ฐ程序包记录哪个应用正在执行，以及它在何时正在做什么。因为应用是通过“oracນlev$动态视图”能显示目前内存中生的情况向程序包传递信息的，于是我们可以轻易地掌握这些信息。总结：易识别的语句有助于定位性能问题。保持数据库连接稳定stabຘledataທba色9sssttaaທbຘblleeddaaທttaabbaaທs色eoonnnneettiioonnss建立一个ฐ新的数据库连接，既快又方แ便，但这其中ณ往往掩藏着重复建立数据库连接带来的巨大开销。所以，管理数据库连接必须非常小心。允许多重连接——可能就藏在你的应用中ณ——的后果可能很严å重，下面即是一例。
--ๅ-ๅ----ๅ---ๅ---ๅ----ๅ-----ๅ-paທge7------ๅ------ๅ---ๅ--ๅ-ๅ--ๅ-ๅ--
不久前，我遇到一个应用，要处理很多小的文本文件。这些文本文件最大的也不过一百行，每一行包含要加载的数据及数据库等信息。此例中ณ固然只有一个ฐ数据库实例，但即使有上百个，这里所说明的原理也是适用的。处理每个文件的代码如下：openthefileuntiltheendoffileisreachedreadaro9๗9ecttothe色rverspe9色rtthedaທtaທdis9ectclo色thefile上述处理工作令人满意，但当大量小文件都在极短的时间内到达时，可能应用程序来不及处理，于是积压大量待处理文件，花费时间相当可观。我用cນ语言编了个简单的程序来模拟上述情况，以说明频๗繁的数据库连接和中断所造成的系统性能ม下降问题。表2-1้列出了模拟的结果。注意产生表2๐-1结果的程序使用了常规的in色rt语句。顺便提一下，直接加载dire9๗g的技术会更快。表2-1้：连接／中断性能测试结果测试结果依次对每一行作连接／中ณ断74๒行秒连接一次，所有行逐个插入1้681行秒连接一次，以10行为ฦ一数组插入5914行秒连接一次，以100่行为一数组插入919๗0行秒
----ๅ-ๅ--ๅ---ๅ---ๅ--ๅ-ๅ------ๅ-paທge8๖---ๅ------ๅ--ๅ-ๅ---------ๅ--
此例说明了尽量减少分别ี连接数据库次数的重要性。对比表中ณ前后两次针ฤ对相同数据库的插入操作，明显现性能有显着提升。其实还可以做进一步的优化。因为数据库实例的数量势必有限，所以可以建立一组处理程序haທndler分别ี负责一个数据库连接，每个数据库只连接一次，使性能进一步提高。正如表2-ๅ1้所示ิ，仅连接数据库一次或很少次的简单技巧ู，再加上一点额外工作，就能让效率提升200倍以上。当然，在上述改进的基础上，再将欲更新า的数据填入数组，这样就尽可能减少了程序和数据库核心间的交互次数，从而使性能产生了另一次飞跃。这种每次插入几行数据的做法，可以使数据的总处理能力又增加了5๓倍。表2-ๅ1中ณ的结果显示改进后的性能几乎是最初ม的12๐00่倍。为ฦ何有如此大的性能提升？第一个原因，也是最大的原因，在于数据库连接是很“重”的操作，消เ耗资源很多。在常见的客户服务器模式中ณ现在仍广为使用，简单的连接操作背后潜藏着如下事实：先，客户端与远程服务器的监听程序listenerprograທm建立联系；接着，监听程序要么เ创建一个进程或线程来执行数据库核心程序，要么เ直接或间接地把客户请求传递给已存在的服务器进程，这取决于此服务器是否为共享服务器。除了这些系统操作创น建进程或线程并开始执行之外，数据库系统还必须为每次色ssion建立新环境，以跟踪它的行为。建立新色ssion前，dbms还要检查密码是否与保存的加密的账户密码相符。或许，dbຘms还要执行登录触器logontrigger，还要初始化存储过程和程序包如果它们是第一次被调用。上面这些还不包括客户端进程和服务器进程之间要完成的握手协议。正因为ฦ如此，连接池9g等保持永久数据库连接的技术对性能ม才如此重要。第二个原因，你的程序甚至包括存储过程和数据库之间的交互也有开销。即使数据库连结已经建立且仍未中断，程序和dbຘms核心之ใ间的上下文切换9text9๗itcນh也有代价。因此，如果dbms支持数据通过数组传递，应毫不犹豫地使用它。如果该数组接口是隐式的aທpi内部使用，但你不能使用，那ว么明智的做法是检查它的默认大小并根据具体需要修改它。当然，任何逐行处理的方式都面临上下文切换的问题，并对性能ม产生严å重影响——本章后面还会多次涉及此问题。总结：数据库连接和交互好似万里长城——长度越长，传递消息越耗时。战略๓优先于战术strategybຘeforetaທcນticນsssttrraaທtteeggyybbeeffoorreettaattiiss战略决定战术，反之则谬也。思考如何处理数据时，有经验的开者不会着眼于细微步骤，而是着眼于最终结果。要获得想要的结果，最显而易见的方法是按照业务规则规定的顺序按部ຖ就班地处理，但这不是最有效的方法——接下来的例子将显示，刻意关注业务处理流程可能ม会使
--ๅ------ๅ------ๅ--ๅ-ๅ---ๅ---page9-ๅ-------ๅ--ๅ--ๅ-ๅ------ๅ----
我们错失最有效的解决方แ案。几年前，有人给了我一个ฐ存储过程，让我“尝试”着进行一下优化。为什么เ说是“尝试”呢？因为该存储过程已经被优化两ä次了，一次是由原开者，另一次是由á一个自称oracle专家的人。但尽管如此，这个存储过程的执行仍会花上20分钟，使用者无法接受。此存储过程的目的，是根据现有库存和各地订单，计算出总厂需要订购的原料数量。大体上，它就是把不同数据源的几个ฐ相同的表聚合aggregate到一个ฐ主表mastertable中。先，将每个ฐ数据源的数据插入主表；接着，对主ว表中的各项数据进行合计并更新；最后，将与合计结果无关的数据从表中删除。针ฤ对每个ฐ数据源，重复执行上述步骤。所有sql语句都不是特别复杂，也没有哪个ฐ单独的sql语句特别ี低效。为ฦ了理解这个存储过程，我花了大半天时间，终于现了问题：为什么该过程要用这么多步骤呢？在from子句中ณ加上包含union的子查询，就能得到所有数据源的聚合aທggregaທtion。一条色lect语句，只需一步就得到了结果集，而之前要通过插入目标表targettable得到เ结果集。优化后，性能的提升非常惊人——从20分钟็减至2๐0秒；当然，之后我花了一些时间验证了结果集，与未优化前完全相同。想要获得上述的大幅提高性能，无需特别ี技能，仅要求站在局外思考thinkoutsidethebຘox的能力。之前两ä次优化因“太关注问题本身”而收到了干扰。我们需要大胆的思维，站得远一些，试着从大局的角度看待问题。要问自己้一些关键的问题：写存储过程之前，我们已๐有哪些数据？我们希望存储过程返回什么เ结果？再辅以大胆的思维，思考这些问题的答案，就能得到一个性能大幅提升的处理方式了。总结：考虑解决方案的细节之ใ前，先站得远一些，把握大局。先定义问题，再解决问题probຘlemdefinitionbeforesolutionpprroobຘblleemmddeeffiinniittiioonnbbeeffoorreessoolluuttiioonn一知半解是危险的。人们常在听说了新技术或特殊技术之ใ后——有时的确很吸引人——试图采用它作为新的解决方案。普通开者和设计师通常会立即采纳这些新“解决方แ案”，直到เ后来才现它们会产生许多后续问题。现成的解决方แ案中ณ，非规范化设计引人注目。设计伊始，非规范化设计的拥护者就提出此方案，为ฦ了寻求“性能”而无视最终将会面临的升级恶魔——而事实上，在开周期早期，改进设计或学习如何使用join也是一个不错的选择。作为非规范化设计的一种手段，物化视图materializedvie9常被认为ฦ是灵丹妙药。物化视图有时被称为ฦ快照snapshot，这个更加平常的词更形象地反映了可悲的事实：物化视图是某时间点的数据副本。在没有其他办法时，这个理论上遭到เ质疑的技术也未尝不值得一试，借用卡夫卡franzkafka的一句名言：“逻辑诚可贵，生存价更高。”然而，绝大部分问题都可借助传统技术巧妙解决。先，应学会充分利用简单、传统的技术。只有完全掌握了这些技术，才能正确评价它们的局限性，最终现它相当于新技术的潜在优势如果有的话。所有技术方案，都只是我们达到目标的手段。没有经验的开者误把新技术本身当成了目标。
----------ๅ--ๅ------ๅ-----page10-ๅ----------ๅ--ๅ----ๅ--ๅ--ๅ-ๅ-
对于热衷于技术、过于看重技术的人来说，此问题就更为严重。总结：先打基础，再赶时髦：摆弄新า工ื具之前，先把手艺学好。直接操作实际数据operaທtionsaທgainstacນtuaທldataooppeerraattiioonnssaaggaaiinnssttaທattuuaທallddaທattaa许多开者喜欢建立临时工作表temporary9orktable，把后续处理使用的大量数据放入其中，然后开始“正式”工作。这种方แ法广受质疑，反映了“跳出业务流程细节考虑问题”的能ม力不足。记住，永久表permaທnenttaທble可以设置非常复杂的存储选项在第5章将讨论一些存储选项的设置，而临时表不能。临ภ时表的索ิ引如果有的话可能不是最优的，因此，查询临时表的语句效率比永久ื表的差。另外，查询之前必然先为ฦ临时表填入数据，这自然也多了一笔额外的开销。就算使用临ภ时表有充足理由á，若数据量大，也绝不能把永久ื表当作临时工作表来用。问题之一在于统计信息的自动收集：若没有实时收集要求，dbms通常会在不活动或活动少时进行统计信息收集，而这时作为临时工作表可能ม为ฦ空，从而使优化器收到了完全错误的信息。这些不正确且有偏差的统计信息可能造成执行计划ฐexe9๗完全不合理，导致性能下降。所以，如果一定要用临时表，应确保数据库知道哪些表是临ภ时的。总结：暂时工ื作表意味着以不太合理的方แ式存储更多信息。sql用ssqqll处理集合色tpro9sqls色ettpprrooeessssiinnggiinnssqqllsql完全基于集合色t来处理数据。对大部分更新或删除操作而言——如果不是针ฤ对整个ฐ表的话——你必须先精确定义แ出要处理的记录的集合。这定义了该处理的粒度granularity，可能是对大量记录的粗粒度操作，有可能ม是只影响少数记录的细粒度操作。将一次“大批量数据的处理”分割成多次“小块处理”是个坏主意，除非对数据库的修改太昂贵，否则不要使用，因为这种方法极其低效：1占用过多的空间保存原始数据，以备事务traທnsa9๗回滚rollbacນk之需；2万一修改失败，回滚消耗过长的实践。许多人认为ฦ，进行大规模修改操作时，应在操作数据的代码中有规律地多安排些mit命令。其实，严å格从实践角度来讲，“从头开始重做”比“确定失败生的时间和位置，接着已๐提交部分重做”要容易得多、简单得多、也快得多。处理数据时，应适应数据库的物理实现。考虑事务失败时回滚所需日志的大小，如果要为undo保存的数据量确实巨大，或许应该考虑数据修改的频率问题。也就是说，将大规模的“每月更新”，改为规模不大的“每周更新”，甚至改为规模更小的“每日更新”，或许是个ฐ有效方案。总结：几千个语句，借助游标cursor不断循环，很慢。换成几个语句，处理同样的数据，还是较慢。换成一个语句，解决上述问题，最好。
-----ๅ--ๅ-ๅ---------ๅ----ๅ--ๅpage11้--ๅ-ๅ---ๅ------ๅ---ๅ-ๅ--ๅ--ๅ-ๅ--
sql动作丰富的ssqqll语句aທ9-pa9tsaaທttiioonn-ๅ-ppaaທkkeeddssqqllssttaທatteemmeennttsssql不是过程性语言pro9๗guaທge，尽管也可以将过程逻辑procedurallogicນ用于sql，但必须小心。混淆声明性处理de9g和过程逻辑，最常见的例子出现在需要从数据库中提取数据、然后处理数据、然后再插入到数据库时。在一个ฐ程序或程序中的一个ฐ函数接收到特定输入值后，如下情况太常见了：用输入值从数据库中ณ检索ิ到เ一个或多个另外的数据值，然后，借助循环或条件逻辑通常是ifthenel色将一些语句组织起来，对数据库进行操作。大多数情况下，造成上述错误做法的原因有三：根深蒂固的坏习惯、sql知识的缺乏็、盲从功能需求规格说明。其实，许多复杂操作往往可由一条sql语句完成。因此，如果用户了一些数据值，尽量不要将操作分解为多条提取中间结果的语句。避免在sql中引入“过程逻辑proceduraທllogic”的主要原因有二。数据库访问，总会跨多个软件层，甚至包括网络访问。即使没有网络访问，也会涉及进程间通讯；额外的存取访问意味着更多的函数调用、更大的带宽，以及更长的等待时间。一旦这些调用要重复多次，其对性能的影响就非常可观了。在sql中引入过程逻辑，意味着性能和维护问题应由á你的程序承担。大多数据库系统都了成熟ງ的算法，来处理join等操作，来优化查询以获得更高的效率。基于开销的优化器cost-bຘaທ色doptimizer，cbo是很复杂的软件，它早已๐不像刚ธ推出时那样没什么用了，而在大部ຖ分情况下都是非常出色的成熟产品了，优秀的cbo查询优化的效率极高。然而，cbo所能改变的只有sql语句。如果在一条单独的sql语句中完成尽可能多的操作，那么性能优化可以还由dbms核心负责，你的程序可以充分利用dbms的所有升级。也就是说，未来大部分维护工作从程序间接转移给了dbms供货商。当然，“避免在sql中引入过程逻辑”规则ท也有例外。有时过程逻辑确实能ม加快处理度，庞大的sql语句未必总是高效。然而，过程逻辑及其之后的处理相同数据的语句，可以编写到一个单独的sql语句中，cbo就是这么做的，从而获得最高效的执行方แ式。总结：尽可能多地把事情交给数据库优化器来处理。充分利用每次数据库访问profitabledatabຘa色aທes色spprrooffiittaaທbblleeddaທattaabຘbaas色eaaທeesss色ess如果计划ฐ逛好几家商店，你会先决定在每家店买哪些东西。从这一刻起，就要计划按何种顺ิ序购物才能少走冤枉路。每逛一家店，计划东西购买完毕，才逛下一家。这是常识，但其中蕴含的道理许多数据库应用却不懂得。要从一个表中ณ提取多段信息时，采用多次数据库访问的做法非常糟糕，即使多段信息看似“无关”但事实上往往并非如此。例如，如果需要多个ฐ字段的数据，千万不要逐个ฐ字段地提取，而应
--ๅ---ๅ---ๅ---ๅ------ๅ--ๅ-ๅ---page12๐------ๅ---ๅ-ๅ--ๅ--ๅ-ๅ---ๅ-----
一次操作全部ຖ完成。很不幸，面向对象oo的最佳实践提倡为ฦ每个ฐ属性定义一个get方法。不要把oo方法与关系数据库处理混为一谈。混淆关系和面向对象的概念，以及将表等同于类、字段等同于属性，都是致命的错误。总结：在合理范围内，利ำ用每次数据库访问完成尽量多的工作。dbms接近ddbbmmss核心9ellloos色enneessssttootthheeddbbຘmmskeerrnneell代码的执行越接近dbຘms核心，则执行度越快。数据库真正强大之处就在于此，例如，有些数据库管理产品支持扩展，你可以用c等较底层的语言为它编写新功能。用含有指针操作的底层语言有个缺点，即一旦指针ฤ处理出错会影响内存。仅影响到一个用户已很糟糕，何况数据库服务器就像“服务器”名字所指的一样出了问题会影响众多“用户”——服务器内存出了问题，所有使用这些数据的无辜的应用程序都会受影响。因此，dbms核心采取了负责任的做法，在沙箱sandbຘox环境中执行程序代码，这样，即使出了问题也不会影响到数据。例如，ora99๗和它自身之间实现了一套复杂的通信机制，此机制在某些方面很像控制数据库连结的方แ法，以管理两个或多个服务器上的数据库实例之ใ间的通信。到เ底采用plsql存储过程还是外部cນ函数，应综合比较后决定。如果精心编写外部c函数获得的好处过了建立外部环境和上下文切换9๗g的成本，就应采用外部函数。但需要处理一个ฐ大数据量的表的每一行时，不要使用外部ຖ函数。这需要平衡考虑，解决问题时应完全了解备选策略的后果。如要使用函数，始终应选dbms自带的函数。这不仅仅是为ฦ了避免无谓的重复劳动，还因为自带函数在执行时比任何第三方开的代码更接近数据库核心，相应地其效率也会高出许多。下面这个简单例子是用oraclesql编写的，显示ิ了使用oracle函数所获得的效率。假设手工输入的文本数据可能包含多个ฐ相邻的“空格”，我们需要一个函数将多个ฐ空格替换为一个空格。如果不采用oraທcledatabaທ色10่g开始的正规表达式regulaທrexpression，函数代码将会是这样：99๗gi女aທr9๗vaທr9gvar9number:=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;
---ๅ--ๅ-ๅ------ๅ-------ๅ--ๅ--paທge14๒-ๅ---------ๅ----------ๅ--ๅ-
还有第三种方แ法：9๗9gi女ar9varchar2isv_stringvar9g;len1numbຘer;len2๐number;๙bຘeginlen1:=lengthp_string;v_ຕstring:=repla9g,''ู,''ู;len2:๘=lengthv_string;9hilelen2色lectsqueeze1้'ูazerythgfrdtr'2fromdual3aທzerythgfrdtrelap色d:00่:00:0่00่0่sql色lectsqueeze2'aທzerythgfrdtr'2fromdual3๑azerythgfrdtrelaທp色d:0่0่:0่0่:0001sql色lecນtsqueeze3๑'aທzerythgfrdtr'2fromdual3azerythgfrdtrelap色d:00:๘00:๘0่00่0
-ๅ-ๅ-------ๅ--ๅ-ๅ----ๅ-----ๅ--ๅpage15--ๅ--ๅ----ๅ--ๅ--ๅ-ๅ---ๅ------ๅ-
那么，如果每天要调用该空格替换操作几千次呢？我们构造一个接近现实负载的环境，下面的代码将建立一个ฐ用于测试的表并填入随机数据，已๐检测上面三个ฐ函数是否有性能差ๆ异：createtaທblesqueezableraທndom_textvarchaທr25๓0de9ary_integer;j逼nary_integer;k逼nary_integer;v_stringvare1,ไ100;v_string:๘=dbms_randomstring'u',50่;9๗hilej9๗hen9stillsomethingel色el色
-ๅ-----ๅ-ๅ-----ๅ----ๅ--ๅ-ๅ----page1้9-------ๅ-ๅ---ๅ------ๅ--ๅ-ๅ---
end数值或日期的比较则ท简单明了。操作字符串ธ可以用oraທcle的greaທtest或least，或者mysql的str9色rt语句增加过程逻辑，具体办法是多重in色rt及条件in色rt注3，并借助merge语句。如果dbຘms了这样语句，毫不犹豫地使用它。也就是说，有许多逻辑可以放入sql语句中；虽然仅执行多条语句中的一条这种逻辑价值不大，但如果设法利ำ用ca色、merge或类似功能ม将多条语句合并成一条，价值可就大了。总结：只要有可能ม，应尽量把条件逻辑放到sql语句中，而不是sql的宿主语言中ณ。一次完成多个ฐ更新multipleupdatesaທtoncemmuullttiipplleeuuppddaatteessaaທttoonnee我的基本主张是：如果每次更新的是彼此无关的记录，对一张表连续进行多次update操作还可以接受；否则，就应该把它们合并成一个ฐupdaທte操作。例如，下面是来自实际应用的一些代码注4๒：updatetbຘo_i女oice_ຕextractor色tpga_staທtus=0pdatetbຘo_ຕi女oice_ຕextracນtor色trd_status=09hererd_staທtusin1,3aທndi女_type=0;两ä个连续的更新是对同一个表进行的。但它们是否将访问相同的记录呢？不得而知。问题是，搜索条件的效率有多高？任何名为type或status的字段，其值的分布通常是杂乱无章的，所以上面两个updaທte语句极可能对同一个表连续进行两次完整扫描：一个ฐupdate有效地利用了索引，而第二个update不可避免地进行全表扫描；或者，幸运的话，两次updaທte都有效地利用了索引。无论如何，把这两个update合并到一起，几乎不会有损失，只会有好处：updaທtetbo_ຕi女oice_extraທctor色tpgaທ_status=9๗1then0่9hen3then0el色pga_ຕstatus
-ๅ----ๅ-ๅ--ๅ-----ๅ-ๅ--ๅ-ๅ------ๅpage20่-----ๅ----ๅ--ๅ-ๅ---------ๅ--
end,rd_status=91้then09hen3then0el色rd_staທtusendsin1้,3๑aທndi女_type=0่;๙上例中ณ，可能出现重复更新相同字段为ฦ相同内容的情况，这的确增加了一小点儿开销。但在多数情况下，一个ฐupdate会比多个updaທte快得多。注意上例中的“逻辑logic”，我们通过cນaທ色语句实现了隐式的条件逻辑impli9allogic，来处理那些符合更新条件的数据记录，并且更新า条件可以有多条。总结：有可能的话，用一个语句处理多个更新；尽量减少对同一个表的重复访问。慎用自定义แ函数carefulu色ofu色r-ๅ9rittenfun9s将自定义函数u色r-9๗rittenfun9๗嵌到sql语句后，它可能ม被调用相当多次。如果在色lecນt语句的选出项列ต表中使用自定义函数，则每返回一行数据就会调用一次该函数。如果自定义函数出现在9here子句中ณ，则每一行数据要成功通过过滤条件都会调用一次该函数；如果此时其他过滤条件的筛选能力不够强，自定义函数被调用的次数就非常可观了。如果自定义函数内部ຖ还要执行一个ฐ查询，会生什么情况呢？每次函数调用都将执行此内部ຖ查询。实际上，这和关联子查询cນorrelatedsubຘquery效果相同，只不过自定义函数的方式阻碍了基于开销的优化器cນost-bຘaທ色doptimizer，cbo对整个查询的优化效果，因为“子查询”隐藏在函数中ณ，数据库优化器鞭长莫及。下面举ะ例说明将sql语句隐藏在自定义函数中的危险性。表flights描述商务航班，有航班号、起飞时间、到达时间及机场iaທta代码注5๓等字段。iata代码均为ฦ三个ฐ字母，有9๗000多个ฐ，它们的解释保存在参照表中，包含城市名称若一个城市有多个机场则应为ฦ机场名称、国家名称等。显然，显示航班信息时，应该包含目的城市的机场名称，而不是简单的iata代码。在此就遇到了现代软件工ื程中的矛盾之一。被认为是“优良传统”的模块化编程一般情况下非常适用，但对数据库编程而言，代码是开者和数据库引擎的共享活动shaທredactivity，模块化要求并不明确。例如，我们可以遵循模块化原则ท编写一个小函数来查找iaທtaທ代码，并返回完整的机场名称：99๗airport_ຕ9๗9varchar2๐is
------ๅ--ๅ--ๅ----ๅ--ๅ-ๅ------ๅpage21้--ๅ-ๅ--ๅ-ๅ------ๅ-----ๅ-ๅ-----ๅ
9aທmevar9amefromiata_airport_codes9๗herecode=iataທ_9aທme;๙end;对于不熟悉oraທcle语法的读者，在此做个说明，以下查询中ณtruncsysdaທte的返回值为“今天的00:00่aທm”，日期计算以天为单位；所以起飞时间的条件是指今天8๖:3๑0่am至4:0่0pm之间。调用airport_cນity函数的查询可以非常简单，例如：色le9๗umbຘer,ไto_chardeparture_time,'hh24๒:๘mi'depaທrture,airport_cityarrival"to"fromflights9heredepaທrture_ຕtimebet9eentrunre_ຕtime这个ฐ查询的执行度令人满意；在我机器上的随机样本中，返回7๕7๕行数据只用了0่18๖秒多次执行的平均值，用户对这样的度肯定满意统计数据表明，此处理访问了303个数据块，53个是从磁盘读出的——而且每行数据有个递归调用。我们还可以用join来重写这段代码，作为查找函数的替代方แ案，当然它看起来会稍微复杂些：色le9umber,ไto_ຕcharfdeparture_ຕtime,'ูhh24:mi'ูdepaທrture,acity"ิto"ิfromflightsf,iaທta_airport_codesa9herea9๗ddeparture_timebet9eentrunre_time
-ๅ--ๅ-ๅ------ๅ---ๅ---ๅ--ๅ-ๅ--ๅ-ๅ-page2๐2----ๅ-ๅ-----ๅ----ๅ--ๅ-ๅ------
这个查询只用了00่5๓秒统计数据同前，但没有递归调用。对于执行时间不到เ02秒的查询来说，度快了3倍似乎无关紧要，但在大型系统中，这些查询每天经常执行数十万次——假设以上查询每天只执行五万次，于是查询的总耗时为2๐5小时。若不使用上述查找函数lookupfun9则ท只需要不到42๐分钟，度提高过300่%，这对大数据量的系统意义重大，最终带来经济上的节约。通常，使用查找函数会使批处理程序的性能极差。而且查询时间的增加，会使同一台机器支持的并用户数减少，我们将在第9章对此展开讨论。总结：优化器对自定义函数的代码无能ม为力。sql简洁的ssqqllsuincນtsql熟练的开者使用尽可能少的sql语句完成尽可能多的事情。相反，拙劣的开者则倾向于严格遵循已๐制ๆ订好的各功能步骤，下面是个ฐ真实的例子：--ๅgetthestaທrtoftheaountingperiod色le9todtperstaທfromtperrslt9๗herefiscaທl_ຕyear=to_ຕcharparaທm_ຕdta,ไ'yyyy'ูandrslt_ຕperiod='1'||to_charparam_ຕdtaທ,'mm';๙--ๅgettheendoftheperiodoutofcນlosure色le9todtperclosurefromtperrslt9herefiscal_year=to_charpaທraທm_dta,ไ'ูyyyy'aທndrslt_period='9'||to_cນharparam_dtaທ,ไ'mm';就算度可以接受，这也是段极糟的代码。很不幸，性能ม专家经常遇到这种糟糕的代码。既然两个ฐ值来自于同一表，为什么要分别用两ä个不同的语句呢？下面用oracle的bulkcollecນt子句，一次性将两个值放到数组中ณ，这很容易实现，关键在于对rslt_period进行orderby操作，如下所示ิ：色lectcນlosure_ຕdatebulk9todtperstaທarrayfromtperrslt9๗herefiscaທl_ຕyear=to_ຕcharparaທm_ຕdta,'yyyy'ูandrslt_ຕperiodin'1'||to_ຕcharpaທram_dta,'mm'ู,
-ๅ-ๅ----ๅ---------ๅ---ๅ-ๅ--ๅ--ๅpage23----ๅ--ๅ-ๅ----ๅ-----ๅ--ๅ-ๅ---ๅ-
'ู9'ู||to_cນhaທrparam_dtaທ,'mm'orderbyrslt_period;于是，这两个ฐ日期被分别ี保存在数组的第一个和第二个ฐ位置。其中，bulkcollect是plsql语言特有的，但任何支持显式或隐式数组提取的语言都可如法炮制。其实甚至数组都是不必要的，用以下的小技巧注6๔，这两ä个值就可以被提取到เ两个变量中ณ：色lectmaxdecນodesubstrrslt_period,1้,1้,ไ--checນkthefirstcharacນter'1',closure_date,--ๅifit's'1้'returnthedaທte9๗e9๗aທntto_date'1410่1้066',ไ'ddmmyyyy',--other9i色somethingoldmaxdecodesubstrrslt_period,1้,1,ไ'9'ู,closure_ຕdaທte,--thedaທte9e9aທntto_date'14๒101066',ไ'ูddmmyyyy'ู,intodtpersta,dtpercນlosurefromtperrslt9herefiscal_year=to_charparaທm_ຕdta,ไ'yyyy'andrslt_ຕperiodin'1'||to_ຕcharpaທraທm_dta,'ูmm'ู,'9๗'||to_chaທrpaທram_dta,'ูmm';在这个例子中，预ไ期返回值为两ä行数据，所以问题是：如何把原本属于一个字段的两行数据，以一行数据两个字段的方แ式检索出来正如数组提取的例子一样。为此，我们检查rslt_period字段，两行数据的rslt_period字段有不同值；如果找到需要的记录，就返回要找的日期；否则，就返回一个ฐ在任何情况下都远比我们所需日期要早的日期此处选了哈斯丁之役bຘattleofhastings的日期。只要每次取出最大值，就可以确保获得需要的日期。这是个ฐ非常实用的技巧，也可以应用在字符或数值数据，第1้1章会有更详细的说明。总结：sql是声明性语言de9guaທge，所以设法使你的代码越业务过程的规格说明。sqlssqqll的进攻式编程offensive9๗g9ithsql一般的建议是进行防御式编程9๗sively，在开始处理之前先检查所有参数的合法性。但实际上，对数据库编程而言，尽量同时做几件事情的进攻式编程有切实的优势。有个ฐ很好的例子：进行一连串ธ检查，每当其中一个ฐ检查所要求的条件不符时就产生异常。信用卡付款的处理中就涉及类似步骤。例如，检查所提交的客户身份和卡号是否有效，以及两者是否匹配；检查信用卡是否过期；最后，检查当前的支付额是否过了信用额度。如果通过了所
------ๅ--ๅ-ๅ---ๅ------ๅ---ๅ--ๅpage24-ๅ--------ๅ-ๅ-----ๅ----ๅ--ๅ-ๅ-
有检查，支付操作才继续进行。为ฦ了完成上述功能，不熟练的开者会写出下列语句，并检查其返回结果：色le9tfromcustomers9herecustomer_id=provided_ຕid接