提高商业智能环境中DB2查询的性能（1）

高效地运行大型查询，是商业智能环境中的顶级性能挑战。学习在这种环境中提高 IBM® DB2® 数据服务器查询性能的技巧。逐步了解各种不同的方法，然后在自己的系统上进行试验。将每种方法应用于一条 SQL 语句，并使用 db2batch 工具评测性能。

简介

本文主要讨论可以使决策支持系统（DSS）中的大型查询高效地执行的一些方法。这些查询通常都是访问较多数据的单纯 select 查询。下面是我们要讨论的一些方法：

1、建立适当的参照完整性约束

2、使用物化查询表（MQT）将表复制到其它数据库分区，以允许非分区键列上的合并连接

3、使用多维集群（MDC）

4、使用表分区（DB2® 9 的新功能）

5、结合使用表分区和多维集群

6、使用 MQT 预先计算聚合结果

本文中的例子针对 Windows 平台上运行的 DB2 9。但是，其中的概念和信息对于任何平台都是有用的。由于大多数商业智能（BI）环境都使用 DB2 Database Partitioning Feature（DPF，DB2 数据库分区特性），我们的例子也使用 DPF 将数据划分到多个物理和逻辑分区之中。

数据库布局和设置

本节描述用于在我们的系统上执行测试的数据库的物理和逻辑布局。

星型模式布局

本文使用如下所示的星型模式：

清单 1. 星型模式　　　　　　　　　　　　　　　　　PRODUCT_DIM　　　　　　 DATE_DIM
　　　　　　　　　　　　　　　　　　　　　　　　　　　　/
　　　　　　　　　　　　　　　　　　　　　　 　　　　　/
　　　　　　　　　　　　　　　　　　　　　　　SALES_FACT
　　　　　　　　　　　　　　　　　　　　　　　　　|
　　　　　　　　　　　　　　　　　　　　　　　　　|
　　　　　　　　　　　　　　　　　　 STORE_DIM

其中的表的定义如下：

事实表 SALES_FACT 包含 2006 年的总体销售信息。它包括产品售出日期、产品 ID、销售该产品的商店的 ID、售出的特定产品的数量，以及产品的价格。事实表中还添加了 TRANSACTION_DETAILS 列，以便在从事实表中访问数据时生成更多的 I/O。

维度表 DATE_DIM 包含商店开放期间的惟一的日期和相应的月份、季度和年份信息。

维度表 PRODUCT_DIM 包含公司所销售的不同产品。每种产品有一个惟一的产品 ID 和一个产品描述、型号以及质地。

维度表 STORE_DIM 包含不同的商店 ID 和商店的位置、所属街区以及所属区域等信息。

数据库分区信息

各表都位于它自己的分区组中。3 个维度表都比较小，所以它们位于一个数据库分区上。而事实表则跨 4 个分区。

表空间信息

各表都位于自己的表空间中。还有一种常见的方法是将这 3 个维度表放在同一个表空间中。

缓冲池信息

本文中的测试所使用的默认缓冲池是 IBMDEFAULTBP，该缓冲池由 1,000 个 4K 的页面组成。在本文的测试中，所有表空间共享这个缓冲池。在通常的 BI 环境中，会创建不同的缓冲池。

主查询

下面的查询用于测试本文中讨论的各种不同的方法。该查询执行一个向外连接，比较二月份和十一月份 10 家商店的销售信息。

清单 2. 主查询 [Query1.sql]WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS
( SELECT
　　 D.MONTH AS MONTH,
　　 S.STORE_ID AS STORE_ID,
　　 S.DISTRICT AS DISTRICT,
　　 S.REGION AS REGION,
　　 SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT
　FROM
　　 SKAPOOR.SALES_FACT F1,
　　 SKAPOOR.DATE_DIM D,
　　 SKAPOOR.PRODUCT_DIM P,
　　 SKAPOOR.STORE_DIM S
　　
　WHERE
　　 P.MODEL LIKE '%model%' AND
　　 F1.DATE_ID=D.DATE_ID AND
　　 F1.PRODUCT_ID=P.PRODUCT_ID AND
　　 F1.STORE_ID=S.STORE_ID AND
　　 F1.DATE_ID BETWEEN '2006-01-01' AND '2006-01-31' AND
　　 F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND　　　　
　　 D.MONTH = 1
　GROUP BY
　　 S.STORE_ID,S.DISTRICT,S.REGION,D.MONTH) ,
　　
TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS
( SELECT
　　 D1.MONTH AS MONTH,
　　 S1.STORE_ID AS STORE_ID,
　　 S1.DISTRICT AS DISTRICT,
　　 S1.REGION AS REGION,
　　 SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT
　FROM
　　 SKAPOOR.SALES_FACT F2,
　　 SKAPOOR.DATE_DIM D1,
　　 SKAPOOR.PRODUCT_DIM P1,
　　 SKAPOOR.STORE_DIM S1
　WHERE
　　 P1.MODEL LIKE '%model%' AND
　　 F2.DATE_ID=D1.DATE_ID AND
　　 F2.PRODUCT_ID=P1.PRODUCT_ID AND
　　 F2.STORE_ID=S1.STORE_ID AND
　　 F2.DATE_ID BETWEEN '2006-11-01' AND '2006-11-30' AND
　　 F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND　　　　
　　 D1.MONTH=11
　GROUP BY
　　 S1.STORE_ID,S1.DISTRICT,S1.REGION,D1.MONTH)
SELECT
　　 A.*,
　　 B.*
FROM
　　 TMP1 A LEFT OUTER JOIN TMP2 B ON
　　　 (A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT)
ORDER BY A.AMOUNT_1 DESC, B.AMOUNT_11 DESC;

环境设置

本文的测试是使用以下环境执行的：

清单 3. db2levelDB2 9 Enterprise Edition:
DB21085I　Instance "DB2" uses "32" bits and DB2 code release "SQL09010" with
level identifier "02010107".
Informational tokens are "DB2 9.1.0.356", "s060629", "NT32", and Fix Pack "0".
Product is installed at "C:PROGRA~1IBMSQLLIB" with DB2 Copy Name "DB2COPY1".

清单 4. 操作系统System: WIN32_NT SKAPOOR Service Pack 2 5.1 x86 Family 15, model 4, stepping 3

清单 5. 硬件CPU: total:2 online:2 Cores per socket:1 Threading degree per core:2
Physical Memory(MB): total:2551 free:1988 available:1949
Virtual　Memory(MB): total:4950 free:6575
Swap　　 Memory(MB): total:2399 free:4587
1 Physical disk Size 100GB

空间需求

为了重新创建本文中描述的所有测试用例，需要高达 20Gb 的磁盘空间来存放数据和日志文件。其中将近 13Gb 的空间要分配给日志文件。我们要使用循环日志记录，分配 100 个主日志：

清单 6. 用于日志的数据库配置Log file size (4KB)　　　　　　　　　　　　 (LOGFILSIZ) = 8192
Number of primary log files　　　　　　　　(LOGPRIMARY) = 100
Number of secondary log files　　　　　　　 (LOGSECOND) = 150

略加修改为事实表填充数据的脚本，即可减少日志文件所需的磁盘空间。本文的后面将对此进行讨论。

设置数据库

第一步是创建一个测试数据库。

在本文的测试中，创建了 4 个逻辑数据分区。在 etcservices 文件中，应确保有足够的端口用于创建 4 个数据分区。在我们的测试环境中，文件 C:WINDOWSsystem32driversetcservices 中包含关于实例 "DB2" 的以下内容：

清单 7. services 文件的内容DB2_DB2　　　　　 60000/tcp
DB2_DB2_1　　　　 60001/tcp
DB2_DB2_2　　　　 60002/tcp
DB2_DB2_END　　　 60003/tcp
DB2c_DB2　　　　　50000/tcp

为向实例添加数据库分区，可使用 DB2 CLP 执行以下命令：

清单 8. 使用 db2ncrt 命令创建数据库分区db2stop
db2ncrt /n:1 /u:username,password /i:DB2 /m:machine /p:1
db2ncrt /n:2 /u:username,password /i:DB2 /m:machine /p:2
db2ncrt /n:3 /u:username,password /i:DB2 /m:machine /p:3

其中 /u 选项所表示的用户名和密码，/m 选项所表示的计算机名，以及 /i 选项所表示的实例名应该根据您自己的环境加以修改。

创建数据库

创建数据库 DSS_DB。这里使用 D: 盘存储该数据库。请根据您自己的环境进行调整。

清单 9. 创建数据库的命令db2 create database dss_db on D:;

数据库和数据库管理器是使用下面的设置来配置的。db2_all 工具用于设置所有数据库分区上的数据库配置和数据库管理器配置。

清单 10. 更新数据库管理器配置的语句db2_all update dbm cfg
　　 using cpuspeed 2.282997e-007 intra_parallel NO comm_bandwidth 100.000000
db2_all update db cfg for DSS_DB
　　 using locklist 2450 dft_degree 1 maxlocks 60
　　　　　 avg_appls 1 stmtheap 16384 dft_queryopt 5

创建数据库分区组和表空间

使用以下语句创建数据库分区组和表空间。可以将这些语句复制到一个名为 STORAGE.ddl 的文件中，然后使用下面的命令执行它们：

注意：表空间被定义为 "NO FILE SYSTEM CACHING"，以避免文件系统缓存歪曲测试各种方法时得到的评测结果。

使用 db2batch 工具评测性能

db2batch 程序用于运行 清单 2 中的主查询。为了使用 db2batch 命令运行该查询，需要将查询保存在一个以分号结尾的文件中，并使用以下选项，使 db2batch 工具查看计时情况：

清单 12. 使用 db2batch 评测查询的性能　　　　　　　　db2batch -d <dbname> -f <input_file> -i <elapsed_time> -iso <isolation level>
　　　　　　　　-o p <perf_detail> o <optlevel> r <rows_out> -r <result_file>

其中 <dbname> 是数据库名称，<input_file> 是以分号结尾、包含查询的文件。

-iso <isolation level>:

在我们的测试中，默认隔离级别是 CS，但是默认情况下 db2batch 工具使用隔离级别 RR。如果使用隔离级别 RR 执行一个查询，那么使用隔离级别 CS 创建的 MQT 不会被考虑。为了解决这个问题，可以在 db2batch 命令中使用 -iso 选项和隔离级别 CS，以便查询选择 MQT。而且，应用程序可使用默认的 CS 隔离级别，不带 -iso 选项运行 db2batch 会导致它使用 RR 隔离级别，并可能导致锁争用。

-o - options options:

p <perf_detail>: 性能详细信息。返回数据库管理器、数据库、应用程序和语句的快照（只有在自动提交关闭，且处理的是单个语句，而非语句块时，才返回语句快照）。另外还返回缓冲池、表空间和 FCM的快照（只有在多数据库分区环境中才会返回 FCM 快照）。 对于例子 p 5，我们使用最详细的输出，但是也可以使用不同级别的性能输出。

o <optlevel>: 查询优化级别。（本文使用优化级别 5，这里不需要显式地指定这个优化级别，因为它是数据库的默认优化级别，如 清单 10 所示。）

r <rows_out>: 所获取且将发送到输出的行数。我们的例子 r 0 不发送行。

-r <result_file>: 结果文件。在我们的例子中，results.txt 是输出文件名，db2batch 将结果输出到该文件中。

在本文中，我们使用：

提高查询性能的方法

在本节中，让我们逐步了解用于提高 清单 2 中描述的查询的性能的各种不同方法。在讨论任何方法之前，必须创建基本的事实表和维度表。

步骤 A：创建好表空间之后，就要创建事实表和维度表。可以将 SKAPOOR 改为符合您自己环境的模式名。这样做时，务必更新 清单 2 中的查询，以反映适当的模式名。可以将下面的语句复制到一个名为 TEST1.ddl 的文件中，然后使用以下命令来执行该文件：db2 -tvf TEST1.ddl -z test1.log

清单 13. TEST1.ddl 的内容CONNECT TO DSS_DB;
---------------------------------------------------
-- DDL Statements for table "SKAPOOR "."SALES_FACT"
---------------------------------------------------
CREATE TABLE "SKAPOOR "."SALES_FACT"　(
　　　　　"DATE_ID" DATE ,
　　　　　"PRODUCT_ID" INTEGER ,
　　　　　"STORE_ID" INTEGER ,
　　　　　"QUANTITY" INTEGER ,
　　　　　"PRICE" INTEGER ,
　　　　　"TRANSACTION_DETAILS" CHAR(100) )　
　　　　 DISTRIBUTE BY HASH("DATE_ID")　
　　　　　 IN "FACT_SMS" ;
-------------------------------------------------
-- DDL Statements for table "SKAPOOR "."DATE_DIM"
-------------------------------------------------
CREATE TABLE "SKAPOOR "."DATE_DIM"　(
　　　　　"DATE_ID" DATE NOT NULL ,
　　　　　"MONTH" INTEGER ,
　　　　　"QUARTER" INTEGER ,
　　　　　"YEAR" INTEGER )　
　　　　 IN "DATE_SMS" ;
-- DDL Statements for primary key on Table "SKAPOOR "."DATE_DIM"
-- DATE_ID is the unique identifier
ALTER TABLE "SKAPOOR "."DATE_DIM"
　　ADD PRIMARY KEY
　　　　("DATE_ID");
----------------------------------------------------
-- DDL Statements for table "SKAPOOR "."PRODUCT_DIM"
----------------------------------------------------
CREATE TABLE "SKAPOOR "."PRODUCT_DIM"　(
　　　　　"PRODUCT_ID" INTEGER NOT NULL ,
　　　　　"PRODUCT_DESC" CHAR(20) ,
　　　　　"MODEL" CHAR(10) ,
　　　　　"MAKE" CHAR(10) )　
　　　　 IN "PRODUCT_SMS" ;
-- DDL Statements for primary key on Table "SKAPOOR "."PRODUCT_DIM"
-- PRODUCT_ID is the unique identifier
ALTER TABLE "SKAPOOR "."PRODUCT_DIM"
　　ADD PRIMARY KEY
　　　　("PRODUCT_ID");
--------------------------------------------------
-- DDL Statements for table "SKAPOOR "."STORE_DIM"
--------------------------------------------------
CREATE TABLE "SKAPOOR "."STORE_DIM"　(
　　　　　"STORE_ID" INTEGER NOT NULL ,
　　　　　"LOCATION" CHAR(15) ,
　　　　　"DISTRICT" CHAR(15) ,
　　　　　"REGION" CHAR(15) )　
　　　　 IN "STORE_SMS" ;
-- DDL Statements for primary key on Table "SKAPOOR "."STORE_DIM"
-- STORE_ID is the unique identifier
ALTER TABLE "SKAPOOR "."STORE_DIM"
　　ADD PRIMARY KEY
　　　　("STORE_ID");
COMMIT WORK;
CONNECT RESET;

步骤 B：创建好表后，将数据插入到三个维度表中，并根据您自己的环境调整模式：

清单 14. 填充 DATE_DIM 表db2 -td@ -vf date_insert.txt -z date_insert.log

清单 15. 填充 PRODUCT_DIM 表db2 -td@ -vf product_insert.txt -z product_insert.log

清单 16. 填充 STORE_DIM 表db2 -td@ -vf store_insert.txt -z store_insert.log

这三个文件的内容是：

DATE_DIM 表被填入 2006 年所有 365 天的值。

清单 17. date_insert.txt 的内容connect to dss_db@
begin atomic
　declare cnt INT default 1;
　declare dat DATE default '01/01/2006';
　declare yer INT default 2006;
　declare quart INT default 1;
while (cnt <= 365) do　　
　　if (int(dat + cnt DAYS)/100) between 200601 and 200603 then
　　　　　 set quart=1;
　　elseif (int(dat + cnt DAYS)/100) between 200604 and 200606 then
　　　　　 set quart=2;
　　elseif (int(dat + cnt DAYS)/100) between 200607 and 200609 then
　　　　　 set quart=3;
　　elseif (int(dat + cnt DAYS)/100) between 200610 and 200612 then
　　　　　 set quart=4;　　
　　end if;
　　
　　insert into SKAPOOR.DATE_DIM values (
　　dat + cnt DAYS,
　　(int(dat + cnt DAYS)/100) - 200600,
　　quart,
　　yer
　　);
　　　
　　set cnt=cnt+1;
end while;
end@
connect reset@

PRODUCT_DIM 表被填入 60,000 种产品。

清单 18. product_insert.txt 的内容connect to dss_db@
drop sequence seq1@
drop sequence seq2@
create sequence seq1 as integer start with 1 increment by 1@
create sequence seq2 as integer start with 1 increment by 1@
begin atomic
　 declare cnt INT default 1;
　 while (cnt < 60001) do
　　insert into SKAPOOR.PRODUCT_DIM values (
　　　　nextval for SEQ2,
　　　　'product desc' concat char(nextval for SEQ1),
　　　　　　'model ' concat char(integer(rand()*1000)),
　　　　'maker ' concat char(integer(rand()*500))
　　);
　　set cnt=cnt+1;
　　　　end while;
end@
drop sequence seq1@
drop sequence seq2@
connect reset@

STORE_DIM 表被填入 201 家商店。

清单 19. store_insert.txt 的内容connect to dss_db@
drop sequence seq2@
create sequence seq2 as integer start with 0 increment by 1@
begin atomic
　　declare cnt INT default 1;
　　while (cnt < 202) do
　　　 insert into SKAPOOR.STORE_DIM values (
　　　　nextval for SEQ2,
　　　　'location' concat char(integer(rand()*500)),
　　　　'district' concat char(integer(rand()*10)),
　　　　'region' concat char(integer(rand()*5))
　　　 );
　　　 set cnt=cnt+1;
　　end while;
end@
drop sequence seq2@
connect reset@

步骤 C：将数据插入到 SALES_FACT 表中。根据您自己的环境调整模式。在我们的测试环境中，将数据插入到事实表花了约一个半小时的时间。

清单 20. 填充 SALES_FACT 表db2 -td@ -vf sales_fact_insert.ddl -z sales_fact_insert.log

清单 21. sales_fact_insert.ddl 的内容connect to dss_db@
VALUES (CURRENT TIMESTAMP)@
begin atomic
　 declare cnt INT default 1;
　 declare cnt1 INT default 1;
　 declare dat DATE default '01/01/2006';
　 while (cnt <= 365) do　　
　　
　　INSERT INTO SKAPOOR.SALES_FACT
　　with v(DATE_ID, PRODUCT_ID, STORE_ID, QUANTITY, PRICE, TRANSACTION_DETAILS, U_ID) as
　　　　 (values(dat + CNT1 DAYS, 1, 1, 1, 1, '', 1) union all
　　　　　select DATE_ID, int(rand()*59999) + 1, int(rand()*200),　int(rand()*50) + 1,
　　　　　INT(RAND()*200 + 1), RESERVE, U_ID + 1
　　　　　from　 v
　　　　　where　U_ID < 60000)
　　 select date_id, product_id, store_id, quantity, price, transaction_details from v;
　　 set cnt1 = cnt1 + 1;
　　 set cnt　= cnt + 1;
　 end while;
end@
VALUES (CURRENT TIMESTAMP)@
connect reset@

注意：在 清单 21 中，SALES_FACT 表是在一次事务处理中填充的，这需要大量的磁盘空间来作日志记录。为了降低日志记录的影响，可以创建一个存储过程，并分步提交插入内容：

清单 22. 填充 SALES_FACT 表的另一种方法connect to dss_db@
VALUES (CURRENT TIMESTAMP)@
-- Create a procedure to populate the SALES_FACT table
-- committing the inserts in stages to reduce the impact
-- of logging
create procedure salesFactPopulate()
specific salesFactPopulate
language sql
begin
　 declare cnt INT default 1;
　 declare cnt1 INT default 1;
　 declare dat DATE default '01/01/2006';
　 while (cnt <= 365) do　　
　　
　　INSERT INTO SKAPOOR.SALES_FACT
　　with v(DATE_ID, PRODUCT_ID, STORE_ID, QUANTITY, PRICE, TRANSACTION_DETAILS, U_ID) as
　　(
　　　 values(dat + CNT1 DAYS, 1, 1, 1, 1, '', 1)
　　　union all
　　　 select DATE_ID, int(rand()*59999) + 1, int(rand()*200),　int(rand()*50) + 1,
　　　　　　　INT(RAND()*200 + 1), RESERVE, U_ID + 1
　　　　 from v
　　　　where U_ID < 60000
　　)
　　select date_id, product_id, store_id, quantity, price, TRANSACTION_DETAILS from v;
　　commit work;
　　set cnt1 = cnt1 + 1;
　　set cnt=cnt+1;
　 end while;
end@
-- populate the SALES_FACT table
invoke salesFactPopulate@
VALUES (CURRENT TIMESTAMP)@
connect reset@

步骤 D：为了理解各种不同的方法对所选查询访问计划的有怎样的影响，我们需要解释（Explain）查询，以查看 DB2 查询优化器选择的访问计划。为此，可使用 EXPLAIN 工具，这要求存在 EXPLAIN 表。为了创建 EXPLAIN 表，执行以下步骤：

1、进入 sqllibmisc 目录所在的位置。

2、在我们的测试环境中，这个位置为 "C:Program FilesIBMSQLLIBMISC"。

3、执行 db2 connect to dss_db。

4、执行 db2 -tvf EXPLAIN .DDL。