[Bulk Insert/Update] Mybatis 배치작업과 리턴값(returnValue)

이상현·2023년 3월 21일
BatchResultMyBatisSpringbootbulkexecutorType.batchforeachgetUpdateCounts()
2

차곡차곡

목록 보기
7/8
post-thumbnail
post-custom-banner

들어가며,

주문/재고 관리시스템에서 배치작업을 개발/개선하는 업무를 맡게되었습니다.
배치작업 특성상 많은 데이터를 한번에 처리하면서 겪게되었던 경험을 정리해봅니다.

글의 순서

  • 다건의 데이터를 DB에 반영하기 위해, Bulk 쿼리 적용
    • Mybatis <foreach> 사용
    • SqlSession ExcecuteType.BATCH 사용
    • 각 방식에 따른 단순 성능비교
    • Update를 위한 방식
  • Bulk 쿼리에서의 Return 값에 대해
  • Bulk Insert/Update 방법 비교

개발환경

  • SpringBoot
  • MyBatis

다건의 데이터를 DB에 반영하기 위해, Bulk 쿼리 적용

단건 쿼리로 배치작업을 하고있던 기존 시스템에서,
Bulk 쿼리를 적용하여 성능을 개선.

기존 시스템을 분석하다보니, 배치작업에 실행되는 쿼리가 비효율적으로 동작하고 있는 것을 발견하였습니다.

배치가 돌면서 다량의 데이터가 Insert 또는 Update 되는 로직이 존재하는데
이때, 각각의 데이터가 매번 단건으로 DB에 반영이 되고 있었습니다.

<!-- 데이터를 한 건씩 Insert 하는 쿼리 -->
<insert id="insertItem" parameterType="com.example.batchjob.dto.ItemDto">
    INSERT INTO item_list
    VALUES ( #{itemCode}, #{name}, #{kindCode}, #{price})
</insert>
@Test
@DisplayName("반복문을 이용한 다건 Insert")
@Transactional
void BulkInsertUsingLoop() {

    for (ItemDto itemDto : inputDataList) {
        itemRepository.insertItem(itemDto);
    }
}

실행되는 로그를 확인해보면, 각각의 데이터가 단 건씩 입력되는 것을 확인할 수 있습니다.

INSERT INTO item_list
        VALUES ( '0', '0', '0', 0)
 {executed in 6 msec}
INSERT INTO item_list
        VALUES ( '1', '1', '1', 1)
 {executed in 5 msec}
INSERT INTO item_list
        VALUES ( '2', '2', '2', 2)
 {executed in 4 msec}
INSERT INTO item_list
        VALUES ( '3', '3', '3', 3)
 {executed in 4 msec}
INSERT INTO item_list
        VALUES ( '4', '4', '4', 4)
 {executed in 4 msec}

DB에 반영하는 매 건 마다, 커넥션을 가져오고 Commit을 하는 과정에서 비용이 발생하여 성능적으로 문제가 발생합니다.

배치작업에서는 시스템의 크기에 따라 반영해야하는 데이터의 건 수가 많아질 수 있기 때문에, 개선을 위해 Bulk 쿼리를 적용하였습니다.

MyBatis <foreach> 태그를 이용한 방법

MyBatis의 <foreach> 태그를 이용하면 여러건을 Insert 하는 쿼리를 실행할 수 있습니다.

<!-- foreach를 이용한 다건의 데이터 Insert -->
<insert id="insertItemList" parameterType="com.example.batchjob.dto.ItemDto">
    INSERT INTO item_list
    VALUES
    <foreach collection="list" item="item" open="(" close=")" separator="), (">
        #{item.itemCode}, #{item.name}, #{item.kindCode}, #{item.price}
    </foreach>
</insert>

위의 쿼리는 하나의 Insert문으로 다 건의 데이터를 입력할 수 있도록 합니다.

INSERT INTO item_list
    VALUES
        ( '0', '0', '0', 0 )
        , ( '1', '1', '1', 1)
        , ( '2', '2', '2', 2)
        , ( '3', '3', '3', 3)
        , ( '4', '4', '4', 4)
 {executed in 13 msec}

SqlSession ExcecuteType.BATCH 사용

MyBatis의 태그를 사용하지않고, SqlSession의 ExecuteType을 BATCH로 설정하는 방법으로도
다건의 데이터를 효율적으로 입력할 수 있습니다.

@Test
@DisplayName("SqlSession Execute.BATCH 를 이용한 배치처리")
void BulkInsertUsingSqlSession() {

    SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
    ItemMapper mapper = sqlSession.getMapper(ItemMapper.class);

    for (ItemDto itemDto : inputDataList) {
        retValue = mapper.insertItem(itemDto); // 리턴값으로 row와 상관없는 값이 나옴
    }
    sqlSession.flushStatements();
    sqlSession.Commit()
}

단 건의 쿼리가 반복되지만, SqlSession에 BATCH 설정을 통해 여러 건의 쿼리를 한번에 실행하도록 합니다.

batching 5 statements:
1:  INSERT INTO item_list#
        VALUES ( '0', '0', '0', 0)
2:  INSERT INTO item_list
        VALUES ( '1', '1', '1', 1)
3:  INSERT INTO item_list
        VALUES ( '2', '2', '2', 2)
4:  INSERT INTO item_list
        VALUES ( '3', '3', '3', 3)
5:  INSERT INTO item_list
        VALUES ( '4', '4', '4', 4)
 {executed in 13 msec}

각 방식의 성능비교

지금까지 언급했던 각 방식의 성능을 단순 실행에 걸리는 시간을 계산하여 비교해보면 아래와 같습니다.

<!-- 쿼리 로그를 찍지 않고, 실행했을 때, 1000건의 데이터를 Insert 하는데에 걸린 시간 -->
반복문을 이용한 다건 Insert : 3725ms
Mybatis <foreach> 태그를 이용한 다건 Insert - INSERT 문을 반복 : 3375ms
Mybatis <foreach> 태그를 이용한 다건 Insert - VALUES 값을 반복 : 270ms
SqlSession Execute.BATCH 를 이용한 배치처리 : 36ms

(위의 비교는 특정한 조건에서의 단순 실행시간 비교이므로, 주어진 상황에 따라서 차이가 있을 수 있습니다.)

Update 를 위한 방법

데이터를 Update 하는 로직의 경우,

  • (1) '특정 row들을 어떠한 값으로 일괄세팅 하는 경우'와
  • (2) '특정 row를 각각의 상황에 맞게 업데이트 하는 경우'로 구분할 수 있습니다.
  • (1)의 경우에는 를 WHERE절의 IN 구문에 사용하고
  • (2)의 경우에는 SET절에 CASE문에 를 사용하여 처리할 수 있습니다.
<update id="updateItemListUsingWhereClauseIn">
    UPDATE item_list
    SET price = CASE
        <foreach collection="list" item="item">
            WHEN item_code = #{item.itemCode} THEN #{item.price}
        </foreach>
            END
    WHERE item_code IN (
        <foreach collection="list" item="item" separator=",">
            #{item.itemCode}
        </foreach>
        )
</update>

Bulk 쿼리에서의 Return 값에 대해

지금까지의 방법으로 단건 -> 다건으로 쿼리를 실행하여 성능을 개선시켰습니다.
만약 단건으로 처리하던 이전 코드에서 쿼리 실행 후, affectedRow를 확인하는 경우에는 비즈니스의 문제가 생길 수 있으니 주의해야 합니다.

일반적으로 프로그램에서 실행되는 CRUD 쿼리는 리턴값으로 DB에 반영/조회된 Row의 갯수를 리턴합니다.
하지만 Batch 쿼리에서는 Row의 갯수를 제대로 리턴하지 못하는 경우가 있습니다.

MyBatis 태그를 이용한 방식

태그를 이용하면, 두가지 형태의 쿼리가 만들어집니다.

;(딜리미터)를 기준으로

  • (1) 하나의 쿼리로 여러행을 입력하는 형태 (e.g. MyBatis <foreach> VALUES문을 반복)
  • (2) 여러개의 쿼리를 실행하는 형태 (e.g. MyBatis <foreach> INSERT문을 반복 )
  • (1)의 경우에는 기존의 단건쿼리를 실행할때와 동일하게 리턴값을 통해, affectedRow를 얻을 수 있습니다.
  • (2)의 경우에는 리턴값으로 affectedRow를 얻을 수 없습니다. 리턴값으로 여러 쿼리 중 첫번째로 실행된 쿼리의 리턴값을 리턴합니다.

아래의 쿼리를 통해 확인해보도록 하겠습니다.

-- 하나의 Insert 문으로 5건의 Row를 입력하는 경우, 리턴값 5
INSERT INTO item_list
        VALUES
         ( '0', '0', '0', 0 )
         , ( '1', '1', '1', 1)
         , ( '2', '2', '2', 2)
         , ( '3', '3', '3', 3)
         , ( '4', '4', '4', 4)
-- 5건의 쿼리가 한번에 실행되는 경우, 리턴값 1
-- 가장 먼저 실행된 (1)번 쿼리의 결과값만 리턴이 됩니다.
INSERT INTO item_list VALUES ( '0', '0', '0', 0);  -- (1)
INSERT INTO item_list VALUES ( '1', '1', '1', 1);
INSERT INTO item_list VALUES ( '2', '2', '2', 2);
INSERT INTO item_list VALUES ( '3', '3', '3', 3);
INSERT INTO item_list VALUES ( '4', '4', '4', 4);

SqlSession Execute.BATCH 을 이용한 방식

Execute.Batch 방식을 이용했을 때의 리턴값은 afftectedRow 가 아닌, 이상한 값 -2147482646이 리턴됩니다.
BATCH 설정으로 인해 리턴되는 값으로, sqlSession.flushStatements()를 실행하기 전에는 실제 쿼리가 실행되지 않은 상태이기에 Row를 알 수 없는 것입니다.

MyBatis의 가이드에 따르면,
Execute.Batch 방식을 이용했을 때에는 아래와 같이 BatchResult#getUpdateCounts() 함수를 통해 affectedRow를 얻을 수 있습니다.

    @Test
    @DisplayName("SqlSession Execute.BATCH 를 이용한 배치처리")
    void BulkUpdateUsingSqlSession() {

        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        ItemMapper mapper = sqlSession.getMapper(ItemMapper.class);

        int retValue = 0;

        for (ItemDto itemDto : inputDataList) {
            itemDto.setPrice(itemDto.getPrice() * 10); // item의 가격을 10배 인상
            retValue = mapper.updateItem(itemDto); // 리턴값으로 row와 상관없는 값이 나옴
        }
        // BatchMode 에서는 flushStatements을 이용하여 리턴값을 얻을 수 있음
        List<BatchResult> batchResults = sqlSession.flushStatements();
        
        int[] updateCounts = batchResults.get(0).getUpdateCounts();
        for( int updateCnt : updateCounts ) {
            Assert.assertEquals(updateCnt, 1);
        }
    }

(mariaDB + mariadb-jdbc-connector 를 사용하고 있다면,
드라이버 연결 시, useBulkStmts=false 값을 설정해줘야 정상적인 값을 받을 수 있습니다.)

참고자료

profile
이상현
'당신을 한 줄로 소개해보세요'를 이 블로그로 대신 해볼까합니다.
이전 포스트

MSA 전환시, 세션 유지를 위한 방법들..

다음 포스트

[이슈 공유] MariaDB Executor.BATCH 시, BatchResult#getUpdateCounts() return 값 이슈

post-custom-banner

1개의 댓글

comment-user-thumbnail
Denia
2024년 8월 5일

좋은 정보 잘 보고 갑니다!
감사합니다

답글 달기