前言
最近在项目上遇到了批量刺进的场景问题,因为每次需求刺进超越 10w+ 的数据量而且字段也蛮多的导致假如运用循环单次刺进的办法刺进数据刺进的效率不高。信任读者们在实践开发中也遇到过这样相似的场景,那么批量刺进怎么完结呢?
其实我也是一知半解,之前只见过他人博客上的批量刺进完结,关于实践优化上的细节以及优化的程度并不了解。所以正好借此机会,在这儿认真地把批量刺进的完结及优化进程实操一遍并记载下来,有兴趣的读者们能够接着往下观看,有不对的地方还希望能在评论里指出来。
已然触及到了数据库层面的操作,我想从 JDBC 和 MyBatis / MyBatis Plus 两个层面分别完结一下批量刺进,下面将依次解说完结及优化进程。
JDBC 完结批量刺进
在编写代码前,先准备一下 JDBC 批量刺进需求的测验环境。
JDBC 测验环境
建表句子(运用数据库版别 mysql 5.7.19)
DROP TABLE IF EXISTS `fee`;
CREATE TABLE `fee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
`owner` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '归属人',
`fee1` decimal(30, 5) NULL DEFAULT NULL COMMENT '费用1',
`fee2` decimal(30, 5) NULL DEFAULT NULL COMMENT '费用2',
`fee3` decimal(30, 5) NULL DEFAULT NULL COMMENT '费用3',
`fee4` decimal(30, 5) NULL DEFAULT NULL COMMENT '费用4',
`fee5` decimal(30, 5) NULL DEFAULT NULL COMMENT '费用5',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci COMMENT = '费用表' ROW_FORMAT = Dynamic;
maven 坐标(运用 Java 版别 JDK 1.8)
<!-- MySQL 驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.9</version>
</dependency>
一般刺进
在完结批量刺进之前呢,为了更明显的看到一般刺进办法和批量刺进办法的不同,先来写一遍一般刺进(循环刺进)的完结并记载一下刺进所需时刻。
运用 JDBC 不需求增加额定的装备文件,直接上代码:
/**
* JDBC - 一般刺进(循环遍历一条一条刺进)
* @author 单程车票
*/
public class JDBCDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "123456";
String driver = "com.mysql.jdbc.Driver";
// sql句子
String sql = "INSERT INTO fee(`owner`,`fee1`,`fee2`,`fee3`,`fee4`,`fee5`) VALUES (?,?,?,?,?,?);";
Connection conn = null;
PreparedStatement ps = null;
// 开端时刻
long start = System.currentTimeMillis();
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
ps = conn.prepareStatement(sql);
// 循环遍历刺进数据
for (int i = 1; i <= 100000; i++) {
ps.setString(1, "o"+i);
ps.setBigDecimal(2, new BigDecimal("11111.111"));
ps.setBigDecimal(3, new BigDecimal("11111.111"));
ps.setBigDecimal(4, new BigDecimal("11111.111"));
ps.setBigDecimal(5, new BigDecimal("11111.111"));
ps.setBigDecimal(6, new BigDecimal("11111.111"));
ps.executeUpdate();
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 结束时刻
long end = System.currentTimeMillis();
System.out.println("十万条数据刺进时刻(一般刺进办法):" + (end - start) + " ms");
}
}
履行成果:
能够看到运用一般刺进的办法刺进 10w 条数据需求的时刻大概在 80 s左右,接下来看看运用批量刺进的办法优化了多少。
批处理刺进
下面就是 JDBC 批量刺进的完结办法:批处理刺进办法 + 手动业务提交。
代码:
/**
* JDBC - 批处理刺进
* @author 单程车票
*/
public class JDBCPlusDemo {
public static void main(String[] args) {
// url 设置答应重写批量提交 rewriteBatchedStatements=true
String url = "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true";
String user = "root";
String password = "123456";
String driver = "com.mysql.jdbc.Driver";
// sql句子(留意url设置为rewriteBatchedStatements=true时,不答应sql句子带有;号,不然会抛出BatchUpdateException异常)
String sql = "INSERT INTO fee(`owner`,`fee1`,`fee2`,`fee3`,`fee4`,`fee5`) VALUES (?,?,?,?,?,?)";
Connection conn = null;
PreparedStatement ps = null;
// 开端时刻
long start = System.currentTimeMillis();
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
ps = conn.prepareStatement(sql);
// 封闭主动提交
conn.setAutoCommit(false);
for (int i = 1; i <= 100000; i++) {
ps.setString(1, "o"+i);
ps.setBigDecimal(2, new BigDecimal("11111.111"));
ps.setBigDecimal(3, new BigDecimal("11111.111"));
ps.setBigDecimal(4, new BigDecimal("11111.111"));
ps.setBigDecimal(5, new BigDecimal("11111.111"));
ps.setBigDecimal(6, new BigDecimal("11111.111"));
// 参加批处理(将当时sql参加缓存)
ps.addBatch();
// 以 1000 条数据作为分片
if (i % 1000 == 0) {
// 履行缓存中的sql句子
ps.executeBatch();
// 清空缓存
ps.clearBatch();
}
}
ps.executeBatch();
ps.clearBatch();
// 业务提交(实践开发中需求判别有刺进失败的需求在 finally 中做好业务回滚操作)
conn.commit();
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 结束时刻
long end = System.currentTimeMillis();
System.out.println("十万条数据刺进时刻(批处理刺进):" + (end - start) + " ms");
}
}
履行成果:
能够看到运用批处理+手动提交的办法刺进 10w 条数据的履行时刻大概在 1s 左右,速度明显进步了许多。接下来看看批处理办法需求留意的细节和重点有哪些:
-
运用
PreparedStatement
批量处理的三个办法来完结批量操作,分别是:-
addBatch()
:该办法用于向批处理中增加一批参数。通常在履行批量操作之前,经过多次调用该办法,将不同的参数增加到批处理中,然后一次性将这些参数一同提交给数据库履行。 -
executeBatch()
:该办法用于履行当时的批处理。一旦增加完一切参数到批处理后,能够调用该办法将这些参数提交给数据库履行。该办法会回来一个整数数组,表明批处理中每个操作所影响的行数。 -
clearBatch()
:该办法用于清空当时的批处理。在履行完当时批处理后需求清空当时批处理,能够调用该办法来清空之前增加的一切参数。
-
-
在装备 MySQL 的 url 时需求加上
rewriteBatchedStatements=true
才能到达真正意义上的批处理作用。- 这个设置是为了把答应重写批量提交(
rewriteBatchedStatements
)敞开。 - 在默许不敞开的情况下,会无视
executeBatch()
办法,将原本应该批量履行的 sql 句子又离散成单条句子履行。也就是说假如不敞开答应重写批量提交,实践上批处理操作和原本的单条句子循环刺进的作用相同。
- 这个设置是为了把答应重写批量提交(
-
运用 JDBC 时需求留意刺进的 sql 句子结尾不能带
;
号,不然会抛出BatchUpdateException
异常。- 如图:
- 这是因为运用批处理是会在结尾处进行拼接,假如结尾有
;
号会导致刺进句子变成INSERT INTO TABLE(X,X,X,X) VALUES (X,X,X,X);,(X,X,X,X);,(X,X,X,X);,
这样自然会呈现 sql 语法错误。
-
需求留意批量处理时的分片操作,上面代码的分片巨细为 1000(这是参阅了后边 MP 结构的默许分片巨细),分片操作能够避免一次性提交的数据量过大从而导致数据库在处理时呈现的功用问题和内存占用过高问题,有效的分片能够减轻数据库的担负。
-
运用手动业务提交能够进步刺进速度,在批量刺进很多数据时,手动业务提交相关于主动提交业务来说能够削减磁盘的写入次数,削减锁竞赛,从而进步刺进的功用。
- 能够经过
setAutoCommit(false)
来封闭主动提交业务,等悉数批量刺进完结后再经过commit()
手动提交业务。
- 能够经过
MyBatis / MyBatis Plus 完结批量刺进
因为 MyBatis Plus 相关于 MyBatis 来说只做了增强并没有改动 MyBatis 的功用,所以接下来将以 MyBatis Plus 来完结批量刺进,其中有些办法是两个结构都能够运用的,有些则是 MP 独有的,会在后续解说中标示出来。
MyBatis Plus 测验环境
先来配一下 MP 需求的测验环境,持续运用上文的 JDBC 测验环境并补充 MP 需求的测验环境:
maven 坐标:
<!-- MyBatis Plus 依赖 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.3.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
application.properties
:
# 装备数据库
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
spring.datasource.username=root
spring.datasource.password=123456
完结代码
因为 MyBatis / MyBatis Plus 的测验代码过多,所以在这儿共同展示实体类、service、mapper 的完结代码,后续只给出测验代码。
Fee.java – 实体类
/**
* Fee 实体类
* @author 单程车票
*/
@TableName("fee")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Fee {
@TableId(type = IdType.AUTO)
private Long id;
private String owner;
private BigDecimal fee1;
private BigDecimal fee2;
private BigDecimal fee3;
private BigDecimal fee4;
private BigDecimal fee5;
}
FeeMapper.java – Mapper 接口
/**
* Fee Mapper接口
* @author 单程车票
*/
@Mapper
public interface FeeMapper extends BaseMapper<Fee> {
/**
* 单条数据刺进
* @param fee 实体类
* @return 刺进成果
*/
int insertByOne(Fee fee);
/**
* foreach动态拼接sql刺进
* @param feeList 实体类集合
* @return 刺进成果
*/
int insertByForeach(List<Fee> feeList);
}
这儿承继 BaseMapper
仅仅为了运用最终的 MP 自带的批处理刺进办法。假如不运用那种办法则能够不承继。
FeeMapper.xml – Mapper 映射文件
<?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="db.review.mapper.FeeMapper">
<insert id="insertByOne">
INSERT INTO fee(`owner`,`fee1`,`fee2`,`fee3`,`fee4`,`fee5`)
VALUES (#{owner}, #{fee1}, #{fee2}, #{fee3}, #{fee4}, #{fee5})
</insert>
<insert id="insertByForeach">
INSERT INTO fee(`owner`,`fee1`,`fee2`,`fee3`,`fee4`,`fee5`)
VALUES
<foreach collection="feeList" item="fee" separator=",">
(#{fee.owner}, #{fee.fee1}, #{fee.fee2}, #{fee.fee3}, #{fee.fee4}, #{fee.fee5})
</foreach>
</insert>
</mapper>
FeeService.java – Service 接口
/**
* Fee Service 接口
* @author 单程车票
*/
public interface FeeService extends IService<Fee> {
/**
* 一般刺进
* @param feeList 实体类列表
* @return 刺进成果
*/
int saveByFor(List<Fee> feeList);
/**
* foreach 动态拼接刺进
* @param feeList 实体类列表
* @return 刺进成果
*/
int saveByForeach(List<Fee> feeList);
/**
* 批处理刺进
* @param feeList 实体类列表
* @return 刺进成果
*/
int saveByBatch(List<Fee> feeList);
}
相同这儿承继 IService
也仅仅为了运用最终的 MP 自带的批处理刺进办法。假如不运用那种办法则能够不承继。
FeeServiceImpl.java – Service 完结类
/**
* Fee Service 完结类
* @author 单程车票
*/
@Service
public class FeeServiceImpl extends ServiceImpl<FeeMapper, Fee> implements FeeService {
@Resource
private FeeMapper feeMapper;
@Resource
private SqlSessionFactory sqlSessionFactory;
@Override
public int saveByFor(List<Fee> feeList) {
// 记载成果(影响行数)
int res = 0;
// 循环刺进
for (Fee fee : feeList) {
res += feeMapper.insertByOne(fee);
}
return res;
}
@Override
public int saveByForeach(List<Fee> feeList) {
// 经过mapper的foreach动态拼接sql刺进
return feeMapper.insertByForeach(feeList);
}
@Transactional
@Override
public int saveByBatch(List<Fee> feeList) {
// 记载成果(影响行数)
int res = 0;
// 敞开批处理模式
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
FeeMapper feeMapper = sqlSession.getMapper(FeeMapper.class);
for (int i = 1; i <= feeList.size(); i++) {
// 运用mapper的单条刺进办法刺进
res += feeMapper.insertByOne(feeList.get(i-1));
// 进行分片相似 JDBC 的批处理
if (i % 100000 == 0) {
sqlSession.commit();
sqlSession.clearCache();
}
}
sqlSession.commit();
sqlSession.clearCache();
return res;
}
}
相同这儿承继 ServiceImpl
也仅仅为了运用最终的 MP 自带的批处理刺进办法。假如不运用那种办法则能够不承继。
一般刺进
相同为了形成对比,先来看看循环单条刺进所需求的履行时刻,经过 SpringBootTest 进行测验:
测验代码(代码没有运用 MyBatis Plus 的增强功用,所以这种办法在 MyBatis 和 MyBatis Plus 两个结构都适用):
/**
* MP 测验类
*
* @author 单程车票
*/
@SpringBootTest
public class MPDemo {
@Resource
private FeeService feeService;
@Test
public void mpDemo1() {
// 获取 10w 条测验数据
List<Fee> feeList = getFeeList();
// 开端时刻
long start = System.currentTimeMillis();
// 一般刺进
feeService.saveByFor(feeList);
// 结束时刻
long end = System.currentTimeMillis();
System.out.println("十万条数据刺进时刻(一般刺进办法):" + (end - start) + " ms");
}
private List<Fee> getFeeList() {
List<Fee> list = new ArrayList<>();
for (int i = 1; i <= 100000; i++) {
list.add(new Fee(null, "o" + i,
new BigDecimal("11111.111"),
new BigDecimal("11111.111"),
new BigDecimal("11111.111"),
new BigDecimal("11111.111"),
new BigDecimal("11111.111")));
}
return list;
}
}
测验成果:
能够看到花费时刻大致和 JDBC 的一般刺进办法共同都在 80s 左右。
foreach 动态拼接刺进
接下来,看看运用 foreach 动态sql来完结拼接 sql 的办法进行刺进的履行时刻是多少。
测验代码(代码没有运用 MyBatis Plus 的增强功用,所以这种办法在 MyBatis 和 MyBatis Plus 两个结构都适用):
/**
* MP 测验类
*
* @author 单程车票
*/
@SpringBootTest
public class MPDemo {
@Resource
private FeeService feeService;
@Test
public void mpDemo2() {
// 获取 10w 条测验数据
List<Fee> feeList = getFeeList();
// 开端时刻
long start = System.currentTimeMillis();
// foreach动态拼接刺进
feeService.saveByForeach(feeList);
// 结束时刻
long end = System.currentTimeMillis();
System.out.println("十万条数据刺进时刻(foreach动态拼接刺进办法):" + (end - start) + " ms");
}
private List<Fee> getFeeList() {
List<Fee> list = new ArrayList<>();
for (int i = 1; i <= 100000; i++) {
list.add(new Fee(null, "o" + i,
new BigDecimal("11111.111"),
new BigDecimal("11111.111"),
new BigDecimal("11111.111"),
new BigDecimal("11111.111"),
new BigDecimal("11111.111")));
}
return list;
}
}
测验成果:
能够看到当数据量为 10w 条时,测验成果报错,这是因为默许情况下 MySQL 可履行的最大 SQL 句子巨细为 4194304 即 4MB,这儿运用动态 SQL 拼接后的巨细远大于默许值,故报错。
能够经过设置 MySQL 的默许 sql 巨细来解决此问题(这儿设置为10MB):
set global max_allowed_packet=10*1024*1024;
重新运转后的测验成果:
能够看到增大默许是 SQL 巨细后刺进的时刻在 3s 左右,相关于 JDBC 的批处理来说速度要稍微慢一点,但比起一般刺进来说已经优化许多了。可是这种办法的坏处也很明显,就是无法确定 SQL 究竟多大,不能总是更改默许的 SQL 巨细,不实用。
批处理刺进
接下来,来看看 JDBC 的批处理刺进办法在 MyBatis / MyBatis Plus 结构中是怎么完结的。
测验代码(代码没有运用 MyBatis Plus 的增强功用,所以这种办法在 MyBatis 和 MyBatis Plus 两个结构都适用):
/**
* MP 测验类
*
* @author 单程车票
*/
@SpringBootTest
public class MPDemo {
@Resource
private FeeService feeService;
@Test
public void mpDemo3() {
// 获取 10w 条测验数据
List<Fee> feeList = getFeeList();
// 开端时刻
long start = System.currentTimeMillis();
// 批处理刺进
feeService.saveByBatch(feeList);
// 结束时刻
long end = System.currentTimeMillis();
System.out.println("十万条数据刺进时刻(批处理刺进办法):" + (end - start) + " ms");
}
private List<Fee> getFeeList() {
List<Fee> list = new ArrayList<>();
for (int i = 0; i < 100000; i++) {
list.add(new Fee(null, "o" + i,
new BigDecimal("11111.111"),
new BigDecimal("11111.111"),
new BigDecimal("11111.111"),
new BigDecimal("11111.111"),
new BigDecimal("11111.111")));
}
return list;
}
}
测验成果:
能够看到运用 MyBatis / MyBatis Plus 结构完结的批处理刺进办法和 JDBC 的批处理刺进办法的履行时刻都在 1s 左右。
完结的中心代码如下:
@Transactional
@Override
public int saveByBatch(List<Fee> feeList) {
// 记载成果(影响行数)
int res = 0;
// 敞开批处理模式
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
FeeMapper feeMapper = sqlSession.getMapper(FeeMapper.class);
for (int i = 1; i <= feeList.size(); i++) {
// 运用mapper的单条刺进办法刺进
res += feeMapper.insertByOne(feeList.get(i-1));
// 进行分片相似 JDBC 的批处理
if (i % 100000 == 0) {
sqlSession.commit();
sqlSession.clearCache();
}
}
sqlSession.commit();
sqlSession.clearCache();
return res;
}
需求留意的是:
- 和 JDBC 相同都需求敞开答应重写批量处理提交(即在装备文件的数据库装备 url 中加上
rewriteBatchedStatements=true
)。 - 代码中需求运用批处理模式(运用
SqlSessionFactory
设置批处理模式并获取对应的 Mapper 接口) - 代码中相同进行了分片操作,目的是为了减轻数据库的担负避免在处理时内存占用过高。
- 能够在完结办法中加上
@Transactional
注解来起到手动提交业务的作用(优点和 JDBC 相同)。
MP 自带的批处理刺进
接下来,看看 MyBatis Plus 自带的批处理办法的履行效率怎么。
测验代码(留意代码中没有运用到上面的任何完结代码,而是依托 MP 自带的 saveBatch()
办法完结批量刺进):
/**
* MP 测验类
*
* @author 单程车票
*/
@SpringBootTest
public class MPDemo {
@Resource
private FeeService feeService;
@Test
public void mpDemo4() {
// 获取 10w 条测验数据
List<Fee> feeList = getFeeList();
// 开端时刻
long start = System.currentTimeMillis();
// MP 自带的批处理刺进
feeService.saveBatch(feeList);
// 结束时刻
long end = System.currentTimeMillis();
System.out.println("十万条数据刺进时刻(MP 自带的批处理刺进办法):" + (end - start) + " ms");
}
private List<Fee> getFeeList() {
List<Fee> list = new ArrayList<>();
for (int i = 0; i < 100000; i++) {
list.add(new Fee(null, "o" + i,
new BigDecimal("11111.111"),
new BigDecimal("11111.111"),
new BigDecimal("11111.111"),
new BigDecimal("11111.111"),
new BigDecimal("11111.111")));
}
return list;
}
}
测验成果:
能够看到运用 MP 自带的批处理办法履行时刻在 2s 左右,虽然比自己完结的批处理办法差了一点点,可是架不住它能够拿来就用,所以这也是一种好的选择。
留意:这儿仍旧需求敞开答应重写批量处理提交(即在装备文件的数据库装备 url 中加上rewriteBatchedStatements=true
)。这个很要害,不然效率上会大打折扣的。
放一张没有敞开答应重写批量处理的履行成果:
MP 自带的
saveBatch()
办法源码分析
信任我们不仅仅为了运用 MP 的批量处理办法,应该都猎奇 MP 自带的 saveBatch()
办法是怎么完结的,那么接下来我想深化源码一同来看看。
进入第一层源码:
能够看到这儿带上了一个参数 batchSize = 1000
(这儿其实就是分片巨细 1000,也是我上述代码借鉴的分片巨细),接着往下进入 executeBatch()
办法:
能够看到 Lambda 表达式其实跟上面的完结批处理刺进办法相似,先一条一条刺进数据,当到达分片巨细后,提交并改写,从而到达批处理的作用。再深化到下一个 executeBatch()
办法会看到底层运用的也是批处理模式。
所以其实 MP 自带的批处理办法和上文中完结的批处理办法相似。
总要有总结
以上就是这次批量刺进场景问题下怎么经过 JDBC 和 MyBatis / MyBatis Plus 结构完结批量刺进的整个优化进程了。
经过上面的解说,信任我们应该也能够看出来哪些完结办法是有着良好的效率和功用的。
- 运用 JDBC 引荐运用自己完结批处理办法。
- 运用 MyBatis / MyBaits Plus 引荐运用自己完结的批处理办法或 MP 自带的批处理办法。
记住运用批处理办法进行批量刺进一定要带上 rewriteBatchedStatements=true
,这点很重要。