在DB2中提高INSERT性能(1)

出处:数据库用户　 

 

阅读提示：本文介绍在DB2中如何提高INSERT性能的技巧

INSERT 处理过程概述

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

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

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

INSERT 的替代方案

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

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
同等效的 INSERT ... SELECT 语句相比，从游标装载几乎可以提高 20% 的性能。

从 CLI 装载
这种方法显然只限于调用层接口（CLI）应用程序，但是它非常快。这种技巧非常类似于数组插入，DB2 附带了这样的示例，使用 load 时的速度是使用经过完全优化的数组插入时的两倍，几乎要比未经优化的数组插入快 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 (?, ?)

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

可以归为这一类的优化技巧有好几种。最重要的一种技巧是在每条 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，而不是对每一组数组内容都执行一次。对于个体来说，这些调用的代价并不高，但是这些代价是累积的。