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

核心提示：通过-i 指定的SQL文件可以包含多个查询，但是查询必须以分号分隔，提高DB2查询性能的常用方法(3)，这与db2expln命令不同，db2expln可以通过-z参数指定多个查询之间的分隔符，这种扫描的范围扩大到了整个索引，我们称之为 Non-matching Index Scan，用户可以把某一个 workload中

通过-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:tempsql_3.txt -r d:tempsql_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.根据不同索引生成的存取计划