简介：本文从数据仓库物理设计的角度，分析了显著影响查询性能的
三项关键技术，即分区数据库，表分区和多维集群（MDC）。文章首先分析三项技术 在提升查询性能方面的理论依据，然后进行实例演示。实例演示采用
IBM BCU 设计 架构，以基准测试 TPC-H 为数据源（300GB 数据量）和测试案例，展示了“三驾马车”
对查询性能的拉动效果。无论是在 POC 测试还是在现实生产系统中，查询性能都是 客户非常关注的重要指标。通过本文，读者可以充分了解“三驾马车”的奥秘所在，
文中的实例演示对读者有借鉴和参考意义。
前言
在数据仓库领域中，无论是在生产系统中，还是 POC（Proof Of Concept）
性能测试，查询性能对于客户来说都是非常重要的性能指标。良好的查询性能 为各类数据仓库应用的高效作业奠定了基础。而对于查询性能来说，众所周知，
其主要性能瓶颈来自于系统 I/O，因此本文从数据仓库物理设计的角度出发， 阐述了影响查询性能的三项关键技术，并以基准测试
TPC-H 进行了实例演示， 展示性能提升的效果。
关于分区数据库，表分区和多维集群（MDC），developerWorks
上已经有 很多优秀的文章对其基本原理和特点分别进行了阐述，本文将不再赘述。本文 重点关注该三项技术在物理设计方面对查询性能的影响。
理论依据
分区数据库（Database Partitioning Feature）
分区数据库中的 Share-Nothing 架构，将繁重而又耗时的系统
I/O 作业平均分配到集群中的各个节点，结合 SAN（Storage Area Network）存储 网络，能够充分利用磁盘控制器的
I/O 性能以及存储网络的带宽。
为了能够平衡各节点的 I/O 繁忙程度，均衡的数据分布，就显得尤为重要。
数据的分布情况取决于数据本身以及数据库分区键的选择，数据库分区键的 选择应遵循以下原则：
1. 唯一数值较多、较分散的列；
2. 经常用于联结（JOIN）的列；
在数据均衡分布的情况下 , 才能避免某一节点因处理过多数据造成 I/O
过度 繁忙从而成为整个集群的瓶颈。
在单机数据库环境下，查询的处理只能利用单机中的系统资源（CPU， Memory，I/O），当数据存储在单张大表时，BI（Business
Intelligence） 查询通常需要访问表中的大部分数据，如对于查询 sql1 来说，在单机环境下，
数据的物理分布如图 1 所示。在没有创建索引的情况下，数据库需要扫描整张 大表来查询符合条件的记录，不难想象扫描大表所需要的繁忙
I/O 对查询整体 性能的影响。
清单 1. 查询 sql1
SELECT C_NAME, C_TOTAL_SPEND, C_LOYALTY_TIER from CUSTOMER
where C_REGION = ‘ North America ’ AND C_MONTH= ‘ March ’ AND C_TYPE= ‘ VIP ’ |
（注：蓝色三角形代表符合查询条件的数据，即 C_REGION= ‘ North
America ’ AND C_MONTH= ‘ March ’ AND C_TYPE= ‘ VIP ’）
图 1. 单机数据库环境中数据的物理分布
采用多分区数据库，可以将数据均衡地分布于集群中的各节点，虽然 BI 查询
仍需要扫描整张大表、读取大部分数据，但查询可以并行到所有节点，如图 2 所示。
图 2. 分区数据库环境中数据的物理分布
表分区（Range Partitioning） Partitioning
Feature）
在表分区中，用户可以根据实际需要指定特定列，DB2 根据列值 将表数据划分为一个个的数据分区。在物理上，数据库将一个数据分区内的
数据页存储在一段连续的磁盘空间上。正是因为这样的物理设计，数据库在 查询某一个或某几个数据分区时，磁盘控制器的
I/O 操作是顺序读取，与随机 读取相比，减少了 I/O 操作次数，同时提高了每次 I/O 操作的吞吐。毫无疑问，
这将大大提升整体 I/O 性能。
在分区数据库的基础上使用表分区，所有相同范围内的数据在物理上落在 同一数据分区中，对于
BI 查询，数据库仅读取适合分区的数据，减少了查询 所需的 I/O，如图 3 所示。
图 3. 分区数据库中采用表分区后的数据物理分布
多维集群 （Multi-dimensional Clustering）
在多维集群中，数据记录根据维度值被分布到不同的 Cell 中， 每个 Cell
包含多个 Block。每个 Cell 中的 Block 是磁盘上一段连续的存储空间， 包含一组连续的数据页，Block
中存储具有相同维度值的数据记录，Block 大小 由表空间的 Extent 大小决定。通过下面这个简单的例子，来对多维集群中的
物理存储进行阐述。
表 T1 按照 C1 列进行聚集，每个 Block 中存储的数据记录具有相同的
C1 值。 表结构和加载数据如下所示。
清单 2. 表结构及加载数据
CREATE TABLE T1 (C1 VARCHAR(10), C2 INT, C3 INT) ORGANIZE BY C1
C1 C2 C3
------- ----- -----
aaaaaaa 11 9
bbb 22 10
cccccc 33 11
dddd 44 5
bbb 22 7 |
那么，数据在磁盘中的存储为下图 4 所示。
图 4. 多维集群数据存储简单示例
在加载第 1 条数据时，数据库首先查看是否已经有适合的、未填满的 Block
可供存储，在本例中，因为该记录是第一条具有 C1 值为 aaaaaaa 的记录，所以 数据库为维度值为
aaaaaaa 的数据记录新分配一个 Block；同理，对于第 2,3,4 条记录，因为都没有同样维度值的
Block 存在，数据库为维度值为 bbb，cccccc， dddd 的数据记录分别分配 1 个 Block；在处理第
5 条记录时，因为维度值为 bbb 的 Block 已经存在且未填满，所以数据库直接将第 5 条记录存储到已有的
Block2 中。 随着数据的后续加载，如果 Block2 被填满，当有新的维度值为 bbb 的记录插入时，
数据库将为其分配新的 Block。
通过多维集群，数据进一步根据属性聚集，数据库只需要扫描符合条件的 Block，从而进一步减少
I/O。因为 Block 中存储的是具有相同维度值的连续数据页， I/O 的效率也会更高。对于查询 sql1，在分区数据库下使用分区表，结合多维集群，
使得 BI 查询只需访问合适数据分区中符合条件的 Block，进一步减小了 I/O 作业量， 如图 5
所示。
图 5. 分区数据库中采用表分区结合多维集群后的数据物理分布
实例演示
硬件环境
硬件环境采用 IBM BCU（Balanced Configuration
Unit）设计架构，保证 每个 Data module 中的系统资源（CPU，Memory，I/O，Network）均衡配比，
如图 6 所示。每个 Data module 包含一台 IBM POWER 740 节点和三台 IBM
System Storage DS3524 外部存储，其中 POWER 740 包含的资源为 2 X 8
core 3.55 GHz CPU，4 X 32GB 内存以及一张 800GB PCIe SSD（Solid
State Drives）RAID 适配卡；每台 DS3524 存储包含 24 X 300GB 10,000
rpm SAS 磁盘，具有两个配置 相同的 RAID 控制器，以及 2GB Cache 缓存。SAN40B
光线交换机用于联结 POWER 740 节点和存储设备，从而形成 SAN（Storage Area Network）存储网络，交换
机端口速率为 8Gbps（对应的 POWER 740 接口和存储设备接口速率亦为 8Gbps）。 Juniper
EX4500 为 40 端口以太网光纤交换机，端口速率为 10Gbps（对应的 POWER 740 接口和存储设备接口速率亦为
10Gbps），用于分区数据库环境中不同节点 分区间通信，即 FCM（Fast Communication
Manager）通信。
图 6. 实例演示硬件架构
存储设计
在每个 DS3524 存储上，共划分 4 个磁盘阵列，每个阵列包含 6
块磁盘，阵列 类型为 RAID6，即 4+P+Q。因此对于每个 Data module，共有 3 个 DS3524
存储， 共 12 个 RAID6 磁盘阵列。
分区数据库环境
每个 Data module 创建 12 个数据库分区，共 24 个数据库分区。每个数据库分区
的物理资源分配情况如下表 1 所示。操作系统为 AIX 6.1，DB2 版本为 V9.7 FP6。 由表
1 可见，分区数据库设计符合 IBM BCU 架构规范。
表 1. 数据库分区物理资源配比
CPU |
Memory
|
Disks
|
1 |
8
GB |
6
(4+P+Q) |
测试场景
本文以基准测试 TPC-H 300GB 数据为数据源（通过 TPC-H
数据生成程序 dbgen – s 300 获得），对比数据库物理设计调优前后 TPC-H 查询（Q6,Q7,Q14,Q15,Q19,
Q20）的性能变化，来展示“三驾马车”对于查询性能拉升的效果。300GB 数据分 布情况如下表 2 所示。
表 2. TPC-H 300GB 数据源分布
测试方法
1. 在分区数据库环境中，采用 TPC-H 原始表结构，对 8 张表进行数据加载，
加载完成后，分别进行查询 Q6,Q7,Q14,Q15,Q19,Q20，并记录查询响应时间 以及系统 I/O
状态；
2. 在分区数据库环境中，对适合的表进行表分区设计和多维集群设计， 之后进行数据加载；加载完成后，分别进行查询
Q6,Q7,Q14,Q15,Q19,Q20， 并记录查询响应时间以及系统 I/O 状态；
3. 对比 1、2 中查询结果，并对比同一查询在两种物理设计中的性能差异。
表分区与多维集群设计
表分区的关键在于分区键的选择，通常选择在查询中出现频率最高、数据类型 为时间的列作为分区键。多维集群的设计关键在于确定维度、维度数量、维度粒度
以及 Block 大小，从而保证在充分利用多维集群提升性能的同时，尽量节省存储空间。 本文实例中的表设计如下表
3 所示：
表 3. TPC-H 表分区与多维集群设计
以 LINEITEM 表为例，对该表的设计进行阐述。LINEITEM 表根据
L_SHIPDATE 列 对数据进行分区，L_SHIPDATE 列年限为 1992-1998，以 1 年为间隔，因此共有
7 个 数据分区，每个数据分区中的数据记录具有相同的 L_SHIPDATE 年限。同时该表 根据列 L_QUANTITY,
L_SHIPMODE, L_SHIPINSTRUCT 进行聚簇，关于多维集群的 设计，如下表 4 所示。由表
4 可见，在数据加载完成后，表中共有 84 个 Cell， 每个 Cell 包含 44236 个 Block，每个
Block 中的数据记录具有相同的维度值。需要 注意的是，在数据加载完成后，会有若干 Block 没有填满，即留有空置的数据页，
而空置的数据页就造成了存储空间上的浪费，所以在 MDC 设计过程中，需要将 空间浪费因素考虑进去，不应为了单方面提升性能而造成过度的存储空间浪费。
在本例中，存储空间浪费百分比为 3.5%，在可以接纳的范围内。
表 4. LINEITEM 表 MDC 设计
测试结果
测试结果如表 5 所示 , 为了避免测试过程中可能产生的系统误差 , 本文进行了
两轮测试 , 从而确保测试结果的一致性和可对比性。
表 5. 查询响应时间结果对比
测试结果分析
以 Q19 为例，对比设计前后系统 I/O 消耗情况，如图 7，8 所示，分别为设计
前 Data module1 和 Data module2 的系统 I/O。
图 7. 设计前 Data module1
系统 I/O
图 8. 设计前 Data module2
系统 I/O
对比设计后 Data module1 和 Data module2 的系统
I/O 情况，如图 9，10 所示。
图 9. 设计后 Data module1
系统 I/O
图 10. 设计后 Data module2
系统 I/O
通过对比设计前后系统 I/O，不难发现，设计前每秒 I/O 请求的数量级为
2X103， 每秒 I/O 吞吐为 1.5X105 Kbps；而设计后每秒 I/O 请求的数量级为 5X102，每秒
I/O 吞吐为 3X104 Kbps。显而易见，设计后的系统 I/O 是设计前的 1/20~1/100
倍，因而 大大改善了查询的整体性能。
（注：因篇幅有限，其他查询不再做如上分析，读者可以依据以上分析类比）
读者可以使用本文下面附件中的测试脚本在自己的环境中进行测试对比，测试 结果也一并附在附件中。
|
