在开发过程中,有时我们会碰到将大批量的数据入库的场景,那么我们一般有下面三种方式入库:
下面我们用一个案例来测试一下,看下三种方式哪种效率最好
现在我数据库新建一张表t_user,建表语句如下:
DROP TABLE IF EXISTS `t_user`; CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名', `age` int(11) NULL DEFAULT NULL COMMENT '年龄', `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '手机号', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表' ROW_FORMAT = DYNAMIC;
我这里MySQL数据库版本是5.5.28
然后我用上面三种方式分别向t_user表中存5万条数据,每次存数据前,先清空一下表数据,使用下面语句快速清除表数据:
truncate table t_user;
每种方式我都测试5次,然后每种方式我都计算一个耗时平均值,看哪种方式耗时最小
这是我的基础项目:https://gitee.com/colinWu_java/spring-boot-base.git
我会在此基础项目上做测试
ExecutorType.BATCH介绍:
下面开始编码
在UserController新增下面接口
/** * 测试大批量数据插入数据库 * 方式1:用一个 for 循环,把数据一条一条的插入 * @return */ @GetMapping("/insertUser1/{count}") public JSONResult insertUser1(@PathVariable("count") Integer count){ return userService.insertUser1(count); }
userService代码:
public JSONResult insertUser1(Integer count) { //如果自动提交设置为true,将无法控制提交的条数。所以我这里设置为false,,改为统一提交 SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false); UserMapper uMapper = session.getMapper(UserMapper.class); //获取测试用户集合数据 List<User> userList = Tools.getUserList(count); int commitCount = 5000;//每次提交的数量条数 long startTime = System.currentTimeMillis(); for(int i=0; i<userList.size(); i++){ uMapper.addUserOne(userList.get(i)); if (i != 0 && i % commitCount == 0) { session.commit(); } } session.commit(); long endTime = System.currentTimeMillis(); log.info("方式1耗时:{}", (endTime - startTime)); return JSONResult.success(); }
在UserMapper中新增addUserOne方法:
Integer addUserOne(User user);
对应xml代码:
<insert id="addUserOne"> insert into t_user (name, age, phone) values (#{name}, #{age}, #{phone}) </insert>
getUserList方法代码如下,就是获取指定数量的测试用户数据而已:
/** * 获取指定数量的用户测试对象 * @param count 数量 * @return */ public static List<User> getUserList(int count){ List<User> userList = new ArrayList<>(); User user = null; for(int i=1; i<=count; i++){ user = new User(); user.setName("王天霸" + i + "号"); user.setAge(i); user.setAge(i); user.setPhone("18022222222"); userList.add(user); } return userList; }
注意事项:
如果单次提交给MySQL数据库的数据量太大的话,可能会报如下错误:
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (204444558 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
出现这个错误的原因是:
大数据量的插入或更新会被 max_allowed_packet 参数限制,导致失败,因为MySQL根据配置文件会限制server允许接收的数据包大小
解决方案:
修改MySQL的配置参数
打开Mysql命令客户端:
然后输入密码之后输入下面命令:
show VARIABLES like '%max_allowed_packet%';
结果:
用这个值除以1024*1024等于200M,那我这里就是设置的200M,根据实际情况设置该值,执行如下命令即可:
set global max_allowed_packet = 200*1024*1024;
设置完成之后,需要关闭MySQL服务,然后再重启(注意,这里最大设置为:1G)
重启成功后,需要关掉上面的MySQL命令客户端,再重新打开一次,查看是否设置成功
注意:
url: jdbc:mysql://127.0.0.1:3306/test1?allowMultiQueries=true&rewriteBatchedStatements=true
//allowMultiQueries=true,允许一次性执行多条SQL,批量插入时必须在连接地址后面加allowMultiQueries=true这个参数
//rewriteBatchedStatements=true,批量将数据传给MySQL,数据库会更高性能的执行批量处理,MySQL数据库版本在5.1.13以上,才能实现高性能的批量插入
UserController新增方法:
/** * 测试大批量数据插入数据库 * 方式2:foreach标签批量插入 * @return */ @GetMapping("/insertUser2/{count}") public JSONResult insertUser2(@PathVariable("count") Integer count){ return userService.insertUser2(count); }
insertUser2代码:
public JSONResult insertUser2(Integer count) { //获取测试用户集合数据 List<User> userList = Tools.getUserList(count); int countItem = 5000;//每次提交的记录条数 int userSize = userList.size(); List<User> userListTemp = new ArrayList<>(); long startTime = System.currentTimeMillis(); for (int i = 0, n=userSize; i < n; i++) { User user= userList.get(i); userListTemp.add(user); if ((i>0 && i % countItem == 0) || i == userSize - 1) { //每5000条记录提交一次 userMapper.insertUserBatch(userListTemp); userListTemp.clear(); } } long endTime = System.currentTimeMillis(); log.info("方式2耗时:{}", (endTime - startTime)); return JSONResult.success(); }
UserMapper的insertUserBatch方法:
void insertUserBatch(@Param("userList") List<User> userList);
对应xml代码:
<insert id="insertUserBatch"> insert into t_user (name, age, phone) values <foreach collection="userList" item="user" separator=","> (#{user.name}, #{user.age}, #{user.phone}) </foreach> </insert>
UserController新增方法:
@Autowired public UserService userService; /** * 测试大批量数据插入数据库 * 方式3:mybatisplus自带的saveBatch批量新增方法 * @return */ @GetMapping("/insertUser3/{count}") public JSONResult insertUser3(@PathVariable("count") Integer count){ //获取测试用户集合数据 List<User> userList = Tools.getUserList(count); long startTime = System.currentTimeMillis(); userService.saveBatch(userList, 5000);//每次往数据库提交5000条数据 long endTime = System.currentTimeMillis(); log.info("方式3耗时:{}", (endTime - startTime)); return JSONResult.success(); }
在浏览器中访问地址:http://localhost:8001/user/insertUser1/50000
这里的50000是表示插入5万条数据到t_user表
每次访问后,都记录一下耗时,然后清掉t_user表数据,再访问,反复测试5次
然后再访问http://localhost:8001/user/insertUser2/50000和http://localhost:8001/user/insertUser2/50000
步骤和上面一致,最终我统计出来的结果如下:
很明显,方式一效率最高
注意:以上结果仅是我本地测试情况,大家机器上可能会不同
经过今天的测试,我们知道批量插入数据最快的方式就是ExecutorType.BATCH批处理方式插入有些小的注意点记得在实际开发中谨慎处理,比如url参数配置,还有提交数量不要太大