生产数据库性能优化之reorg和表重建

生产数据库的性能调优是一个系统的工程，它不仅要求DBA熟悉db2的工作原理和各种性能指标，还要求DBA也要熟悉操作系统、存储等其他知识，甚至要求DBA熟悉应用的设计原理以及它使用数据库的方式等。这里，我们只就生产数据库性能优化之reorg和表重建进行初步的探讨。

1. reorg的作用

要进行高效率的数据访问和获得最佳工作负载性能，具有组织良好的表数据是很关键的。在对表数据进行许多更改之后，逻辑上连续的数据可能会位于不连续的物理数据页上，比如说对某个表新增一个字段，或者表的某列是可变长度的，这样在插入或者更新操作时有可能会导致行溢出。另外，在删除大量行后，也会造成表空间的数据碎片。这些情况下都会降低数据的访问速度，从而影响到数据库的性能。

表重组操作会整理数据碎片，降低表空间高水位，减少浪费的空间，并对行进行重新排序以合并溢出记录，从而加快数据访问速度并最终提高查询性能。还可以指定根据特定索引来重新排序数据，以便查询通过最少次数据读取操作就可以访问数据。既可重组系统目录表，也可以重组数据库表。

考虑以下因素，以确定是否应重组表： 

· 对查询所访问的表进行了大量的插入、更新和删除活动

· 对于使用具有高集群率的索引的查询，其性能发生了明显变化

· 在执行 RUNSTATS 以刷新统计信息后，性能没有得到改善

· REORGCHK 命令指示需要重组表

· 综合考虑查询性能不断降低所浪费的成本和重组表所需的成本（包括 CPU 时间、经过的时间和 REORG 实用程序在完成重组操作之前锁定表造成的并行性降低），以确定是否进行表重组。

2. 生产数据库要不要做reorg

DB2提供了在线或离线执行reorg的选项。默认情况下，离线 REORG 允许其他用户读这个表，ALLOW READ ACCESS 是默认选项。可以通过指定 ALLOW NO ACCESS 选项来限制对表的访问。在线 REORG （也称inplace REORG）支持对表的读或写访问，ALLOW WRITE ACCESS 是默认选项。

     对于生产数据库，如果有运维时间窗口，建议执行离线的reorg，并且在执行离线reorg时使用临时表空间（-USE--tbspace-name-）。对于没有运维时间窗口的，可以尝试做在线的reorg，但是因为在线的reorg时间会特别的长，需要人为控制，避开业务高峰期。

3. 生产数据库做reorg的步骤

生产数据库做reorg的步骤：REORGCHK->REORG->RUNSTATS

reorg的同时，可以用db2pd -db ibps -reorgs 查看阶段和进度

4. 表重建的应用和步骤

之所以要提出表重建的方案，是因为在实际中，存在这样的情况，比如针对某个7*24小时业务系统的表TBL_LMSGLOG，为了减少给该表“瘦身”，会不定期的将表中的数据导出，并导入到归档库，然后删除已经归档成功的数据，生产库的表只保留近3个月的数据。

针对这种情况，我们选择在维护时间段（需要特殊申请）进行离线reorg，该表的总大小为240GB左右，每次reorg都需要将近1个小时，针对这个表的特点，也就是说每次删除的数据量比较大，跟剩余的数据量处于同一数量级，我们可以选择对该表进行重建，并且重建后的表放在单独的表空间中，通过数据迁移来实现对表的维护，同样达到reorg的目的，数据迁移的方式也有很多，比较快的方式是利用load cursor，数据不落地。

 重建表的步骤，比如原表空间为A_TBSPACE, 原表为A_TABLE

1) 利用db2look导出A_TABLE的表定义和索引定义

2) 新建表空间B_TBSPACE

3) 在B_TBSPACE中按照A_TABLE的表定义新建表B_TABLE

4) 利用load cursor方式实现从A_TABLE到B_TABLE的数据迁移

select current time from syscat.tables fetch first 1 rows only;

DECLARE c1 CURSOR FOR SELECT * FROM A_TABLE; 

LOAD FROM c1 of CURSOR INSERT INTO B_TABLE NONRECOVERABLE; 

select current time from syscat.tables fetch first 1 rows only;

5) 删除原表A_TABLE的主键和索引，将A_TABLE重名为A_TABLE_BAK, 

6) 将表B_TABLE重命名为A_TABLE，并重建A_TABLE的主键和索引

7) 检查数据有效性，如有失效的package，需要rebind一下。

验证表或试图的状态

db2 "select substr(tabschema,1,10) as tabschema,substr(tabname,1,30) as tabname,status from syscat.tables where status != 'N' with ur"

验证package的状态

db2 "select substr(PKGSCHEMA,1,10) as PKGSCHEMA,substr(PKGNAME,1,30) as PKGNAME,VALID from syscat.PACKAGES  where VALID !='Y' with ur"

验证routine的状态

db2 "select substr(ROUTINESCHEMA,1,10) as ROUTINESCHEMA,substr(ROUTINENAME,1,30) as ROUTINENAME,ROUTINETYPE,VALID from syscat.ROUTINES  where VALID!='Y' and VALID !='' with ur"

8) 对表A_TABLE执行RUNSTATS

5. 具体例子的对比

下表是我们实际维护过程中的对比情况，总耗时差不多，均为50分钟左右。

6. 其他思路

虽然上面的两种思路都实现了表的优化维护，但是还有没有其他思路呢？

另外一种比较好的思路是根据表的字段情况，可以按照时间段将原表改造成分区表，每个月的数据建立一个数据分区，这样删除三个月之前数据就可以直接detach对应的分区，在db2V9.7之前的话，detach后还需要对全局索引进行维护，如果数据库版本是db2 V 9.7的话，可以将索引移植成分区索引，每个分区索引由多个索引分区（index partition）组成，每个索引分区只对相应的数据分区（data partition）的数据作索引。

其实在db2V9.7还有一个比较好的功能，就是可以对分区表的单个分区进行表和索引重组，这个新特性使得用户可以仅对某一个分区进行重组，并仅在该分区上对其它事务的读写权限进行限制。这样可以最大程度的缩小重组命令对其他事务的影响，提高事务的并发度。这样当用户使用分区重组时，在表上没有非分区索引的情况下，重组命令将完全只在一个分区上进行，从而对其他分区上的事务没有任何影响。

这里只简单列一下分区重组的相关选项，供参考。

db2 V9.7分区重组命令 REORG TABLE 里各选项搭配的实现效果

7. 小结

本文简述了生产数据库性能优化的两种常见手段：reorg和表重建，这两种方法无优劣之分，这里只是提供一些思路供生产数据库维护中采用。至于实际操作过程中采取哪种手段，则需要根据表的具体情况而定。

本文节选自《数据库与信息治理》，作者：民生银行  胡经伟