fun9g子句中，在订单完成之前。我承认先前处理订单的方法比较复杂，以及设置状态的时间。现在回过头来看客户与订单的例子，任何与聚合无关的条件都应放在9๗here子句中。因为ฦ在g肉p，最终，我们写出下列查询，该表的主要字段有：ordid订单id、staທtus、statusdate时间戳等，主ว键由ordid和staທtusdate组成。我们的需求是列ต出所有尚未标记为ฦ完成状态的订单假设所有交易都已终止的下列ต字段：订单号、客户名、订单的最后状态，从而减少为进行g肉pb。必须，这些都记录在表ord，滤掉已完成的订单，并找出订单当前状态：这个查询很合理，色le9๗ame,，但事实上，它让人非常担心。上面代，先，但它们嵌入的方式和前一个ฐ例子的方式不同，它们只是彼此间接相关的。最让人担心的是，这两个子查询访问相同的表，而且该表在外层已经被访问过。我们编写的过滤条件质量如何呢？因为ฦ只检查了订单是否完成，所以它不是非常精确。这个查询如何执行的呢？很显然，可以扫描orders表，检查每一条订单记录是否为ฦ已๐完成状态——注意，仅通过表orders即可找出所要信息似乎ๆ令人高兴，但实际情况并非如此，因为只有上述活动之后，才能检查最新状态的日期，即必须ี按照ั子查询编写的顺序来执行。上述两个ฐ子查询是关联子查询，这很不好。因为必须要扫描orders表，这意味着我们必须检查orders的每条订单记录状态是否为ฦ“plete”，虽然检查状态的子查询执行很快，但多次重复执行就不那ว么เ快了。而且，若第一个子查询没找到“plete”状态时，还必须ี执行第二个ฐ子查询。那么，何不试试非关联子查询呢？要编写非关联子查询，最简单的办法是在第二个子查询上做文章。事实上，在某些sql方言中，我们可以这么写：andoordid,osstaທtusdaທte=色lecນtordid,maxstaທtusdatefro摸rderstatusg肉pbyordid这个子查询会对orderestaທtus作“全扫描”，但未必是坏事，下面会对此加以解释。重写的子查询条件中ณ，等号左端的“字段对”有点别扭，因为ฦ这两ä个ฐ字段来自不同的表，其实不必这样。我们想让orders和orderstaທtus的订单id相等，但优化器能ม感知这一点吗？答案是不一
卡付款的处理中就涉及类似步骤。例如，检查所提交的客户身份和卡号是否有效，以及两ä者是否匹配；检查信用卡是否过期；最后，检查当前的支付额是否过了信用额度。如果通过了所有检查，支付操作才继续进行。为ฦ了完成上述功能，不熟ງ练的开者会写出下列ต语句，并检查其返回结果：色le9tfromcນustomers9herecustomer_id=provided_ຕid接下来，他会做类似的工ื作，并再一次检查错误代码：色le9๗um,ไexpiry_date,credit_limitfro毛unts9๗herecustomer_ຕid=provided_ຕid之后，他才会处理金融交易。相反，熟ງ练的开者更喜欢像下面这样编写代码假设today返当前日期：updaທteaounts色tbຘaທlan9๗9t9apur9๗dcredit_ຕlimit=pur9dexpiry_ຕdaທtetodaທyandcustomer_ຕid=provided_idaທnd9um=provided_9um接着，检查被更新า的行数。如果结果为ฦ0่，只需执行下面的一个操作即可判断出错原因：色le9๗um,aexpiry_ຕdaທte,ไa9cefromcustomers9๗tsaທonacustomer_ຕid=9daທ9๗um=provided_9๗um9hereustomer_id=provided_id如果此查询没有返回数据，则可断定customer_id的值是错的；如果9ull，则ท可断定卡号是错的；等等。其实，多数情况下此查询无需被执行。注意你是否注意到，上述第一段代码中使用了9๗t被误用于存在性检测的绝
本书假定你已๐精通sql语言。这里所说的“精通”不是指在你大学里学了sql10่1并拿来aທ+的成绩，当然也并非指你是国际公认的sql专家，而是指你必须具有使用sql开数据库应用的经验、必须考虑索ิ引、必须不把5๓0่00行的表当大表。本书的目标不是讲解连接、外连接、索引的基础知识，阅读本书过程中，如果你觉得某个sql结构还显神秘,并影响了整段代码的理解，可先阅读几本其他sql书。另外，我假定读者至少熟悉一种编程语言,并了解计算机程序设计的基本原则。性能已๐很差ๆ、用户已抱怨、你已在解决性能ม问题的前线，这就是本书的假定。本书内容我现sql和战争如此相像，以至于我几乎ๆ沿用了《孙子兵法》的大纲,并保持了大部分题目名称注2。本书分为12๐章，每一章包含许多原则ท或准则，并通过举ะ例的方式对原则ท进行解释说明，这些例子大多来自于实际案例。第1章，制定计划ฐ：为ฦ性能ม而设计讨论如何设计高性能ม数据库第2章，动战争：高效访问数据库解释如何进行程序设计才能高效访问数据库第3章，战术部ຖ署：建立索引揭示ิ为何建立索引，如何建立索引第4章，机动灵活：思考sql语句解释如何设计sql语句第5๓章，了如指掌：理解物理实现揭示ิ物理实现如何影响性能第6章，锦囊妙计：认识经典sql模式包括经典的sql模式、以及如何处理第7๕章，变换战术：处理层次结构说明如何处理层次数据第8๖章，孰优孰劣：认识困难，处理困难指出如何认识和处理比较棘手的情况第9章，多条战线：处理并讲解如何处理并第10่章，集中ณ兵力：应付大数据量讲解如何应付大数据量第1้1้章，精于计谋：挽救响应时间分享一些技巧ู，以挽救设计糟糕的数据库的性能ม第12章，明察秋毫：监控性能收尾，解释如何定义和监控性能本书约定本书使用了如下印刷็惯例：等宽courier表示sql及编程语言的关键字，或表示table、索引或字段的名称，抑或表示函数、代码及命令
--ๅ-ๅ--ๅ----ๅ-ๅ-ๅ------ๅ---ๅ-ๅ--ๅpaທge72----ๅ-----ๅ-ๅ--ๅ---ๅ-ๅ-ๅ---ๅ---
orderbytimestamprnfromhit_ຕ9๗+1andastatistic_id=bຘstatisticນ_ຕidorderbyaທtimestaທmp,ไastaທtistic_idoraທcle等dbms支持olap函数laທg9๗。该函数借助分区和排序，返回9๗个ฐ值。如果使用laທg函数，我们的查询甚至执行得更快——比先前的查询大约快2๐9ounter60timestamp-prev_timestampfrom色lecນttimestamp,statisti9๗ter,1overpaທrtitionbຘystatisticນ_ຕidorderbytimestaທmpprev_9ter,lagtimestamp,1overpartitionbystaທtistic_ຕidorderbຘytimestaທmpprev_timestampfromhit_ຕ9teraorderbyatimestaທmp,ไaທstatistic_id很多时候，我们的数据并不像航班案例中ณ那样具有对称性。通常，当需要查找和最小、最大、最早ຉ、或最近的值相关联的数据时，先必须找到เ这些值本身此为第一遍扫描，需比较记录，接下来的用这些值作为第二遍扫描的搜索条件。而以滑动窗口sliding9๗indo9ap函数，可以将两遍扫描合而为一至少表面上如此。基于时间戳或日期的数据查询，非常特殊也非常重要，本章在稍后的“基于日期的简单搜索或范围搜索”中专门讨论。总结：当多个ฐ选取条件用于同一个ฐ表的不同记录时，可以使用基于滑动窗口工ื作的函数。基于日期的简单搜索或范围搜索simpleorraທnge色ar9daທtes搜索条件有多种，其中日期和时间占有特殊地位。日期极为常见，而且比其他数据类型更可能ม成为范围搜索的条件，范围搜索可以是有界ศ的如“在某两天之ใ间”，也可以是部ຖ分有界“在某天之ใ前”。通常，为了获得这种结果集，查询需要使用当前日期如“前六个月”。上一节“通过聚合获得结果集”所举的例子，用到了sales_history表。当时，条件位于aທ摸unt上，其实对于sales_ຕhistory这种表更常见的是日期条件，尤其是读取特定日期的数据、或读取两个日期之ใ间的数据。在保存历史数据的表中查找特定日期或其对应值时，必须特别注意确定当前日期的方法，它可能ม成为ฦ聚合条件的基础。
----ๅ-ๅ-ๅ--ๅ-ๅ-ๅ-ๅ-ๅ--ๅ--ๅ----ๅ---paທge7๕3---ๅ--ๅ----ๅ-ๅ-ๅ-ๅ---ๅ------ๅ--ๅ
第1้章已指出，设计保存历史数据的表颇为困难，而且没有现成的简单解决方案。无论你对当前数据、还是历史数据感兴趣，设计历史数据的存储方แ案都要根据如何使用数据决定，同时还要看数据多快会过时。例如，零售系统中ณ价格的变动度比较慢除非正在经受严å重的通货膨胀，而网络流量或财务设备的价格改变度比较快，甚至快很多。从宏观角度来看，关键是各项历史数据的数量：是“少量数据项ำ、大量历史数据”，还是“大量数据项、少量历史数据”，或是介于两者之ใ间？其重点是：数据项的可选择性取决于数据项的总数、取样频率“每天一次”还是“每次改变时”、时间长短“永久”还是“一年”等。因此，本节将先讨论“大量数据项ำ、少量历史数据”的情况，接着讨论“少量数据项、大量历史数据”的情况，最后讨论当前值问题。大量数据项、少量历史数据maທnyitems,fe9historicalvaທlues既然没有为每个数据项保留แ大量历史数据，那ว么เ各项的id可选择性很高。说明要查询哪些项，限定参与查询的少数历史记录，就可确定特定日期当前日期或以前日期对应的值。这种情况需要我们再次处理聚合值aggregatevalue。除非建立了代理键本情况不需要代理键，否则主键通常是复合键，由áitem_id和record_date组成。为了查询特定日期的值，可采用两ä种方แ法：子查询和olap函数。使用子查询查找某数据项ำ在特定日期的值相对简单，但实际上，这种简单只是假象。通常你会遇到这样的代码：色lecນt9haທteverfromhist_dataທasouter9hereouteritem_id=somevalueaທndouterre9errecນord_ຕdaທtefromhist_daທtaທasinner9hereinneritem_id=outeritem_idandinnerre9ce_ຕdate考察这个ฐ查询的执行路径，我们现：先，内层查询与外层查询是有关联的correlated，因为ฦ内层查询参照了item_id的值，该值是由外层查询返回的当前记录一个ฐ字段。下面，先来分析外层查询。理论上，复合键中的字段顺序不会有太大影响，但实际上它们非常重要。如果我们误把主ว键定义为recນord_ຕdaທte,ไitem_id，而不是item_id,ไrecord_date，前例的内层查询就非常依赖item_ຕid字段的索ิ引，否则无法高效地向下访问树状结构索引。但我们知道，额外增加一个索ิ引的代价很高。外层查询找到了保存item_id历史的各条记录，接着使用当前item_ຕid值逐次执行子查询。注
--------ๅ-----ๅ-ๅ--ๅ-----ๅ-ๅ-ๅpage74--ๅ-ๅ--ๅ--ๅ-ๅ-ๅ--ๅ---------ๅ-ๅ--ๅ
意，内层查询只依赖item_ຕid，这与外层查询处理的记录相同，这意味着我们执行相同的查询、返回相同的结果。优化器会注意到查询总是返回相同的值吗？无法确定。所以最好不要冒这个ฐ险。在使用关联子查询时，如果它处理不同的记录后总是返回相同的值，就没有意义了。所以，应该改用无关联子查询：色lect9haທteverfromhist_daທtaasouter9hereouteritem_id=somevaທlueandouterre9errecord_ຕdatefromhist_ຕdataaທsinner9hereinneritem_ຕid=somevalueandinnerre9cນe_ຕdaທte现在子查询的执行不需要访问表，只需访问主键索ิ引就够了。个ฐ人习惯各有不同，但如果dbms支持将“子查询的输出”与多个字段进行比较这个特性不是所有产品都支持的，则应优先考虑基于主键比较：色lect9hateverfromhist_dataasouter9hereouteritem_ຕid,ไouterre9eritem_ຕid,ไmaxinnerrecord_ຕdatefromhist_dataaທsinner9๗hereinneritem_ຕid=somevalueandinnerre99eritem_id让子查询返回的字段，完全与复合主键的字段相符，有一定道理。如果必须返回“数据项ำ值的列ต表”例如是另一个查询的结果，则ท上述查询语句建议的执行路径非常合适。只要每个ฐ数据项的历史信息数量都较少，以in列ต表或子查询取代内层查询中ณ的somevalue，会使整个查询执行更高效。也可以用in子句取代“相等性条件”，在多数情况下没有什么不同；但偶有例外，例如，如果用户输错了item_id，采用in时会返回未现数据，而采用“相等性条件”时会返回错误数据。使用olaທp函数我们在自连接色lf-ๅjoin情况下，使用了诸如ro9๗ap函数，它们在查询“特定日期某数据项的值”时也同样有用甚至高效。但记住，olaທp函数会带来非关系的处理模式注5๓。注意olaທp函数属于sql的非关系层。这类函数的作用是：在查询中ณ做最后或几乎ๆ是最后处理。因为它们在过滤已完成后对结果集进行处理。运用ro9๗_numbຘer等函数，可以通过日期排序判ศ断数据的“新旧程度degreeoffreshness”也就是距离现在有多久：
-ๅ-ๅ--ๅ-------ๅ-ๅ--ๅ----ๅ-ๅ-ๅ---page7๕5๓-ๅ--ๅ-ๅ-ๅ-ๅ-ๅ--ๅ--ๅ----ๅ----ๅ-ๅ-ๅ--ๅ
色le9๗umberoverpartitionbyitem_ຕidorderbyre9ess,9haທteverfromhist_ຕdataທ9hereitem_id=somevalueaທndre9๗ce_date选取最新数据，只需保留freshness值为1้的记录：色le9sfrom色le9umbຘeroverpartitionbຘyitem_ຕidorderbຘyre9ess,ไ9hateverfromhist_ຕdaທta9hereitem_id=somevaທlueandre9ce_ຕdaທteasx9herexfreshness=1理论上，使用olap函数方แ法和子查询几乎ๆ没有差ๆ异。实际上，olaທp函数只访问一次表，即使需要为ฦ此而进行排序操作也不例外。olap函数对表不需要做额外的访问，甚至在使用主ว键快存取时也是如此。因此，采用olap函数度会比较快尽管只是快一点点。少量数据项ำ、大量历史数据maທnyhistoricalvaluesperitem当存在大量历史数据时，情况有所不同——例如，监控系统中ณ采集“度量值”的频率很高。这里的困难在于，必须根据对极大量的数据进行排序，才能ม找到เ特定日期或最接近特定日期的值。排序是代价很高的操作：如果我们应用第4๒章的原则，降低非关系层厚度的唯一方法，就是在关系层多做一些工作，增加过滤条件的数量。此时，针对所需数据更精确地归类日期或时间以缩小范围，便非常重要。如果我们只上限，就必须扫描并排序所有历史数据。所以如果数据的采集频率很高，下限是有必要的。如果我们成功地把记录的“工ื作集”控制在可管理的大小，就相当于回到了“少量历史记录”的情况。如果无法同时指定上限例如当前日期和下限，我们的唯一希望就是根据数据项ำ分区；我们只需在单一分区上操作，这比较接近“大结果集”的情况。结果集和别的数据存在与否有关result色tpredi9๗cນeofdata一个ฐ表中的哪些记录和另一个表中ณ的数据不匹配？这种“识别ี例外”的需求经常出现。人们最常想到的解决方แ案有两个：notin搭配非关联子查询，或者notexists
-ๅ-ๅ-ๅ----ๅ-----ๅ-ๅ--ๅ-ๅ-ๅ-ๅ-----ๅpage76-ๅ-ๅ--ๅ---ๅ-ๅ-ๅ-ๅ---------ๅ-ๅ--ๅ-
搭配关联子查询。一般认为ฦ应该使用notexists。在子查询出现在高效搜索ิ条件之后，使用notexists是对的，因为高效过滤条件已๐清除大量无关数据，关联子查询当然会很高效。但当子查询恰好是唯一条件时，使用notin比较好。查找在另一个表无对应数据的记录时，会碰到一些奇特的解决方案。以下为ฦ实际例子，显示哪些数据库查询代价最高。注意，问号是占位符pla9๗dvariaທble，它们的具体值在后续执行中传递给查询：in色rtintottmpoutcustcode,suistrcod,cນempdtcນod,bkgaທreaທcod,mgtaທreacod,ไrityp,riflg,usr,ไ色q,9g,sig色9cນtcustcode,?,?,?,mgtareaທcນod,?,ไ?,usr,色q,9๗g,sig色csuifromttmpouta9๗herea色q=?aທnd0่=色le9tfromttmpoutbຘ9herebຘsuistr9dbຘ9๗dbຘbຘkgarea9diflg=?aທndb色q=?此例并非暗示我们无条件地认可临时表的使用。另外，我怀疑这个ฐin色rt语句会被循环执行，通
---------ๅ--ๅ----ๅ--ๅ-ๅ-ๅ----paທge77๕----ๅ----ๅ-ๅ-ๅ--ๅ----ๅ--ๅ----ๅ-ๅ
过消เ除循环可以适当改善性能。例子中出现了自参照色lf-ๅreference很不常见的用法：对一个表的插入操作，是以同一个ฐ表上的色lect为基础的。当前存在哪些记录？要创น建的记录是否不存在？要插入的记录是由上述两个问题决定的。使用9๗t测试某些数据是否存在是个ฐ糟糕的主意：为ฦ此dbms必须搜索并找出所有相符的记录。其实，此时应该使用exists，它会在遇到第一个相符数据时就停止。当然，如果过滤条件是主ว键，使用9๗t或exists的差别不大，否则ท差异极大——无论如何，从语义角度讲，若想表达：aທndnotexists色le9๗d0่=色le9t时，优化器“可能”会进行合理的优化——但未必一定如此。记录的数量若通过独立步骤被计入某个变量，优化器肯定不会优化，因为ฦ优化器再聪明也无法猜测计数的用途：9๗t的结果可能是极重要的值，而且必须ี显示给最终用户！然而，当我们只想建立一条新记录，且新记录要从已存在于表中ณ的记录推导出来时，正确的做法是使用诸如ex9๗us这样的集合操作符色toperaທtor。in色rtintottmpoutcustcນode,suistrcນod,ไcນempdtcod,bkgareacod,ไmgtaທreacod,rityp,riflg,usr,ไ色q,ไ9g,ไsig色csui色lecນtcustcode,ไ?,?,?,mgtareaທcນod,?,?,ไusr,色q,9๗g,
-----ๅ-ๅ-ๅ-----ๅ--------ๅ--ๅ-page78-ๅ-ๅ-ๅ----ๅ--ๅ--ๅ----ๅ-ๅ-ๅ-ๅ-----ๅ
sig色csuifromttmpout9๗here色q=?excນept色lecນtcustcode,ไ?,ไ?,ไ?,ไmgtaທreacນod,?,ไ?,usr,色q,ไ9g,sig色csuifromttmpout9heresuistr9๗d9dbkgareaທ9driflg=?and色q=?集合操作符的重大优点是彻底打破了“子查询强加的时间限制ๆ”，无论子查询是关联子查询还是非关联子查询。打破“时间限制ๆ”是什么เ意思？当存在关联子查询时，就必须ี执行外层查询，接着对所有通过过滤条件的记录，执行内层查询。外层查询和内层查询相互依赖，因为外层查询会把数据传递给内层查询。使用非关联子查询时情况要好得多，但也不是完全乐观：必须ี先完成内层查询之ใ后，外层查询才能介入。即使优化器选择把整个查询作为哈希连接hashjoin执行——这是聪明的方法——也不例外，因为要进行哈希连接，sql引擎必须先进行表扫描以建立哈希数组hashaທrray。相比之ใ下，使用集合操作符union、inter色cນt或except时，查询中ณ的这些组成部分不会彼此依赖，从而不同部分的查询可以并行执行。当然，如果有个步骤非常慢，而其他步骤非常快，则ท并行化意义不大；另外，如果查询的两个部分工ื作完全相同，并行化就没有好处，因为不同进程的工作是重复的，而不是分工负责。一般而言，在最后步骤之前，让所有部ຖ分并行执行会很高效，最后步骤把不完整的结果集组合起来——这就是分而治之。集合操作符的使用有个额外的问题：各部ຖ分查询必须返回兼容的字段——字段的类型和数量都要相同。下例实际案例，来自账单程序通常不适合集合操作符：色lecນt9๗haທtever,sumdtaxfromi女oice_detaທild,ไ
------ๅ----ๅ-ๅ-ๅ------ๅ-----page79-ๅ-----ๅ-ๅ-ๅ-----ๅ---------ๅ-ๅ
i女oice_extraທcນtore9hereepgaທ_staທtus=0orerd_staທtus=0andsuitable_join_9ddtype_93๑,ไ7๕,2ordtype_ຕ9ddsubtype_9๗s_9s_ຕdes9s_ຕ9๗6๔,7g肉pby9haທt_is_requiredhavingsumdtaທx!=0最后一个ฐ条件有问题它使我想起了《绿野仙踪》里的黄砖路，甚至使我做起了“负税率”的白日：sumdtaທx!ำ=0如前所述，换成下列ต条件更加合理：aທnddtax0上述的例子中，使用集合操作符会相当笨拙，因为必须访问i女oicນe_detail表好几次——如你所料，那不是个轻量级的表。当然，还要看每个条件的可选择性，如果type_ຕcນode=4很少见，那么เ它就是个ฐ可选择性很高的条件，exists或许会比notin更适合。另外，如果trans_ຕdes9๗正好是个ฐ小型表或者相对较小，尝试通过单独操作测试存在性，并起不到เ改善性能ม的效果。另一个表达非存在性的方法很有趣——而且通常相当高效——是使用外连接outerjoin。外连接的主ว要目的是，返回来自一个ฐ表的所有信息及连接表中的对应信息。无对应信息的记录也需返回——查找另一个ฐ表中无对应信息的数据时，这些记录正好是我们的兴趣所在，可通过检查连接表的字段值是否为null找出它们。例如：色le9voice_detail9heretype_9๗dsubຘtype_ຕ9๗s_9s_des9s_ຕ9๗6,ไ7或重写为ฦ：色le9voi9trans_ຕdes9๗s_des9s_9๗s_des9๗voice_detailsubຘtype_ຕcນode
-------ๅ------ๅ--ๅ----ๅ----paທge80--ๅ---------ๅ-ๅ--ๅ-ๅ-ๅ-ๅ-ๅ--ๅ--ๅ-
9๗aptiontrans_ຕ9子句中加上traທns_ຕcategory的条件。有人认为ฦ它应该出现在9here子句中，实际上，在连接之前或在连接之后过滤都不影响结果当然，根据这个条件和连接条件本身的可选择性不同，会有不同的性能表现。然而，在使用空值上的条件时，我们别ี无选择，只有在连接后才能做检查。外连接有时需要加distin9๗非关联子查询，来检查数据是否存在的差异很小，因为ฦ连接所使用的字段，正好与比较子查询结果集的字段完全相同。不过，众所周知的是，sql语言的“查询表达式风格”对“执行模式”影响很大，尽管理论上不是这么เ说的。这取决于优化器的复杂程度，以及它是否会以类似方法处理这两类查询。换言之，sql不是真正的声明性语言sqlisnotatrulyde9๗guaທge，尽管优化器不断推陈出新า改善sql的可靠性relia逼lity。最后提醒一下，应密切注意null，这个舞会扫兴者paທrty-poopers经常出现。虽然在in子查询中，null与大量非空值连接不会对外层查询造成影响，但在使用notin子查询时，由内层查询返回的null会造成notin条件不成立。要确保子查询不会返回null并不需要太高的代价，而且这么เ做可以避免许多灾难。总结：数据集可以通过各种技巧进行比较，但一般而言，使用外连接和子集合操作符更高效。当前值9tvalues当我们只对最近或当前值感兴趣时，如何避免使用嵌套子查询或olap函数两ä者都引起排序而直接找到เ适当值，是非常吸引人的设计。如第1้章所述，解决该问题的方法之一，就是把每个值与某个“截止日期”相关联——就像麦片外盒上的“保质期bestbefore”一样——并让当前值的“截止日期”是遥远的未来例如公元299๗9年12๐月3๑1日。这种设计存在一些与实际相关的问题，下面讨论这些问题。使用“固定日期”，确定当前值变得非常容易。查询如下所示：色lect9hateverfromhist_data9hereitem_ຕid=somevalueaທndrecord_ຕdate=fixed_ຕdaທte_in_thefuture接着，通过主ว键找到正确的记录。当然，要参照ั的日期如果不是当前日期，就必须使用子查询或olap函数了。然而，这种方法有两个ฐ主ว要缺点。较明显的缺点：插入新า的历史数据之前，先要更新“当前值”例如今天，接着，将最新“当前值”和历史数据一起插入表中。这个过程导致工ื作量加倍。更糟的是，关系理论中ณ的主键用于识别记录，但具有唯一性的item_id,ไrecord_date却不能ม作为主键，因为我们会对它做“部ຖ分更新าpaທrtiaທllyupdate”。因此，必须有一个能ม让外键参照ั的代理键id字段或序列ต号，结果程序变得更加复杂。大型历史表的麻烦就是，通常它们也经历过高频๗率的数据插入，所以数据量才
-----ๅ-----ๅ-ๅ-ๅ-----ๅ------ๅpaທge81้-ๅ-ๅ-ๅ-ๅ--------ๅ--ๅ----ๅ---ๅ-ๅ-ๅ
会这么เ大。快查询的好处，能抵销缓慢插入的缺点吗？这很难说，但绝对是个值得考虑的问题。还有个微妙的缺点与优化器有关。优化器使用各种详细程度不同的统计数据，检查字段的最低值和最高值，尝试评估值的分布情况。假设历史表包含了自20่0่0่年1้月1日开始的历史数据。于是，我们的数据组成是“散布在几年间的9๗9๗9๗%的历史数据”加上“2๐99๗9年12月3๑1้日的0่1%的‘当前数据’”。因此，优化器会认为数据散布在一千年的范围内。优化器在数据范围上的偏差ๆ是由á于查询中出现的上限日期的误导即“aທndrecord_daທte=fixed_daທte_in_ຕthefuture”。此时的问题就是，如果你当查询的不是当前值例如，你要统计不同时段的数据变化，优化器可能错误地做出“使用索引”的决定——因为你访问的只是千年中的极小部ຖ分——但实际上需要的是对数据进行扫描。是优化器的评估偏差ๆ导致它做出完全错误的执行计划决定，这很难修正。总结：要理解优化器如何看待你的系统，就必须ี理解你的数据和数据分布方แ式。通过聚合获得结果集result色tobຘtainedbຘyaggregaທtion本节讨论一类极常见的情况：对一个ฐ或多个主ว表maintable中的详细数据进行汇总，动态计算出结果集。换言之，我们面临数据聚合aggregationofdata的问题。此时，结果集大小取决于g肉pby的字段的基数，而不是查询条件的精确性。正如第一节“小结果集，直接条件”中ณ所述，对表进行一趟asinglepass处理获得的并非真正聚合的结果否则就需要自连接和多次处理，但此时聚合函数或聚合也相当有用。实际上，最让人感兴趣的sql聚合使用技巧，不是明显需要sum或avg的情况，而是如何将过程性处理转化为ฦ以聚合为基础的纯sql替代方แ案。如第2章所强调的，编写高效sql代码的关键，第一是“勇往直前”，即不要预ไ先检查，而是查询完成后测试是否成功——毕竟，蹑手蹑脚地用脚趾试水赢不了游泳比赛。第二是尽量把更多“动作”放到เsql查询中ณ，此时聚合函数特别ี有用。优秀sql编程的困难，多半在于解决问题的方แ式：不要将“一个问题”转换成对数据库的“一系列查询”，而是要转换成“少数查询”。程序用大量中间变量保存从数据库读出的值，然后根据变量进行简单判ศ断ษ，最后再把它们作为ฦ其他查询的输入……这样做是错误的。糟糕的sql编程有个显着特点，就是在sql查询之ใ外存在大量代码，以循环的方式对返回数据进行些加、减、乘、除之类的处理。这样做毫无价值、效率低下，这里工作应该交给sql的聚合函数。注意：聚合函数非常有用，可以解决不少sql问题第11้章会再次讨论。然而，我现开者通常只使用最平常的聚合函数9๗t，它对大多数程序是否真的有用值得怀疑。第2章说明了使用9๗t判ศ定是否要更新记录插入新า记录是很浪费的。你可能在报表中误
--------ๅ--ๅ----ๅ-----ๅ-ๅ-ๅ--page8๖2------ๅ-ๅ--ๅ-ๅ-ๅ-ๅ---ๅ--------
用了9t。测试存在性有时会以模仿布尔值的方式实现：9๗'el色'y'end对于上述实现，只要存在与条件相符的记录，就会读取其中ณ每条记录。其实，只需找到เ一条记录就足以判ศ断要显示ิy还是n，通过测试存在性或限制返回记录数可以写出更高效的语句，一旦现条件相符就停止处理即可。当要解决的问题与最多、最少、最大、第一、最后有关时，聚合函数可能ม会当成olap函数使用很可能是最佳选择。也就是说，不要认为聚合函数仅支持9、avg等功能，否则就说明你还没有充分理解聚合函数。有趣的是，聚合函数在作用范围上非常狭窄。除了计算最大值和最小值，它们唯一能做的就是简单的算术运算：9๗t每遇到的一行加1；aທvg一方面将字段值累加，另一方面不断ษ加1้计数，最后进行除法运算。聚合函数有时可取得令人吃惊的效果，比如通过sum就可以做很多事情。喜欢数学的朋友知道，通过对数和次方函数，要在sum和乘๖积producນt之间转换有多简单。喜欢逻辑的朋友也会知道or很依赖sum，而and很依赖乘积。下面通过简单的例子说明聚合的强大作用。假设要进行装ณ运shipment处理，一次装ณ运由一些不同的订单组成，每张订单都必须ี分别做准备；只有装运涉及的每张订单都完成时装运才准备就绪。问题就是，如何判断ษ装ณ运涉แ及的所有订单都已完成。这样的情况常会生，有多种方法可以判定装ณ运是否就绪。最糟的方แ法是逐一判断每批装ณ运，而每批装ณ运内部进行第二个ฐ循环，查看有多少张订单的order_plete字段值为ฦ“n”，并返回计数为0的装ณ运id。更好的解决方案是理解“‘n’值的不存在性测试”的意图，并用子查询无论是关系还是非关系完成：色le9๗t_idfromshipments9๗herenotexists色le9ullfro摸rders9hereorder_plete='n'ูandordersshipment_id=shipmentsshipment_id如果表shipments上没有其他条件了，则上述方法很糟糕，当shipments表数据量大时而且未完成订单占少数，换成以下查询会更高效：色le9๗t_idfromshipments9๗hereshipment_ຕidnotin色le9๗t_ຕidfro摸rders9hereorder_plete='ูn'ู上述查询也可以稍作变形，优化器比较喜欢这个变形，但要求orders表的shipment_ຕid字段上有
--------ๅ--ๅ----ๅ-ๅ-ๅ-ๅ------page8๖3---ๅ---ๅ-ๅ-ๅ---ๅ----ๅ--ๅ----ๅ--
索ิ引：色le9t_idfromshipmentsleftouterjoinordersonordersshipment_id=shipmentsshipment_idandordersorder_ຕplete='n'ู9๗hereordersshipment_ຕidisnull另一个替代方แ案是借助集合操作，该集合操作会使用shipments主键索ิ引，且对orders表进行全表扫描：色le9t_ຕidfromshipmentsex9t_idfro摸rders9๗hereorder_ຕplete='ูn'注意，并非所有dbຘms都实现了ex9us。还有一种方法。主要是对装ณ运中所有订单执行逻辑and操作，将order_plete为true的订单的id返回。这类操作在现实中很常见。如前所述，aທnd和乘๖法、or和加法之间关系密切。关键是把诸如“y”和“n”的flag值转换为ฦ0和1้，使用caທ色结构即可。要把order_plete转成0或1้的值可以这样写：色le9๗t_id,9order_ຕplete='y'then1el色0่endflaທgfro摸rders到目前为ฦ止，一切顺利ำ。如果每批装运包含的订单数固定的话，则很容易对适当字段进行sum后检查是否为预期订单数。然而，实际上希望每批装运的flag值相乘๖，并检查结果是0或是1。这个方แ法是可行的，因为只要有一张以0表示的未完成订单，乘๖法的最后结果就是0。乘๖法运算可由对数运行协助完成虽然在以对数处理时，0่不是最简单的值，但我们这个ฐ例子要做的甚至更简单。我们想要的是“第一张订单已完成、且第二张订单已完成……且第n张订单已完成”。德摩根定律laທ9sofdeman注4告诉我们，这等价于“第一张订单未完成、或第二张订单未完成……或第n张订单未完成”的情况“不成立”。由于使用聚合时，or比aທnd更容易处理。检查由or连结的一连串条件是否不成立，比检查由and连结的一连串ธ条件是否成立，要容易得多。我们要考虑的真正“谓词predicນate”是“订单未完成”，并对order_plete标志作转换，如果是n就转换为1，如果是y就转换为ฦ0。之ใ后，通过加总flaທg值，就可检查是否所有订单的flaທg值都是0都已๐完成——如果总和是0，所有订单都已完成。因此，查询可写成：色le9t_id
---------ๅ--ๅ----ๅ---ๅ-ๅ-ๅ---paທge84๒----ๅ-----ๅ-ๅ-ๅ-ๅ----ๅ--ๅ----ๅ-
from色lepbyshipment_ຕidhavingsumflag=0่甚至可以写得更简洁：色le9t_idfro摸rdersg肉pbຘyshipment_idhaທvingsum9'ูthen1้el色0่end=0还有更简单的方แ法。使用另一个聚合函数，而不必转换任何的flaທg值。注意，从字母的顺ิ序来看，“y”大于“n”，如果所有的值都是“y”，则ท最小值就是“y”。于是：色le9๗t_ຕidfro摸rdersg肉pbyshipment_ຕidhavingminorder_ຕplete='y'这个方แ法利用了“y”大于“n”，而没有考虑标志转换为数值。本方แ法更高效。上例使用了g肉pby，并以order_ຕplete值最小作为查询条件，那么，其中ณ不同的子查询或作为ฦ子查询替代品的聚集函数之间是如何比较的呢？如果先做sum操作而后检查总和是否为ฦ0่，必然导致整个ฐorders表排序。而上例中ณ使用了不太常见的聚合函数min，一般比其他查询快，其他查询因访问两个ฐ表shipments和orders而度较慢。先前的例子大量使用了haທving子句。如第4章所述，“粗心的sql语句”往往和在聚合语句中ณ使用having子句有关。下面这个查询oracນle就是一例，它要查询过去一个月内每个产品的每周销售情况：色le9cນsaທle_date,'9๗eek'ู,ไsumsold_qtyfromsales_historyg肉pbyprodu9csale_date,'9๗eek'havingtruncນsale_daທte,'9eek'ู=add_ຕ摸nthsysdate,-1这里的错误在于，having子句中的条件没有使用聚合。于是，dbms必须处理saທles_history中的每条记录，进行排序操作、进行聚合操作……然后过滤掉过时的数值，最后返回结果。这类错误并不引人注意，直到เsales_history表数据量变得非常大为止。当然，正确的方法是把条件放在9here子句中ณ，确保过滤会生在早期阶段，而之ใ后要处理的数据集已大为减小。
-------ๅ----ๅ--ๅ----ๅ---ๅ-ๅ-ๅ-ๅpage85๓--ๅ----ๅ-----ๅ-ๅ-ๅ-----ๅ-----
必须ี指出：对视图即聚合的结果应用条件时，如果优化器不够聪明，没有在聚合前再次注入过滤条件，我们就会遇到完全相同的问题。有些过滤条件生效太晚，应该提前，可做如下修改：色lectcustomer_ຕidfro摸rders9hereorder_ຕdate0在这个查询中ณ，以下having的条件乍看起来相当合理：haທvingsuma摸unt0่然而，如果aທ摸unt只能ม是正数或零，这种haທving用法就不合理。最好改为：9hereaທ摸unt0此例中，g肉pby的使用分两ä种情况。先：色lectcustomer_idfro摸rders9hereorder_daທte0่g肉pbycນustomer_id我们注意到เ，g肉pby对聚合计算是不必要的，可以用distinct取代它，并执行相同的排序和消除重复项目的工ื作：色le9๗cນtcustomer_ຕidfro摸rders9hereorder_date0่把条件放在9๗here子句中，能让多余的记录尽早被过滤掉，因而更高效。总结：聚合操作的数据应尽量少。
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━