调优 IBM DB2 UDB SQL 存取路径

核心提示：简介Visual Explain 是 IBM® DB2® Universal Database™ 中的杰出工具，程序员和 DBA 用它来详细说明 DB2 优化器为 SQL 语句所选择的存取路径，调优 IBM DB2 UDB SQL 存取路径，事实上，Explain 应该是您性能监控策略的关键

简介

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）。散列连接要求有一个或多个 table1.ColX = table2.ColY 形式的谓词，并要求列类型必须相同。扫描内表，然后将行复制到为排序堆分配的内存缓冲区。根据“散列代码”将内存缓冲区分成几个分区，这些“散列代码”是根据连接谓词的列计算得来的。如果第一个表的大小超过了可用的排序堆空间，则选中分区的缓冲区被写到临时表中。处理完内表之后，扫描外表，通过比较“散列代码”，将外表的行与内表的行进行匹配。散列连接可能需要大量内存。所以，要使散列连接真正提高性能，可能需要更改 sortheap 数据库配置参数和 sheapthres 数据库管理器配置参数的值。

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

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

用优化等级指定搜索策略

连接方法的选择还取决于正在使用的优化等级。优化等级指定了各种搜索策略，当编译和优化 SQL 语句时，优化器将使用这些策略。所以，优化器并非总是使用上面所描述的每种存取路径技术。相反，根据优化等级，优化器使用各种不同的技术。优化等级的用途是通过它来指导 DB2 何时采用哪种搜索策略和优化技术。通常，优化器考虑的搜索策略越多，用于查询的存取方案就越好。然而，当优化器被指导去考虑的搜索策略越多，把 SQL 编译成可执行的存取路径的时间就越长。幸运的是，可以设置优化等级来限制优化查询时所应用的技术数目。对于较简单的查询、资源受限系统和动态 SQL，这是非常有用的。表 1 概括了优化等级。

表 1. DB2 优化等级。

等级	描述
0	指导优化器使用最少的优化来生成存取方案。只可使用嵌套循环连接和索引扫描访问方法。限制使用统计信息（例如，不考虑非一致性分布统计）。
1	类似于等级 0，但添加了归并连接、表扫描和非常基本的查询重写（再加一些额外的特性）。
3	极大地改进了等级 1，但比等级 3 所付出的编译代价要低得多。这个等级利用了所有可用的统计信息、大多数查询重写规则、列表预取装和汇总表路由。类似于等级 5，但它使用贪婪的连接枚举（Greedy join enumeration），而不是动态编程。
5	该等级最接近于 DB2 for OS/390® 所采用的查询优化。它提供了中等数量的优化，需要中等数量资源来编译。
7	提供了极多的优化，需要比等级 3 更多的资源来编译。优化器智能地确定何时不保证额外资源用于动态 SQL。对于兼有复杂和较简单的查询这种混合情形，等级 5 是很好的选择。
9	该等级类似于等级 5，但它添加了一些在等级 5 中不可用的优化技术。该等级不会确定对于动态 SQL 什么时候会出现额外资源不足。
0	使用所有可用的优化技术。

虽然可以选择上表中所描述的任何查询优化等级，但只有很少一些情形才会使用等级 0 和 9。等级 0、1 和 2 使用贪婪连接枚举算法；对于复杂查询，这个算法与等级 3 及其之上的等级相比，考虑的备用计划极少，因此编译时间也少得多 。等级 3 和这之上的等级使用动态编程连接枚举（Dynamic Programming join enumeration）算法；这个算法与等级 0、1 和 2 相比，考虑的备用计划更多，这可能促成需要极其多的编译时间。

设定具体的查询优化等级方式取决于是使用静态 SQL 还是使用动态 SQL。对于静态 SQL 语句，在 PREP 和 BIND 命令上指定优化等级。SYSCAT.PACKAGES 目录表中的 QUERYOPT 列记录了用于绑定包的优化等级。动态 SQL 语句使用由 CURRENT QUERY OPTIMIZATION 专用寄存器指定的优化等级，可以用 SQL SET 语句来设置 CURRENT QUERY OPTIMIZATION。

最后，让我总结一下两类搜索策略及其特征。第一类，贪婪连接枚举，等级 0、1 和 2 使用该算法。使用贪婪连接枚举时，对于两个表，一旦选定连接方法之后，在进一步的优化期间，不会更改连接方法。所以，当连接许多表时，这种策略所选择的可能不是绝对最佳的存取方案。对于仅连接几个表的查询而言，另一类搜索策略（动态编程连接枚举）所选定的存取方案极有可能与贪婪连接枚举所选定的存取方案相同。随着正在连接的表的数目增加，动态编程连接枚举将需要更多的时间和资源。这比贪婪连接枚举更有可能选出最佳存取方案。

使用 Visual Explain

既然我们已经基本掌握了 DB2 可以选择以实现 SQL 请求的存取路径，那么，让我们讨论如何弄清楚 DB2 对这些查询使用了哪种存取路径。可以使用 explain 来做到这一点。explain 可以用于单个的 SQL 语句或者包中一系列 SQL 语句。当然，在“说明”包时，只会“说明”静态 SQL。对于 Delphi，这是没有帮助的，正如前面所提到的，这是因为所有 SQL 是动态的，而不是静态的。

当请求 explain 时，通过 DB2 优化器传递 SQL 语句，并将 DB2 所选定的存取路径以代码格式外部化成一组 DB2 explain 表。explain 表只不过是标准的 DB2 表，必须用预先确定的列、数据类型和长度来定义这些表。但是，请记住，explain 表不是自动创建的。为了使用 explain，您（或者您的 DBA）必须首先创建这些 explain 表。可以在安装了 DB2 的 sqllib 目录的 misc 子目录中找到名为 explain.ddl 的 DB2 CLP 脚本。执行该脚本将会创建 explain 表。一旦成功地创建了 explain 表之后，可以用几个选项来“说明”DB2 存取路径。

Visual Explain 是最方便的方法，因为可以用带有简单的点击式命令和下拉菜单的 GUI 来访问它（请参阅 图 2）。可以作为单独的工具或者从 DB2 命令中心来访问 Visual Explain。Visual Explain 的主要好处是它提供了存取路径的图形化描述，所以不需要理解 explain 表中的代码信息。每个存取路径操作都被置于树状结构中带颜色的代码节点。在节点上，简单地移动鼠标，并单击鼠标，就可以显示存取路径中该部分的参数、统计信息和成本估计。还可以用 db2vexp.exe 命令从命令行运行 Visual Explain。

图 2. Visual Explain GUI

图 2中的这个示例显示了 DB2 使用索引 PK_15 的索引扫描来选择 ATTRIBUTE_REGION 表中的数据。单击节点，可以获得其它一些有关存取路径每个部分中各组成部分的详细信息。正如我们在前面有关存取路径一节中所讨论的，将有表示扫描、索引式访问和排序的节点。图 3 显示了单击 IXSCAN 节点后的结果。我们可以看到将对 REGION_TYPE_ID 列进行索引扫描。通过查看 explain 的输出，可以方便地确定 DB2 将用于实现每条 SQL 查询的存取路径（请参阅图 3）。

图 3. Visual Explain 详细信息

如果没有象 Visual Explain 这样的工具来提供易于阅读的存取路径信息，则需要一个手工过程。而且，必须能够解释 explain 表中的代码信息，以理解手工 explain 的输出。有了 Visual Explain，就不需要您亲自关注 explain 表的实际格式或内容 — 这个工具会为您做所有这一切。

DB2 还提供其它 EXPLAIN 工具。其中包括 db2expln，它是这样一种“基石”工具：仅为静态包提供存取路径的文本描述。对于 Delphi，该工具没有用；相反，您可能选择用 dynexpln，它提供了动态 SQL 查询的文本分析。dynexpln 工具将打包动态查询，然后调用 db2expln 来做这项工作。但是，一般情况下，请坚持使用 Visual Explain，因为它更易于使用，并且提供了调优 SQL 所需的基本信息。

对于 Delphi 用户，好的经验规则是使用 Visual Explain 来显示 Delphi 程序中 SQL SELECT 语句的存取路径。对于大多数 SELECT 语句，请尝试使用索引式访问。要做到这一点，可以创建其它索引（在生产环境中，只有在 DBA 的指导下才能这样做），或者修改 SQL 语句以包含可索引的谓词。分析正在使用的连接方法，理解其中的含义。例如，归并扫描连接需要排序吗？这是可以接受的，还是性能会受到影响？

请记住，的结果仅仅相当于 DB2 系统目录中的统计信息。在使用 explain 之前，请确保 DB2 系统目录统计信息是最新的。在系统目录中，DB2 表、索引和列的精确统计信息有助于优化器选择有效的存取方案。如果最近没有收集统计信息，则在运行 explain 之前，验证这些信息是否仍合适。

最后，要意识到还有一些方面这里未讨论到，它们都是做好 SQL 调优工作所必需的。要正确分析 SQL 性能，需要的内容将不仅仅是 explain 结果。正确的性能分析需要：

实际的 SQL 语句

正在被访问和／或修改的对象的 DDL（或系统目录信息）的列表

内嵌 SQL 语句的 Delphi 代码

在执行 explain 时，存在当前的系统目录统计信息

了解将执行 SQL 语句的 DB2 环境（包括缓冲区和锁定参数等设置）

了解正在运行 SQL 的环境（包括操作系统、处理器的数目和类型以及内存大小等）

了解在执行（或将要执行）SQL 语句时，系统中的并发活动

可以将这些附加信息和 explain 输出一起使用，以估计任何给定 SQL 语句的性能。Delphi 代码很重要，它可以帮助您调节应用程序性能，因为 explain 不能提供有关内嵌 SQL 的高级语言的信息。explain 输出可以显示 SQL 语句的有效存取路径，不过，如果 SQL 语句嵌入在运行数千次的循环中，则性能很可能会受到影响。

使用 explain 来帮助确保索引被正确地用于连接谓词、本地谓词以及 GROUP BY 和 ORDER BY 子句，以避免排序。而且，应用您对表中数据的了解来确定正采用的连接类型是否正确，以及正在用于连接的内表和外表的表是否正确。对这些类型细节的注意会因优化的应用程序和较慢执行者的不同而不同。

结束语

有效地使用 Visual Explain 工具可以帮助 Delphi 程序员了解正在用于实现 DB2 SQL 请求的存取路径。有许多可供 DB2 选择的技术来实现对数据的请求 — 其中一些技术比其它技术要有效得多。博学的 Delphi 程序员可以使用 explain 来优化其代码，从而能有效地访问 DB2 数据。