【Oracle】准实时大规模数据提取

文中使用的Oracle版本为10g。

这篇文章是之前本人在前公司内部做可行性分析报告中的其中一个板块的内容，具体讲述的是为了做大规模数据提取和数据清洗做了一个试验demo。先说结论，一般来说像这种操作不应优先考虑关系型数据库去解决。本文中提到的采用Job模拟多线程操作的方式只是不得已而为之，请各位看官慎用。

前因

需要将数据库中表（190+）按照人员编码和业务发生时间进行汇总和排序，得到用户在某时间内的所有操作行为。一般情况下想到使用UNION ALL连接后去重排序，但实际上会面临以下6个问题：

1. 需要数据提取的表目前有197个且以后还会随着业务增长而增加（不要问为什么这样设计，别人项目管不了）；
2. 表中数据都是千万级或者亿级，查询获取数据量大；
3. 数据获取动作要求准实时，也就是说在用户触发后需要短时间内获得反馈结果；
4. 服务器性能低，基本上不存在系统调优的情况；
5. 数据库表不能进行改造，会有别的项目直接访问这些表（不能理解知道为什么允许这样做）；
6. Java后台程序尽量保持良好的可读性；

基于上述的情况，其实不难分析出两点：

第一，表个数不固定，所以功能必须是可动态配置的；

第二，大量的表和大量的数据获取，在SQL语句上不能直接使用UNION ALL操作。需要考虑到可执行SQL 语句长度会受到限制。譬如，每一个表SQL查询格式将会是

select 字段a,字段b,字段c,字段d,字段e,字段f 
from 表名
where gather_person = 'gather_person内容'
and gather_date between to_date('开始时间','yyyy-mm-dd HH24:MI:SS') 
and to_date('结束时间','yyyy-mm-dd HH24:MI:SS');

这里通过WHERE条件缩小表数据查询范围，在得到数据集后再进行UNION ALL其他数据集操作。但是这197个表下来这个SQL语句将非常庞大。运行起来会占用大量的CPU和内存资源。

其次，即使是使用了UNION ALL连接数据库也是采取串行的查询方式，对于速度提升没有太大帮助，因此使用UNION ALL的方案先按下不表。

既然不能通过一次查询得到结果，那就采用分步获取后再统一查询这种方法（虽然有点麻烦）。通过存储过程进行分步收集数据后再通过统一查询得出结果，初步逻辑结构如下：

存储过程的处理逻辑基本按照上图所示。这里最重要是利用job来模拟多线程处理来提高数据获取效率。job会分别起一个会话来获取数据，执行后数据将会放入一个结果表中。该结果表作为获取数据存储对象按照基础字段建立好索引和分区。最后条件查询结果表将去重后的结果集放入游标返回。

由于用户会进行多次数据获取操作，无可避免结果表中会存在重复的数据出现。这部分重复数据将会在晚上执行另一个job进行数据清理。

测试代码讲解

这次的存储过程是用package来做，因为整个查询和数据获取的过程都是一个整体，所以放在一起在可读性和完整性都比较好，如下图：

package提供存储过程接口，如下图：

存储过程入口是generate_trajectory方法，dynamic_thread和batch_generate都是嵌套调用的关系，而最后的generate_test_data是用来生成测试数据的，在实际操作上不需要用到。

其中package body 的generate_trajectory方法，如下图：

定义了各种变量，最后为job定义了一个动态数组

为传入参数设定一个默认值，由于传入参数中若设定默认值会被传入NULL所替代，所以在这里需要用nvl函数进行预设。在获取数据之前先查询该用户在该时间段是否已经存在数据在结果表中，若存在先进行删除。

根据传入参数thread和配置表中数据进行除法，向下取整后得到一个job中需要执行多少个表的统计，并按照数据进行后续的分页统计。这里调用了dynamic_thread方法，这个方法执行后将返回job编码，获取job编码后放入动态数组中。

由于除法会存在不整除的情况，这部分数据获取将会单独拿出来进行获取，这里直接调用batch_generate方法处理即可。

通过循环去监听全部Job是否已经全部完成，直接统计结果表中去重后的tablename个数，由于每个表的插入语句都是批量插入的，若结果表中存在该表名，则证明该表已经获取完毕（下面会讲到）。

这里对退出循环监听有一些判断，还有的就是超过了5次循环后将会认为超时会强制退出。在这里为了不频繁查询结果，使用了dbms_lock.sleep系统函数做了一下休眠。

退出监听后将会认为是数据获取完毕，这时就需要删除掉刚刚产生出来的job，最后通过查询语句将结果集放入到一个游标中进行返回。

package body 的dynamic_thread方法，如下图：

这个存储过程就是用来生成job用的，job参数是必须要返回的，不然不知道对应的job是那个。

package body 的batch_generate方法，如下图：

定义好各种的变量和游标。

游标SQL用来分页获取配置表中的字段和表名。

通过遍历游标获取到变量信息后进行了一些数据的replace操作是为了使结果表与原始表字段能对应起来。将插入语句写成 insert into… select … from ...的方式进行批量插入当中用到/*+APPEND*/进行直接路径插入加快插入速度。

至此处理过程结束。

package body 的generate_test_data方法，如下图：

传入参数是rowcounts和gather_person_custom字段，调用这个存储过程的时候可以定义给配置表中定义的每个表批量新增多少条数据，并且这些数据对应的gather_person为那个人。方便数据库层面对数据进行调试。

这里的语句针对目前配置表中数据进行整理，其中包括一些表复合主键的问题处理。

遍历游标获取到表名，时间字段和主键字段。插入前先删除之前上次操作的该人员的数据，并对表名进行修改，后续需要创建临时表，所以这里需要对表名进行修改保证表名在30个字符以内。

这里用了类似java的try...catch...方式创建表。其中create table … as select … from ...在创建临时表时可能会出现表已存在的情况，这时候需要先将已存在的表删除（因为已存在的表中还存在数据，与其删除数据，还不如用drop table … purge方式连表一起删除，这样还可以释放一些表空间），之后再重新新建表。

在使用create table … as select … from ...创建表的时候其实数据也复制了过去了，所以之后添加数据可以通过count方式获取到临时表中记录条数，若新增条数达到用户设定界限则退出循环。若有些表本来就不存在数据的情况下就直接退出循环。

循环插入数据是通过insert into … select … from ...的方式进行插入，由于各个表的字段不同，这里就用*来代替了。

数据插入完成后，先在临时表中进行数据整理，包括更新时间、更新主键和更新gather_person。然后再重新批量插入回原始表中。插入完成后将会把临时表删除释放表空间。

测试结果

v1.0

首先使用了generate_test_data方法为这190+张表每张表插入10w条数据。存储过程第一次获取数据此时结果表为空，启动参数如下：

虽同时启动10个job执行但结果不太理想，共耗时11138秒，如下图：

这样的结果显然是不能接受的，于是想出了第二套方案。

v1.1

在generate_trajectory方法中屏蔽了删除原来数据的操作，如下图：

由于可以通过晚上从定时器上进行去重工作，这部分的删除工作可以忽略不做来减少时间。

接着在数据插入之前先删除表中的所有索引，如下图：

在启动多个job执行之前先干掉结果表中的所有索引来提高插入速度。

再者，循环启动多线程的时候设定一个休眠时间，如下图：

休眠时间为每个线程生成产生一个时间差，不至于多个线程同时启动抢占资源造成锁表现象出现。

将原来“查询结果表中表的数量是否与配置表中一致”的判断条件去掉，换成用job是否完成来判断，如下图：

结果表中数据量庞大的时候用count来统计表中数量，其实是做了全表扫描性能很差，查询一次要差不多180+秒。于是回归原点来想了一下，反正这里为了都是判断所有的job是否完成这样的话还不如直接找job的信息来判断，于是想到了user_jobs表。当表中this_sec(执行时间)为NULL则表明job已经完成了这时就可以remove掉，若不是就休眠5秒再来判断。

以查询速度来看，这个比之前查询结果表统计的时间少很多而且更能够确切知道job是否已经完成了，完成一个干掉一个。

在删除掉job之后就要重新为结果表添加上索引，如下图：

在最后查询的时候将并行查询打开，如下图：

由于结果表是一个分区表，而且数据量庞大。所以查询开启并行觉得很有必要。

在创建表的时候就将并行开启了。

同理，在batch_generate方法中，在插入部分也将并行开启了，如下图：

维护数据方法data_maintain里面将去重的删除方法改成分批删除，并且通过游标查询找到rowid，直接通过rowid定位进行delete操作，提高删除效率。如下图：

每一个批次10000数据删除。

v1.1测试结果（新增插入）

同样的启动参数，整个获取过程用了1949.778秒。在相同的条件下（结果表为空）v1.1比v1.0中的快了差不多6倍。

v1.1测试结果（存量更新/插入）

在原有数据的基础上再一次执行获取数据，用了2476.376秒。

v1.2

再继续查看服务器允许的最大并行数

修改结果表的并行度改成16

修改存储过程中并行数为14

v1.2测试结果（新增插入）

在结果表为空的情况下获取的结果是1933.32秒，看上去增加并行数并没有减少执行时间。再深入到获取每个方法的执行时间后发现，其实时间都消耗在创建索引的地方去了，因为创建索引是需要内部排序的，三个索引就需要全表扫描三次，这个时间的确是不可忽视。

结论

像这种需要获取大量大表的操作，在关系型数据库中其实不太适合实时获取的，若将这部分数据都放在后台拆分后定时获取效果将会好很多。在使用并行操作后性能瓶颈已不在插入操作，而在重建索引。如果在插入数据前不屏蔽索引又会产生大量索引的redo信息，导致插入慢情况。

经过比较后发现，插入后重建索引效率比直接插入的效率还要高，但是根据demo来看，试验结果还是令人不太满意。目前存储过程在不重建索引的情况下能够将190+表共1800w+条（设定值内）数据在15分钟内插入完成，因此重建索引问题不解决，不推荐使用关系型数据库做类似的获取操作。