━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
sqlssqqll语言艺术内容介绍本书分为ฦ12๐章，为ฦ你的实际工作提出了具体，非常便于实践。尽管当今新潮技术层出不ຘ，对九种sql经典查询场景以及其性能影响讨论，并通过举例&#x，每一章包含许多原则或准则。数据处理依然处于我们系统的核心地位，而且需管理的，尤其是数据库应用维护人员阅读。资深sql专家stéphanefa肉lt倾力打造《软件架构设计》作者温昱最新译作巧妙借鉴《孙子兵法》的智慧结晶传授25年的sql性能ม与调校经验深入探讨九种常见查询方แ案及其性能前言过去，“信息技术it”的名字还不如今天这般耀眼，被称为“电å子数据处理”。其实，这些例子大多来自于实际案例，本书适合sql数据库开者、软件架，也适合dbaທ。今天，最重要的集团数据都被保存在数据库中，sql语言虽有缺点。通过sql语言来&#x，但非常流行，它从1้980年代早期开始被广泛接受，如今。随后就æ，年轻开者在接受面试时，没有谁不宣称自己能熟练应用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开必须考虑数据的变化，其中ณ有很大的不确定性。某些表的数据量出乎意料é地增长将会如何？同时，用户数量也倍增了，又将会如何？希望数据在线保存好几年将会如何？如此一来，运行在硬件之上的这些程序的行为是否会完全不同？架构级的选择是在赌未来，当然需要明确可靠的理论知识——但这是进一步运用艺术的先决条件。第一次世界ศ大战联军总司令ferdinandfo9๗checolesupérieuredeguerre的一次演讲中说：战争的艺术和其他艺术一样，有它的历史和原则ท——否则ท，就不能成其为艺术。本书不是cookbຘook，不会列出一串问题然后给出“处方”。本书的目标重在帮助开者和他们的经理提出犀利的问题。阅读和理解了本书之ใ后，你并不是永不再写出丑陋缓慢的查询了——有时这是必须的——但希望你是故意而为之、且有充足的理由á。目标读者本书的目标读者是：有丰富经验的sql数据库开者他们的经理数据库占重要地位的系统的软件架构师我希望一些dbຘaທ、尤其是数据库应用维护人员也能ม喜欢本书。不过，他们不是本书的主ว要目标读者。本书假定
-ๅ--ๅ---ๅ-ๅ-ๅ-------ๅ--------paທge3---ๅ-ๅ-ๅ------ๅ---ๅ-ๅ--ๅ-ๅ-ๅ-ๅ---
本书假定你已精通sql语言。这里所说的“精通”不是指在你大学里学了sql101并拿来aທ+ใ的成绩，当然也并非指你是国际公认的sql专家，而是指你必须具有使用sql开数据库应用的经验、必须考虑索引、必须不把50่0่0่行的表当大表。本书的目标不是讲解连接、外连接、索引的基础知识，阅读本书过程中，如果你觉得某个ฐsql结构还显神秘,并影响了整段代码的理解，可先阅读几本其他sql书。另外，我假定读者至少熟悉一种编程语言,ไ并了解计算机程序设计的基本原则。性能ม已๐很差ๆ、用户已๐抱怨、你已在解决性能问题的前线，这就是本书的假定。本书内容我现sql和战争如此相像，以至于我几乎ๆ沿用了《孙子兵法》的大纲,并保持了大部分题目名称注2๐。本书分为1้2章，每一章包含许多原则ท或准则，并通过举例的方式对原则进行解释说明，这些例子大多来自于实际案例。第1章，制定计划：为ฦ性能ม而设计讨论如何设计高性能数据库第2章，动战争：高效访问数据库解释如何进行程序设计才能高效访问数据库第3๑章，战术部署：建立索引揭示ิ为何建立索ิ引，如何建立索引第4章，机动灵活：思考sql语句解释如何设计sql语句第5๓章，了如指掌：理解物理实现揭示物理实现如何影响性能第6章，锦囊妙计：认识经典sql模式包括经典的sql模式、以及如何处理第7๕章，变换战术：处理层次结构说明如何处理层次数据第8章，孰优孰劣：认识困难，处理困难指出如何认识和处理比较棘手的情况第9章，多条战线：处理并讲解如何处理并第10章，集中兵力：应付大数据量讲解如何应付大数据量第1้1章，精于计谋：挽救响应时间分享一些技巧，以挽救设计糟糕的数据库的性能第12章，明察秋毫：监控性能ม收尾，解释如何定义แ和监控性能本书约定本书使用了如下印刷惯例：等宽courier表示sql及编程语言的关键字，或表示ิtable、索ิ引或字段的名称，抑或表示函数、代码及命令
-ๅ---ๅ-ๅ--ๅ----ๅ-ๅ-ๅ----ๅ-----ๅ-ๅpaທge4๒-----ๅ-------ๅ-ๅ--ๅ---ๅ-ๅ-ๅ-ๅ--
输出。等宽黑体cນourier表示必须由用户逐字键入的命令等文本。此风格仅用于同时包含输入、输出的代码示例。等宽斜体courier表示这些文本，应该被用户的值代替。总结：箴言，概括重要的sql原则。注意提示、建议、一般性注解。为ฦ相关主ว题有用的附加信息。代码示例本书是为了帮助你完成工ื作的。总的来说，你可以将本书的代码用于你的程序和文档，但是，若要大规模复制代码，则必须ี联系o'reilly申ã请授权。例如：编程当中ณ用了本书的几段代码，无需授权；但出售或分o'reilly书籍中ณ案例的cນd-ๅrom光盘，需要授权。再如：回答问题时，引用了本书或其中的代码示例，无需授权；但在你的产品文档中大量使用本书代码，需要授权。o'reilly公司感谢但不强制ๆ归属声明。归属声明通常包括书名、作者、出版商、isbn。例如“theaທrtofsqlbystéphanefaທ肉lt9ithpeterrobຘsoncopyrightcນ20่06o'ูreillymediaທ,ไ0-ๅ5๓9๗6๔-00่894-5”。如果你对代码示例的使用出了上述范围，请通过permissions@oreilly联系出版商。评论与提问我们已尽力核验本书所的信息，尽管如此，仍不能ม保证本书完全没有瑕疵，而网络世界ศ的变化之ใ快，也使得本书永不过时的保证成为ฦ不可能。如果读者现本书内容上的错误，不管是赘字、错字、语意不清，甚至是技术错误，我们都竭诚虚心接受读者指教。如果您有任何问题，请按照以下的联系方式与我们联系。o'ูreillymediaທ,in9high9๗aynorth色bຘastopol,9theusor9๗ternationalorlocal7๕07๕8๖2๐9๗-0104๒fax致谢本书原版用英语写作，英语既不是我的家乡话，又不是我所在国家的语言，所以写这样一本书要求极度乐观回想起来几近疯狂。幸运的是，peterrobson不仅为ฦ本书贡献了他在sql和数据库设计方แ面的知识，也贡献了持续的热情来修改我冗长的句子、调整副词位置、斟酌替换词汇。peterrobson和我在好几个ฐ大会上都碰过面，我们都是演讲者。jonaທthaທngenni9athangennick是o'reilly出版的sqlpo9athaທn是个非常尊重作者的编辑。由于他的专业、他对细节的关注、他的犀利ำ视角，使本书的质量大大提升。同时，jonathaທn也使本书的语言更具“中ณ大西洋”风味peter和我现，虽然我们保证按美国英语拼写，但还远远不够。
------ๅ---ๅ-ๅ--ๅ----ๅ-ๅ-ๅ----ๅ-page5๓--ๅ-ๅ-ๅ-ๅ-----ๅ-------ๅ-ๅ--ๅ---ๅ
我还要感谢很多人，他们来自三个不同的大陆，阅读了本书全部ຖ或部分草稿并坦诚地提出意见。他们是：philippebຘertolino、ra9๗il9๗s、timgorman、jeaທn-paທulmartin、saທnjaທymishraທ、anthony摸linaທro、tiongsoohuaທ。我特别感激laທrry，因为本书的思想最初ม来自于我们的e-mail讨论。我也要感谢o'reilly的许多人，他们使本书得以出版。他们是：mar9๗o、jamiepeppard、mikekohnke、ron逼lodeaທu、jessamynreaທd、andre9๗savikas。感谢nan9haທrdt卓越的手稿编辑工作。特别感谢艳n-ๅarzeldurelle-maທrc慷慨第12๐章用到的图片。感谢paທulmcນ9๗horter授权我们将他的战争图用于第6๔章。最后，感谢rogermaທn色r和steelbusinessiefing的职员，他们为peter和我了位于伦敦的办公室还有大量咖啡。感谢qianlenaashley了本书开始引用的《孙子兵法》的中文原文。作者介绍stéphanefa肉lt从1้98๖3๑年开始接触关系数据库。oracນle法国成立早期他即加入此前是短暂的ibຘm经历和渥太华大学任教生涯，并在不久ื之后对性能和调优产生了兴趣。19๗88年他离开了oracle，此后一年间，他进行调整，并研究过运筹学。之后，他重操旧ງ业，一直从事数据库咨询工作，并于1998年创办了肉gh色aທ公司肉gh色aທ。stéphanefaທ肉lt出版了fortranstru9๗umériques一书法语，dunod出版社，1้9๗8๖6,与didiersi摸n合作，并在ora9e和色lecນt分别为英国和北美oracle用户组杂志以及oraທcນle杂志在线版上表了许多文章。他还是美国、英国、挪威等众多用户组大会的演讲者。peterrobson毕业于达拉谟大学地质专业1้9๗68๖年，然后在爱丁堡大学任教，并于1้97๕5年获得地质学研究型硕士学位。在希腊ຘ度过了一段地质学家生涯之后，他开始在纽卡斯尔大学专攻地质和医学数据库。他使用数据库始于1้977年，198๖1้年开始使用关系数据库，19๗8๖5๓年开始使用oracle，这期间担任过开工ื程师、数据架构师、数据库管理员等角色。1้980年，peter参加了英国地质普查，负责指导使用关系数据库管理系统。他擅长sql系统，以及从组织级到เ部门级的数据建模。peter多次出席英国、欧洲、北美的oracle数据库大会，在许多数据库专业杂志上表过文章。他现任英国oracນle用户组委员会主任，可通过peterrobson@justsql联系他。查询的识别
--------ๅ--ๅ----ๅ-----ๅ-ๅ-ๅ--page6๔-------ๅ-ๅ--ๅ-ๅ-ๅ-ๅ---ๅ-------
有经验的朋友都知道，把关键系统从开环境切换到生产环境是一场战役，一场甚嚣尘上的战役。通常，在“攻击起日d-day”的前几周，性能测试会显示新系统达不到เ预ไ期要求。于是，找专家，调优sql语句，召集数据库管理员和系统管理员不断ษ开会讨论对策。最后，性能ม总算与以前的系统大致相当了尽管新系统用的是价格翻倍的硬件。人们常常使用战术，而忽略了战略๓。战略要求从大局上把握整个ฐ架构与设计。和战争一样，战略的基本原则ท并不多，且经常被忽视。架构错误的代价非常高，sql程序员必须准备充分，明确目标，了解如何实现目标。在本章中ณ，我们讨论编写高效访问数据库的程序需要实现哪些关键目标。查询的识别queryidentifi9qquueerryyiiddeennttiiffiiaaທttiioonn数个世纪以来，将军通过辨别ี军装颜色和旗帜等来判断各部ຖ队的位置，以此检查激战中部队行进情况。同样，当一些进程消耗了过多的cpu资源时，通常也可以确定是由哪些正被执行的sql语句造成的。但是，要确定是应用的哪部ຖ分提交了这些sql语句却困难得多，特别是复杂的大型系统包含动态建立的查询的时候。尽管许多产品良好的监控工具，但要确定一小段sql语句与整个ฐ系统的关系，有时却非常困难。因此，要养成为ฦ程序和关键模块加注释的习惯，在sql中ณ插入注释有助于辨别查询在程序中的位置。例如：9色lectbຘlah这些注释在查错时非常有用。另外，注释也有助于判ศ断ษ单独应用对服务器造成的负载有多大；例如我们希望本地应用承担更多工作，需要判断当前硬件是否能ม承受突高负载，这时注释特别有用。有些产品还了专门的记录功能registraທtionfaທcນilities，将你从“为ฦ每个ฐ语句加注释”的乏味工作中解放出来。例如ora9_info包，它支持4๒8个字符的模块名称摸dulenaທme、3๑2๐个ฐ字符的动作名称a9ame和6๔4个字符的客户信息，这些字段的内容可由á我们定制。在oracle环境下，你可以利用这个ฐ程序包记录哪个ฐ应用正在执行，以及它在何时正在做什么。因为ฦ应用是通过“oraclev$动态视图”能ม显示目前内存中生的情况向程序包传递信息的，于是我们可以轻易地掌握这些信息。总结：易识别的语句有助于定位性能问题。保持数据库连接稳定staທbຘledataທbຘa色9sssttaabຘbຘlleeddaທaທttaaທbbaas色eoonnnneettiioonnss建立一个ฐ新า的数据库连接，既快又方แ便，但这其中往往掩藏着重复建立数据库连接带来的巨เ大开销。所以，管理数据库连接必须非常小心。允许多重连接——可能ม就藏在你的应用中ณ——的后果可能很严å重，下面即是一例。
----ๅ---ๅ-ๅ--ๅ-----ๅ-ๅ-ๅ---ๅ---paທge7๕--ๅ-ๅ-ๅ----ๅ-------ๅ-ๅ--ๅ----ๅ-ๅ
不久前，我遇到เ一个ฐ应用，要处理很多小的文本文件。这些文本文件最大的也不过一百行，每一行包含要加载的数据及数据库等信息。此例中ณ固然只有一个数据库实例，但即使有上百个ฐ，这里所说明的原理也是适用的。处理每个文件的代码如下：openthefileuntiltheendoffileisreacນhedreadaro9๗9ecttothe色rverspe9๗色rtthedaທtadis9ectcນlo色thefile上述处理工作令人满意，但当大量小文件都在极短的时间内到达时，可能应用程序来不及处理，于是积压大量待处理文件，花费时间相当可观。我用c语言编了个简单的程序来模拟上述情况，以说明频๗繁的数据库连接和中ณ断ษ所造成的系统性能下降问题。表2-1้列ต出了模拟的结果。注意产生表2-1结果的程序使用了常规的in色rt语句。顺ิ便提一下，直接加载dire9g的技术会更快。表2๐-1้：连接／中断ษ性能测试结果测试结果依次对每一行作连接／中ณ断74๒行秒连接一次，所有行逐个ฐ插入1้6๔8๖1行秒连接一次，以10行为一数组插入5๓914行秒连接一次，以1้00行为ฦ一数组插入9190行秒
-------ๅ------ๅ--ๅ----ๅ-ๅ-ๅ-ๅ-ๅpage8---ๅ--ๅ----ๅ---ๅ-ๅ-ๅ-----ๅ--ๅ--ๅ
此例说明了尽量减少分别连接数据库次数的重要性。对比表中前后两次针ฤ对相同数据库的插入操作，明显现性能有显着提升。其实还可以做进一步的优化。因为数据库实例的数量势必有限，所以可以建立一组处理程序handler分别ี负责一个数据库连接，每个ฐ数据库只连接一次，使性能进一步提高。正如表2๐-ๅ1所示，仅连接数据库一次或很少次的简单技巧，再加上一点额外工作，就能ม让效率提升200倍以上。当然，在上述改进的基础上，再将欲更新า的数据填入数组，这样就尽可能减少了程序和数据库核心间的交互次数，从而使性能产生了另一次飞跃。这种每次插入几行数据的做法，可以使数据的总处理能力又增加了5倍。表2๐-ๅ1中的结果显示改进后的性能ม几乎ๆ是最初的120่0倍。为何有如此大的性能ม提升？第一个原因，也是最大的原因，在于数据库连接是很“重”的操作，消เ耗资源很多。在常见的客户服务器模式中现在仍广为使用，简单的连接操作背后潜藏着如下事实：先，客户端与远程服务器的监听程序listenerprogram建立联系；接着，监听程序要么เ创建一个ฐ进程或线程来执行数据库核心程序，要么เ直接或间接地把客户请求传递给已๐存在的服务器进程，这取决于此服务器是否为ฦ共享服务器。除了这些系统操作创น建进程或线程并开始执行之外，数据库系统还必须ี为每次色ssion建立新า环境，以跟踪它的行为。建立新色ssion前，dbຘms还要检查密码是否与保存的加密的账户密码相符。或许，dbຘms还要执行登录触器logontrigger，还要初始化存储过程和程序包如果它们是第一次被调用。上面这些还不包括客户端进程和服务器进程之间要完成的握手协议。正因为如此，连接池9g等保持永久数据库连接的技术对性能才如此重要。第二个ฐ原因，你的程序甚至包括存储过程和数据库之ใ间的交互也有开销。即使数据库连结已经建立且仍未中断，程序和dbຘms核心之ใ间的上下文切换9text9๗itch也有代价。因此，如果dbຘms支持数据通过数组传递，应毫不犹豫地使用它。如果该数组接口是隐式的aທpi内部ຖ使用，但你不能使用，那么明智的做法是检查它的默认大小并根据具体需要修改它。当然，任何逐行处理的方式都面临上下文切换的问题，并对性能产生严å重影响——本章后面还会多次涉及此问题。总结：数据库连接和交互好似万里长城——长度越长，传递消息越耗时。战略๓优先于战术strategybeforetaທcticsssttrraatteeggyybbຘeeffoorreettaaທttiiss战略决定战术，反之ใ则ท谬也。思考如何处理数据时，有经验的开者不会着眼于细微步骤，而是着眼于最终结果。要获得想要的结果，最显而易见的方法是按照业务规则规定的顺序按部就班地处理，但这不是最有效的方法——接下来的例子将显示ิ，刻意关注业务处理流程可能ม会使
-ๅ-ๅ-ๅ-----ๅ---ๅ-ๅ--ๅ--ๅ-ๅ-ๅ-----page9-ๅ-ๅ--ๅ----ๅ-ๅ-ๅ--ๅ-------ๅ-ๅ--ๅ-ๅ
我们错失最有效的解决方案。几年前，有人给了我一个存储过程，让我“尝试”着进行一下优化。为什么说是“尝试”呢？因为该存储过程已经被优化两ä次了，一次是由á原开者，另一次是由á一个ฐ自称oraທcນle专家的人。但尽管如此，这个存储过程的执行仍会花上2๐0่分钟，使用者无法接受。此存储过程的目的，是根据现有库存和各地订单，计算出总厂需要订购的原料é数量。大体上，它就是把不同数据源的几个相同的表聚合aggregate到เ一个主表mastertaທble中。先，将每个数据源的数据插入主表；接着，对主表中的各项数据进行合计并更新；最后，将与合计结果无关的数据从表中ณ删ฤ除。针对每个数据源，重复执行上述步骤。所有sql语句都不是特别ี复杂，也没有哪个ฐ单独的sql语句特别ี低效。为ฦ了理解这个存储过程，我花了大半天时间，终于现了问题：为什么该过程要用这么เ多步骤呢？在from子句中ณ加上包含union的子查询，就能得到เ所有数据源的聚合aທggregation。一条色lect语句，只需一步就得到เ了结果集，而之ใ前要通过插入目标表taທrgettaທble得到结果集。优化后，性能的提升非常惊人——从2๐0่分钟็减至2๐0秒；当然，之后我花了一些时间验证了结果集，与未优化前完全相同。想要获得上述的大幅提高性能，无需特别技能ม，仅要求站在局外思考thinkoutsidethebox的能力。之前两ä次优化因“太关注问题本身”而收到了干扰。我们需要大胆的思维，站得远一些，试着从大局的角度看待问题。要问自己一些关键的问题：写存储过程之前，我们已๐有哪些数据？我们希望存储过程返回什么เ结果？再辅以大胆的思维，思考这些问题的答案，就能ม得到一个性能大幅提升的处理方式了。总结：考虑解决方案的细节之ใ前，先站得远一些，把握大局。先定义问题，再解决问题problemdefinitionbeforesolutionpprroobblleemmddeeffiinniittiioonnbຘbeeffoorreessoolluuttiioonn一知半解是危险的。人们常在听说了新า技术或特殊技术之后——有时的确很吸引人——试图采用它作为ฦ新的解决方案。普通开者和设计师通常会立即采纳这些新“解决方案”，直到后来才现它们会产生许多后续问题。现成的解决方แ案中，非规范化设计引人注目。设计伊始，非规范化设计的拥护者就提出此方แ案，为了寻求“性能”而无视最终将会面临的升级恶魔——而事实上，在开周期早ຉ期，改进设计或学习如何使用join也是一个不错的选择。作为非规范化设计的一种手段，物化视图materializedvie9๗常被认为是灵丹妙药。物化视图有时被称为ฦ快照snapshot，这个更加平常的词更形象地反映了可悲的事实：物化视图是某时间点的数据副本。在没有其他办法时，这个理论上遭到เ质疑的技术也未尝不值得一试，借用卡夫卡fraທnzkafkaທ的一句名言：“逻辑诚可贵，生存价更高。”然而，绝大部分问题都可借助传统技术巧妙解决。先，应学会充分利用简单、传统的技术。只有完全掌握了这些技术，才能正确评价它们的局限性，最终现它相当于新技术的潜在优势如果有的话。所有技术方案，都只是我们达到目标的手段。没有经验的开者误把新า技术本身当成了目标。
--------ๅ----ๅ--ๅ----ๅ--ๅ-ๅ-ๅ-page10-ๅ--ๅ----ๅ----ๅ-ๅ-ๅ------ๅ----
对于热衷于技术、过于看重技术的人来说，此问题就更为严å重。总结：先打基础，再赶时髦：摆弄新工具之ใ前，先把手艺学好。直接操作实际数据operationsaທgainstaທcນtualdataooppeerraaທttiioonnssaທaggaທaທiinnssttaattuuaທallddaທattaa许多开者喜欢建立临ภ时工ื作表temporary9๗orktable，把后续处理使用的大量数据放入其中，然后开始“正式”工ื作。这种方法广受质疑，反映了“跳出业务流程细节考虑问题”的能力不足。记住，永久ื表permanenttable可以设置非常复杂的存储选项在第5章将讨论一些存储选项ำ的设置，而临时表不能ม。临时表的索ิ引如果有的话可能不是最优的，因此，查询临ภ时表的语句效率比永久ื表的差ๆ。另外，查询之前必然先为临时表填入数据，这自然也多了一笔额外的开销。就算使用临时表有充足理由，若数据量大，也绝不能ม把永久表当作临ภ时工作表来用。问题之一在于统计信息的自动收集：若没有实时收集要求，dbms通常会在不活动或活动少时进行统计信息收集，而这时作为临时工作表可能为空，从而使优化器收到了完全错误的信息。这些不正确且有偏差的统计信息可能造成执行计划ฐexe9๗完全不合理，导致性能下降。所以，如果一定要用临时表，应确保数据库知道哪些表是临时的。总结：暂时工ื作表意味着以不太合理的方แ式存储更多信息。sql用ssqqll处理集合色tpro9๗sqls色ettpprrooeessssiinnggiinnssqqllsql完全基于集合色t来处理数据。对大部ຖ分更新า或删除操作而言——如果不是针对整个表的话——你必须ี先精确定义แ出要处理的记录的集合。这定义了该处理的粒度graທnularity，可能是对大量记录的粗粒度操作，有可能ม是只影响少数记录的细粒度操作。将一次“大批量数据的处理”分割成多次“小块处理”是个坏主ว意，除非对数据库的修改太昂贵，否则ท不要使用，因为这种方法极其低效：1占用过多的空间保存原始数据，以备事务traທnsa9回滚rollbacນk之需；2万一修改失败，回滚消耗过长的实践。许多人认为ฦ，进行大规模修改操作时，应在操作数据的代码中有规律地多安排些mit命令。其实，严格从实践角度来讲，“从头开始重做”比“确定失败生的时间和位置，接着已提交部分重做”要容易得多、简单得多、也快得多。处理数据时，应适应数据库的物理实现。考虑事务失败时回滚所需日志的大小，如果要为ฦundo保存的数据量确实巨大，或许应该考虑数据修改的频率问题。也就是说，将大规模的“每月更新า”，改为规模不大的“每周更新า”，甚至改为规模更小的“每日更新”，或许是个有效方案。总结：几千个语句，借助游标cursor不断ษ循环，很慢。换成几个ฐ语句，处理同样的数据，还是较慢。换成一个语句，解决上述问题，最好。
-----ๅ---ๅ-ๅ--ๅ-----ๅ-ๅ-ๅ---ๅ--page11้--ๅ-ๅ-ๅ----ๅ-------ๅ-ๅ--ๅ----ๅ-ๅ
sql动作丰富的ssqqll语句aທ9-ๅpa9tsaattiioonn-ๅ-ppaakkeeddssqqllssttaatteemmeennttsssql不是过程性语言pro9guaທge，尽管也可以将过程逻辑proceduraທllogicນ用于sql，但必须小心。混淆声明性处理de9g和过程逻辑，最常见的例子出现在需要从数据库中提取数据、然后处理数据、然后再插入到数据库时。在一个ฐ程序或程序中ณ的一个函数接收到特定输入值后，如下情况太常见了：用输入值从数据库中ณ检索到一个ฐ或多个另外的数据值，然后，借助循环或条件逻辑通常是ifthenel色将一些语句组织起来，对数据库进行操作。大多数情况下，造成上述错误做法的原因有三：根深蒂固的坏习惯、sql知识的缺乏็、盲从功能需求规格说明。其实，许多复杂操作往往可由一条sql语句完成。因此，如果用户了一些数据值，尽量不要将操作分解为多条提取中ณ间结果的语句。避免在sql中ณ引入“过程逻辑procedurallogicນ”的主要原因有二。数据库访问，总会跨多个ฐ软件层，甚至包括网络访问。即使没有网络访问，也会涉及进程间通讯；额外的存取访问意味着更多的函数调用、更大的带宽，以及更长的等待时间。一旦ຆ这些调用要重复多次，其对性能的影响就非常可观了。在sql中引入过程逻辑，意味着性能ม和维护问题应由á你的程序承担。大多数据库系统都了成熟的算法，来处理join等操作，来优化查询以获得更高的效率。基于开销的优化器cນost-bຘaທ色doptimizer，cbo是很复杂的软件，它早已๐不像刚推出时那样没什么用了，而在大部分情况下都是非常出色的成熟产品了，优秀的cbຘo查询优化的效率极高。然而，cbຘo所能改变的只有sql语句。如果在一条单独的sql语句中ณ完成尽可能多的操作，那么เ性能优化可以还由ádbms核心负责，你的程序可以充分利用dbms的所有升级。也就是说，未来大部分维护工作从程序间接转移给了dbms供货商。当然，“避免在sql中ณ引入过程逻辑”规则ท也有例外。有时过程逻辑确实能加快处理度，庞大的sql语句未必总是高效。然而，过程逻辑及其之后的处理相同数据的语句，可以编写到เ一个单独的sql语句中，cbo就是这么เ做的，从而获得最高效的执行方แ式。总结：尽可能ม多地把事情交给数据库优化器来处理。充分利ำ用每次数据库访问profitaທbledataba色aທes色spprrooffiittaaທbຘblleeddaທattaabbaaທs色eaaeesss色ess如果计划ฐ逛好几家商店，你会先决定在每家店买哪些东西。从这一刻起，就要计划按何种顺ิ序购物才能少走冤枉路。每逛一家店，计划ฐ东西购买完毕，才逛下一家。这是常识，但其中蕴含的道理许多数据库应用却不懂得。要从一个表中ณ提取多段信息时，采用多次数据库访问的做法非常糟糕，即使多段信息看似“无关”但事实上往往并非如此。例如，如果需要多个字段的数据，千万不要逐个ฐ字段地提取，而应
--ๅ-ๅ--ๅ-----ๅ-ๅ-ๅ-------ๅ-ๅ-ๅ--ๅpaທge12------ๅ---ๅ-ๅ--ๅ----ๅ-ๅ-ๅ----ๅ-
一次操作全部ຖ完成。很不幸，面向对象oo的最佳实践提倡为ฦ每个ฐ属性定义一个get方แ法。不要把oo方แ法与关系数据库处理混为一谈。混淆关系和面向对象的概念，以及将表等同于类、字段等同于属性，都是致命的错误。总结：在合理范围内，利ำ用每次数据库访问完成尽量多的工ื作。dbຘms接近ddbຘbຘmmss核心9ellloos色enneessssttootthheeddbbmmskeerrnneell代码的执行越接近dbms核心，则ท执行度越快。数据库真正强大之处就在于此，例如，有些数据库管理产品支持扩展，你可以用c等较底层的语言为ฦ它编写新功能ม。用含有指针操作的底层语言有个ฐ缺点，即一旦指针处理出错会影响内存。仅影响到เ一个用户已很糟糕，何况数据库服务器就像“服务器”名字所指的一样出了问题会影响众多“用户”——服务器内存出了问题，所有使用这些数据的无辜的应用程序都会受影响。因此，dbຘms核心采取了负责任的做法，在沙箱sandbox环境中ณ执行程序代码，这样，即使出了问题也不会影响到数据。例如，ora99๗和它自身之间实现了一套复杂的通信机制，此机制在某些方面很像控制数据库连结的方法，以管理两ä个或多个ฐ服务器上的数据库实例之间的通信。到เ底采用plsql存储过程还是外部c函数，应综合比较后决定。如果精心编写外部ຖc函数获得的好处过了建立外部ຖ环境和上下文切换9g的成本，就应采用外部ຖ函数。但需要处理一个ฐ大数据量的表的每一行时，不要使用外部ຖ函数。这需要平衡考虑，解决问题时应完全了解备选策略的后果。如要使用函数，始终应选dbms自带的函数。这不仅仅是为ฦ了避免无谓的重复劳动，还因为自带函数在执行时比任何第三方แ开的代码更接近数据库核心，相应地其效率也会高出许多。下面这个简单例子是用oraທclesql编写的，显示ิ了使用oraທcນle函数所获得的效率。假设手工输入的文本数据可能包含多个相邻๑的“空格”，我们需要一个ฐ函数将多个ฐ空格替换为一个ฐ空格。如果不采用oraທcນledaທtabaທ色10g开始的正规表达式regularexpression，函数代码将会是这样：99gi女aທr9๗vaທr9๗gvar9number:๘=lengthp_string;๙i逼naທry_ຕinteger:=1;j逼naທry_integer;bຘegin9hilei0loopv_string:=substrv_string,ไ1,ไi||ltrimsubstrv_ຕstring,i+1;i:=instrv_string,ไ'';endloop;retur女_string;๙end;
----ๅ-ๅ-ๅ---ๅ---ๅ-ๅ--ๅ----ๅ-ๅ-ๅ--paທge14-ๅ-ๅ--ๅ-ๅ-ๅ-ๅ-----ๅ-------ๅ-ๅ--ๅ-
还有第三种方แ法：99gi女ar9varcນhaທr2isv_stringvaທr9g;len1numbຘer;๙len2๐numbຘer;beginlen1้:๘=lengthp_string;v_ຕstring:=replaທ9g,'','ู'ู;๙len2:=lengthv_ຕstring;9hilelen2色lecນtsqueeze1้'ูaທzerythgfrdtr'2๐fromdual3azerythgfrdtrelap色d:0่0:๘00:0่000่sql色lectsqueeze2'aທzerythgfrdtr'2fromduaທl3๑aທzerythgfrdtrelaທp色d:๘0่0:0่0:0001sql色lectsqueeze3'aທzerythgfrdtr'2fromdual3azerythgfrdtrelap色d:๘00:00่:0000
---------ๅ--ๅ----ๅ-ๅ-ๅ-ๅ-----paທge15๓----ๅ---ๅ-ๅ-ๅ---ๅ----ๅ--ๅ----ๅ-
那么เ，如果每天要调用该空格替换操作几千次呢？我们构造一个接近现实负载的环境，下面的代码将建立一个用于测试的表并填入随机数据，已๐检测上面三个函数是否有性能差ๆ异：creaທtetablesqueezaທbຘlerandom_ຕtextvaທrcນhaທr2๐50de9ary_ຕinteger;j逼nary_ຕinteger;k逼naທry_integer;v_stringvare1,ไ1้00่;v_string:=dbms_ຕraທndomstring'u',5๓0่;9๗hilej9hen9stillsomethingel色el色
--------ๅ--ๅ--ๅ----ๅ-----ๅ-ๅ-ๅpage1้9-ๅ----ๅ--ๅ-ๅ-ๅ--ๅ------ๅ--ๅ----ๅ
end数值或日期的比较则简单明了。操作字符串ธ可以用oraທcນle的greatest或leaທst，或者mysql的str9色rt语句增加过程逻辑，具体办法是多重in色rt及条件in色rt注3，并借助merge语句。如果dbຘms了这样语句，毫不犹豫地使用它。也就是说，有许多逻辑可以放入sql语句中ณ；虽然仅执行多条语句中的一条这种逻辑价值不大，但如果设法利用caທ色、merge或类似功能ม将多条语句合并成一条，价值可就大了。总结：只要有可能，应尽量把条件逻辑放到sql语句中，而不是sql的宿主ว语言中。一次完成多个ฐ更新multipleupdatesaທtoncemmuullttiipplleeuuppddaທatteessaທaທttoonnee我的基本主张是：如果每次更新的是彼此无关的记录，对一张表连续进行多次updaທte操作还可以接受；否则，就应该把它们合并成一个updaທte操作。例如，下面是来自实际应用的一些代码注4：updatetbຘo_i女oicນe_ຕextractor色tpgaທ_ຕstaທtus=0pdatetbຘo_i女oice_extracນtor色trd_ຕstatus=09hererd_ຕstatusin1,3aທndi女_type=0;两个连续的更新า是对同一个ฐ表进行的。但它们是否将访问相同的记录呢？不得而知。问题是，搜索条件的效率有多高？任何名为type或status的字段，其值的分布通常是杂乱ກ无章的，所以上面两个update语句极可能ม对同一个表连续进行两次完整扫描：一个ฐupdate有效地利用了索ิ引，而第二个update不可避免地进行全表扫描；或者，幸运的话，两次updaທte都有效地利用了索ิ引。无论如何，把这两ä个ฐupdate合并到一起，几乎不会有损失，只会有好处：updaທtetbo_i女oice_extraທcນtor色tpga_staທtus=9๗1้then09๗hen3๑then0el色pgaທ_ຕstatus
-----ๅ-ๅ--ๅ---ๅ-ๅ-ๅ-------ๅ---ๅpage20--ๅ-ๅ-ๅ---ๅ-----ๅ-ๅ--ๅ--ๅ-ๅ-ๅ----ๅ
end,rd_status=91then09๗hen3๑then0el色rd_statusendsin1้,3๑andi女_ຕtype=0;上例中ณ，可能出现重复更新า相同字段为相同内容的情况，这的确增加了一小点儿开销。但在多数情况下，一个ฐupdaທte会比多个update快得多。注意上例中ณ的“逻辑logic”，我们通过ca色语句实现了隐式的条件逻辑impli9๗allogic，来处理那ว些符合更新条件的数据记录，并且更新า条件可以有多条。总结：有可能的话，用一个语句处理多个ฐ更新；尽量减少对同一个ฐ表的重复访问。慎用自定义函数carefulu色ofu色r-9๗rittenfun9s将自定义函数u色r-9rittenfun9๗嵌到sql语句后，它可能ม被调用相当多次。如果在色lecນt语句的选出项列ต表中ณ使用自定义函数，则ท每返回一行数据就会调用一次该函数。如果自定义函数出现在9here子句中ณ，则每一行数据要成功通过过滤条件都会调用一次该函数；如果此时其他过滤条件的筛选能力不够强，自定义函数被调用的次数就非常可观了。如果自定义函数内部ຖ还要执行一个查询，会生什么情况呢？每次函数调用都将执行此内部查询。实际上，这和关联子查询correlatedsubຘquery效果相同，只不过自定义แ函数的方แ式阻碍了基于开销的优化器cນost-ba色doptimizer，cນbo对整个ฐ查询的优化效果，因为“子查询”隐藏在函数中ณ，数据库优化器鞭长莫及。下面举例说明将sql语句隐藏在自定义แ函数中ณ的危险性。表flights描述商务航班，有航班号、起飞时间、到เ达时间及机场iaທta代码注5等字段。iaທta代码均为三个字母，有900่0่多个ฐ，它们的解释保存在参照表中，包含城市名称若一个城市有多个机场则应为ฦ机场名称、国家名称等。显然，显示航班信息时，应该包含目的城市的机场名称，而不是简单的iata代码。在此就遇到เ了现代软件工程中的矛盾之一。被认为是“优良传统”的模块化编程一般情况下非常适用，但对数据库编程而言，代码是开者和数据库引擎的共享活动sharedaທctivity，模块化要求并不明确。例如，我们可以遵循模块化原则编写一个ฐ小函数来查找iata代码，并返回完整的机场名称：9๗9๗airport_9๗9๗vaທrcນhar2is
-----ๅ-ๅ--ๅ---ๅ-ๅ-ๅ-ๅ---------ๅpage21--ๅ-ๅ-ๅ-----ๅ---ๅ-ๅ--ๅ--ๅ-ๅ-ๅ----
9amevar9๗amefromiata_ຕairport_codes9herecode=iataທ_ຕ9๗ame;end;对于不熟悉oracle语法的读者，在此做个ฐ说明，以下查询中truncsysdaທte的返回值为ฦ“今天的00:00่aທm”，日期计算以天为单位；所以起飞时间的条件是指今天8๖:3๑0am至4๒:๘0่0pm之间。调用aທirport_city函数的查询可以非常简单，例如：色le9๗umber,ไto_chaທrdeparture_time,ไ'hh24๒:๘mi'depaທrture,airport_cityaທrrival"to"ิfromflights9heredepaທrture_ຕtimebet9eentrunre_time这个查询的执行度令人满意；在我机器上的随机样本中ณ，返回77行数据只用了018๖秒多次执行的平均值，用户对这样的度肯定满意统计数据表明，此处理访问了303个数据块，5๓3๑个ฐ是从磁盘读出的——而且每行数据有个递归调用。我们还可以用join来重写这段代码，作为查找函数的替代方案，当然它看起来会稍微复杂些：色le9๗umber,ไto_charfdepaທrture_ຕtime,'ูhh24๒:๘mi'ูdepaທrture,ไaທcນity"ิto"fromflightsf,ไiata_airport_codesa9๗hereaທ9๗ddeparture_timebet9eentrunre_time
-ๅ-ๅ-ๅ-------ๅ-ๅ--ๅ-----ๅ-ๅ-ๅ---page2๐2๐--ๅ--ๅ-ๅ-ๅ--------ๅ---ๅ-ๅ--ๅ---
这个查询只用了005秒统计数据同前，但没有递归调用。对于执行时间不到02秒的查询来说，度快了3๑倍似乎无关紧要，但在大型系统中，这些查询每天经常执行数十万次——假设以上查询每天只执行五万次，于是查询的总耗时为ฦ2๐5๓小时。若不使用上述查找函数lookupfun9则只需要不到42๐分钟，度提高过3๑0่0่%，这对大数据量的系统意义แ重大，最终带来经济上的节约。通常，使用查找函数会使批处理程序的性能极差ๆ。而且查询时间的增加，会使同一台机器支持的并用户数减少，我们将在第9章对此展开讨论。总结：优化器对自定义แ函数的代码无能ม为力。sql简洁的ssqqllsuinctsql熟练的开者使用尽可能少的sql语句完成尽可能ม多的事情。相反，拙劣的开者则倾向于严å格遵循已制订好的各功能步骤，下面是个ฐ真实的例子：--getthestaທrtoftheaທountingperiod色le9todtperstafromtperrslt9herefiscaທl_yeaທr=to_charpaທraທm_ຕdta,'yyyy'andrslt_period='1'||to_cນhaທrparam_dta,'mm';๙--gettheendoftheperiodoutofcນlosure色le9๗todtperclosurefromtperrslt9herefiscນaທl_ຕyear=to_ຕcharpaທraທm_dta,ไ'yyyy'ูaທndrslt_period='ู9'||to_charparam_dta,ไ'ูmm';就算度可以接受，这也是段极糟的代码。很不幸，性能专家经常遇到这种糟糕的代码。既ຂ然两个ฐ值来自于同一表，为什么要分别用两ä个ฐ不同的语句呢？下面用oraທcນle的bulkcollecນt子句，一次性将两个值放到数组中，这很容易实现，关键在于对rslt_period进行orderby操作，如下所示ิ：色lecນtcນlosure_daທtebulk9todtperstaທaທrrayfromtperrslt9๗herefiscaທl_year=to_ຕcharpaທram_dta,ไ'yyyy'ูandrslt_periodin'1้'||to_charparam_ຕdta,'mm'ู,
-----ๅ-ๅ-ๅ--ๅ-ๅ-ๅ-ๅ-ๅ--ๅ--ๅ----ๅ--paທge23๑----ๅ--ๅ----ๅ-ๅ-ๅ-ๅ---ๅ------ๅ-
'ู9'ู||to_ຕcharparam_dta,'mm'orderbຘyrslt_period;๙于是，这两个日期被分别保存在数组的第一个和第二个位置。其中，bulkcollect是plsql语言特有的，但任何支持显式或隐式数组提取的语言都可如法炮制ๆ。其实甚至数组都是不必要的，用以下的小技巧注6，这两个值就可以被提取到เ两ä个ฐ变量中：色lectmaທxdecodesubstrrslt_ຕperiod,1,1,-ๅ-checນkthefirstcharacter'ู1'ู,ไcນlosure_date,-ๅ-ๅifit'ูs'ู1'returnthedaທte9e9๗aທntto_date'ู14๒10่1้0่66',ไ'ddmmyyyy'ู,--other9i色somethingoldmaxdecodesubຘstrrslt_period,ไ1้,1,ไ'9',closure_ຕdate,ไ--thedaທte9e9antto_date'ู1้4๒101066๔','ูddmmyyyy',intodtpersta,dtpercນlosurefromtperrslt9herefiscaທl_ຕyear=to_charpaທraທm_dtaທ,ไ'yyyy'andrslt_ຕperiodin'1'||to_ຕchaທrpaທram_dta,'ูmm'ู,'9'||to_chaທrpaທram_dtaທ,'mm';๙在这个例子中ณ，预ไ期返回值为ฦ两ä行数据，所以问题是：如何把原本属于一个字段的两行数据，以一行数据两ä个ฐ字段的方式检索出来正如数组提取的例子一样。为此，我们检查rslt_period字段，两行数据的rslt_period字段有不同值；如果找到เ需要的记录，就返回要找的日期；否则ท，就返回一个ฐ在任何情况下都远比我们所需日期要早的日期此处选了哈斯丁之ใ役battleofhastings的日期。只要每次取出最大值，就可以确保获得需要的日期。这是个非常实用的技巧ู，也可以应用在字符或数值数据，第1้1้章会有更详细的说明。总结：sql是声明性语言de9guage，所以设法使你的代码越业务过程的规格说明。sqlssqqll的进攻式编程offensive9g9๗ithsql一般的建议是进行防御式编程9๗sively，在开始处理之前先检查所有参数的合法性。但实际上，对数据库编程而言，尽量同时做几件事情的进攻式编程有切实的优势。有个ฐ很好的例子：进行一连串检查，每当其中ณ一个ฐ检查所要求的条件不符时就产生异常。信用卡付款的处理中ณ就涉及类似步骤。例如，检查所提交的客户身份和卡号是否有效，以及两ä者是否匹配；检查信用卡是否过期；最后，检查当前的支付额是否过了信用额度。如果通过了所
------ๅ--ๅ-ๅ-ๅ--ๅ---------ๅ-ๅ-page2๐4๒-ๅ------ๅ---ๅ-ๅ--ๅ-ๅ-ๅ-ๅ-------ๅ
有检查，支付操作才继续进行。为了完成上述功能ม，不熟练的开者会写出下列语句，并检查其返回结果：色le9tfromcustomers9๗herecນustomer_ຕid=provided_id接