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

按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！



它们的具体值在后续执行中传递给查询：　

insertinto　ttmpout（custcode；　

suistrcod；　

cempdtcod；　

bkgareacod；　

mgtareacod；　

risktyp；　

riskflg；　

usr；　

seq；　

country；　

rating；　

sigsecsui）　

select　distinct　custcode；　

？；　

？；　

？；　

mgtareacod；　

？；　

？；　

usr；　

seq；　

country；　

rating；　

sigsecsui　

from　ttmpout　a　

where　a。seq=？　

and0=（select　count（*）　

from　ttmpout　b　

where　b。suistrcod　=？　

andb。cempdtcod　=？　

andb。bkgareacod　=？　

andb。risktyp　=？　

andb。riskflg=？　

andb。seq　=？）　

此例并非暗示我们无条件地认可临时表的使用。另外，我怀疑这个insert语句会被循环执行，通　


…………………………………………………………Page　77……………………………………………………………

过消除循环可以适当改善性能。　



例子中出现了自参照（self…reference）很不常见的用法：对一个表的插入操作，是以同一个表上　

的　select　为基础的。当前存在哪些记录？要创建的记录是否不存在？要插入的记录是由上述两　

个问题决定的。　

使用　count（*）　测试某些数据是否存在是个糟糕的主意：为此DBMS　必须搜索并找出所有相符　

的记录。其实，此时应该使用　exists，它会在遇到第一个相符数据时就停止。当然，如果过滤　

条件是主键，使用count或exists的差别不大，否则差异极大——无论如何，从语义角度讲，若想　

表达：　

andnot　exists（select　1。。。）　

不能换成：　

and0=（select　count（*）　。。。）　

使用count（*）时，优化器“可能”会进行合理的优化——但未必一定如此。记录的数量若通过独立　

步骤被计入某个变量，优化器肯定不会优化，因为优化器再聪明也无法猜测计数的用途：count（）　

的结果可能是极重要的值，而且必须显示给最终用户！　

然而，当我们只想建立一条新记录，且新记录要从已存在于表中的记录推导出来时，正确的做　

法是使用诸如except（有时称为　minus）这样的集合操作符（set　operator）。　

insertinto　ttmpout（custcode；　

suistrcod；　

cempdtcod；　

bkgareacod；　

mgtareacod；　

risktyp；　

riskflg；　

usr；　

seq；　

country；　

rating；　

sigsecsui）　

（select　custcode；　

？；　

？；　

？；　

mgtareacod；　

？；　

？；　

usr；　

seq；　



country；　

rating；　


…………………………………………………………Page　78……………………………………………………………

sigsecsui　

from　ttmpout　

where　seq=？　

except　

select　custcode；　

？；　

？；　

？；　

mgtareacod；　

？；　

？；　

usr；　

seq；　

country；　

rating；　

sigsecsui　

from　ttmpout　

where　suistrcod　=？　

andcempdtcod　=？　

andbkgareacod　=？　

andrisktyp　=？　

andriskflg　=？　

andseq　=？）　

集合操作符的重大优点是彻底打破了“子查询强加的时间限制”，无论子查询是关联子查询还是　

非关联子查询。打破“时间限制”是什么意思？当存在关联子查询时，就必须执行外层查询，接　

着对所有通过过滤条件的记录，执行内层查询。外层查询和内层查询相互依赖，因为外层查询　

会把数据传递给内层查询。　

使用非关联子查询时情况要好得多，但也不是完全乐观：必须先完成内层查询之后，外层查询　

才能介入。即使优化器选择把整个查询作为哈希连接（hash　join）执行——这是聪明的方法——　

也不例外，因为要进行哈希连接，SQL　引擎必须先进行表扫描以建立哈希数组（hasharray）。　



相比之下，使用集合操作符union、intersect或except时，查询中的这些组成部分不会彼此依赖，　

从而不同部分的查询可以并行执行。当然，如果有个步骤非常慢，而其他步骤非常快，则并行　

化意义不大；另外，如果查询的两个部分工作完全相同，并行化就没有好处，因为不同进程的　

工作是重复的，而不是分工负责。一般而言，在最后步骤之前，让所有部分并行执行会很高效，　

最后步骤把不完整的结果集组合起来——这就是分而治之。　



集合操作符的使用有个额外的问题：各部分查询必须返回兼容的字段　——　字段的类型和数量　

都要相同。下例（实际案例，来自账单程序）通常不适合集合操作符：　

select　whatever；　sum（d。tax）　

from　invoice_detail　d；　


…………………………………………………………Page　79……………………………………………………………

invoice_extractor　e　

where　（e。pga_status　=0　

ore。rd_status=0）　

andsuitable_join_condition　

and（d。type_code　in（3；7；2）　

or（d。type_code　=4　

andd。subtype_code　not　in　

（select　trans_code　

from　trans_description　

where　trans_categoryin（6；　7））））　

groupbywhat_is_required　

havingsum（d。tax）！=　0　

最后一个条件有问题（它使我想起了《绿野仙踪》里的黄砖路，甚至使我做起了“负税率”的白　

日梦）：　

sum（d。tax）！=　0　

如前所述，换成下列条件更加合理：　

andd。tax　》0　

上述的例子中，使用集合操作符会相当笨拙，因为必须访问invoice_detail表好几次——如你所　

料，那不是个轻量级的表。当然，还要看每个条件的可选择性，如果　type_　code=4很少见，那　

么它就是个可选择性很高的条件，exists或许会比notin（）更适合。另外，如果trans_description正　

好是个小型表（或者相对较小），尝试通过单独操作测试存在性，并起不到改善性能的效果。　



另一个表达非存在性的方法很有趣——而且通常相当高效——是使用外连接（outerjoin）。外连　

接的主要目的是，返回来自一个表的所有信息及连接表中的对应信息。无对应信息的记录也需　

返回——查找另一个表中无对应信息的数据时，这些记录正好是我们的兴趣所在，可通过检查　

连接表的字段值是否为null找出它们。　



例如：　

select　whatever　

from　invoice_detail　

where　type_code　=4　



andsubtype_code　notin　

（select　trans_code　

from　trans_description　

where　trans_categoryin（6；　7））　

或重写为：　

select　whatever　

from　invoice_detail　

outerjoin　trans_description　

on　trans_description。trans_category　in（6；　7）　

andtrans_description。trans_code　=invoice_detail。subtype_code　


…………………………………………………………Page　80……………………………………………………………

where　trans_description。trans_code　isnull　

我故意在join子句中加上trans_category的条件。有人认为它应该出现在where　子句中，实际上，　

在连接之前或在连接之后过滤都不影响结果（当然，根据这个条件和连接条件本身的可选择性　

不同，会有不同的性能表现）。然而，在使用空值上的条件时，我们别无选择，只有在连接后才　

能做检查。　



外连接有时需要加　distinct。实际上，通过外连接或notin（）非关联子查询，来检查数据是否存在　

的差异很小，因为连接所使用的字段，正好与比较子查询结果集的字段完全相同。不过，众所　

周知的是，SQL　语言的“查询表达式风格”对“执行模式”影响很大，尽管理论上不是这么说的。　

这取决于优化器的复杂程度，以及它是否会以类似方法处理这两类查询。换言之，SQL　不是真　

正的声明性语言（SQLis　not　a　truly　declarative　language），尽管优化器不断推陈出新改善SQL的　

可靠性（reliability）。　



最后提醒一下，应密切注意null，这个舞会扫兴者（party…poopers）经常出现。虽然在in（）子查　

询中，null与大量非空值连接不会对外层查询造成影响，但在使用notin（）子查询时，由内层查　

询返回的null会造成notin（）条件不成立。要确保子查询不会返回null并不需要太高的代价，而且　

这么做可以避免许多灾难。　

总结：数据集可以通过各种技巧进行比较，但一般而言，使用外连接和子集合操作符更高效。　



当前值　



CurrentValues　



当我们只对最近或当前值感兴趣时，如何避免使用嵌套子查询或　OLAP　函数（两者都引起排序）　

而直接找到适当值，是非常吸引人的设计。如第1章所述，解决该问题的方法之一，就是把每个　

值与某个“截止日期”相关联　——　就像麦片外盒上的“保质期（bestbefore）”一样——并让当前　

值的“截止日期”是遥远的未来（例如公元　2999　年　12　月　31　日）。这种设计存在一些与实际相　

关的问题，下面讨论这些问题。　



使用“固定日期”，确定当前值变得非常容易。查询如下所示：　

select　whatever　

from　hist_data　

where　item_id　=somevalue　

andrecord_date　=fixed_date_in_the　future　



接着，通过主键找到正确的记录。（当然，要参照的日期如果不是当前日期，就必须使用子查询　

或　OLAP　函数了。）然而，这种方法有两个主要缺点。　

　　较明显的缺点：插入新的历史数据之前，先要更新“当前值”（例如今天），接着，将最新“当　

前值”和历史数据一起插入表中。这个过程导致工作量加倍。更糟的是，关系理论中的主键用于　

识别记录，但具有唯一性的（item_id；　record_date）却不能作为主键，因为我们会对它做“部分更新　

（partiallyupdate）”。因此，必须有一个能让外键参照的代理键（ID字段或序列号），结果程序　

变得更加复杂。大型历史表的麻烦就是，通常它们也经历过高频率的数据插入，所以数据量才　


…………………………………………………………Page　81……………………………………………………………

会这么大。快速查询的好处，能抵销缓慢插入的缺点吗？这很难说，但绝对是个值得考虑的问　

题。　



　　还有个微妙的缺点与优化器有关。优化器使用各种详细程度不同的统计数据，检查字段的最　

低值和最高值，尝试评估值的分布情况。假设历史表包含了自　2000　年　1　月　1　日开始的历史　

数据。于是，我们的数据组成是“散布在几年间的99。9％　的历史数据”加上“2999　年　12　月　31　日　

的0。1％　的‘当前数据’”。因此，优化器会认为数据散布在一千年的范围内。优化器在数据范围　

上的偏差是由于查询中出现的上限日期的误导（即“andrecord_date　=fixed_date_in_the　future”）。　

此时的问题就是，如果你当查询的不是当前值（例如，你要统计不同时段的数据变化），优化器　

可能错误地做出“使用索引”的决定——因为你访问的只是千年中的极小部分——但实际上需要　

的是对数据进行扫描。是优化器的评估偏差导致它做出完全错误的执行计划决定，这很难修正。　



总结：要理解优化器如何看待你的系统，就必须理解你的数据和数据分布方式。　



通过聚合获得结果集　



Result　Set　ObtainedbyAggregation　



本节讨论一类极常见的情况：对一个或多个主表（main　table）中的详细数据进行汇总，动态计　

算出结果集。换言之，我们面临数据聚合（aggregation　of　data）的问题。此时，结果集大小取　

决于groupby的字段的基数，而不是查询条件的精确性。正如第一节“小结果集，直接条件”中所　

述，对表进行一趟（asingle　pass）处理获得的并非真正聚合的结果（否则就需要自连接和多次　

处理），但此时聚合函数（或聚合）也相当有用。实际上，最让人感兴趣的SQL聚合使用技巧，　

不是明显需要sum或avg的情况，而是如何将过程性处理转化为以聚合为基础的纯　SQL替代方　

案。　



如第2章所强调的，编写高效SQL代码的关键，第一是“勇往直前”，即不要预先检查，而是查询　

完成后测试是否成功　——　毕竟，蹑手蹑脚地用脚趾试水赢不了游泳比赛。第二是尽量把更多　

“动作”放到SQL　查询中，此时聚合函数特别有用。　



优秀SQL编程的困难，多半在于解决问题的方式：不要将“一个问题”转换成对数据库的“一系列　

查询”，而是要转换成“少数查询”。程序用大量中间变量保存从数据库读出的值，然后根据变量　

进行简单判断，最后再把它们作为其他查询的输入……这样做是错误的。糟糕的SQL编程有个　

显著特点，就是在　SQL　查询之外存在大量代码，以循环的方式对返回数据进行些加、减、乘、　

除之类的处理。这样做毫无价值、效率低下，这里工作应该交给SQL的聚合函数。　



注意：　



聚合函数非常有用，可以解决不少SQL问题（第11章会再次讨论）。然而，我发现开发者通常只　

使用最平常的聚合函数count（），它对大多数程序是否真的有用值得怀疑。　

第2章说明了使用count（*）判定是否要更新记录（插入新记录）是很浪费的。你可能在报表中误　


…………………………………………………………Page　82……………………………………………………………

用了count（*）。测试存在性有时会以模仿布尔值的方式实现：　

casecount（*）　

when　0then　'N'　

else'Y'　

end　

对于上述实现，