SQL语言艺术

SQL
语言艺术
内容介绍
本书分为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各种重要版
本升级(例如支持互联网访问、支持网格,不一而足)。所以,代码必须从一开始就快速、健全。
很多开发者仅仅是“知道”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、尤其是数据库应用维护人员也能喜欢本书。不过,他们不是本书的主要目标
读者。
本书假定
本书假定你已精通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、索引或字段的名称,抑或表示函数、代码及命令
输出。
等宽黑体(Courier)
表示必须由用户逐字键入的命令等文本。此风格仅用于同时包含输入、输出的代码示例。
等宽斜体(Courier)
表示这些文本,应该被用户提供的值代替。
总结:箴言,概括重要的SQL原则。
注意
提示、建议、一般性注解。为相关主题提供有用的附加信息。
代码示例
本书是为了帮助你完成工作的。总的来说,你可以将本书的代码用于你的程序和文档,但是,
若要大规模复制代码,则必须联系O'Reilly申请授权。例如:编程当中用了本书的几段代码,无
需授权;但出售或分发O'Reilly书籍中案例的CD-ROM光盘,需要授权。再如:回答问题时,引
用了本书或其中的代码示例,无需授权;但在你的产品文档中大量使用本书代码,需要授权。
O'Reilly公司感谢但不强制归属声明。归属声明通常包括书名、作者、出版商、ISBN。例如“The Art
of SQL by Stéphane Faroult with Peter Robson. Copyright © 2006 O'Reilly Media, 0-596-00894-5”。
如果你对代码示例的使用超出了上述范围,请通过permissions@oreilly.com 联系出版商。
评论与提问
我们已尽力核验本书所提供的信息,尽管如此,仍不能保证本书完全没有瑕疵,而网络世界的
变化之快,也使得本书永不过时的保证成为不可能。如果读者发现本书内容上的错误,不管是
赘字、错字、语意不清,甚至是技术错误,我们都竭诚虚心接受读者指教。如果您有任何问题,
请按照以下的联系方式与我们联系。
O'Reilly Media, 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和我发现,虽然我们保证按美国英语拼写,但还远远不够)。
我还要感谢很多人,他们来自三个不同的大陆,阅读了本书全部或部分草稿并坦诚地提出意见。
他们是:Philippe Bertolino、Rachel Carmichael、Sunil CS、Larry Elkins、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://www. roughsea.com)。
Stéphane Faroult出版了Fortran Structuré et Méthodes Numériques一书(法语,Dunod出版社,1986,
与Didier Simon合作),并在Oracle Scene和Select(分别为英国和北美Oracle用户组杂志)以及
Oracle杂志在线版上发表了许多文章。他还是美国、英国、挪威等众多用户组大会的演讲者。
Peter Robson毕业于达拉谟大学地质专业(1968年),然后在爱丁堡大学任教,并于1975年获得
地质学研究型硕士学位。在希腊度过了一段地质学家生涯之后,他开始在纽卡斯尔大学专攻地
质和医学数据库。
他使用数据库始于1977年,1981年开始使用关系数据库,1985年开始使用Oracle,这期间担任
过开发工程师、数据架构师、数据库管理员等角色。1980年,Peter参加了英国地质普查,负责
指导使用关系数据库管理系统。他擅长SQL系统,以及从组织级到部门级的数据建模。Peter多
次出席英国、欧洲、北美的Oracle数据库大会,在许多数据库专业杂志上发表过文章。他现任
英国Oracle用户组委员会主任,可通过peter.robson@justsql.com联系他。
查询的识别
有经验的朋友都知道,把关键系统从开发环境切换到生产环境是一场战役,一场甚嚣尘上的战
役。通常,在“攻击发起日(D-Day)”的前几周,性能测试会显示新系统达不到预期要求。于是,
找专家,调优SQL语句,召集数据库管理员和系统管理员不断开会讨论对策。最后,性能总算
与以前的系统大致相当了(尽管新系统用的是价格翻倍的硬件)。
人们常常使用战术,而忽略了战略。战略要求从大局上把握整个架构与设计。和战争一样,战
略的基本原则并不多,且经常被忽视。架构错误的代价非常高,SQL 程序员必须准备充分,明
确目标,了解如何实现目标。在本章中,我们讨论编写高效访问数据库的程序需要实现哪些关
键目标。
查询的识别
Query
Identification
数个世纪以来,将军通过辨别军装颜色和旗帜等来判断各部队的位置,以此检查激战中部队行
进情况。同样,当一些进程消耗了过多的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
建立一个新的数据库连接,既快又方便,但这其中往往掩藏着重复建立数据库连接带来的巨大
开销。所以,管理数据库连接必须非常小心。允许多重连接——可能就藏在你的应用中——的
后果可能很严重,下面即是一例。
不久前,我遇到一个应用,要处理很多小的文本文件。这些文本文件最大的也不超过一百行,
每一行包含要加载的数据及数据库等信息。此例中固然只有一个数据库实例,但即使有上百个,
这里所说明的原理也是适用的。
处理每个文件的代码如下:
上述处理工作令人满意,但当大量小文件都在极短的时间内到达时,可能应用程序来不及处理,
于是积压大量待处理文件,花费时间相当可观。
我用C 语言编了个简单的程序来模拟上述情况,以说明频繁的数据库连接和中断所造成的系
统性能下降问题。表2-1列出了模拟的结果。
注意
产生表2-1结果的程序使用了常规的insert语句。顺便提一下,直接加载(direct-loading)的技
术会更快。
表2-1:连接/中断性能测试结果
Open the file
Until the end of file is reached
Read a row
Connect to the server specified by the row
Insert the data
Disconnect
Close the file
测试结果
依次对每一行作连接/中断7.4 行/秒
连接一次,所有行逐个插入1 681 行/秒
连接一次,以10 行为一数组插入5 914 行/秒
连接一次,以100 行为一数组插入9 190 行/秒
此例说明了尽量减少分别连接数据库次数的重要性。对比表中前后两次针对相同数据库的插入
操作,明显发现性能有显著提升。其实还可以做进一步的优化。因为数据库实例的数量势必有
限,所以可以建立一组处理程序(handler)分别负责一个数据库连接,每个数据库只连接一次,
使性能进一步提高。正如表2-1 所示,仅连接数据库一次(或很少次)的简单技巧,再加上一
点额外工作,就能让效率提升200倍以上。
当然,在上述改进的基础上,再将欲更新的数据填入数组,这样就尽可能减少了程序和数据库
核心间的交互次数,从而使性能产生了另一次飞跃。这种每次插入几行数据的做法,可以使数
据的总处理能力又增加了5倍。表2-1 中的结果显示改进后的性能几乎是最初的1 200 倍。
为何有如此大的性能提升?
第一个原因,也是最大的原因,在于数据库连接是很“重”的操作,消耗资源很多。
在常见的客户/服务器模式中(现在仍广为使用),简单的连接操作背后潜藏着如下事实:首先,
客户端与远程服务器的监听程序(listener program)建立联系;接着,监听程序要么创建一个
进程或线程来执行数据库核心程序,要么直接或间接地把客户请求传递给已存在的服务器进程,
这取决于此服务器是否为共享服务器。
除了这些系统操作(创建进程或线程并开始执行)之外,数据库系统还必须为每
次session建立新环境,以跟踪它的行为。建立新session前,DBMS还要检查密码是否与保存
的加密的账户密码相符。或许,DBMS还要执行登录触发器(logon trigger),还要初始化存储
过程和程序包(如果它们是第一次被调用)。上面这些还不包括客户端进程和服务器进程之间要
完成的握手协议。正因为如此,连接池(connection pooling)等保持永久数据库连接的技术对
性能才如此重要。
第二个原因,你的程序(甚至包括存储过程)和数据库之间的交互也有开销。
即使数据库连结已经建立且仍未中断,程序和DBMS 核心之间的上下文切换(context switch)
也有代价。因此,如果DBMS 支持数据通过数组传递,应毫不犹豫地使用它。如果该数组接
口是隐式的(API内部使用,但你不能使用),那么明智的做法是检查它的默认大小并根据具体
需要修改它。当然,任何逐行处理的方式都面临上下文切换的问题,并对性能产生严重影响—
—本章后面还会多次涉及此问题。
总结:数据库连接和交互好似万里长城——长度越长,传递消息越耗时。
战略优先于战术
Strategy
Before
Tactics
战略决定战术,反之则谬也。思考如何处理数据时,有经验的开发者不会着眼于细微步骤,而
是着眼于最终结果。要获得想要的结果,最显而易见的方法是按照业务规则规定的顺序按部就
班地处理,但这不是最有效的方法——接下来的例子将显示,刻意关注业务处理流程可能会使
我们错失最有效的解决方案。
几年前,有人给了我一个存储过程,让我“尝试”着进行一下优化。为什么说是“尝试”呢?因为
该存储过程已经被优化两次了,一次是由原开发者,另一次是由一个自称Oracle 专家的人。但
尽管如此,这个存储过程的执行仍会花上20分钟,使用者无法接受。
此存储过程的目的,是根据现有库存和各地订单,计算出总厂需要订购的原料数量。大体上,
它就是把不同数据源的几个相同的表聚合(aggregate)到一个主表(master table)中。首先,
将每个数据源的数据插入主表;接着,对主表中的各项数据进行合计并更新;最后,将与合计
结果无关的数据从表中删除。针对每个数据源,重复执行上述步骤。所有SQL 语句都不是特
别复杂,也没有哪个单独的SQL语句特别低效。
为了理解这个存储过程,我花了大半天时间,终于发现了问题:为什么该过程要用这么多步骤
呢?在from子句中加上包含union 的子查询,就能得到所有数据源的聚合(aggregation)。一条
select 语句,只需一步就得到了结果集,而之前要通过插入目标表(target table)得到结果集。
优化后,性能的提升非常惊人——从20 分钟减至20 秒;当然,之后我花了一些时间验证了
结果集,与未优化前完全相同。
想要获得上述的大幅提高性能,无需特别技能,仅要求站在局外思考(think outside the box)的
能力。之前两次优化因“太关注问题本身”而收到了干扰。我们需要大胆的思维,站得远一些,
试着从大局的角度看待问题。要问自己一些关键的问题:写存储过程之前,我们已有哪些数据?
我们希望存储过程返回什么结果?再辅以大胆的思维,思考这些问题的答案,就能得到一个性
能大幅提升的处理方式了。
总结:考虑解决方案的细节之前,先站得远一些,把握大局。
先定义问题,再解决问题
Problem
Definition
Before
Solution
一知半解是危险的。人们常在听说了新技术或特殊技术之后——有时的确很吸引人——试图采
用它作为新的解决方案。普通开发者和设计师通常会立即采纳这些新“解决方案”,直到后来才
发现它们会产生许多后续问题。
现成的解决方案中,非规范化设计引人注目。设计伊始,非规范化设计的拥护者就提出此方案,
为了寻求“性能”而无视最终将会面临的升级恶魔——而事实上,在开发周期早期,改进设计(或
学习如何使用join)也是一个不错的选择。作为非规范化设计的一种手段,物化视图(materialized
view)常被认为是灵丹妙药。物化视图有时被称为快照(snapshot),这个更加平常的词更形象
地反映了可悲的事实:物化视图是某时间点的数据副本。在没有其他办法时,这个理论上遭到
质疑的技术也未尝不值得一试,借用卡夫卡(Franz Kafka)的一句名言:“逻辑诚可贵,生存价
更高。”
然而,绝大部分问题都可借助传统技术巧妙解决。首先,应学会充分利用简单、传统的技术。
只有完全掌握了这些技术,才能正确评价它们的局限性,最终发现它相当于新技术的潜在优势
(如果有的话)。
所有技术方案,都只是我们达到目标的手段。没有经验的开发者误把新技术本身当成了目标。
对于热衷于技术、过于看重技术的人来说,此问题就更为严重。
总结:先打基础,再赶时髦:摆弄新工具之前,先把手艺学好。
直接操作实际数据
Operations
Against
Actual
Data
许多开发者喜欢建立临时工作表(temporary work table),把后续处理使用的大量数据放入其中,
然后开始“正式”工作。这种方法广受质疑,反映了“跳出业务流程细节考虑问题”的能力不足。
记住,永久表(permanent table)可以设置非常复杂的存储选项(在第5章将讨论一些存储选项
的设置),而临时表不能。临时表的索引(如果有的话)可能不是最优的,因此,查询临时表的
语句效率比永久表的差。另外,查询之前必然先为临时表填入数据,这自然也多了一笔额外的
开销。
就算使用临时表有充足理由,若数据量大,也绝不能把永久表当作临时工作表来用。问题之一
在于统计信息的自动收集:若没有实时收集要求,DBMS通常会在不活动或活动少时进行统计
信息收集,而这时作为临时工作表可能为空,从而使优化器收到了完全错误的信息。这些不正
确且有偏差的统计信息可能造成执行计划(execution plan)完全不合理,导致性能下降。所以,
如果一定要用临时表,应确保数据库知道哪些表是临时的。
总结:暂时工作表意味着以不太合理的方式存储更多信息。
用SQL
处理集合
Set
Processing
in
SQL
SQL 完全基于集合(Set)来处理数据。对大部分更新或删除操作而言—— 如果不是针对整
个表的话—— 你必须先精确定义出要处理的记录的集合。这定义了该处理的粒度
(granularity),可能是对大量记录的粗粒度操作,有可能是只影响少数记录的细粒度操作。
将一次“大批量数据的处理”分割成多次“小块处理”是个坏主意,除非对数据库的修改太昂贵,
否则不要使用,因为这种方法极其低效:
(1)占用过多的空间保存原始数据,以备事务(transaction)回滚(rollback)之需;
(2)万一修改失败,回滚消耗过长的实践。
许多人认为,进行大规模修改操作时,应在操作数据的代码中有规律地多安排些commit命令。
其实,严格从实践角度来讲,“从头开始重做”比“确定失败发生的时间和位置,接着已提交部分
重做”要容易得多、简单得多、也快得多。
处理数据时,应适应数据库的物理实现。考虑事务失败时回滚所需日志的大小,如果要为undo
保存的数据量确实巨大,或许应该考虑数据修改的频率问题。也就是说,将大规模的“每月更新”,
改为规模不大的“每周更新”,甚至改为规模更小的“每日更新”,或许是个有效方案。
总结:几千个语句,借助游标(cursor)不断循环,很慢。换成几个语句,处理同样的数据,
还是较慢。换成一个语句,解决上述问题,最好。
动作丰富的SQL
语句
Action-Packed
SQL
Statements
SQL 不是过程性语言(procedural language),尽管也可以将过程逻辑(procedural logic)用于SQL,
但必须小心。混淆声明性处理(declarative processing)和过程逻辑,最常见的例子出现在需要
从数据库中提取数据、然后处理数据、然后再插入到数据库时。在一个程序(或程序中的一个
函数)接收到特定输入值后,如下情况太常见了:用输入值从数据库中检索到一个或多个另外
的数据值,然后,借助循环或条件逻辑(通常是if ... then ... else)将一些语句组织起来,对数
据库进行操作。大多数情况下,造成上述错误做法的原因有三:根深蒂固的坏习惯、SQL知识
的缺乏、盲从功能需求规格说明。其实,许多复杂操作往往可由一条SQL 语句完成。因此,
如果用户提供了一些数据值,尽量不要将操作分解为多条提取中间结果的语句。
避免在SQL 中引入“过程逻辑(procedural logic)”的主要原因有二。
数据库访问,总会跨多个软件层,甚至包括网络访问。
即使没有网络访问,也会涉及进程间通讯;额外的存取访问意味着更多的函数调用、更大的带
宽,以及更长的等待时间。一旦这些调用要重复多次,其对性能的影响就非常可观了。
在SQL中引入过程逻辑,意味着性能和维护问题应由你的程序承担。
大多数据库系统都提供了成熟的算法,来处理join等操作,来优化查询以获得更高的效率。基于
开销的优化器(cost-based optimizer,CBO)是很复杂的软件,它早已不像刚推出时那样没什么
用了,而在大部分情况下都是非常出色的成熟产品了,优秀的CBO 查询优化的效率极高。然而,
CBO 所能改变的只有SQL 语句。如果在一条单独的SQL语句中完成尽可能多的操作,那么性
能优化可以还由DBMS 核心负责,你的程序可以充分利用DBMS的所有升级。也就是说,未
来大部分维护工作从程序间接转移给了DBMS 供货商。
当然,“避免在SQL 中引入过程逻辑”规则也有例外。有时过程逻辑确实能加快处理速度,庞
大的SQL语句未必总是高效。然而,过程逻辑及其之后的处理相同数据的语句,可以编写到一
个单独的SQL 语句中,CBO 就是这么做的,从而获得最高效的执行方式。
总结:尽可能多地把事情交给数据库优化器来处理。
充分利用每次数据库访问
Profitable
Database
Accesses
如果计划逛好几家商店,你会首先决定在每家店买哪些东西。从这一刻起,就要计划按何种顺
序购物才能少走冤枉路。每逛一家店,计划东西购买完毕,才逛下一家。这是常识,但其中蕴
含的道理许多数据库应用却不懂得。
要从一个表中提取多段信息时,采用多次数据库访问的做法非常糟糕,即使多段信息看似“无关”
(但事实上往往并非如此)。例如,如果需要多个字段的数据,千万不要逐个字段地提取,而应
一次操作全部完成。
很不幸,面向对象(OO)的最佳实践提倡为每个属性定义一个get方法。不要把OO 方法与关
系数据库处理混为一谈。混淆关系和面向对象的概念,以及将表等同于类、字段等同于属性,
都是致命的错误。
总结:在合理范围内,利用每次数据库访问完成尽量多的工作。
接近DBMS
核心
Closeness
to
the
DBMS
Kernel
代码的执行越接近DBMS 核心,则执行速度越快。数据库真正强大之处就在于此,例如,有些
数据库管理产品支持扩展,你可以用C等较底层的语言为它编写新功能。用含有指针操作的底
层语言有个缺点,即一旦指针处理出错会影响内存。仅影响到一个用户已很糟糕,何况数据库
服务器(就像“服务器”名字所指的一样)出了问题会影响众多“用户”——服务器内存出了问题,
所有使用这些数据的无辜的应用程序都会受影响。因此,DBMS 核心采取了负责任的做法,在
沙箱(sandbox)环境中执行程序代码,这样,即使出了问题也不会影响到数据。例如,Oracle 在
外部函数(external function)和它自身之间实现了一套复杂的通信机制,此机制在某些方面很
像控制数据库连结的方法,以管理两个(或多个)服务器上的数据库实例之间的通信。到底采
用PL/SQL 存储过程还是外部C 函数,应综合比较后决定。如果精心编写外部C 函数获得的
好处超过了建立外部环境和上下文切换(context-switching)的成本,就应采用外部函数。但需
要处理一个大数据量的表的每一行时,不要使用外部函数。这需要平衡考虑,解决问题时应完
全了解备选策略的后果。
如要使用函数,始终应首选DBMS自带的函数。这不仅仅是为了避免无谓的重复劳动,还因为
自带函数在执行时比任何第三方开发的代码更接近数据库核心,相应地其效率也会高出许多。
下面这个简单例子是用Oracle SQL编写的,显示了使用Oracle 函数所获得的效率。假设手工
输入的文本数据可能包含多个相邻的“空格”,我们需要一个函数将多个空格
替换为一个空格。如果不采用Oracle Database 10g 开始提供的正规表达式(regular expression),
函数代码将会是这样:
create or replace function squeeze1(p_string in varchar2)
return varchar2
is
v_string varchar2(512) := '';
c_char char(1);
n_len number := length(p_string);
i binary_integer := 1;
j binary_integer;
begin
while (i <= n_len)
上述代码中的'X' 在内层循环中被串接到字符串上,以避免超出字符串长度的测试。
还有别的方法消除多个空格,可以使用Oracle 提供的字符串函数。以下为替代方案:
loop
c_char := substr(p_string, i, 1);
v_string := v_string || c_char;
if (c_char = ' ')
then
j := i + 1;
while (substr(p_string || 'X', j, 1) = ' ')
loop
j := j + 1;
end loop;
i := j;
else
i := i + 1;
end if;
end loop;
return v_string;
end;
/
create or replace function squeeze2(p_string in varchar2)
return varchar2
is
v_string varchar2(512) := p_string;
i binary_integer := 1;
begin
i := instr(v_string, ' ');
while (i > 0)
loop
v_string := substr(v_string, 1, i)
|| ltrim(substr(v_string, i + 1));
i := instr(v_string, ' ');
end loop;
return v_string;
end;
/
还有第三种方法:
用一个简单的例子对上述三种方法进行测试,每个函数都能正确工作,且没有明显的性能差异:
create or replace function squeeze3(p_string in varchar2)
return varchar2
is
v_string varchar2(512) := p_string;
len1 number;
len2 number;
begin
len1 := length(p_string);
v_string := replace(p_string, ' ', ' ');
len2 := length(v_string);
while (len2 < len1)
loop
len1 := len2;
v_string := replace(v_string, ' ', ' ');
len2 := length(v_string);
end loop;
return v_string;
end;
/
SQL> select squeeze1('azeryt hgfrdt r')
2 from dual
3 /
azeryt hgfrdt r
Elapsed: 00:00:00.00
SQL> select squeeze2('azeryt hgfrdt r')
2 from dual
3 /
azeryt hgfrdt r
Elapsed: 00:00:00.01
SQL> select squeeze3('azeryt hgfrdt r')
2 from dual
3 /
azeryt hgfrdt r
Elapsed: 00:00:00.00
那么,如果每天要调用该空格替换操作几千次呢?我们构造一个接近现实负载的环境,下面的
代码将建立一个用于测试的表并填入随机数据,已检测上面三个函数是否有性能差异:
create table squeezable(random_text varchar2(50))
/
declare
i binary_integer;
j binary_integer;
k binary_integer;
v_string varchar2(50);
begin
for i in 1 .. 10000
loop
j := dbms_random.value(1, 100);
v_string := dbms_random.string('U', 50);
while (j < length(v_string))
loop
k := dbms_random.value(1, 3);
v_string := substr(substr(v_string, 1, j) || rpad(' ', k)
|| substr(v_string, j + 1), 1, 50);
j := dbms_random.value(1, 100);
end loop;
insert into squeezable
values(v_string);
end loop;
上面的脚本在测试表中建立了10 000条记录(决定SQL 语句要执行多少次时,这是数字比较适
中)。要执行该测试,运行下列语句:
我运行这个测试时,关闭了所有头信息(headers)和屏幕的显示。禁止输出可确保结果反映的
是替换空格算法所花费的时间,而不是显示结果所花费的时间。这些语句会执行多次,以确保
不受缓存(caching)的影响。
表2-2显示了在测试机上的运行结果。
表2-2:处理10 000条记录中空格所花的时间
commit;
end;
/
select squeeze_func(random_text)
from squeezable;
函数机制时间
squeeze1
用PL/SQL 循环处理字符0.86 秒
尽管都在1秒内完成了10 000次调用,但squeeze2的速度是squeeze1的1.8 倍,而squeeze3
则是它的2.2 倍。为什么呢?原因很简单,因为SQL 函数比PL/SQL“离核心更近”。当函数只偶
尔执行一次时,性能差异微乎其微,但在批处理程序或高负载的OLTP 服务器中性能差异就非
常明显。
总结:代码喜欢SQL内核——离核心越近,它就运行得越快。
只做必须做的
Doing
Only
What
Is
Required
开发者使用count(*)往往只是为了测试“是否存在”。这通常是由以下的需求说明引起的:
如果存在满足某条件的记录
那么处理这些记录
用代码直接实现就是:
当然,在90% 的情况下,count(*) 是完全不必要的,正如上面的例子。要对多项记录进行操
作,直接做即可,不必用count(*)。即使一个操作对任何记录都没有影响,也没有关系,不用
count(*)没有什么不好。而且,即使要对未知的记录进行复杂处理,也能通过第一个操作就确定
并返回受影响的记录——要么通过特殊的API (例如PHP 中的mysql_affected_rows()),要么
采用系统变量(Transact-SQL 中为@@ROWCOUNT,PL/SQL 中为SQL%ROWCOUNT),若使
用内嵌式SQL,则使用SQL通讯区(SQL Communication Area,SQLCA)的特殊字段。有时,
squeeze2 Instr() + ltrim()
0.48 秒
squeeze3 循环调用replace() 0.39 秒
select count(*)
into counter
from table_name
where <certain_condition>
if (counter > 0) then
可以通过函数访问数据库然后直接返回要处理的记录数,例如JDBC 的executeUpdate()方法。
总之,统计记录数极可能意味着重复全部搜索,因为它对相同数据处理了两次。
此外,如果是为了更新或插入记录(常使用count检查键是否已经存在),一些数据库系统会提
供专用的语句(例如Oracle 9i 提供MERGE 语句),其执行效率要比使用count高得多。
总结:没必要编程实现那些数据库隐含实现的功能。
SQL
语句反映业务逻辑
SQL Statements Mirror Business Logic
大多数数据库系统都提供监控功能,我们可以借此查看当前正在执行的语句及其执行的次数。
同时,必须对有多少个“业务单元(business units)”正在执行心里有数——例如待处理的订单、
需处理的请求、需结账的客户,或者业务管理者了解的任何事情。我们应检查上述语句活动和
业务活动的数量关系是否合理(并不要求绝对精确)。换言之,如果客户数量一定,那么数据库
初始化活动的数量是否与之相同?如果查询customers 表的次数比同一时间正在处理的客户量
多20 倍,那一定是某个地方出了问题,或许该查询对表中相同记录做了重复(而且多余)的
访问,而不是一次就从表中找出了所需信息。
总结:检查数据库活动,看它是否与当时正进行的业务活动保持合理的一致性。
把逻辑放到查询中
Program Logic into Queries
在数据库应用程序中实现过程逻辑(procedural logic)的方法有几种。SQL语句内部可实现某
种程度上的过程逻辑(尽管SQL语句应该说明做什么,而不是怎么做)。即便内嵌式SQL的宿主
语言(host language)非常完善,依然推荐尽量将上述过程逻辑放在SQL语句当中,而不是宿
主语言当中,因为前一种做法效率更高。过程性语言(Procedural language)的特点在于拥有
执行迭代(循环)和条件(if ... then ... else 结构)逻辑的能力。SQL不需要循环能力,因为它
本质上是在操作集合,SQL只需要执行条件逻辑的能力。
条件逻辑包含两部分——IF和ELSE。要实现IF的效果相当容易——where子句可以胜任,困难
的是实现ELSE 逻辑。例如,要取出一些记录,然后对其分组,每组进行不同的转换。case 表
达式(Oracle 早已在decode()(注1)中提供了功能等效的操作符)可以容易地模拟ELSE逻辑:
根据每条记录值的不同,返回具有不同值的结果集。下面用伪代码(pseudocode)表达case 结
构的使用(注2):
CASE
WHEN condition THEN <return something to the result set>
WHEN condition THEN <return something else>
...
WHEN condition THEN <return still something else>
ELSE <fall back on this value>
数值或日期的比较则简单明了。操作字符串可以用Oracle 的greatest()或least(),或者MySQL
的strcmp()。有时,可以为insert语句增加过程逻辑,具体办法是多重insert及条件insert(注3),
并借助merge 语句。如果DBMS 提供了这样语句,毫不犹豫地使用它。也就是说,有许多
逻辑可以放入SQL 语句中;虽然仅执行多条语句中的一条这种逻辑价值不大,但如果设法利
用case、merge 或类似功能将多条语句合并成一条,价值可就大了。
总结:只要有可能,应尽量把条件逻辑放到SQL语句中,而不是SQL的宿主语言中。
一次完成多个更新
Multiple
Updates
at
Once
我的基本主张是:如果每次更新的是彼此无关的记录,对一张表连续进行多次update操作还可
以接受;否则,就应该把它们合并成一个update操作。例如,下面是来自实际应用的一些代码
(注4):
两个连续的更新是对同一个表进行的。但它们是否将访问相同的记录呢?不得而知。问题是,
搜索条件的效率有多高?任何名为type或status的字段,其值的分布通常是杂乱无章的,所以上
面两个update语句极可能对同一个表连续进行两次完整扫描:一个update有效地利用了索引,而
第二个update不可避免地进行全表扫描;或者,幸运
的话,两次update都有效地利用了索引。无论如何,把这两个update合并到一起,几乎不会有损
失,只会有好处:
END
update tbo_invoice_extractor
set pga_status = 0
where pga_status in (1,3)
and inv_type = 0;
update tbo_invoice_extractor
set rd_status = 0
where rd_status in (1,3)
and inv_type = 0;
update tbo_invoice_extractor
set pga_status = (case pga_status
when 1 then 0
when 3 then 0
else pga_status
上例中,可能出现重复更新相同字段为相同内容的情况,这的确增加了一小点儿开销。但在多
数情况下,一个update会比多个update快得多。注意上例中的“逻辑(logic)”,我们通过case 语
句实现了隐式的条件逻辑(implicit conditional logic),来处理那些符合更新条件的数据记录,并
且更新条件可以有多条。
总结:有可能的话,用一个语句处理多个更新;尽量减少对同一个表的重复访问。
慎用自定义函数
Careful Use of User-Written Functions
将自定义函数(User-Written Function)嵌到SQL语句后,它可能被调用相当多次。如果在select
语句的选出项列表中使用自定义函数,则每返回一行数据就会调用一次该函数。如果自定义函
数出现在where 子句中,则每一行数据要成功通过过滤条件都会调用一次该函数;如果此时
其他过滤条件的筛选能力不够强,自定义函数被调用的次数就非常可观了。
如果自定义函数内部还要执行一个查询,会发生什么情况呢?每次函数调用都将执行此内部查
询。实际上,这和关联子查询(correlated subquery)效果相同,只不过自定义函数的方式阻
碍了基于开销的优化器(cost-based optimizer,CBO)对整个查询的优化效果,因为“子查询”
隐藏在函数中,数据库优化器鞭长莫及。
下面举例说明将SQL语句隐藏在自定义函数中的危险性。表flights描述商务航班,有航班号、起
飞时间、到达时间及机场IATA 代码(注5)等字段。IATA代码均为三个字母,有9 000多个,
它们的解释保存在参照表中,包含城市名称(若一个城市有多个机场则应为机场名称)、国家名
称等。显然,显示航班信息时,应该包含目的城市的机场名称,而不是简单的IATA 代码。
在此就遇到了现代软件工程中的矛盾之一。被认为是“优良传统”的模块化编程一般情况下非常
适用,但对数据库编程而言,代码是开发者和数据库引擎的共享活动(shared activity),模块
化要求并不明确。例如,我们可以遵循模块化原则编写一个小函数来查找IATA 代码,并返回
完整的机场名称:
end),
rd_status = (case rd_status
when 1 then 0
when 3 then 0
else rd_status
end)
where (pga_status in (1,3)
or rd_status in (1, 3))
and inv_type = 0;
create or replace function airport_city(iata_code in char)
return varchar2
is
对于不熟悉Oracle 语法的读者,在此做个说明,以下查询中trunc(sysdate)的返回值为“今天的
00:00 a.m.”,日期计算以天为单位;所以起飞时间的条件是指今天8:30 a.m. 至4:00 p.m. 之
间。调用airport_city函数的查询可以非常简单,例如:
这个查询的执行速度令人满意;在我机器上的随机样本中,返回77行数据只用了0.18 秒(多次
执行的平均值),用户对这样的速度肯定满意(统计数据表明,此处理访问了
303个数据块,53个是从磁盘读出的——而且每行数据有个递归调用)。
我们还可以用join来重写这段代码,作为查找函数的替代方案,当然它看起来会稍微复杂些:
city_name varchar2(50);
begin
select city
into city_name
from iata_airport_codes
where code = iata_code;
return(city_name);
end;
/
select flight_number,
to_char(departure_time, 'HH24:MI') DEPARTURE,
airport_city(arrival) "TO"
from flights
where departure_time between trunc(sysdate) + 17/48
and trunc(sysdate) + 16/24
order by departure_time
/
select f.flight_number,
to_char(f.departure_time, 'HH24:MI') DEPARTURE,
a.city "TO"
from flights f,
iata_airport_codes a
where a.code = f.arrival
and departure_time between trunc(sysdate) + 17/48
and trunc(sysdate) + 16/24
order by departure_time
/
这个查询只用了0.05 秒(统计数据同前,但没有递归调用)。对于执行时间不到0.2 秒的查
询来说,速度快了3倍似乎无关紧要,但在大型系统中,这些查询每天经常执行数十万次——假
设以上查询每天只执行五万次,于是查询的总耗时为2.5 小时。若不使用上述查找函数(lookup
function)则只需要不到42 分钟,速度提高超过300%,这对大数据量的系统意义重大,最终
带来经济上的节约。通常,使用查找函数会使批处理程序的性能极差。而且查询时间的增加,
会使同一台机器支持的并发用户数减少,我们将在第9章对此展开讨论。
总结:优化器对自定义函数的代码无能为力。
简洁的SQL
Succinct SQL
熟练的开发者使用尽可能少的SQL语句完成尽可能多的事情。相反,拙劣的开发者则倾向于严
格遵循已制订好的各功能步骤,下面是个真实的例子:
就算速度可以接受,这也是段极糟的代码。很不幸,性能专家经常遇到这种糟糕的代码。既然
两个值来自于同一表,为什么要分别用两个不同的语句呢?下面用Oracle的bulk collect子句,
一次性将两个值放到数组中,这很容易实现,关键在于对rslt_period进行order by操作,如下所
示:
-- Get the start of the accounting period
select closure_date
into dtPerSta
from tperrslt
where fiscal_year=to_char(Param_dtAcc,'YYYY')
and rslt_period='1' || to_char(Param_dtAcc,'MM');
-- Get the end of the period out of closure
select closure_date
into dtPerClosure
from tperrslt
where fiscal_year=to_char(Param_dtAcc,'YYYY')
and rslt_period='9' || to_char(Param_dtAcc,'MM');
select closure_date
bulk collect into dtPerStaArray
from tperrslt
where fiscal_year=to_char(Param_dtAcc,'YYYY')
and rslt_period in ('1' || to_char(Param_dtAcc,'MM'),
于是,这两个日期被分别保存在数组的第一个和第二个位置。其中,bulk collect 是PL/SQL 语
言特有的,但任何支持显式或隐式数组提取的语言都可如法炮制。
其实甚至数组都是不必要的,用以下的小技巧(注6),这两个值就可以被提取到两个变量中:
在这个例子中,预期返回值为两行数据,所以问题是:如何把原本属于一个字段的两行数据,
以一行数据两个字段的方式检索出来(正如数组提取的例子一样)。为此,我们
检查rslt_period字段,两行数据的rslt_period字段有不同值;如果找到需要的记录,就返回要找
的日期;否则,就返回一个在任何情况下都远比我们所需日期要早的日期(此处选了哈斯丁之
役(battle of Hastings)的日期)。只要每次取出最大值,就可以确保获得需要的日期。这是个
非常实用的技巧,也可以应用在字符或数值数据,第11章会有更详细的说明。
总结:SQL是声明性语言(declarative language),所以设法使你的代码超越业务过程的规格
说明。
SQL
的进攻式编程
Offensive Coding with SQL
一般的建议是进行防御式编程(code defensively),在开始处理之前先检查所有参数的合法性。
但实际上,对数据库编程而言,尽量同时做几件事情的进攻式编程有切实的优势。
有个很好的例子:进行一连串检查,每当其中一个检查所要求的条件不符时就产生异常。信用
卡付款的处理中就涉及类似步骤。例如,检查所提交的客户身份和卡号是否有效,以及两者是
否匹配;检查信用卡是否过期;最后,检查当前的支付额是否超过了信用额度。如果通过了所
'9' || to_char(Param_dtAcc,'MM'))
order by rslt_period;
select max(decode(substr(rslt_period, 1, 1), -- Check the first character
'1', closure_date,
-- If it's '1' return the date we want
to_date('14/10/1066', 'DD/MM/YYYY'))),
-- Otherwise something old
max(decode(substr(rslt_period, 1, 1),
'9', closure_date, -- The date we want
to_date('14/10/1066', 'DD/MM/YYYY'))),
into dtPerSta, dtPerClosure
from tperrslt
where fiscal_year=to_char(Param_dtAcc,'YYYY')
and rslt_period in ('1' || to_char(Param_dtAcc,'MM'),
'9' || to_char(Param_dtAcc,'MM'));
有检查,支付操作才继续进行。
为了完成上述功能,不熟练的开发者会写出下列语句,并检查其返回结果:
接下来,他会做类似的工作,并再一次检查错误代码:
之后,他才会处理金融交易。
相反,熟练的开发者更喜欢像下面这样编写代码(假设today()返当前日期):
接着,检查被更新的行数。如果结果为0,只需执行下面的一个操作即可判断出错原因:
如果此查询没有返回数据,则可断定customer_id 的值是错的;如果card_num 是null,则可
断定卡号是错的;等等。其实,多数情况下此查询无需被执行。
注意
你是否注意到,上述第一段代码中使用了count(*)呢?这是个count(*)被误用于存在性检测的绝
佳例子。
“进攻式编程”的本质特征是:以合理的可能性(reasonable probabilities)为基础。例如,检查
select count(*)
from customers
where customer_id = provided_id
select card_num, expiry_date, credit_limit
from accounts
where customer_id = provided_id
update accounts
set balance = balance - purchased_amount
where balance >= purchased_amount
and credit_limit >= purchased_amount
and expiry_date > today()
and customer_id = provided_id
and card_num = provided_cardnum
select c.customer_id, a.card_num, a.expiry_date,
a.credit_limit, a.balance
from customers c
left outer join accounts a
on a.customer_id = c.customer_id
and a.card_num = provided_cardnum
where c.customer_id = provided_id
客户是否存在是毫无意义的——因为既然该客户不存在,那么他的记录根本就不在数据库中!
所以,应该先假设没有事情会出错;但如果出错了,就在出错的地方(而且只在那个地方)采
取相应措施。有趣的是,这种方法很像一些数据库系统中采用的“乐观并发控制(optimistic
concurrency control)”,后者会假设update冲突不会发生,只在冲突真的发生时才进行控制处理。
结果,乐观方法比悲观方法的吞吐量高得多。
总结:以概论为基础进行编程。假设最可能的结果;不是的确必要,不要采用异常捕捉的处理
方式。
简洁的SQL
Succinct SQL
熟练的开发者使用尽可能少的SQL语句完成尽可能多的事情。相反,拙劣的开发者则倾向于严
格遵循已制订好的各功能步骤,下面是个真实的例子:
就算速度可以接受,这也是段极糟的代码。很不幸,性能专家经常遇到这种糟糕的代码。既然
两个值来自于同一表,为什么要分别用两个不同的语句呢?下面用Oracle的bulk collect子句,
一次性将两个值放到数组中,这很容易实现,关键在于对rslt_period进行order by操作,如下所
示:
-- Get the start of the accounting period
select closure_date
into dtPerSta
from tperrslt
where fiscal_year=to_char(Param_dtAcc,'YYYY')
and rslt_period='1' || to_char(Param_dtAcc,'MM');
-- Get the end of the period out of closure
select closure_date
into dtPerClosure
from tperrslt
where fiscal_year=to_char(Param_dtAcc,'YYYY')
and rslt_period='9' || to_char(Param_dtAcc,'MM');
select closure_date
bulk collect into dtPerStaArray
from tperrslt
where fiscal_year=to_char(Param_dtAcc,'YYYY')
and rslt_period in ('1' || to_char(Param_dtAcc,'MM'),
'9' || to_char(Param_dtAcc,'MM'))
order by rslt_period;
于是,这两个日期被分别保存在数组的第一个和第二个位置。其中,bulk collect 是PL/SQL 语
言特有的,但任何支持显式或隐式数组提取的语言都可如法炮制。
其实甚至数组都是不必要的,用以下的小技巧(注6),这两个值就可以被提取到两个变量中:
在这个例子中,预期返回值为两行数据,所以问题是:如何把原本属于一个字段的两行数据,
以一行数据两个字段的方式检索出来(正如数组提取的例子一样)。为此,我们
检查rslt_period字段,两行数据的rslt_period字段有不同值;如果找到需要的记录,就返回要找
的日期;否则,就返回一个在任何情况下都远比我们所需日期要早的日期(此处选了哈斯丁之
役(battle of Hastings)的日期)。只要每次取出最大值,就可以确保获得需要的日期。这是个
非常实用的技巧,也可以应用在字符或数值数据,第11章会有更详细的说明。
总结:SQL是声明性语言(declarative language),所以设法使你的代码超越业务过程的规格
说明。
SQL
的进攻式编程
Offensive Coding with SQL
一般的建议是进行防御式编程(code defensively),在开始处理之前先检查所有参数的合法性。
但实际上,对数据库编程而言,尽量同时做几件事情的进攻式编程有切实的优势。
有个很好的例子:进行一连串检查,每当其中一个检查所要求的条件不符时就产生异常。信用
select max(decode(substr(rslt_period, 1, 1), -- Check the first character
'1', closure_date,
-- If it's '1' return the date we want
to_date('14/10/1066', 'DD/MM/YYYY'))),
-- Otherwise something old
max(decode(substr(rslt_period, 1, 1),
'9', closure_date, -- The date we want
to_date('14/10/1066', 'DD/MM/YYYY'))),
into dtPerSta, dtPerClosure
from tperrslt
where fiscal_year=to_char(Param_dtAcc,'YYYY')
and rslt_period in ('1' || to_char(Param_dtAcc,'MM'),
'9' || to_char(Param_dtAcc,'MM'));
卡付款的处理中就涉及类似步骤。例如,检查所提交的客户身份和卡号是否有效,以及两者是
否匹配;检查信用卡是否过期;最后,检查当前的支付额是否超过了信用额度。如果通过了所
有检查,支付操作才继续进行。
为了完成上述功能,不熟练的开发者会写出下列语句,并检查其返回结果:
接下来,他会做类似的工作,并再一次检查错误代码:
之后,他才会处理金融交易。
相反,熟练的开发者更喜欢像下面这样编写代码(假设today()返当前日期):
接着,检查被更新的行数。如果结果为0,只需执行下面的一个操作即可判断出错原因:
如果此查询没有返回数据,则可断定customer_id 的值是错的;如果card_num 是null,则可
断定卡号是错的;等等。其实,多数情况下此查询无需被执行。
注意
你是否注意到,上述第一段代码中使用了count(*)呢?这是个count(*)被误用于存在性检测的绝
select count(*)
from customers
where customer_id = provided_id
select card_num, expiry_date, credit_limit
from accounts
where customer_id = provided_id
update accounts
set balance = balance - purchased_amount
where balance >= purchased_amount
and credit_limit >= purchased_amount
and expiry_date > today()
and customer_id = provided_id
and card_num = provided_cardnum
select c.customer_id, a.card_num, a.expiry_date,
a.credit_limit, a.balance
from customers c
left outer join accounts a
on a.customer_id = c.customer_id
and a.card_num = provided_cardnum
where c.customer_id = provided_id
佳例子。
“进攻式编程”的本质特征是:以合理的可能性(reasonable probabilities)为基础。例如,检查
客户是否存在是毫无意义的——因为既然该客户不存在,那么他的记录根本就不在数据库中!
所以,应该先假设没有事情会出错;但如果出错了,就在出错的地方(而且只在那个地方)采
取相应措施。有趣的是,这种方法很像一些数据库系统中采用的“乐观并发控制(optimistic
concurrency control)”,后者会假设update冲突不会发生,只在冲突真的发生时才进行控制处理。
结果,乐观方法比悲观方法的吞吐量高得多。
总结:以概论为基础进行编程。假设最可能的结果;不是的确必要,不要采用异常捕捉的处理
方式。
精明地使用异常(Exceptions

Discerning Use of Exceptions
勇敢与鲁莽的界线很模糊,我建议进攻式编程,但并不是要你模仿轻步兵旅在Balaclava的自杀
性冲锋(注7)。针对异常编程,最终可能落得虚张声势的愚蠢结果,但自负的开发者还是对它“推
崇备至(go for it)”,并坚信检查和处理异常能使他们完成任务。
正如其名字所暗示的,异常应该是那些例外情况。对数据库编程的具体情况而言,不是所有异
常都要求同样的处理方式——这是理解异常的使用是否明智的关键点。有些是“好”异常,应预
先抛出;有些是“坏”异常,仅当真正的灾害发生时才抛出。
例如,以主键为条件进行查询时,如果没有结果返回则开销极少,因为只需检查索引即可判断。
然而,如果查询无法使用索引,就必须搜索整个表——当此表数据量很大,所在机器又正在接
近满负荷工作时,可能造成灾难。
有些异常的处理代价高昂,即使是在最佳情况下也不例外,例如重复键(duplicate key)的探
测。“唯一性(uniqueness)”如何保证呢?我们几乎总是建立一个唯一性索引,每次向该索引增
加一个键时,都要检查是否违反了该唯一性索引的约束。然而,建立索引项需要记录物理地址,
于是就要求先将记录插入表,后将索引项插入索引。如果违反此约束,数据库会取消不完全的
插入,并返回违反约束的错误信息。上述这些操作开销巨大。但最大的问题是,整个处理必须
围绕个别异常展开,于是我们必须“从个别记录的角度进行思考”,而不是“从数据集出发进行思
考”,这与关系数据库理论完全背道而驰。多次违反此约束会导致性能严重下降。
来看一个Oracle 的例子。假设在两家公司合并后,电子邮件地址定为<Initial><Name>的标准
格式,最多12 个字符,所有空格或引号以下划线代替。
如果新的employee表已经建好,并包含3 000 条从employee_old表中提取并进行标准化处理的
电子邮件地址。我们希望每个员工的电子邮件地址具有唯一性,于是Fernando Lopez的地址为
flopez,而Francisco Lopez的地址为flopez2。实际上,我们实际测试的数据中有33 个潜在的
重复项,所以我们需要做如下测试:
SQL> insert into employees(emp_num, emp_name,
emp_firstname, emp_email)
2 select emp_num,
3 000 条数据中重复33 条,比率大约是1%,所以,或许可以心安理得地处理符合标准的
99%,并用异常来处理其余部分。毕竟,1% 的不符标准数据带来的异常处理开销应该不大。
但这个异常处理的开销到底在哪里呢?让我们先从测试数据中剔除“问题记录”,然后再执行相
同的测试,比较发现:这次测试的总运行时间,与上次几乎相同,都是18 秒。然而,从测试数
据中剔除“问题记录”之后再执行前面第一段insert...select 语句时,速度明显比循环快:最终发
现采用“一次处理一行”的方式导致耗时增加了近50%。那么,在此例中可以不用“一次处理一
行”的方式吗?可以,但要首先避免使用异常。正是这个通过异常处理解决“问题记录”问题决定,
迫使我们采用循序方式的。
另外,由于发生冲突的电子邮件地址可能不止一个,可以为它们指定某个数字获得唯一性。
很容易判断有多少个数据记录发生了冲突,增加一个group by子句就可以了。但在分配数字时,
如果不使用主数据库系统提供的分析功能,恐怕比较困难。(Oracle 称为分析功能(analytical
function), DB2 则称在线分析处理(online analytical processing,OLAP), SQL Server 称之为排
名功能(ranking function)。) 纯粹从SQL角度来看,探索此问题的解决方案很有意义。
重复的电子邮件地址都可以被赋予一个具唯一性的数字:1赋给年纪最大的员工,2 赋给年纪次
之的的员工……依次类推。为此,可以编写一个子查询,如果是group中的第一个电子邮件地址
就不作操作,而该group中的后续电子邮件地址则加上序号。代码如下:
3 emp_name,
4 emp_firstname,
5 substr(substr(EMP_FIRSTNAME, 1, 1)
6 ||translate(EMP_NAME, ' ''', '_ _'), 1, 12)
7 from employees_old;
insert into employees(emp_num, emp_name, emp_firstname, emp_email)
*
ERROR at line 1:
ORA-00001: unique constraint (EMP_EMAIL_UQ) violated
Elapsed: 00:00:00.85
SQL> insert into employees(emp_num, emp_firstname,
2 emp_name, emp_email)
3 select emp_num,
4 emp_firstname,
5 emp_name,
6 decode(rn, 1, emp_email,
7 substr(emp_email,
上面的代码避免了一次一行的处理,而且该解决方案的执行时间仅是先前方案的60%。
总结:异常处理会迫使我们采用过程式逻辑。应始终使用声明式SQL,尽量预测可能的异常情况。
SQL
的本质
本章我们将深入讨论SQL查询,并研究如何根据不同情况的具体要求,来编写SQL语句。我们
会分析复杂的SQL查询语句,将它们拆解成小的语句片断,并讲解这些语句片断如何共同促成
了最终查询结果的产生。
SQL
的本质
The Nature of SQL
在深入讨论如何编写SQL查询之前,我们
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页