在 DB2 Universal Database 中提高 INSERT 性能的技巧

WindySky 2007-11-14

展开全文

本文精妙地解释了执行 insert 操作时所发生的事情，考察了 insert 的一些替代方案，并研究了影响 insert 性能的一些问题，例如锁、索引维护以及约束管理。

在使用 DB2® Universal Database (UDB) 的时候，行的插入是我们要执行的最常见、也是最重要的任务之一。本文是关于优化 insert 、尤其是插入量比较大的 insert 的一个技巧汇编。谈到性能，往往都存在着某些权衡，这里也不例外。我们将讨论在优化 insert 的过程中可能带来的权衡问题。例如，您对 insert 采用了某种优化技巧，但是这种技巧可能要求在 insert 之后还要进行附加的处理，或者可能影响查询的性能。我会提供一些性能测试的结果，以便让您了解很多优化技巧的作用。附录 A包含了对这些结果的一个小结，并且编了号。我将在全文各处以测试编号来引用结果。在后面的结束语一节中，总结了大部分有益的技巧，而附录 B则列出了本文给出的所有建议。在本文中，我们无意研究关于如何实现这些技巧的细节，但是这方面的信息可以在 DB2 手册中找到。请阅读参考资料一节，以了解更多细节。

回页首

INSERT 处理过程概述

首先让我们快速地看看插入一行时的处理步骤。这些步骤中的每一步都有优化的潜力，对此我们在后面会一一讨论。

在客户机准备语句。对于动态 SQL，在语句执行前就要做这一步，此处的性能是很重要的；对于静态 SQL，这一步的性能实际上关系不大，因为语句的准备是事先完成的。
在客户机，将要插入的行的各个列值组装起来，发送到 DB2 服务器。
DB2 服务器确定将这一行插入到哪一页中。
DB2 在用于该页的缓冲池中预留一个位置。如果 DB2 选定的是一个已有的页，那么就需要读磁盘；如果使用一个新页，则要在表空间（如果是 SMS，也就是系统管理存储的表空间）中为该页物理地分配空间。插入了新行的每一页最后都要从缓冲池写入到磁盘。
在目标页中对该行进行格式化，并获得该行上的一个 X（exclusive，独占的）行锁。
将反映该 insert 的一条记录写入到日志缓冲区中。
最后提交包含该 insert 的事务，如果这时日志缓冲区中的记录还没有被写入日志文件的话，则将这些记录写到日志文件中。

此外，还可能发生很多类型的附加处理，这取决于数据库配置，例如，索引或触发器的存在。这种额外的处理对于性能来说也是意义重大的，我们在后面会讨论到。

回页首

insert 的替代方案

在详细讨论 insert 的优化之前，让我们先考虑一下 insert 的两种替代方案：load 和 import。import 实用程序实际上是 SQL INSERT 的一个前端，但它的某些功能对于您来说也是有用的。load 也有一些有用的额外功能，但是我们使用 load 而不使用 insert 的主要原因是可以提高性能。这里我们不会进一步讨论 import，不过读者可以参阅后面的参考资料一节，在那里可以找到指向 Data Movement Utilities Guide 的链接，该指南讨论了 import 与 load 之间的不同之处。

load 直接格式化数据页，而避免了由于插入导致的对每一行进行处理的大部分开销（例如，日志记录在这里实际上是消除了）。而且，load 可以更好地利用多处理器机器上的并行性。在 V8 load 中有两个新功能，它们对于 load 成为 insert 的替代方案有着特别的功效，这两个功能是：从游标装载和从调用层接口（CLI）应用程序装载。

从游标装载

这种方法可用于应用程序的程序代码（通过 db2Load API），或用于 DB2 脚本。下面是后一种情况的一个例子：
declare staffcursor cursor forselect * from staff; load from staffcursor of cursor insert into myschema.new_staff;
这两行可以用下面一行替代：
insert into myschema.new_staff select * from staff
附录 A中的 test 6 和 9 表明，同等效的 INSERT ... SELECT 语句相比，从游标装载几乎可以提高 20% 的性能。

从 CLI 装载

这种方法显然只限于调用层接口（CLI）应用程序，但是它非常快。这种技巧非常类似于数组插入（后面会讨论），DB2 附带了这样的示例，请查看 sqllib/samples/cli/tbload.c。通过查看附录 A 中的 test 79我们可以看到，使用 load 时的速度是使用经过完全优化的数组插入（test 71）时的两倍，几乎要比未经优化的数组插入（比如 test 69）快 10 倍。

回页首

所有 insert 可以改进的地方

让我们看看插入处理的一些必要步骤，以及我们可以用来优化这些步骤的技巧。

1. 语句准备

作为一条 SQL 语句，INSERT 语句在执行之前必须由 DB2 进行编译。这一步骤可以自动发生（例如在 CLP 中，或者在一次 CLI SQLExecDirect 调用中），也可以显式地进行（例如，通过一条 SQL Prepare、CLI SQLPrepare 或 JDBC prepareStatement 语句）。该编译过程牵涉到授权检查、优化，以及将语句转化为可执行格式时所需的其他一些活动。在编译语句时，语句的访问计划被存储在包缓存中。

如果重复地执行相同的 INSERT 语句，则该语句的访问计划（通常）会进入到包缓存中，这样就免除了编译的开销。然而，如果 insert 语句对于每一行有不同的值，那么每一条语句都将被看成是惟一的，必须单独地进行编译。因此，将像下面这样的重复语句：
insert into mytable values (1, 'abc') insert into mytable values (2, 'def')
等等，
换成带有参数标记的语句，一次准备，重复执行，这样做是十分可取的：
insert into mytable values (?, ?)

将 test 1与 test 2相比， 61-64与 65-68相比，我们可以看到，使用参数标记可以让一系列的 insert 的运行速度提高数倍。（在静态 SQL 程序中使用主机变量也可以获得类似的好处。）

2. 发送列值到服务器

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

在 VALUES 子句中包含多行的内容。例如，下面的语句将插入三行：INSERT INTO mytable VALUES (1, 'abc'), (2, 'def'), (3, 'ghi')
在 CLI 中使用数组插入（array insert）。这需要准备一条带参数标记的 INSERT 语句，定义一个用于存储要插入的值的数组，将该数组绑定到参数标记，以及对于每个数组中的一组内容执行一次 insert。请参阅 CLI Guide and Reference以了解详细信息。而且，示例程序 sqllib/samples/cli/tbload.c 提供了数组插入的基本框架（但是执行的是 CLI LOAD）。通过比较 test 68（使用了 10x 行）、71、73、74 和 78 各自所耗的时间，我们可以看到，从不使用数组改为使用包含 100 行的数组，可以将时间缩短大约 2.5 倍。所以应该尽可能地使用包含至少 100 行的数组。
在 JDBC 中使用批处理操作。这跟 CLI 中的数组插入一样，基于相同的概念，但是实现细节有所不同。当通过 prepareStatement 方法准备了 insert 语句之后，剩下的步骤是针对每一列调用适当的 setXXXX 方法（例如，setString 或 setInt），然后是 addBatch。对于要插入的每一行，都要重复这些步骤，然后调用 executeBatch 来执行插入。要查看这方面的例子，请参阅“参考资料”一节中的 JDBC Tutorial。
使用 load 将数据快速地装入到一个 staging 表中，然后使用 INSERT ... SELECT 填充主表。（请参阅后面的“ 通过 staging 表和其他方法增加并行性”小节。）通过这种方法节省下来的代价源于 load 的速度非常快，再加上 INSERT ... SELECT 是在 DB2 内（在服务器上）传输数据的，从而消除了通信上的代价。一般情况下我们不会使用这种方法，除非在 INSERT ... SELECT 中还要另外做 load 无法完成的处理。

如果不可能在一条 insert 语句中传递多行，那么最好是将多条 insert 语句组成一组，将它们一起从客户机传递到服务器。（不过，这意味着每条 insert 都包含不同的值，都需要准备，因而其性能实际上要比使用参数标记情况下的性能更差一些，前面“语句准备”一节已对此作了讨论。）将多条语句组合成一条语句可以通过 Compound SQL 来实现：

在 SQL 中，复合语句是通过 BEGIN ATOMIC 或 BEGIN COMPOUND 语句创建的。
在 CLI 中，复合语句可以通过 SQLExecDirect 和 SQLExecute 调用来建立。请参阅 CLI Guide and Reference Volume 1 以了解详细信息。对于 DB2 V8 FixPak 4，另一种生成复合语句的方法是在（对一条预处理语句）发出多个 SQLExecute 调用之前设置语句属性 SQL_ATTR_CHAINING_BEGIN，并在调用之后设置语句属性 SQL_ATTR_CHAINING_END。

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

如果可能的话，让客户机与要存取的数据库使用相同的代码页，以避免在服务器上的转换代价。数据库的代码页可以通过运行“get db cfg for <database>”来确定。
在某些情况下，CLI 会自动执行数据类型转换，但是这样同时也会带来看不见的（小小的）性能损耗。因此，尽量使插入值直接处于与相应列对应的格式。
将应用程序中与插入相关的设置开销最小化。例如，当在 CLI 中使用数组插入时，对于整个一组插入，应该尽量保证对于每一列只执行一次 SQLBindParameter，而不是对每一组数组内容都执行一次。对于个体来说，这些调用的代价并不高，但是这些代价是累积的。

3. 找到存储行的地方

DB2 使用三种算法中的一种来确定将行插入到哪里。（如果使用了多维群集（Multi-dimensional Clustering，MDC），则另当别论，我们在这里不予讨论。）要了解关于插入算法方面的细节，请参阅 DB2 V8 Administration Guide: Performance中提到的相关内容。

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

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

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

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

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

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

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

如果表在系统管理存储的（System Managed Storage，SMS）表空间中，当需要新页时，缺省情况下是从文件系统中分别为每一页分配空间。但是，如果对数据库运行了 db2empfa 命令，那么每个 SMS 表空间就会为新页一次性分配一个区段。test 11 和 test 82 表明，与区段大小为缺省的 32 页的情况相比，运行 db2empfa 命令可以使对 SMS 表空间的插入快到两倍，因为在 test 82 中对于一个页有 32 次分配，而 test 11 中是 32 个页一次分配。test 11 和 test 83-85 表明，如果区段大小小于 32 页，则性能会逐步下降，因为要进行额外的分配，但是让区段大于 32 这样的建议也未必有帮助。我们建议运行 db2empfa 命令，并使用 32 页的区段。

对于数据库管理的存储（Database Managed Storage，DMS）表空间，空间是在创建表空间时就预先分配的，但是页的区段则是在插入处理过程中指派给表的。与 SMS 相比，DMS 对空间的预分配可以提高大约 20% 的性能 -- 请参考 test 11 和 test 81。test 81 使用 DMS 文件表空间，而如果使用了 DMS raw 表空间的话，还可以多得到一点好处。使用 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。也就是说，它允许所有新页暂时安放在缓冲池中，但是最终仍需要将这些页写到磁盘上。

当发生将页写到磁盘的 I/O 时，通过一些常规的 I/O 调优步骤可以加快这一过程，例如：

将表空间分布在多个容器（这些容器映射到不同磁盘）。
尽可能使用最快的硬件和存储管理配置，这包括磁盘和通道速度、写缓存以及并行写等因素。
避免 RAID5（除非是与像 Shark 这样有效的存储设备一起使用）。

5. 锁

缺省情况下，每一个插入的行之上都有一个 X 锁，这个锁是在该行创建时就开始有的，一直到 insert 被提交。有两个跟 insert 和锁相关的性能问题：

为获得和释放锁而产生的 CPU 开销。
可能由于锁冲突而导致的并发问题。

对于经过良好优化的批量插入，由获得每一行之上的一个 X 锁以及后来释放该锁引起的 CPU 开销是比较可观的。对于每个新行之上的锁，惟一可以替代的是表锁（DB2 中没有页锁）。test 11 和 test 101 表明，当使用表锁时，耗时减少了 3%。有 3 种情况可以导致表锁的使用，在讨论表锁的缺点之前，我们先用一点时间看看这 3 种情况：

运行 ALTER TABLE <name> LOCKSIZE TABLE。这将导致 DB2 为随后使用该表的所有 SQL 语句使用一个表锁，直到 locksize 参数改回到 ROW。
运行 LOCK TABLE <name> IN EXCLUSIVE MODE。这将导致表上立即上了一个 X 锁。注意，在下一次提交（或回滚）的时候，这个表将被释放，因此，如果您要运行一个测试，测试中每 N 行提交一次，那么就需要在每次提交之后重复执行 LOCK TABLE。
使用缺省锁，但是让 LOCKLIST 和 MAXLOCKS 数据库配置参数的值比较小。当获得少量的行锁时，行锁就会自动地逐渐升级为表锁。

当然，所有这些的缺点就在于并发的影响：如果表上有一个 X 锁，那么其他应用程序除非使用了隔离级别 UR（未提交的读），否则都不能访问该表。如果知道独占访问不会导致问题，那么就应该尽量使用表锁。但是，即使您坚持使用行锁，也应记住，在批量插入期间，表中可能存在数千个有 X 锁的新行，所以就可能与其他使用该表的应用程序产生冲突。通过一些方法可以将这些冲突减至最少：

确保锁的升级不会无故发生。您可能需要加大 LOCKLIST 和/或 MAXLOCKS 的值，以允许插入应用程序有足够的锁。
对于其他的应用程序，使用隔离级别 UR。
对于 V8 FixPak 4，或许也可以通过 DB2_EVALUNCOMMITTED 注册表变量来减少锁冲突：如果将该变量设置为 YES，那么在很多情况下，只能获得那些符合某个谓词的行上的锁，而并不是获得被检查的所有行上的锁。
发出一个 COMMIT 命令以释放锁，因此如果更频繁地提交的话就足以减轻锁冲突的负担。

注意

在 V7 中，存在涉及 insert 和键锁的并发问题，但是在 V8 中，由于提供了 type-2 索引，这些问题实际上已经不见了。如果要迁移到 V8 中来，那么应该确保使用带 CONVERT 关键字的 REORG INDEXES 命令，以便将索引从 type-1 转换为 type-2。
在 V7 中，插入过程中可能使用 W 或 NW 锁，但是在 V8 中只有在使用了 type-1 索引或者隔离级别为 RR 的情况下才会出现这两种锁。因此，应尽可能避免这两种情况。
一条 insert 所据有的锁（通常是一个 X 锁）通常不会受隔离级别的影响。例如，使用隔离级别 UR 不会阻止从插入的行上获得锁。然而，如果使用了 INSERT ... SELECT，则隔离级别将影响从 SELECT 获得的锁。

6. 日志记录

缺省情况下，每条 insert 都会被记录下来，以用于恢复。日志记录首先被写到内存中的日志缓冲池，然后再写到日志文件，通常是在日志缓冲池已满或者发生了一次提交时写到日志文件的。对批量插入的日志记录的优化实际上就是最小化日志记录写的次数，以及使写的速度尽可能快。

这里首先考虑的是日志缓冲池的大小，这由数据库配置参数 LOGBUFSZ 来控制。该参数缺省值为 8 页或 32 K，这与大多数批量插入所需的理想日志缓冲池大小相比要小些。举个例子，对于一个批量插入，假设对于每一行的日志内容有 200 字节，则在插入了 160 行之后，日志缓冲池就将被填满。如果要插入 1000 行，因为日志缓冲池将被填满几次，再加上提交，所以大概有 6 次日志写。如果将 LOGBUFSZ 的值增加到 64 页（256K）或者更大，缓冲池就不会被填满，这样的话对于该批量插入就只有一次日志写（在提交时）。test 104 和 test 105 表明，通过使用更大的 LOGBUFSZ 可以获得大约 13% 的性能提升。较大日志缓冲池的不利之处是，紧急事故恢复所花的时间可能要稍微长一点。

减少日志写的另一种可能性是对新行要插入到的那个表使用“ALTER TABLE <name> ACTIVATE NOT LOGGED INITIALLY”(NLI)。如果这样做了，那么在该工作单元内不会记录任何 insert 操作，但是这里存在两个与 NLI 有关的重要问题：

如果有一条语句失败，那么这个表将被标记为不可访问的，并且需要被删除掉。这与其他恢复问题（请参阅 SQL Reference 关于 Create Table 的讨论）一起使得 NLI 在很多情况下不能成为可行的方法。
在工作单元最后进行的提交，必须等到在此工作单元内涉及的所有脏页都被写到磁盘之后才能完成。这意味着这种提交要占用大量的时间。实际上， test 6 和 7已表明，如果没有积极地进行页清除，那么在使用 NLI 的情况下，Insert 加上提交所耗费的总时间要更长一些。不过，test 8 表明，将 NLI 与积极的页清除一起使用的时候，可以大大减少耗时。如果使用 NLI，就要瞪大眼睛盯紧提交操作所耗费的时间。

至于提高日志写的速度，有下面一些可能性：

将日志与新行所要插入到的表分别放在不同的磁盘上。
在操作系统层将日志分放到多个磁盘。
考虑为日志使用原始设备（raw device），但是要注意，这样管理起来要更困难些。
避免使用 RAID 5，因为它不适合于写密集型（write-intensive）活动。

7. 提交

提交迫使将日志记录写到磁盘上，以保证提交的插入肯定会存在于数据库中，并且释放新行上的锁。这些都是有价值的活动，但是因为 Commit 总是要牵涉到同步 I/O（对于日志），而 insert 则不会，所以 Commit 的开销很容易高于 insert 的开销。因此，在进行批量插入时，每一行都提交一次的做法对于性能来说是很糟糕的，所以应确保不使用自动提交（对于 CLI 和 CLP 来说缺省情况正是如此）。建议大约每 1000 行提交一次：test 61-78 表明，当每 1000 行而不是一两行提交一次时，性能可以提高大概 10 倍。不过，一次提交多于 1000 行只能节省少量的时间，但是一旦出现失败，恢复起来所花的时间要更多。

对上述方法的一种修正：如果 MINCOMMIT 数据库配置参数的值大于 1 （缺省值），则 DB2 就不必对每次 commit 都进行一次同步 I/O，而是等待，并试图与一组事件一起共享日志 I/O。对于某些环境来讲，这样做是有好处，但是对于批量插入常常没有作用，甚至有负作用，因此，如果要执行的关键任务是批量插入，就应该让 MINCOMMIT 的值保持为 1。

回页首

可以选择性地进行改进的地方

对于一次 insert，有几种类型的处理将自动发生。如果您的主要目标只是减少插入时间，那么最简单的方法是避免所有这些处理的开销，但是如果从总体上考虑的话，这样做未必值得。让我们依次进行讨论。

索引维护

对于插入的每一行，必须添加一个条目到表上的每个索引中（包括任何主键索引）。这一过程主要有两方面的代价：

遍历每个索引树，在树的每一层搜索一个页，以确定新条目必须存储在哪里（索引条目总是按键顺序存储的），这一过程所引起的 CPU 开销；
将所有搜索到的页读入缓冲池，并最终将每个更新后的页写到磁盘上的 I/O 开销。

更坏的场景是，在索引维护期间有大量的随机 I/O。假设要插入 10,000 行，在索引的缓冲池中有 5000 页，并且要插入的各行的键值随机分布在整个键范围内。那么，有 10,000 个这么多的叶子页（可能还有些非叶子页）需要进入缓冲池，以便对它们进行搜索和/或更新，对于一个给定的叶子页，它预先已经在缓冲池中的概率只有 10%。对于每次的 insert，需要读磁盘的概率如此之高，使得这种场景往往性能很差。

对于逐行插入，将新行添加到已有的索引中比起创建一个新索引来代价要高得多。如果是插入到一个空表，应该总是在进行了列插入之后创建索引。（注意，如果使用了 load，则应该预先创建索引。）如果要插入到一个已经填充过的表，那么在列插入之前删除索引，并在列插入之后重新创建索引，这种方法可能是最快的，但是只有在要插入相当多的行 -- 大概大于表的 10-20% 的时候，才能这么说。如果为索引表空间使用较大的缓冲池，并且尽可能地将不同 insert 排序，以便键值是排好序的，而不是随机的，就可以帮助加快索引维护。

附录 A 中的 test 31-37给出的结果表明，insert 的耗时是如何随着索引的数目以及创建索引的时机而变化的。总而言之，与没有索引相比，有 4 条索引的情况下 insert 可能要多耗费数倍的时间，而如果在 insert 之后才创建索引，就可以将总耗时（insert 耗时加上创建索引的耗时）缩短 1/4 到几乎 1/2。

如果关键目标是将 insert 的性能最优化，那么增加索引的 PCTFREE 时，就可能减少在随机插入索引条目时出现页拆分（page split）的次数。所以这样做时要小心，不过，太多的自由空间意味着大量的索引页，这对查询的性能乃至 insert 处理本身都会产生负面影响。

约束验证

这一类的开销包括检查约束验证和外键约束（参照完整性（RI））验证。检查约束开销很低（请参阅附录 A 中的 test 11-13），这很大程度上是因为不需要对每一行使用 I/O（因为要检查的值是在行内，只是要进行一些计算而已）。

如果有外键，则插入一行就是另外一回事了。对于每个外键，都必须在父表中进行一次查找，以确保有父行存在。虽然这种查找是通过主键索引进行的，但是这仍然要占用 CPU 循环来进行搜索，而且可能还要占用 I/O 将索引页读入缓冲池。test 11、14 和 15 表明，当有两个外键时，insert 的耗时要翻一番。

在填充表之后使用 CREATE INDEX 比起通过一条一条的 INSERT 语句建立索引来代价要小些，同样，使用 ALTER TABLE 创建外键（即进行验证）作为批量操作，比起在每次 insert 期间的验证所增加的代价的总和来，要小一些。比较 test 14 和 test 16、test 15 和 test 17，我们可以看到，在 insert 之后创建外键可以将总耗时减少大约 40%。

如果可能的话，在大量插入行到一个表之前，应该先使用 ALTER TABLE 删除表上的所有约束，在插入之后再重新创建这些约束（仍是使用 ALTER TABLE），只有在插入的行不到该表中所有行的 10-20% 时才可不必这样做。

有些应用程序本身也会做一些检查，以确保表之间的关系是有效的。也就是说，在插入一个子行之前，应用程序会读一个父行，以确保父行存在。如果这种检查得以正确执行，那么在数据库中定义外键约束将增加额外的开销。但是，至少有三个原因可以说明为什么在数据库中定义这些约束要更好些：

没有检查的应用程序会更简单。
如果由 DB2 来负责检查，性能要稍微好一些。
在数据库中定义约束使 DB2 可以知道表之间的关系，并且在某些情况下允许 DB2 根据这样的知识选择更好的访问计划。

如果应用程序本身的检查不能少，那么最好的替代方案是在 DB2 中定义外键，但是在 CREATE TABLE 或 ALTER TABLE 中带上 NOT ENFORCED 子句，这样就避免了检查开销，而优化器又能使用关系知识。

触发器执行

如果在一个表上定义了一个或多个进行 Insert 操作的触发器，那么每次的 insert 都将引起触发器定义中的动作的执行。由于那些被触发的动作通常是一条或多条 INSERT、UPDATE 或 DELETE 语句，因此，在数据量很大的 insert 中，触发器的开销会很大。附录 A 中的 test 18-21表明，增加触发器会导致 insert 的性能减慢数倍。索引和约束可以临时删除，但是应用程序知识却必须知道何时避免触发器的执行是可接受的。也就是说，知道何时不会导致数据完整性问题。如果避免触发器的执行是可接受的，您可以通过参考资料中列出的文章里所描述的技术来临时禁用触发器。

标识列和序列对象

这两种方法可以让 DB2 自动生成整型列值，这通常是在 insert 期间进行的。应该清楚的主要性能问题是，由于可恢复性的原因，生成的值必须做日志记录。为了减少日志记录的开销，可以将这些值预存（缓存）起来，每当缓存用完时，才写一条日志记录。缺省情况下是缓存 20 个值。

附录 A 中 test 41-51的结果表明，如果没有缓冲的话，耗时会非常大（几乎要比缺省情况慢 9 倍），而如果使用比缺省情况更大的缓存，则可以缩减大半的时间，并且将使用 Identity（标识）或 Sequence（序列）的时间减至不到原先的 20%。如果您要在使用 Identity 还是 Sequence 之间作选择的话，那么我告诉您使用 Identity 要好出几个百分点。

生成的列

当插入行到一个表，并且该表用“generated as”子句定义了一个或多个列，例如： CREATE TABLE t1 (c1 CHAR(5), c2 CHAR(5) generated always as (UPPER(c1))) ，这时，为建立生成的值而进行的函数调用将导致附加的开销。然而，这种开销非常小，不至于影响您使用这种功能的决心。

“refresh immediate”物化查询表（MQT）的重新生成

MQT 可用于通过预先计算聚合值来增强查询性能。例如： create table staffsum as ( select count(salary) as salcount, sum(salary) as salsum, dept from staff group by dept ) data initially deferred refresh immediate
如果 MQT 被定义为“refresh immediate”，则在每次 insert 时将重新计算 MQT 中的聚合，因此通常来讲，对带有 refresh immediate MQT 的表执行列插入不大可取。不过，DB2 会尽其所能优化重新计算，例如扫描总结表，而不是整个基本表。我们建议对于涉及 MQT 的 insert 运行 Explain，以便清楚幕后情况。

回页首

其他方面的考虑

分区表（在 DPF 中，以前的 V7 EEE）

当插入一行到一个分区表（使用 DB2 V8 的 Data Partitioning Feature (DPF)）时，首先要散列（hash）该行的分区键值以确定必须将该行插入到哪个分区，然后 DB2 将该行发送到那个分区。这种一次一行的处理方式比较慢，前面我们已看到，在 CLI 中，这种方式与数组插入方式比起来的确很慢，而且因为还需要将每一行从协调分区发送到目标分区，使得情况更加糟糕。

为了把开销降至最低，可以使用缓冲插入（buffered insert）。如果使用了这种方法，定向于某个给定分区的散列行首先会进入一个缓冲区中，然后成组地发送到那个分区，而不是一次发送一行。您可以通过 Prep 或 Bind 命令的“INSERT BUF”选项来引起缓冲插入。要了解关于缓冲插入的细节，请参阅 Application Development Guide 的参考资料：Programming Client Applications。在开发者园地也有关于基于 Java（只包括 SQLJ）的缓冲插入的文章，请参阅后面的“参考资料”。

在 DPF 环境中，如果对于重复的批量插入要求绝对最大的性能，那么您可能需要研究两个相关的 API。第一个 API 是 sqlugtpi，它让应用程序可以获得一个表的分区信息。之后就可以使用这种信息，再结合 sqlugrpn API 来找到一行所属的分区号。您可以使用这些 API 将属于某个给定分区的所有数据组到一起，然后连接到那个分区，这样就不需要在分区之间传输数据，对于每个分区都重复这么做。这种方法可以取得非常快的性能，但是要花一定的精力来确保这种方法在有多个数据类型、代码页等等的情况下也能十分有效。

DPF 插入可能引起的另一个问题是，要插入到的那个表（子表）上可能有外键约束。假设父表和子表有不同的分区键。那么每个子行的父亲一般会在一个不同的分区上，因此，对于大多数插入的行，对其父亲的验证检查就需要从子分区跨越到父分区。对此的解决办法是，让父表和子表的分区键相同（这对于查询性能来说不是最好的选择），或者，如果使用了多个逻辑分区，就可以将 DB2 注册表变量设置为 YES。

通过 Staging 表以及其他方法增加并行性

通过使用 staging 表可以为某些场景下的 insert 提高性能。通常的用法是，不是批量插入行到一个表中，而是使用 LOAD 命令将行装载到一个 staging 表中；然后，就可以使用 INSERT ... SELECT 将行插入到主表。不管 LOAD 还是 INSERT ... SELECT，都比常规的插入要快得多，即使将这两步加起来也常常要比常规插入快些。不过，单单就性能而言，在 V8 中还是使用 load 直接将表装载到主表要快些，因为在 V8 中的 load 不像 V7 中那样有并发限制。

除了数据的消息传递以外，致使您在 V8 中仍想使用 staging 表的主要原因是，这样可以将批量插入拆散成能够并行运行的更小的块。在一个有多个处理器的系统上，每条插入将在一个 DB2 代理中运行，并且不会占用多于一个的处理器，即使将 DBMINTRA_PARALLEL 参数被设为 ON 也是如此。例如，如果要在一台 8-way 的机器上插入 1M 的行，一般的插入过程通常对 CPU 的利用不会多于 12% （100 / 8）。（另一方面，load 则会自动使用百分比大得多的 CPU，这也是它比 insert 更可取的另一个原因。）相反，您可以将 1M 的行装载到一个 staging 表中，然后运行 8 条并发的 insert ... Select 语句，8 条 Select 语句中都有谓词，每条语句从 staging 表中检索大约 1/8 的惟一的行子集。

最后，您可以通过一个多线程应用程序运行并发的插入，其中每个线程做它自己的插入。

对于一条 INSERT ... SELECT，只要能使选择更快，就可以减少整条语句的耗时，但是这超出了本文的范围，不适合进行详细的讨论。下面列出了一些可能性。注意，这些只适用于选择部分。而同样的这些因素对于插入部分一般没什么帮助。

添加索引（不是在插入表上的索引！）。
使用大的缓冲池。
使用并行（INTRA_PARALLEL=YES 且 DFT_DEGREE > 1）。
使用隔离级别 UR （例如用一个 WITH UR 子句）。

插入 LOB 和 LONG 列

这些类型的列是惟一的，它们不会缓存在缓冲池中。因此，任何包括一个或多个这种列的 insert 都会使得这些列被直接写到磁盘上，用 DB2 术语来说就是“直接写（direct write”）。您可以想像，这会使 LOB/LONG 的 insert 比“一般” 的 insert 要慢得多： test 91中使用了一个 CLOB 列，这种情况比基线测试（test 11，有一个 CHAR 列）要慢 9 倍以上。有这样一些优化的可能性：

将 LOB 或 LONG 改为 VARCHAR。这允许发生缓冲池的缓存。这可能要求将表放入到一个页宽较大（例如 32 K）的表空间中，因为页宽必须大到足以装下所有的非 LOB 和非 LONG 列。
使用 SMS 或 DMS 文件表空间，这样便允许操作系统的缓存抵消某些性能上的降低。
为 LOB/LONG 使用最佳的存储/硬件配置。
尝试为 LOB 列使用 COMPACT 和 NOT LOGGED 属性。这两个属性对于我这个小测试来说没有多大提高，但是当使用了大量数据的时候，效果就出来了。

优化级别

对于没有约束的简单插入，将优化级别从缺省值（5）改为 1 的测试虽然将优化器的算法变得更廉价，但是并没有对性能产生很大的变化。如果要经常准备插入语句，插入牵涉到约束，或者有选择部分，那么使用较低的优化级别可能会有好处。相反，如果插入部分很小，而选择部分比较复杂，那么将优化级别从 5 增至更大将带来好处。

利用源表（source table）插入/更新目标表（MERGE 语句）

一个相当常见的数据库任务就是利用一个源表更新一个目标表。举个特定的例子，比如取源表中的每一行，如果该行不在目标表中，那么就将该行插入到目标表，否则就更新目标行。在 V8 中可以使用 MERGE 语句独立完成上述任务，而不必多次执行不同的语句，从而性能也就更好一些。

回页首

监视和调优 insert

当您试图监视和调优 insert 时，基本任务跟大多数其他的性能分析没什么不同：找出瓶颈所在，然后直接处理瓶颈。欲确定瓶颈，首先就是利用操作系统工具查看 CPU、I/O、内存和网络消耗。这样应该就可以让您排除某些方面，而将注意力放在一两个方面。对操作系统实用程序的深入讨论超出了本文的范围。

尽量不要被非必要的问题转移了视线。例如，如果 CPU 利用率是 100%，那么这时减少 I/O 很可能无法提高性能，而当以后 CPU 瓶颈已经解除时，这样的更改本来可能非常有用，但是您可能会因为上一次的失败而不再作这样的更改了。

应该尽量让应用程序在操作期间的不同时刻报告插入的速率。例如，如果知道在运行后的第 10 分钟与第 5 分钟时各自的每秒插入次数是否相同，是很有用的。通常，当 insert 开始的时候，有一小段较慢的启动时间，然后当缓冲池填充了内容并且没有数据页的 I/O 时，就有一段速度比较快的时期。接着，当开始将数据页往外写的时候，速率又会慢下来，如果页清除或者 I/O 子系统不是最优的，则更是如此。

对于非常大的批量插入，通常在某一时刻插入的速率会趋于平稳。如果不是这样，那么通常是因为要在插入期间创建索引，使得需要创建越来越多的索引页，并且可能还要进行随机的 I/O 操作，读取已有的索引页以便更新它们。如果的确是上述情况，那么使用更大的缓冲池是最好的解决办法，但是为索引页增加更多的自由空间也有所帮助。

现在让我们看看可以帮助您监视和调优 insert 的关键 DB2 实用程序：Snapshots（快照监视）、Event Monitoring（事件监视）和 Explain。要了解关于快照监视和事件监视的更多信息，请参阅 System Monitor Guide and Reference；至于 Explain，请参阅 Administration Guide: Performance。

快照监视

快照监视可以提供大量信息片断来描述在插入的处理期间发生了什么事情。您可以使用以下步骤获得所有可以得到的信息（或者也可以选择获得信息的子集）：

使用 UPDATE MONITOR SWITCHES 命令打开所有开关。
运行 RESET MONITOR ALL重设计数器。这样更易于在一次测试的过程中比较不同快照并找出不同。
等一段标准长度的时间，例如 1 分钟或者 5 分钟，然后发出 GET SNAPSHOT FOR ALL ON <database>。重复前两步，以获得多个用于比较的快照。

大多数与 insert 相关的信息都可以在数据库快照中找到，并且大部分的这些信息也都会在适当的缓冲池、应用程序和表空间快照中以更大的粒度提供。

以下是数据库快照中最相关的几行：

Buffer pool data writes                    = 500
            Asynchronous pool data page writes         = 500
            Buffer pool index writes                   = 0
            Asynchronous pool index page writes        = 0
            Total buffer pool write time (ms)          = 25000
            Total elapsed asynchronous write time      = 25000
            LSN Gap cleaner triggers                   = 21
            Dirty page steal cleaner triggers          = 0
            Dirty page threshold cleaner triggers      = 0
            Update/Insert/Delete statements executed   = 100000
            Rows inserted                              = 100000

请注意连续快照中的“Rows inserted”，看看在批量插入期间插入的速率是否有变化。大多数其他的值都反映了 I/O 量和页清除的效力。至于后者，理想情况下您可以看到，所有数据写都是同步的，而所有缓冲池写时间都是异步的（就像上面输出的那样）；如果不是这样，尝试降低 CHNGPGS_THRESH 和/或增加 NUM_IOCLEANERS。

通常在动态 SQL 快照中可以找到关于 insert 的附加信息。看看总耗时，并将其与用户和系统 CPU 时间相比较，这样做是十分有用的。耗时与 CPU 之间的差值大部分在于 I/O 部分，所以，哪个地方占去了大部分的时间以及哪个地方需要调优也就很清楚了。

以下是一个 100,000 行的 CLI 数组插入的动态 SQL 快照条目的一个子集。注意，尽管应用程序只发送那个数量的 1/10 那么多的数组，“Number of executions”仍是对于每一行都有一个。

Number of executions               = 100000
            Number of compilations             = 1
            Rows written                       = 100000
            Buffer pool data logical reads     = 102120
            Total execution time (sec.ms)      = 13.830543
            Total user cpu time (sec.ms)       = 10.290000
            Total system cpu time (sec.ms)     = 0.130000
            Statement text                     = INSERT into test1 values(?, ?, ?, ?, ?)

快照输出中的其他信息：

“Lock waits”和“Time database waited on locks” -- 使用它们来查看插入的行上的锁是否引起其他应用程序的并发问题。
Table Snapshot --“Rows Written”将反映插入（或更新）的行的数目。

事件监视

当事件在服务器上发生时，通过 DB2 事件监视器可以获得关于事件的性能信息。为了分析 insert 的性能，需要为语句创建一个事件监视器，并在 insert 执行期间激活该事件监视器。虽然有点过分，但是事件监视器的输出会显示每条 insert 语句的耗时。对于 OLTP 型的应用程序，对语句运行事件监视的开销相当高，其输出也十分冗长，所以应注意不要让事件监视运行太长的时间。即使是几秒钟也会产生数兆的输出。您可以将监视器信息写入到一个表中，以便于对结果的分析，例如性能趋势。

下面是一个浓缩的示例语句事件，在一系列的 10,000 个 CLI 数组插入中每 10 行对应一次这样的语句事件。每个数组只有一个事件，在这里就是每 10 行有一个事件。

17) Statement Event ...
            Appl Handle: 9
            Appl Id: *LOCAL.wilkins.0953D9033443
            -------------------------------------------
            Type     : Dynamic
            Operation: Execute
            Section  : 4
            Creator  : NULLID
            Package  : SYSSH200
            Text     : INSERT into test1 values(?, ?)
            -------------------------------------------
            Start Time: 01-28-2004 22:34:43.918444
            Stop Time:  01-28-2004 22:34:43.919763
            Exec Time:  0.001319 seconds
            Number of Agents created: 1
            User CPU: 0.000000 seconds
            System CPU: 0.000000 seconds
            Fetch Count: 0
            Rows read: 0
            Rows written: 10
            Internal rows deleted: 0
            Internal rows updated: 0
            Internal rows inserted: 0
            Bufferpool data logical reads: 10
            SQLCA:
            sqlcode: 0
            sqlstate: 00000

Explain

如果 insert 的性能不像预期的那么好，则有可能是因为有“隐藏”的处理发生。前面已经讨论过，这种处理可能以不同的形式出现，例如索引维护、约束验证或者触发器执行。对 insert 运行某种形式的 Explain （例如 Visual Explain，或者 Explain 语句加上 db2exfmt），就可以揭示大多数额外的处理（除了索引维护）。如果额外的处理可能是性能问题的起因，那么可以消除这种额外的处理。

作为一个简单的例子，下面的图（由 db2exfmt 产生）展示了“Insert into test1 values (?, ?, ...)”语句的访问计划。您可以猜出这里的额外处理是什么吗？回答就在下面。

                               Rows
            RETURN
            (   1)
            Cost
            I/O
            |
            0.333333
            TBSCAN
            (   2)
            28.2956
            1
            /----+---\            0.04            1
            FILTER   TABFNC: SYSIBM
            (   3)        GENROW
            28.2266
            1
            |
            1
            NLJOIN
            (   4)
            28.1268
            1
            /---------+--------\            1                       1
            INSERT                  IXSCAN
            (   5)                  (   7)
            25.5248                 2.60196
            1                       0
            /---+--\\                   |
            1          116               120
            TBSCAN  TABLE: WILKINS    INDEX: SYSIBM
            (   6)       test1       SQL0401290925513
            0.0048
            0
            |
            1
            TABFNC: SYSIBM
            GENROW

在我给出答案之前：Explain 对于 INSERT ... SELECT 语句也十分有用。insert 本身是非常快的，但是 SELECT 可能存在一个访问计划问题，这个问题会拖慢整个语句。通过 Explain 就可以揭示这一切。
答案：
上述访问计划是针对将行插入到拥有外键关系的子表的 insert 的。其中有一个 insert（步骤 5）与索引扫描（步骤 7）之间的嵌套循环连接（NLJOIN，步骤 4 ）。索引扫描实际上是对父表进行主键查找，并完成外键约束验证。在这种情况下，来自额外处理的开销相对来说就比较小：索引扫描的成本估计只有 2.60196 timerons（timerons 是（主要）结合了 CPU 和 I/O 代价的成本单位），而 insert 本身的成本是 25.5248 timerons。例如，如果有一个触发器的话，那么就会在访问计划中的一个或多个 insert、Update 或 Delete 条目中反映出来。

顺便提一下，当您在访问计划中看到“GENROW”时，其实就是一个“generate row”步骤。这代表用于后续步骤的临时行的创建，这里不需要担心。

回页首

结束语

在本文中，我们给出了多种提高 insert 性能的方法。请参阅附录 B以了解这些方法的完整清单。下面是最重要的一部分方法，每种方法在某些情况下可以使性能快上两倍：

尽可能使用 Load。
使用参数标记，以避免对于每一行都有 Prepare 成本。
每 N 行发出一次 Commit，其中 N 是一个比较大的数，例如 1000。千万不要每一行都提交，因此要小心自动提交情况。
一次插入一组行。
将 insert 期间出现约束、索引和触发器的机会降至最少。
如果使用 SMS 表空间，则运行 db2empfa。
优化“special features”的使用：带分区表的缓冲插入，用于 Identity 和 Sequence 值的大的缓存。

我们希望本文可以让您很好地了解在 DB2 insert 处理期间所发生的事情，以及如何监视和提高其性能。

回页首

附录 A -- 性能评测

该附录举例说明了在本文中讨论的那些优化技术可以带来的一些好处（以及对非优化方法的冲击）。每次测试都至少运行 2 次，并且结果一致，但是该结果跟您在自己环境中看到的结果不一定完全吻合。特别地，这些结果是在一个 I/O 强度比理想情况下更大的一个系统上得到的，因为用户表空间和日志被放在相同的文件系统中，并且是在相同的两个磁盘上。因此，能减少 CPU 开销的改进策略通常还有比这里提到的更多的好处。

下面几条适用于所有这些测试，如有例外则会另外注明：

小型 RS/6000 系统上的 DB2 V8 FixPak 4。
使用了本地客户机。
为用户数据使用了 SMS 表空间，并对数据库运行了 db2empfa 命令以建立多页文件分配。表空间页宽/区段大小/预取大小采用缺省值（4K，32，32）。要插入到的那个表一开始为空，并且没有索引。
除了 test 1-2、8、61-68 和 79 以外，所有测试都是在 CLI 中使用数组插入来完成的。
每 1000 行有一次 Commit。

表 1. 准备语句和使用数组的效果

test #	插入方法/注解	# 行数	耗时（秒）
1	CLI -- 对每一行执行 SQLExecDirect	10,000	180.63
2	CLI -- 预处理语句，但是每次只插入 1 行	10,000	92.05
3	CLI -- 预处理语句，并且是数组插入（每次插入 10 行）	10,000	12.85

test 1-3 中自动提交是处于启用状态的，因而对每一次 insert 都有一次 Commit。也就是说，在 test 1 和 test 2 中，对于每一行都有一次 Commit，在 test 3 中对于每 10 行有一次 Commit。至于 Commit 的性能影响，请参考 test 61-78。

表 2. 用一个表的内容填充另一个表

test #	插入方法/注解	# 行数	耗时（秒）
6	带日志记录的 INSERT ... SELECT	100,000	15.66
7	带 NOT LOGGED INITIALLY 选项的 INSERT ... SELECT	100,000	16.43
8	与 test 7 一样，但是 CHNGPGS_THRESH = 5	100,000	11.80
9	从游标装载（与 test 6-8 相同的 Select）	100,000	12.95

test 6-9 计算了利用一个表填充另一个表的时间，包括 Commit 的时间。test 7 表明，使用 NOT LOGGED INITIALLY (NLI) 实际上会导致性能下降，因为需要在 Commit 时将新页写到磁盘上：Commit 占了大半的时间。然而，当 test 8 中采用了更积极的页清除时，性能就大大提高了，这主要是因为 Commit 的时间缩短了超过 5 秒钟。test 9 表明，通过使用 Load 而不是 insert ... Select，性能提高了 17%，并且没有 test 6 中那样的 NLI 风险。

表 3. 检查约束、外键和触发器的影响

test #	插入方法/注解	# 行数	耗时（秒）
11	CLI -- 与 test 3 相同，但是有 100K 的行，提交 1000 次	100,000	31.51
12	CLI -- 与 test 11 相同，但是有 1 个检查约束	100,000	33.65
13	CLI -- 与 test 11 相同，但是有 2 个检查约束	100,000	36.63
14	CLI -- 与 test 11 相同，但是有 1 个外键（FK）	100,000	55.37
15	CLI -- 与 test 11 相同，但是有 2 个外键（FK）	100,000	72.71
16	CLI -- 与 test 14 相同，但是 Insert 之后添加了 FK	100,000	32.84
17	CLI -- 与 test 15 相同，但是 Insert 之后添加了 FK	100,000	38.89
18	CLI -- 与 test 11 相同，但是有 1 个进行 Insert 操作的触发器	100,000	67.57
19	CLI -- 与 test 11 相同，但是有 2 个进行 Insert 操作的触发器	100,000	175.95
20	CLI -- 与 test 11 相同，但是有 1 个进行 Update 操作的触发器	100,000	54.71
21	CLI -- 与 test 11 相同，但是有 2 个进行 Update 操作的触发器	100,000	150.18
22	CLI -- 与 test 11 相同，但是有 1M 的行	100,000	282.02
23	CLI -- 与 test 22 相同，但是 APPEND 的状态是 ON	100,000	281.64

显然，检查约束的使用对性能有一点小小的影响，但是外键和触发器的影响却非常大。在 test 18 和 test 19 中，各自的触发器都将一行插入到基本表所在的相同表空间内的一个不同的表中，这暴露了数据库和日志磁盘上的 I/O 瓶颈。在 test 20 和 test 21 中，各自的触发器在一个只有一行的表中增加了一个“insert count”列；虽然这里不像 test 18 和 test 19 那样有其他的行，但是更新开销和日志记录仍导致 insert 运行起来比没有触发器的情况下慢很多。虽然通过标准数据库 I/O 调优可以改进 test 18-21，但关键是在大规模插入期间应尽可能避免存在约束。

表 4. 在插入前后创建索引的影响

test #	插入方法/注解	# 行数	索引个数	耗时（秒）
31	CLI -- 与 test 3 相同，但是有 100K 行，提交 1000 次	100,000	0	31.51
32	CLI -- 与 test 31 相同，但是有 1 个索引	100,000	1	53.73
33	CLI -- 与 test 31 相同，但是有 2 个索引	100,000	2	83.26
34	CLI -- 与 test 31 相同，但是有 3 个索引	100,000	3	108.21
35	CLI -- 与 test 31 相同，但是有 4 个索引	100,000	4	141.63
36	CLI -- 与 test 35 相同，但是在插入之后创建索引	100,000	4 (*）	73.75
37	CLI -- 与 test 32 相同，但是在插入之后创建索引	100,000	1	39.44
38	CLI -- 与 test 32 相同，但是索引被群集起来	100,000	1	62.93

在 test 32-35 中，索引是在插入之前创建的，而在 test 36-37 中索引是在插入之后创建的（并且，对于后一次测试，耗时包括 insert 的时间加上 CREATE INDEX 语句的时间）。

表 5. 使用标识符或序列

test #	插入方法/注解	# 行数	耗时（秒）
41	CLI -- 与 test 31 相同（没有 Identity 或 Sequence）	100,000	31.51
42	CLI -- 与 test 41 相同，但是有 Identity 列，无缓存	100,000	896.61
43	CLI -- 与 test 41 相同，但是有 Identity 列，缓存 5	100,000	212.52
44	CLI -- 与 test 41 相同，但是有 Identity 列，缓存 20（缺省）	100,000	99.06
45	CLI -- 与 test 41 相同，但是有 Identity 列，缓存 100	100,000	61.62
46	CLI -- 与 test 41 相同，但是有 Identity 列，缓存 1000	100,000	37.51
47	CLI -- 与 test 41 相同，但是有 Sequence，无缓存	100,000	896.92
48	CLI -- 与 test 41 相同，但是有 Sequence，缓存 5	100,000	212.58
49	CLI -- 与 test 41 相同，但是有 Sequence，缓存 20（缺省）	100,000	101.87
50	CLI -- 与 test 41 相同，但是有 Sequence，缓存 100	100,000	66.55
51	CLI -- 与 test 41 相同，但是有 Sequence，缓存 1000	100,000	39.55

test 41-51 表明，如果缓存很小或者没有缓存的话，使用 Identity 或 Sequence 对性能有很大的负面影响，但是通过使用较大的缓存可以使这种开销几乎可以忽略不计。

表 6. 展示使用大型数组和每次提交很多 insert 的效果

test #	插入方法/注解	# 行数	耗时（秒）
1	CLI -- 对每一行执行 SQLExecDirect（同上）	10,000	183.55
61	CLI -- 与 test 1 相同，每 5 行一次 Commit	10,000	118.41
62	CLI -- 与 test 1 相同，每 10 行一次 Commit	10,000	114.23
63	CLI -- 与 test 1 相同，每 100 行一次 Commit	10,000	103.43
64	CLI -- 与 test 1 相同，每 1000 行一次 Commit	10,000	102.86
2	CLI -- 预处理语句，但是每次插入 1 行（同上）	10,000	92.05
65	CLI -- 与 test 2 相同，每 5 行一次 Commit	10,000	22.34
66	CLI -- 与 test 2 相同，每 10 行一次 Commit	10,000	20.78
67	CLI -- 与 test 2 相同，每 100 行一次 Commit	10,000	10.11
68	CLI -- 与 test 2 相同，每 1000 行一次 Commit	10,000	7.58
69	CLI -- 与 test 3 相同（10x 行）；数组大小为 10，每 10 行一次 Commit	10,000	118.18
70	CLI -- 与 test 69 相同，但是数组大小为 10，每 100 行一次 Commit	10,000	56.71
71	CLI -- 与 test 69 相同，但是数组大小为 10，每 1000 行一次 Commit	10,000	30.09
72	CLI -- 与 test 69 相同，但是数组大小为 100，每 100 行一次 Commit	10,000	50.65
73	CLI -- 与 test 69 相同，但是数组大小为 100，每 1000 行一次 Commit	10,000	24.27
74	CLI -- 与 test 69 相同，但是数组大小为 1000，每 1000 行一次 Commit	10,000	23.43
75	CLI -- 与 test 69 相同，但是数组大小为 2，每 2 行一次 Commit	10,000	471.82
76	CLI -- 与 test 69 相同，但是数组大小为 2，每 10 行一次 Commit	10,000	155.21
77	CLI -- 与 test 69 相同，但是数组大小为 2，每 100 行一次 Commit	10,000	74.82
78	CLI -- 与 test 69 相同，但是数组大小为 2，每 1000 行一次 Commit	10,000	48.51
79	CLI -- 通过 SQLSetStmtAttr（数组大小为 10）使用 Load	10,000	13.68

前面的测试展示了使用大型数组和每次提交大量的行所带来的好处，其中后者尤为重要。

表 7. 使用 SMS 或 DMS

test #	插入方法/注解	# 行数	耗时（秒）
11	CLI -- SMS，运行了 db2empfa（同上）	100,000	31.51
81	与 test 11 相同，但是使用 DMS 文件表空间	100,000	25.52
82	与 test 11 相同，但是 db2empfa 没有运行（SMS）	100,000	62.87
83	与 test 11 相同，但是 extentsize = 4 （并且 prefetchsize = 4）	100,000	38.37
83	与 test 11 相同，但是 extentsize = 4 （并且 prefetchsize = 4）	100,000	38.37
84	与 test 11 相同，但是 extentsize = 8 （并且 prefetchsize = 8）	100,000	34.61
85	与 test 11 相同，但是 extentsize = 16 （并且 prefetchsize = 16）	100,000	31.75

前面的测试表明，对于 SMS，运行 db2empfa 是取得良好的 insert 性能的关键，而使用小于 32 页的区段是有害的。DMS 比 SMS 更好一些。

表 8. 使用 CLOB 列的效果

test #	插入方法/注解	# 行数	耗时（秒）
11	CLI -- （同上）	100,000	31.51
91	与 test 11 相同，但是 CHAR(10) 列现在是 CLOB(10)	100,000	286.49

前面的比较展示了使用 CLOB 列（虽然非常短）带来的巨大影响。我们还对 test 91 的变种做过尝试，但是结果仍然十分接近，对于 CLOB 列不管选择作不作日志记录，或者是否进行压缩，都影响不大。

表 9. 使用表锁而不是使用行锁

test #	插入方法/注解	# 行数	耗时（秒）
11	CLI -- （同上）	100,000	31.51
101	与 test 11 相同，但是使用了 LOCKSIZE TABLE	100,000	30.58

上述比较表明，使用表锁而不是行锁可以节省大约 3% 的耗时。

表 10. 改变 LOGBUFSZ

test #	插入方法/注解	# 行数	耗时（秒）
104	与 test 71 相同，但是每次提交 10K 的行（LOGBUFSZ=8）	100,000	28.53
105	与 test 104 相同，但是 LOGBUFSZ = 256	100,000	24.91

test 105 表明，如果将 LOGBUFSZ 升至足够高，以免在 Insert 期间因为日志缓冲区被填满而被迫将日志写到磁盘，这样做可以提高大约 13% 的性能。

表 11. 使用多种优化

test #	插入方法/注解	# 行数	耗时（秒）
22	CLI -- （同上）	1,000,000	282.02
111	与 test 22 相同，但是使用了所有优化（见下面）	1,000,000	160.45
1(*）	CLI -- （与 test 1 相同，但是增加到了 1,000,000 行）	1,000,000	~18000.00

test 111 使用了前面试过的所有优化技术来替代基线：使用 DMS 而不是 SMS，使用 1000 行的数组（而不是 10 行），每 10000 行提交一次（而不是 1000 行），使用 LOCKSIZE TABLE，使 APPEND 的状态为 ON，并且 LOGBUFSZ 设为 256。注意，与最初的基线（test 1）相比，test 22 已经做了一些优化。这里将 test 1 扩展到了 1,000,000 行，以表明仅仅更改少量因素就可能造成性能上的巨大差异。

回页首

附录 B -- 对于批量插入的优化建议清单

下面是在本文前面详细讨论过的一些建议。请记住，这些建议的影响程度千差万别，有些建议对于除 Insert 之外的其他任务还可能有负面影响。

只准备 Insert 语句一次，在语句中使用参数标记，然后多次执行该语句。如果经常要准备语句，那么可以试着调低优化级别。
通过 CLI 数组或 JDBC 批处理操作，在每次 Insert 中包括多行，并尽可能地将处理工作从数组/批处理循环中移出来，以优化应用程序。
将多条 Insert 组到一起（Compound SQL）。
让客户机应用程序和数据库使用相同的代码页。
避免客户机与数据库之间的数据类型转换。
避免使用 LOB 和 LONG 列；如果无法避免的话，请参考优化对这两种列的使用的建议。
为表使用 APPEND 模式，或者将 DB2MAXFSCRSEARCH （注册表）设置成一个较低的值。
尽可能避免表上有任何索引，尤其是不要有群集索引。
在数据页上预留适当大小的自由空间（如果使用了 APPEND 模式，则预留的空间为 0）。
在索引页上预留适当大小的自由空间（如果是随机 insert，则预留的自由空间应大于 10%）。
DMS 表空间是最好的，但是如果使用了 SMS，那么运行 db2empfa 并使用 32 页或更大的区段。
使用大的缓冲池，如果必须在 Insert 期间建立索引的话，更应如此。
通过降低 CHNGPGS_THRESH 和增加 NUM_IOCLEANERS (DB CFG)，确保有效的页清除。在 V8 FP4 中，要考虑 DB2_USE_ALTERNATE_PAGE_CLEANING（注册表）。
将数据、索引和日志散布在多个磁盘上，日志使用的磁盘应不同于其他东西使用的磁盘。对于数据、索引和日志，应避免 RAID 5。
使用 LOCK TABLE 或 ALTER TABLE 建立表一级的锁。如果存在并发问题，那么可能需要增加 LOCKLIST 和/或 MAXLOCKS，以确保不会出现独占锁升级的现象。
考虑 DB2_EVALUNCOMMITTED （注册表，V8 FP4），以减少锁对其他应用程序的影响。同时还为其他应用程序使用隔离级别 UR，以减少锁对它们的影响。
增加 LOGBUFSZ（DB CFG）。
为 Insert 表使用 ACTIVATE NOT LOGGED INITIALLY，但是要清楚恢复问题。
每过 N 行之后再 Commit，其中 N 是一个较大的数，比如 1000。
尽量减少表上的触发器、检查和外键约束以及生成的列。
尽可能避免 IDENTITY 和 SEQUENCE，如果不能避免，则使用较大的缓存。
避免将行插入到带有“refresh immediate”MQT 的表中。
在 DPF 环境中，使用缓冲插入并避免分区间的传输。
使用 Load 和 staging 表或多线程应用程序，以实现 Insert 的并行性。
对于 Insert ... Select，确保对 Select 部分进行优化。
使用 Explain 来发现“隐藏”的处理。
并且使用尽可能快的 CPU 和磁盘，这一点是不用说的。