【转载】【MyBatis】几种批量插入效率的比较
文章是转载网络上的,
内容贴过来,格式乱了,
建议参考原文: 【MyBatis】几种批量插入效率的比较
批处理数据主要有三种方式:
- 反复执行单条插入语句
- foreach 拼接 sql
- 批处理
一、前期准备
基于Spring Boot + Mysql,同时为了省略get/set,使用了lombok,详见pom.xml。
1.1 表结构
id 使用数据库自增。
DROP TABLE IF EXISTS `user_info_batch`; CREATE TABLE `user_info_batch` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id', `user_name` varchar(100) NOT NULL COMMENT '账户名称', `pass_word` varchar(100) NOT NULL COMMENT '登录密码', `nick_name` varchar(30) NOT NULL COMMENT '昵称', `mobile` varchar(30) NOT NULL COMMENT '手机号', `email` varchar(100) DEFAULT NULL COMMENT '邮箱地址', `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `gmt_update` timestamp NULL DEFAULT NULL COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT 'Mybatis Batch';
1.2 项目配置文件
细心的你可能已经发现,数据库url 后面跟了一段 rewriteBatchedStatements=true,有什么用呢?先不急,后面会介绍。
数据库配置
spring: datasource: url: jdbc:mysql://47.111.118.152:3306/mybatis?rewriteBatchedStatements=true username: mybatis password: password driver-class-name: com.mysql.cj.jdbc.Driver # mybatis mybatis: mapper-locations: classpath:mapper/*.xml type-aliases-package: cn.van.mybatis.batch.entity
1.3 实体类
@Data @Accessors(chain = true) public class UserInfoBatchDO implements Serializable { private Long id; private String userName; private String passWord; private String nickName; private String mobile; private String email; private LocalDateTime gmtCreate; private LocalDateTime gmtUpdate; }
1.4 UserInfoBatchMapper
public interface UserInfoBatchMapper { /** 单条插入 * @param info * @return */ int insert(UserInfoBatchDO info); /** * foreach 插入 * @param list * @return */ int batchInsert(List<UserInfoBatchDO> list); }
1.5 UserInfoBatchMapper.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="cn.van.mybatis.batch.mapper.UserInfoBatchMapper"> <insert id="insert" parameterType="cn.van.mybatis.batch.entity.UserInfoBatchDO"> insert into user_info_batch (user_name, pass_word, nick_name, mobile, email, gmt_create, gmt_update) values (#{userName,jdbcType=VARCHAR}, #{passWord,jdbcType=VARCHAR},#{nickName,jdbcType=VARCHAR}, #{mobile,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{gmtCreate,jdbcType=TIMESTAMP}, #{gmtUpdate,jdbcType=TIMESTAMP}) </insert> <insert id="batchInsert"> insert into user_info_batch (user_name, pass_word, nick_name, mobile, email, gmt_create, gmt_update) values <foreach collection="list" item="item" separator=","> (#{item.userName,jdbcType=VARCHAR}, #{item.passWord,jdbcType=VARCHAR}, #{item.nickName,jdbcType=VARCHAR}, #{item.mobile,jdbcType=VARCHAR}, #{item.email,jdbcType=VARCHAR}, #{item.gmtCreate,jdbcType=TIMESTAMP}, #{item.gmtUpdate,jdbcType=TIMESTAMP}) </foreach> </insert> </mapper>
1.6 预备数据
为了方便测试,抽离了几个变量,并进行提前加载。
private List<UserInfoBatchDO> list = new ArrayList<>(); private List<UserInfoBatchDO> lessList = new ArrayList<>(); private List<UserInfoBatchDO> lageList = new ArrayList<>(); private List<UserInfoBatchDO> warmList = new ArrayList<>(); // 计数工具 private StopWatch sw = new StopWatch();
为了方便组装数据,抽出了一个公共方法。
private List<UserInfoBatchDO> assemblyData(int count){ List<UserInfoBatchDO> list = new ArrayList<>(); UserInfoBatchDO userInfoDO; for (int i = 0;i < count;i++){ userInfoDO = new UserInfoBatchDO() .setUserName("Van") .setNickName("风尘博客") .setMobile("17098705205") .setPassWord("password") .setGmtUpdate(LocalDateTime.now()); list.add(userInfoDO); } return list; }
预热数据
@Before public void assemblyData() { list = assemblyData(200000); lessList = assemblyData(2000); lageList = assemblyData(1000000); warmList = assemblyData(5); }
二、反复执行单条插入语句
可能‘懒’的程序员会这么做,很简单,直接在原先单条insert语句上嵌套一个for循环。
2.1 对应 mapper 接口
int insert(UserInfoBatchDO info);
2.2 测试方法
因为这种方法太慢,所以数据降低到 2000 条
@Test public void insert() { log.info("【程序热身】"); for (UserInfoBatchDO userInfoBatchDO : warmList) { userInfoBatchMapper.insert(userInfoBatchDO); } log.info("【热身结束】"); sw.start("反复执行单条插入语句"); // 这里插入 20w 条太慢了,所以我只插入了 2000 条 for (UserInfoBatchDO userInfoBatchDO : lessList) { userInfoBatchMapper.insert(userInfoBatchDO); } sw.stop(); log.info("all cost info:{}",sw.prettyPrint()); }
2.3 执行时间
该方式插入2000 条数据,执行三次的平均时间:60991 ms。
三、foreach 拼接SQL
3.1 对应mapper 接口
int batchInsert(List
3.2 测试方法
该方式和下一种方式都采用20w条数据测试。
@Test public void batchInsert() { log.info("【程序热身】"); for (UserInfoBatchDO userInfoBatchDO : warmList) { userInfoBatchMapper.insert(userInfoBatchDO); } log.info("【热身结束】"); sw.start("foreach 拼接 sql"); userInfoBatchMapper.batchInsert(list); sw.stop(); log.info("all cost info:{}",sw.prettyPrint()); }
3.3 执行时间
该方式插入20w 条数据,执行三次的平均时间:18852 ms。
四、批处理
该方式 mapper 和xml 复用了 2.1。
4.1 rewriteBatchedStatements 参数
我在测试一开始,发现改成 Mybatis Batch提交的方法都不起作用,实际上在插入的时候仍然是一条条记录的插,而且速度远不如原来 foreach 拼接SQL的方法,这是非常不科学的。
后来才发现要批量执行的话,连接URL字符串中需要新增一个参数:rewriteBatchedStatements=true
rewriteBatchedStatements参数介绍
MySql的JDBC连接的url中要加rewriteBatchedStatements参数,并保证5.1.13以上版本的驱动,才能实现高性能的批量插入。MySql JDBC驱动在默认情况下会无视executeBatch()语句,把我们期望批量执行的一组sql语句拆散,一条一条地发给MySql数据库,批量插入实际上是单条插入,直接造成较低的性能。只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL。这个选项对INSERT/UPDATE/DELETE都有效。
4.2 批处理准备
手动注入 SqlSessionFactory
@Resource private SqlSessionFactory sqlSessionFactory;
测试代码
@Test public void processInsert() { log.info("【程序热身】"); for (UserInfoBatchDO userInfoBatchDO : warmList) { userInfoBatchMapper.insert(userInfoBatchDO); } log.info("【热身结束】"); sw.start("批处理执行 插入"); // 打开批处理 SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH); UserInfoBatchMapper mapper = session.getMapper(UserInfoBatchMapper.class); for (int i = 0,length = list.size(); i < length; i++) { mapper.insert(list.get(i)); //每20000条提交一次防止内存溢出 if(i%20000==19999){ session.commit(); session.clearCache(); } } session.commit(); session.clearCache(); sw.stop(); log.info("all cost info:{}",sw.prettyPrint()); }
该方式插入20w 条数据,执行三次的平均时间:9092 ms。
4.4 如果数据更大
当我把数据扩大到 100w 时,foreach 拼接 sql 的方式已经无法完成插入了,所以我只能测试批处理的插入时间。
测试时,仅需将 【4.2】测试代码中的 list 切成 lageList 测试即可。
该方式插入100w 条数据,执行三次的平均时间:32467 ms。
五、总结
批量插入方式数据量执行三次的平均时间循环插入单条数据200060991 msforeach 拼接sql20w18852 ms批处理20w9092 ms批处理100w32467 ms
循环插入单条数据虽然效率极低,但是代码量极少,数据量较小时可以使用,但是数据量较大禁止使用,效率太低了;
foreach 拼接sql的方式,使用时有大段的xml和sql语句要写,很容易出错,虽然效率尚可,但是真正应对大量数据的时候,依旧无法使用,所以不推荐使用;
批处理执行是有大数据量插入时推荐的做法,使用起来也比较方便。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库