👇👇关注后回复 “进群” ,拉你进程序员交流群👇👇
本文记录个人使用MySQL插入大数据总结较实用的方案,通过对常用插入大数据的4种方式进行测试,即for循环单条、拼接SQL、批量插入saveBatch()
、循环 + 开启批处理模式,得出比较实用的方案心得。
一、前言
最近趁空闲之余,在对MySQL数据库进行插入数据测试,对于如何快速插入数据的操作无从下手,在仅1W数据量的情况下,竟花费接近47s,实在不忍直视!在不断摸索之后,整理出一些较实用的方案。
二、准备工作
测试环境:SpringBoot项目、MyBatis-Plus框架、MySQL8.0.24、JDK13
前提:SpringBoot项目集成MyBatis-Plus上述文章有配置过程,同时实现IService接口用于进行批量插入数据操作saveBatch()
方法
1、Maven项目中pom.xml文件引入的相关依赖如下
<dependencies>
<!-- SpringBoot Web模块依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- MyBatis-Plus 依赖 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.1</version>
</dependency>
<!-- 数据库连接驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- 使用注解,简化代码-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
2、application.yml配置属性文件内容(重点:开启批处理模式)
server:
# 端口号
port: 8080
# MySQL连接配置信息(以下仅简单配置,更多设置可自行查看)
spring:
datasource:
# 连接地址(解决UTF-8中文乱码问题 + 时区校正)
# (rewriteBatchedStatements=true 开启批处理模式)
url: jdbc:mysql://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表结构(注意:无索引)
![d0c250b7396dedc5cc4ee96519b23d23.png](https://i-blog.csdnimg.cn/blog_migrate/d62c3ff8d1a0deee76d8b314ca66d48e.png)
三、测试工作
简明:完成准备工作后,即对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秒
![e4b084b9e037cad18bcc704dcee374b1.png](https://i-blog.csdnimg.cn/blog_migrate/2c5a4f13e4828627618dbe3747f13c64.png)
(2)第二次测试结果:175926 约等于 176秒(服务未重启)
![40d72786525e05861995c7ff4f58460c.png](https://i-blog.csdnimg.cn/blog_migrate/31044ca35ecdf75fac65e0d832846253.png)
(3)第三次测试结果:174726 约等于 174秒(服务重启)
![817e16756f4ddd87769f9a251d8f39da.png](https://i-blog.csdnimg.cn/blog_migrate/b3584bbc8cc70585ac05390ed93048ec.png)
2、拼接SQL语句(总耗时:2.9秒)
简明:拼接格式:insert into student(xxxx) value(xxxx),(xxxx),(xxxxx).......
总结:拼接结果就是将所有的数据集成在一条SQL语句的value值上,其由于提交到服务器上的insert语句少了,网络负载少了,性能也就提上去。
但是当数据量上去后,可能会出现内存溢出、解析SQL语句耗时等情况,但与第一点相比,提高了极大的性能。
@GetMapping("/sql")
public void sql(){
ArrayList<Student> 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("<script>" +
"insert into student (name,age,addr,addr_num) values " +
"<foreach collection='studentList' item='item' separator=','> " +
"(#{item.name}, #{item.age}, #{item.addr}, #{item.addrNum}) " +
"</foreach> " +
"</script>")
int insertSplice(@Param("studentList") List<Student> studentList);
(1)第一次测试结果:3218 约等于 3.2秒
![52e670b1fb80437c4ba27d976d4c2aac.png](https://i-blog.csdnimg.cn/blog_migrate/5cddc54d8bb7b492d0be62b04bec1792.png)
(2)第二次测试结果:2592 约等于 2.6秒(服务未重启)
![d7fd0510618fb29ada0f10040f8fdac9.png](https://i-blog.csdnimg.cn/blog_migrate/8c61ca45d60b9a26440584e3376f3314.png)
(3)第三次测试结果:3082 约等于 3.1秒(服务重启)
![fbf2c08ab77d25a4fde1e39a42bb230c.png](https://i-blog.csdnimg.cn/blog_migrate/cf2ef59b199eddb6f8be1db8985f282c.png)
3、批量插入saveBatch(总耗时:2.7秒)
简明:使用MyBatis-Plus实现IService接口中批处理saveBatch()
方法,对底层源码进行查看时,可发现其实是for循环插入,但是与第一点相比,为什么性能上提高了呢?因为利用分片处理(batchSize = 1000
) + 分批提交事务的操作,从而提高性能,并非在Connection上消耗性能。
@GetMapping("/saveBatch1")
public void saveBatch1(){
ArrayList<Student> 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秒
![1c03d5d6bb1efa7e54cbcbfcc9b00ce9.png](https://i-blog.csdnimg.cn/blog_migrate/74718262052805863e9454f49c040d2a.png)
(2)第二次测试结果:2302 约等于 2.3秒(服务未重启)
![abc383600da4df800f9687ba60d29a26.png](https://i-blog.csdnimg.cn/blog_migrate/13d9452728a699b73ad6f0e4b000b0ff.png)
(3)第三次测试结果:2893 约等于 2.9秒(服务重启)
![d71808a71dff31e0c5df11db78f87f43.png](https://i-blog.csdnimg.cn/blog_migrate/c66f700f570179fa2035699e727ef449.png)
重点注意:MySQL JDBC驱动默认情况下忽略saveBatch()
方法中的executeBatch()
语句,将需要批量处理的一组SQL语句进行拆散,执行时一条一条给MySQL数据库,造成实际上是分片插入,即与单条插入方式相比,有提高,但是性能未能得到实质性的提高。
测试:数据库连接URL地址缺少 rewriteBatchedStatements = true
参数情况
# MySQL连接配置信息
spring:
datasource:
# 连接地址(未开启批处理模式)
url: jdbc:mysql://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秒(未开启批处理模式)
![51a26cba50bdeb8bacd4598bc1d5a3c6.png](https://i-blog.csdnimg.cn/blog_migrate/602eba15461b2a1142c1795042850f36.png)
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秒
![1b5bbf173d22de68b2689fe9d7ae679e.png](https://i-blog.csdnimg.cn/blog_migrate/6f03442ea10561205dfc3ae51c4e5d99.png)
(2)第二次测试结果:1382 约等于 1.4秒(服务未重启)
![ae03c3756e2d94606feccb4cfc918a96.png](https://i-blog.csdnimg.cn/blog_migrate/16409847db1b4c018dc4126b126907db.png)
(3)第三次测试结果:1883 约等于 1.9秒(服务重启)
![1b94f7845c953eb4662d59748df6f551.png](https://i-blog.csdnimg.cn/blog_migrate/c8fac4c3bc1fbf04e086a33704a3a6cf.png)
四、总结
本文记录个人学习MySQL插入大数据一些方案心得,可得知主要是在获取连接、关闭连接、释放资源和提交事务等方面较耗能,其中最需要注意是开启批处理模式,即URL地址的参数:rewriteBatchedStatements = true
,否则也无法发挥作用。
对于测试方案的设定、对考虑不周、理解和编写错误的地方等情况,请多指出,共同学习!
来源:blog.csdn.net/a18505947362/article/
details/123667215
-End-
最近有一些小伙伴,让我帮忙找一些 面试题 资料,于是我翻遍了收藏的 5T 资料后,汇总整理出来,可以说是程序员面试必备!所有资料都整理到网盘了,欢迎下载!
点击👆卡片,关注后回复【面试题
】即可获取
在看点这里好文分享给更多人↓↓