调优IBMDB2UDBSQL存取路径

简介　　Visual Explain 是 IBM DB2 Universal Database 中的杰出工具，程序员和 DBA 用它来详细说明 DB2 优化器为 SQL 语句所选择的存取路径。事实上，Explain 应该是您性能监控策略的关键组件。Explain 为解决许多类型的性能问题提供了价值无法估量的信息，因为它提供这样的细节：

DB2 在“幕后”所做的工作，以实现 SQL 请求的数据需求 DB2 是否使用可用的索引，如果使用，DB2 如何使用它们 为满足连接条件而访问 DB2 表的次序 实现 SQL 语句的锁定需求 基于所选存取路径的 SQL 语句的性能　　对于 Borland Delphi 7 程序员，Visual Explain 会是一个用于发现 DB2 如何执行 SQL 请求的神奇资源。Delphi 使用 CLI 本机接口来与 DB2 交互。因而，Delphi 使用的是动态 SQL。当将 SQL 语句提交给 DB2 执行时，DB2 会“实时”地为动态 SQL 语句设计出存取路径。对分析者而言，在执行每条 SQL 语句之前，无法检查 DB2 为这些语句所选择的存取路径。所以，使用 Visual Explain 定期地检查 DB2 为所有 Delphi SQL 语句所选择的存取路径，这是很有意义的。这样做可以观察到哪些语句消耗了大部分资源。另外，还可以指导您如何调优 SQL 以达到更好的性能。

但在深入探讨 explain 的用法之前，我首先需要研究 explain 确切地“说明”了什么。答案很简单，它说明了 DB2 存取路径。存取路径是 DB2 所使用的一种算法，以满足 SQL 语句的需求。但有大量各种类型的存取路径需要掌握。

DB2 存取路径的类型及其组成部分

当 DB2 优化器为每条 SQL 语句创建优化的存取路径时，可以挑选各种不同的技术。这些技术包括从简单的一连串顺序读到更为复杂的策略(譬如，使用多个索引来访问数据)。让我们来了解优化器用来设计 DB2 存取路径的一些最常用技术。

在优化器必须做的许多决定中，最重要的决定可能是，是否使用索引来实现查询。在优化器做此项决定之前，它必须首先确定是否存在索引。请记住，您可以查询任何表中的任何列，却不能期望单单通过索引就能做到这一点。所以，优化器必须能够访问未建立索引的数据;它可以使用扫描来做到这一点。

在大多数情形下，DB2 优化器喜欢使用索引。这是事实，因为索引可以大大优化数据检索。然而，如果不存在索引，就无法使用它了。并且在某些情况下仅仅使用数据的全扫描就可以极好地实现某些类型的 SQL 语句。例如，考虑下面这条 SQL 语句：

SELECT * FROM EMP;

在这条语句中，为什么 DB2 非要试图使用索引呢?这里没有 WHERE 子句，所以全扫描是最佳的。即使指定了 WHERE 子句，优化器也可能确定页面的顺序扫描要比索引式检索更好 — 所以可能不会选择索引式检索这种方法。

存在索引的首要原因是它可以改善性能，那为什么非索引式的访问会比索引式的访问要好?唔，索引式访问可能比简单的扫描要慢。例如，一个非常小的表可能只有几个页面。读取所有的页面可能比先读取索引页然后再读取数据页要快。甚至对于较大的表，在某些情况下，组织索引可能需要额外的 I/O 以实现查询。当不使用索引来实现查询时，产生的存取路径会采用表扫描(或表空间扫描)方法。

表扫描通常会读取表中每个页面。但在某些情况下，DB2 会非常聪明形成完善的方案后，它会限定要扫描的页面。此外，DB2 可以调用顺序预取以在请求某些页面之前就读取这些页面。当 SQL 请求需要按照数据存储在磁盘上的顺序来顺序地访问多行数据时，顺序预取特别有用。当优化器确定查询将按照顺序读取数据页面时，它会通知应该启用顺序预取。表扫描常常得益于顺序预取所作的提前读取的工作，因为当某个查询请求数据时，这些数据已经放在内存中了。

快速的索引式访问

一般来讲，访问 DB2 数据的最快方式是使用索引。索引是为了能够快速找到某个特定数据块的目的来构造的。图 1 显示了 B 树索引的结构。可以看到，通过简单地从树根遍历到叶子页，可以快速地找到相应的数据页，在那里有您请求的数据。但是，DB2 所采用的索引方式因语句不同而各不相同。DB2 使用各种不同的内部算法来遍历索引结构(请参阅 图 1)。

图 1. B 树索引的结构

在 DB2 使用索引来实现数据访问请求之前，必须满足以下条件：

至少有一个 SQL 谓词必须是可索引的。某些谓词因其特有的本性而为不能被索引，所以优化器从来不能够使用索引来满足它们。 其中一列(在任何可索引谓词中)必须作为可用索引中的列而存在。　　所以，您明白，对于 DB2，考虑使用索引的要求是相当简单的。但关于 DB2 中的索引式访问仍有许多要了解的内容。事实上，索引式访问有各种类型即便是发小孩子脾气。

第一种(也是最简单的)索引式访问类型是直接索引查找。对于直接索引查找，DB2 使用索引的根页面，从顶部开始，向下遍历，经过中间叶子页直到抵达相应的叶子页。在那里，它将读取实际数据页面的指针。根据索引条目，DB2 将读取正确的数据页面以返回期望的结果。对于 DB2，为了执行直接索引查找，在索引中必须为每个列提供值。例如，考虑一个 EMPLOYEE 表，该表有一个关于 DEPTNO、TYPE 和 EMPCODE 列的索引。现在考虑这个查询：

SELECT FIRSTNAME, LASTNAME

FROM EMPLOYEE

WHERE DEPTNO = 5

AND TYPE = 'X'

AND EMPCODE = 10;

如果只指定这些列中的一列或两列，则不可能采用直接索引查找，因为 DB2 没有针对每列的值，不可能匹配整个索引关键字。相反，可以选用索引扫描。有两类索引扫描：匹配索引扫描和非匹配索引扫描。有时称匹配索引扫描为绝对定位;称非匹配索引为相对定位。还记得前面所讨论的表扫描吗?索引扫描与此类似。在索引扫描中，按顺序读取索引的叶子页。

匹配索引扫描从索引的根页开始，遍历至叶子页，这种扫描方式与直接索引查找方式完全一样。然而，因为无法用完整的索引关键字，所以 DB2 必须使用它所拥有的值来扫描叶子页，直到检索出所有匹配的值。现在考虑重写前面那个查询，这次没有用 EMPCODE 谓词：

SELECT FIRSTNAME, LASTNAME

FROM EMPLOYEE

WHERE DEPTNO = 5

AND TYPE = 'X';

通过从根部开始遍历索引，匹配索引扫描用相应的 DEPTNO 和 TYPE 值来查找第一个叶子页。但可能有多条索引条目具有这两个值的组合，而这些索引条目的 EMPCODE 值却不同。所以，会按顺序扫描至右边的叶子页，直到不再遇到有效的 DEPTNO、TYPE 和各种 EMPCODE 的组合。

要请求执行匹配索引，必须指定索引关键字中的高次序列，就是前面这个示例中的 DEPTNO。这向 DB2 提供了遍历索引结构的启始点，从根页开始遍历，直到相应的叶子页。但如果没有指定这个高次序列，则会发生什么呢?假定对上面这个样本查询做点改动，不指定 DEPTNO 谓词：

SELECT FIRSTNAME, LASTNAME

FROM EMPLOYEE

WHERE TYPE = 'X'

AND EMPCODE = 10;

在这实例中，会用到非匹配索引扫描。在这种情形下，DB2 不能使用索引树结构，因为关键字中第一列不可用。非匹配索引扫描从索引中的第一个叶子页开始遍历，应用可用的谓词，顺序扫描后续的叶子页。不使用根页和任何中间叶子页。

一种特殊类型的索引扫描是“仅索引访问”。如果所需要的全部数据都位于索引中，则 DB2 完全可以避免读取数据页。例如：

SELECT DEPTNO, TYPE

FROM EMPLOYEE

WHERE EMPCODE = 10;

请记住，本文中的这个数据库包含关于 DEPTNO、TYPE 和 EMPCODE 列的索引。在前面的查询中，只请求查询这几列。所以，DB2 完全不需要访问表，因为在索引中可以找到所有数据。

DB2 可使用的另一类索引式访问是多索引访问。针对一个存取路径，多索引访问将使用多个索引。例如，查询 EMPLOYEE 表，其中只有两个索引：关于 EMPNO 列的 IX1 和关于 DEPTNO 列的 IX2。然后，要求这条查询显示在某个特定部门工作的员工：

SELECT LASTNAME, FIRSTNME, MIDINIT

FROM EMPLOYEE

WHERE EMPNO IN ('000100', '000110', '000120')

AND DEPTNO = 5;

DB2 将会使用用于 EMPNO 谓词的 IX1 还是使用用于 DEPTNO 谓词的 IX2?为什么不一起使用这两者呢?这就是多索引访问的实质所在。根据谓词是用 AND 连接还是用 OR 连接，可将多索引访问分为两类。

理解连接方法

至此，已经讨论了涉及单个表的简单存取路径。而连接以及更复杂的 SQL 语句怎样呢?DB2 优化器有一系列可供自己使用的技术来用于连接表数据。当在 FROM 子句中引用多个 DB2 表(或指定了 JOIN 子句)时，SQL 会请求 DB2 执行连接操作。根据连接标准，必会执行一系列的指令来组合表中的数据。

DB2 如何做这件事?每个多表查询会分解成数个单独的存取路径。为完成此连接操作，DB2 优化器先选择其中的两张表并创建一条经过优化的存取路径。它不是随机地做这件事，而是根据它认为是此连接的最优方式来进行选择。然后，优化器继续连接其它表，直到优化完整条查询。

在连接表时，优化器将必须确定要使用的最佳连接算法。连接算法(或连接方法)定义了组合表的基本过程。DB2 可以采用三类连接方法：嵌套循环(nested loop)、归并扫描(merge scan)和散列连接(hash join)。每种连接方法的运行方式各不相同，但可得出相同的结果。然而，连接方法的选用会极大地影响到连接性能。DB2 根据这样的方式来采用每种连接方法：基于一组统计，采用这种方法可以达到最佳性能。所以，您应该掌握各种连接方法，以及促成选择这些方法的因素。

每种连接方法通常都涉及一些特定的基本步骤。通常，首先确定先处理哪个表。称这个表为外表。做出决定之后，对该外表执行一系列的操作，为连接做准备。然后，将该表中的各行与第二个表(称之为内表)进行组合。另外，还要对内表执行的一系列操作，这可以在连接发生之前进行，也可以连接发生时进行，或者在这两者时进行。虽然所有连接方法包含的步骤都类似，但除这一点之外，这三种连接方法都各不相同。优化器知道每种方法的优缺点，知道采用哪种方法会怎样影响到性能。根据系统目录中的当前统计，优化器还知道哪些表最适合做内表，哪些表最适合做外表。以下从较高层面汇总了优化器所要考虑的一些事项：

表越小，越有可能被选为外表。这有助于减少必须再次访问内表的次数。 如果选择谓词可以应用到某个表，则该表更适合于被选作外表，因为在访问内表时只会用那些符合这些谓词(应用于该外表的)的行。 如果可能对其中某个表做索引查找，则该表很适合于作为内表。如果一个表没有索引，则最好不要将其作为内表，因为每扫描外表中的一行，就要扫描一遍整个内表。 在连接操作中，重复元素最少的表倾向于被选作外表。　　当然，这些不是固定不变的规则。最后，优化器将根据详细的代价估计来选择外表和内表。现在，我将讨论可用于 DB2 的连接类型，以及这些连接类型之间的区别。

最常用的连接类型可能是嵌套循环连接(nested loop join，NLJ)。要执行 NLJ，先在外表中确定符合条件的行，然后扫描内表来搜索匹配。符合条件的行是指与针对表中列的谓词相匹配的行。在完成对内表的扫描之后，再在外表中确定另一符合条件的行。然后，再扫描内表以查找匹配，如此反复。通常，用索引来重复扫描内表以将 I/O 代价降到最低。

DB2 采用的第二类连接方法是归并连接(merge join，MJ)。用 MJ 时，需要按照连接谓词对要连接的表进行排序。这意味着必须按照指定连接标准的列的顺序访问每个表。这个顺序可以用排序或索引式访问来实现。在确保对外表和内表正确排序之后，按照顺序读取每个表，然后匹配连接列。在归并扫描连接中，每个表只扫描一遍。

第三类连接取决于运行 DB2 的平台。对于 DB2 for OS/390 and z/OS，存在混合连接(hybrid join)。混合连接组合数据和指针来访问和组合正在连接的表中的行。关于这种连接类型的完整讨论超出了本文的讨论范围。

对于 DB2 for Linux、UNIX 和 Windows，第三类连接是散列连接(hash join)。散列连接要求有一个或多个 lX = lY 形式的谓词，并要求列类型必须相同。扫描内表，然后将行复制到为排序堆分配的内存缓冲区。根据“散列代码”将内存缓冲区分成几个分区，这些“散列代码”是根据连接谓词的列计算得来的。如果第一个表的大小超过了可用的排序堆空间，则选中分区的缓冲区被写到临时表中。处理完内表之后，扫描外表，通过比较“散列代码”，将外表的行与内表的行进行匹配。散列连接可能需要大量内存。所以，要使散列连接真正提高性能，可能需要更改 sortheap 数据库配置参数和 sheapthres 数据库管理器配置参数的值。

可是，您知道何时应该使用哪种连接方法?通常，当符合连接的行数较少时，就执行代价而言，建议使用嵌套循环连接。随着行数的增加，归并连接成为较好的选择。最后，在散列连接这种情形下，内表是保存在内存缓冲区中。如果内存缓冲区太少，则散列连接不得不溢出。优化器会试图避免这种情况，所以选两个表中较小的表作为内表，较大的作为外表。

最终性能一般取决于确切的符合条件的行数以及其它因素(譬如，数据库的设计、数据库的组织、统计信息的精确性、硬件类型和 DB2 环境的设置等)。