MySQL批量插入数据的四种方案(性能测试对比)
本文记录个人使用mysql插入大数据总结较实用的方案，通过对常用插入大数据的4种方式进行测试，即for循环单条、拼接sql、批量插入savebatch()、循环 + 开启批处理模式，得出比较实用的方案心得。
一、前言
最近趁空闲之余，在对mysql数据库进行插入数据测试，对于如何快速插入数据的操作无从下手，在仅1w数据量的情况下，竟花费接近47s，实在不忍直视！在不断摸索之后，整理出一些较实用的方案。
二、准备工作
测试环境：springboot项目、mybatis-plus框架、mysql8.0.24、jdk13
前提：springboot项目集成mybatis-plus上述文章有配置过程，同时实现iservice接口用于进行批量插入数据操作savebatch()方法
1、maven项目中pom.xml文件引入的相关依赖如下
org.springframework.boot spring-boot-starter-web com.baomidou mybatis-plus-boot-starter 3.3.1 mysql mysql-connector-java org.projectlombok lombok
2、application.yml配置属性文件内容（重点：开启批处理模式）
server: # 端口号 port: 8080 # mysql连接配置信息（以下仅简单配置，更多设置可自行查看）spring: datasource: # 连接地址（解决utf-8中文乱码问题 + 时区校正） # （rewritebatchedstatements=true 开启批处理模式） url: jdbc//127.0.0.1:3306/bjpowernode?useunicode=true&characterencoding=utf-8&servertimezone=asia/shanghai&rewritebatchedstatements=true # 用户名 username: root # 密码 password: xxx # 连接驱动名称 driver-class-name: com.mysql.cj.jdbc.driver
3、entity实体类（测试）
/** * student 测试实体类 * * @data注解：引入lombok依赖，可省略setter、getter方法 * @author lbf * @date 2022/3/18 16:06 */@data@tablename(value = student)public class student { /** 主键 type:自增 */ @tableid(type = idtype.auto) private int id; /** 名字 */ private string name; /** 年龄 */ private int age; /** 地址 */ private string addr; /** 地址号 @tablefield：与表字段映射 */ @tablefield(value = addr_num) private string addrnum; public student(string name, int age, string addr, string addrnum) { this.name = name; this.age = age; this.addr = addr; this.addrnum = addrnum; }}
4、数据库student表结构（注意：无索引）
三、测试工作
简明：完成准备工作后，即对for循环、拼接sql语句、批量插入savebatch()、循环插入+开启批处理模式，该4种插入数据的方式进行测试性能。
注意：测试数据量为5w、单次测试完清空数据表（确保不受旧数据影响）
以下测试内容可能受测试配置环境、测试规范和数据量等诸多因素影响，读者可自行结合参考进行测试
1、for循环插入（单条）（总耗时：177秒）
总结：测试平均时间约是177秒，实在是不忍直视（捂脸），因为利用for循环进行单条插入时，每次都是在获取连接(connection)、释放连接和资源关闭等操作上，（如果数据量大的情况下）极其消耗资源，导致时间长。
@getmapping(/for)public void forsingle(){ // 开始时间 long starttime = system.currenttimemillis(); for (int i = 0; i < 50000; i++){ student student = new student(李毅 + i,24,张家界市 + i,i + 号); studentmapper.insert(student); } // 结束时间 long endtime = system.currenttimemillis(); system.out.println(插入数据消耗时间： + (endtime - starttime));}
（1）第一次测试结果：190155 约等于 190秒
（2）第二次测试结果：175926 约等于 176秒（服务未重启）
（3）第三次测试结果：174726 约等于 174秒（服务重启）
2、拼接sql语句（总耗时：2.9秒）
简明：拼接格式：insert into student(xxxx) value(xxxx),(xxxx),(xxxxx).......
总结：拼接结果就是将所有的数据集成在一条sql语句的value值上，其由于提交到服务器上的insert语句少了，网络负载少了，性能也就提上去。
但是当数据量上去后，可能会出现内存溢出、解析sql语句耗时等情况，但与第一点相比，提高了极大的性能。
@getmapping(/sql)public void sql(){ arraylist arraylist = new arraylist(); long starttime = system.currenttimemillis(); for (int i = 0; i < 50000; i++){ student student = new student(李毅 + i,24,张家界市 + i,i + 号); arraylist.add(student); } studentmapper.insertsplice(arraylist); long endtime = system.currenttimemillis(); system.out.println(插入数据消耗时间： + (endtime - starttime));}// 使用@insert注解插入：此处为简便，不写mapper.xml文件@insert()int insertsplice(@param(studentlist) list studentlist);
（1）第一次测试结果：3218 约等于 3.2秒
（2）第二次测试结果：2592 约等于 2.6秒（服务未重启）
（3）第三次测试结果：3082 约等于 3.1秒（服务重启）
3、批量插入savebatch（总耗时：2.7秒）
简明：使用mybatis-plus实现iservice接口中批处理savebatch()方法，对底层源码进行查看时，可发现其实是for循环插入，但是与第一点相比，为什么性能上提高了呢？因为利用分片处理（batchsize = 1000） + 分批提交事务的操作，从而提高性能，并非在connection上消耗性能。
@getmapping(/savebatch1)public void savebatch1(){ arraylist arraylist = new arraylist(); long starttime = system.currenttimemillis(); // 模拟数据 for (int i = 0; i < 50000; i++){ student student = new student(李毅 + i,24,张家界市 + i,i + 号); arraylist.add(student); } // 批量插入 studentservice.savebatch(arraylist); long endtime = system.currenttimemillis(); system.out.println(插入数据消耗时间： + (endtime - starttime));}
（1）第一次测试结果：2864 约等于 2.9秒
（2）第二次测试结果：2302 约等于 2.3秒（服务未重启）
（3）第三次测试结果：2893 约等于 2.9秒（服务重启）
重点注意：mysql jdbc驱动默认情况下忽略savebatch()方法中的executebatch()语句，将需要批量处理的一组sql语句进行拆散，执行时一条一条给mysql数据库，造成实际上是分片插入，即与单条插入方式相比，有提高，但是性能未能得到实质性的提高。
测试：数据库连接url地址缺少 rewritebatchedstatements = true 参数情况
# mysql连接配置信息spring: datasource: # 连接地址（未开启批处理模式） url: jdbc//127.0.0.1:3306/bjpowernode?useunicode=true&characterencoding=utf-8&servertimezone=asia/shanghai # 用户名 username: root # 密码 password: xxx # 连接驱动名称 driver-class-name: com.mysql.cj.jdbc.driver
测试结果：10541 约等于 10.5秒（未开启批处理模式）
4、循环插入 + 开启批处理模式（总耗时：1.7秒）（重点：一次性提交）
简明：开启批处理，关闭自动提交事务，共用同一个sqlsession之后，for循环单条插入的性能得到实质性的提高；由于同一个sqlsession省去对资源相关操作的耗能、减少对事务处理的时间等，从而极大程度上提高执行效率。（目前个人觉得最优方案）
@getmapping(/forsavebatch)public void forsavebatch(){ // 开启批量处理模式 batch 、关闭自动提交事务 false sqlsession sqlsession = sqlsessionfactory.opensession(executortype.batch,false); // 反射获取，获取mapper studentmapper studentmapper = sqlsession.getmapper(studentmapper.class); long starttime = system.currenttimemillis(); for (int i = 0 ; i < 50000 ; i++){ student student = new student(李毅 + i,24,张家界市 + i,i + 号); studentmapper.insertstudent(student); } // 一次性提交事务 sqlsession.commit(); // 关闭资源 sqlsession.close(); long endtime = system.currenttimemillis(); system.out.println(总耗时： + (endtime - starttime));}
（1）第一次测试结果：1831 约等于 1.8秒
（2）第二次测试结果：1382 约等于 1.4秒（服务未重启）
（3）第三次测试结果：1883 约等于 1.9秒（服务重启）
四、总结
本文记录个人学习mysql插入大数据一些方案心得，可得知主要是在获取连接、关闭连接、释放资源和提交事务等方面较耗能，其中最需要注意是开启批处理模式，即url地址的参数：rewritebatchedstatements = true，否则也无法发挥作用。
对于测试方案的设定、对考虑不周、理解和编写错误的地方等情况，请多指出，共同学习！
微软或将简化Windows 10更新过程
长鑫存储取得时钟信号生成技术专利
2011年硅磁传感器销售额预计增长24%
PLC的机械压力机控制系统设计
OSCAR第二次成果发布会在京召开 首个容器产业白皮书及四大开源标准齐发布
MySQL批量插入数据的四种方案(性能测试对比)
MAX14525 具有4个使能输入的电池开关，用于在便携式设
LoRa和NB－IoT能否弥补各自的缺点
现代舰船所用的核动力是数十年来反应堆小型化的成果
2020年～2025年激光显示技术成主流，加速实现我国激光电视产业化
研华基于AMD CPU平台的嵌入式产品，广泛应用于各个行业
重庆市副市长江敦涛一行莅临重庆芯讯通调研
米兔儿童学习手表4Pro发布 售价1299元
LT8613 5V 降压型转换器具 6A 输出电流限制
健身房中智能镜面显示屏的应用，将引领智能健身房新潮流
宁德时代与长安汽车在宁德签订战略合作协议
河套IT WALK(总第69期)：苹果收购任天堂合作伙伴，掀AR领域新浪潮
浅谈直流高压微安表在直流高压发生器中的使用方法
美开发出新型自愈材料 未来可用于医疗
温州数智芯科技公司通过云计算等技术研发数智芯鞋业数字化智造平台
一、前言
最近趁空闲之余，在对mysql数据库进行插入数据测试，对于如何快速插入数据的操作无从下手，在仅1w数据量的情况下，竟花费接近47s，实在不忍直视！在不断摸索之后，整理出一些较实用的方案。
二、准备工作
测试环境：springboot项目、mybatis-plus框架、mysql8.0.24、jdk13
前提：springboot项目集成mybatis-plus上述文章有配置过程，同时实现iservice接口用于进行批量插入数据操作savebatch()方法
1、maven项目中pom.xml文件引入的相关依赖如下
org.springframework.boot spring-boot-starter-web com.baomidou mybatis-plus-boot-starter 3.3.1 mysql mysql-connector-java org.projectlombok lombok
2、application.yml配置属性文件内容（重点：开启批处理模式）
server: # 端口号 port: 8080 # mysql连接配置信息（以下仅简单配置，更多设置可自行查看）spring: datasource: # 连接地址（解决utf-8中文乱码问题 + 时区校正） # （rewritebatchedstatements=true 开启批处理模式） url: jdbc//127.0.0.1:3306/bjpowernode?useunicode=true&characterencoding=utf-8&servertimezone=asia/shanghai&rewritebatchedstatements=true # 用户名 username: root # 密码 password: xxx # 连接驱动名称 driver-class-name: com.mysql.cj.jdbc.driver
3、entity实体类（测试）
/** * student 测试实体类 * * @data注解：引入lombok依赖，可省略setter、getter方法 * @author lbf * @date 2022/3/18 16:06 */@data@tablename(value = student)public class student { /** 主键 type:自增 */ @tableid(type = idtype.auto) private int id; /** 名字 */ private string name; /** 年龄 */ private int age; /** 地址 */ private string addr; /** 地址号 @tablefield：与表字段映射 */ @tablefield(value = addr_num) private string addrnum; public student(string name, int age, string addr, string addrnum) { this.name = name; this.age = age; this.addr = addr; this.addrnum = addrnum; }}
4、数据库student表结构（注意：无索引）
三、测试工作
简明：完成准备工作后，即对for循环、拼接sql语句、批量插入savebatch()、循环插入+开启批处理模式，该4种插入数据的方式进行测试性能。
注意：测试数据量为5w、单次测试完清空数据表（确保不受旧数据影响）
以下测试内容可能受测试配置环境、测试规范和数据量等诸多因素影响，读者可自行结合参考进行测试
1、for循环插入（单条）（总耗时：177秒）
总结：测试平均时间约是177秒，实在是不忍直视（捂脸），因为利用for循环进行单条插入时，每次都是在获取连接(connection)、释放连接和资源关闭等操作上，（如果数据量大的情况下）极其消耗资源，导致时间长。
@getmapping(/for)public void forsingle(){ // 开始时间 long starttime = system.currenttimemillis(); for (int i = 0; i < 50000; i++){ student student = new student(李毅 + i,24,张家界市 + i,i + 号); studentmapper.insert(student); } // 结束时间 long endtime = system.currenttimemillis(); system.out.println(插入数据消耗时间： + (endtime - starttime));}
（1）第一次测试结果：190155 约等于 190秒
（2）第二次测试结果：175926 约等于 176秒（服务未重启）
（3）第三次测试结果：174726 约等于 174秒（服务重启）
2、拼接sql语句（总耗时：2.9秒）
简明：拼接格式：insert into student(xxxx) value(xxxx),(xxxx),(xxxxx).......
总结：拼接结果就是将所有的数据集成在一条sql语句的value值上，其由于提交到服务器上的insert语句少了，网络负载少了，性能也就提上去。
但是当数据量上去后，可能会出现内存溢出、解析sql语句耗时等情况，但与第一点相比，提高了极大的性能。
@getmapping(/sql)public void sql(){ arraylist arraylist = new arraylist(); long starttime = system.currenttimemillis(); for (int i = 0; i < 50000; i++){ student student = new student(李毅 + i,24,张家界市 + i,i + 号); arraylist.add(student); } studentmapper.insertsplice(arraylist); long endtime = system.currenttimemillis(); system.out.println(插入数据消耗时间： + (endtime - starttime));}// 使用@insert注解插入：此处为简便，不写mapper.xml文件@insert()int insertsplice(@param(studentlist) list studentlist);
（1）第一次测试结果：3218 约等于 3.2秒
（2）第二次测试结果：2592 约等于 2.6秒（服务未重启）
（3）第三次测试结果：3082 约等于 3.1秒（服务重启）
3、批量插入savebatch（总耗时：2.7秒）
简明：使用mybatis-plus实现iservice接口中批处理savebatch()方法，对底层源码进行查看时，可发现其实是for循环插入，但是与第一点相比，为什么性能上提高了呢？因为利用分片处理（batchsize = 1000） + 分批提交事务的操作，从而提高性能，并非在connection上消耗性能。
@getmapping(/savebatch1)public void savebatch1(){ arraylist arraylist = new arraylist(); long starttime = system.currenttimemillis(); // 模拟数据 for (int i = 0; i < 50000; i++){ student student = new student(李毅 + i,24,张家界市 + i,i + 号); arraylist.add(student); } // 批量插入 studentservice.savebatch(arraylist); long endtime = system.currenttimemillis(); system.out.println(插入数据消耗时间： + (endtime - starttime));}
（1）第一次测试结果：2864 约等于 2.9秒
（2）第二次测试结果：2302 约等于 2.3秒（服务未重启）
（3）第三次测试结果：2893 约等于 2.9秒（服务重启）
重点注意：mysql jdbc驱动默认情况下忽略savebatch()方法中的executebatch()语句，将需要批量处理的一组sql语句进行拆散，执行时一条一条给mysql数据库，造成实际上是分片插入，即与单条插入方式相比，有提高，但是性能未能得到实质性的提高。
测试：数据库连接url地址缺少 rewritebatchedstatements = true 参数情况
# mysql连接配置信息spring: datasource: # 连接地址（未开启批处理模式） url: jdbc//127.0.0.1:3306/bjpowernode?useunicode=true&characterencoding=utf-8&servertimezone=asia/shanghai # 用户名 username: root # 密码 password: xxx # 连接驱动名称 driver-class-name: com.mysql.cj.jdbc.driver
测试结果：10541 约等于 10.5秒（未开启批处理模式）
4、循环插入 + 开启批处理模式（总耗时：1.7秒）（重点：一次性提交）
简明：开启批处理，关闭自动提交事务，共用同一个sqlsession之后，for循环单条插入的性能得到实质性的提高；由于同一个sqlsession省去对资源相关操作的耗能、减少对事务处理的时间等，从而极大程度上提高执行效率。（目前个人觉得最优方案）
@getmapping(/forsavebatch)public void forsavebatch(){ // 开启批量处理模式 batch 、关闭自动提交事务 false sqlsession sqlsession = sqlsessionfactory.opensession(executortype.batch,false); // 反射获取，获取mapper studentmapper studentmapper = sqlsession.getmapper(studentmapper.class); long starttime = system.currenttimemillis(); for (int i = 0 ; i < 50000 ; i++){ student student = new student(李毅 + i,24,张家界市 + i,i + 号); studentmapper.insertstudent(student); } // 一次性提交事务 sqlsession.commit(); // 关闭资源 sqlsession.close(); long endtime = system.currenttimemillis(); system.out.println(总耗时： + (endtime - starttime));}
（1）第一次测试结果：1831 约等于 1.8秒
（2）第二次测试结果：1382 约等于 1.4秒（服务未重启）
（3）第三次测试结果：1883 约等于 1.9秒（服务重启）
四、总结
本文记录个人学习mysql插入大数据一些方案心得，可得知主要是在获取连接、关闭连接、释放资源和提交事务等方面较耗能，其中最需要注意是开启批处理模式，即url地址的参数：rewritebatchedstatements = true，否则也无法发挥作用。
对于测试方案的设定、对考虑不周、理解和编写错误的地方等情况，请多指出，共同学习！
微软或将简化Windows 10更新过程
长鑫存储取得时钟信号生成技术专利
2011年硅磁传感器销售额预计增长24%
PLC的机械压力机控制系统设计
OSCAR第二次成果发布会在京召开 首个容器产业白皮书及四大开源标准齐发布
MySQL批量插入数据的四种方案(性能测试对比)
MAX14525 具有4个使能输入的电池开关，用于在便携式设
LoRa和NB－IoT能否弥补各自的缺点
现代舰船所用的核动力是数十年来反应堆小型化的成果
2020年～2025年激光显示技术成主流，加速实现我国激光电视产业化
研华基于AMD CPU平台的嵌入式产品，广泛应用于各个行业
重庆市副市长江敦涛一行莅临重庆芯讯通调研
米兔儿童学习手表4Pro发布 售价1299元
LT8613 5V 降压型转换器具 6A 输出电流限制
健身房中智能镜面显示屏的应用，将引领智能健身房新潮流
宁德时代与长安汽车在宁德签订战略合作协议
河套IT WALK(总第69期)：苹果收购任天堂合作伙伴，掀AR领域新浪潮
浅谈直流高压微安表在直流高压发生器中的使用方法
美开发出新型自愈材料 未来可用于医疗
温州数智芯科技公司通过云计算等技术研发数智芯鞋业数字化智造平台