项目搭建
本次使用 mariadb-12.1.2 作为数据库
- 项目完整结构
interview-practice/
├── pom.xml # Maven 配置文件
├── .gitignore # Git 忽略文件
│
├── src/
│ ├── main/
│ │ ├── java/com/define/
│ │ │ ├── SpringBootJpaBatchApplication.java # 应用程序主入口
│ │ │ │
│ │ │ ├── entity/
│ │ │ │ └── User.java # 用户实体类
│ │ │ │
│ │ │ ├── repository/
│ │ │ │ └── UserRepository.java # 用户数据访问层
│ │ │ │
│ │ │ ├── service/
│ │ │ │ ├── BatchService.java # 批量操作服务接口
│ │ │ │ └── impl/
│ │ │ │ └── BatchServiceImpl.java # 批量操作服务实现
│ │ │ │
│ │ │ └── util/
│ │ │ └── UserDataGenerator.java # 测试数据生成器
│ │ │
│ │ └── resources/
│ │ ├── application.yml # 应用配置文件
│ │ └── schema.sql # 数据库初始化脚本
│ │
│ └── test/
│ └── java/com/define/
│ └── BatchPerformanceTest.java # 性能测试类
│
└── target/ # 编译输出目录（忽略）
- pom
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.5.8</version>
</parent>
<properties>
<maven.compiler.source>21</maven.compiler.source>
<maven.compiler.target>21</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<!-- Spring Boot Starter Web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Spring Boot Starter Data JPA -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- Spring Boot Starter JDBC -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- MySQL Driver -->
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<!-- Lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- Spring Boot Starter Test -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
- User
import jakarta.persistence.*;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Entity
@Table(name = "users")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(nullable = false, length = 100)
private String username;
@Column(nullable = false, length = 100)
private String email;
@Column(nullable = false, length = 20)
private String phone;
@Column(nullable = false)
private Integer age;
@Column(length = 200)
private String address;
@Column(name = "create_time")
private Long createTime;
@Column(name = "update_time")
private Long updateTime;
@PrePersist
protected void onCreate() {
createTime = System.currentTimeMillis();
updateTime = System.currentTimeMillis();
}
@PreUpdate
protected void onUpdate() {
updateTime = System.currentTimeMillis();
}
}
- UserRepository
import com.define.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
public interface UserRepository extends JpaRepository<User, Long> {
}
- BatchService
import com.define.entity.User;
import java.util.List;
public interface BatchService {
void batchInsertWithJdbcTemplate(List<User> users);
void batchUpdateWithJdbcTemplate(List<User> users);
void batchInsertWithHibernate(List<User> users);
void batchUpdateWithHibernate(List<User> users);
}
- BatchServiceImpl
import com.define.entity.User;
import com.define.service.BatchService;
import jakarta.annotation.Resource;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
@Slf4j
@RequiredArgsConstructor
public class BatchServiceImpl implements BatchService {
@Resource
private JdbcTemplate jdbcTemplate;
@PersistenceContext
private EntityManager entityManager;
private static final String INSERT_SQL = "INSERT INTO users (username, email, phone, age, address, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?, ?)";
private static final String UPDATE_SQL = "UPDATE users SET username = ?, email = ?, phone = ?, age = ?, address = ?, update_time = ? WHERE id = ?";
private static final int BATCH_SIZE = 50;
@Override
@Transactional
public void batchInsertWithJdbcTemplate(List<User> users) {
long startTime = System.currentTimeMillis();
jdbcTemplate.batchUpdate(INSERT_SQL, users, users.size(), (ps, user) -> {
ps.setString(1, user.getUsername());
ps.setString(2, user.getEmail());
ps.setString(3, user.getPhone());
ps.setInt(4, user.getAge());
ps.setString(5, user.getAddress());
ps.setLong(6, System.currentTimeMillis());
ps.setLong(7, System.currentTimeMillis());
});
long endTime = System.currentTimeMillis();
// log.info("JdbcTemplate批量插入 {} 条数据，耗时: {} ms", users.size(), endTime - startTime);
}
@Override
@Transactional
public void batchUpdateWithJdbcTemplate(List<User> users) {
long startTime = System.currentTimeMillis();
jdbcTemplate.batchUpdate(UPDATE_SQL, users, users.size(), (ps, user) -> {
ps.setString(1, user.getUsername());
ps.setString(2, user.getEmail());
ps.setString(3, user.getPhone());
ps.setInt(4, user.getAge());
ps.setString(5, user.getAddress());
ps.setLong(6, System.currentTimeMillis());
ps.setLong(7, user.getId());
});
long endTime = System.currentTimeMillis();
// log.info("JdbcTemplate批量更新 {} 条数据，耗时: {} ms", users.size(), endTime - startTime);
}
@Override
@Transactional
public void batchInsertWithHibernate(List<User> users) {
long startTime = System.currentTimeMillis();
for (int i = 0; i < users.size(); i++) {
entityManager.persist(users.get(i));
// 每BATCH_SIZE条清除一次一级缓存 防止内存溢出
if (i % BATCH_SIZE == 0 && i > 0) {
entityManager.flush();
entityManager.clear();
}
}
// 最后一次刷新
entityManager.flush();
entityManager.clear();
long endTime = System.currentTimeMillis();
// log.info("Hibernate批量插入 {} 条数据，耗时: {} ms", users.size(), endTime - startTime);
}
@Override
@Transactional
public void batchUpdateWithHibernate(List<User> users) {
long startTime = System.currentTimeMillis();
for (int i = 0; i < users.size(); i++) {
User user = entityManager.merge(users.get(i));
// 每BATCH_SIZE条清除一次一级缓存
if (i % BATCH_SIZE == 0 && i > 0) {
entityManager.flush();
entityManager.clear();
}
}
// 最后一次刷新
entityManager.flush();
entityManager.clear();
long endTime = System.currentTimeMillis();
// log.info("Hibernate批量更新 {} 条数据，耗时: {} ms", users.size(), endTime - startTime);
}
}
- UserDataGenerator
import com.define.entity.User;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
public class UserDataGenerator {
private static final String[] USERNAMES = {
"张三", "李四", "王五", "赵六", "钱七", "孙八", "周九", "吴十",
"郑十一", "王十二", "刘十三", "陈十四", "杨十五", "黄十六", "赵十七"
};
private static final String[] DOMAINS = {
"@qq.com", "@163.com", "@gmail.com", "@outlook.com", "@126.com"
};
private static final String[] CITIES = {
"北京市", "上海市", "广州市", "深圳市", "杭州市", "成都市", "武汉市", "西安市"
};
private static final Random random = new Random();
public static List<User> generateUsers(int count) {
List<User> users = new ArrayList<>();
for (int i = 0; i < count; i++) {
users.add(generateUser());
}
return users;
}
public static User generateUser() {
String username = USERNAMES[random.nextInt(USERNAMES.length)] + random.nextInt(1000);
String email = "user" + random.nextInt(10000) + DOMAINS[random.nextInt(DOMAINS.length)];
String phone = String.format("1%s%d", getRandomDigit(), random.nextInt(1000000000));
int age = 18 + random.nextInt(50);
String address = CITIES[random.nextInt(CITIES.length)] + "街道" + (random.nextInt(100) + 1) + "号";
return new User(null, username, email, phone, age, address, null, null);
}
private static int getRandomDigit() {
int[] digits = {3, 5, 7, 8, 9};
return digits[random.nextInt(digits.length)];
}
public static List<User> generateUsersWithIds(List<User> existingUsers) {
List<User> users = new ArrayList<>();
for (User existing : existingUsers) {
User updated = new User();
updated.setId(existing.getId());
updated.setUsername(generateUser().getUsername());
updated.setEmail(generateUser().getEmail());
updated.setPhone(generateUser().getPhone());
updated.setAge(generateUser().getAge());
updated.setAddress(generateUser().getAddress());
users.add(updated);
}
return users;
}
}
- SpringBootJpaBatchApplication
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class SpringBootJpaBatchApplication {
public static void main(String[] args) {
SpringApplication.run(SpringBootJpaBatchApplication.class, args);
}
}
- application.yml
spring:
application:
name: spring-boot-jpa-batch-demo
datasource:
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: 123456
jpa:
hibernate:
ddl-auto: none
show-sql: false
properties:
hibernate:
dialect: org.hibernate.dialect.MySQLDialect
format_sql: true
jdbc:
batch_size: 50
batch_versioned_data: true
order_inserts: true
order_updates: true
- schema.sql
CREATE TABLE IF NOT EXISTS users (
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
username VARCHAR(100) NOT NULL COMMENT '用户名',
email VARCHAR(100) NOT NULL COMMENT '邮箱',
phone VARCHAR(20) NOT NULL COMMENT '手机号',
age INT NOT NULL COMMENT '年龄',
address VARCHAR(200) COMMENT '地址',
create_time BIGINT COMMENT '创建时间',
update_time BIGINT COMMENT '更新时间',
INDEX idx_username (username),
INDEX idx_email (email),
INDEX idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
- BatchPerformanceTest
import com.define.entity.User;
import com.define.repository.UserRepository;
import com.define.service.BatchService;
import com.define.util.UserDataGenerator;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
import static org.junit.jupiter.api.Assertions.*;
@SpringBootTest
class BatchPerformanceTest {
@Autowired
private BatchService batchService;
@Autowired
private UserRepository userRepository;
@Test
void testJdbcTemplateBatchInsertPerformance() {
System.out.println("\n========== JdbcTemplate 批量插入性能测试 ==========");
int[] batchSizes = {10};
for (int batchSize : batchSizes) {
List<User> users = UserDataGenerator.generateUsers(batchSize);
long startTime = System.currentTimeMillis();
batchService.batchInsertWithJdbcTemplate(users);
long endTime = System.currentTimeMillis();
long duration = endTime - startTime;
System.out.printf("批量大小: %d, 耗时: %d ms, 平均: %.3f ms/条%n", batchSize, duration, (double) duration / batchSize);
// 验证数据是否插入成功
long count = userRepository.count();
assertEquals(batchSize, count, "插入数据数量不匹配");
// 清空数据
userRepository.deleteAll();
}
}
@Test
void testHibernateBatchInsertPerformance() {
System.out.println("\n========== Hibernate 批量插入性能测试 ==========");
int[] batchSizes = {10};
for (int batchSize : batchSizes) {
List<User> users = UserDataGenerator.generateUsers(batchSize);
long startTime = System.currentTimeMillis();
batchService.batchInsertWithHibernate(users);
long endTime = System.currentTimeMillis();
long duration = endTime - startTime;
System.out.printf("批量大小: %d, 耗时: %d ms, 平均: %.3f ms/条%n", batchSize, duration, (double) duration / batchSize);
// 验证数据是否插入成功
long count = userRepository.count();
assertEquals(batchSize, count, "插入数据数量不匹配");
// 清空数据
userRepository.deleteAll();
}
}
@Test
void testJdbcTemplateBatchUpdatePerformance() {
System.out.println("\n========== JdbcTemplate 批量更新性能测试 ==========");
// 先插入数据
int batchSize = 10;
List<User> users = UserDataGenerator.generateUsers(batchSize);
batchService.batchInsertWithJdbcTemplate(users);
// 获取所有数据并生成更新数据
List<User> allUsers = userRepository.findAll();
List<User> updatedUsers = UserDataGenerator.generateUsersWithIds(allUsers);
// 测试批量更新
long startTime = System.currentTimeMillis();
batchService.batchUpdateWithJdbcTemplate(updatedUsers);
long endTime = System.currentTimeMillis();
long duration = endTime - startTime;
System.out.printf("批量更新大小: %d, 耗时: %d ms, 平均: %.3f ms/条%n", batchSize, duration, (double) duration / batchSize);
// 验证数据是否更新成功
List<User> afterUpdate = userRepository.findAll();
assertEquals(batchSize, afterUpdate.size(), "更新后数据数量不匹配");
assertNotEquals(allUsers.get(0).getUsername(), afterUpdate.get(0).getUsername(), "数据应该被更新");
// 清空数据
userRepository.deleteAll();
}
@Test
void testHibernateBatchUpdatePerformance() {
System.out.println("\n========== Hibernate 批量更新性能测试 ==========");
// 先插入数据
int batchSize = 10;
List<User> users = UserDataGenerator.generateUsers(batchSize);
batchService.batchInsertWithHibernate(users);
// 获取所有数据并生成更新数据
List<User> allUsers = userRepository.findAll();
List<User> updatedUsers = UserDataGenerator.generateUsersWithIds(allUsers);
// 测试批量更新
long startTime = System.currentTimeMillis();
batchService.batchUpdateWithHibernate(updatedUsers);
long endTime = System.currentTimeMillis();
long duration = endTime - startTime;
System.out.printf("批量更新大小: %d, 耗时: %d ms, 平均: %.3f ms/条%n", batchSize, duration, (double) duration / batchSize);
// 验证数据是否更新成功
List<User> afterUpdate = userRepository.findAll();
assertEquals(batchSize, afterUpdate.size(), "更新后数据数量不匹配");
assertNotEquals(allUsers.get(0).getUsername(), afterUpdate.get(0).getUsername(), "数据应该被更新");
// 清空数据
userRepository.deleteAll();
}
@Test
void testComparativePerformance() {
System.out.println("\n========== JdbcTemplate vs Hibernate 批量插入性能对比 ==========");
int[] batchSizes = {2000, 5000, 10000};
System.out.printf("%-10s %-20s %-20s %-15s%n", "数据量", "JdbcTemplate(ms)", "Hibernate(ms)", "差异(ms)");
System.out.println("=".repeat(70));
for (int batchSize : batchSizes) {
// JdbcTemplate 测试
List<User> jdbcUsers = UserDataGenerator.generateUsers(batchSize);
long jdbcStart = System.currentTimeMillis();
batchService.batchInsertWithJdbcTemplate(jdbcUsers);
long jdbcEnd = System.currentTimeMillis();
long jdbcDuration = jdbcEnd - jdbcStart;
userRepository.deleteAll();
// Hibernate 测试
List<User> hibernateUsers = UserDataGenerator.generateUsers(batchSize);
long hibernateStart = System.currentTimeMillis();
batchService.batchInsertWithHibernate(hibernateUsers);
long hibernateEnd = System.currentTimeMillis();
long hibernateDuration = hibernateEnd - hibernateStart;
System.out.printf("%-10d %-20d %-20d %-15d%n", batchSize, jdbcDuration, hibernateDuration, jdbcDuration - hibernateDuration);
userRepository.deleteAll();
}
}
@Test
void testUpdateComparativePerformance() {
System.out.println("\n========== JdbcTemplate vs Hibernate 批量更新性能对比 ==========");
int[] batchSizes = {2000, 5000, 10000};
System.out.printf("%-10s %-20s %-20s %-15s%n", "数据量", "JdbcTemplate(ms)", "Hibernate(ms)", "差异(ms)");
System.out.println("=".repeat(70));
for (int batchSize : batchSizes) {
// 准备数据
List<User> users = UserDataGenerator.generateUsers(batchSize);
batchService.batchInsertWithJdbcTemplate(users);
// 获取数据并准备更新
List<User> allUsers = userRepository.findAll();
// JdbcTemplate 更新测试
List<User> jdbcUpdatedUsers = UserDataGenerator.generateUsersWithIds(allUsers);
long jdbcStart = System.currentTimeMillis();
batchService.batchUpdateWithJdbcTemplate(jdbcUpdatedUsers);
long jdbcEnd = System.currentTimeMillis();
long jdbcDuration = jdbcEnd - jdbcStart;
// Hibernate 更新测试
List<User> hibernateUpdatedUsers = UserDataGenerator.generateUsersWithIds(allUsers);
long hibernateStart = System.currentTimeMillis();
batchService.batchUpdateWithHibernate(hibernateUpdatedUsers);
long hibernateEnd = System.currentTimeMillis();
long hibernateDuration = hibernateEnd - hibernateStart;
System.out.printf("%-10d %-20d %-20d %-15d%n", batchSize, jdbcDuration, hibernateDuration, jdbcDuration - hibernateDuration);
userRepository.deleteAll();
}
}
}
配置测试
与批量操作相关的参数有挺多的，接下来我们逐个来测试
如何查看是否做了批量操作
折腾了很久才发现，无论是使用 P6Spy 还是调整 JDBC 或 Hibernate 的日志级别，都无法看到实际执行的批量 SQL 语句，目前网传有两种有效方法可以实现这一目标
- 第一种方法（经实际验证，切实有效）：查看 MySQL 的 general_log
- 第二种办法（经实际验证，未能生效）：使用 datasource-proxy
general_log
开启 MySQL 的 general_log，然后去对应的 general_log_file 中才能看到经驱动重写后的完整 SQL（例如合并后的多值 INSERT 语句）
-- 查看
SHOW VARIABLES LIKE 'general_log%';
-- 开启
SET GLOBAL general_log = 'ON';
-- 查看
SHOW VARIABLES LIKE 'general_log%';
MariaDB 的 general_log_file 位置取决于安装配置，通常在 ${MariaDB}/data 目录下，也可执行 SELECT @@datadir 查看
datasource-proxy（无效）
参考 Configuring a datasource-proxy in Spring Boot 来做整合
- 添加依赖
<!-- Source: https://mvnrepository.com/artifact/net.ttddyy/datasource-proxy -->
<dependency>
<groupId>net.ttddyy</groupId>
<artifactId>datasource-proxy</artifactId>
<version>1.11.0</version>
<scope>compile</scope>
</dependency>
- 添加配置类
import net.ttddyy.dsproxy.listener.logging.SLF4JLogLevel;
import net.ttddyy.dsproxy.support.ProxyDataSourceBuilder;
import org.aopalliance.intercept.MethodInterceptor;
import org.aopalliance.intercept.MethodInvocation;
import org.springframework.aop.framework.ProxyFactory;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.config.BeanPostProcessor;
import org.springframework.stereotype.Component;
import org.springframework.util.ReflectionUtils;
import javax.sql.DataSource;
import java.lang.reflect.Method;
@Component
public class DatasourceProxyBeanPostProcessor implements BeanPostProcessor {
@Override
public Object postProcessBeforeInitialization(final Object bean, final String beanName) throws BeansException {
return bean;
}
@Override
public Object postProcessAfterInitialization(final Object bean, final String beanName) throws BeansException {
if (bean instanceof DataSource) {
ProxyFactory factory = new ProxyFactory(bean);
factory.setProxyTargetClass(true);
factory.addAdvice(new ProxyDataSourceInterceptor((DataSource) bean));
return factory.getProxy();
}
return bean;
}
private record ProxyDataSourceInterceptor(DataSource dataSource) implements MethodInterceptor {
private ProxyDataSourceInterceptor(final DataSource dataSource) {
this.dataSource = ProxyDataSourceBuilder.create(dataSource).countQuery().logQueryBySlf4j(SLF4JLogLevel.INFO).build();
}
@Override
public Object invoke(final MethodInvocation invocation) throws Throwable {
Method proxyMethod = ReflectionUtils.findMethod(dataSource.getClass(), invocation.getMethod().getName());
if (proxyMethod != null) {
return proxyMethod.invoke(dataSource, invocation.getArguments());
}
return invocation.proceed();
}
}
}
- 测试效果表明：无论是否启用批量操作，所输出的日志内容完全相同，无法通过日志直观区分是否真正执行了批量处理
Hibernate
我们参考 Hibernate/JPA 批量插入与更新 和 Hibernate 官方文档 来验证各参数的作用
主键生成策略影响
如果使用 IDENTITY 主键生成策略，Hibernate 会自动禁用批量插入
以下内容摘自 Hibernate 官方文档
Hibernate disables insert batching at the JDBC level transparently if you use an identity identifier generator.
测试
- 主键生成策略设置为
GenerationType.IDENTITY
@Entity
@Table(name = "users")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
}
- 执行 testHibernateBatchInsertPerformance 方法，查看 general_log_file 中的内容如下
260124 10:34:34 58 Query SET autocommit=0
58 Query insert into users (address,age,create_time,email,phone,update_time,username) values ('上海市街道86号',35,1769222074566,'user4393@126.com','17870516392',1769222074566,'钱七865')
58 Query insert into users (address,age,create_time,email,phone,update_time,username) values ('武汉市街道24号',59,1769222074631,'user3669@126.com','17321693841',1769222074631,'刘十三355')
58 Query insert into users (address,age,create_time,email,phone,update_time,username) values ('杭州市街道76号',43,1769222074633,'user604@outlook.com','15946984771',1769222074633,'刘十三303')
58 Query insert into users (address,age,create_time,email,phone,update_time,username) values ('武汉市街道6号',55,1769222074636,'user9487@outlook.com','19877781559',1769222074636,'张三717')
58 Query insert into users (address,age,create_time,email,phone,update_time,username) values ('广州市街道61号',56,1769222074642,'user6534@outlook.com','15502740867',1769222074642,'刘十三623')
58 Query insert into users (address,age,create_time,email,phone,update_time,username) values ('杭州市街道31号',55,1769222074644,'user507@gmail.com','18693899633',1769222074644,'张三923')
58 Query insert into users (address,age,create_time,email,phone,update_time,username) values ('上海市街道59号',44,1769222074645,'user203@126.com','13712775938',1769222074645,'赵十七525')
58 Query insert into users (address,age,create_time,email,phone,update_time,username) values ('上海市街道6号',21,1769222074646,'user2462@qq.com','13407185175',1769222074646,'钱七409')
58 Query insert into users (address,age,create_time,email,phone,update_time,username) values ('杭州市街道34号',63,1769222074647,'user1763@126.com','17302138210',1769222074647,'郑十一656')
58 Query insert into users (address,age,create_time,email,phone,update_time,username) values ('杭州市街道12号',47,1769222074648,'user9699@qq.com','13691077092',1769222074648,'赵六902')
58 Query COMMIT
INSERT 语句是逐条执行的，说明并未走批量插入
- 执行 testHibernateBatchUpdatePerformance 方法，查看 general_log_file 中的内容如下
68 Query SET autocommit=0
68 Query ...
68 Query update users set address='武汉市街道20号',age=29,create_time=null,email='user3299@126.com',phone='17720966695',update_time=1769222389525,username='陈十四672' where id=11;update users set address='西安市街道34号',age=64,create_time=null,email='user9521@outlook.com',phone='18163379060',update_time=1769222389526,username='杨十五921' where id=12;update users set address='上海市街道14号',age=36,create_time=null,email='user1967@qq.com',phone='19436884381',update_time=1769222389526,username='刘十三424' where id=13;update users set address='杭州市街道90号',age=21,create_time=null,email='user6254@gmail.com',phone='17485987967',update_time=1769222389526,username='周九4' where id=14;update users set address='上海市街道19号',age=31,create_time=null,email='user7100@qq.com',phone='15451458883',update_time=1769222389526,username='王五341' where id=15;update users set address='成都市街道91号',age=65,create_time=null,email='user5321@gmail.com',phone='17150589522',update_time=1769222389526,username='钱七794' where id=16;update users set address='成都市街道15号',age=37,create_time=null,email='user1160@qq.com',phone='13337767779',update_time=1769222389527,username='孙八765' where id=17;update users set address='深圳市街道47号',age=20,create_time=null,email='user3538@163.com',phone='15322407208',update_time=1769222389527,username='刘十三870' where id=18;update users set address='杭州市街道20号',age=27,create_time=null,email='user562@163.com',phone='193961709',update_time=1769222389527,username='赵六495' where id=19;update users set address='深圳市街道14号',age=42,create_time=null,email='user6605@163.com',phone='18440567515',update_time=1769222389527,username='张三212' where id=20
68 Query COMMIT
UPDATE 语句一次性发给了 MySQL，说明走了批量更新，减少的是网络 IO 时间
- 主键生成策略设置为
GenerationType.SEQUENCE
@Entity
@Table(name = "users")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id;
}
- 创建表作为序列
CREATE TABLE users_seq (
next_val BIGINT NOT NULL
);
-- 必须设置初始值 否则报错
-- could not read a hi value - you need to populate the table: users_seq
INSERT INTO users_seq VALUES (1);
虽然高版本 MySQL 确实引入了原生 SEQUENCE 对象，但截至 Hibernate 6.6（当前使用版本为 6.6.36）Hibernate 并未适配 MySQL 原生 SEQUENCE 语法
-- 创建SEQUENCE的SQL语句
CREATE SEQUENCE users_seq
START WITH 1
INCREMENT BY 1;
将断点打在 com.mysql.cj.jdbc.ClientPreparedStatement#executeQuery 方法中，可看到执行的 SQL 如下
select next_val as id_val from users_seq for update
注意：这种方式每次获取 ID 都要加锁写表，性能不如 GenerationType.IDENTITY
- 执行 testHibernateBatchInsertPerformance 方法，查看 general_log_file 中的内容如下
260124 11:31:43 128 Query SET autocommit=0
129 Query SET autocommit=0
129 Query select next_val as id_val from users_seq for update
129 Query update users_seq set next_val= 151 where next_val=101
129 Query COMMIT
129 Query SET autocommit=1
128 Query SELECT @@session.transaction_read_only
128 Query insert into users (address,age,create_time,email,phone,update_time,username,id) values ('深圳市街道95号',43,1769225503092,'user5640@outlook.com','18537595092',1769225503092,'黄十六942',52),('西安市街道37号',28,1769225503101,'user3061@163.com','17914558495',1769225503101,'张三691',53),('西安市街道44号',35,1769225503101,'user2175@126.com','18471466083',1769225503101,'李四465',54),('西安市街道97号',44,1769225503101,'user5008@qq.com','19497216364',1769225503101,'刘十三969',55),('上海市街道73号',54,1769225503101,'user7883@126.com','18848111787',1769225503101,'钱七791',56),('西安市街道45号',39,1769225503101,'user2161@gmail.com','17272211570',1769225503101,'刘十三846',57),('上海市街道36号',53,1769225503101,'user1504@gmail.com','1350246128',1769225503101,'孙八644',58),('杭州市街道15号',41,1769225503101,'user8716@126.com','13810510773',1769225503101,'张三154',59),('成都市街道52号',45,1769225503101,'user60@126.com','17772959790',1769225503101,'陈十四874',60),('武汉市街道42号',59,1769225503101,'user9698@126.com','18824689064',1769225503101,'郑十一404',61)
128 Query COMMIT
看到了合并后的多值 SQL 语句，说明走了批量插入。除此之外，可以看到使用
GenerationType.SEQUENCE生成主键时，会比GenerationType.IDENTITY多执行两条 SQL 语句，所以前者性能必然更差
- 执行 testHibernateBatchUpdatePerformance 方法，查看 general_log_file 中的内容如下
260124 11:41:17 138 Query COMMIT
138 Query SET autocommit=1
138 Query SET SESSION TRANSACTION READ WRITE
138 Query SET autocommit=0
138 Query ...
138 Query update users set address='深圳市街道74号',age=66,create_time=null,email='user6633@outlook.com',phone='15314500231',update_time=1769226077022,username='周九234' where id=102;update users set address='杭州市街道86号',age=25,create_time=null,email='user3387@163.com',phone='19719882690',update_time=1769226077024,username='赵六17' where id=103;update users set address='北京市街道3号',age=49,create_time=null,email='user8031@126.com',phone='15908861802',update_time=1769226077024,username='王五696' where id=104;update users set address='深圳市街道53号',age=41,create_time=null,email='user778@gmail.com',phone='17671536541',update_time=1769226077024,username='钱七914' where id=105;update users set address='西安市街道21号',age=61,create_time=null,email='user5316@126.com',phone='1923391798',update_time=1769226077024,username='王五303' where id=106;update users set address='广州市街道81号',age=58,create_time=null,email='user3255@outlook.com',phone='17358503717',update_time=1769226077024,username='赵十七565' where id=107;update users set address='上海市街道61号',age=23,create_time=null,email='user6667@163.com',phone='18539677296',update_time=1769226077024,username='钱七773' where id=108;update users set address='深圳市街道83号',age=38,create_time=null,email='user899@gmail.com',phone='19537215282',update_time=1769226077024,username='陈十四737' where id=109;update users set address='深圳市街道74号',age=37,create_time=null,email='user6690@126.com',phone='18904170159',update_time=1769226077024,username='吴十31' where id=110;update users set address='广州市街道80号',age=42,create_time=null,email='user4384@outlook.com',phone='18754092598',update_time=1769226077024,username='陈十四856' where id=111
138 Query COMMIT
UPDATE 语句一次性发给了 MySQL，说明走了批量更新，减少的是网络 IO 时间
结论
- 如果使用 IDENTITY 主键生成策略，Hibernate 会自动禁用批量插入，但批量更新仍生效
- 如果使用 SEQUENCE 主键生成策略，批量插入可生效。目前 Hibernate 6.6 不支持 MySQL 原生的 SEQUENCE 对象，它会使用数据表来模拟 MySQL SEQUENCE 对象，插入数据时，会比 IDENTITY 多执行两条 SQL 语句
- Hibernate 支持 Oracle 的原生 SEQUENCE 且非常成熟，因此推荐在 Oracle 用 SEQUENCE 策略，在 MySQL 用 IDENTITY 策略
batch_size
Hibernate 默认不启用批量操作，这意味着每次插入或更新操作都会单独发送 SQL 语句，要启用批量操作，必须设置以下配置
spring.jpa.properties.hibernate.jdbc.batch_size=5
以下内容摘自官方文档
Controls the maximum number of statements Hibernate will batch together before asking the driver to execute the batch. Zero or a negative number disables this feature.
测试
上一节已经测试了配置 batch_size 的场景，本小节就只测试未配置 batch_size 的场景
- 修改 application.yml 配置即可，其余代码同上一节
spring:
application:
name: spring-boot-jpa-batch-demo
datasource:
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: 123456
jpa:
hibernate:
ddl-auto: none
show-sql: false
properties:
hibernate:
dialect: org.hibernate.dialect.MySQLDialect
format_sql: true
jdbc:
# batch_size: 50
batch_versioned_data: true
order_inserts: true
order_updates: true
- 执行 testHibernateBatchInsertPerformance 方法，查看 general_log_file 中的内容如下
260124 12:11:55 148 Query SET autocommit=0
149 Query SET autocommit=0
149 Query select next_val as id_val from users_seq for update
149 Query update users_seq set next_val= 251 where next_val=201
149 Query COMMIT
149 Query SET autocommit=1
148 Query insert into users (address,age,create_time,email,phone,update_time,username,id) values ('上海市街道71号',36,1769227915410,'user7100@126.com','1588466401',1769227915410,'赵六894',152)
148 Query insert into users (address,age,create_time,email,phone,update_time,username,id) values ('广州市街道71号',40,1769227915419,'user4129@163.com','1831838950',1769227915419,'吴十212',153)
148 Query insert into users (address,age,create_time,email,phone,update_time,username,id) values ('武汉市街道30号',51,1769227915419,'user4029@qq.com','18892595230',1769227915419,'孙八121',154)
148 Query insert into users (address,age,create_time,email,phone,update_time,username,id) values ('西安市街道74号',62,1769227915419,'user1102@gmail.com','13620358259',1769227915419,'陈十四839',155)
148 Query insert into users (address,age,create_time,email,phone,update_time,username,id) values ('成都市街道32号',41,1769227915419,'user834@163.com','1894001701',1769227915419,'王五339',156)
148 Query insert into users (address,age,create_time,email,phone,update_time,username,id) values ('北京市街道71号',53,1769227915419,'user2514@gmail.com','17324463038',1769227915419,'钱七701',157)
148 Query insert into users (address,age,create_time,email,phone,update_time,username,id) values ('武汉市街道62号',66,1769227915419,'user9622@163.com','1558356752',1769227915419,'孙八780',158)
148 Query insert into users (address,age,create_time,email,phone,update_time,username,id) values ('西安市街道45号',62,1769227915420,'user4591@163.com','19567699939',1769227915420,'赵十七552',159)
148 Query insert into users (address,age,create_time,email,phone,update_time,username,id) values ('杭州市街道9号',56,1769227915420,'user9179@gmail.com','18547657129',1769227915420,'李四258',160)
148 Query insert into users (address,age,create_time,email,phone,update_time,username,id) values ('深圳市街道22号',38,1769227915420,'user9561@qq.com','17562419587',1769227915420,'李四953',161)
148 Query COMMIT
INSERT 语句是逐条执行的，说明并未走批量插入
- 执行 testHibernateBatchUpdatePerformance 方法，查看 general_log_file 中的内容如下
260124 12:15:11 158 Query SET autocommit=0
159 Query ...
158 Query SET autocommit=1
158 Query SET SESSION TRANSACTION READ WRITE
158 Query SET autocommit=0
158 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username from users u1_0 where u1_0.id=202
158 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username from users u1_0 where u1_0.id=203
158 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username from users u1_0 where u1_0.id=204
158 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username from users u1_0 where u1_0.id=205
158 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username from users u1_0 where u1_0.id=206
158 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username from users u1_0 where u1_0.id=207
158 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username from users u1_0 where u1_0.id=208
158 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username from users u1_0 where u1_0.id=209
158 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username from users u1_0 where u1_0.id=210
158 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username from users u1_0 where u1_0.id=211
158 Query update users set address='杭州市街道75号',age=50,create_time=null,email='user6086@gmail.com',phone='18735080189',update_time=1769228111484,username='钱七222' where id=202
158 Query update users set address='武汉市街道15号',age=49,create_time=null,email='user5617@126.com',phone='17667676385',update_time=1769228111485,username='李四927' where id=203
158 Query update users set address='武汉市街道93号',age=55,create_time=null,email='user748@outlook.com',phone='15777261643',update_time=1769228111485,username='孙八580' where id=204
158 Query update users set address='杭州市街道52号',age=46,create_time=null,email='user5956@gmail.com',phone='17914649382',update_time=1769228111485,username='王十二97' where id=205
158 Query update users set address='武汉市街道85号',age=62,create_time=null,email='user4510@qq.com',phone='19114255747',update_time=1769228111485,username='吴十953' where id=206
158 Query update users set address='上海市街道92号',age=21,create_time=null,email='user8038@126.com',phone='1897094988',update_time=1769228111485,username='赵六89' where id=207
158 Query update users set address='北京市街道68号',age=57,create_time=null,email='user2081@qq.com',phone='19928807059',update_time=1769228111485,username='赵十七11' where id=208
158 Query update users set address='广州市街道87号',age=44,create_time=null,email='user9183@outlook.com',phone='13577148120',update_time=1769228111485,username='郑十一264' where id=209
158 Query update users set address='深圳市街道47号',age=49,create_time=null,email='user9056@qq.com',phone='15176720914',update_time=1769228111485,username='刘十三975' where id=210
158 Query update users set address='北京市街道41号',age=39,create_time=null,email='user951@126.com',phone='18958068511',update_time=1769228111485,username='张三966' where id=211
158 Query COMMIT
UPDATE 语句是逐条执行的，说明并未走批量更新
结论
- 未设置 batch_size 时，批量插入与批量更新都不生效
其余优化参数
以下参数都是在配置了 batch_size 参数时，针对特定场景的优化，所以测试前需恢复对 batch_size 的配置
order_inserts
补充说明下 order_inserts 参数的含义，官方文档释义如下
Forces Hibernate to order inserts to allow for more batching to be used. Comes with a performance hit, so benchmark before and after to see if this actually helps or hurts your application.
其核心作用是：对同一事务中要插入的实体按类型（Entity Type）进行分组和排序，使得相同类型的 INSERT 语句连续执行，从而提升 JDBC 批处理（batching）的效率
在默认情况下，Hibernate 按照实体被持久化的顺序执行 SQL，例如
entityManager.persist(userA); // User 类型
entityManager.persist(order1); // Order 类型
entityManager.persist(userB); // User 类型
entityManager.persist(order2); // Order 类型
默认生成的 SQL 顺序可能是
INSERT INTO users ...; -- userA
INSERT INTO orders ...; -- order1
INSERT INTO users ...; -- userB
INSERT INTO orders ...; -- order2
由于 INSERT 语句交替出现不同类型，JDBC 驱动无法将它们合并成批量操作（因为 PreparedStatement 的 SQL 模板不同），导致批处理失效、多次网络往返、性能下降
开启 order_inserts 后，Hibernate 会在 flush 时重新排序，把相同类型的实体放在一起
INSERT INTO users ...; -- userA
INSERT INTO users ...; -- userB
INSERT INTO orders ...; -- order1
INSERT INTO orders ...; -- order2
这样所有 users 的 INSERT 使用同一个 PreparedStatement 可合并为一批，所有 orders 的 INSERT 也合并为另一批，显著提升批处理效率
性能权衡
- 优点：大幅提升批量插入性能（实测可提升数倍吞吐量）
- 缺点：
- 需要在内存中缓存并重排待插入实体，增加 CPU 和内存开销
- 对小事务或单实体插入场景无益，反而略慢
- 如果实体间有外键依赖（如先插 user 再插 order 引用 user.id），排序可能影响逻辑（但 Hibernate 通常能正确处理依赖关系，先处理无依赖的实体）
📌 官方建议：在批量导入、数据迁移等场景下开启；普通 OLTP 场景可不开启，或通过压测决定
order_updates
与 order_inserts 类似，官方文档释义如下
Forces Hibernate to order SQL updates by the entity type and the primary key value of the items being updated. This allows for more batching to be used. It will also result in fewer transaction deadlocks in highly concurrent systems. Comes with a performance hit, so benchmark before and after to see if this actually helps or hurts your application.
batch_versioned_data
允许 Hibernate 对带有版本控制（@Version）的实体进行批量更新
作用
当在实体中使用 @Version 实现乐观锁时
@Entity
public class User {
@Id
private Long id;
@Version
private Integer version; // 乐观锁字段
}
Hibernate 默认会在 UPDATE 语句中加入版本检查
UPDATE users
SET name = ?, version = version + 1
WHERE id = ? AND version = ?;
由于每个实体的 version 值可能不同（比如 user1.version=3, user2.version=5），生成的 PreparedStatement 参数不同，导致 JDBC 无法将这些 UPDATE 合并到同一批中（因为 WHERE 条件中的 version = ? 值不同）因此，默认情况下，Hibernate 禁用对带 @Version 字段实体的批处理更新
而开启 batch_versioned_data=true 后，Hibernate 会仍然尝试批处理，即使存在版本字段。它使用相同的 SQL 模板（WHERE id = ? AND version = ?）将不同实体的 (id, version) 作为不同参数传入同一批次
-- 批量执行（JDBC addBatch）
UPDATE users SET name = ?, version = version + 1 WHERE id = 1 AND version = 3;
UPDATE users SET name = ?, version = version + 1 WHERE id = 2 AND version = 5;
虽然每条 SQL 的 version 值不同，但 SQL 模板相同，所以 JDBC 驱动仍可将其视为同一批
测试
- 实体类添加 version 字段
@Version
private Integer version;
- 表添加 version 字段
ALTER TABLE users ADD version int(11) DEFAULT NULL NULL;
batch_versioned_data = false
spring:
application:
name: spring-boot-jpa-batch-demo
datasource:
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: 123456
jpa:
hibernate:
ddl-auto: none
show-sql: false
properties:
hibernate:
dialect: org.hibernate.dialect.MySQLDialect
format_sql: true
jdbc:
batch_size: 50
batch_versioned_data: false
order_inserts: true
order_updates: true
- 执行 testHibernateBatchUpdatePerformance 方法，查看 general_log_file 中的内容如下
260124 15:27:29 228 Query SET autocommit=0
229 Query SET autocommit=0
229 Query select next_val as id_val from users_seq for update
229 Query update users_seq set next_val= 651 where next_val=601
229 Query COMMIT
229 Query SET autocommit=1
260124 15:27:30 228 Query insert into users (address,age,create_time,email,phone,update_time,username,version,id) values ('杭州市街道34号',67,1769239649986,'user2878@163.com','19725511251',1769239649986,'钱七786',1,552)
228 Query insert into users (address,age,create_time,email,phone,update_time,username,version,id) values ('杭州市街道94号',43,1769239649993,'user7226@outlook.com','13378262744',1769239649993,'张三3',1,553)
228 Query insert into users (address,age,create_time,email,phone,update_time,username,version,id) values ('广州市街道72号',60,1769239649994,'user7906@126.com','13165023079',1769239649994,'钱七488',1,554)
228 Query insert into users (address,age,create_time,email,phone,update_time,username,version,id) values ('杭州市街道14号',34,1769239649994,'user7973@gmail.com','19931913536',1769239649994,'李四824',1,555)
228 Query insert into users (address,age,create_time,email,phone,update_time,username,version,id) values ('深圳市街道20号',67,1769239649994,'user2889@163.com','19897435785',1769239649994,'吴十763',1,556)
228 Query insert into users (address,age,create_time,email,phone,update_time,username,version,id) values ('杭州市街道4号',46,1769239649994,'user2127@gmail.com','17584029854',1769239649994,'刘十三304',1,557)
228 Query insert into users (address,age,create_time,email,phone,update_time,username,version,id) values ('成都市街道5号',27,1769239649994,'user9973@qq.com','18108306628',1769239649994,'刘十三823',1,558)
228 Query insert into users (address,age,create_time,email,phone,update_time,username,version,id) values ('上海市街道91号',50,1769239649994,'user6662@qq.com','17958449556',1769239649994,'张三808',1,559)
228 Query insert into users (address,age,create_time,email,phone,update_time,username,version,id) values ('杭州市街道66号',58,1769239649994,'user391@126.com','13920012949',1769239649994,'吴十781',1,560)
228 Query insert into users (address,age,create_time,email,phone,update_time,username,version,id) values ('杭州市街道81号',55,1769239649994,'user8224@qq.com','19184105090',1769239649994,'周九314',1,561)
228 Query COMMIT
228 Query SET autocommit=1
228 Query SET SESSION TRANSACTION READ ONLY
228 Query SET autocommit=0
228 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0
228 Query COMMIT
228 Query SET autocommit=1
228 Query SET SESSION TRANSACTION READ WRITE
228 Query SET autocommit=0
228 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=552
228 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=553
228 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=554
228 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=555
228 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=556
228 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=557
228 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=558
228 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=559
228 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=560
228 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=561
228 Query update users set address='广州市街道25号',age=28,create_time=null,email='user1168@gmail.com',phone='191168534',update_time=1769239650152,username='王五69',version=2 where id=552 and version=1
228 Query update users set address='上海市街道66号',age=32,create_time=null,email='user8614@qq.com',phone='15785168710',update_time=1769239650153,username='周九847',version=2 where id=553 and version=1
228 Query update users set address='北京市街道37号',age=31,create_time=null,email='user7769@163.com',phone='13981305188',update_time=1769239650154,username='张三881',version=2 where id=554 and version=1
228 Query update users set address='广州市街道52号',age=26,create_time=null,email='user9958@163.com',phone='15136686091',update_time=1769239650154,username='李四571',version=2 where id=555 and version=1
228 Query update users set address='成都市街道60号',age=54,create_time=null,email='user2726@gmail.com',phone='18550865299',update_time=1769239650154,username='吴十670',version=2 where id=556 and version=1
228 Query update users set address='广州市街道3号',age=23,create_time=null,email='user3256@outlook.com',phone='19548429767',update_time=1769239650154,username='郑十一214',version=2 where id=557 and version=1
228 Query update users set address='西安市街道37号',age=45,create_time=null,email='user5130@gmail.com',phone='13277928705',update_time=1769239650154,username='李四130',version=2 where id=558 and version=1
228 Query update users set address='上海市街道79号',age=60,create_time=null,email='user9063@qq.com',phone='18918295549',update_time=1769239650154,username='张三264',version=2 where id=559 and version=1
228 Query update users set address='北京市街道16号',age=49,create_time=null,email='user4459@163.com',phone='13372939322',update_time=1769239650154,username='孙八838',version=2 where id=560 and version=1
228 Query update users set address='成都市街道32号',age=48,create_time=null,email='user3477@outlook.com',phone='13198095947',update_time=1769239650154,username='王十二980',version=2 where id=561 and version=1
228 Query COMMIT
INSERT 和 UPDATE 语句都是逐条执行的，说明批量插入和批量更新均失效
batch_versioned_data = true
spring:
application:
name: spring-boot-jpa-batch-demo
datasource:
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: 123456
jpa:
hibernate:
ddl-auto: none
show-sql: false
properties:
hibernate:
dialect: org.hibernate.dialect.MySQLDialect
format_sql: true
jdbc:
batch_size: 50
batch_versioned_data: true
order_inserts: true
order_updates: true
- 执行 testHibernateBatchUpdatePerformance 方法，查看 general_log_file 中的内容如下
260124 15:31:10 238 Query SET autocommit=0
239 Query SET autocommit=0
239 Query select next_val as id_val from users_seq for update
239 Query update users_seq set next_val= 701 where next_val=651
239 Query COMMIT
239 Query SET autocommit=1
238 Query SELECT @@session.transaction_read_only
238 Query insert into users (address,age,create_time,email,phone,update_time,username,version,id) values ('上海市街道85号',56,1769239870723,'user5739@126.com','15211059514',1769239870723,'杨十五103',1,602),('广州市街道95号',20,1769239870734,'user8558@outlook.com','18772968722',1769239870734,'陈十四453',1,603),('武汉市街道15号',36,1769239870734,'user8395@qq.com','15383092428',1769239870734,'杨十五49',1,604),('成都市街道22号',53,1769239870734,'user4759@gmail.com','17651771137',1769239870734,'黄十六410',1,605),('上海市街道90号',38,1769239870734,'user6905@163.com','19349926712',1769239870734,'张三580',1,606),('成都市街道2号',43,1769239870734,'user1218@126.com','18191078793',1769239870734,'陈十四273',1,607),('广州市街道82号',54,1769239870734,'user5377@qq.com','18137232842',1769239870734,'黄十六913',1,608),('武汉市街道77号',43,1769239870734,'user530@qq.com','18795859439',1769239870734,'孙八149',1,609),('北京市街道92号',32,1769239870734,'user60@qq.com','18555487232',1769239870734,'吴十779',1,610),('武汉市街道26号',31,1769239870734,'user9636@gmail.com','13676425514',1769239870734,'赵十七759',1,611)
238 Query COMMIT
238 Query SET autocommit=1
238 Query SET SESSION TRANSACTION READ ONLY
238 Query SET autocommit=0
238 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0
238 Query COMMIT
238 Query SET autocommit=1
238 Query SET SESSION TRANSACTION READ WRITE
238 Query SET autocommit=0
238 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=602
238 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=603
238 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=604
238 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=605
238 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=606
238 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=607
238 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=608
238 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=609
238 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=610
238 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username,u1_0.version from users u1_0 where u1_0.id=611
238 Query SELECT @@session.transaction_read_only
238 Query SELECT @@session.transaction_read_only
238 Query update users set address='杭州市街道35号',age=63,create_time=null,email='user1458@gmail.com',phone='17305612329',update_time=1769239870890,username='李四852',version=2 where id=602 and version=1;update users set address='深圳市街道23号',age=22,create_time=null,email='user2868@gmail.com',phone='15267954389',update_time=1769239870891,username='孙八856',version=2 where id=603 and version=1;update users set address='杭州市街道38号',age=59,create_time=null,email='user7962@outlook.com',phone='15801740730',update_time=1769239870891,username='孙八438',version=2 where id=604 and version=1;update users set address='深圳市街道22号',age=37,create_time=null,email='user6726@126.com',phone='18353677916',update_time=1769239870891,username='张三829',version=2 where id=605 and version=1;update users set address='上海市街道83号',age=30,create_time=null,email='user884@outlook.com',phone='15301015668',update_time=1769239870891,username='郑十一286',version=2 where id=606 and version=1;update users set address='成都市街道91号',age=39,create_time=null,email='user3135@qq.com',phone='13967467672',update_time=1769239870891,username='王五907',version=2 where id=607 and version=1;update users set address='武汉市街道14号',age=47,create_time=null,email='user6250@qq.com',phone='19356841533',update_time=1769239870891,username='郑十一315',version=2 where id=608 and version=1;update users set address='广州市街道17号',age=36,create_time=null,email='user3218@outlook.com',phone='17962984349',update_time=1769239870891,username='李四480',version=2 where id=609 and version=1;update users set address='成都市街道62号',age=37,create_time=null,email='user6071@qq.com',phone='15969182166',update_time=1769239870891,username='杨十五632',version=2 where id=610 and version=1;update users set address='杭州市街道47号',age=22,create_time=null,email='user8516@126.com',phone='15127493431',update_time=1769239870891,username='郑十一851',version=2 where id=611 and version=1
238 Query COMMIT
出现合并后的多值 SQL 语句，说明走了批量插入；UPDATE 语句一次性发给了 MySQL，说明走了批量更新
结论
- 当实体中存在被 @Version 修饰的字段时，需设置
batch_versioned_data = true否则批量插入和批量更新都会失效
黄金配置
使用 Hibernate 做批量更新的黄金配置
# 此参数值根据实际情况而定
hibernate.jdbc.batch_size=50
hibernate.jdbc.batch_versioned_data=true
hibernate.order_inserts=true
hibernate.order_updates=true
# MySQL JDBC 连接串参数（后续介绍）
...&rewriteBatchedStatements=true
JdbcTemplate
为保证测试效果，将 version 字段删除
主键生成策略影响
JdbcTemplate 不受主键生成策略（如 @GeneratedValue）的影响，因为该注解是 JPA/Hibernate 特有的机制，而 JdbcTemplate 是基于原生 JDBC 的操作，完全绕过了 JPA 的实体管理和 ID 生成逻辑
测试
- 主键生成策略设置为
GenerationType.IDENTITY
@Entity
@Table(name = "users")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
}
- 执行 testJdbcTemplateBatchInsertPerformance 方法，查看 general_log_file 中的内容如下
260124 15:46:26 248 Query SET autocommit=0
248 Query SELECT @@session.transaction_read_only
248 Query INSERT INTO users (username, email, phone, age, address, create_time, update_time) VALUES ('郑十一41', 'user5455@qq.com', '15636769138', 19, '广州市街道55号', 1769240786764, 1769240786764),('李四142', 'user6243@qq.com', '17303316160', 19, '北京市街道25号', 1769240786764, 1769240786764),('赵六275', 'user9489@qq.com', '15638748496', 39, '广州市街道33号', 1769240786764, 1769240786764),('钱七570', 'user7281@outlook.com', '19223062020', 43, '杭州市街道66号', 1769240786764, 1769240786764),('王五351', 'user3972@163.com', '18514440727', 24, '成都市街道70号', 1769240786764, 1769240786764),('钱七662', 'user1186@outlook.com', '13715643064', 51, '西安市街道49号', 1769240786765, 1769240786765),('陈十四839', 'user4622@outlook.com', '15684452299', 52, '北京市街道60号', 1769240786765, 1769240786765),('李四864', 'user3219@163.com', '18909718093', 18, '杭州市街道65号', 1769240786765, 1769240786765),('孙八762', 'user4273@163.com', '15769398252', 41, '杭州市街道66号', 1769240786765, 1769240786765),('刘十三631', 'user3607@outlook.com', '13988369912', 62, '北京市街道19号', 1769240786765, 1769240786765)
248 Query COMMIT
出现合并后的多值 SQL 语句，说明走了批量插入
- 执行 testJdbcTemplateBatchUpdatePerformance 方法，查看 general_log_file 中的内容如下
260124 15:49:23 258 Query select u1_0.id,u1_0.address,u1_0.age,u1_0.create_time,u1_0.email,u1_0.phone,u1_0.update_time,u1_0.username from users u1_0
258 Query COMMIT
258 Query SET autocommit=1
258 Query SET SESSION TRANSACTION READ WRITE
258 Query SET autocommit=0
258 Query SELECT @@session.transaction_read_only
258 Query SELECT @@session.transaction_read_only
258 Query UPDATE users SET username = '张三373', email = 'user5370@outlook.com', phone = '1739395137', age = 55, address = '上海市街道64号', update_time = 1769240963083 WHERE id = 622;UPDATE users SET username = '张三292', email = 'user6188@163.com', phone = '15308494207', age = 35, address = '西安市街道57号', update_time = 1769240963083 WHERE id = 623;UPDATE users SET username = '孙八918', email = 'user1916@126.com', phone = '1997977996', age = 53, address = '上海市街道49号', update_time = 1769240963083 WHERE id = 624;UPDATE users SET username = '钱七839', email = 'user9149@163.com', phone = '15815367279', age = 30, address = '北京市街道21号', update_time = 1769240963083 WHERE id = 625;UPDATE users SET username = '陈十四380', email = 'user4306@gmail.com', phone = '15359341832', age = 28, address = '武汉市街道74号', update_time = 1769240963083 WHERE id = 626;UPDATE users SET username = '王五78', email = 'user6977@gmail.com', phone = '17508819348', age = 39, address = '杭州市街道32号', update_time = 1769240963083 WHERE id = 627;UPDATE users SET username = '李四179', email = 'user9010@outlook.com', phone = '15171774441', age = 33, address = '成都市街道47号', update_time = 1769240963083 WHERE id = 628;UPDATE users SET username = '黄十六774', email = 'user2880@qq.com', phone = '13963502413', age = 18, address = '北京市街道57号', update_time = 1769240963083 WHERE id = 629;UPDATE users SET username = '赵六471', email = 'user9250@163.com', phone = '13588324205', age = 34, address = '深圳市街道93号', update_time = 1769240963083 WHERE id = 630;UPDATE users SET username = '赵十七140', email = 'user5127@gmail.com', phone = '17917788775', age = 64, address = '成都市街道44号', update_time = 1769240963083 WHERE id = 631
258 Query COMMIT
UPDATE 语句一次性发给了 MySQL，说明走了批量更新，减少的是网络 IO 时间
- 主键生成策略设置为
GenerationType.SEQUENCE
@Entity
@Table(name = "users")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Long id;
}
- 运行 testJdbcTemplateBatchInsertPerformance 和 testJdbcTemplateBatchUpdatePerformance 方法，所得到的 general_log_file 中的内容与
GenerationType.IDENTITY一致，批量插入与批量更新均生效
结论
- JdbcTemplate 不受主键生成策略影响
JDBC 驱动
本节介绍 JDBC 驱动中关于批量操作的一些参数
rewriteBatchedStatements
rewriteBatchedStatements 是 MySQL Connector/J（即 MySQL 的 JDBC 驱动）中的一个连接参数，用于优化批量插入或更新操作的性能。当启用 rewriteBatchedStatements=true 时，JDBC 驱动会尝试将多个单条的 INSERT 或 UPDATE 语句重写为一条包含多值（multi-value）的 SQL 语句，从而减少与数据库服务器之间的网络往返次数，提升批量操作的执行效率
-- 原始SQL语句
INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
-- 优化后的SQL语句
INSERT INTO users (name, age) VALUES ('Alice', 25), ('Bob', 30)
测试
先前的小节已经测试了 rewriteBatchedStatements=true 的场景，本小节就只测试未配置 rewriteBatchedStatements 的场景
- 不设置 rewriteBatchedStatements 参数，其余代码均按照支持批量插入和批量更新的要求进行配置
spring:
application:
name: spring-boot-jpa-batch-demo
datasource:
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: 123456
jpa:
hibernate:
ddl-auto: none
show-sql: false
properties:
hibernate:
dialect: org.hibernate.dialect.MySQLDialect
format_sql: true
jdbc:
batch_size: 50
batch_versioned_data: true
order_inserts: true
order_updates: true
-
运行 testJdbcTemplateBatchInsertPerformance、testHibernateBatchInsertPerformance、testJdbcTemplateBatchUpdatePerformance、testHibernateBatchUpdatePerformance 方法，查看 general_log_file 中的内容，可以发现，无论是 Hibernate 还是 JdbcTemplate，它们的批量插入、批量更新都失效了
如果再关注下 DELETE 语句，就会发现 Hibernate 的批量删除（CrudRepository#deleteAll）也失效了
结论
- 不设置 rewriteBatchedStatements 参数时，批量插入和批量更新均失效
Hibernate VS JdbcTemplate
运行 testComparativePerformance 和 testUpdateComparativePerformance 方法比对二者的批处理效率
========== JdbcTemplate vs Hibernate 批量新增性能对比 ==========
数据量 JdbcTemplate(ms) Hibernate(ms) 差异(ms)
======================================================================
2000 157 338 -181
5000 107 702 -595
10000 218 1091 -873
========== JdbcTemplate vs Hibernate 批量更新性能对比 ==========
数据量 JdbcTemplate(ms) Hibernate(ms) 差异(ms)
======================================================================
2000 230 1199 -969
5000 389 1712 -1323
10000 817 2118 -1301
JdbcTemplate 更快，但请注意为了让 Hibernate 的批量插入生效，使用的是 GenerationType.SEQUENCE 主键生成策略，这会造成一定的性能损耗（针对于 Hibernate 的批量插入而言）
