 | 级别： 初级 骆洪青 ( hq_l@tom.com), 软件事业部经理, 北京银信长远科技有限公司
2009 年 7 月 13 日 DB2 V9.7 提出了范围分区表的分区索引概念，这为客户在原有的全局索引基础上增加了一个新的功能选择。分区索引采用 B 树结构，将一棵全局大树分解为诺干个小树，树的层次将会显著减少，这将会提高数据的插入、更新、删除和扫描的性能。分区索引在分区表的 Roll in/Roll out 时不需要重新构建整个索引，加速了数据的滚入和滚出。
简介
在 DB2 V9.7 以前，范围分区表只支持全局索引（或者称为非分区索引），这样即使分区表的数据是分布在多个表空间上的，分区表的所有索引只能存储在同一个表空间中。这一特性限制了索引扫描只能在一个表空间上对该索引的页面进行读取，导致读取页面过多并约束了扫描的并行性。
DB2 V9.7 使用分区索引的索引组织方案，即索引数据根据表的分区方案分布到多个索引分区中，每个索引分区都只引用对应数据分区中的表行。从 DB2 V9.7 开始，创建索引时默认创建分区索引，除非出现以下情况：
- 对 CREATE INDEX 语句指定了 UNIQUE，并且索引键未包括所有表分区键列。
- 创建基于空间数据的索引。
- 创建基于 XML 数据的索引
分区索引的创建
一个分区表可以同时存在分区索引和非分区索引。如果创建分区索引，那个每个索引分区将都包含单个数据分区的索引条目，索引叶子节点中的 RID 也将只指向单个数据分区。
在我们使用 Alter table 语句的 Attach Partition 子句将数据 Roll in 或者 Roll Out 分区表时，分区索引将特别有用。如果使用非分区索引，那么必须先发出 Set Integrity 语句，新添加的分区中的数据才能进入联机状态。这个过程可能非常消耗时间，并可能消耗大量日志空间。当使用分区索引时这些开销将会被消除。
分区索引的每个分区均采用 B 树结构存储，由于分区后一个大型 B 树被划分为若干小型 B 树，树的层数将会减少，这会提高数据的插入、更新、删除以及扫描的性能。同时我们执行查询时，DB2 将会采用分区消除优化方法提高扫描性能和并行性。分区消除技术帮助优化器先过滤了不需要的索引分区，只需要扫描相应的分区就能完成查询，这比扫描非分区的索引更为高效。
清单 1. 创建示例分区表与索引
CREATE TABLE t1 ( l_orderkey INTEGER, l_partkey INTEGER, l_suppkey INTEGER,
l_shipdate date, padding1 char(30) )
PARTITION BY RANGE(l_shipdate)
(
partition quarter01 STARTING '2008-01-01' ENDING '2008-03-31'in DMS_D1,
partition quarter02 STARTING '2008-04-01' ENDING '2008-06-30'in DMS_D2,
partition quarter03 STARTING '2008-07-01' ENDING '2008-09-30' in DMS_D3,
partition quarter04 STARTING '2008-10-01' ENDING '2008-12-31' in DMS_D4
);
Create index idx_t1_l_orderkey on t1(l_orderkey) NOT partitioned ;
Create index idx_t1_l_partkey on t1(l_partkey) partitioned; |
上面代码中，我们创建了一个拥有四个分区的范围分区表，同时创建了两个索引。索引 index idx_t1_l_orderkey 是非分区索引，idx_t1_l_partkey 是分区索引。
我们下面查看一下上面代码执行后数据字典关于表 T1 以及它的索引的描述。系统视图 syscat.datapartitions 中包含了分区表的分区信息以及相关统计信息，系统视图 syscat.indexpartitions 中则包含了分区索引的分区信息和相关统计信息。
清单 2. 分区表的数据分区信息
db2 "select substr(TABNAME ,1,10) t_name,substr(DATAPARTITIONNAME ,1,10) part_name,
DATAPARTITIONID ,substr(LOWVALUE,1,12) low_value,substr(HIGHVALUE,1,12) high_value
from syscat.datapartitions where tabname='T1'"
T_NAME PART_NAME DATAPARTITIONID TBSPACEID LOW_VALUE HIGH_VALUE ----------
T1 QUARTER01 0 8 '2008-01-01' '2008-03-31'
T1 QUARTER02 1 9 '2008-04-01' '2008-06-30'
T1 QUARTER03 2 10 '2008-07-01' '2008-09-30'
T1 QUARTER04 3 11 '2008-10-01' '2008-12-31' |
清单 2 中代码执行查询，从系统视图 syscat.datapartitions 中获取关于数据分区的信息。查询结果显示，表 T1 具有四个分区，名称与我们创建时相同，ID 依次为 0、1、2、3，数据分布在表空间 DMS_D1（ID 为 8）、DMS_D2（ID 为 9）、DMS_D2（ID 为 10）、DMS_D3（ID 为 11）上。本例中的表空间信息如下：
清单 3. 表空间信息
db2 "select substr(TBSPACE,1,18) tablespace_name,TBSPACEID ,TBSPACETYPE
from syscat.tablespaces"
TABLESPACE_NAME TBSPACEID TBSPACETYPE
------------------ ----------- -----------
SYSCATSPACE 0 D TEMPSPACE1 1 S USERSPACE1 2 D
IBMDB2SAMPLEREL 3 D IBMDB2SAMPLEXML 4 D SYSTOOLSPACE 5 D
SYSTOOLSTMPSPACE 6 S TBS_TEST 7 D
DMS_D1 8 D DMS_D2 9 D DMS_D3 10 D DMS_D4 11 D DMS_I1 12 D |
清单 4. 索引基本信息
db2 "select substr(tabname,1,10) tab_name, substr(INDNAME,1,18) inx_name,TBSPACEID
from syscat.indexes where tabname='T1'"
TAB_NAME INX_NAME TBSPACEID ---------- ------------------ -----------
T1 IDX_T1_L_ORDERKEY
8
T1 IDX_T1_L_PARTKEY 65530 |
我们看到非分区索引 IDX_T1_L_ORDERKEY 数据存放在表空间 DMS_D1 上（ID 为 8），这意味着在创建索引未明确指定表空间时，DB2 将使用第一个数据分区所在的表空间存放非分区索引。而索引 IDX_T1_L_PARTKEY 所在的表空间 ID 为 65530，我们通过表空间信息部分看到我们的数据库中没有 ID 为 65530 表空间。由于分区索引的存储特性是和数据分区关联的，一个索引将分布在多个表空间中，因此 DB2 用了 65530 特殊值表示索引的表空间，并不表示这个 ID 对应的表空间存在。
清单 5. 索引分区信息
db2 "select substr(INDNAME,1,18) idx_name,DATAPARTITIONID from syscat.indexpartitions"
IDX_NAME DATAPARTITIONID
------------------ ---------------
IDX_T1_L_PARTKEY 0
IDX_T1_L_PARTKEY 1
IDX_T1_L_PARTKEY 2
IDX_T1_L_PARTKEY 3 |
我们看到索引 IDX_T1_L_PARTKEY 被分为 4 个区，存放索引的表空间与数据分区的表空间相同，如索引 IDX_T1_L_PARTKEY 第一个分区只引用数据分区 0（QUARTER01 分区）的行，数据保存在 DMS_D1 中。请注意索在创建分区索引时，不能直接为其指定用于存储索引分区的表空间，其表空间由创建表时为数据分区指定的表空间确定。默认情况下，分区的缺省存放位置与它所引用的数据分区的位置相同。
清单 6. 为索引指定表空间
CREATE TABLE t1 (columns) in ts1 INDEX IN ts2 1
PARTITION BY RANGE (column expression) (PARTITION PART0 STARTING
FROM constant ENDING constant IN ts3,
PARTITION PART1 STARTING FROM constant ENDING constant INDEX IN ts5,
PARTITION PART2 STARTING FROM constant ENDING constant INDEX IN ts4,
PARTITION PART3 STARTING FROM constant ENDING constant INDEX IN ts4,
PARTITION PART4 STARTING FROM constant ENDING constant)
CREATE INDEX x1 ON t1 (...) NOT PARTITIONED;
CREATE INDEX x2 ON t1 (...) PARTITIONED;
CREATE INDEX x3 ON t1 (...) PARTITIONED; |
上面的示例中，非分区索引 X1 存储在表空间 TS2 上，这是由于在创建表 T1 的为所有非分区索引指定了缺省表空间 TS2 。
分区索引 X2 和 X3 的数据分区 0 对应的索引分区存储在表空间 ts3 上，这是因为索引分区的缺省位置与其所引用的数据分区相同。数据分区 1、2 对应的索引分区存储在表空间 ts4 上，这是因为这两个数据分区明确指示了存储索引的表空间。数据分区 4 对应的索引分区存储存储在 ts1 上，这是因为我们没有给数据分区 4 指定存储表空间，其数据默认存储表空间为 ts1 。
在 DB2 V9.7 以后，用户创建的索引默认都是分区索引。创建非唯一分区索引时将会 DB2 将会自动使用分区键进行分区。创建唯一分区索引时，索引列中必须包含用于分区的所有列，否则 DB2 将返回 SQL20303N 错误表示索引创建失败。
分区索引空间占用与扫描性能
分区表主要应用在表比较大的场景下，因此我们使用大表才能测试出性能。下面我们创建测试表。
清单 7. 创建测试大表
drop table t1;
CREATE TABLE t1
( l_orderkey INTEGER NOT NULL, l_partkey
INTEGER, l_suppkey INTEGER, l_shipdate date, padding1 char(30)
)
PARTITION BY RANGE(l_shipdate)
(
STARTING '2008-01-01' ENDING '2008-12-31' EVERY 1 MONTH
)
;
INSERT INTO t1 (l_orderkey, l_partkey, l_suppkey,l_shipdate,padding1)
WITH TEMP (COUNTER, l_orderkey, l_partkey, l_suppkey,l_shipdate,padding1) AS
( VALUES (0, MOD(INT(RAND() * 12000000), 25), MOD(INT(RAND() * 12000000), 30),
MOD(INT(RAND() * 12000000), 30), DATE(MOD(INT(RAND() * 12000000), 366)+733042), 'A')
UNION ALL SELECT (COUNTER + 1), MOD(INT(RAND() * 12000000), 25),
MOD(INT(RAND() * 12000000), 30), MOD(INT(RAND() * 12000000), 30),
DATE(MOD(INT(RAND() * 12000000), 366)+733042), 'A' FROM TEMP
WHERE (COUNTER + 1) < 12000000
)
SELECT l_orderkey, l_partkey, l_suppkey,l_shipdate,padding1
FROM TEMP
; |
我们创建的表包含 1200 万行数据，按照月份每个月一个分区，分区列 l_shipdate 的数据分布在’ 2008-01-01 ’和’ 2008-12-31 ’之间，且均匀分布。注意 733042 是日期 2008-01-01 在 DB2 内以天数的表达形式，是通过 days() 函数获得的。
我们首先在列 l_orderkey 上创建非分区索引。
清单 8. 创建非分区索引
db2 "Create index idx_nopart_l_orderkey on t1(l_orderkey) not partitioned"
db2 "runstats on table db2inst1.t1 and indexes all"
db2 "select substr(INDNAME,1,25) idx_name,NLEVELS,NLEAF,INDCARD
from syscat.indexes where tabname='T1'"
DX_NAME NLEVELS NLEAF INDCARD
------------------ ------- -------------------- --------------------
IDX_NOPART_L_ORDERKEY 3 16831 12000000 |
清单 8 表明，非分区索引 B 树高度为 3 层，具有 16831 个叶子页面。
清单 9. 测试非分区索引性能
db2 set current explain mode yes
db2 values current timestamp
1
--------------------------
2009-07-07-15.46.24.863000
db2 "select count(*) from t1 "
1
----------- 12000000
db2 values current timestamp
1
--------------------------
2009-07-07-15.46.27.394000
db2exfmt -d sample -w -1 -n % -s % -# 0 -t
Total Cost: 24109.7
Query Degree: 1
Rows RETURN ( 1) Cost I/O
| 1 GRPBY ( 2) 24109.7 17002 | 1.2e+007 IXSCAN ( 3) 23259.5 17002 | 1.2e+007
INDEX: ADMINISTRATOR
IDX_NOPART_L_ORDERKEY Q1 |
清单 9 表明使用索引 IDX_NOPART_L_ORDERKEY 统计表 T1 的总行数时，估计总成本为 24109.7，IO 次数估计为 17002，实际花费时间为 2.45 秒。
清单 10. 创建分区索引
db2 "Create index idx_part_l_orderkey on t1(l_orderkey) partitioned"
db2 "runstats on table db2inst1.t1 and indexes all"
db2 "select substr(INDNAME,1,25) idx_name,DATAPARTITIONID,NLEVELS,NLEAF,INDCARD
from syscat.indexpartitions"
IDX_NAME DATAPARTITIONID NLEVELS NLEAF INDCARD
------------------------- --------------- ------- --------- ---------
IDX_PART_L_ORDERKEY 0 3 1134 1021133
IDX_PART_L_ORDERKEY 1 3 1062 956131
IDX_PART_L_ORDERKEY 2 3 1136 1023293
IDX_PART_L_ORDERKEY 3 3 1098 988650
IDX_PART_L_ORDERKEY 4 3 1134 1021552
IDX_PART_L_ORDERKEY 5 3 1100 990715
IDX_PART_L_ORDERKEY 6 3 1134 1020850
IDX_PART_L_ORDERKEY 7 3 1137 1023727
IDX_PART_L_ORDERKEY 8 3 1101 991839
IDX_PART_L_ORDERKEY 9 3 1133 1020225
IDX_PART_L_ORDERKEY 10 3 1078 970906
IDX_PART_L_ORDERKEY 11 3 1078 970979 |
清单 10 表明，分区索引 idx_part_l_orderkey 具有 12 个分区，B 树高度为 3 层，合计具有 13325 个叶子页面，叶子页面数比非分区索引下降 20% 。
清单 11. 测试分区索引性能
db2 set current explain mode yes
db2 values current timestamp
1
--------------------------
2009-07-07-15.59.09.722000
db2 "select count(*) from t1 "
1
----------- 12000000
db2 values current timestamp
1
--------------------------
2009-07-07-15.59.11.910000
db2exfmt -d sample -w -1 -n % -s % -# 0 -t
Total Cost: 24109.7
Query Degree: 1 Total Cost: 22059.4
Query Degree: 1
Rows RETURN ( 1) Cost I/O | 1 GRPBY ( 2)
22059.4 14178.4 | 1.2e+007 IXSCAN ( 3) 21209.2 14178.4 | 1.2e+007
INDEX: ADMINISTRATOR
IDX_PART_L_ORDERKEY Q1 |
清单 11 表明使用索引 IDX_PART_L_ORDERKEY 统计表 T1 的总行数时，估计总成本为 22059.4，比非分区索引下降 8.5%，IO 次数估计为 14178.4，比非分区索引下降 16%, 实际花费时间为 2.19 秒，比非分区索引下降 10% 。
上述测试表明，分区索引在空间占用、扫描性能方面比非分区索引具有一定的性能优势。
分区索引对 Roll Out/Roll In 的影响
分区表的一个重要功能是能够快速的将单个分区数据进行 Roll Out/Roll in 。在 DB2 V9.7 之前，对分区表进行 Roll Out/Roll in 时需要对所有索引进行维护，使用分区索引后，将会消除在 DETACH 时对索引进行的异步维护。在进行分区 Attach 时，只需要对新联结上分区构建索引即可。与非分区索引相比，这两个特性将会极大的提高分区 Roll In 速度，同时也会极大减少日志空间要求。
仿照清单 7 创建测试大表的方法，生成 120 万条记录，时间为 2009-01-01 至 2009-01-31，不过需要注意日期 2009-01-01 在 DB2 内部表示的天数为为 733408 。我们准备把 2008-01-01 至 2008-01-31 数据 Detach 出来，然后将新生成的 2009-01-01 至 2009-01-31 数据 Attach 到 t1 表中。
清单 12. 非分区索引 Detach/Attach 性能
values current timestamp
1
--------------------------
2009-07-07-17.33.13.019000
alter table t1 DETACH PARTITION PART0 into temp
values current timestamp
1
--------------------------
2009-07-07-17.33.13.347000
load from d:/t10901.IXF of ixf replace into temp
values current timestamp
1
--------------------------
2009-07-07-17.33.31.722000
ALTER TABLE t1 ATTACH PARTITION PART0901 STARTING FROM '2009-01-01' ENDING AT '2
009-01-31' FROM temp
values current timestamp
1
--------------------------
2009-07-07-17.33.32.425000
COMMIT WORK
select TOTAL_LOG_AVAILABLE ,TOTAL_LOG_USED from sysibmadm.snapdb
TOTAL_LOG_AVAILABLE TOTAL_LOG_USED
-------------------- -------------------- 4162501098 35778902
values current timestamp
1
--------------------------
2009-07-07-17.33.32.519000
SET INTEGRITY FOR t1 ALLOW WRITE ACCESS IMMEDIATE CHECKED
values current timestamp
1
--------------------------
2009-07-07-17.33.50.972000
select TOTAL_LOG_AVAILABLE ,TOTAL_LOG_USED from sysibmadm.snapdb
TOTAL_LOG_AVAILABLE TOTAL_LOG_USED
-------------------- -------------------- 3948641205 249638795 |
从清单 12 可以看出，DETACH 分区花费 0.34 秒，Attach 分区 0.7 秒，SET INTEGRITY 花费时间 18.453 秒。在 SET INTEGRITY 时，消耗日志 213859893 字节，约 203MB 。
清单 13. 分区索引 Detach/Attach 性能
values current timestamp
1
--------------------------
2009-07-07-17.27.44.753000
alter table t1 DETACH PARTITION PART0 into temp
values current timestamp
1
--------------------------
2009-07-07-17.27.45.128000
load from d:/t10901.IXF of ixf replace into temp
values current timestamp
1
--------------------------
2009-07-07-17.27.58.910000
ALTER TABLE t1 ATTACH PARTITION PART0901 STARTING FROM '2009-01-01' ENDING AT '2
009-01-31' FROM temp
values current timestamp
1
--------------------------
2009-07-07-17.27.59.738000
COMMIT WORK
select TOTAL_LOG_AVAILABLE ,TOTAL_LOG_USED from sysibmadm.snapdb
TOTAL_LOG_AVAILABLE TOTAL_LOG_USED
-------------------- --------------------
4163425378 34854622
values current timestamp
1
--------------------------
2009-07-07-17.27.59.863000
SET INTEGRITY FOR t1 ALLOW WRITE ACCESS IMMEDIATE CHECKED
values current timestamp
1
--------------------------
2009-07-07-17.28.01.831000
select TOTAL_LOG_AVAILABLE ,TOTAL_LOG_USED from sysibmadm.snapdb
TOTAL_LOG_AVAILABLE TOTAL_LOG_USED
-------------------- -------------------- 4163421697 34858303 |
从清单 13 可以看出，DETACH 分区花费 0.375 秒，Attach 分区 0.828 秒，SET INTEGRITY 花费时间 1.97 秒。在 SET INTEGRITY 时，消耗日志 3681 字节。
从两者对比可以看到，分区索引在联结分区方面速度约提高了 10 倍，而日志空间消耗则减少了几千倍。
总结
DB2 V9.7 提供的分区索引功能可以帮助我们更加快速的将数据 Roll in/Roll out 分区表，同时它在查询优化方面能够提供一定的性能帮助。分区索引在 DB2 V9.7 是默认行为，基本上不需要人工的参与，这又简化了 DBA 的管理工作。
参考资料 学习
获得产品和技术
- 使用可直接从 developerWorks 下载的 IBM 产品评估试用软件 构建您的下一个开发项目。
- 现在可以免费使用 DB2。下载 DB2 Express-C，这是为社区提供的 DB2 Express Edition 的免费版本，它提供了与 DB2 Express Edition 相同的核心数据特性，为构建和部署应用程序奠定了坚实的基础。
讨论
关于作者 | | | 骆洪青，北京银信长远科技有限公司软件事业部经理，主要从事 DB2、数据仓库、ETL、AIX、HACMP 等方面的研究工作，对 DB2 的性能调优有浓厚的兴趣。 |
对本文的评价
| |
