前言
写在前面
各位小伙伴好久不见,时光荏苒,不知不觉已经来到了寒风刺骨的冬天,今天出门差点把自己冻废在路上。在这寒冷的冬天,我带着我对程序探究的热情,来温暖这个寒冷的冬天。虽然好久不见,迫于生计,还是要一有时间就要总结的。
开始今天的分享,初级的小伙伴在面试的过程中,肯定会问到目前主流的持久层框架使用的相关技术问题,当然作为“IT小白”的我,在面试求职者的时候同样也会问关于Mybtatis使用、二级缓存等等相关的问题。比较主流的一个问题就是:如果在数据量大的情况下,你如何进行数据的批量插入,回答我问题的答案,一般就是两个。
那我们今天就亲身尝试下这两种方式所耗时间的区别。
程序验证
程序批量插入
搭建SpringBoot+Mybatis基础的整合框架,创建表
CREATE TABLE t_user(
user_id BIGINT PRIMARY KEY COMMENT '用户编号',
user_name VARCHAR(20) COMMENT '租户名称',
user_pass VARCHAR(50) COMMENT '用户密码'
)COMMENT '用户信息表';
编写实体类
package com.yang.bean;
/**
* 用户实体类
* @author Yang
*
*/
public class User {
/*用户名称*/
private int userId;
/*用户登录名称*/
private String userName;
/* 用户登录密码 */
private String userPass;
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserPass() {
return userPass;
}
public void setUserPass(String userPass) {
this.userPass = userPass;
}
}
编写插入与查询的UserMapper文件
package com.yang.dao;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import com.yang.bean.User;
/**
* 用户登录数据层
* @author Yang
*
*/
@Mapper //标识为mybatis数据层接口
public interface UserDao {
/**
*模糊查询用户
* @return
*/
List<User> getUser();
/**
* 添加用户信息
* @param user
* @return
*/
int addUser(User user);
}
<?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="com.yang.dao.UserDao">
<!-- 获得用户列表 -->
<select id="getUser" resultType="com.yang.bean.User">
select user_id userId,user_name userName,user_pass userPass from t_user
</select>
<insert id="addUser" parameterType="com.yang.bean.User">
INSERT INTO t_user VALUES (#{userId},#{userName},#{userPass})
</insert>
</mapper>
开始验证,编写单个插入方法
/**
* 添加用户信息
* @return
*/
public Long addUser(){
log.info("开始插入数据");
//记录影响的行数
int i = 0;
//模拟生成数组
List<User> userList = new ArrayList<User>();
//生成数据
for (int j=0;j<5000;j++){
User users = new User();
users.setUserId(j);
users.setUserName("test" + j);
users.setUserPass("abcdef" + j);
userList.add(users);
}
long startNow=System.currentTimeMillis();
//去单个插入数据
for (User temp : userList){
i += userDao.addUser(temp);
}
log.info("影响行数"+i);
long endTime=System.currentTimeMillis();
long time = endTime - startNow;
log.info("所耗费时间"+time);
return time;
}
插入五万行数据,结果如下,共费时长12263ms
2020-11-20 15:44:25.340 INFO 3182 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl : 开始插入数据
2020-11-20 15:44:25.390 INFO 3182 --- [nio-8088-exec-1] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
2020-11-20 15:44:37.622 INFO 3182 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl : 影响行数50000
2020-11-20 15:44:37.622 INFO 3182 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl : 所耗费时间12263
程序验证
Mybatis批量插入
编写批量插入SQL
<!-- 批量插入 -->
<insert id="bathAddUser" parameterType="java.util.List">
INSERT INTO t_user VALUES
<foreach collection="list" separator="," item="item">
(#{item.userId},#{item.userName},#{item.userPass})
</foreach>
</insert>
批量插入的业务代码
/**
* 批量添加用户信息
* @return
*/
public Long bathAddUser(){
log.info("开始批量插入数据");
//记录影响的行数
int i = 0;
//模拟生成数组
List<User> userList = new ArrayList<User>();
//生成数据
for (int j=0;j<50000;j++){
User users = new User();
int index = 50000 +j;
users.setUserId(index);
users.setUserName("testBath" + index);
users.setUserPass("testBath" + index);
userList.add(users);
}
long startNow=System.currentTimeMillis();
//去批量插入数据
i += userDao.bathAddUser(userList);
log.info("影响行数"+i);
long endTime=System.currentTimeMillis();
long time = endTime - startNow;
log.info("所耗费时间"+time);
return time;
}
结果验证如下,批量插入五万行数据,结果如下,共费时长1165ms
2020-11-20 15:56:50.972 INFO 3404 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl : 开始批量插入数据
2020-11-20 15:56:51.598 INFO 3404 --- [nio-8088-exec-1] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
2020-11-20 15:56:52.156 INFO 3404 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl : 影响行数50000
2020-11-20 15:56:52.156 INFO 3404 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl : 所耗费时间1165
结论
结果对比
单个插入 | 批量插入 |
---|---|
耗时12263ms | 耗时1165ms |
经过程序的验证,五万条数据使用程序一个个插入,和使用Mybatis将SQL进行拼接,使用批量插入SQL,只有三个字段的实体,在耗时层面效率差距≈10.5倍,如果当实体类数据较为复杂,数据量更大的情况下,这个差距会拉取的更大,单个插入,每次插入需要程序将SQL给到MySQL执行,期间交互5万次,而批量插入只需要交互一次,且使用程序循环的过程中也会造成对内存的浪费,所以当面试官再问到此次问题的时候,请毫不犹豫的选择使用Mybatis的foreach标签进行批量插入。
反问
插入是否有限制
反问:Mybatis的批量插入有限制吗?可以随便插入任意条数据吗?来验证一下当Mybatis什么时候会承受不住插入的数据量,会报什么异常?我来用20万条数据做个试验,程序抛出了异常。
### Cause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (10400030 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.
; SQL []; Packet for query is too large (10400030 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.; nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (10400030 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.] with root cause
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (10400030 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3540) ~[mysql-connector-java-5.1.35.jar:5.1.35]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2417) ~[mysql-connector-java-5.1.35.jar:5.1.35]
此时程序抛出异常:com.mysql.jdbc.PacketTooBigException,查看资料后发现,MYSQL会根据配置文件会限制server接受的数据包大小,有时候在大的插入和更新会被max_allowed_packet 参数限制掉,导致失败。我们查看MySQL本机的配置为多少。
yangzhenxin@MacBook-Pro bin % ./mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1106
Server version: 5.7.30 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show VARIABLES like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| max_allowed_packet | 4194304 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.01 sec)
mysql>
经过查询,本机Mysql的插入的包的最大限制为4194304(B),错误提示中的“10400030 > 4194304”,正好对应了本机的MySQL服务的限制,插入的SQL达到了10400030B,故程序超出错误,MySQL对插入的数量并未进行限制,只是限制了包的大小,所以在项目中遇到批量迁移数据的时候,也并不是一股脑的去把所有的程序一次插入,可采用分批+批量插入的方式完成。可参考如下代码:
/**
* 批量添加用户信息
* @return
*/
public Long bathAddUser(){
log.info("开始批量插入数据");
//记录影响的行数
int i = 0;
//模拟生成数组
List<User> userList = new ArrayList<User>();
//生成数据
for (int j=0;j<200000;j++){
User users = new User();
int index = 200000 +j;
users.setUserId(index);
users.setUserName("testBath" + index);
users.setUserPass("testBath" + index);
userList.add(users);
}
long startNow=System.currentTimeMillis();
//去批量插入数据
List userListTemp = new ArrayList();
for(int j=0;j<200000;j++){
int index = j+1 ;
//分页插入 一次1000条
if(index%1000==0){
userListTemp.add(userList.get(j));
i += userDao.bathAddUser(userListTemp);
userListTemp.clear();
}else{
userListTemp.add(userList.get(j));
}
}
log.info("影响行数"+i);
long endTime=System.currentTimeMillis();
long time = endTime - startNow;
log.info("所耗费时间"+time);
return time;
}
2020-11-20 16:31:02.365 INFO 4101 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl : 开始批量插入数据
2020-11-20 16:31:02.496 INFO 4101 --- [nio-8088-exec-1] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
2020-11-20 16:31:06.122 INFO 4101 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl : 影响行数200000
2020-11-20 16:31:06.122 INFO 4101 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl : 所耗费时间3706
最终插入20万条数据使用了3706ms,比之前单个插入的5万条数据还要快。
实践可以告诉我们真相,也能验证认知中的场景,去深刻的记得自己理论在实践中的具体效果。