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

xiaochuan

　　http://www.searchdatabase.com.cn/ShowContent_17217.htm
　　本文先对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 提供了多种方法来得到可视化查询计划。
　　1. 通过 DB2 Control Center 获得可视化查询计划。如图1：

　　
　图1. 可视化查询计划
　　可视化查询计划
 
　　点击”Explain SQL”后输入要进行分析的查询语句以及查询标号和标签，点击 Ok 按钮便可得到可视化的查询计划。此时，查询计划会被存储在系统的 Explain 表中。用户可以通过图 1 中的”Show Explained Statements History”命令获得存储在 Explain 表中的所有查询计划。
　　2. 通过 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则指定了多个查询间的间隔符。