提高 DB2 查询性能的常用方法

最新推荐文章于 2023-10-12 08:58:52 发布

pianzif

最新推荐文章于 2023-10-12 08:58:52 发布

阅读量4.4k

点赞数

分类专栏： DB2@BK 文章标签： db2 查询计划

DB2@BK 专栏收录该内容

104 篇文章 6 订阅

订阅专栏

本文探讨了DB2环境下提升查询性能的方法，包括使用VisualExplain和db2expln工具查看存取计划，合理创建和维护索引，改写查询语句，以及通过UDF优化复杂查询。重点介绍了通过分析查询计划来识别性能瓶颈，针对性地优化索引布局，减少数据扫描，利用子查询和UDF提高查询效率。

摘要由CSDN通过智能技术生成

2007 年 9 月 28 日

本文先对 DB2 提供的几种用于提高查询性能的相关工具和命令进行介绍，然后根据笔者的工作经验介绍一些常用的技巧和方法来提高查询性能。主要集中于如何创建和维护索引、改写查询以及改变查询的实现方式，相关内容都将通过实例加以说明。

简介

随着 DB2 应用的逐渐增多，越来越多的数据库开发人员在项目开发过程中都会遇到查询过于复杂，导致性能难以接受的问题。本文将主要从一个数据库开发者的角度介绍几种常用的方法来提高 DB2 查询的性能，而并不讨论如何通过配置 DB2 的各项参数以及调整服务器环境等方式来提高整个数据库性能的方法。系统配置等工作属于 DBA 的工作范畴，在一般的项目开发中，这对于开发人员都是透明的。本文先对 DB2 提供的几种用于提高查询性能的相关工具和命令进行介绍，然后根据笔者的工作经验介绍一些常用的技巧和方法来提高查询性能。主要集中于如何创建和维护索引、改写查询以及改变查询的实现方式，相关内容都将通过实例加以说明。

DB2 提供的几种相关工具和命令

我们将着重介绍如何使用 Visual Explain 和 db2expln 查看动态查询的存取计划。读者可以查阅 DB2 Info Center 获得有关查看静态查询存取计划的内容。

DB2 Visual Explain

DB2 提供了非常直观有效的方法来查看查询的存取计划。DB2 Visual Explain 能够获得可视化的查询计划，而 db2expln 命令则可以获得文本形式的查询计划。有了查询计划，我们就可以有针对的对查询进行优化。根据查询计划找出代价最高的扫描 ( 表扫描，索引扫描等 ) 和操作 (Join，Filter，Fetch 等 )，继而通过改写查询或者创建索引消除代价较高的扫描或操作来优化查询。

DB2 提供了多种方法来得到可视化查询计划。

通过 DB2 Control Center 获得可视化查询计划。如图 1：

图 1. 可视化查询计划

点击”Explain SQL”后输入要进行分析的查询语句以及查询标号和标签，点击 Ok 按钮便可得到可视化的查询计划。此时，查询计划会被存储在系统的 Explain 表中。用户可以通过图 1 中的”Show Explained Statements History”命令获得存储在 Explain 表中的所有查询计划。

通过 Command Editor( 在 DB2 8.2 版本之前叫做 Command Center) 获得可视化的查询计划。如图 2：

图 2. 获得可视化的查询计划

在主窗口输入查询并连接数据库后，点击图中所示的按钮即可得到可视化的查询计划，如图 3：

图 3. 查询计划结果

在图 3 所示的查询计划中，还可以点击图示中的每个节点来察看详细的统计信息。譬如双击节点”FETCH(13) 21,959.75” 后将会弹出如图 4 所示的对话框：

图 4. 详细的统计信息

图 4 中的统计信息主要包括此 FETCH 操作的总代价，CPU，I/O 以及获得结果集中的第一行的代价。在这里，timerons 是结合了 CPU 和 I/O 代价的成本单位。此外，图 4 中还收集了其他相关信息。譬如此操作读取了哪个表的哪些列，每个谓词的选择度 (selectivity)，使用了多少 buffer 等等。

db2exfmt

db2exfmt 命令能够将 Explain 表中存储的存取计划信息以文本的形式进行格式化输出。db2exfmt 命令将各项信息更为直观的显示，使用起来更加方便。命令如清单 1 所示：

清单 1. db2exfmt 命令

                
db2exfmt -d <db_name> -e <schema> -g T -o <output> -u <user> <password> -w <timestamp>
Example: db2exfmt -d test_db -e user -g T -o D:\temp\sql_1_result_db2exfmt.txt
 -u user password -w l
Query:
                sql_1.txt（附件中）
                Results:
                sql_1_result_db2exfmt.txt（附件中）

db2expln

db2expln 是命令行下的解释工具，和前面介绍的 Visual Explain 功能相似。通过该命令可以获得文本形式的查询计划。命令如清单 2 所示 :

清单 2. db2expln 命令

                db2expln -d <db_name> -user <user> <password> -stmtfile <sql.file>
		 -z @ -output <output> -g
Example: db2expln -d test_db -user user password -stmtfile D:\temp\sql_1.txt
 -z @ -output D:\temp\sql_1_result_db2expln.txt –g
Query:
                 sql_1.txt（附件中）
                Results:
                 sql_1_result_db2expln.txt（附件中）

db2expln 将存取计划以文本形式输出，它只提供存取计划中主要的信息，并不包含每一个操作占用多少 CPU、I/O、占用 Buffer 的大小以及使用的数据库对象等信息，方便阅读。但是 db2expln 也会将各项有关存取计划的信息存入 Explain 表中，用户可以使用 db2exfmt 察看详细的格式化文本信息。

db2advis

db2advis 是 DB2 提供的另外一种非常有用的命令。通过该命令 DB2 可以根据优化器的配置以及机器性能给出提高查询性能的建议。这种建议主要集中于如何创建索引，这些索引可以降低多少查询代价，需要创建哪些表或者 Materialized Query Table(MQT) 等。命令如清单 3 所示：

清单 3. db2advis 命令

                db2advis -d <db_name> -a <user>/<password> -i <sql.file> -o <output>
Example: db2advis -d test_db -a user/password
 -i D:\temp\sql_2.txt > D:\temp\sql_2_result_db2advis.txt
Query:
                 sql_2.txt（附件中）
                Results:
                sql_2_result_db2advis.txt（附件中）

通过 -i 指定的 SQL 文件可以包含多个查询，但是查询必须以分号分隔。这与 db2expln 命令不同，db2expln 可以通过 -z 参数指定多个查询之间的分隔符。用户可以把某一个 workload 中所使用的所有查询写入 SQL 文件中，并在每个查询之前使用”--#SET FREQUENCY <num>”为其指定在这个 workload 中的执行频率。db2advis 会根据每个查询在这个 workload 的频率指数进行权衡来给出索引的创建建议，从而达到整个 workload 的性能最优。

db2batch

前面介绍的工具和命令只提供了查询的估算代价，但有些时候估算代价和实际的执行时间并不是完全呈线形关系，有必要实际执行这些查询。db2batch 就是这样一个 Benchmark 工具，它能够提供从准备到查询完成中各个阶段所花费地具体时间，CPU 时间，以及返回的记录。命令如清单 4 所示：

清单 4. db2batch 命令

                db2batch -d <db_name> -a <user>/<password>
 -i <time_condition> -f <sql.file> -r <output>
Example: db2batch -d test_db -a user/password
 -i complete -f D:\temp\sql_3.txt -r d:\temp\sql_3_result_db2batch.txt
Query:
                 sql_3.txt（附件中）
                Results:
                 sql_3_result_db2batch.txt（附件中）

对于执行 db2batch 时一些详细的设置可以通过 -o 参数指定，也可以在 SQL 文件中指定，譬如本例中在 SQL 文件中使用了下面的配置参数 :

--#SET ROWS_FETCH -1 ROWS_OUT 5 PERF_DETAIL 1 DELIMITER @ TIMESTAMP

其中 ROWS_FETCH 和 ROWS_OUT 定义了从查询的结果集中读取记录数和打印到输出文件中的记录数，PERF_DETAIL 设置了收集性能信息的级别，DELIMITER 则指定了多个查询间的间隔符。

下面我们将从三个方面介绍一些提高查询性能的方法。

创建索引

根据查询所使用的列建立多列索引

建立索引是用来提高查询性能最常用的方法。对于一个特定的查询，可以为某一个表所有出现在查询中的列建立一个联合索引，包括出现在 select 子句和条件语句中的列。但简单的建立一个覆盖所有列的索引并不一定能有效提高查询，因为在多列索引中列的顺序是非常重要的。这个特性是由于索引的 B+ 树结构决定的。一般情况下，要根据谓词的选择度来排列索引中各列的位置，选择度大的谓词所使用的列放在索引的前面，把那些只存在与 select 子句中的列放在索引的最后。譬如清单 5 中的查询：

清单 5. 索引中的谓词位置

                select add_date
from temp.customer
where city = 'WASHINGTON'
and cntry_code = 'USA';

对于这样的查询可以在 temp.customer 上建立 (city，cntry_code，add_date) 索引。由于该索引包含了 temp.customer 所有用到的列，此查询将不会访问 temp.customer 的数据页面，而直接使用了索引页面。对于包含多列的联合索引，索引树中的根节点和中间节点存储了多列的值的联合。这就决定了存在两种索引扫描。回到清单 5 中的查询，由于此查询在新建索引的第一列上存在谓词条件，DB2 能够根据这个谓词条件从索引树的根节点开始遍历，经过中间节点最后定位到某一个叶子节点，然后从此叶子节点开始往后进行在叶子节点上的索引扫描，直到找到所有满足条件的记录。这种索引扫描称之为 Matching Index Scan。但是如果将 add_date 放在索引的第一个位置，而查询并不存在 add_date 上的谓词条件，那么这个索引扫描将会从第一个索引叶子节点开始，它无法从根节点开始并经过中间节点直接定位到某一个叶子节点，这种扫描的范围扩大到了整个索引，我们称之为 Non-matching Index Scan。图 5 显示了 DB2 根据不同索引生成的存取计划。

图 5. 根据不同索引生成的存取计划

根据条件语句中的谓词的选择度创建索引

因为建立索引需要占用数据库的存储空间，所以需要在空间和时间性能之间进行权衡。很多时候，只考虑那些在条件子句中有条件判断的列上建立索引会也会同样有效，同时节约了空间。譬如清单 5 中的查询，可以只建立 (city，cntry_code) 索引。我们还可以进一步地检查条件语句中的这两个谓词的选择度，执行清单 6 中的语句检查谓词选择度：

清单 6. 检查谓词选择度

                
                Queries:
1. select count(*) from temp.customer
where city = 'WASHINGTON'
and cntry_code = 'USA';
2. select count(*) from temp.customer
where city = 'WASHINGTON';
3. select count(*) from temp.customer
where cntry_code = 'USA';
Results:
1. 1404
2. 1407
3. 128700

选择度越大，过滤掉的记录越多，返回的结果集也就越小。从清单 6 的结果可以看到，第二个查询的选择度几乎有和整个条件语句相同。因此可以直接建立单列索引 (city)，其性能与索引 (city，cntry_code，add_date) 具有相差不多的性能。表 1 中对两个索引的性能和大小进行了对比。

表 1. 两个索引的性能和大小对比

索引	查询计划总代价	索引大小
cust_i1(city，cntry_code，add_date)	28.94 timerons	19.52M
cust_i3(city)	63.29 timerons	5.48M

从表 1 中可以看到单列索引 (city) 具有更加有效的性能空间比，也就是说占有尽可能小的空间得到尽可能高的查询速度。

避免在建有索引的列上使用函数

这是一个很简单的原则，如果在建有索引的列上使用函数，由于函数的单调性不确定，函数的返回值和输入值可能不会一一对应，就可能存在索引中位置差异很大的多个列值可以满足带有函数的谓词条件，因此 DB2 优化器将无法进行 Matching Index Scan，更坏的情况下可能会导致直接进行表扫描。图 6 中对比了使用 function 前后的存取计划的变化。

图 6. 使用 function 前后的存取计划的变化

在那些需要被排序的列上创建索引

这里的排序不仅仅指 order by 子句，还包括 distinct 和 group by 子句，他们都会产生排序的操作。由于索引本身是有序的，在其创建过程中已经进行了排序处理，因此在应用这些语句的列上创建索引会降低排序操作的代价。这种情况一般针对于没有条件语句的查询。如果存在条件语句，DB2 优化器会首先选择出满足条件的纪录，然后才对中间结果集进行排序。对于没有条件语句的查询，排序操作在总的查询代价中会占有较大比重，因此能够较大限度的利用索引的排序结构进行查询优化。此时可以创建单列索引，如果需要创建联合索引则需要把被排序的列放在联合索引的第一列。图 7 对比了清单 7 中的查询在创建索引前后的存取计划。

清单 7. 查询在创建索引前后的存取计划

                select distinct add_date from temp.customer;

图 7. 在创建索引前后的存取计划

从图 7 中我们可以看到在没有索引的情况下 SORT 操作是 24751.69 timerons，但是有索引的情况下，不再需要对结果集进行排序，可以直接进行 UNIQUE 操作，表中显示了这一操作只花费了 2499.98 timerons.

图 8 对比了清单 8 中的查询在创建联合索引前后的存取计划，从中可以更好的理解索引对排序操作的优化。

清单 8. 查询示例

                
select cust_name from temp.customer order by add_date;

图 8. 创建联合索引前后的存取计划

索引的 B+ 树结构决定了索引 temp.cust_i5 的所有叶子节点本身就是按照 add_date 排序的，所以对于清单 8 中的查询，只需要顺序扫描索引 temp.cust_i5 的所有叶子节点。但是对于 temp.cust_i6 索引，其所有叶子节点是按照 cust_name 排序，因此在经过对索引的叶子节点扫描获得所有数据之后，还需要对 add_date 进行排序操作。

合理使用 include 关键词创建索引

对于类似下面的查询 :

清单 9. 查询示例

                select cust_name from temp.customer
where cust_num between '0007000000' and '0007200000'

在第一点中我们提到可以在 cust_num 和 cust_name 上建立联合索引来提高查询性能。但是由于 cust_num 是主键，可以使用 include 关键字创建唯一性索引：

create unique index temp.cust_i7 on temp.customer(cust_num) include (cust_name)

使用 include 后，cust_name 列的数据将只存在于索引树的叶子节点，并不存在于索引的关键字中。这种情况下，使用带有 include 列的唯一索引会带来优于联合索引的性能，因为唯一索引能够避免一些不必要的操作，如排序。对于清单 9 中的查询创建索引 temp.cust_i7 后存取计划的代价为 12338.7 timerons，创建联合索引 temp.cust_i8(cust_num，cust_name) 后的代价为 12363.17 timerons。一般情况下，当查询的 where 子句中存在主键的谓词我们就可以创建带有 include 列的唯一索引，形成纯索引访问来提高查询性能。注意 include 只能用在创建唯一性索引中。

指定索引的排序属性

对于下面用来显示最近一个员工入职的时间的查询：

select max(add_date) from temp.employee

很显然这个查询会进行全表扫描。查询计划如图 9.a:

图 9. 查询计划

显然我们可以在 add_date 上创建索引。根据下面的命令创建索引后的查询计划如图 9.b。

create index temp.employee_i1 on temp.employee(add_date)

这里存在一个误区，大家可能认为既然查询里要取得的是 add_date 的最大值，而我们又在 add_date 上建立了一个索引，优化器应该知道从索引树中直接去寻找最大值。但是实际情况并非如此，因为创建索引的时候并没有指定排序属性，默认为 ASC 升序排列，DB2 将会扫描整个索引树的叶子节点取得所有值后，然后取其最大。我们可以通过设置索引的排序属性来提高查询性能，根据下面的命令创建索引后的查询计划如图 9.c。

create index temp.employee_i1 on temp.employee(add_date desc)

对于降序排列的索引，DB2 不需要扫描整个索引数的叶子节点，因为第一个节点便是最大的。我们同样可以使用 ALLOW REVERSE SCANS 来指定索引为双向扫描，具有和 DESC 近似的查询性能。ALLOW REVERSE SCANS 可以被认为是 ASC 和 DESC 的组合，只是在以后数据更新的时候维护成本会相对高一些。

如果无法改变索引的排序属性，但是我们具有额外的信息，该公司每个月都会有新员工入职，那么这个查询就可以改写成：

select max(add_date) from temp.employee where add_date > current timestamp - 1 month

这样通过限定一个查询范围也会有效地提高查询性能。

索引和表的维护

重新组织索引

随着数据的不断删除，插入和更新，索引页会变得越来越零散，索引页的物理存储顺序不再匹配其逻辑顺序，索引结构的层次会变得过大，这些都会导致索引页的预读取变得效率低下。因此，根据数据更新的频繁程度需要适当的重新组织索引。可以使用 REORG INDEXES 命令来重新组织索引结构，也可以删除并重新创建索引达到相同的目的。同样的，对表进行重新组织也会带来性能的改善。

重新组织某一个表的所有索引的命令如下：REORG INDEXES ALL FOR TABLE table_name。

重新组织一个表的数据的命令如下，在下面的命令还可以为其指定一个特定的索引，REORG 命令将会根据这个索引的排序方式重新组织该表的数据。

REORG TABLE table_name INDEX index_name。

重新收集表和索引的统计信息

和在 2.1 中提到的原因类似，当一个表经过大量的索引修改、数据量变化或者重新组织后，可能需要重新收集表以及相关索引的统计信息。这些统计信息主要是关于表和索引存储的物理特性，包括记录数目，数据页的数目以及记录的平均长度等。优化器将根据这些信息决定使用什么样的存取计划来访问数据。因此，不能真实反映实际情况的统计信息可能会导致优化器选择错误的存取计划。收集表及其所有索引的统计信息的命令如下：RUNSTATS ON TABLE table_name FOR INDEXES ALL。

上述两个命令具有复杂的参数选择，用户可以参阅 DB2 Info Center 来根据实际情况使用这两个命令。

修改查询

合理使用 NOT IN 和 NOT EXISTS

一般情况下 NOT EXISTS 具有快于 NOT IN 的性能，但是这并不绝对。根据具体的数据情况、存在的索引以及查询的结构等因素，两者会有较大的性能差异，开发人员需要根据实际情况选择适当的方式。

譬如下面的查询：

清单 10. 查询示例

                表结构：temp.customer(cust_num) 主键：cust_num
表结构：temp.contact(cnt_id，cust_num) 主键：cnt_id
表结构：temp.contact_detail(cnt_id，address，phone) 主键：cnt_id
查询 :
select cust_num
from temp.customer cust
where not exists (select 1 from temp.contact cont
here cust.cust_num = cont.cust_num)

此查询用来列出所有不存在联系人的客户。对于这样的需求，开发人员会最自然的写出清单 10 中的查询，的确，对于大部分情况它具有最优的性能。该查询的查询代价为 178,430 timerons。让我们再来看看使用 NOT IN 后查询的总代价，请看清单 11。

清单 11. 查询示例

                
                查询：
select cust_num
from temp.customer cust
where cust.cust_num not in (select cont.cust_num from temp.contact cont)
代价：12,648,897,536 timerons

可以看到 NOT EXISTS 的性能要比 NOT IN 高出许多。NOT IN 是自内向外的操作，即先得到子查询的结果，然后执行最外层的查询，而 NOT EXISTS 恰好相反，是自外向内的操作。在上述例子中，temp.contact 表中有 65 万条记录，使得 10.2 查询中的 NOT IN 列表非常大，导致了使用 NOT IN 的查询具有非常高的查询代价。下面我们对 10.1 和 10.2 的查询进行修改，将 temp.contact 表中的记录限制到 100 条，请看下面的查询：

清单 12. 查询示例

                
                查询：
select cust_num
from temp.customer cust
where not exists (select 1 from temp.contact cont
here cust.cust_num = cont.cust_num
and cont.cnt_id < 100)
代价：42,015 timerons

清单 13. 查询示例

                
                查询：
select cust_num
from temp.customer cust
where cust.cust_num not in (select cont.cust_num from temp.contact cont
where cont.cnt_id < 100)
代价：917,804 timerons

从 12 和 13 中可以看出 NOT EXISTS 的查询代价随子查询返回的结果集的变化没有大幅度的下降，随着子查询的结果集从 65 万下降到 100 条，NOT EXISTS 的查询代价从 178,430 下降到 42,015，只下降 4 倍。但是 NOT IN 的查询代价却有着极大的变化，其查询代价从 12,648,897,536 下降到 917,804，下降了 13782 倍。可见子查询的结果集对 NOT IN 的性能影响很大，但是这个简单的查询不能说明 NOT EXISTS 永远好于 NOT IN，因为同样存在一些因素对 NOT EXISTS 的性能有很大的影响。我们再看下面的例子：

清单 14. 查询示例

                
                查询：
select cust_num
from temp.customer cust
where not exists (select 1 from temp.contact cont
where cust.cust_num = cont.cust_num
and cont.cnt_id in (select cnt_id from temp.contact_detail
where cnt_id<100))
代价：5,263,096 timerons

清单 15. 查询示例

                
                查询：
select cust_num
from temp.customer cust
where cust_num not in (select cust_num from temp.contact cont
where cont.cnt_id in (select cnt_id from temp.contact_detail
where cnt_id<100))
代价：4,289,095 timerons

在上面的例子中，我们只是对查询增加了一个小改动，使用一个嵌套查询限制了在 temp.contact 中扫描的范围。但是在这两个新的查询中，NOT IN 的性能却又好于 NOT EXISTS。NOT EXISTS 的代价增加了 125 倍，而 NOT IN 的代价却只增加了 4 倍。这是由于 NOT EXISTS 是自外向内，嵌套查询的复杂度对其存在较大的影响。因此在实际应用中，要考虑子查询的结果集以及子查询的复杂度来决定使用 NOT EXISTS 或者 NOT IN。对于 IN，EXISTS 和 JOIN 等操作，大多数情况下 DB2 优化器都能形成比较一致的最终查询计划

合理使用子查询减少数据扫描和利用索引

某些情况下可以将查询中的某一部分逻辑提取出来作为子查询出现，能够减少扫描的数据量，以及利用索引进行数据检索。请看清单 16 中的查询：

清单 16.

                
                索引：temp.cust_i1 on temp.customer(add_date)
temp.order_i1 on temp.order(sold_to_cust_num)
temp.order_i2 on temp.order(add_date)
查询：
select cust.cust_num
from temp.customer cust
left join temp.order ord
on cust.cust_num = ord.sold_to_cust_num
where cust.add_date > current timestamp - 2 months
or ord.add_date > current timestamp - 2 months

上面的查询用来选择所有两个月内新增加的用户以及在两个月内定购了产品的用户。从图 10.a 的查询计划中可看出没有任何索引被使用。

图 10. 查询计划

使用子查询对该查询重新改写后，请看清单 17:

清单 17.

                
                查询：
with tmp as(
select sold_to_cust_num from temp.order 
where add_date > current timestamp - 2 months)
select cust.cust_num from temp.customer cust
where cust.add_date > current timestamp - 2 months
or cust.cust_num in (select sold_to_cust_num from tmp )

在清单 17 的查询中，我们使用子查询预先限定了要扫描 temp.order 表中的记录数目，而不是像清单 16 中的查询那样对 temp.order 表进行全表扫描。同时，在预先限定数据范围的时候，能够利用 temp.order_i2 索引。请看其查询计划，如图 10.b。可以看到查询代价有大幅度下降。其实，即使没有 temp.order_i2 索引，修改后的查询也仍然由于前者，因为它预先限定了数据的扫描范围，也减少了后续连接处理的数据量，请看图 10.c。

重新排列各个表的连接顺序，尽量减小中间结果集的数据量

一般情况下，DB2 会根据各表的 JOIN 顺序自顶向下顺序处理，因此合理排列各表的连接顺序会提高查询性能。譬如清单 18 中的查询：

清单 18.

                
                查询：
select cust.cust_name, ord.order_num, cnt.cnt_first_name
from temp.customer cust
left join temp.order ord
on cust.cust_num = ord.sold_to_cust_num
join temp.contact cnt
on cust.cust_num = cnt.cust_num
where cnt.mod_date > current timestamp - 1 months

清单 18 中的查询用来选择出所有最近一个月内修改过联系人信息的客户的订单信息。此查询会按照链接的顺序先将 temp.customer 表和 temp.order 表进行 LEFT JOIN，然后使用结果集去 JOIN temp.contact 表。由于该查询使用了 LEFT JOIN，因此在生成中间结果集的时候不会有任何记录会被过滤掉，中间结果集的记录数目大于等于 temp.customer 表。了解到了 DB2 是如何解释和执行这样的查询后，很自然的我们就会想到将 JOIN 提前。请看清单 19。

清单 19.

                
                查询：
select cust.cust_name, ord.order_num, cnt.cnt_first_name
from temp.customer cust
join temp.contact cnt
on cust.cust_num = cnt.cust_num
left join temp.order ord
on cust.cust_num = ord.sold_to_cust_num
where cnt.mod_date > current timestamp - 1 months

图 11.a 和图 11.b 分别为清单 18 和 19 的查询的存取计划。在 19 的查询中，在形成中间结果集的时候也应用到了 WHERE 语句中的条件，而不是在所有 JOIN 都结束以后才被应用去除记录的。

图 11. 查询计划

另外，在修改查询尽量减少中间结果集的记录条数的时候还要考虑中间结果集的数据总量，譬如中间结果集需要保存的每条记录的长度。如果我们把 JOIN temp.contact 提前以后，由于中间结果集需要保存过多的 contact 表的列反而使得结果集的数据总量变大，可能不会带来性能上的改善。

使用 UDF 代替查询中复杂的部分

由于 UDF 是预先编译的，性能普遍优于一般的查询，UDF 使用的存取计划一经编译就会相对稳定。笔者在工作中曾多次发现，使用 UDF 代替查询或者视图中的复杂部分会提高几倍甚至几十倍的性能，主要原因是迫使 DB2 使用指定的存取计划来充分利用 index 或者调整其访问过程（如 Join 顺序， Filter 位置等）。使用 UDF 进行优化的基本思路是，将复杂查询分解为多个部分执行，针对每个部分优化处理，将各部分组合时能够避免存取计划的一些不必要变化，优化整体性能。譬如清单 20 中的查询：

清单 20.

                
                查询：select * from temp.customer where cust_num in (
select distinct sold_to_cust_num from temp.order
where add_date > current timestamp - 2 months
union
select distinct cust_num from temp.contact
where add_date > current timestamp - 2 months
)

这个查询会导致优化器生成比较复杂的查询计划，尤其是 temp.customer 是一个比较复杂的视图的时候。这种情况下我们可以通过创建 UDF，将其分步执行：先执行子查询获得 cust_num 值的列表，然后执行最外层的查询。下面的例子是通过 UDF 对清单 20 的查询的改写：

清单 21.

                CREATE FUNCTION temp.getCustNum(p_date timestamp)
RETURNS
TABLE (cust_num CHARACTER(10))
RETURN
select distinct sold_to_cust_num from temp.order
where add_date > p_date
union
select distinct cust_num from temp.contact
where add_date > p_date;
select * from customer where cust_num in (
select cust_num from table(temp.getCustNum(current timestamp - 2 months)) tbl
)

改写前后的查询代价分别是 445,159.31 和 254,436.98。当面对比较复杂的查询时考虑使用 UDF 将其拆分为多步执行常常会带来意想不到的效果。在实际的项目中，如果数据处理和查询调用是包含在其他应用程序中如 Unix 脚本，Java 程序等，同样可以考虑采用分步数据处理的方式来调用数据库，以优化应用性能。

总结

本文主要介绍了如何使用 DB2 提供的各种查看存取计划的工具，并根据作者在 DB2 方面的开发经验总结了一些提高查询性能的方法和技巧。如果能够有效地利用 DB2 提供的各种工具，理解 DB2 中索引的结构，以及查询将如何被解释，数据库开发人员可以更好的提高查询性能来满足需求。

下载

描述	名字	大小	下载方法
本文用到的 SQL 脚本示例	dm-0709zhangdw.zip	8KB	HTTP


	关于下载方法的信息

参考资料

学习

DB2 Info Center：http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp。
在 developerWorks 中国网站 Information Management 专区中学习更多信息管理方面的知识。

获得产品和技术

下载 IBM 软件试用版，体验强大的 DB2®，Lotus®，Rational®，Tivoli® 和 WebSphere® 软件。

讨论

查看 developerWorks DB2 9 技术资源中心。

作者简介


		张大为，IBM CSDL 软件工程师. 目前从事 DB2 相关工作，主要对电子商务应用进行数据支持。对 DB2、Unix、Web Services 以及 Java 技术很感兴趣。


		陈力，目前从事 UNIX/DB2 相关工作，主要对电子商务应用进行数据支持。熟悉 DB2，J2EE 等技术，对 Web 应用、Web Services 以及 Java 技术很感兴趣。

http://www.xker.com/page/e2010/0424/96153_4.html