db2嵌套查询效率_提高 DB2 查询性能的常用方法

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

张大为 和 陈力

2007 年 9 月 28 日发布

简介

随着 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 -e -g T -o -u -w

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 -user -stmtfile

-z @ -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 -a / -i -o

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 ”为其指定在这个 workload 中的执行频率。db2advis 会根据每个查询在这个 workload 的频率指数进行权衡来给出索引的创建建议，从而达到整个 workload 的性能最优。

db2batch

前面介绍的工具和命令只提供了查询