利用散列连接从DB2通用数据库获得最佳性能

利用散列连接从DB2通用数据库获得最佳性能

DB2 UDB 优化器可以在执行连接时选择不同方法：在缺省情况下，它在嵌套循环连接（nested loop join）与合并连接（merge join）之间选择。当设置了特殊环境变量时，它还可以选择散列连接（hash join）。散列连接可显著提高某些查询的性能，在决策支持系统（Decision Support System，DSS）环境中尤为突出，因为该环境中的查询比较复杂。本文旨在说明散列连接的工作原理以及如何正确地调优它以获得最佳性能。
首先我们将介绍阅读本文所必需的背景知识。我们将说明不同类型的连接方法，这些方法的工作原理，以及 DB2 UDB 如何针对特定的连接选择特定的方法。随后我们将说明调优并监控散列连接所需的元素。在这之后，我们将介绍我们做的几项实验和许多有趣的结果。最后是我们的结论。
1. 背景知识
两个表之间的连接是这样操作的：将一个表中的行与另一个表中的行并置在一起。另外，可以指定条件以定义并置哪些行。为执行这一操作，DB2 可以选择不同的连接方法。本节概括了不同连接方法的工作原理以及 DB2 如何针对特定连接选用连接方法。
1.1 连接方法
在连接两个表时，无论使用哪种连接方法，总有一个表被选为外表（outer table）而另一个表被选为内表（inner table）。优化器根据所选连接方法的成本和类型决定哪个是外表、哪个是内表。首先访问外表，并且只扫描一次。根据连接的类型和存在的索引，可以多次扫描内表。还有一点也很重要，要记住即使您试图连接两个以上的表，优化器也将每次只连接两个表，并在必要时保存中间结果。要理解散列连接方法的优势，先理解其它连接方法的工作原理也很重要。
嵌套循环连接:正如我们前面提到的那样，外表只被扫描一次。对于嵌套循环连接，要在内表中找到与外表中每一行相匹配的行有两种方法：
扫描内表。即，读取内表中的每一行，并且针对该行决定是否应将其与正在考虑的外表中的行相连接。
对内表上的连接列进行索引查找。当用于连接的谓词所包含的列在内表的索引中时，这种方法是可行的。这极大地减少了在内表中访问的行数。
在嵌套循环连接中，决定哪个是外表、哪个是内表非常重要，因为外表只扫描一次，而针对外表中的每一行，都要访问一次内表。正如前面提到的那样，优化器用成本模型来决定谁是外表谁是内表。优化器做此决定时会考虑几个因素：表的大小、缓冲、谓词、排序要求、是否存在索引、连接列不能是 LONG 或 LOB 字段。
合并连接：合并连接需要一个等式连接谓词（即具有 table1.column = table2.column 格式的谓词）。它还要求根据连接列对输入表进行排序。通过扫描现有索引或在进行连接之前对表进行排序就可以做到这一点。连接列不能是 LONG 或 LOB 字段。
同时扫描两个表，以查找匹配行。外表和内表都只扫描一次，除非外表中有重复的值，那样的话可能要再次扫描内表的某些部分。因为表通常只被扫描一次，所以决定哪个是外表、哪个是内表不象在其它连接方法中那么重要。尽管如此，由于可能有重复的值，所以优化器通常选择重复值较少的表作为外表。但是，优化器最终还是使用成本模型来决定谁是外表谁是内表。
散列连接：散列连接需要一个或多个等式连接谓词，其中每个谓词的列类型相同。就 CHAR 类型而言，长度必须相同。就 DECIMAL 类型而言，精度和小数位必须相同。同样，连接列不能是 LONG 或 LOB 字段。散列连接可处理多个等式谓词这一事实相对于合并连接是一大优势，后者只能处理一个等式谓词。
对于散列连接，首先扫描内表（也称为构建表，bulid table），表中的行被复制到内存缓冲区。根据“散列代码（hash code）”，这些缓冲区被分为几个分区，散列代码是根据连接谓词中的列计算出来的。如果内存中没有足够的空间容纳整个表，则有些分区被写入磁盘上的临时表。然后扫描外表（称为探测表，probe table）。对于探测表中的每一行，对连接列应用同一散列算法。如果所获得的散列代码与构建行的散列代码相匹配，则比较实际的连接列。如果与探测表行匹配的分区在内存中，则比较会立即进行。如果分区被写入临时表，则探测行也被写入临时表。最后，处理包含同一分区中的行的临时表以进行匹配。
由于将构建表保存在内存中所具有的好处，优化器通常选择较小的表作为构建表，以避免必须将该表溢出（spill）到磁盘上。但是，要再次强调的是，成本模型最终决定哪个表是内表、哪个表是外表。
让我们更深入地研究散列连接如何利用 SMP 系统。在 SMP 系统（其中 intra-parallel = ON 且 dft_degree >1）中，一个散列连接可能由多个任务在同一个 CPU 或多个 CPU 上以并行方式执行。在以并行方式执行时，构建表被动态地分为多个并行的元组流，每个流由独立的任务处理以便将构建元组输入内存。在对构建表流的处理结束时，散列连接进程会调整内存的内容，并执行任何必需的将分区移入或移出内存的操作。接下来，根据驻留内存的分区来处理探测表的多个并行元组流，并且，在需要时，为溢出到临时表的散列连接分区的元组来溢出这些并行元组流。最后，以并行方式处理溢出的分区，每个任务处理一个或多个溢出的分区。
2.2 选择哪种连接方法？
到目前为止，我们已经讨论了在 DB2 UDB 中可用的不同连接方法。正如我们所知，初看起来，某些方法与其它方法相比是更好的选择。例如，与根据外表的每一行扫描内表的嵌套循环连接相比，合并连接具有只对表扫描一次的优势。于是，合并连接似乎是一个更好的选择；但是，如果存在索引的话，则嵌套循环会是更好的选择。
同样地，与合并连接相比，散列连接似乎是更好的选择，因为它不需要在执行前对输入表排序，但如果我们需要保持外表中行的次序，则合并连接或嵌套循环连接可能是更好的选择 — 散列连接不能保证维持次序，因为它可能溢出到磁盘而那样会破坏次序。
那么 DB2 UDB 优化器如何针对特定连接来决定使用哪种连接方法呢？首先，它必须考虑查询中谓词的类型。当选择了可能的连接方法时，DB2 UDB 优化器随后根据成本模型和选定的优化级别来决定使用哪种连接方法。优化级别是数据库配置文件中可配置的参数，它告诉优化器要进行多大程度的优化。这个值越高，优化操作就越多。优化级别可能的值为：0、1、2、3、5、7 和 9。这些值对可能的连接方法的影响如下：嵌套循环连接在每个优化级别都可行、合并连接在优化级别 1 及以上级别是可行的、散列连接在优化级别 5 及以上级别是可行的。
3. 如何调优和监控散列连接
散列连接可显著提高某些查询的性能，在具有复杂查询的决策支持系统（DDS）中尤为突出。与合并连接相比，散列连接的性能优势之一是它不要求预先进行任何排序，排序的成本是很高的。散列连接的关键是能够将构建表的所有（或尽可能多的）行放入内存，而不必使表溢出到磁盘上。这些缓冲区所用的内存来自 sortheap，因此对该参数（以及 sheapthres）的调优是非常重要的。
sortheap 是数据库可配置参数，它定义可用于排序或散列连接的最大内存量。每个排序或散列连接都有独立的 sortheap，由数据库管理器按需分配。并非所有的排序和散列连接都要分配该内存量；如果不需要全部的内存，则可分配较小的 sortheap。根据操作的需求，可以从共享或专用内存分配 sortheap。只对 degree >1 的内部并行（intra-parallel）（SMP）查询使用共享 sortheap；当只有一个代理程序执行排序或散列连接且无需与其它代理程序共享内存时，则使用专用 sortheap。一个查询可能有多个散列连接和／或排序，并且根据计划的性质可能要求同时分配多个 sortheap，理解这一点也很重要。
sheapthres 是数据库管理器可配置参数。对该参数的使用因共享和专用排序而有所不同：
对于专用排序或散列连接，sheapthres 所起的作用是对所有并发专用排序能使用的内存总量充当实例范围的软限制。当所用的内存达到该限制时，为另外的新到 sortheap 请求分配的内存会显著减少。
对于共享排序或散列连接，sheapthres 所起的作用是对所有共享排序能使用的内存总量充当实例范围的硬限制。当所用的内存接近该限制时，为另外的新到 sortheap 请求分配的内存会显著减少，并且最终将不再允许共享排序的内存请求。
在单处理器系统中，散列连接只使用专用 sortheap。在 SMP 系统（其中 intra-parallel = ON 且 dft_degree >1）中，散列连接在操作的第一阶段使用共享 sortheap，在最后的阶段使用专用 sortheap。有关这些参数的更多信息，请参阅与 DB2 UDB 一起提供的 Administration Guide: Performance。
如何调优这些参数呢？与大多数调优练习一样，您需要有一个出发点：一个要测试的工作负载以及测量调优结果的工具。然后就是一次更改一个参数再进行测量的迭代过程。在大多数情况下，现有系统中可能已经设置了 sortheap 和 sheapthres 的值，因此我们建议您先从当前的设置着手。如果您有全新的安装，那么您可以遵循适用于 DSS 系统的经验，将所拥有的内存的 50% 分配给缓冲池，另外 50% 则分配给 sheapthres。然后，将 sheapthres 除以要同时执行的复杂并发查询的数目，再除以一般查询中并发排序和散列连接的最大数目，就得到 sortheap。（适合开始时采用的数字是 5 或 6。）总结如下：
sortheap = sheapthres / （复杂并发查询数 * 一般查询中并发排序和散列连接的最大数目）
请记住“复杂并发查询数”不等于“并发用户数”。用户的数目通常比每次执行的复杂并发查询数多得多。在大多数 DSS TPC-H 基准程序（它们设法将数据库的能力发挥至极限）中，对于最大为 10 TB 的数据库，将不大于 8 或 9 的数作为复杂并发查询（也称为流）的数目，因此开始时要保守一些，然后在必要时增加。
设置了初始的 sortheap 和 sheapthres 值以后，运行一般的工作负载，然后收集数据库和数据库管理器快照。DB2 UDB 提供了一些监视器元素以便能够监控散列连接。您不需要打开