基于数据仓库的数据库应用程序常见任务是海量数据查询或修改。应用程序开发人员面临的问题是如何在处理海量数据集时实现高性能。

数据处理技术分为两个类型：迭代运算和集合运算。

关于迭代的数据处理技巧

在迭代处理中，应用程序使用条件循环遍历一组行数据。

典型的迭代处理流程如下（并不是每一步都是必需的），用到了客户端/服务器模型：

  1. 客户端应用程序从数据库端读取了一组数据。
  2. 客户端应用程序内部逐笔处理这组数据。
  3. 客户端将这组数据处理结果写回给数据库。

您可以使用三种主要技术实现迭代算法：逐行处理、数组处理和手动并行。

  • 逐行迭代处理

在逐行处理中，单进程遍历数据集，并且每次在单个行上操作。在典型的实现中，应用程序从数据库中检索一行，在中间层处理，然后将行发送回数据库，数据库执行 DML 并提交。

假设您有个需求，需要处理一个表 raw_list 中记录，把每行记录分拆为两笔记录插入到表 op_list 中。下面的 PL 程序示例代码使用逐行处理技术实现了这个需求。

  1. CREATE TABLE raw_list(
  2.       id number NOT NULL PRIMARY KEY
  3.     , debit_id number NOT NULL
  4.     , credit_id number NOT NULL
  5.     , op_amount number NOT NULL
  6.     , op_time date NOT NULL 
  7. );
  8. CREATE SEQUENCE seq_op_list START WITH 1 ;
  9. CREATE TABLE op_list(
  10.       op_id number NOT NULL PRIMARY KEY
  11.     , op_code varchar2(20) NOT NULL
  12.     , account_id number NOT NULL
  13.     , amount number NOT NULL
  14.     , gmt_create date DEFAULT sysdate NOT NULL
  15. );
  17. delimiter /
  18. DECLARE
  19.     CURSOR c1 IS SELECT * FROM raw_list ;
  20.     r   raw_list%rowtype ;
  21. BEGIN
  22.     OPEN c1;
  23.     LOOP
  24.         FETCH c1 INTO r ;
  25.         EXIT WHEN c1%NOTFOUND ;
  27.         INSERT INTO op_list(op_id, op_code, account_id, amount) values(seq_op_list.nextval, '-', r.credit_id, r.op_amount);
  28.         INSERT INTO op_list(op_id, op_code, account_id, amount) values(seq_op_list.nextval, '+', r.debit_id, r.op_amount);
  29.         COMMIT;
  30.     END LOOP; 
  31. END;
  32. /
  33. delimiter ;

逐行处理技术有如下的优点：

  • 在数据量不大的时候性能很好。
  • 专业的开发人员对循环算法非常熟悉，容易快速实现，代码可读性也很高。

逐行处理技术也有如下的缺点：

  • 数据量非常大的时候，总的处理时间会非常长。

  • 应用程序是串行执行的，因此不能发挥运行在现代硬件上的数据库的原生的并行处理特性。

  • 分组迭代处理

分组处理与逐行处理相同，只是它在每次迭代中处理多行，而不是单行。如下示例：

  1. delimiter /
  2. DECLARE
  3.     CURSOR c1 IS SELECT * FROM raw_list ;
  4.     TYPE T_RAW  IS TABLE OF raw_list%ROWTYPE INDEX BY binary_integer ;
  5.     t   T_RAW;
  6.     row_cnt binary_integer  := 0;
  7.     array_size  NUMBER := 100;
  8. BEGIN
  9.     OPEN c1;
  10.     LOOP
  11.         FETCH c1 BULK COLLECT INTO t LIMIT array_size ;
  12.         EXIT WHEN t.COUNT = 0 ;
  14.         FORALL i IN 1..t.COUNT
  15.             INSERT INTO op_list(op_id, op_code, account_id, amount) values(seq_op_list.nextval, '-', t(i).credit_id, t(i).op_amount);
  17.         FORALL i IN 1..t.COUNT
  18.             INSERT INTO op_list(op_id, op_code, account_id, amount) values(seq_op_list.nextval, '+', t(i).debit_id, t(i).op_amount);
  20.         COMMIT;
  21.     END LOOP; 
  22. END;
  23. /
  24. delimiter ;

上面代码跟逐行迭代处理区别在于在 FETCH 语句中使用了 BULK COLLECT 操作符分组获取数据，并限制了每组大小 array_size 。

相比于逐行迭代处理技术，分组迭代技术有如下优点：

  • 分组使得应用程序每次能处理一组数据，节省了网路上来回、COMMIT 次数和时间。
  • 数据库更加高效，因为使用批量插入后再提交事务，而不是每次插入2笔就提交事务。

分组迭代技术的缺点跟逐行迭代技术是一样的，面对大数据量时，这个处理时间也是不能接受的。此外就是应用程序也只能串行执行，不能发挥数据库的并行处理能力。

  • 手动并行迭代处理

手动并行迭代与逐行和分组处理使用相同的迭代算法，但允许多个服务器进程划分工作并并行运行。

假设业务功能需求跟前面逐行迭代处理是一样的，但是做如下调整：

  1. 原始记录存储在32个结构跟表raw_list 相同的表中，命名为 raw_list_0 , raw_list_1, … , raw_list_31
  2. 数据库端开启32个独立的会话同时运行，每个会话处理一个原始记录表，不同会话处理不同的原始记录表。
  3. 每个会话的执行逻辑跟上面分组迭代处理基本相同，具体的参数 ${thr} 由外部传入，每个会话都不相同。
  1. delimiter /
  2. DECLARE 
  3.     sqlstmt varchar2(1024)  := q'[
  4. DECLARE
  5.     CURSOR c1 IS SELECT * FROM raw_list_${thr} ;
  6.     TYPE T_RAW  IS TABLE OF raw_list%ROWTYPE INDEX BY binary_integer ;
  7.     t   T_RAW;
  8.     row_cnt binary_integer  := 0;
  9.     array_size  NUMBER := 100;
  10. BEGIN
  11.     OPEN c1;
  12.     LOOP
  13.         FETCH c1 BULK COLLECT INTO t LIMIT array_size ;
  14.         EXIT WHEN t.COUNT = 0 ;
  16.         FORALL i IN 1..t.COUNT
  17.             INSERT INTO op_list(op_id, op_code, account_id, amount) values(seq_op_list.nextval, '-', t(i).credit_id, t(i).op_amount);
  19.         FORALL i IN 1..t.COUNT
  20.             INSERT INTO op_list(op_id, op_code, account_id, amount) values(seq_op_list.nextval, '+', t(i).debit_id, t(i).op_amount);
  22.         COMMIT;
  23.     END LOOP; 
  24. END;
  25. ]'  ;
  27. BEGIN
  28.     sqlstmt := replace(sqlstmt, '${thr}', 1);
  29.     EXECUTE IMMEDIATE sqlstmt ;
  30. END;
  31. /
  32. delimiter ;

手动并行处理技术有如下优点：

  • 在大数据量处理时，数据库端会话能并行处理。
  • 如果各个分表 raw_list_${thr} 的数据量差不多，则这32个并行的会话处理时间基本是一样的。

手动并行处理技术有如下缺点：

  • 代码会相对比较长，复杂，不易理解。
  • 应用程序在正式处理数据之前必须额外做一些准备工作(如为不同会话分配不同的参数)。
  • 多个并发的会话在对相同数据做修改时可能会增加一些锁冲突和内部竞争。
  • 相比其他处理技术，手动并行处理技术会提高数据库端 CPU 利用率。

关于基于集合的数据处理

基于集合的处理是一种 SQL 技术，它的处理对象是数据库中的数据集。

在基于集合的模型中，SQL 语句定义了结果，并允许数据库确定最有效的获取方法。 相反，迭代算法使用条件逻辑将数据库中的每一行或每一组数据行拉到客户端应用程序，处理客户端上的数据，然后将数据发送回数据库。基于集合的处理消除了网络往返和数据库API开销，因为数据不会离开数据库。

还是以前面例子为例，以下 SQL 语句使用基于集合的算法满足这一要求：

  1. INSERT INTO op_list(op_id, op_code, account_id, amount, gmt_create) 
  2. SELECT  seq_op_list.nextval, '+', debit_id, op_amount, sysdate
  3. FROM raw_list;
  4. INSERT INTO op_list(op_id, op_code, account_id, amount, gmt_create) 
  5. SELECT  seq_op_list.nextval, '-', credit_id, op_amount, sysdate
  6. FROM raw_list;
  7. COMMIT;

INSERT 语句后的 SELECT 子句读取了所有数据同时一次性写入到目标表，然后应用一次性提交事务。

相比迭代算法，基于集合技术有如下优点：

  • 中等规模数据量情况下，基于集合处理技术性能会更好一些。
  • 代码更加简洁明了。
  • 相比手动并行技术，基于集合的处理技术可以发挥数据库内部的并行处理技术。
  • 如果涉及到表连接，数据库端可能会使用更高效的 HASH 连接算法，这个比迭代算法中的嵌套循环算法要好很多。

相比迭代算法，基于集合技术有如下缺点：

  • SQL 逻辑比较简单，不能实现复杂的业务逻辑。
  • 可能产生大事务，如果数据规模非常大的时候，大事务可能导致数据库内存资源紧张。