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

发布时间：2016-10-30 16:26:21 所属栏目：交互来源：站长网

导读：DB2 UDB 优化器可以在执行连接时选择不同方法：在缺省情况下，它在嵌套循环连接（nested loop join）与合并连接（merge join）之间选择。当设置了特殊环境变量时，它还可以选择散列连接（hash join）。散列连接可显著提高某些查询的性能，在决策支持系统（D

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 提供了一些监视器元素以便能够监控散列连接。您不需要打开任何监视器开关，因为所有的散列连接监视器元素都是用 Basic 监视器开关收集的。此外，所有的元素都是计数器，都是可重新设置的。有关 DB2 UDB 快照工作原理的详细信息，请参阅与 DB2 UDB 一起提供的 System Monitor and Reference Guide。
以下是对每个监视器元素的描述：
1.散列连接总数（Total Hash Joins）：该监视器元素计算已执行的散列连接的总数。这个值可用数据库或应用程序快照来收集。
2.散列连接溢出数（Hash Join Overflows）：该监视器元素计算散列连接设法将构建表中的行放入内存时超出了可用 sortheap 的总次数。这个值可用数据库或应用程序快照来收集。
3.散列连接少量溢出数（Hash Joins Small Overflows）：该监视器元素计算当设法将构建表的行放入内存时，散列连接超过可用 sortheap 不到 10% 的总次数。出现较小的溢出表明增加 sortheap 有助于提高性能。这个值可用数据库或应用程序快照来收集。
4.散列循环总数（Total Hash Loops）：该监视器元素计算散列连接的单个分区大于可用 sortheap 的总次数。
请记住，如果 DB2 UDB 不能将构建表的所有行放入内存，会将某些分区溢出到临时表以供以后处理。在处理这些临时表时，DB2 会尝试将每个构建分区装入一个 sortheap。然后 DB2 UDB 读取相应的探测行，并设法使它们与构建表匹配。如果 DB2 不能将某些构建分区装入 sortheap，则 DB2 必须借助散列循环算法。监视散列循环非常重要，因为散列循环表明散列连接的执行效率低下，并且可能导致严重的性能下降。它可能表明 sortheap 大小对于工作负载太小，或者，更可能是 sheapthres 太小，无法获得对 sortheap 内存的请求。这个值可用数据库或应用程序快照来收集。
5.散列连接阈值（Hash Join Threshold）：该监视器元素计算散列连接 sortheap 请求由于对共享或专用内存堆空间的并发使用而受限的总次数。这意味着散列连接请求了一定量的 sortheap，但得到的少于请求的。它可能表明 sheapthres 对于工作负载太小。这个值可用数据库管理器快照来收集。
关于hljoin连接的相关测试：
1.sortheap 实验
正如我们前面提到的那样，对 sortheap 和 sheapthres 参数的调优是散列连接性能中最重要的因素。本实验的主要目的是理解不同的 sortheap 大小如何会影响散列连接的性能。在这个测试中，我们使用了在前一节中说明过的所有初始调优，仅仅改变了 sortheap 的大小，同时使 sheapthres 保持其原始值 192000。我们在这些测试中只运行一个流。结果显示在图 5中。
所显示的时间是与运行 256 页的 sortheap 所用时间比较而言的。我们把运行 256 页 sortheap 的工作负载所用的时间看作一个时间单位。在这个实验中，所有其它的结果都相对于这个单位来表示。
让我们详细地分析结果。我们可以看到，通常 sortheap 越大，我们遇到的溢出就越少并且性能也更佳。我们在 40000 页处获得最佳性能，其中仅看到三个溢出。
第一个问题是，为什么更大的 sortheap 不能给我们更佳的性能呢？如果您看一下图 6中显示的监视器信息，您可以看到在 50000 页处我们开始命中 sheapthres。而且，sortheap 越大，我们得到的性能就越差，因为我们命中 sheapthres 的次数越多。命中 sheapthres 时性能下降的原因是，当 sortheap 请求命中阈值时，DB2 将力图满足它，但所给的内存少于请求的。正如我们在这些例子中看到那样，尽管我们请求的 sortheap 比以前更大，但却得到更多的溢出。这是因为我们得到的 sortheap 实际上比我们请求的少，而这导致了溢出，于是就降低了散列连接的性能。
通过观察图表我们注意到另一现象：随着 sortheap 从 256 页到 500 页，再从 500 页到 1000 页，然后一直到 1000 页，我们在图表的左边获得非常大的改进。为什么呢？再次观察监视器信息后，我们可以发现，散列循环的数量随着 sortheap 的增加而明显下降，并且在 4000 页处消失，在这之后增加 sortheap 的性能增益大大小于先前。
我们可以从这些结果得出这样的结论：散列连接的最大敌人是散列循环。尽管命中 sheapthres 会导致性能下降，就象当 sortheap >40000 时我们看到的那样，但出现散列循环时性能下降的数量级远远大于命中 sheapthres 时性能下降的数量级，当不可能同时避免这两种情形时要牢牢记住这一点。
2 sheapthres 实验
这些实验的主要目的是理解 sheapthres 的调优，sortheap 和 sheapthres 之间的关系，以及这种关系在散列连接中的影响。针对这一目的，我们将该实验分为五组测试。在每组测试中，我们固定了 sortheap 的大小并更改 sheapthres，使其大小分别为 sortheap 的两倍、三倍、四倍、五倍、六倍和七倍。我们使用的五个 sortheap 的大小是 5000、10000、15000、20000 和 30000 页。结果显示在图 7到图 9 中。
所显示的时间是与在 sortheap 为 5000 页和 sheapthres 为 10000 页（sortheap 大小的两倍）时运行所用的时间比较而言的。因此，我们把运行 5000 页 sortheap 和 10000 页 sheapthres 的工作负载所用的时间看作一个时间单位。在这个实验中，所有其它的结果都相对于这个单位来表示。
如果观察图 7，我们可以看到对于所有的 sortheap，该特定工作负载的最佳性能出现在 sheapthres 值为 sortheap 值的 6 到 7 倍时。观察图 8和图 9中的监视器数据，可以看到正是在这一级别上我们消除了所有的散列连接阈值命中。在所有的例子中，最佳改进出现在 sheapthres 值从 sortheap 值的两倍变为三倍时，因为散列连接阈值在那时明显下降。另外，当 sortheap 为 5000 时，散列循环在 sheapthres 值从这一大小的两倍变为三倍时明显降低。正如 DB2 UDB 手册所指出的那样，通常的建议是使 sheapthres 至少为 sortheap 的三倍，但我们希望看到 sheapthres 过小对于散列连接有什么结果。对于您实际的系统，我们决不会建议使 sheapthres 低于 sortheap 的三倍。
观察到的另一重要现象是增加 sheapthres 所获得的性能增益在某些情况下非常小；在数据库／实例的整体性能调优方案中，把内存分配给 sheapthres 可能并不划算，而（例如）有更大的 sortheap 或更多缓冲池可能更划算。要清楚如何设置 sheapthres 以及它对整个系统的影响。我们从这些实验得出的最重要的结论是：调优 sheapthres 对散列连接的性能有一些影响，但这种影响没有调优 sortheap 那么显著。
6. 结束语
正如我们看到的那样，散列连接只需少量调优就可以明显地改进性能。正如我们在第一个实验中演示的那样，只要遵循一些基本经验，就可以通过启用散列连接在我们的 DSS 工作负载中使性能有显著的改进。此外，我们还演示了可以在有许多流时改进可伸缩性。
我们还从两个影响散列连接的主要参数 — sortheap 和 sheapthres — 知道 sortheap 似乎是最重要的。通过增加 sortheap，我们有可能避免出现散列循环这个散列连接最大的敌人。另外，我们看到，sortheap 越大，溢出就越少并且性能更佳。这并不意味着我们可以忽略 sheapthres，它肯定会影响性能，但通常 sheapthres 是由机器中可用的内存量决定的。于是我们可以得出如下结论：调优散列连接的最佳方法是确定您可以给 sheapthres 分配多少内存，然后相应地调优 sortheap 值。
调优散列连接的关键是：使 sortheap 尽可能地大以避免溢出和散列循环，但不要大到超过 sheapthres的限制。

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!