BackEnd๐ŸŒฑ/Spring

Spring JDBC๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ Batch Insert ์ˆ˜ํ–‰ํ•˜๊ธฐ

dkswnkk 2023. 1. 9. 23:07

์„œ๋ก 

ํ‰์†Œ์— JPA๋ฅผ ํ™œ์šฉํ•˜๋ฉด์„œ ๋ฐ์ดํ„ฐ๋ฅผ ์ถ”๊ฐ€ํ•  ๋•Œ, ๋งŽ์€ ๊ฐœ๋ฐœ์ž๋“ค์ด repository.save() ํ•จ์ˆ˜๋ฅผ ์ด์šฉํ•˜์—ฌ ๋‹จ๊ฑด ๋‹จ์œ„๋กœ ์ €์žฅํ•˜๋Š” ๋กœ์ง์„ ๊ตฌํ˜„ํ•ฉ๋‹ˆ๋‹ค. ๊ทธ๋Ÿฌ๋‚˜ ์ˆ˜๋ฐฑ, ์ˆ˜์ฒœ ๊ฐœ ์ด์ƒ์˜ ๋Œ€๋Ÿ‰ ๋ฐ์ดํ„ฐ๋ฅผ ์ €์žฅํ•ด์•ผ ํ•˜๋Š” ๊ฒฝ์šฐ์—๋„ ํ•ด๋‹น ๋ฐฉ๋ฒ•์„ ์‚ฌ์šฉํ•ด๋„ ๋ ๊นŒ์š”? ์ด๋Š” ์„ฑ๋Šฅ๊ณผ ํด๋ผ์ด์–ธํŠธ์˜ ๋Œ€๊ธฐ ์‹œ๊ฐ„์„ ๊ฒฐ์ •ํ•˜๋Š” ์ค‘์š”ํ•œ ์š”์†Œ์ž…๋‹ˆ๋‹ค.

๋”ฐ๋ผ์„œ ์ด๋ฒˆ ๊ธ€์—์„œ๋Š” Spring ํ™˜๊ฒฝ์—์„œ ๋‹ค๋Ÿ‰์˜ ๋ฐ์ดํ„ฐ๋ฅผ ํšจ์œจ์ ์œผ๋กœ ์‚ฝ์ž…ํ•˜๋Š” ๋ฐฉ๋ฒ•์ธ Batch Insert์— ๋Œ€ํ•ด ์•Œ์•„๋ณด๋ ค ํ•ฉ๋‹ˆ๋‹ค.

ํ…Œ์ŠคํŠธ๋Š” Apple Silicon (M1), Java 11, Spring Boot, JPA, JUnit5, Docker MySQL ํ™˜๊ฒฝ์—์„œ ์ง„ํ–‰ํ•˜์˜€์œผ๋ฉฐ, ์†Œ์Šค์ฝ”๋“œ๋Š” ๊นƒํ—ˆ๋ธŒ์—์„œ ํ™•์ธ ๊ฐ€๋Šฅํ•ฉ๋‹ˆ๋‹ค.

๋ชฉ์ฐจ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์Šต๋‹ˆ๋‹ค.

  1. Batch Insert๋ž€?
  2. Identity ์ „๋žต์œผ๋กœ๋Š” Batch Insert๊ฐ€ ๋ถˆ๊ฐ€๋Šฅํ•œ ์ด์œ 
  3. JdbcTemplate๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ Batch Insert ์ ์šฉํ•˜๊ธฐ
  4. ์„ฑ๋Šฅ ๋น„๊ต

 

 

Batch Insert๋ž€?

Batch Insert๋Š” ๋งŽ์€ ์–‘์˜ ๋ฐ์ดํ„ฐ๋ฅผ ํ•œ ๋ฒˆ์— ์‚ฝ์ž…ํ•˜๋Š” ๋ฐฉ๋ฒ•์ž…๋‹ˆ๋‹ค. ์•„๋ž˜์˜ ์ผ๋ฐ˜์ ์ธ Insert SQL๊ณผ ๋น„๊ตํ•ด ๋ณด๋ฉด ์ดํ•ดํ•˜๊ธฐ ์‰ฝ์Šต๋‹ˆ๋‹ค.

INSERT INTO table (col1, col2) VALUES (val1, val11);
INSERT INTO table (col1, col2) VALUES (val2, val22);
INSERT INTO table (col1, col2) VALUES (val3, val33);

์œ„ ์ฟผ๋ฆฌ๋Š” ๊ฐœ๋ณ„ Insert์ž…๋‹ˆ๋‹ค.

INSERT INTO table (col1, col2) VALUES
(val1, val11),
(val2, val22),
(val3, val33);

์œ„ ์ฟผ๋ฆฌ๋Š” Batch Insert์ž…๋‹ˆ๋‹ค. 

๋ณดํ†ต ์ฟผ๋ฆฌ๋ฅผ ์‹คํ–‰ํ•˜๊ณ  ์‘๋‹ต์„ ๋ฐ›์€ ํ›„์—์•ผ ๋‹ค์Œ ์ฟผ๋ฆฌ๋ฅผ ์ „๋‹ฌํ•˜๊ธฐ ๋•Œ๋ฌธ์— ๊ฐœ๋ณ„ Insert์˜ ๊ฒฝ์šฐ ์ง€์—ฐ ์‹œ๊ฐ„์ด ๋Š˜์–ด๋‚˜์ง€๋งŒ, ํ•˜๋‚˜์˜ ํŠธ๋žœ์žญ์…˜์œผ๋กœ ๋ฌถ์ด๋Š” Batch Insert๋Š” ํ•˜๋‚˜์˜ ์ฟผ๋ฆฌ๋ฌธ์œผ๋กœ ์—ฌ๋Ÿฌ ๋ฐ์ดํ„ฐ๋ฅผ ์ฒ˜๋ฆฌํ•˜๊ธฐ ๋•Œ๋ฌธ์— ์„ฑ๋Šฅ์ด ๋›ฐ์–ด๋‚ฉ๋‹ˆ๋‹ค.

 

 

Identity ์ „๋žต์œผ๋กœ๋Š” Batch Insert๊ฐ€ ๋ถˆ๊ฐ€๋Šฅํ•œ ์ด์œ 

@Entity
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class Example {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

}

JPA์™€ MySQL์„ ํ•จ๊ป˜ ์‚ฌ์šฉํ•  ๋•Œ, ์œ„์™€ ๊ฐ™์ด IDENTITY ์ „๋žต์„ ์‚ฌ์šฉํ•˜์—ฌ auto_increment๋ฅผ ํ†ตํ•ด PK ๊ฐ’์„ ์ž๋™์œผ๋กœ ์ฆ๊ฐ€์‹œํ‚ค๋Š” ๋ฐฉ์‹์„ ์ผ๋ฐ˜์ ์œผ๋กœ ์‚ฌ์šฉํ•˜๋ฉฐ. ์ด๋•Œ ID๋Š” @GeneratedValue(strategy = GenerationType.IDENTITY)๋กœ ์„ค์ •ํ•˜๊ณ  ์•„๋ž˜์™€ ๊ฐ™์ด save() ๋ฉ”์„œ๋“œ๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์ €์žฅํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

Product product = new Product(title, price);
productRepository.save(product);

์ด ๋ฐฉ์‹์€ Spring Data JPA์—์„œ ์ œ๊ณตํ•˜๋Š” JpaRepository.save(T) ๋ฉ”์„œ๋“œ์˜ ๋‚ด๋ถ€ ๋™์ž‘ ๋ฐฉ์‹์œผ๋กœ, ID๊ฐ’์„ ๋ช…์‹œํ•˜์ง€ ์•Š์•„๋„ ์ž๋™์œผ๋กœ ์ €์žฅ๋ฉ๋‹ˆ๋‹ค.

Hibernate๋Š” JDBC ์ˆ˜์ค€์—์„œ batch insert๋ฅผ ๋น„ํ™œ์„ฑํ™” ํ•œ๋‹ค

๊ทธ๋Ÿฌ๋‚˜ ์ด ๋ฐฉ์‹์„ ์‚ฌ์šฉํ•˜๋ฉด Hibernate๋Š” JDBC ์ˆ˜์ค€์—์„œ Batch Insert๋ฅผ ๋น„ํ™œ์„ฑํ™”ํ•ฉ๋‹ˆ๋‹ค(์ฐธ๊ณ ). ์ด์œ ๋Š” ์ƒˆ๋กœ ํ• ๋‹นํ•  Key ๊ฐ’์„ ๋ฏธ๋ฆฌ ์•Œ ์ˆ˜ ์—†๋Š” IDENTITY ์ „๋žต์„ ์‚ฌ์šฉํ•  ๊ฒฝ์šฐ, Hibernate๊ฐ€ ์ฑ„ํƒํ•œ flush ๋ฐฉ์‹์ธ 'Transactional Write Behind'์™€ ์ถฉ๋Œ์ด ๋ฐœ์ƒํ•˜๊ธฐ ๋•Œ๋ฌธ์ž…๋‹ˆ๋‹ค. ๋”ฐ๋ผ์„œ IDENTITY ์ „๋žต์„ ์‚ฌ์šฉํ•˜๋ฉด Batch Insert๋Š” ๋™์ž‘ํ•˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.

์ด๋ฅผ ๊ตฌ์ฒด์ ์ธ ์˜ˆ๋กœ ์„ค๋ช…ํ•˜๋ฉด, OneToMany์˜ Entity๋ฅผ insertํ•  ๊ฒฝ์šฐ Hibernate๋Š” ์•„๋ž˜ ๊ณผ์ •์„ ์ง„ํ–‰ํ•˜๋ฉฐ ์ด ๊ณผ์ •์˜ ์ฟผ๋ฆฌ๋ฅผ ๋ชจ์•„์„œ ์‹คํ–‰ํ•ฉ๋‹ˆ๋‹ค.

  1. ๋ถ€๋ชจ Entity๋ฅผ insertํ•˜๊ณ  ์ƒ์„ฑ๋œ Id๋ฅผ ๋ฐ˜ํ™˜
  2. ์ž์‹ Entity์—์„œ๋Š” ์ด์ „์— ์ƒ์„ฑ๋œ ๋ถ€๋ชจ Id๋ฅผ FK ๊ฐ’์œผ๋กœ ์ฑ„์›Œ์„œ insert

ํ•˜์ง€๋งŒ Batch Insert์™€ ๊ฐ™์€ ๋Œ€๋Ÿ‰ ๋“ฑ๋ก์˜ ๊ฒฝ์šฐ, ์ด ๋ฐฉ์‹์„ ์‚ฌ์šฉํ•  ์ˆ˜ ์—†๋Š”๋ฐ ๋ถ€๋ชจ Entity๋ฅผ ํ•œ ๋ฒˆ์— ๋Œ€๋Ÿ‰์œผ๋กœ ๋“ฑ๋กํ•˜๊ฒŒ ๋˜๋ฉด ์–ด๋Š ์ž์‹ Entity๊ฐ€ ์–ด๋Š ๋ถ€๋ชจ Entity์— ๋งคํ•‘๋˜์–ด์•ผ ํ•˜๋Š”์ง€ ์•Œ ์ˆ˜ ์—†์Šต๋‹ˆ๋‹ค. ๋”ฐ๋ผ์„œ IDENTITY ์ „๋žต์„ ์‚ฌ์šฉํ•˜๋ฉด Batch Insert๋Š” ๋™์ž‘ํ•˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.

๋ฌผ๋ก  Auto Increment๊ฐ€ ์•„๋‹ ๊ฒฝ์šฐ์—” ์•„๋ž˜์™€ ๊ฐ™์€ ์˜ต์…˜์„ ํ†ตํ•ด values ์‚ฌ์ด์ฆˆ๋ฅผ ์กฐ์ ˆํ•˜์—ฌ Batch Insert๋ฅผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

spring.jpa.properties.hibernate.jdbc.batch_size=๊ฐœ์ˆ˜

 

 

JdbcTemplate๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ Batch Insert ์ ์šฉํ•˜๊ธฐ

ํ…Œ์ด๋ธ” ์ „๋žต์„ ๋ณ€๊ฒฝํ•˜๋Š” ๋ฐฉ๋ฒ•์ด ์žˆ์ง€๋งŒ, ์ด๋Š” ํ…Œ์ด๋ธ” ๋ณ€๊ฒฝ์ด ํ•„์š”ํ•˜๊ณ  ์ด๋ฏธ ์ง„ํ–‰ ์ค‘์ธ ํ”„๋กœ์ ํŠธ์— ์ ์šฉํ•˜๊ธฐ ์–ด๋ ต์Šต๋‹ˆ๋‹ค. ๊ทธ๋ž˜์„œ ๋Œ€์•ˆ์œผ๋กœ JdbcTemplate๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ Batch Insert๋ฅผ ์ ์šฉํ•˜๋Š” ๋ฐฉ์•ˆ์„ ์„ค๋ช…ํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค.

JdbcTemplate์—๋Š” Batch๋ฅผ ์ง€์›ํ•˜๋Š” batchUpdate() ๋ฉ”์„œ๋“œ๊ฐ€ ์žˆ์Šต๋‹ˆ๋‹ค. ๋จผ์ € MySQL์—์„œ Bulk Insert๋ฅผ ์‚ฌ์šฉํ•˜๋ ค๋ฉด, DB-URL์— 'rewriteBatchedStatements=true' ํŒŒ๋ผ๋ฏธํ„ฐ๋ฅผ ์ถ”๊ฐ€ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/batch_test?&rewriteBatchedStatements=true
    username: root
    password: 1234
    driver-class-name: com.mysql.cj.jdbc.Driver

'rewriteBatchedStatements'๋ฅผ true๋กœ ์„ค์ •ํ•˜์ง€ ์•Š์œผ๋ฉด Insert ์ฟผ๋ฆฌ๊ฐ€ ์—ฌ์ „ํžˆ ๋‹จ๊ฑด์œผ๋กœ ์ˆ˜ํ–‰๋ฉ๋‹ˆ๋‹ค.

Batch Insert๊ฐ€ ์ œ๋Œ€๋กœ ์ง„ํ–‰๋˜๋Š”์ง€ ํ™•์ธํ•˜๋ ค๋ฉด ๋‹ค์Œ๊ณผ ๊ฐ™์€ ์ถ”๊ฐ€ ์˜ต์…˜์„ ์„ค์ •ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

spring:
    datasource:
        url: jdbc:mysql://localhost:3306/db๋ช…?rewriteBatchedStatements=true&profileSQL=true&logger=Slf4JLogger&maxQuerySizeToLog=999999

๊ฐ ํŒŒ๋ผ๋ฏธํ„ฐ์˜ ์„ค๋ช…์€ ๋‹ค์Œ๊ณผ ๊ฐ™์Šต๋‹ˆ๋‹ค.

  • postfileSQL = true : Driver์— ์ „์†กํ•˜๋Š” ์ฟผ๋ฆฌ๋ฅผ ์ถœ๋ ฅํ•ฉ๋‹ˆ๋‹ค.
  • logger=Slf4JLogger : Driver์—์„œ ์ฟผ๋ฆฌ ์ถœ๋ ฅ ์‹œ ์‚ฌ์šฉํ•  ๋กœ๊ฑฐ๋ฅผ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.
    • MySQL ๋“œ๋ผ์ด๋ฒ„ : ๊ธฐ๋ณธ๊ฐ’์€ System.err๋กœ ์ถœ๋ ฅํ•˜๋„๋ก ์„ค์ •๋˜์–ด ์žˆ๊ธฐ ๋•Œ๋ฌธ์— ํ•„์ˆ˜๋กœ ์ง€์ •ํ•ด ์ค˜์•ผ ํ•ฉ๋‹ˆ๋‹ค.
    • MariaDB ๋“œ๋ผ์ด๋ฒ„ : Slf4j ๋ฅผ ์ด์šฉํ•˜์—ฌ ๋กœ๊ทธ๋ฅผ ์ถœ๋ ฅํ•˜๊ธฐ ๋•Œ๋ฌธ์— ์„ค์ •ํ•  ํ•„์š”๊ฐ€ ์—†์Šต๋‹ˆ๋‹ค.
  • maxQuerySizeToLog=999999 : ์ถœ๋ ฅํ•  ์ฟผ๋ฆฌ ๊ธธ์ด
    • MySQL ๋“œ๋ผ์ด๋ฒ„ : ๊ธฐ๋ณธ๊ฐ’์ด 0์œผ๋กœ ์ง€์ •๋˜์–ด ์žˆ์–ด ๊ฐ’์„ ์„ค์ •ํ•˜์ง€ ์•Š์„ ๊ฒฝ์šฐ ์ฟผ๋ฆฌ๊ฐ€ ์ถœ๋ ฅ๋˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.
    • MariaDB ๋“œ๋ผ์ด๋ฒ„ : ๊ธฐ๋ณธ๊ฐ’์ด 1024๋กœ ์ง€์ •๋˜์–ด ์žˆ์Šต๋‹ˆ๋‹ค. MySQL ๋“œ๋ผ์ด๋ฒ„์™€๋Š” ๋‹ฌ๋ฆฌ 0์œผ๋กœ ์ง€์ • ์‹œ ์ฟผ๋ฆฌ์˜ ๊ธ€์ž ์ œํ•œ์ด ๋ฌด์ œํ•œ์œผ๋กœ ์„ค์ •๋ฉ๋‹ˆ๋‹ค.

๋‹ค์Œ์€ Entity์™€ Batch Insert๋ฅผ ์ •์˜ํ•œ Repository ์ฝ”๋“œ์ž…๋‹ˆ๋‹ค. Batch Insert ๊ด€๋ จ ์ฝ”๋“œ๋Š” ๊ณต์‹๋ฌธ์„œ๋ฅผ ์ฐธ๊ณ ํ•˜์—ฌ ์ž‘์„ฑํ•˜์˜€์Šต๋‹ˆ๋‹ค.

์ฝ”๋“œ

@Entity(name = "product")
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;
    private Long price;

    public Product(String title, Long price) {
        this.title = title;
        this.price = price;
    }

}
@Repository
@RequiredArgsConstructor
public class ProductBulkRepository {

    private final JdbcTemplate jdbcTemplate;

    @Transactional
    public void saveAll(List<Product> products) {
        String sql = "INSERT INTO product (title, price) " +
                "VALUES (?, ?)";

        jdbcTemplate.batchUpdate(sql,
                products,
                products.size(),
                (PreparedStatement ps, Product product) -> {
                    ps.setString(1, product.getTitle());
                    ps.setLong(2, product.getPrice());
                });
    }
}

batchUpdate ๋ฉ”์„œ๋“œ์˜ ํŒŒ๋ผ๋ฏธํ„ฐ๋Š” ์ˆœ์„œ๋Œ€๋กœ "sql, batchArgs, batchSize, sql ?์— ๋“ค์–ด๊ฐˆ ๊ฐ’"์ž…๋‹ˆ๋‹ค.

๋˜๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์ด ์ž‘์„ฑํ•  ์ˆ˜๋„ ์žˆ์Šต๋‹ˆ๋‹ค.

@Repository
@RequiredArgsConstructor
public class ProductBulkRepository {

    private final JdbcTemplate jdbcTemplate;

    @Transactional
    public void saveAll(List<Product> products) {
        String sql = "INSERT INTO product (title, price) " +
                "VALUES (?, ?)";

        jdbcTemplate.batchUpdate(sql,
                new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        Product product = products.get(i);
                        ps.setString(1, product.getTitle());
                        ps.setLong(2, product.getPrice());
                    }

                    @Override
                    public int getBatchSize() {
                        return products.size();
                    }
                });
    }

}

 

 

์„ฑ๋Šฅ๋น„๊ต

@SpringBootTest
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
@Transactional
@Rollback(value = false)
class ProductTest {

    private static final int COUNT = 10_000;
    @Autowired
    private ProductRepository productRepository;
    @Autowired
    private ProductBulkRepository productBulkRepository;

    @BeforeAll
    void init() {
        Product product = new Product("์ดˆ๊ธฐ", 10_000L);
        productRepository.save(product);
    }

    @Test
    @DisplayName("normal insert")
    void ์ผ๋ฐ˜_insert() {
        long startTime = System.currentTimeMillis();
        for (long i = 2; i <= COUNT; i++) {
            String title = "์ด๋ฆ„: " + i;
            long price = i + 1L;
            Product product = new Product(title, price);
            productRepository.save(product);
        }
        long endTime = System.currentTimeMillis();
        System.out.println("---------------------------------");
        System.out.printf("์ˆ˜ํ–‰์‹œ๊ฐ„: %d\n", endTime - startTime);
        System.out.println("---------------------------------");
    }

    @Test
    @DisplayName("bulk insert")
    void ๋ฒŒํฌ_insert() {
        long startTime = System.currentTimeMillis();
        List<Product> products = new ArrayList<>();
        for (long i = 0; i < COUNT; i++) {
            String title = "์ด๋ฆ„: " + i;
            long price = i + 1L;
            Product product = new Product(title, price);
            products.add(product);
        }
        productBulkRepository.saveAll(products);
        long endTime = System.currentTimeMillis();
        System.out.println("---------------------------------");
        System.out.printf("์ˆ˜ํ–‰์‹œ๊ฐ„: %d\n", endTime - startTime);
        System.out.println("---------------------------------");
    }

}

๊ฐ„๋‹จํ•˜๊ฒŒ ์œ„์™€ ๊ฐ™์ด ํ…Œ์ŠคํŠธ์ฝ”๋“œ๋ฅผ ์ž‘์„ฑํ•˜์—ฌ ์ˆ˜ํ–‰์‹œ๊ฐ„์„ ๋น„๊ตํ–ˆ์Šต๋‹ˆ๋‹ค.

๋‹จ๊ฑด Insert ์ˆ˜ํ–‰ ์‹œ๊ฐ„
Batch Insert ์ˆ˜ํ–‰ ์‹œ๊ฐ„

๋‹จ์ˆœํ•œ 10,000๊ฑด์„ ์‚ฝ์ž…ํ•˜๋Š”๋ฐ ์œ„์™€ ๊ฐ™์€ ์ˆ˜ํ–‰์†๋„์˜ ์ฐจ์ด๊ฐ€ ๋‚˜์™”์Šต๋‹ˆ๋‹ค. ๋‹จ์ˆœํ•œ ๋ฐ์ดํ„ฐ์—๋„ ์—„์ฒญ๋‚œ ์ฐจ์ด๋ฅผ ๋ณด์—ฌ์ฃผ๋Š”๋ฐ ๋ฐ์ดํ„ฐ๊ฐ€ ๋” ๋งŽ๊ณ , ๋ณต์žกํ•˜๋‹ค๋ฉด Batch Insert๋ฅผ ํ•„์ˆ˜๋กœ ์‚ฌ์šฉํ•˜์—ฌ์•ผ ํ•œ๋‹ค๊ณ  ์ƒ๊ฐํ•ฉ๋‹ˆ๋‹ค.