DB2数据仓库优化艺术

在使用DB2数据仓库的过程中，经常会遇到性能问题，这些性能问题与OLTP环境相比有什么区别？那么如何设定优化目标？如何从物理和逻辑上分别进行调优？

我们着重回答上述问题。

1.  与OLTP环境的区别

DB2 数据仓库环境与OLTP环境中的性能调优不一样，主要表现在以下几个方面：

l       事务长短。OLTP 环境中的事务通常是短事务，包含多条SQL 语句，这些语句常常可以在数秒内完成；数据仓库环境中，往往是长事务，只执行少数几条SQL 语句，但是却需要运行几分钟，甚至几小时。

l       数据更新时间不同。在OLTP环境中，数据更新操作随时都在发生；对于数据仓库应用，数据通常在夜间进行更新，往往需要执行大量提取、转换和装载 (ETL) 操作。

l       结果集大小不同。OLTP 环境中的查询语句通常只获取少量数据行；数据仓库查询，尤其是那些用来生成报表或进行多维分析的查询可能返回上百万行数据。

l       查询复杂度不同。OLTP环境中的查询语句比较简单，通常只访问少数几个表；但是，数据库仓库环境中的查询语句比较复杂，可能包含十几个甚至更多表的关联、包含嵌套的或通用的表表达式、递归SQL、CASE 表达式等。

l       SQL语句的生成方式不同。OLTP环境，通常是自己编写SQL语句；在数据仓库环境中，常常由OLAP 工具生成 SQL语句，在执行之前很少有机会修改它，DBA负责设置适当的DB2运行环境，让这些查询能够高效运行。

2.  设定目标

在进行调优时，不但要追求良好的性能，还要确保方向正确。如果一个查询运行时间很长，但是用户觉得可以接受，那么这个查询也不一定需要优化；相反，一个频繁执行的查询，由于用户期望它能在一秒内完成，那么即使它的运行时间只有 10 秒，也会引起用户的抱怨。

对于判断什么地方需要调优，最好听听用户的声音，优先解决用户最关注的查询；如果用户没有什么抱怨，那么可以花时间调整那些运行时间长和执行频率高的查询。针对这些查询，要评估它们的访问路径，需要进行全表扫描吗？要使用索引吗？如果要连接表，使用哪种连接方法？

为了正确地评估这些访问路径，需要从DB2编目中获取统计信息。如果编目中的统计信息不准确，需要使用 RUNSTATS 进行更新。在我看来，DB2 具有市场上最好的查询优化器，但是它必须有足够的统计信息，才能做出合理的访问路径决策。最好的实现方法是定期运行 RUNSTATS或者让DB2自动收集统计信息。

有关如何解决查询慢的问题，请读者阅读第九章的相关内容。

3.  物理调优

建立有助于提高查询性能的 DB2 数据仓库环境，物理上应该从CPU、存储和内存方面加以调整：

1.     利用多核CPU并行性

我们从10.2.1节了解到，DB2通常运行在SMP服务器和MPP服务器集群环境，针对这两种环境，要发挥多核CPU的并行能力来提升查询性能。

在在SMP服务器环境上，一颗CPU通常都有多个内核，DB2 可以把处理查询所需的工作分割为片段，并行地执行这些片段，这会显著减少查询时间。通过把数据库管理程序参数 intra_parallel 的值设置为 YES，就可以启用SMP服务器的查询并行性。

     除了能够在SMP服务器中并行查询之外，DB2 还可以通过MPP服务器集群来 提升查询性能，读者可以按照10.2节有关内容做好MPP环境下的配置工作。

2.     做好存储规划

关于存储的设计，原则是保证每一个数据BPU都有属于自己的一组物理磁盘，并且保证数据仓库中的所有数据对象都均匀地分布在这些磁盘上。这种设计模式，可以保证每个BPU中的所有数据对象，都会平衡地进行I/O操作。

为了避免数据和日志读写冲突，数据和日志应该放在不同的磁盘上。此外，把主日志文件和镜像日志文件存放在不同的磁盘上，这样当某个磁盘出现故障时还有另外一份日志文件可以使用。

3.     合理配置缓冲池

对于 I/O 密集型的数据仓库工作负载而言，缓冲池越大，性能通常就越好，这是由于随着缓冲池大小的增加，磁盘读 I/O 活动的数量会减少。

但是对32位系统而言，内存空间限制为4GB，为了能够使用更多的内存，建议优先使用64位系统。如果64位的服务器有大量内存资源，就应该多分配内存资源供 DB2数据仓库使用。我曾经见过在有32GB 系统内存的服务器上，只给DB2配置了 800MB 的缓冲池，这太小了，配置10-20GB比较合适。

4.  逻辑调优

逻辑上，我们要多利用DB2提供的分区、索引、MDC、MQT等特性来提升性能，具体的优化建议如下：

1.     对大表进行分区。

     对于哪些表应该进行分区，并没有绝对的规则，例如，可以对包含一百万或更多          行的大表进行分区。为表选择分区键，可以是单一列，也可以包含多列，这取决于需         要，例如我们可以选择日期列作为分区键。     

2.     利用索引。

对于OLTP 环境，索引会使得Insert、Update和Delete操作变慢，所以在创建索引方面往往非常保守，通常建议索引的个数为3个到5个。

                 与 OLTP 数据库相比，在数据仓库中绝大多数操作是查询，所以创建更多索引通常是有意义的，通常建议5个到10个。但是，也不要为数据仓库表创建过多的索引，否则会影响ETL 数据更新的性能。

3.     利用MDC特性。

数据仓库环境中，常常会大批地获取行，例如获取某客户在过去5年在某区域所有的销售记录？这种情况就可以使用DB2提供的多维聚簇MDC 特性，从而可以大批量地获取具有相同维度值的行。

4.     使用物化查询表 (MQT) 功能。

MQT 是通过查询语句定义的一个结果集，这样就不需要在执行查询时动态地构建这个结果集。MQT 有三个优点：

l         DB2 可以自动地重写查询以使用 MQT。

l         MQT中的结果集已经建立了，经过DB2重写访问这个MQT的查询不必在查询执行时花时间动态地构建结果集，这会显著降低运行时间。

l         可以在 MQT 上定义索引，这会进一步提高查询性能。

 

在数据仓库环境中，一些常用的复杂报表或者分析应用，可以使用MQT来提升性能。