数据仓库中拉动查询性能的三驾马车

来源：互联网发布：js循环table tr 编辑：程序博客网时间：2024/06/02 11:30

From : http://www.ibm.com/developerworks/cn/data/library/techarticle/dm-1303wulei/

前言

在数据仓库领域中，无论是在生产系统中，还是 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 所示。