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

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


　　join　orders　o　

　　on　o。custid　=　c。custid　

　　join　orderdetail　od　

　　on　od。ordid　=　o。ordid　


…………………………………………………………Page　40……………………………………………………………

　　join　articles　a　

　　on　a。artid　=　od。artid　

　　where　c。city=　'GOTHAM'　

　　and　a。artname　=　'BATMOBILE'　

　　and　o。ordered　》=　somefunc　

其中，somefunc是个函数，返回距今六个月前的具体日期。注意上面用了distinct，因为考虑到　

某个客户可以是大买家，最近订购了好几台蝙蝠车。　

暂不考虑优化器将如何改写此查询，我们先看一下这段代码的含义。首先，来自customers表的　

数据应只保留城市名为　Gotham　的记录。接着，搜索orders表，这意味着custid字段最好有索　

引，否则只有通过排序、合并或扫描orders表建立一个哈希表才能保证查询速度。对orders表，　

还要针对订单日期进行过滤：如果优化器比较聪明，它会在连接（join）前先过滤掉一些数据，　

从而减少后面要处理的数据量；不太聪明的优化器则可能会先做连接，再作过滤，这时在连接　

中指定过滤条件利于提高性能，例如：　

　　join　orders　o　

　　on　o。custid　=　c。custid　

　　and　a。ordered　》=　somefunc　



即使过滤条件与连接（join）无关，优化器也会受到过滤条件的影响。例如，若orderdetail的主　

键为（ordid；　artid），即ordid为索引的第一个属性，那么我们可以利用索引找到与订单相关的记　

录，就和第3章中讲的一样。但如果主键是（artid；　ordid）就太不幸了（注意，就关系理论而言，　

无论哪个版本都是完全一样），此时的访问效率比（ordid；　artid）作为索引时要差，甚至一些数　

据库产品无法使用该索引（注3），唯一的希望就是在　ordid　上加独立索引了。　

连接了表　orderdetail和orders之后，来看articles表，这不会有问题，因为表　orderdetail　主键　

包括　artid字段。最后，检查　articles　中的值是否为Batmobile。查询就这样结束了吗？未必结　

束，因为用了distinct　，通过层层筛选的客户名还必须要排序，以剔除重复项目。　

分析至此，可以看出这个查询有多种编写方式。下面的语句采用了古老的join语法：　

　　　select　distinct　c。custname　

　　　from　customersc；　

　　　orders　o；　

　　　orderdetail　od；　

　　　articles　a　

　　　where　c。city=　'GOTHAM'　

　　　and　c。custid　=　o。custid　

　　　and　o。ordid　=　od。ordid　

　　　and　od。artid　=　a。artid　

　　　and　a。artname　=　'BATMOBILE'　

　　　and　o。ordered　》=　somefunc　



本性难移，我偏爱这种较古老的方式。原因只有一个：从逻辑的角度来看，旧方法突显出数据　

处理顺序无足轻重这一事实；无论以什么顺序查询表，返回结果都是一样的。customers　表非　

常重要，因为最终所需数据都来自该表，在此例中，其他表只起辅助作用。注意，没有适用于　


…………………………………………………………Page　41……………………………………………………………

所有问题的解决方案，表连接的方式会因情况不同而异，而决定连接方式取决于待处理数据的　

特点。　



特定的SQL查询解决特定的问题，而未必适用于另一些问题。这就像药，它能治好这个病人，　

却能将另一个病人医死。　



蝙蝠车买主的进一步讨论　



下面看看查询蝙蝠车买家的其他方法。我认为，避免在最高层使用distinct应该是一条基本规则。　

原因在于，即使我们遗漏了连接的某个条件，distinct也会使查询“看似正确”地执行——无可否　

认，较旧的SQL语法在此方面问题较大，但ANSI/SQL92　在通过多个字段进行表的连接时也可　

能出现问题。发现重复数据容易，但发现数据不准确很难，所以避免在最高层使用distinct应该　

是一条基本规则。　



发现结果不正确更难，这很容易证明。前面使用　distinct　返回客户名的两个查询，都可能返回　

不正确结果。例如，如果恰巧有多位客户都叫“Wayne”，distinct不但会剔除由同个客户的多张　

订单产生的重复项目，也会剔除由名字相同的不同客户产生的重复项目。事实上，应该同时返　

回具唯一性的客户ID和客户名，以保证得到蝙蝠车买家的完整清单。在实际中，发现这个问题　

可不容易。　



要摆脱　distinct，可考虑以下思路：客户在　Gohtam市，而且满足存在性测试，即在最近六个　

月订购过蝙蝠车。注意，多数（但非全部）　SQL　方言支持以下语法：　

　　select　c。custname　

　　from　customers　c　

　　where　c。city=　'GOTHAM'　

　　and　exists　（select　null　

　　from　orders　o；　

　　orderdetail　od；　

　　articles　a　

　　where　a。artname　=　'BATMOBILE'　

　　and　a。artid　=　od。artid　

　　and　od。ordid　=　o。ordid　

　　and　o。custid　=　c。custid　

　　and　o。ordered　》=　somefunc　）　

上例的存在性测试，同一个名字可能出现多次，但每个客户只出现一次，不管他有多少订单。　

有人认为我对　ANSI　SQL　语法的挑剔有点苛刻（指“蝙蝠车买主”的例子），因为上面代码中　

customers表的地位并没有降低。其实，关键区别在于，新查询中customers表是查询结果的唯　

一来源（嵌套的子查询会负责找出客户子集），而先前的查询却用了join。　



这个嵌套的子查询与外层的　select关系十分密切。如代码第　11　行所示（粗体部分），子查询　

参照了外层查询的当前记录，因此，内层子查询就是所谓的关联子查询（correlated　subquery）。　

此类子查询有个弱点，它无法在确定当前客户之前执行。如果优化器不改写此查询，就必须先　

找出每个客户，然后逐一检查是否满足存在性测试，当来自Gotham市的客户非常少时执行效率　

倒是很高，否则情况会很糟（此时，优秀的优化器应尝试其他执行查询的方式）。　



我们还可以这样编写查询：　


…………………………………………………………Page　42……………………………………………………………

　　　select　custname　

　　　from　customers　

　　　where　city　=　'GOTHAM'　

　　　and　custid　in　

　　　（select　o。custid　

　　　from　orders　o；　

　　　orderdetail　od；　

　　　articles　a　

　　　where　a。artname　=　'BATMOBILE'　

　　　and　a。artid　=　od。artid　

　　　and　od。ordid　=　o。ordid　

　　　and　o。ordered　》=　somefunc）　



在这个例子中，内层查询不再依赖外层查询，它已变成了非关联子查询（uncorrelated　

subquery），只须执行一次。很显然，这段代码采用了原有的执行流程。在本节的前一个例子中，　

必须先搜寻符合地点条件的客户（如均来自GOTHAM），接着依次检查各个订单。而现在，订　

购了蝙蝠车的客户，可以通过内层查询获得。　



不过，如果更仔细地分析一下，前后两个版本的代码还有些更微妙的差异。含关联子查询的代　

码中，至关重要的是orders　表中的　custid字段要有索引，而这对另一段代码并不重要，因为这　

时要用到的索引（如果有的话）是表customers的主键索引。　



你或许注意到，新版的查询中执行了隐式的　distinct。的确，由于连接操作，子查询可能会返回　

有关一个客户的多条记录。但重复项目不会有影响，因为　in　条件只检查该项目是否出现在子　

查询返回的列表中，且in不在乎某值在列表中出现了一次还是一百次。但为了一致性，作为整　

体，应该对子查询和主查询应用相同的规则，也就是在子查询中也加入存在性测试：　

　　　select　custname　

　　　from　customers　

　　　where　city　=　'GOTHAM'　

　　　and　custid　in　

　　　（select　o。custid　

　　　from　orders　o　

　　　where　o。ordered　》=　somefunc　

　　　and　exists　（select　null　

　　　from　orderdetail　od；　

　　　articles　a　

　　　where　a。artname　=　'BATMOBILE'　

　　　and　a。artid　=　od。artid　

　　　and　od。ordid　=　o。ordid））　



或者：　


…………………………………………………………Page　43……………………………………………………………

　　　select　custname　

　　　from　customers　

　　　where　city　=　'GOTHAM'　

　　　and　custid　in　

　　　（select　custid　

　　　from　orders　

　　　where　ordered　》=　somefunc　

　　　and　ordid　in　（select　od。ordid　

　　　from　orderdetail　od；　

　　　articles　a　

　　　where　a。artname　=　'BATMOBILE'　

　　　and　a。artid　=　od。artid）　



尽管嵌套变得更深、也更难懂了，但子查询内应选择　exists　还是in　的选择规则相同：此选择　

取决于日期与商品条件的有效性。除非过去六个月的生意非常清淡，否则商品名称应为最有效　

的过滤条件，因此子查询中用in　比　exists　好，这是因为，先找出所有蝙蝠车的订单、再检查　

销售是否发生在最近六个月，比反过来操作要快。如果表　orderdetail　的artid字段有索引，这　

个方法会更快，否则，这个聪明巧妙的举措就会黯然失色。　



注意　

每当对大量记录做存在性检查时，选择in还是exists须斟酌。　

利于多数　SQL　方言，非关联子查询可以被改写成from　子句中的内嵌视图。然而，一定要记住　

的是，in　会隐式地剔除重复项目，当子查询改写为　from　子句中的内嵌视图时，必须要显式地　

消除重复项目。例如：　

　　select　custname　

　　from　customers　

　　where　city　=　'GOTHAM'　

　　and　custid　in　

　　（select　o。custid　

　　from　orders　o；　

　　（select　distinct　od。ordid　

　　from　orderdetail　od；　

　　articles　a　

　　where　a。artname　=　'BATMOBILE'　

　　and　a。artid　=　od。artid）　x　

　　where　o。ordered　》=　somefunc　

　　and　x。ordid　=　o。ordid）　



编写功能等价的查询时，不同的编写方式就好像同义词。在书面语和口语中，同义词的意思虽　


…………………………………………………………Page　44……………………………………………………………

然大致相同，但又有细微差异，因此某个词在特定语境中更合适。同样，数据和处理的具体实　

现细节可以决定选择哪种查询方式。　



蝙蝠车买主案例总结　



前面讨论的各段SQL语句，看似意义不大的编程技巧练习，实则不然。关键是“擒获（attack）”　

数据的方法有很多，不必按照先customers、然后orders、接着orderdetail和articles的方式来编　

写查询。　

现在以箭头表示搜索条件的强度——条件分辨力越强，箭头就越大。假设　Gotham市的客户非　

常少，但过去六个月的销售业绩不错，卖出了很多蝙蝠车，此时规划图如图4…6所示。虽然商品　

名称之上有个过滤条件，但图中的中等大小的箭头指向了表orderdetail，因为该表是真正重要　

的表。待售商品可能很少，反映出销售收入的百分比；也可能待售商品很多，最畅销的商品之　

一就是蝙蝠车。　

相反，如果我们假设多数客户在　Gotham市，但其中很少的客户买了蝙蝠车，则规划图如图4…7　

所示。很显然，此时表orderdetail　是最大的目标。来自这个表的数据的数据量缩减速度越快，　

查询执行得就越快。　

还要注意的非常重要的一点是，“过去六个月”并不是个非常精确的条件。但如果我们把条件改为　

过去两个月，而库中有十年的销售记录，会发生什么呢？在这种情况下，如果能先访问到近期　

的订单（借助第5章中描述的一些技术，这些数据或许就聚集在一起），查询的效率就会更高些；　

找出近期订单后，一方面选取Gotham　的客户，另一方面则选取蝙蝠车订单。所以，换个角度　

来看，最好的执行计划并不只相依于数据值，还应该随着时间而不断进化。　



好了，总结一下。首先，解决问题的方法不只一种……而且查询的编写方式经常会与数据隐含　

的假设相关。殊途同归，最终的结果集都是一样的，但执行速度可能有极大差异。查询的编写　

方式会影响执行路径，尤其是应用无法在真正的关系环境中表达的条件时。若想让优化器发挥　

极致，我们就必须扩大关系处理的工作量，并确保非关系的部分对最后结果集的影响最小。　



本章前面一直假设代码的执行方式与编写方式一样，但其实，优化器可能改写查询——有时改　

动还很大。你或许认为优化器所做的改写无关紧要，因为　SQL本是一种声明性语言（declarative　

language），用它来说明想要什么，并让　DBMS　予以执行。然而，你也看到了，每次用不同方　

式改写查询时，都必须更新关于数据分布和已有索引的假设。因此有一点非常重要：应预先考　

虑优化器的工作，以确定它能找到所需数据——这可能是索引，也可能是数据相关的详细统计　

信息。　



总结：保证SQL　语句返回正确结果，只是建立最佳　SQL语句的第一步。　



大数据量查询　



Querying　Large　Quantities　of　Data　



越快剔除不需要的数据，查询的后续阶段必须处理的数据量就越少，自然查询的效率就越高，　

这听起来显而易见。集合操作符（set　operator）是这一原理的绝佳应用，其中的union使用最　

为广泛，我们经常看到通过union操作将几个表“粘”在一起。中等复杂程度的union语句较为常见，　

大多数被连接的表都会同时出现在union两端的select　语句中。例如下面这段代码：　


…………………………………………………………Page　45……………………………………………………………

　　　　　　select　。。。　

　　　　　fromA；　

　　　　　B；　

　　　　　C；　

　　　　　D；　

　　　　　E1　

　　　　　where　（condition　on　E1）　

　　　　　and　（joins　and　other　conditions）　



　　　　　union　

　　　　　select　。。。　

　　　　　fromA；　

　　　　　B；　

　　　　　C；　

　　　　　D；　

　　　　　E2　

　　　　　where　（condition　on　E2）　

　　　　　and　（joins　and　other　conditions）　



这类查询是典型的“照搬式”编程。为了提高效率，可以仅对代码中非共用的表（本例中即E1和　

E2）使用union，然后配合筛选条件，把　union　语句降级为内嵌视图。代码如下：　

　　　　select　。。。　

　　　　fromA；　

　　　　B；　

　　　　C；　

　　　　D；　

　　　　（select　。。。　

　　　　from　E1　

　　　　where　（condition　on　E1）　

　　　　union　

　　　　select　。。。　

　　　　from　E2　

　　　　where　（condition　on　E2））　E　

　　　　where　（joins　and　other　conditions）　



另一个“查询条件用错了