验证mybatis批量插入能否一次能插入1万条数据
之前聊到自己做过的功能优化,就说了通讯录同步的优化,详细见通讯录同步效率优化,提到用Mybatis批量插入数据,把上限1万条数据一次性的插入到表中。面试官对一次性插入1万条数据有疑问,认为不可以插入这么多数据,但是我做这个功能的时候确实是成功的,那具体能一次插入数据的上限我也不确定,后面就找时间做了下面这个实验。
首先自己搭建了SpringBoot+Mybatis的项目测试的,搭建步骤如下
1. 搭建测试工程
idea构建SpringBoot+MyBatis项目
gitee上代码:
https://gitee.com/AJiSun/SpringBoot-MyBatis
File->New->Project
依赖:不选也行,后续在pom中添加,这里就选了一个mysql的依赖
添加需要的pom
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.2.0</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> 复制代码
然后就是新建文件夹,新建需要的文件,我的目录结构如下
application.yml中的配置
server: port: 7070 spring: application: name: ajisun-mybatis datasource: driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/ajisun_mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=false&useInformationSchema=true username: root password: root mybatis: mapperLocations: classpath:mapper/*.xml typeAliasesPackage: com.ajisun.coding.ajisunmybatis.entity #开启驼峰命名 configuration: map-underscore-to-camel-case: true 复制代码
启动类中加上注解@MapperScan(启动的时候能够扫描到mapper)
@SpringBootApplication @MapperScan("com.ajisun.coding.ajisunmybatis.mapper") public class AjisunMybatisApplication { public static void main(String[] args) { SpringApplication.run(AjisunMybatisApplication.class, args); } } 复制代码
实体类中的内容
public class SyncEmployee implements Serializable { private Long syncId; private Long syncCode; private String employeeNum; private String imageUrl; ....... // set/get省略 } 复制代码
mapper.java中内容
public interface SyncEmployeeMapper { /** * 查询列表 * @return */ List<SyncEmployee> selectList(); } 复制代码
mapper.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="com.ajisun.coding.ajisunmybatis.mapper.SyncEmployeeMapper"> <select id="selectList" resultType="com.ajisun.coding.ajisunmybatis.entity.SyncEmployee"> SELECT * FROM sync_employee; </select> </mapper> 复制代码
Interface service类 中的内容
public interface SyncEmployeeService { /** * 查询列表 * @return */ List<SyncEmployee> selectList(); } 复制代码
实现Interface service的类中的内容
加上@Service 注解,标明这个类是一个service,会被springboot扫描。
@Service public class SyncEmployeeServiceImpl implements SyncEmployeeService { @Autowired private SyncEmployeeMapper syncEmployeeMapper; /** * 查询列表 * @return */ @Override public List<SyncEmployee> selectList() { return syncEmployeeMapper.selectList(); } 复制代码
接口文件controller的内容
@RestController @RequestMapping("/user") public class SyncEmployeeController { @Autowired private SyncEmployeeService syncEmployeeService; @GetMapping("/list") public ResponseEntity<List<SyncEmployee>> list(){ return ResponseEntity.ok(syncEmployeeService.selectList()); } } 复制代码
至此就可以启动了,端口是yml中配置的7070,访问接口是【get】http://localhost:7070/user/list
2. 造数据,做测试
1. 表结构
CREATE TABLE `ajisun_mybatis`.`sync_employee` ( `sync_id` bigint(20) NOT NULL AUTO_INCREMENT, `sync_code` bigint(30) NOT NULL COMMENT '同步批次号', `employee_num` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '员工编码', `name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '员工姓名', `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户ID', `email` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '电子邮件', `mobile` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '移动电话', `inter_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '国际码', `code_mobile` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '国际码+手机号', `gender` tinyint(4) NULL DEFAULT NULL, `cid` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '身份编码', `status` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '员工状态', `enabled_flag` tinyint(1) NULL DEFAULT NULL COMMENT '启用状态', `entry_date` datetime(0) NULL DEFAULT NULL COMMENT '员工入职时间', `birthday` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '出生日期', `ldap_flag` tinyint(1) NOT NULL DEFAULT 0 COMMENT '否是ldap用户', `password` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '密码', `image_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '头像', `sync_status` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'sync状态', `sync_message` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'sync信息', PRIMARY KEY (`sync_id`) USING BTREE, INDEX `sync_emp_n1`(`sync_code`, `tenant_id`) USING BTREE, INDEX `sync_emp_n2`(`email`, `sync_code`, `tenant_id`) USING BTREE, INDEX `sync_emp_n3`(`mobile`, `sync_code`, `tenant_id`) USING BTREE, INDEX `sync_emp_n4`(`employee_num`, `tenant_id`, `sync_code`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '员工同步中间表' ROW_FORMAT = Dynamic; 复制代码
在SpringBoot-Mybatis 的项目中添加方法(具体参考代码,这里只说主要的地方)
2. mapper.xml中批量insert代码
<insert id="batchInsertData"> insert into sync_employee (sync_id,sync_code,employee_num,name,tenant_id, email,mobile,inter_code,code_mobile,gender,cid,status,enabled_flag, entry_date,birthday,ldap_flag,image_url,sync_status,sync_message) values <foreach collection="syncEmployeeList" item="sync" separator=","> ( #{sync.syncId}, #{sync.syncCode}, #{sync.employeeNum}, #{sync.name}, #{sync.tenantId}, #{sync.email}, #{sync.mobile}, #{sync.interCode}, #{sync.codeMobile}, #{sync.gender}, #{sync.cid}, #{sync.status}, #{sync.enabledFlag}, #{sync.entryDate}, #{sync.birthday}, #{sync.ldapFlag}, #{sync.imageUrl}, 0, #{sync.syncMessage} ) </foreach> </insert> 复制代码
3. service中的代码(循环造需要的数据量)
@Override public void batchInsertData(Long syncCode) { List<SyncEmployee> syncEmployeeList = new ArrayList<SyncEmployee>(); for (int i=10000;i<20000;i++){ SyncEmployee ee = new SyncEmployee(); ee.setSyncId(Long.valueOf(i)); ee.setSyncCode(syncCode); ee.setEmail(i+"@qq.com"); ee.setMobile("121000"+String.valueOf(+i)); ee.setCodeMobile(ee.getInterCode()+"-"+ee.getMobile()); ee.setEmployeeNum("ajisun"+i); ee.setEnabledFlag(1); ee.setGender(i%2); ee.setImageUrl("http://ajisun.com/头像.png"); ee.setName(i+""); ee.setLdapFlag(i%2); ee.setEntryDate(new Date()); ee.setStatus("ON"); ee.setTenantId(0L); ee.setBirthday("1900-01-01"); ee.setSyncMessage("new data "+i); syncEmployeeList.add(ee); } long start = System.currentTimeMillis(); syncEmployeeMapper.batchInsertData(syncEmployeeList); long end = System.currentTimeMillis(); System.out.println(end-start); } 复制代码
4. 准备完毕,开始执行
通过上述方法一次构造一万条数据,然后通过sql批量插入
com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (4,879,714 > 4,194,304). You can change this valueon the server by setting the 'max_allowed_packet' variable. 复制代码
报错了,根据错误信息知道 构造的一万条数据的大小超过了mysql限制的大小,但是我开发环境并没有问题啊(去开发环境看了下这个值确实比较大)
查看当前环境max_allowed_packet的大小,如下 这个版本默认是4M的大小
mysql> show global variables like 'max_allowed_packet'; +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 4194304 | +--------------------+---------+ 1 row in set (0.01 sec) mysql> select 4194304/1024/1024; +-------------------+ | 4194304/1024/1024 | +-------------------+ | 4.00000000 | +-------------------+ 1 row in set (0.00 sec) 复制代码
把这个参数改大点试试(设置10M)
mysql> set global max_allowed_packet = 1024*1024*10; Query OK, 0 rows affected (0.01 sec) 复制代码
命令set采用的临时修改方式,需要打开新的会话才能生效,重启SpringBoot服务然后在调用接口。(还可以通过修改mysql配置文件使其永久生效 max_allowed_packet = 1024* 1024*10)。
还可以通过修改mysql配置文件使其永久生效 max_allowed_packet = 1024* 1024*10
再次调用接口就成功了
继续加大操作数据量,修改循环条件(3万数据量),数据量的总大小在14M左右
com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (14,637,233 > 10,485,760). You can change this value on the server by setting the 'max_allowed_packet' variable. 复制代码
如上还是出现了相同的max_allowed_packet 错误,packet的大小超过了max_allowed_packet的值。
所以经过测试可以确定Mybatis究竟能插入多少数据取决于mysql的max_allowed_packet大小限制,而不是其自身的限制。
3. 总结
伪原创工具 SEO网站优化 https://www.237it.com/
本文着重在于测试mybatis对批量插入的数据量是否有影响,经过测试发现并没有影响,主要是mysql自身对接收数据量的大小限制,通过参数max_allowed_packet控制。
但是使用mybatis大数据量批量插入要注意效率问题,这里只是测试,并不推荐这种方式。
我是纪先生,用输出倒逼输入而持续学习,持续分享技术系列文章,以及全网值得收藏好文,欢迎关注或者关注公众号,做一个持续成长的技术人。
作者:纪先生
链接:https://juejin.cn/post/7035523265049296927