DB2 V9.7 索引压缩新特性的使用

最新推荐文章于 2021-01-09 22:12:18 发布

feier7501

最新推荐文章于 2021-01-09 22:12:18 发布

阅读量1.8k

点赞数

分类专栏： DB2

DB2 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0907luohq2/

DB2 V9.1 提出了行压缩技术，当我们的系统中数据量很大，IO 需求超过了磁盘系统提供的容量（即 IO 成为系统的瓶颈）时，行压缩技术能够有效的减少读写磁盘的次数。 DB2 9.7 更进一步提出了索引压缩技术，减少索引磁盘空间的占用，减少读取索引时的 IO 次数从而提高了性能。 DB2 压缩不仅有助于减少在线数据库存储区需求，还有助于减少在备份和灾难恢复时所需的存储器数量。此外，由于在备份与恢复过程中涉及到的数据量变小了，所以备份与恢复操作所花的时间也就变短了。所有这些因素都在无形中节约了 IT 成本。

简介

数据库中占用物理存储空间的对象主要是表和索引，这两类对象的大小直接影响着磁盘空间的占用，同时也决定着数据库的性能。当前数据库系统中，随着时间的推移表会越来越大，对应着索引也会越来越大，这也是我们的系统越来越慢的原因。 DB2 V9.1 中提出了全新的行深度压缩（deep compression）的技术，以应对这种挑战。尽管深度压缩的主要目的是节省存储空间，但是使用它也可以大大节省磁盘 I/O 并提高缓冲池命中率。因而可以提高性能，并无需成本——数据压缩和解压缩需要占用额外的 CPU 周期。深度压缩的存储节省和性能影响与数据、数据库本身的设计、数据库调优程度以及应用程序负载有关。

在 DB2 V9.7，IBM 在行压缩的基础上提出了索引压缩，其目标与行压缩一样，都是为减少磁盘空间的占用，这同时适用于大型 OLTP 和数据仓库环境。 DB2 V9.7 采用多种压缩算法对索引进行自动压缩。本文不会对具体的压缩算法进行讨论，而是将重点放在索引压缩的应用场景上，即如何启动索引压缩、什么数据分布适合索引压缩，什么数据不适合索引压缩。

如何启用索引压缩

在缺省情况下，当对表启动压缩后，索引压缩也处在启动状态。对于未压缩的表索引压缩处于禁用状态，我们可以使用 CREATE INDEX 语句的 COMPRESS YES 选项可以更改此缺省行为。创建索引之后，我们还可以使用 ALTER INDEX 语句来启用或禁用索引压缩功能；然后，必须执行 INDEX REORG 以重建索引。

启用索引压缩功能后，DB2 将根据数据库管理器所选择的压缩算法对索引页在磁盘上和内存中的格式进行修改，以便最大程度地减少存储空间耗用量。根据所创建索引类型以及索引所包含数据的不同，DB2 实现的压缩程度也会有所变化。例如，通过存储重复键的记录标识（RID）的缩写格式，数据库管理器可以对包含大量重复键的索引进行压缩。在索引键前缀的公共程度很高的索引中，数据库管理器可以根据索引键前缀的相似性来进行压缩。

索引压缩是使用 CPU 的空闲周期或者是 CPU 在等待 IO 时的周期对索引数据进行压缩、解压缩的。因此在带来 IO 成本节约的同时，索引压缩技术增加了系统的 CPU 负担，如果我们的系统不受到 CPU 的约束，我们在对数据进行 Select、Insert、Update 时都能感觉到索引压缩技术带来的性能提升。如果我们的系统本身 CPU 就已经比较繁忙了，再启用索引压缩可能会带来一些负面影响。

清单 1. 创建表时指定表压缩

db2 "create table t1 (col1 int) compress yes" 
 db2 "create index idx_col1 on t1(col1) " 
 db2 "select substr(INDNAME,1,18),substr(TABNAME,1,18),COMPRESSION,PCTPAGESSAVED 
 from syscat.indexes where tabname='T1'" 

 1 2 
				           COMPRESSION PCTPAGESSAVED 
 ------------------ ------------------ ----------- ------------- 
 IDX_COL1 T1 Y  -1

上面的语句中首先创建了一张表 T1，并对该表启动行压缩。在创建索引 idx_col1 时，由于基表启动了压缩，索引压缩也被自动启动。上述代码的第三句就是验证索引 idx_col1 确实启动了压缩，而由于未收集统计信息因此当前压缩比例是 -1 。当我们向表中 Insert 或者 Update 数据时，索引自动被压缩维护到物理存储上。

如果我们在创建表时未指定表进行压缩，那么此表上创建的索引默认情况下是不压缩的，如果期望对索引进行压缩需要进行以下步骤。

db2 "create table t2 (col1 int) 

 db2 "create index idx_col2 on t2(col1) " 

 db2 "select substr(INDNAME,1,18),substr(TABNAME,1,18),COMPRESSION,PCTPAGESSAVED 
 from syscat.indexes where tabname='T2'" 


 1 2  COMPRESSION PCTPAGESSAVED 
 ------------------ ------------------ ----------- ------------- 
 IDX_COL2 T2 N 
				    -1 

 db2 "alter index idx_col2 compress yes" 


 db2 "select substr(INDNAME,1,18),substr(TABNAME,1,18),COMPRESSION,PCTPAGESSAVED 
 from syscat.indexes where tabname='T2'" 


 1 2  COMPRESSION PCTPAGESSAVED 
 ------------------ ------------------ ----------- ------------- 
 IDX_COL2 T2  Y  -1

上面语句中开始创建表时未指定表进行压缩，后继创建的索引默认情况下不压缩。如果希望索引启动压缩功能，则可以使用 alter 语句进行更改。

注意，即使我们更改将索引更改为压缩后，后来插入的数据还是未压缩的，直到我们使用 reorg 语句重组索引。 DB2 考虑中间更改索引的压缩属性，需要对更改前、更改后的插入的数据保持一致性，不可能在索引中同时存在非压缩、压缩数据。

我们对上面的 IDX_COL2 执行以下脚本，插入 1 万行数据：

INSERT INTO t2 (col1) 
 WITH TEMP (COUNTER, col1) AS 
 ( 
  VALUES (0, INT(RAND() * 1000)) 
  UNION ALL 
  SELECT 
  (COUNTER + 1), INT(RAND() * 1000) 
  FROM 
  TEMP 
  WHERE 
 (COUNTER + 1) < 10000 
 ) 
 SELECT 
  col1 
 FROM 
  TEMP 
 ;

然后我们收集表和索引的统计信息。

db2 "runstats on table db2admin.t2 and indexes all" 
 db2 "select substr(INDNAME,1,18),substr(TABNAME,1,18),COMPRESSION,PCTPAGESSAVED 
 from syscat.indexes where tabname='T2'" 


 1 2  COMPRESSION PCTPAGESSAVED 
 ------------------ ------------------ ----------- ------------- 
 IDX_COL2 T2  Y  0

大家会发现压缩率为 0，这是因为我们还没有对索引进行 reorg 。当然，除了上面 Select 语句我们也可以使用 REORGCHK 工具检查是否需要对索引进行 Reorg 。

db2 "reorg indexes all for table db2admin.t2" 

 db2 "runstats on table db2admin.t2 and indexes all" 

 db2 "select substr(INDNAME,1,18),substr(TABNAME,1,18),COMPRESSION,PCTPAGESSA 
 VED from syscat.indexes where tabname='T2'" 

 1 2  COMPRESSION PCTPAGESSAVED 
 ------------------ ------------------ ----------- ------------- 
 IDX_COL2 T2 Y  40

使用表函数

DB2 V9.7 版本中提供一个表函数 ADMIN_GET_INDEX_COMPRESS_INFO，帮助我们对潜在的索引压缩空间节约情况进行评估，或者报告当前索引压缩的统计信息。

ADMIN_GET_INDEX_COMPRESS_INFO(objecttype , objectschema , 
objectname , dbpartitionnum , datapartitionid )

表函数 ADMIN_GET_INDEX_COMPRESS_INFO 需要以下参数：

Objecttype 。取值为 'T'、 NULL 或者空字符串表示表。如果取值为 'I' 则表示索引。
objectschema 。如果 Objecttype 为表，objectschema 则表示是表的模式，如此次数 objectname 为空，则表示指定模式下的所有表的所有索引，否则为指定表的索引。如果 Objecttype 为索引，objectschema、objectname 则指具体的索引。
objectname 。对象名称，具体含义见上面描述。
dbpartitionnum 。多分区数据库下使用，指定分区号，单分区数据库设置为 -2 或 NULL 。
datapartitionid 。分区索引的分区 ID 。使用 datapartitionid 限制仅返回指定数据分区的索引信息。如果希望返回分区所有的所有分区中的数据，设置该值为 -2 或 NULL 。对非分区索引设置为 -2,0 或者 NULL 。

示例：

db2 SELECT  substr(TABNAME,1,18),substr(INDNAME,1,18),iid, compress_attr, 
 index_compressed, pct_pages_saved, num_leaf_pages_saved 
 FROM TABLE(sysproc.admin_get_index_compress_info('', 'DB2ADIN', 'T1', -2, -2)) AS t 

 TNAME I_NAME IID  COMPRESS_ATTR INDEX_COMPRESSED PCT_PAGES_SAVED NUM_LEAF_PAGES_SAVED 
 ------ ------------- ------------ -------------- ------------ -------------- 
 T1 INX_COL2 1 N N 7  10 
 T1 IDX_COL1 2 N N 66 74

上面查询语句中 Objecttype 为 NULL，表示 objectschema、objectname 指定的是表，在本查询中为 db2admin.t1 。由于我们的数据库、索引均未分区，则 dbpartitionnum、datapartitionid 均指定为 -2 。

查询结果表明 T1 表未启动压缩，它的两个索引 IDX_COL1、IDX_COL2 也未启动压缩。索引评估表明如果我们对 IDX_COL1 启动压缩，则压缩比能达到 66 ％，可以节约 74 个页面；如果我们对 IDX_COL2 启动压缩，则压缩比能达到 7 ％，可以节约 10 个页面。

上面例子显示的是表函数 ADMIN_GET_INDEX_COMPRESS_INFO 对未压缩的索引评估结果。

示例 2：

db2 SELECT  substr(TABNAME,1,18) tname,substr(INDNAME,1,18) i_name,iid, 
 compress_attr, index_compressed, pct_pages_saved, num_leaf_pages_saved 
 FROM TABLE(sysproc.admin_get_index_compress_info('', 'DB2ADMIN', 'T2', -2, -2)) AS t 

 TNAME I_NAME IID  COMPRESS_ATTR INDEX_COMPRESSED PCT_PAGES_SAVED NUM_LEAF_PAGES_SAVED 
  ------ ------------- ------------ -------------- ------------ -------------- 
 T2 IDX_T2_COL1 1 Y Y -1  -1

上面查询显示 T2 表启动了压缩，它的索引 IDX_T2_COL1 也启动了压缩。由于未收集统计信息，压缩比例和节约页面为 -1 。

db2 runstats on table DB2ADMIN.T2 for indexes all 

 db2 SELECT  substr(TABNAME,1,18) tname,substr(INDNAME,1,18) i_name,iid, 
 compress_attr, index_compressed, pct_pages_saved, num_leaf_pages_saved 
 FROM TABLE(sysproc.admin_get_index_compress_info('', 'DB2ADMIN', 'T2', -2, -2)) AS t 

 TNAME I_NAME IID  COMPRESS_ATTR INDEX_COMPRESSED PCT_PAGES_SAVED NUM_LEAF_PAGES_SAVED 
  ------ ------------- ------------ -------------- ------------ -------------- 
 T2 IDX_T2_COL1 1 Y Y 40  5

上述结果表明，收集过表 T2 的统计信息后，索引 IDX_T2_COL1 显示的压缩比是 40%，节约了 5 个页面。

索引压缩应用场景

DB2 V9.7 索引压缩新特性的效果是通过压缩比来衡量的，而压缩比的大小是与数据分布密切相关的。

我们下面测试即将创建一个表 test，它有两个列 col1，col2 。表 test 有 10 万行数据，列 col1 重复值比较多，只有 25 个不同值，而列 col2 重复值比较少，有 31154 取值。

创建 Test 表脚本：

drop table test; 
 CREATE TABLE test 
 ( 
  col1 int, 
  col2 int, 
  padding char(50) 
 ); 

 INSERT INTO test (col1, col2,padding) 
 WITH TEMP (COUNTER, col1, col2,padding) AS 
 ( 
  VALUES (0, MOD(INT(RAND() * 1000), 25),MOD(INT(RAND() * 100000), 99999),'A') 
  UNION ALL 
  SELECT 
  (COUNTER + 1),MOD(INT(RAND() * 1000), 25),MOD(INT(RAND() * 100000), 100000),'A' 
  FROM 
  TEMP 
  WHERE 
  (COUNTER + 1) < 100000 
 ) 
 SELECT 
  col1, col2,padding 
 FROM 
  TEMP 
 ;

下面是表、列的统计信息

db2 "select count(*) from test" 

 1 
 ----------- 
  100000 

 db2 "select count(*) from (select distinct col1 from test) t" 

 1 
 ----------- 
  25 

 db2 "select count(*) from (select distinct col2 from test) t" 

 1 
 ----------- 
  31154

我们在 test 表 col1 上创建索引，但不启动压缩，然后使用 db2exfmt 工具查看 Select 语句的执行成本。

db2 "create index idx_test_col1 on test(col1)" 
 db2 "alter index idx_test_col1 compress no" 
 db2 "reorg indexes all for table db2admin.test  " 
 db2 "runstats on table db2admin.test and indexes all" 
 db2 set current explain mode explain 
 db2 "select count(*) from test" 
 db2exfmt -d sample  -w -1 -n % -s % -# 0 -t 

 Total Cost:  287.387 
 Query Degree:  1 
	      Rows
	     RETURN
	     (   1)
	      Cost
	       I/O
	       |
		1
	     GRPBY
	     (   2)
	     287.387
	     158.623
	       |
	     100000
	     IXSCAN
	     (   3)
	     278.825
	     158.623
	       |
	     100000
	 INDEX: USERID
	  IDX_TEST_COL1
	       Q1

执行计划表明在索引未压缩情况下，从索引 IDX_TEST_COL1 统计表 test 行数的 Select 语句的 IO 成本估计是 158.623，总成本估计是 287.387 。索引 IDX_TEST_COL1 的统计信息显示其占用 111 叶子页面，索引层数是 2 。

我们在 test 表 col1 上创建索引，启动压缩，然后使用重新 db2exfmt 工具查看 Select 语句的执行成本。

db2 "create index idx_test_col1 on test(col1)" 
 db2 "alter index idx_test_col1 compress yes" 
 db2 "reorg indexes all for table db2admin.test  " 
 db2 "runstats on table db2admin.test and indexes all" 
 db2 set current explain mode explain 
 db2 "select count(*) from test" 
 db2exfmt -d sample  -w -1 -n % -s % -# 0 -t 
 Total Cost:  145.487 
 Query Degree:  1 
 
                          Rows
	     RETURN
	     (   1)
	      Cost
	       I/O
	       |
		1
	     GRPBY
	     (   2)
	     145.487
	     31.0417
	       |
	     100000
	     IXSCAN
	     (   3)
	     136.925
	     31.0417
	       |
	     100000
	 INDEX: USERID
	  IDX_TEST_COL1
	       Q1

执行计划表明在索引启动压缩情况下，从索引 IDX_TEST_COL1 统计表 test 行数的 Select 语句的 IO 成本估计是 31.0417，总成本估计是 145.487 。索引 IDX_TEST_COL1 的统计信息显示其占用 30 叶子页面，索引层数是 2 。启动压缩带来 IO 成本降低约 80 ％，总成本降低约 49 ％，性能优化效果明显。

我们再来看对 col2 建立索引启动压缩是否能够带来与 col1 相同的性能优势呢？

首先我们查看未启动压缩时的情况。

db2 "create index idx_test_col2 on test(col2)" 
 db2 "alter index idx_test_col2 compress no" 
 db2 "reorg indexes all for table db2admin.test  " 
 db2 "runstats on table db2admin.test and indexes all" 
 db2 set current explain mode explain 
 db2 "select count(*) from test" 
 db2exfmt -d sample  -w -1 -n % -s % -# 0 -t 
 Total Cost:  319.439 
 Query Degree:  1 
  
	      Rows
	     RETURN
	     (   1)
	      Cost
	       I/O
	       |
		1
	     GRPBY
	     (   2)
	     319.439
	     191.667
	       |
	     100000
	     IXSCAN
	     (   3)
	     310.878
	     191.667
	       |
	     100000
	 INDEX: USERID
	  IDX_TEST_COL2
	       Q1

我们看到在索引未压缩情况下，从索引 IDX_TEST_COL2 统计表 test 行数的 Select 语句的 IO 成本估计是 191.667，总成本估计是 319.439 。索引 IDX_TEST_COL 的统计信息显示其占用 145 叶子页面，索引层数是 2 。

db2 "create index idx_test_col2 on test(col2)" 
 db2 "alter index idx_test_col2 compress yes" 
 db2 "reorg indexes all for table db2admin.test  " 
 db2 "runstats on table db2admin.test and indexes all" 
 db2 set current explain mode explain 
 db2 "select count(*) from test" 
 db2exfmt -d sample  -w -1 -n % -s % -# 0 -t 
 Total Cost:  330.963 
 Query Degree:  1 
 
	      Rows
	     RETURN
	     (   1)
	      Cost
	       I/O
	       |
		1
	     GRPBY
	     (   2)
	     330.962
	     198.548
	       |
	     100000
	     IXSCAN
	     (   3)
	     322.401
	     198.548
	       |
	     100000
	 INDEX: USERID
	  IDX_TEST_COL2
	       Q1

我们看到启动索引 IDX_TEST_COL2 压缩后，从索引 IDX_TEST_COL1 统计表 test 行数的 Select 语句的 IO 成本估计是 198.548，总成本估计是 330.963 。索引 IDX_TEST_COL1 的统计信息显示其占用 125 叶子页面，索引层数是 2 。启动压缩带来 IO 成本反而增加约 3 ％，总成本增加约 3 ％，性能优化效果不明显。

为什么会出现上面的情况呢？主要就在于 col1 和 col2 的分布不同，col1 中有很多重复值，压缩效果就好，对于重复值不多的列效果就没有那么好了。这种特性可以帮助我们在特定场合推荐使用索引压缩，如员工表的 SEX 列、EDLEVEL 列，他们取有限的几个值，存在大量重复行就可以适用索引压缩技术。

总结

DB2 V9.7 的索引压缩特性是在表压缩的进一步发展，减少了磁盘存储空间要求，节约了 IT 成本。同时，由于 I/O 性能和内存利用效率的改善，系统的性能也得到了提升。不过在使用索引压缩时并不是所有情况下都能获得较高的压缩比和较好的性能，需要用户在进行测试后作出决策。

feier7501

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
DB2 V9.7 索引压缩新特性的使用

http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0907luohq2/DB2 V9.1 提出了行压缩技术，当我们的系统中数据量很大，IO 需求超过了磁盘系统提供的容量（即 IO 成为系统的瓶颈）时，行压缩技术能够有效的减少读写磁盘的次数。 DB2 9.7 更进一步提出了索引压缩技术，减少索引磁盘空间
复制链接

扫一扫