Maksym Petrenko, DB2 仓库集成专家, IBM

Paul McInerney, 以用户为中心的设计专家, DB2 Development, IBM

简介： 选择恰当的分区键对于具有 Database Partitioning Feature (DPF) 的 IBM® DB2® Enterprise Server Edition for Linux®, UNIX®, and Windows® 的最佳查询性能十分重要。为了帮助此任务，本文提供了一些新的例程来估算已有的以及新的分区键的数据倾斜。本文还详细给出了一些最佳实践并展示了如何更改一个分区键而同时又能保持表的可访问性。

本文做了如下假设：

您具有一个 DB2 DPF 环境并且熟悉 DB2 DPF 的概念。
您正在设计一个将被哈希分区的新表，或者您已经有了一个哈希分区的表，并且此表有可能会存在数据倾斜问题。

本文将帮助您实现如下任务：

在定义和填充一个表之前，选择正确的初始分区键（PK）
评估表上已有 PK 的质量
评估已有表上的候选替换 PK 的质量
在保持表在线的情况下，更改此 PK

本文提供了如下这类帮助：

回顾概念和关注点
设计准则
新例程用来评估已有的和新的分区键的数据倾斜

哈希分区的快速浏览

在 DPF 环境中，大型表一般要跨多个数据库分区。对表进行分区的方法有几种，但本文只着重于哈希方式的分区。有关其他的分区方式，请参考参考资料部分给出的文章。

哈希分区基于的是分区键。一个分区键由在表创建时定义的一个或多个列组成。对于每个新插入的记录，分区键会决定这个记录应该存储在哪个数据库分区。这种安置是由一个内部的哈希函数决定的，该函数接受定义为分区键的列内的值并返回数据库分区号。哈希函数是一个确定性 函数，这意味着对于相同的分区键值，它总是假设对数据库分区组的定义没有更改并总是会生成相同的分区安置。

如下的语法示例展示了创建一个哈希分区表所需步骤：

创建一个数据库分区组来指定要参加分区的那些数据库分区。如下的例子展示了如何在数据库分区 1、2、 3 和 4 上创建一个数据库分区组 PDPG：
CREATE DATABASE PARTITION GROUP pdpg1 ON DBPARTITIONNUMS(1 to 4)
根据 IBM Smart Analytics System 和 IBM InfoSphere™ Balanced Warehouse 的最佳实践，被哈希分区的表应该在 coordinator 或 administration 分区（数据库分区 0）上创建。数据库分区 0 通常用于存储小型的非分区的查找表。
创建数据库分区组内的表空间。在此表空间内创建的所有对象都将跨在数据库分区组定义内指定的这些数据库分区：
CREATE TABLESPACE tbsp1 IN pdpg1 ...
在表空间内创建表。至此，此表的定义与数据库分区组的定义相关联。更改此关系的惟一方式是丢弃此表并在与不同的数据库分区组相关联的一个不同的表空间内重新创建它。
在如下的例子中，Table1 创建于数据库分区 1、2、3 和 4 并且会基于列 COL1 上的一个分区键重新分配：
CREATE TABLE table1(col1 INTEGER NOT NULL, col2 SMALLINT NOT NULL, col3 CHAR(10), PRIMARY KEY (col1,col2) ) IN tbsp1 DISTRIBUTE BY HASH (col1)

请记住，数据库分区组定义可以更改。比如，可以添加新的数据库分区。如果发生更改，那么在此次更改之前定义的哈希分区表将不会使用这个新的分区，直到此数据库分区组被 REDISTRIBUTE DATABASE PARTITION GROUP 命令重新分配。

定义分区键

分区键由 CREATE TABLE 命令内的 DISTRIBUTED BY HASH 子句定义。分区键定义后，就不能更改。更改它的惟一办法是重新创建此表。

如下的规则和建议适用于分区键定义：

表的主键和所有惟一索引都必须是相关分区键的超集。换句话说，作为分区键一部分的所有列都必须出现在主键或惟一索引定义中。列的顺序可任意。
一个分区键应该包括一至三个列。通常列越少越好。
整型分区键要比字符键高效，而字符键又比小数键高效。
如果在 CREATE TABLE 命令内没有显式地提供分区键，那么就会使用如下的这些默认值：
- 如果在 CREATE TABLE 语句中指定了一个主键，那么主键的首列会被用作分配键。
- 如果没有主键，就会使用非长型字段的首列。

为何选择正确的分区键如此重要

选择正确的分区键之所以如此关键，有两方面的原因：

它改善了使用哈希分区的那些查询的性能
它平衡了所有分区的存储需求

数据平衡

数据平衡指的是存储在各个数据库分区的记录的相对数量。理想情况下，一个哈希分区表内的每个数据库分区都应具有相同数量的记录。如果各数据库分区存储的记录数不均，就会导致不均衡的存储需求和性能问题。之所以会出现性能问题是因为查询均独立在每个数据库分区完成，但是查询的结果则要由协调代理进行整合，而该代理必须等所有数据库分区均返回结果集后才开始整合。换言之，整体的性能受制于最慢的数据库分区的性能。

表数据倾斜 指的是特定的一些数据库分区上的某个表内的记录数与这个表所跨的所有数据库分区的平均记录数之间的差额。所以，对于本例，如果某个表在数据库分区 1 上的表数据倾斜是 60%，那么这意味着此数据库分区包含的该表的行要比平均的数据库分区多出 60%。

从最佳实践的角度来看，各个数据库分区上的表数据倾斜应该不多于 10%。为了实现此目标，分区键应该在具有较高基数（换言之，即包含大量不同值）的列上选择。

如果表的统计信息是最新的，那么就可以通过如下语句快速检查现有表内列的基数：

				
SELECT colname, colcard FROM syscat.columns
WHERE tabname='CUSTOMER' AND tabschema = 'BCULINUX' ORDER BY colno


COLNAME                                                                  COLCARD
------------------------------------------------------------------------ ---------------
C_CUSTOMER_SK                                                                     100272
C_CUSTOMER_ID                                                                      25068
C_CURRENT_CDEMO_SK                                                                 25068
C_CURRENT_HDEMO_SK                                                                  6912
C_CURRENT_ADDR_SK                                                                  19456
C_FIRST_SHIPTO_DATE_SK                                                              3651
C_FIRST_SALES_DATE_SK                                                               3584
... [remainder of the output omitted from this example]

并置

一个查询内的两个合并表之间的并置意味着两个表的匹配行将总是处于相同的数据库分区内。如果这种合并不加以并置，数据库管理器就必须通过网络将记录从一个数据库分区运送至另一个分区，这样一来，就会导致性能的不甚理想。为了数据库管理器能够使用并置合并，有一些条件必须要满足：

被合并的表必须在相同的数据库分区组内定义。
每个被合并的表的分区键都必须匹配。换言之，它们必须包含相同数量和顺序的列。
对于被合并的表的分区键内的每列，必须存在一个同等连接的谓语。

如果基于查询工作负载选择一个分区键，那么此分区键通常应该包含一个合并列或常被用于很多查询的一组列。

虽然经并置的表通常都会获得最健壮的性能，但是在实际中，不太可能对所有表都进行并置。此外，基于为数不多的 SQL 语句选择分区键也不是一个好的做法。在决策支持的环境中，查询通常无法预测。在这种环境下，应该查看数据模型来决定分区键的最佳选择。这个数据模型以及表间的业务关系可以提供一种比 SQL 语句更为稳定的选择分区键的方式。

在选择分区键时，可以画出一个数据模型，用来显示数据库内的这些表之间的关系。标示出频繁合并和常用表。基于数据模型，选择那些有利于频繁合并且基于主键的分区键。理想情况下，还应该并置频繁合并的表。改善合并并置的另一个策略是复制每个数据库分区上的较小的表。

并置与数据平衡间的对比

在某些情况下，您可能会发现根据并置和数据平衡选择恰当的分区键的准则会相互矛盾。在这些情况下，建议您基于数据平衡选择分区键。

验证现有表上的分区键

如果想要验证分区键的好坏，可以查看工作负载内查询是否已经被并置以及数据是否已经被很好地平衡。随着时间的推移，因数据的改变，旧的分区键有可能会不如之前那么好。可以通过查看由 DB2 Explain 生成的访问计划来检查查询合并的并置情况。如果查询没有经过并置，通常会看到 TQUEUE（表查询）操作符送到这个合并，如图 1 所示：

解释输出显示了查询在 NLJOIN 之前先通过了 TQUEUE 操作符

为了检查表内的数据是否跨数据库分区进行了适当的均衡，可以借助 DBPARTITIONNUM 函数在按此数据库分区 ID 分组的表上运行一个简单的计数。

也可以使用定制存储过程 ESTIMATE_EXISTING_DATA_SKEW 例程（在下载部分可以找到），它提供了更为用户友好的输出，其中包括一个数据库分区列表、相对于平均值的倾斜百分比等。这个例程可以在原始数据的样本上运行以获得更快速的性能。（参见附录获得完整的例程描述。）

若计划在一个生产环境内运行此例程，可以考虑在维护窗口期间或是当系统处于轻负载的情况下运行它。为了估算此例程需要花费多久才能返回结果，不妨在一个较小的表上用示例值 1% 尝试此例程。

示例 1

这个示例测试的是分区键被更改为 S_NATIONKEY 的场景中的数据倾斜。这个示例只使用了样本中 25% 的数据。正如从结果中看到的，数据大量倾斜，某些数据库分区内的数据量居然有 60% 的倾斜。

				
$ db2 "set serveroutput on"
$ db2 "CALL estimate_existing_data_skew('TPCD', 'SUPPLIER', 25)"
CALL estimate_existing_data_skew('TPCD', 'SUPPLIER', 25)

  Return Status = 0

DATA SKEW ESTIMATION REPORT FOR TABLE: TPCD.SUPPLIER
Accuracy is based on 25% sample of data
------------------------------------------------------------------------
TPCD.SUPPLIER
Estimated total number of records in the table: : 19,994,960
Estimated average number of records per partition : 2,499,368

Row count at partition 1 : 1,599,376 (Skew: -36.00%)
Row count at partition 2 : 2,402,472 (Skew: 3.87%)
Row count at partition 3 : 4,001,716 (Skew: 60.10%)
Row count at partition 4 : 2,394,468 (Skew: -4.19%)
Row count at partition 5 : 1,600,028 (Skew: -35.98%)
Row count at partition 6 : 1,599,296 (Skew: -36.01%)
Row count at partition 7 : 2,397,116 (Skew: -4.09%)
Row count at partition 8 : 4,000,488 (Skew: 60.05%)

Number of partitions: 8 (1, 2, 3, 4, 5, 6, 7, 8)
------------------------------------------------------------------------

Total execution time: 20 seconds

示例 2

这个示例展示了通配符在 ESTIMATE_EXISTING_DATA_SKEW 例程中的使用。清单 3 报告了具有模式 TPCD 且表名以 “PART” 开头的所有表的现有数据倾斜。由于这些表相对比较大，这个例子构建于 1% 的数据以减少性能的损失。

清单 3. 测量多个表的现有数据倾斜

				
$ db2 "set serveroutput on"
$ db2 "CALL estimate_existing_data_skew('TPCD', 'PART%', 1)"
CALL estimate_existing_data_skew('TPCD', 'PART%', 1)

  Return Status = 0

DATA SKEW ESTIMATION REPORT FOR TABLE: TPCD.PART%
This report is based on the existing partitioning key
Accuracy is based on 1% sample of data
------------------------------------------------------------------------
TPCD.PART
Estimated total number of records in the table: : 399,799,400
Estimated average number of records per partition : 49,974,900

Row count at partition 1 : 50,051,800 (Skew: 0.15%)
Row count at partition 2 : 49,951,200 (Skew: -0.04%)
Row count at partition 3 : 49,862,500 (Skew: -0.22%)
Row count at partition 4 : 49,986,500 (Skew: -0.02%)
Row count at partition 5 : 50,096,400 (Skew: 0.24%)
Row count at partition 6 : 49,993,900 (Skew: -0.03%)
Row count at partition 7 : 49,955,900 (Skew: -0.03%)
Row count at partition 8 : 49,901,200 (Skew: -0.14%)

Number of partitions: 8 (1, 2, 3, 4, 5, 6, 7, 8)
------------------------------------------------------------------------
TPCD.PARTSUPP
Estimated total number of records in the table: : 1,600,374,100
Estimated average number of records per partition : 200,046,700

Row count at partition 1 : 200,298,100 (Skew: 0.12%)
Row count at partition 2 : 200,154,900 (Skew: 0.05%)
Row count at partition 3 : 200,006,700 (Skew: 0.01%)
Row count at partition 4 : 199,831,600 (Skew: -0.10%)
Row count at partition 5 : 199,962,200 (Skew: -0.04%)
Row count at partition 6 : 200,083,900 (Skew: 0.01%)
Row count at partition 7 : 199,910,300 (Skew: -0.06%)
Row count at partition 8 : 200,126,400 (Skew: 0.03%)

Number of partitions: 8 (1, 2, 3, 4, 5, 6, 7, 8)
------------------------------------------------------------------------
TPCD.SUPPLIER
Estimated total number of records in the table: : 20,000,000
Estimated average number of records per partition : 2,500,000

Row count at partition 1 : 2,498,411 (Skew: -0.06%)
Row count at partition 2 : 2,498,837 (Skew: -0.04%)
Row count at partition 3 : 2,500,996 (Skew: 0.03%)
Row count at partition 4 : 2,500,170 (Skew: 0.00%)
Row count at partition 5 : 2,501,254 (Skew: 0.05%)
Row count at partition 6 : 2,499,654 (Skew: -0.01%)
Row count at partition 7 : 2,501,429 (Skew: 0.05%)
Row count at partition 8 : 2,499,249 (Skew: -0.03%)

Number of partitions: 8 (1, 2, 3, 4, 5, 6, 7, 8)
------------------------------------------------------------------------

Total execution time: 51 seconds

评估已有表上的候选替换 PK 的质量

如果决定更改现有的一个分区键，那么很重要的一点是要确认这个新的分区键将会带来好的查询并置及数据的均衡分配。

为了查看查询并置，建议您收集能体现工作负载的那些查询，将这些查询放入一个文件，然后运行一个 db2advis 报告来获得对新分区键的建议：

db2advis -d  -i  -m P

还可以使用如下形式的 db2advis 实用工具基于尚处于包缓存中的最新执行的查询运行一个报告：

db2advis -d  -g -m P

清单 4 给出了一个示例 db2advis 输出：

				
bculinux> db2advis -d tpcds -g -m P
Using user id as default schema name. Use -n option to specify schema
execution started at timestamp 2010-04-06-11.33.04.271678
Recommending partitionings...
Cost of workload with all recommendations included [1761.000000] timerons
1 partitionings in current solution
 [1761.0000] timerons  (without recommendations)
 [1736.0000] timerons  (with current solution)
 [1.42%] improvement


--
--
-- LIST OF MODIFIED CREATE-TABLE STATEMENTS WITH RECOMMENDED PARTITIONING KEYS AND
TABLESPACES AND/OR RECOMMENDED MULTI-DIMENSIONAL CLUSTERINGS
-- ===========================
-- CREATE TABLE "BCULINUX"."ITEM" ( "I_ITEM_SK" INTEGER NOT NULL ,
--       "I_ITEM_ID" CHAR(16) NOT NULL ,
--       "I_REC_START_DATE" DATE ,
--       "I_REC_END_DATE" DATE ,
--       "I_ITEM_DESC" VARCHAR(200) ,
--       "I_CURRENT_PRICE" DECIMAL(7,2) ,
--       "I_WHOLESALE_COST" DECIMAL(7,2) ,
--       "I_BRAND_ID" INTEGER ,
--       "I_BRAND" CHAR(50) ,
--       "I_CLASS_ID" INTEGER ,
--       "I_CLASS" CHAR(50) ,
--       "I_CATEGORY_ID" INTEGER ,
--       "I_CATEGORY" CHAR(50) ,
--       "I_MANUFACT_ID" INTEGER ,
--       "I_MANUFACT" CHAR(50) ,
--       "I_SIZE" CHAR(20) ,
--       "I_FORMULATION" CHAR(20) ,
--       "I_COLOR" CHAR(20) ,
--       "I_UNITS" CHAR(10) ,
--       "I_CONTAINER" CHAR(10) ,
--       "I_MANAGER_ID" INTEGER ,
--       "I_PRODUCT_NAME" CHAR(50) )
-- ---- DISTRIBUTE BY HASH("I_ITEM_SK")
-- ---- IN "HASHTS"
-- DISTRIBUTE BY HASH (I_ITEM_SK)
-- IN USERSPACE1
-- ;
-- COMMIT WORK ;

-- ===========================

为了查看使用新的分区键是否能很好地均衡数据，可以使用下载部分提供的 ESTIMATE_NEW_DATA_SKEW 例程。这个例程用新的分区键创建了现有表的一个副本并用来自原始表的数据对它进行部分或全部加载。例程然后会为了进行现有数据倾斜的估计运行相同的报告并且最后还会丢弃这个副本表。请注意包含原始表的表空间必须能够保存来自原始表最少 1% 的数据，因为复制版本是在相同的表空间内创建的。

示例 3

这个示例测试的是分区键从 S_NATIONKEY 更改为 S_ID 的场景中的数据倾斜。这个例子使用了样本中 100% 的数据。正如这个示例所展示的，新的分区键带来了极少的数据倾斜，因此比示例 1 中的原始 S_NATIONAL 键好很多。

				
$ db2 "set serveroutput on"
$ db2 "CALL estimate_new_data_skew('TPCD', 'SUPPLIER', 'S_ID', 100)"
CALL estimate_new_data_skew('TPCD', 'SUPPLIER', 'S_ID ', 100)

  Return Status = 0

DATA SKEW ESTIMATION REPORT FOR TABLE: TPCD.SUPPLIER
This report is based on the new partitioning key: S_NATIONKEY
Accuracy is based on 100% sample of data
------------------------------------------------------------------------
TPCD.SUPPLIER
Estimated total number of records in the table: : 20,000,000
Estimated average number of records per partition : 2,500,000

Row count at partition 1 : 2,498,411 (Skew: 0.06%)
Row count at partition 2 : 2,498,837 (Skew: 0.04%)
Row count at partition 3 : 2,500,996 (Skew: 0.03%)
Row count at partition 4 : 2,500,170 (Skew: 0.00%)
Row count at partition 5 : 2,501,254 (Skew: 0.05%)
Row count at partition 6 : 2,499,654 (Skew: 0.01%)
Row count at partition 7 : 2,501,429 (Skew: 0.05%)
Row count at partition 8 : 2,499,249 (Skew: 0.03%)

Number of partitions: 8 (1, 2, 3, 4, 5, 6, 7, 8)
------------------------------------------------------------------------

Total execution time: 20 seconds

在保持表在线的情况下更改 PK

在 DB2 9.7 内有一个名为 ADMIN_MOVE_TABLE 的新例程，可用来自动更改表的分区键，同时又能保持表对读写的完全可访问性。除了更改分区键，这个过程能够将表移到不同的表空间、更改列定义等。

示例 4

这个示例将 TPCD.PART 表的分区键从 COL1 更改为 (COL2, COL3)。它还使用 LOAD 选项来提高 ADMIN_MOVE_TABLE 例程的性能。

				

CALL SYSPROC.ADMIN_MOVE_TABLE
      ('TPCD', 'PART', '', '', '',
       '', 'COL2, COL3', '', '',
       'COPY_USE_LOAD, FORCE', 'MOVE')

Result set 1
  --------------

  KEY                              VALUE
  -------------------------------- ----------------------------
  AUTHID                           TPCD
  CLEANUP_END                      2010-03-12-12.40.17.360000
  CLEANUP_START                    2010-03-12-12.37.43.297000
  COPY_END                         2010-03-12-12.37.42.704000
  COPY_OPTS                        OVER_INDEX,LOAD,WITH_INDEXES
  COPY_START                       2010-03-12-11.18.40.563000
  COPY_TOTAL_ROWS                  400000000
  INDEX_CREATION_TOTAL_TIME        0
  INDEXNAME                        PROD_ID_PK
  INDEXSCHEMA                      TPCD
  INIT_END                         2010-03-12-12.59.40.266000
  INIT_START                       2010-03-12-12.40.39.172000
  REPLAY_END                       2010-03-12-11.18.43.125000
  REPLAY_START                     2010-03-12-11.18.42.704000
  REPLAY_TOTAL_ROWS                0
  REPLAY_TOTAL_TIME                0
  STATUS                           COMPLETE
  SWAP_END                         2010-03-12-11.18.43.250000
  SWAP_RETRIES                     0
  SWAP_START                       2010-03-12-11.18.43.125000
  VERSION                          09.07.0000

21 record(s) selected.

在 ADMIN_MOVE_TABLE 过程运行时，TPCD.PART 表是完全可访问的并且分区键的更改对于终端用户是透明的。

结束语

选择适当的分区键对于优化基于 DB2 软件的分区环境中的数据库性能非常关键。本文提供了如何根据您自己的需要选择最佳分区键的指导和工具。

本文描述了：

与分区键有关的概念以及创建分区键的规则和建议
可帮助您评估新的和已有分区键的数据倾斜的例程
在保持表的可访问性的情况下如何更改分区键

附录：例程参考文档

前提条件

ESTIMATE_EXISTING_DATA_SKEW 和 ESTIMATE_NEW_DATA_SKEW 这两个过程在 DB2 9.7 或更高版本中均受支持。用于实际移动表的例程 ADMIN_MOVE_TABLE 则随核心 DB2 9.7 产品或更高版本附带。对于 ESTIMATE_NEW_DATA_SKEW 例程，包含原始表的表空间内必须要有足够的空间来存储样例数据。

部署指导

从下载部分下载并保存 estimate_data_skew.sql 文件。
从命令行连接到数据库并使用如下命令部署例程：
$ db2 -td@ -vf estimate_data_skew.sql

ESTIMATE_NEW_DATA_SKEW 过程

ESTIMATE_NEW_DATA_SKEW 例程基于一个新分区键估算已有表的各数据库分区的数据倾斜。为了改进性能并降低例程的存储要求，估算可使用页面级的极快速取样基于数据的一个子集。

				
>>-ESTIMATE_DATA_SKEW--(--tabschema--,--tabname--,---------------->
>--new_partitioning_keys--,--sampling_percentage--)-------->

过程参数

in_tabschema 这个输入参数指定包含要被估算数据倾斜的表的模式的名称。这个参数大小写敏感，数据类型为 VARCHAR(128)。这个参数不支持通配符。

in_tabname 这个输入参数指定要被估算数据倾斜的表的名称。这个参数大小写不敏感，数据类型为 VARCHAR(128)。这个参数不支持通配符。

new_partitioning_keys 这个输入参数指定要被用在数据倾斜估算中的新分区键。

sampling_percentage 这个输入参数指定用在数据倾斜估算中的数据的百分比。有效值是 1 到 100，其中 100 意味着这个存储过程将使用表内的所有记录进行估算。此参数的目的是改善性能并在用新分区键估算数据倾斜时最小化空间使用。如果性能和磁盘空间是个问题，就将此值指定为 100。

ESTIMATE_EXISTING_DATA_SKEW 过程

ESTIMATE_EXISTING_DATA_SKEW 存储过程基于已有的分区键估算一个或多个表内的各数据库分区的数据倾斜。为了改善此过程的性能，估算可使用页面级的极快速取样基于数据的一个子集。

				
>>-ESTIMATE_EXISTING_DATA_SKEW--(--in_tabschema--,--in_tabname--,->
>--sampling_percentage--)-------->

过程参数

in_tabschema 这个输入参数指定包含要被估算数据倾斜的表的模式的名称。这个参数大小写敏感，数据类型为 VARCHAR(128)。这个参数支持 % 作为通配符。如果指定 NULL 值，就会为此数据库内定义的所有模式运行一个报告。 in_tabname 这个输入参数指定要被估算数据倾斜的表的名称。这个参数大小写不敏感，数据类型为 VARCHAR(128)。这个参数支持 % 作为通配符。 sampling_percentage 这个输入参数指定用在数据倾斜估算中的数据的百分比。有效值是 1 到 100，其中 100 意味着这个存储过程将使用表内的所有记录进行估算。

原文链接：http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-1005partitioningkeys/index.html

ITPub博客

在 DB2 Database Partitioning Feature 环境中选择分区键

最新文章