海量数据库解决方案06

6.1JOIN和LOOPQUERY的比较即使不使用JOIN来实现表连接，也仍然有很多其他能够实现表连接的方法。这些方法包括“UNION„GROUPBY”、子查询（Subquery）、存储函数（StoredFunction）等。另外，还有一个比较原始的方法，即就像以前在过程化应用程序中所使用过的那种，首先从当前已知表的数据范围中读取数据，并将其作为常量从所连接的表中寻找对应行。即执行经过DECLARECURSOR，从当前表中以行为单位执行FETCH后，将从这里所获得的值视为常量，在“FORLOOP”中再次执行连接的SQL的方法。或许这种方法是我们所了解的数据处理方法中的一个非常典型的连接方法。在本书中将该方法称之为“反复连接方式”，之所以这样命名主要是因为之后用于连接（Query）的SQL要以之前从基表中所读取的行为基准而被反复（Loop）执行。很多并不怎么相信JOIN的用户认为反复连接方式在实现表连接方面比JOIN更加有效，导致他们具有这种看法的主要原因应该是他们在开发应用程序并对其程序进行测试的时候发现，反复连接方式比JOIN能够更快地返回结果吧！但是如果他们能够对JOIN有一个正确而深刻的理解并能够正确地使用，则在大部分情况下JOIN会显得更加有效。那么，究竟是什么原因导致了执行速度缓慢问题的出现呢？接下来我们就其原因予以详细分析，并制定出在不同的情况下应当选择何种表连接方式的判断准则。为了便于各位读者对这部分内容的理解，这里将反复连接方式与NestedLoops连接方式放在一起进行比较说明。在一般情况下，由于认为JOIN具有集合运算的概念，所以在连接两个集合的时候并没有必要必须将一个视为主集合，而另外一个视为副集合，并且它就像2×3一样，随意交换前后顺序都不会影响最终结果。因此，JOIN并不是要求必须首先从一个指定的集合中读取数据，再利用该数据去连接另外一个集合；而是根据优化器的判断来决定哪个集合应当被优先处理。当然，随着表连接方式的不同，集合的优先顺序也有所不同。不论以何种步骤进行JOIN，最终的结果都是相同的。唯一不同的是随着处理方式的不同，在内部实际所完成的操作量会有很大的不同。因此，有时只需要我们通过简单的方法改变一下表连接的类型或执行顺序，就可以使执行速度得到很大的改善。当然在表连接的处理中，SQL的类型、索引的构成等因素也对表连接的速度有着非常大的影响。由于随着对数据读取要素管理的有效程度的不同，数据读取效率也有着很大的不同；所以在没有对这些要素进行有效管理的情况下，它们将成为造成执行速度缓慢的根源。因此，正是由于很多用户没有对造成JOIN执行速度缓慢的原因有一个正确的理解，所以才导致他们对JOIN有些成见。在实际工作中，几乎不需要改变原来所使用的SQL，而只需要通过简单改变读取要素的方法就可以使得执行速度在很大程度上得到提高。对于不怎么理解其中缘由的人而言，就如同魔术一样神奇。作者在几十年的数据库相关工作中，曾经历过很多类似的情况。对于根本不太了解其中原理的用户而言，他们并不能看出这样的数据读取方式，他们所能够看到的也只不过是最终结果而已。即，由于他们只能够看到最终结果和所需要的时间，当对最终结果满意而对执行时间不满意时，就将所有的责任推卸给DBMS，而DBMS也只能“忍气吞声”了。由于这些用户无法正确理解导致这种无效执行的原因，再加上连续经历过几次之后自然就会对JOIN产生成见。在连接处理一定量的数据之前，他们会根据自己所具有的知识对其所需要的时间进行预测，当所需要的实际时间超过了他们预计的时间时，他们的自信就会惨遭打击，从而使得他们对JOIN的成见就更深了。这种做法逾越了一般的常识线，从而更容易降低数据处理的效率。他们自己没有掌握处理数据的正确方法，而且对处理一定量的数据所需要的时间没能做出正确的预测，却反倒将所有的不满全部发泄给JOIN，JOIN岂不是冤枉至极？在这里建议各位读者：如果认为自己也是这种人，则在正确掌握了JOIN的原理后，再来回想一下自己以前的狼狈模样。从某种程度来说，反复连接方式也有其不可否认的优点。换言之，它的优点表现在无须像使用JOIN那样需要考虑集合的顺序。从DECLARECURSOR中取出结果的同时在FORLOOP中进行连接的反复连接方式非常类似于NestedLoops表连接方式。我们在使用JOIN的时候，会由于个人失误而没能设计出正确的表连接方向（集合顺序），从而影响了JOIN的执行速度，但在反复连接方式中绝对不会出现由于方向问题而影响执行速度的情形。由此可见，反复连接方式不会由于集合顺序问题而影响执行速度，在JOIN中可能发生的问题在反复连接方式中绝对不会发生。因此，反复连接方式始终不会由于个人失误而降低执行速度。然而，纵使反复连接方式有种种的优点，但这种优点也只不过是与按照无效的执行路径执行JOIN时的执行速度相比而表现出来的而已；而当与有效的JOIN相比时，它就自惭形秽了。所以一些用户将所有的责任全部推卸给JOIN，致使JOIN好像成了应该受到惩罚的罪人一样，而事实并非如此，最应该反省的是使用JOIN的我们。不论是由于不了解问题的原因，还是没有找到解决问题的方法，直接抛弃JOIN的做法都是不正确的。或许也可以认为我们这里所讨论的处理方式的问题源于关系型数据库所提供的数据处理方法吧！而且也可以认为这种问题是关系型数据库所无法回避的根本性问题。在很久以前，很多人喜欢在COBOL或C应用程序中使用DECLARE„CURSOR的方法来处理数据，现在也仍然有人喜欢这么用。当然，在DECLARE„CURSOR中能够实现表连接也是毋庸置疑的事实。至于其效率如何在这里不准备予以说明，在这里准备说明的是表连接究竟是在DECLARE中被实现的，还是在LOOP中按照反复连接的方式被实现的。首先我们来说结论，尽管随着具体情况的不同结果会有所不同，但是这里所指的是在大部分情况下，需要进行JOIN的数据都是在DECLARE中被实现的，而只有在非常特殊的情况下才在LOOP中实现。随着在DECLARE中实现JOIN操作的比重的不同，例如，在DECLARE中实现了30%的JOIN操作，或者70%的JOIN操作，不仅会对执行速度有影响，而且也会对简单性、可持续性、维护难易性等有影响。对于已经熟练地掌握了JOIN的人而言，就敢断言：“不论对任何人所编写的应用程序我都能够实现把代码缩减至原来的1/10，而执行速度提高至原来的10倍。”这并不是吹嘘，而是千真万确的事实。当然，对于以用户界面为主的应用程序而言可能稍微有些困难；但对于以数据处理为主的应用程序而言，不仅能够实现此目的，而且甚至能获得10倍以上的效果。这样的事实对于作者而言，已屡见不鲜了。作者在几年前为某银行优化系统时，将该银行所具有的一个由6个均超过数千行的代码块所构成的批处理程序合并成了只使用一个SQL的程序，从而将原来长达8小时的执行时间压缩到了20分钟。由于各位读者无法亲眼目睹或许有所质疑，但这的确是千真万确的事实。事实上，所谓的数据处理就是指通过读取一个以上的集合，从中获得全部所需的信息，并对其进行必要的加工处理，然后将加工处理后的集合转换为符合要求的集合并输出的过程。在分解SQL的瞬间，其相互之间的间隔需要我们用户自行填充，所以如果不是万不得已的情况下，能够确保不分解SQL的做法也是一项非常重要的战略。6.1.1全部范围扫描方式下的比较在本节中，向各位读者介绍像批处理的SQL或包括了GROUPBY的SQL这种必须要按照全部范围扫描的方式被执行时，内部所执行的具体处理步骤，同时也将向各位读者展示表连接不会给执行速度带来任何不利影响的证据。反复连接方式与NestedLoops表连接方式的比较如图6-1所示。首先来观察一下图6-1。....TAB1TAB2....SQL内部加工处理FETCHSQLSQLSQLSQLSQLSQLSQL....运输单位TAB1TAB2SQL内部加工处理FETCH............[JOIN方式][反复连接]■图6-1首先来观察一下图6-1中左侧的JOIN执行步骤，由于按照全部范围扫描的方式执行了SQL语句，所以对表TAB1中所有满足条件的数据范围以行为单位进行扫描的同时，与表TAB2中对应的行进行连接。尽管在图中没有描述出来，但是必然经过TAB2中的索引进行了连接。假设，在表TAB1中满足条件的数据有1000行，则就需要按照随机的方式执行1000次连接。并根据SQL的具体要求，在执行附加性的加工处理后，将最终结果发送给运输单位并输出。最后，需要强调的是虽然执行了1000次连接，但是SQL只被执行了一次。接下来观察一下图6-1的右侧。在表TAB1中满足条件的数据也同样有1000行，由于数据的处理方式是全部范围扫描，所以读取了整体范围中的所有数据并对其进行加工处理后执行了FETCH处理。在LOOP循环的同时，对FETCH所获得的各个行执行SQL，并按照随机的方式与对应的表TAB2中对应的行进行连接。在该方式中，由于每次FETCH时都需要执行一次SQL，所以只在连接操作中SQL就被执行了1000次。虽然通过这两种执行方式我们能够获得相同的结果，但是表连接的方式却有所不同。不论从左侧一次性的SQL执行还是从右侧1001次的SQL执行中，我们都看不出表连接给执行速度带来的任何不利之处。如果需要被反复执行的SQL，在每次执行的时候都需要重新解析的话，则无形中会增加很大的负担。当然，如果将需要被反复执行的SQL的解析结果在SQL共享池（SharedSQLArea）中共享，则虽然减少了解析时间，但从连接的角度来看，无论如何也看不出有任何有利之处。尤其要注意的是，不论在JOIN方式的SQL中随意添加已经处于连接状态下的表中所有的列并对其加工处理，还是在反复连接的方式中通过其他语言向应用程序中添加额外的加工处理，都会使两者在执行速度上的差距变大。在不同的RDBMS中，这种反复执行SQL的操作对系统所构成的负担存在着一定的差异。对于曾使用过反复连接且对RDBMS并没有构成任何负担的用户而言，当面对使用反复连接会构成负担的RDBMS时，如果以相同的方法使用应用程序，则与原来相比就会形成非常大的反差，以至于有可能落到该系统无法被继续使用的境地。导致这种现象的主要原因是由于不同的RDBMS之间存在着结构上的差异，而并不是因为某个特定的RDBMS比较优秀，而其他的RDBMS比较差。这也从另外一个侧面向我们暗示了在反复执行会对系统构成负担的RDBMS中，就更应该使用JOIN方式来实现表连接。事实证明，在这种情况下使用JOIN方式实现表连接的做法是比较明智的选择。这主要是因为依据表连接的原则，只有通过在SQL中处理必须要被加工处理的集合，才能为接受并理解后面将要介绍的很多关于表连接的内容开辟出一条道路。否则，如果各位读者不试图步入此路，则无论如何也无法真正体会到RDBMS的真正魅力。为了实现在一个SQL中完成所有的数据处理，需要我们将所涉及的所有集合集中起来，此后，就可以将本需要我们直接处理的部分全部提交给DBMS去完成。然而，对于如何确保所输出的数据与实际所发生的多元化的业务规则相匹配以及如何确保以最优的执行计划处理数据的问题，在本书中仍然未能涉及，读者可参考其他相关书籍。我们做任何事情都应当恰到好处，避免过尤不及。同样的道理，在这里如果过分地将所有的数据处理都交给一个SQL去完成，则反倒容易招致不必要的麻烦。因此，这就要求我们对合并SQL的度有一个正确的认识和把握，合并到什么程度就能够获得比较好的效果，超过了这个度反倒会影响SQL的执行效率。接下来让我们来学习一下关于把握这个度的原理和准则。某个SQL需要按照全部范围扫描的方式来执行也就意味着，不仅作为驱动集合的表需要按照全部范围扫描的方式执行，而且作为其他用途的集合也同样需要按照全部范围扫描的方式执行，这样一来反倒加重了数据处理的负担。如果可以将SQL中没有必要必须按照全部范围扫描的方式执行的部分数据处理分离出来，就可以在很大程度上减轻数据处理的负担。由此可见，将必须要按