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 WHERE p.plimit=100000 and p.pmethod=‘batchInsert2‘;

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

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

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

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

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

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

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

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

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

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

3.6.2  测试数据汇总

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

3.7  JDBC测试规律总结

3.7.1  规律一：批量插入尽量使用事务控制

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

3.7.2  规律二：事务批量处理的数据量不要太大

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

3.7.3  规律三：适当地设置MySQL参数可以提高数据库性能

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

3.7.4  规律四：处理数据量和时间不是成倍增长

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

3.7.5  规律五：数据库性能随数据量的不断加大而降低

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

4      结论

经过以上测试得出结论：Mybatis的批量适合处理少了数据的批量处理，而JDBC适合大数据量的批量处理。据此，采用JDBC批量+事务处理大数据量的表插入操作是最合适的。

5      方案

因为要考虑JVM的GC所以数据应该限制一下，但鉴于Mybatis大数据量的批量插入效率不高，所以根据数据大小分段治理。

5.1  小于1W使用：Mybatis批量插入方案

对JVM进行调优，但主要的性能瓶颈在批量插入操作。鉴于mybatis在项目开发方面的优势，数据量很小的情况下还是建议使用Mybatis。

5.2  大于1W小于10W使用：JDBC批量+事务处理

对JVM进行调优（设置Stack和GC等）。一般操作30秒以内是可以容忍的性能耗时。

5.3  10W以上数据使用：数据分批+JDBC批量+事务处理

对JVM进行调优（设置Stack和GC等），通过数据分批处理。对于分批处理需要借鉴前面的测试数据来定义分批量的大小，主要是对操作时间调优。

如果是100W、1000W级别的数据量，分批处理可以很大程度地提升插入效率，具体的分批需要通过实践去分配，数据量太大这里就不做实验了。

6      参考资料

JDBC实现往MySQL数据库插入百万数据：http://www.cnblogs.com/fnz0/p/5713102.html

MySQL Max_allowed_packet: http://stackoverflow.com/questions/8062496/how-to-change-max-allowed-packet-size

Spring Mybatis和Spring JDBC的插入效率比较：http://toplchx.iteye.com/blog/1988254

注：另外一种比较高效的导入方式是生成一个文本文件使用MySQL的JDBC LOAD DATA LOCAL INFILE；参考示例：

MySQL使用JDBC LOAD DATA LOCAL INFILE导入注意事项

MySQL使用LOAD DATA LOCAL INFILE数据3-5秒导入40W数据

本文测试文档代码和数据库均已上传：http://download.csdn.net/detail/boonya/9812860

Mybatis与JDBC批量插入MySQL数据库性能测试及解决方案

原文：http://blog.csdn.net/boonya/article/details/70157820