【实战】批量插入数据，如何优化?

今天我们从实战出发，来看一看批量插入数据应该如何优化？

需求背景

公司有一个资金平台系统，对内承接集团内所有子公司的交易指令，对外与各个银行交互，发送交易指令。

该平台之前与各子公司交互采用的是oracle的dblink方式，100W的交易数据基本上几分钟就同步过来了。

但是随着公司全面推进信创，oracle数据库需要用国产数据库进行替代，那随之面临的问题是，该平台与集团内各子公司的交互方式应该换成什么？我们需要对该平台与各子公司的交互方式重新进行设计。

需求要求

集团内各子公司数据推送的特点可以简化为每隔1 个小时，会有 300 万条交易指令数据的推送。与子公司交互方式的设计方案需要满足如下要求：

1、需要快速的接收大量的数据，不能造成交易等待。

2、需要确保字段完整、无篡改.

3、不能出现重复交易，即历史已存在相同的交易数据时，新数据不能落库。

4、单条数据接收异常，不能影响其他数据的接收。

交互方案

经过深思熟虑，决定引入中间件 RocketMQ作为资金平台与集团各子公司间交互的中间件。即集团各子公司将交易指令推送到MQ中，资金平台消费MQ中的交易指令。

交互方案POC 验证

选择一家子公司进行交互方案POC验证，验证步骤如下：

1、子公司发送800万条数据到MQ队列，交易平台进行消费，一个MQ消息内包含100条交易指令数据。

2、交易平台消费逻辑为：获取数据->非空校验->防重校验->单条数据部分字段落表 A->单条数据部分字段落表 B->发送交易指令接收结果到 MQ 队列。

验证的环境如下：

• 数据库为oracle 数据库。
• 应用使用druid数据库连接池，初始链接数设置为10，最大链接数设置为20。
• 使用@Transactional进行事务控制，防止落表A成功，落表B失败。
• 应用单实例消费。

验证结果：

落表228710条数据，耗时4672秒，平均每秒落表48.95条数据。这个速度显然是不行的，按这个速度，单实例消费 300万条数据需要17个小时左右。

我们要想想有什么可以优化的方法？

方法一：

@Transactional事务会影响数据库插入的性能，落A表成功，落B表失败属于小概率事件。我们可以去掉@Transactional事务，利用在落B表失败时手动捕获异常，然后根据关联的主键删除A表数据的方式来手动实现事务。

方法一验证结果：

落表 379933 条数据，耗时6300 秒，平均每秒落表 60.31 条数据。数据插入表的速度有明显提升，因此去掉事务这个方法有效，可以提升插入数据的速度。

方法二：

我们这个场景是快速消费数据的场景，需要频繁和数据库交互。而我们使用了数据库连接池，这个时候数据库连接池的数量就是一个很关键的配置。应用使用druid 数据库连接池，初始链接数设置为 10，最大链接数设置为 20，我们适当调大数据库链接池的数量，将初始链接数设置为30，最大链接数设置为80，再试试。

方法二验证结果：

落表854521 条数据，耗时14871秒，平均每秒落表 57.46 条数据。方法二是在方法一的基础上改动的，因此结果应该与方法一的结果进行对比。对比方法一、二，发现调大连接池数量并不能直接提升插入速度，反而还会降低。

数据库连接池的数据并非调的越大越好，应该根据具体的业务场景，压测出一个最优的数据库连接池数量的配置。

方法三：

我们先把数据库连接池的数量调回初始链接数设置为10，最大链接数设置为 20。每个 MQ 消息包含100 条交易指令，而我们进行单条数据插入的原因是为了避免单条数据插入失败对其他数据的影响。

其实仔细想一想，插入失败的场景毕竟是少数，我们可以采用批量插入。而当批量插入异常时，再将失败的整批再次单条插入来隔离单条异常。用这个方法再试试。

方法三验证结果：

落表621300条数据，耗时9029秒。平均每秒落表68.81条数据。相比方法一，每秒多落了8条数据。说明单笔插入改为批量插入方法有效，可以提升插入数据的速度。

总结

本期我们从实战出发，根据项目的实际情况介绍了三种批量插入数据的优化方法，优化的效果我们也做了验证。

实际在我们的开发过程中，当系统出现性能瓶颈时，要综合考虑业务场景的特性去做优化

方案（当然只有见的优化方案多了，那时候才能想的到），同时也要针对业务场景去做优化方案验证。毕竟程序设计没有银弹。

同样的优化方案在不同的业务场景下得到效果可能完全不同。比如增加数据库连接数，在我们文中的例子中优化效果不好，但是在我们另外一个高并发业务场景中，连接数从30调到80，每秒能增加20条数据的插入。当然程序设计是一个权衡得失的过程，增加单实例的数据库连接数必然带来的是，可扩展的总实例数的下降。因为数据库的最大连接数是固定的，所有实例的数据库连接相加必然得小于数据库的最大连接数。

最后回到这个实战上，经过3个方案的优化，单实例消费300W数据仍然需要12小时的时间，如何满足1小时消费300W数据呢？

1. 继续根据业务场景优化批量插入数据的效率。
2. 增加消费实例数。