DB2数据库中提高INSERT性能详解

2. 发送列值到服务器

可以归为这一类的优化技巧有好几种。最重要的一种技巧是在每条 insert 语句中包括多行，这样就可以避免对于每一行都进行客户机-服务器通信，同时也减少了 DB2 开销。可用于多行插入的技巧有：

在 VALUES 子句中包含多行的内容。例如，下面的语句将插入三行：INSERT INTO mytable VALUES (1, 'abc'), (2, 'def'), (3, 'ghi') 在 CLI 中使用数组插入（array insert）。这需要准备一条带参数标记的 INSERT 语句，定义一个用于存储要插入的值的数组，将该数组绑定到参数标记，以及对于每个数组中的一组内容执行一次 insert。而且，示例程序 sqllib/samples/cli/tbload.c 提供了数组插入的基本框架（但是执行的是 CLI LOAD）。从不使用数组改为使用包含 100 行的数组，可以将时间缩短大约 2.5 倍。所以应该尽可能地使用包含至少 100 行的数组。

在 JDBC 中使用批处理操作。这跟 CLI 中的数组插入一样，基于相同的概念，但是实现细节有所不同。当通过 prepareStatement 方法准备了 insert 语句之后，剩下的步骤是针对每一列调用适当的 setXXXX 方法（例如，setString 或 setInt），然后是 addBatch。对于要插入的每一行，都要重复这些步骤，然后调用 executeBatch 来执行插入。要查看这方面的例子，请参阅“参考资料”一节中的 JDBC Tutorial。

使用 load 将数据快速地装入到一个 staging 表中，然后使用 INSERT ... SELECT 填充主表。（通过这种方法节省下来的代价源于 load 的速度非常快，再加上 INSERT ... SELECT 是在 DB2 内（在服务器上）传输数据的，从而消除了通信上的代价。一般情况下我们不会使用这种方法，除非在 INSERT ... SELECT 中还要另外做 load 无法完成的处理。

如果不可能在一条 insert 语句中传递多行，那么最好是将多条 insert 语句组成一组，将它们一起从客户机传递到服务器。（不过，这意味着每条 insert 都包含不同的值，都需要准备，因而其性能实际上要比使用参数标记情况下的性能更差一些。）将多条语句组合成一条语句可以通过 Compound SQL 来实现。在 SQL 中，复合语句是通过 BEGIN ATOMIC 或 BEGIN COMPOUND 语句创建的。在 CLI 中，复合语句可以通过 SQLExecDirect 和 SQLExecute 调用来建立。对于 DB2 V8 FixPak 4，另一种生成复合语句的方法是在（对一条预处理语句）发出多个 SQLExecute 调用之前设置语句属性 SQL_ATTR_CHAINING_BEGIN，并在调用之后设置语句属性 SQL_ATTR_CHAINING_END。

下面是关于该话题的其他一些建议：

如果可能的话，让客户机与要存取的数据库使用相同的代码页，以避免在服务器上的转换代价。数据库的代码页可以通过运行“get db cfg for ”来确定。

在某些情况下，CLI 会自动执行数据类型转换，但是这样同时也会带来看不见的（小小的）性能损耗。因此，尽量使插入值直接处于与相应列对应的格式。

将应用程序中与插入相关的设置开销最小化。例如，当在 CLI 中使用数组插入时，对于整个一组插入，应该尽量保证对于每一列只执行一次 SQLBindParameter，而不是对每一组数组内容都执行一次。对于个体来说，这些调用的代价并不高，但是这些代价是累积的。

3. 找到存储行的地方

DB2 使用三种算法中的一种来确定将行插入到哪里。（如果使用了多维群集（Multi-dimensional Clustering，MDC），则另当别论，我们在这里不予讨论。）

缺省模式是，DB2 搜索散布在表的各页上的自由空间控制记录（Free Space Control Records，FSCR），以找到有足够自由空间存放新行的页。显然，如果每页上的自由空间都比较少的话，就要浪费很多的搜索时间。为了应付这一点， DB2 提供了 DB2MAXFSCRSEARCH 注册表变量，以便允许将搜索范围限制为少于缺省的 5 页。

当表是通过 ALTER TABLE 以 APPEND 模式放置时，就要使用第二种算法。这样就完全避免了 FSCR 搜索，因为只需简单地将行直接放到表的末尾。

当表有群集索引（clustering index）时，就要用到最后一种算法。在这种情况下，DB2 试图将每一行插入到有相似键值的一页中。如果那一页没有空间了，DB2 就会尝试附近的页，如果附近的页也没有空间，DB2 就进行 FSCR 搜索。

如果只考虑插入时间的优化，那么使用 APPEND 模式对于批量插入是最快的一种方法，但是这种方法的效果远不如我们这里讨论的很多其他方法那么成效显著。第二好的方法应该是采用缺省算法，但是，如果在最佳环境中，更改 DB2MAXFSCRSEARCH 的值影响很小，而在一个 I/O 约束较少的环境中，这种更改所造成的影响就比较可观了。

如果有群集索引，则对 insert 的性能会有很大的负面影响，这一点也不惊奇，因为使用群集索引的目的就是通过在插入时做额外的工作来提高查询（即 select）性能的。如果的确需要群集索引，那么可以通过确保有足够的自由空间来使其对插入的影响降至最小：使用 ALTER TABLE 增加 PCTFREE，然后使用 REORG 预留自由空间。不过，如果允许太多自由空间的存在，则可能导致查询时需要读取额外的页，这反而大大违反了使用群集索引的本意。另一种选择是，在批量插入之前先删除群集索引，而后再重新创建群集索引，也许这是最优的方法（创建群集索引的开销跟创建常规索引的开销差不多，都不是很大，只是在插入时有额外的开销）。

4. 缓冲池、I/O 和页清除

每一条 insert 在执行时，都是先将新行存储在一个页中，并最终将那个页写到磁盘上。一旦像前面讨论的那样指定了页，那么在将行添加到该页之前，该页必须已经在缓冲池中。对于批量插入，大部分页都是最新指派给表的，因此让我们关注一下对新页的处理。

如果表在系统管理存储的（System Managed Storage，SMS）表空间中，当需要新页时，缺省情况下是从文件系统中分别为每一页分配空间。但是，如果对数据库运行了 db2empfa 命令，那么每个 SMS 表空间就会为新页一次性分配一个区段。我们建议运行 db2empfa 命令，并使用 32 页的区段。

对于数据库管理的存储（Database Managed Storage，DMS）表空间，空间是在创建表空间时就预先分配的，但是页的区段则是在插入处理过程中指派给表的。与 SMS 相比，DMS 对空间的预分配可以提高大约 20% 的性能 -- 使用 DMS 时，更改区段大小并没有明显的效果。

如果表上有索引，则对于每个插入的行，都要添加一个条目到每条索引。这要求在缓冲池中存在适当的索引页。晚些时候我们将讨论索引的维护，但是现在只需记住，插入时对缓冲池和 I/O 的考虑也类似地适用于索引页，对于数据页也是一样。

随着插入的进行，越来越多的页中将填入被插入的行，但是，DB2 不要求在 insert 或 Commit 后将任何新插入的或更新后的数据或索引写入到磁盘。（这是由于 DB2 的 writeahead 日志记录算法。但是有一个例外，这将在关于日志记录的小节中论述到。）然而，这些页需要在某一时刻写到磁盘上，这个时刻可能会在数据库关闭时才会轮到。

一般来说，对于批量插入，您会希望积极地进行 异步页清除（asynchronous page cleaning），这样在缓冲池中就总有可用于新页的空余位置。页清除率，或者说总缺页率，可能导致计时上的很大不同，使得性能比较容易产生误解。例如，如果使用 100,000 页的缓冲池，并且不存在页清除，则批量插入在结束前不会有任何新的或更改过的（“脏的”）页写到磁盘上，但是随后的操作（例如选择，甚至乎关闭数据库）都将被大大推迟，因为这时有至多 100,000 个在插入时产生的脏页要写到磁盘上。另一方面，如果在同一情况下进行了积极的页清除，则批量插入过程可能要花更长的时间，但是此后缓冲池中的脏页要少一些，从而使得随后的任务执行起来性能更佳。至于那些结果中到底哪个要更好些，我们并不是总能分得清，但是通常来说，将所有脏页都存储在缓冲池中是不可能的，所以为了取得最佳性能，采取有效的页清除是有必要的。

为了尽可能好地进行页清除：将 CHNGPGS_THRESH 数据库配置参数的值从缺省的 60 减少到 5 这么低。这个参数决定缓冲池中脏页的阈值百分比，当脏页达到这个百分比时，就会启动页清除。

尝试启用注册表变量 DB2_USE_ALTERNATE_PAGE_CLEANING（在 DB2 V8 FixPak 4 中最新提供）。通过将这个变量设置成 ON，可以为页清除提供一种比缺省方法（基于 CHNGPGS_THRESH 和 LSN 间隙触发器）更积极的方法。我没有评测过其效果。请参阅 FixPak 4 Release Notes 以了解这方面的信息。确保 NUM_IOCLEANERS 数据库配置参数的值至少等于数据库中物理存储设备的数量。

至于 I/O 本身，当需要建立索引时，可以通过使用尽可能大的缓冲池来将 I/O 活动减至最少。如果不存在索引，则使用较大的缓冲池帮助不大，而只是推迟了 I/O。也就是说，它允许所有新页暂时安放在缓冲池中，但是最终仍需要将这些页写到磁盘上。