데이터베이스에 한 번에 많은 데이터 써야 한다면 어떻게 해야할까?
왜 이런 작업이 필요로 하는가?
아마 주변에서 제일 간단하게 찾을 수 있는 상황은 엑셀 파일 업로드가 있을 수 있다. 많게는 수 만건, 수 십만건까지 데이터베이스에 삽입(insert)를 하는 것인데 열당 삽입하게 된다면 굉장히 오랜 작업이 될 수 있다.
BatchInsert
이를 보통 BatchInsert 또는 BulkInsert 라고 말하는데, 여러 Insert 구문을 하나로 하나의 Insert 구문으로 작업하도록 하는 것을 의미한다. 그와 더불어 속도도 빠르다.
예를 들면, 다음 쿼리들이
1
2
3
4
5
6
7
| INSERT INTO message (`content`, `status`, `created_by`, `created_at`,`last_modified_at`)
VALUES (:content, :status, :created_by, :created_at, :last_modified_at);
INSERT INTO message (`content`, `status`, `created_by`, `created_at`,`last_modified_at`)
VALUES (:content, :status, :created_by, :created_at, :last_modified_at);
INSERT INTO message (`content`, `status`, `created_by`, `created_at`,`last_modified_at`)
VALUES (:content, :status, :created_by, :created_at, :last_modified_at);
// ...
|
아래의 쿼리로 대체될 수 있다.
1
2
3
4
5
| INSERT INTO message (`content`, `status`, `created_by`, `created_at`,`last_modified_at`)
VALUES (:content, :status, :created_by, :created_at, :last_modified_at)
, (:content, :status, :created_by, :created_at, :last_modified_at)
, (:content, :status, :created_by, :created_at, :last_modified_at)
, ...;
|
예제
이전에 MySQL - 큰 테이블을 다루는 jdbc 활용법 ①를 분석하면서 작성했던 간단한 예제이다. 내용은 10만건의 데이터를 100개씩 batchInsert 하는 코드 이다.
spring.properties
MySQL Driver의 경우, rewriteBatchedStatements=true 옵션을 추가해야 한다.
1
2
| spring.datasource.url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
|
Application.java
ApplicationRunner는 애플리케이션 로딩 시에 최초 작업을 정의하는 인터페이스이다. 중점적으로 봐야한 곳은 jdbcTemplate.batchUpdate(...) 부분이다. 내부적으로 batchList를 batchSize만큼 처리해주고 있다. (참고)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
| private static final String INSERT_SQL = "INSERT INTO push_message (`content`, `status`, `created_by`, `created_at`, `last_modified_at`) VALUES (?, ?, ?, ?, ?)";
@Autowired
private JdbcTemplate jdbcTemplate;
@Bean
public ApplicationRunner runner() {
return args -> {
int insertCount = 100_000;
int batchSize = 100;
List<PushMessage> batchList = IntStream.range(0, insertCount)
.mapToObj(i -> new PushMessage("content" + i, "wait", "heowc", LocalDateTime.now(), LocalDateTime.now()))
.collect(Collectors.toList());
StopWatch stopWatch = new StopWatch();
stopWatch.start();
jdbcTemplate.batchUpdate(INSERT_SQL, batchList, batchSize, (ps, arg) -> {
ps.setString(1, arg.getContent());
ps.setString(2, arg.getStatus());
ps.setString(3, arg.getCreatedBy());
ps.setTimestamp(4, Timestamp.valueOf(arg.getCreatedAt()));
ps.setTimestamp(5, Timestamp.valueOf(arg.getLastModifiedAt()));
});
stopWatch.stop();
System.out.println(stopWatch.prettyPrint());
};
}
|
소요시간 측정
| rewriteBatchedStatements |
batch size |
time(ms) |
| O |
200 |
2500ms |
| O |
100 |
5000ms |
| X |
x |
200000ms |
역시나 하드웨어 사양, 환경마다 다르겠지만 rewriteBatchedStatements 여부에 따라 batch size에 따라 속도 차이가 많이 났다.
이것도 역시 패킷을 분석해보면, Length가 16388인 패킷을 볼 수 있는데 net_buffer_length의 기본값(16384)과 관련이 있다. 이를 적절히 늘리는 것도 속도 향상에 도움이 될 수 있다(?)
참고
