DB2中提高Insert性能的技巧

本文详细地讲述了DB2数据库中对INSERT语句性能优化的过程。

1、INSERT的执行步骤。

这些步骤中的每一步都有优化的潜力，对此我们在后面会一一讨论。

（1）在客户机准备语句。对于动态 SQL，在语句执行前就要做这一步，此处的性能是很重要的;对于静态 SQL，这一步的性能实际上关系不大，因为语句的准备是事先完成的。

（2）在客户机，将要插入的行的各个 列值组装起来，发送到 DB2 服务器。

（3）DB2 服务器确定将这一行插入到哪一页中。

（4）DB2 在 用于该页的缓冲池中预留一个位置。如果 DB2 选定的是一个已有的页，那么就需要读磁盘;如果使用一个新页，则要在表空间(如果是SMS，也就是系统管理存储的表空间)中为该页物理地分配空间。插入了新行的每一页最后都要从缓冲池写入到磁盘。

（5）在目标页中对该行进行格式化，并获得该行上的一个X(exclusive，独占的) 行锁。

（6）将反映该 insert 的一条记录写入到日志缓冲区中。

（7）最后提交包含该 insert 的事务，如果这时日志缓冲区中的记录还没有被写入日志文件的话，则将这些记录写到日志文件中。

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

2、insert 的替代方案

在详细讨论 insert 的优化之前，让我们先考虑一下 insert 的两种替代方案：load 和 import。import 实用程序实际上是 SQL INSERT 的一个前端，但它的某些功能对于您来说也是有用的。load 也有一些有用的额外功能，但是我们使用 load 而不使用 insert 的主要原因是可以提高性能。

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

（2）从游标装载

这种方法可用于应用程序的程序代码(通过 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

同等效的 INSERT ... SELECT 语句相比，从游标装载几乎可以提高 20% 的性能。

（3）从 CLI 装载

这种方法显然只限于调用层接口(CLI)应用程序，但是它非常快。这种技巧非常类似于数组插入，DB2 附带了这样的示例，使用 load 时的速度是使用经过完全优化的数组插入时的两倍，几乎要比未经优化的数组插入快 10 倍。

3、所有 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 (?, ?)

使用参数标记可以让一系列的 insert 的运行速度提高数倍。(在静态 SQL 程序中使用主机变量也可以获得类似的好处。)

（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 语句创建的。