深入了解DB2 reorg

一、概述

数据库性能调优是一个系统的工程，它不仅要求DBA熟悉DB2的工作原理和各种性能指标，还要求DBA也要熟悉操作系统、存储等其他知识，甚至要熟悉应用的设计原理及其使用数据库的方式等。本文只就reorg进行初步探讨。
要进行高效的数据访问和获得最佳工作负载性能，具有组织良好的表数据是关键。在对表数据进行许多更改之后，逻辑上连续的数据可能位于不连续的物理数据页上，比如对某张表新增一个字段，或者表的某列是可变长度的(DB2可识别的可变长度的数据类型包括：Varchar/Long/Vargraphic/Blob/Clob/Declob/Xml),这样在插入或者更新操作时就有可能导致行溢出【数据存在真正的数据页面之外，一般认为Blob/Long这类的大对象列类型的存储会把数据存放在数据页面之外。但是，这个理解有点偏差，Blob可以不将数据存放在溢出页面，而即使是Varchar列数据类型，依然有可能存放溢出数据。DB2喜欢MySql的Varchar类型，因为相对于Oracle的Varchar2最大存放4000个字节，Sql Server最大存放8000个字节，MySql的Varchar最大可存放65535个字节。实际InnoDB存储引擎并不支持65535长度的Varchar，这是因为还有别的开销。因此实际能存放的长度为65532。需要注意的是，如果sql_mode没有设为严格模式，则可能出现可以新建长度为65535的字段，但是会有一条警告消息。InnoDB存储引擎的页为16KB，即16384个字节，怎么能存放65532个字节呢？一般情况下，数据都存放在B-treeNode的页类型中，但是当发生行溢出时，则存放行溢出的页类型为Uncompress Blob Page,实际存放的数据都存放在Blob页中，数据页面其实只保存了 Varchar(65532)的前768个字节的前缀(prefix)数据，之后跟的是偏移量，指向行溢出页，即Uncompress Blob Page】。另外，在删除大量行后，也会造成表空间的数据碎片，这些情况下都会降低数据的访问速度，从而影响数据库的性能。
由于DB2使用CBO【Oracle的优化器有两种优化方式,即基于规则的优化方式(Rule-Based Optimization，简称为RBO)和基于代价的优化方式(Cost-Based Optimization，简称为CBO)，在Oracle8及以后的版本,Oracle强列推荐用CBO的方式。RBO方式:优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走索引。CBO方式:它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、多少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时候过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。Examda提示:主索引不一定就是优的，比如一个表只有两行数据，一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时全表扫描(full table scan)是最好。优化模式包括Rule、Choose、First rows、All rows四种方式。Rule:基于规则的方式。Choose:默认的情况下Oracle用的便是这种方式。指的是当一个表或索引有统计信息，则走CBO的方式，如果表或索引没统计信息，表又不是特别的小，而且相应的列有索引时，那么就走索引，走RBO的方式。FirstRows:它与Choose方式是类似的，所不同的是当一个表有统计信息时，它将是以最快的方式返回查询的最先的几行，从总体上减少了响应时间。All Rows:也就是我们所说的Cost的方式，当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐量。没有统计信息则走RBO的方式。为什么表的某个字段明明有索引，但执行计划却不走索引?1、优化模式是all_rows的方式2、表作过analyze，有统计信息(最可能的就是统计信息有误)3、表很小，上文提到过的，Oracle的优化器认为不值得走索引。】作为数据库的优化器，数据库对象的状态信息对数据库使用合理的 ACCESS PLAN至关重要。DB2 优化器使用目录统计信息来确定任何给定查询的最佳访问方案。如果有关表或索引的统计信息已过时或者不完整，则会导致优化器选择不是最佳的方案，并且会降低 执行查询的速度。当数据库里某个表中的记录变化量很大时，需要在表上做REORG操作来优化数据库性能。

二、What

通过重构行来消除“碎片”数据并压缩信息，对表进行重组。reorg还有一个功能就是可以将表中的数据按照某个索引关键字的顺序排列，从而可以减少某些查询I/O的数量。执行REORG可以考虑分为表上有索引和没有索引两种情况：
a.如表名为DB2INST1.STAFF,索引名为DB2INST1.ISTAFF
reorg table db2inst1.staff index db2inst1.istaff use tempspace1
b.建议REORG时使用USE参数指定数据重排时使用的临时表空间，否则，REORG工作将会在表所在表空间中原地执行.如果表上有多个索引，INDEX参数值请使用最为重要的索引名.
c.表上没有索引：
reorg table db2inst1.staff use tempspace1
reorg table sysibm.systables use tempspace1

三、Why

在对表数据进行许多更改之后，逻辑上连续的数据可能位于不连续的物理数据页上，比如对某张表新增一个字段，或者表的某列是可变长度的,这样在插入或者更新操作时就有可能导致行溢出。另外，在删除大量行后，也会造成表空间的数据碎片，这些情况下都会降低数据的访问速度，从而影响数据库的性能。表重组操作会整理数据碎片来减少浪费的空间，并对行进行重新排序以合并溢出记录，从而加快数据访问速 度并最终提高查询性能。还可以指定根据特定索引来重新排序数据，以便查询通过最少次数据读取操作就可以访问数据。既可重组系统目录表，也可以重组数据库 表。

四、When

对于生产数据库，如果有运维时间窗口，建议执行离线的reorg，并且在执行离线reorg时使用临时表空间（-USE–tbspace-name-）。对于没有运维时间窗口的，可以尝试做在线的reorg，但是因为在线的reorg时间会特别的长，需要人为控制，避开业务高峰期。 以脱机方式重组表是整理表碎片的最快方法。重组可减少表所需的空间量并提高数据访问和查询性能。

五、How

生产数据库做reorg的步骤：REORGCHK->REORG->RUNSTATS ，reorg的同时，可以用db2pd -db ibps -reorgs 查看阶段和进度。
DB2 优化器使用目录统计信息来确定任何给定查询的最佳访问方案。如果有关表或索引的统计信息已过时或者不完整，则会导致优化器选择不是最佳的方案，并且会降低 执行查询的速度。但是，决定要为给定的工作负载收集哪些统计信息是很复杂的事情，并且使这些统计信息保持最新是一项很花费时间的任务。以往，建议对一个频繁大量更新、插入或者删除操作的表进行 RUNSTATS，建议在重组表之后运行 RUNSTATS 以确保收集并维护正确的统计信息。
1 RUNSTATS
由于在第二步中REORGCHK时可以对指定的表进行RUNSTATS操作(在REORGCHK时指定UPDATE STATISTICS)，所以第一步事实上是可以省略的。
2 REORGCHK
REORGCHK 命令返回有关数据组织的统计信息，并且可以建议您是否需要重组特定表。
可以分为对系统表和用户表两部分分别进行REORGCHK：
1) 针对系统表进行REORGCHK
db2 reorgchk update statistics on table system
使用UPDATE STATISTICS参数指定数据库首先执行RUNSTATS命令。
2) 针对用户表进行REORGCHK
db2 reorgchk update statistics on table user
REORGCHK是根据统计公式计算表是否需要重整。对于每个表有3个统计公式，对索引有3个统计公式(版本8开始有5个公式)，如果公式计算结果该表需重整，在输出的REORG字段中相应值为，否则为-。
reorgchk 所使用的度量的考虑因素包括：（当查看 reorgchk 工具的输出时，找到用于表的 F1、F2 和 F3 这几列，以及用于索引的 F4、F5、F6、F7 和 F8 这几列。如果这些列中的任何一列有星号 ()，则说明当前的表和/或索引超出了阈值。）
F1: 属于溢出记录的行所占的百分比。当这个百分比大于 5% 时，在输出的 F1 列中将有一个星号 ()。
F2: 数据页中使用了的空间所占的百分比。当这个百分比小于 70% 时，在输出的 F2 列上将有一个星号 ()。
F3: 其中含有包含某些记录的数据的页所占的百分比。当这个百分比小于 80% 时，在输出的 F3 列上将有一个星号 ()。
F4: 群集率，即表中与索引具有相同顺序的行所占的百分比。当这个百分比小于 80% 时，那么在输出的F4 列上将有一个星号 ()。
F5: 在每个索引页上用于索引键的空间所占的百分比。当这个百分比小于 50% 时，在输出的 F5 列上将有一个星号 ()。
F6: 可以存储在每个索引级的键的数目。当这个数字小于 100 时，在输出的 F6 列上将有一个星号 ()。
F7: 在一个页中被标记为 deleted 的记录 ID（键）所占的百分比。当这个百分比大于 20% 时，在输出的 F7 列上将有一个星号 ()。
F8: 索引中空叶子页所占的百分比。当这个百分比大于 20% 时，在输出的 F8 列上将有一个星号 ()。

建议(待实现)

可以通过一些工具(文本解析)将REORGCHK输入的需要reorg的表通过自动化的方式自动生成相应脚本。