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

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



？…………………………………………………………Page　1……………………………………………………………

　　　　　　　　　　　　　　　　　　　SQL　

　　　　　　　　　　　　　　　　　　　SSQQLL语言艺术　



内容介绍　



本书分为12章，每一章包含许多原则或准则，并通过举例的方式对原则进行解释说明。这些例　

子大多来自于实际案例，对九种SQL经典查询场景以及其性能影响讨论，非常便于实践，为你　

的实际工作提出了具体建议。本书适合SQL数据库开发者、软件架构师，也适合DBA，尤其是　

数据库应用维护人员阅读。　



　　资深　SQL　专家　Stéphane　Faroult倾力打造　

　　《软件架构设计》作者温昱最新译作　

　　巧妙借鉴《孙子兵法》的智慧结晶　

　　传授25年的SQL性能与调校经验　

　　深入探讨九种常见查询方案及其性能　



前言　



过去，“信息技术（IT）”的名字还不如今天这般耀眼，被称为“电子数据处理”。其实，尽管当　

今新潮技术层出不穷，数据处理依然处于我们系统的核心地位，而且需管理的数据量的增长速　

度似乎比处理器的增长速度还快。今天，最重要的集团数据都被保存在数据库中，通过SQL语　

言来访问。SQL语言虽有缺点，但非常流行，它从1980年代早期开始被广泛接受，随后就所向　

无敌了。　



如今，年轻开发者在接受面试时，没有谁不宣称自己能熟练应用SQL的。SQL作为数据库访问　

语言，已成为任何基础IT课程的必备部分。开发者宣传自己熟练掌握SQL，其实前提是“熟练掌　

握”的定义是“能够获得功能上正确的结果”。然而，全世界的企业如今都面临数据量的爆炸式增　

长，所以仅做到“功能正确”是不够的，还必须足够快，所以数据库性能成了许多公司头疼的问　

题。有趣的是，尽管每个人都认可性能问题源自代码，但普遍接受的事实则是开发者的首要关　

注点应该是功能正确。人们认为：为了便于维护，代码中的数据库访问部分应该尽量简单；“拙　

劣的SQL”应该交给资深的DBA去摆弄，他们还会调整几个“有魔力”的数据库参数，于是速度就　

快了——如果数据库还不够快，似乎就该升级硬件了。　



往往就是这样，那些所谓的“常识”和“可靠方法”最终却是极端有害的。先写低效的代码、后由　

专家调优，这种做法实际上是自找麻烦。本书认为，首先要关注性能的就是开发者，而且SQL　

问题绝不仅仅只包含正确编写几个查询这么简单。开发者角度看到的性能问题和DBA从调优角　

度看到的大相径庭。对DBA而言，他尽量从现有的硬件（如处理器和存储子系统）和特定版本　

的DBMS获得最高性能，他可能有些SQL技能并能调优一个性能极差的SQL语句。但对开发者而　

言，他编写的代码可能要运行5到10年，这些代码将经历一代代的硬件，以及DBMS各种重要版　


…………………………………………………………Page　2……………………………………………………………

本升级（例如支持互联网访问、支持网格，不一而足）。所以，代码必须从一开始就快速、健全。　

很多开发者仅仅是“知道”SQL而已，他们没有深刻理解SQL及关系理论，实在令人遗憾。　



为何写作本书　

SQL书主要分为三种类型：讲授具体SQL方言的逻辑和语法的书、讲授高级技术及解决问题方　

法的书、专家与资深DBA所需的性能和调优的书。一方面，书籍要讲述如何写SQL代码；另一　

方面，要讲如何诊断和修改拙劣的SQL代码。在本书中，我不再为新手从头讲解如何写出优秀　

的SQL代码，而是以超越单个SQL语句的方式看待SQL代码，无疑这更加重要。　



教授语言使用就够难了，那么本书是怎样讲述如何高效使用SQL语言的呢？SQL的简单性具有　

欺骗性，它能支持的情况组合的数目几乎是无限的。最初，我觉得SQL和国际象棋很相似，后　

来，我悟到发明国际象棋是为了教授战争之道。于是，每当出现SQL性能难题的时候，我都自　

然而然地将之视为要和一行行数据组成的军队作战。最终，我找到了向开发者传授如何有效使　

用数据库的方法，这就像教军官如何指挥战争。知识、技能、天赋缺一不可。天赋不能传授，　

只能培养。从写就了《孙子兵法》的孙子到如今的将军，绝大多数战略家都相信这一点，于是　

他们尽量以简单的格言或规则的方式表达沙场经验，并希望这样能指导真实的战争。我将这种　

方法用于战争之外的许多领域，本书借鉴了孙子兵法的方法和书的题目。许多知名IT专家冠以　

科学家称号，而我认为“艺术”比“科学”更能反映IT活动所需的才能、经验和创造力（注1）。很　

可能是由于我偏爱“艺术”的原因，“科学”派并不赞成我的观点，他们声称每个SQL问题都可通　

过严格分析和参考丰富的经验数据来解决。然而，我不认为这两种观点有什么不一致。明确的　

科学方法有助于摆脱单个具体问题的限制，毕竟，SQL开发必须考虑数据的变化，其中有很大　

的不确定性。某些表的数据量出乎意料地增长将会如何？同时，用户数量也倍增了，又将会如　

何？希望数据在线保存好几年将会如何？如此一来，运行在硬件之上的这些程序的行为是否会　

完全不同？架构级的选择是在赌未来，当然需要明确可靠的理论知识——但这是进一步运用艺　

术的先决条件。第一次世界大战联军总司令Ferdinand　Foch，在1900年French　Ecole　Supérieure　de　

Guerre的一次演讲中说：　



战争的艺术和其他艺术一样，有它的历史和原则——否则，就不能成其为艺术。　



本书不是cookbook，不会列出一串问题然后给出“处方”。本书的目标重在帮助开发者（和他们　

的经理）提出犀利的问题。阅读和理解了本书之后，你并不是永不再写出丑陋缓慢的查询了——　

有时这是必须的——但希望你是故意而为之、且有充足的理由。　



目标读者　

本书的目标读者是：　

　　有丰富经验的SQL数据库开发者　

　　他们的经理　

　　数据库占重要地位的系统的软件架构师　

我希望一些DBA、尤其是数据库应用维护人员也能喜欢本书。不过，他们不是本书的主要目标　

读者。　



本书假定　


…………………………………………………………Page　3……………………………………………………………

本书假定你已精通SQL语言。这里所说的“精通”不是指在你大学里学了SQL　101并拿来A＋的成　

绩，当然也并非指你是国际公认的SQL专家，而是指你必须具有使用SQL开发数据库应用的经　

验、必须考虑索引、必须不把5000行的表当大表。本书的目标不是讲解连接、外连接、索引的　

基础知识，阅读本书过程中，如果你觉得某个SQL结构还显神秘；并影响了整段代码的理解，可　

先阅读几本其他SQL书。另外，我假定读者至少熟悉一种编程语言；并了解计算机程序设计的基　

本原则。性能已很差、用户已抱怨、你已在解决性能问题的前线，这就是本书的假定。　



本书内容　



我发现SQL和战争如此相像，以至于我几乎沿用了《孙子兵法》的大纲；并保持了大部分题目名　

称（注2）。本书分为12章，每一章包含许多原则或准则，并通过举例的方式对原则进行解释说　

明，这些例子大多来自于实际案例。　

第1章，制定计划：为性能而设计　

讨论如何设计高性能数据库　

第2章，发动战争：高效访问数据库　

解释如何进行程序设计才能高效访问数据库　

第3章，战术部署：建立索引　

揭示为何建立索引，如何建立索引　

第4章，机动灵活：思考SQL语句　

解释如何设计SQL语句　

第5章，了如指掌：理解物理实现　

揭示物理实现如何影响性能　

第6章，锦囊妙计：认识经典SQL模式　

包括经典的SQL模式、以及如何处理　

第7章，变换战术：处理层次结构　

说明如何处理层次数据　

第8章，孰优孰劣：认识困难，处理困难　

指出如何认识和处理比较棘手的情况　

第9章，多条战线：处理并发　

讲解如何处理并发　

第10章，集中兵力：应付大数据量　

讲解如何应付大数据量　

第11章，精于计谋：挽救响应时间　

分享一些技巧，以挽救设计糟糕的数据库的性能　

第12章，明察秋毫：监控性能　

收尾，解释如何定义和监控性能　



本书约定　

本书使用了如下印刷惯例：　

等宽（Courier）　

表示SQL及编程语言的关键字，或表示table、索引或字段的名称，抑或表示函数、代码及命令　


…………………………………………………………Page　4……………………………………………………………

输出。　

等宽黑体（Courier）　

表示必须由用户逐字键入的命令等文本。此风格仅用于同时包含输入、输出的代码示例。　

等宽斜体（Courier）　

表示这些文本，应该被用户提供的值代替。　

总结：箴言，概括重要的SQL原则。　



注意　



提示、建议、一般性注解。为相关主题提供有用的附加信息。　



代码示例　



本书是为了帮助你完成工作的。总的来说，你可以将本书的代码用于你的程序和文档，但是，　

若要大规模复制代码，则必须联系O'Reilly申请授权。例如：编程当中用了本书的几段代码，无　

需授权；但出售或分发O'Reilly书籍中案例的CD…ROM光盘，需要授权。再如：回答问题时，引　

用了本书或其中的代码示例，无需授权；但在你的产品文档中大量使用本书代码，需要授权。　



O'Reilly公司感谢但不强制归属声明。归属声明通常包括书名、作者、出版商、ISBN。例如“The　Art　

of　SQLbyStéphane　Faroult　with　PeterRobson。　Copyright　（c）2006　O'ReillyMedia；　0…596…00894…5”。　



如果你对代码示例的使用超出了上述范围，请通过　permissions@oreilly。　联系出版商。　



评论与提问　



我们已尽力核验本书所提供的信息，尽管如此，仍不能保证本书完全没有瑕疵，而网络世界的　

变化之快，也使得本书永不过时的保证成为不可能。如果读者发现本书内容上的错误，不管是　

赘字、错字、语意不清，甚至是技术错误，我们都竭诚虚心接受读者指教。如果您有任何问题，　

请按照以下的联系方式与我们联系。　

O'ReillyMedia；　Inc。　

1005　Gravenstein　Highway　North　

Sebastopol；　CA　95472　

（800）　998…9938　（in　the　U。S。　or　Canada）　

（707）　829…0515　（international　or　local）　

（707）　829…0104　（fax）　



致谢　



本书原版用英语写作，英语既不是我的家乡话，又不是我所在国家的语言，所以写这样一本书　

要求极度乐观（回想起来几近疯狂）。幸运的是，Peter　Robson不仅为本书贡献了他在SQL和数　

据库设计方面的知识，也贡献了持续的热情来修改我冗长的句子、调整副词位置、斟酌替换词　

汇。Peter　Robson和我在好几个大会上都碰过面，我们都是演讲者。　



Jonathan　Gennick担任本书编辑，这有点让人受宠若惊，Jonathan　Gennick是O'Reilly出版的　

SQL　Pocket　Guide等畅销名著的作者。Jonathan是个非常尊重作者的编辑。由于他的专业、他　

对细节的关注、他的犀利视角，使本书的质量大大提升。同时，Jonathan也使本书的语言更具“中　

大西洋”风味（Peter和我发现，虽然我们保证按美国英语拼写，但还远远不够）。　


…………………………………………………………Page　5……………………………………………………………

我还要感谢很多人，他们来自三个不同的大陆，阅读了本书全部或部分草稿并坦诚地提出意见。　

他们是：Philippe　Bertolino、Rachel　Carmichael、Sunil　CS、LarryElkins、Tim　Gorman、Jean

Paul　Martin、Sanjay　Mishra、Anthony　Molinaro、Tiong　Soo　Hua。我特别感激Larry，因为本　

书的思想最初来自于我们的E…mail讨论。　



我也要感谢O'Reilly的许多人，他们使本书得以出版。他们是：Marcia　Friedman、Rob　Romano、　

Jamie　Peppard、Mike　Kohnke、Ron　Bilodeau、Jessamyn　Read、Andrew　Savikas。感谢Nancy　

Reinhardt卓越的手稿编辑工作。　



特别感谢Yann…Arzel　Durelle…Marc慷慨提供第12章用到的图片。感谢Paul　McWhorter授权我们　

将他的战争图用于第6章。　



最后，感谢Roger　Manser和Steel　Business　Briefing的职员，他们为Peter和我提供了位于伦敦　

的办公室（还有大量咖啡）。感谢Qian　Lena　（Ashley）提供了本书开始引用的《孙子兵法》的中　

文原文。　



作者介绍　



Stéphane　Faroult从1983年开始接触关系数据库。Oracle法国成立早期他即加入（此前是短暂的　

IBM经历和渥太华大学任教生涯），并在不久之后对性能和调优产生了兴趣。1988年他离开了　

Oracle，此后一年间，他进行调整，并研究过运筹学。之后，他重操旧业，一直从事数据库咨　

询工作，并于1998年创办了RoughSea公司（http：//　roughsea。）。　



Stéphane　Faroult出版了FortranStructuréet　Méthodes　Numériques一书（法语，Dunod出版社，1986；　

与Didier　Simon合作），并在Oracle　Scene和Select（分别为英国和北美Oracle用户组杂志）以及　

Oracle杂志在线版上发表了许多文章。他还是美国、英国、挪威等众多用户组大会的演讲者。　



PeterRobson毕业于达拉谟大学地质专业（1968年），然后在爱丁堡大学任教，并于1975年获得　

地质学研究型硕士学位。在希腊度过了一段地质学家生涯之后，他开始在纽卡斯尔大学专攻地　

质和医学数据库。　



他使用数据库始于1977年，1981年开始使用关系数据库，1985年开始使用Oracle，这期间担任　

过开发工程师、数据架构师、数据库管理员等角色。1980年，Peter参加了英国地质普查，负责　

指导使用关系数据库管理系统。他擅长SQL系统，以及从组织级到部门级的数据建模。Peter多　

次出席英国、欧洲、北美的Oracle数据库大会，在许多数据库专业杂志上发表过文章。他现任　

英国Oracle用户组委员会主任，可通过peter。robson@justsql。联系他。　



查询的识别　


…………………………………………………………Page　6……………………………………………………………

有经验的朋友都知道，把关键系统从开发环境切换到生产环境是一场战役，一场甚嚣尘上的战　

役。通常，在“攻击发起日（D…Day）”的前几周，性能测试会显示新系统达不到预期要求。于是，　

找专家，调优SQL语句，召集数据库管理员和系统管理员不断开会讨论对策。最后，性能总算　

与以前的系统大致相当了（尽管新系统用的是价格翻倍的硬件）。　



人们常常使用战术，而忽略了战略。战略要求从大局上把握整个架构与设计。和战争一样，战　

略的基本原则并不多，且经常被忽视。架构错误的代价非常高，SQL　程序员必须准备充分，明　

确目标，了解如何实现目标。在本章中，我们讨论编写高效访问数据库的程序需要实现哪些关　

键目标。　



查询的识别　



Query　Identification　

QQuueerryy　IIddeennttiiffiiccaattiioonn　



数个世纪以来，将军通过辨别军装颜色和旗帜等来判断各部队的位置，以此检查激战中部队行　

进情况。同样，当一些进程消耗了过多的　CPU　资源时，通常也可以确定是由哪些正被执行的　

SQL　语句造成的。但是，要确定是应用的哪部分提交了这些SQL语句却困难得多，特别是复杂　

的大型系统包含动态建立的查询的时候。尽管许多产品提供良好的监控工具，但要确定一小段　

SQL语句与整个系统的关系，有时却非常困难。因此，要养成为程序和关键模块加注释的习惯，　

在SQL中插入注释有助于辨别查询在程序中的位置。例如：　



/*　CUSTOMER　REGISTRATION　*/　select　blah　。。。　



这些注释在查错时非常有用。另外，注释也有助于判断单独应用对服务器造成的负载有多大；　

例如我们希望本地应用承担更多工作，需要判断当前硬件是否能承受突发高负载，这时注释特　

别有用。　



有些产品还提供了专门的记录功能（registration　facilities），将你从“为每个语句加注释”的乏味　

工作中解放出来。例如Oracle　的dbms_application_info包，它支持48个字　



符的模块名称（module　name）、32　个字符的动作名称（action　name）和64个　字符的客户信　

息，这些字段的内容可由我们定制。在　Oracle　环境下，你可以利用这个程序包记录哪个应用　

正在执行，以及它在何时正在做什么。因为应用是通过“Oracle　V　动态视图”（能显示目前内存　

中发生的情况）向程序包传递信息的，于是我们可以轻易地掌握这些信息。　



总结：易识别的语句有助于定位性能问题。　



保持数据库连接稳定　



Stable　Database　Connections　

SSttaabbllee　DDaattaabbaassee　CCoonnnneeccttiioonnss　



建立一个新的数据库连接，既快又方便，但这其中往往掩藏着重复建立数据库连接带来的巨大　

开销。所以，管理数据库连接必须非常小心。允许多重连接——可能就藏在你的应用中——的　

后果可能很严重，下面即是一例。　


…………………………………………………………Page　7……………………………………………………………

不久前，我遇到一个应用，要处理很多小的文本文件。这些文本文件最大的也不超过一百行，　

每一行包含要加载的数据及数据库等信息。此例中固然只有一个数据库实例，但即使有上百个，　

这里所说明的原理也是适用的。　

处理每个文件的代码如下：　

　　　　　　Open　the　file　

　　　　　Until　the　end　of　fileisreached　

　　　　　Readarow　

　　　　　Connect　tothe　server　specified　bythe　row　

　　　　　Insert　the　data　

　　　　　Disconnect　

　　　　　Close　the　file　



上述处理工作令人满意，但当大量小文件都在极短的时间内到达时，可能应用程序来不及处理，　

于是积压大量待处理文件，花费时间相当可观。　



我用　C　语言编了个简单的程序来模拟上述情况，以说明频繁的数据库连接和中断所造成的系　

统性能下降问题。表　2…1列出了模拟的结果。　



注意　

产生表　2…1结果的程序使用了常规的insert语句。顺便提一下，直接加载（direct…loading）的技　

术会更快。　



表2…1：连接／中断性能测试结果　



　测　试　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　结　果　



　依次对每一行作连接／中断　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　7。4　行/秒　



　连接一次，所有行逐个插入　

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1　681　行/秒　



　连接一次，以　10　行为一数组插入　　　　　　　　　　　　　　　　　　　　　　　　　5　914　行/秒　



　连接一次，以　100　行为一数组插入　　　　　　　　　　　　　　　　　　　　　　　　9　190　行/秒　


…………………………………………………………Page　8……………………………………………………………

此例说明了尽量减少分别连接数据库次数的重要性。对比表中前后两次针对相同数据库的插入　

操作，明显发现性能有显著提升。其实还可以做进一步的优化。因为数据库实例的数量势必有　

限，所以可以建立一组处理程序（handler）分别负责一个数据库连接，每个数据库只连接一次，　

使性能进一步提高。正如表　2…1　所示，仅连接数据库一次（或很少次）的简单技巧，再加上一　

点额外工作，就能让效率提升200倍以上。　



当然，在上述改进的基础上，再将欲更新的数据填入数组，这样就尽可能减少了程序和数据库　

核心间的交互次数，从而使性能产生了另一次飞跃。这种每次插入几行数据的做法，可以使数　

据的总处理能力又增加了5倍。表　2…1　中的结果显示改进后的性能几乎是最初的　1　200　倍。　



为何有如此大的性能提升？　



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



在常见的客户/