Mybatis与JDBC批量插入MySQL数据库性能测试

Author:boonya

Date:2017-04-13

1      背景

系统中需要批量生成单据数据到数据库表，所以采用批量插入数据库的方式。由于系统中ORM操作集成使用的是Mybatis来完成的。

在Mybatis中操作一般使用批量插入的方式如下：

<insert id="insertBatch" parameterType="java.util.List"  >

     insert into userinfo (uid, uname, uphone, uaddress)

     values

    <foreach collection="list" item="item"index="index" separator=",">

    (#{item.uid,jdbcType=INTEGER},

     #{item.uname,jdbcType=VARCHAR},

     #{item.uphone,jdbcType=VARCHAR},

     #{item.uaddress,jdbcType=VARCHAR}

     )

   </foreach>

   </insert>

在实际生产中发现，这样调用的效率并不高，于是我们迫切地需要寻找一种处理批量插入性能较高的方式——回归原生数据库JDBC操作。我们要分析Mybatis和JDBC的插入性能，来决策适合我们生产系统的批量插入方式。

参考资料中有一篇文章是对Spring Mybatis和Spring JDBC插入效率的测试，由于测试的数据量不多不予置评。

2      Mybatis

2.1  Mybatis测试样本1K

测试10组数据：

2.2  Mybatis测试样本2K

测试10组数据：

2.3  Mybatis测试样本4K

测试10组数据：

2.4  Mybatis测试样本6K

测试10组数据：

2.5  Mybatis测试样本8K

测试10组数据：

2.6  Mybatis测试样本10K

测试10组数据：

2.7  Mybatis测试样本15K

测试10组数据：

2.8  Mybatis测试样本20K

测试10组数据：

2.9  性能测试样本平均值

2.9.1  测试样本SQL

2.9.1.1       毫秒级SQL【time(ms)】

-- DELETE FROMprocess_spend_time_result  WHEREpmethod='mybatis';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=1000 AND p.pmethod='mybatis';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=2000 AND p.pmethod='mybatis';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=4000 AND p.pmethod='mybatis';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=6000 AND p.pmethod='mybatis';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=8000 AND p.pmethod='mybatis';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=10000 AND p.pmethod='mybatis';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=15000 AND p.pmethod='mybatis';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=20000 AND p.pmethod='mybatis';

2.9.1.2       秒级SQL【time(s)】

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=1000 AND p.pmethod='mybatis';

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=2000 AND p.pmethod='mybatis';

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=4000 AND p.pmethod='mybatis';

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=6000 AND p.pmethod='mybatis';

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=8000 AND p.pmethod='mybatis';

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=10000 AND p.pmethod='mybatis';

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=15000 AND p.pmethod='mybatis';

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=20000 AND p.pmethod='mybatis';

2.9.2  测试数据汇总

数据4舍5入保留3位小数

2.10    Mybatis批量插入问题

2.10.1             问题一：处理性能低下

处理10000条耗时：34292ms

2.10.2             问题二：批量处理数据量大小受限

Windows下需要修改MySQL的my.ini文件加入如下配置内容：

max_allowed_packet=500M

Linux下是在/my.cnf修改添加如上内容。

也可以直接这样设置：

SET GLOBAL max_allowed_packet=1073741824;

但MySQL重启后就不起作用了。

2.10.3             问题三：线程处理锁表

在通过多线程并发处理的时，很容易导致数据库表锁表，使得后续的操作无法进行。

2.10.4             问题四：导致对象回收GC问题

对象超出GC对象回收阀值，导致程序中断。

3      JDBC

3.1  JDBC测试数据1W

测试10组数据：

3.2  JDBC测试数据5W

测试10组数据：

3.3  JDBC测试数据10W

测试10组数据：

3.4  JDBC测试数据25W

测试10组数据：

3.5  JDBC测试数据50W

测试10组数据：

3.6  JDBC测试样本均值

3.6.1  测试样本SQL

3.6.1.1       毫秒级SQL【time(ms)】

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=10000 and p.pmethod='batchInsert';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=10000 and p.pmethod='batchInsert2';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=10000 and p.pmethod='batchInsertWithTransaction';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=10000 and p.pmethod='batchInsertWithTransaction2';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=50000 and p.pmethod='batchInsert';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=50000 and p.pmethod='batchInsert2';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=50000 and p.pmethod='batchInsertWithTransaction';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=50000 and p.pmethod='batchInsertWithTransaction2';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=100000 and p.pmethod='batchInsert';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=100000 and p.pmethod='batchInsert2';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=100000 and p.pmethod='batchInsertWithTransaction';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=100000 and p.pmethod='batchInsertWithTransaction2';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=250000 and p.pmethod='batchInsert';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=250000 and p.pmethod='batchInsert2';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=250000 and p.pmethod='batchInsertWithTransaction';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=250000 and p.pmethod='batchInsertWithTransaction2';

SELECTAVG(p.ptime) FROM process_spend_time_resultp WHEREp.plimit=500000 andp.pmethod='batchInsert';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=500000 and p.pmethod='batchInsert2';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=500000 and p.pmethod='batchInsertWithTransaction';

SELECTAVG(p.ptime) FROMprocess_spend_time_result p WHERE p.plimit=500000 and p.pmethod='batchInsertWithTransaction2';

3.6.1.2       秒级SQL【time(s)】

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=10000 and p.pmethod='batchInsert';

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=10000 and p.pmethod='batchInsert2';

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=10000 and p.pmethod='batchInsertWithTransaction';

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=10000 and p.pmethod='batchInsertWithTransaction2';

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=50000 and p.pmethod='batchInsert';

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=50000 and p.pmethod='batchInsert2';

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=50000 and p.pmethod='batchInsertWithTransaction';

SELECTAVG(p.ptime)/1000 FROM process_spend_time_resultp WHEREp.plimit=50000 andp.pmethod='batchInsertWithTransaction2';

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p WHERE p.plimit=100000 and p.pmethod='batchInsert';

SELECTAVG(p.ptime)/1000 FROMprocess_spend_time_result p