MYSQL - 단일 RDB 환경 에서 계층형 엔티티 저장 최적화, 벌크 인서트

계층형 엔티티 저장 시 발생하는 문제: 부모-자식 관계와 쿼리의 비효율성

보통 Java, Spring, JPA를 사용하는 경우, 계층형 엔티티를 저장할 때, 부모 엔티티를 JpaRepository로 저장하고 반환된 ID 값을 자식 엔티티에 할당하여 저장하거나, **@ManyToOne**이나 @OneToMany 관계를 이용해 자동 저장하는 방식이 일반적이다.

그런데, 나는 이 방식이 정말 맘에 안 들었다. 왜냐면 부모 엔티티의 수만큼 쿼리가 나가기 때문이다. 이게 문제다.

문제의 핵심: 부모-자식 관계 저장 시 발생하는 비효율성

1. **JpaRepository**를 이용하면, 부모 엔티티를 저장하고 ID 값을 반환받아 자식 엔티티에 ID를 할당해서 저장하는 방식인데, 이 과정에서 매번 부모 ID를 가져오는 쿼리가 나가게 된다. 부모 엔티티의 수만큼 쿼리가 나가는 방식이 너무 비효율적이지 않나?
2. **batchUpdate**를 쓰면 2단계 관계형 엔티티일 경우 쿼리 2~3번이면 끝나겠지만, 부모 ID 값을 알 방법이 없어서 자식 엔티티에 부모 ID를 할당하기가 힘들다. 그래서 결국 JPA를 사용하자니 saveAll() 메서드를 써도 매번 쿼리가 나간다는 문제로 속도가 느려지고, **batchUpdate**는 ID를 알 방법이 없다는 벽에 부딪혀서 해결이 안 된다.
3. 게다가, 2단 관계가 아니라 3단 관계가 되면, 쿼리 수가 기하급수적으로 늘어난다. 부모 -> 자식 -> 자식 자식 이렇게 되면, 쿼리 수가 늘어나서 성능이 급격히 떨어질 수밖에 없다.

해결책을 찾아서: BULK 처리로 성능 최적화

그래서 BULK 처리를 통한 쓰기 성능 최적화에 집중해서 개선해보자고 생각했다. 

 

BULK 처리 Write에 집중해서 개선해보기

 

서칭을 하던 중 마켓컬리에 나와 비슷한 고민의 글을 발견했다.

 

해당 포스팅을 읽어보면 내가 원했던 내용들이긴 한데 댓글들처럼 뭔가 글을 믿기에는 정보가 부족하기도 했고 실제 직접 테스트를 해보지 않았으니 무턱대고 대용량 데이터를 인서트 했다가 꼬이면... 나만 힘들어질테니 
내가 직접 MySQL 공식 문서와 코드로 돌려보면서 테스트해보기로 했다.

MySQL :: MySQL 8.4 Reference Manual :: 14.15 Information Functions

 

MySQL :: MySQL 8.4 Reference Manual :: 17.6.1.6 AUTO_INCREMENT Handling in InnoDB

문제 해결을 위한 두 가지 핵심 요소: LAST_INSERT_ID()와 AUTO_INCREMENT LOCK

 

위에서 말한 LAST_INSERT_ID() 메서드와 기본 PK 생성 전략인 AUTO_INCREMENT LOCK에 대한 내용을 MySQL 8.4 기준으로 살펴보자.

 

먼저 LAST_INSERT_ID() 함수에 대해 설명하자면, 세션 기반으로 가장 최근에 삽입된 AUTO_INCREMENT 값을 반환한다.

이 말은, 각 세션에서 자신이 실행한 가장 최근의 ID 값을 반환해준다는 뜻이다.

 

즉, 다른 클라이언트나 세션에서 AUTO_INCREMENT 값을 생성해도, 자신에게는 영향을 미치지 않는다는 거다.

이게 바로 **LAST_INSERT_ID()**의 핵심 특징 중 하나다.

아래의 내용은 공식 문서에서 LAST_INSERT_ID()의 설명에 대해 발췌한 부분이다.

"The ID that was generated is maintained in the server on a per-connection basis."
This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

...

"If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only."

..

 

이 문장에서 중요한 포인트는 **"세션별로 관리된다"**는 것이다.

 

즉, 각 클라이언트는 자신이 실행한 가장 최근의 AUTO_INCREMENT 값을 안전하게 반환받을 수 있다는 의미이다.
이 특성 덕분에, 다른 클라이언트가 생성한 ID 값은 자기 세션의 LAST_INSERT_ID() 값에 영향을 미치지 않으며, 각 세션은 독립적으로 ID 값을 추적할 수 있다.

 

LAST_INSERT_ID() 함수는 여러 개의 INSERT문을 배치로 실행하더라도, 가장 처음 삽입된 ID 값을 반환한다.

즉, 여러 ID를 삽입할 경우, LAST_INSERT_ID()는 가장 첫 번째로 삽입된 ID 값만을 반환하고,
그 이후 ID는 자동으로 순차적으로 할당된다.

 

이제 **LAST_INSERT_ID()**가 어떻게 세션별로 동작하는지 이해했다면, 그 값이 정확하고 안전하게 할당되는 방식도 중요하다.

 

MySQL은 AUTO_INCREMENT 값을 생성할 때, 테이블 레벨에서 락을 걸어 한 번에 하나의 INSERT만 처리되도록 보장한다.

InnoDB에서는 AUTO_INCREMENT 값을 생성할 때, 테이블 레벨 락을 사용하여, 한 번에 여러 INSERT가 동시에 실행되는 상황에서도 AUTO_INCREMENT 값이 겹치지 않도록 한다.

 

이 방식은 스테이트먼트가 실행되는 동안 해당 범위의 AUTO_INCREMENT 값을 미리 선점하고, 다른 INSERT 쿼리와의 충돌을 방지하는 방식이다.

 

아래의 내용은 공식 문서에서 AUTO_INCREMENT LOCK의 설명에 대해 발췌한 부분이다.

"InnoDB cannot tell in advance how many rows are retrieved from the SELECT in the INSERT statement in Tx1, and it assigns the auto-increment values one at a time as the statement proceeds. With a table-level lock, held to the end of the statement, only one INSERT statement referring to table t1 can execute at a time, and the generation of auto-increment numbers by different statements is not interleaved."

 

이 부분에서 중요한 점은,

테이블 레벨 락을 사용해 AUTO_INCREMENT 값을 미리 선점함으로써, 여러 INSERT 쿼리가 동시에 실행되더라도 충돌 없이 각 쿼리가 안전하게 실행될 수 있도록 보장한다는 것이다.

 

따라서, AUTO_INCREMENT LOCK은 테이블에 대한 락을 거는 방식으로,

동시에 실행되는 INSERT들이 AUTO_INCREMENT 값을 겹치지 않도록 처리하며, 각각의 INSERT가 정확한 값을 받을 수 있게 한다.

 

그러니까 pk 전략을 auto_increment로 하고 auto_increment lock을 디폴트 값으로 설정만 하면

스테이트 먼트 단위로 이 스테이트먼트에 저장될 row수 만큼 뮤텍스 락을 걸어서 아이디를 미리 선점한다는 것이다.

그렇게 하기위해
hikaricp 설정에 rewriteBatchedStatements=true 설정을 반드시 활성화해야 한다.

그래야 우리가 jdbc로 만드는 스테이트먼트에 insert가 한줄로 나가기 때문이다 

 

이 두개의 메커니즘에 대해 이해했으면 last_insert_id로 어떻게 batchUpdate된 부모 엔티티들의 Pk값을 구할 수 있는지 알아보자.

아래 코드를  살펴보자

@Repository
public class ProductGroupJdbcRepository implements ProductGroupPersistenceRepository{

    private final JdbcTemplate jdbcTemplate;
    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public ProductGroupJdbcRepository(JdbcTemplate jdbcTemplate, NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }

    @Transactional
    @Override
    public List<Long> saveAll(List<ProductGroupEntity> productGroupEntities) {
        int[] insertCounts = batchInsertProductGroups(productGroupEntities);

        int numberOfInsertedRows = insertCounts.length;
        List<Long> insertedIds = getInsertedIds(numberOfInsertedRows);
        return insertedIds;
    }




    private int[] batchInsertProductGroups(List<ProductGroupEntity> productGroupEntities) {
        String sql = "INSERT INTO PRODUCT_GROUP_TEST " +
                "(PRODUCT_GROUP_NAME) " +
                "VALUES (:product_group_name)";

        List<Map<String, Object>> batchValues = productGroupEntities.stream()
                .map(productGroup -> {
                    MapSqlParameterSource params = new MapSqlParameterSource()
                            .addValue("product_group_name", productGroup.getProductGroupName());
                    return params.getValues();
                })
                .toList();

        return namedParameterJdbcTemplate.batchUpdate(sql, batchValues.toArray(new Map[0]));
    }

    private List<Long> getInsertedIds(int numberOfRows) {
        long firstInsertedId = jdbcTemplate.queryForObject("SELECT LAST_INSERT_ID()", Long.class);

        return LongStream.range(firstInsertedId, firstInsertedId + numberOfRows)
                .boxed()
                .toList();
    }

}

 

batchInsertProductGroups 실행

배치 삽입을 통해 여러 개의 INSERT문을 실행하는 경우, LAST_INSERT_ID()는 첫 번째 INSERT 문에서 할당된 AUTO_INCREMENT 값을 반환한다.

그 후, getInsertedIds() 메서드는 LAST_INSERT_ID()를 기준으로 후속 ID들을 순차적으로 계산하여 반환한다.

 

예를 들어, batchInsertProductGroups 메서드를 통해 여러 ProductGroupEntity를 삽입하면, LAST_INSERT_ID()는 첫 번째 ID를 반환하고, 그 이후의 ID는 첫 번째 ID에서 시작해 numberOfRows만큼 증가하게 되는것이다.

 

 

해당 내용에 대한 테스트 관련 코드들을 직접 깃허브에 작성해 두었다.

GitHub - ryu-qqq/mysql-bulk-insert

 

 

이를 통해 단일 RDB(MYSQL)에서 키 전략이 AUTO_INCREMENT이며 AUTO_INCREMENT LOCK이 디폴트인 경우

계층형 엔티티들을 저장할때 성능을 최대한 끌어올리는 방법에 대해 공식문서를 읽어보며 테스트 코드를 작성해 확인해보았다.