Home MySQL 벌크 UPDATE에 대한 오해: Row-by-Row
Post
Cancel

MySQL 벌크 UPDATE에 대한 오해: Row-by-Row

Posted Jul 19, 2025
By devjun10
12 min read

글을 작성하게 된 계기

회사에서 벌크 업데이트를 사용하며 새롭게 알게된 내용 과 벌크 업데이트를 할 때 주의할 점 에 대해 정리 하기 위해 글을 작성하게 되었습니다.





1. 무엇을 잘못 알고 있었을까?

MySQL에서 하나의 트랜잭션으로 여러 Row를 업데이트 하면 쿼리가 한 번만 실행 되는 줄 알았는데, 실제로는 InnoDB가 각 Row를 하나씩 찾아 업데이트 하더라고요. 🐧

  • 벌크 업데이트를 할 때, 하나의 트랜잭션 에서 업데이트를 처리할 경우, 한 번만 업데이트가 이루어진다 고 생각
  • 하지만 MySQL은 하나의 트랜잭션에서 여러 Row를 업데이트 할 때, 각 Row를 개별적으로 업데이트




간단한 예제를 통해 이를 살펴보겠습니다. 1,000만 명의 사용자의 포인트를 체크해서 등급을 업데이트 하는 상황을 가정해보겠습니다. 포인트가 1000 이상이면 실버, 2000 이상이면 골드, 3000 이상이면 플래티넘으로 업데이트 해야 합니다.

  • SILVER: 1000 이상
  • GOLD: 2000 이상
  • PLATINUM: 3000 이상




이 경우, 다음과 같이 CASE WHEN 구문을 사용해 한 번에 업데이트를 수행할 수 있습니다.

1
2
3
4
5
6
7
8

UPDATE user
SET grade = CASE
                WHEN point >= 3000 THEN 'PLATINUM'
                WHEN point >= 2000 THEN 'GOLD'
                WHEN point >= 1000 THEN 'SILVER'
                ELSE 'BRONZE'
            END
WHERE id IN (1, 2, 3, 4);




이를 자바 코드로 보면 다음과 같은데, 처음에 이 코드를 볼 때, 청크 단위(IN 쿼리)로 하나의 트랜잭션 에서 한 번에 벌크 업데이트가 이루어질 줄 알았습니다. 하지만 해당 쿼리는 하나의 트랜잭션 에서 각 Row를 건 별로 업데이트 하고 있었습니다. 제가 생각했던 것과는 전혀 다르게요. 왜 그럴까요?

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
29
30
31
32

@Repository
public class UserRepository {

    private final JdbcTemplate jdbcTemplate;

    public UserRepository(final JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void updateUserGradesByPoint(final List<User> users) {
        if (users.isEmpty()) {
            return;
        }

        String idList = users.stream()
            .map(user -> String.valueOf(user.getId()))
            .collect(Collectors.joining(","));

        final String sql = String.format("""
            UPDATE user
            SET grade = CASE
                WHEN point >= 3000 THEN 'PLATINUM'
                WHEN point >= 2000 THEN 'GOLD'
                WHEN point >= 1000 THEN 'SILVER'
                ELSE 'BRONZE'
            END
            WHERE id IN (%s)
            """, idList);

        jdbcTemplate.update(sql);
    }
}







2. 바이너리 로그 살펴보기

이는 InnoDB의 동작방식 때문입니다. InnoDB는 업데이트를 할 때, Primary Key(PK)를 기반으로 해당 Row를 찾은 후, 각 Row에 대해서는 잠금(Lock)을 걸고 하나씩 업데이트 합니다. 따라서 벌크 업데이트로 한 번에 처리되는 것처럼 보여도, 실제로는 Row 단위로 순차적으로 처리하는 것이죠.

When a transaction updates a row in a table, or locks it with SELECT FOR UPDATE, InnoDB establishes a list or queue of locks on that row.




다음과 같이요. 아래는 MySQL의 바이너리 로그(Binary Log)에 기록된 내용입니다. CASE WHEN 이건, IN (..) 이건, MySQL은 결국 Row 하나하나 업데이트하는 형태로 처리하는 것을 알 수 있습니다.

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
29

# at 1549
#250719 23:41:01 server id 1  end_log_pos 1705 CRC32 0xa7933fd9         Update_rows: table id 91 flags: STMT_END_F
### UPDATE `test_db`.`user`
### WHERE
###   @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=1500 /* INT meta=0 nullable=1 is_null=0 */
###   @3='BRONZE' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### SET
###   @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=1500 /* INT meta=0 nullable=1 is_null=0 */
###   @3='SILVER' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### UPDATE `test_db`.`user`
### WHERE
###   @1=3 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2500 /* INT meta=0 nullable=1 is_null=0 */
###   @3='BRONZE' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### SET
###   @1=3 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2500 /* INT meta=0 nullable=1 is_null=0 */
###   @3='GOLD' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### UPDATE `test_db`.`user`
### WHERE
###   @1=4 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=3500 /* INT meta=0 nullable=1 is_null=0 */
###   @3='BRONZE' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### SET
###   @1=4 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=3500 /* INT meta=0 nullable=1 is_null=0 */
###   @3='PLATINUM' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */




물론 단 건씩 업데이트 하는 것 보다는 네트워크를 덜 타기 때문 에 성능은 좋기 때문에 내부 동작 원리만 잘 이해하고 있다면 충분히 이를 잘 활용할 수 있습니다. 천 만 건의 회원 정보를 업데이트할 때, 1,000건 단위로 청크를 나누어 업데이트할 경우, 약 3-5분 정도가 소요되었는데요, 이정도면 나쁘지 않죠? 🚀

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
29
30
31
32
33
34
35
36
37
38

@Repository
class UserDao(
    private val jdbcTemplate: JdbcTemplate
) : UserRepository {

    companion object {
        private const val CHUNK_SIZE = 1_000
    }

    override fun updateUserGradesByIds(userIds: List<Long>) {
        if (userIds.isEmpty()) return

        userIds.chunked(CHUNK_SIZE).forEach { chunk ->
            val placeholders = chunk.joinToString(",") { "?" }

            // language=sql
            val sql = """
                UPDATE user
                SET grade = CASE
                    WHEN points >= 3000 THEN 'PLATINUM'
                    WHEN points >= 2000 THEN 'GOLD'
                    WHEN points >= 1000 THEN 'SILVER'
                    ELSE 'BRONZE'
                END
                WHERE id IN ($placeholders)
            """.trimIndent()

            val start = System.nanoTime()
            jdbcTemplate.update(sql, *chunk.toTypedArray())
            val end = System.nanoTime()
            val elapsedTime = String.format("%.3f", (end - start) / 1e9)
            log.info("[Bulk Update] updated ${chunk.size} users in $elapsedTime seconds")
        }
    }

    ......

}





정리하면 CASE와 INQUERY를 사용할 경우, 한 번에 업데이트를 처리 할 줄 알았지만, 내부적으로는 각 Row를 개별적으로 업데이트 합니다.

  1. 하나의 트랜잭션에서 여러 Row를 업데이트 할 때, MySQL은 각 Row를 개별적으로 업데이트 합니다.
  2. 이는 MySQL InnoDB의 동작 원리 때문인데, 각 Row를 찾은 후, 잠금을 걸고 순차적으로 업데이트 하기 때문입니다.







3. 주의할 점

물론 여기서도 주의할 점이 있습니다. 바로 복제 지연 입니다. Primary 서버에서는 대량 데이터를 업데이트할 때, 해당 내용을 바이너리 로그에 기록한 뒤 트랜잭션을 완료합니다. 그러나 Replica 서버는 이 바이너리 로그를 순차적으로 읽어 하나씩 적용하며 동기화를 진행합니다. 이 과정에서 처리 속도 차이로 인해 복제 지연이 발생할 수 있습니다.

image





처리해야 할 바이너리 로그가 많아지면 I/O 작업이 증가하고, 그에 따라 처리 속도도 느려지게 됩니다. 이는 처리할 데이터 양이 많을수록 더욱 심해집니다. SHOW REPLICA STATUS\G 명령어를 사용해 Seconds_Behind_Source 값을 확인하면, 현재 복제가 얼마나 지연되고 있는지 확인할 수 있습니다.

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
29
30
31
32
33
34
35

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
                           ......

                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 151220159
              Relay_Log_Space: 365402634
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 30       # 30초 지연
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1
                  Source_UUID: d5662674-b7d3-11ef-8100-0242ac130009
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Waiting for replica workers to process their queues
           Source_Retry_Count: 86400
                  Source_Bind:
      
                            ......





복제 지연 발생하면 Replica를 사용하는 서비스는 최신 데이터가 아닌 과거 데이터를 조회 하는 문제가 발생할 수 있습니다. 아래는 복제 지연이 30초 발생했을 때인데요, Replica(오른쪽)와 Primary(왼쪽)의 최신 데이터 개수가 다르며, 갈수록 그 차이가 커지는 것 을 확인할 수 있습니다. 마지막엔 140만 건의 차이가 발생했네요.

image




왜 복제 지연을 고려해야 했냐고요? 성능 만 고려 해서 비동기 로 처리하다 복제 지연을 겪었거든요. 성능은 10배 가까이 향상되었지만, 서비스가 안정적이지 않으면 무슨 소용이겠습니까. 😕 여튼, 대량 쓰기 작업에서는 복제 지연 을 고려해야 하며, 이를 해결하기 위해서는 적절한 청크 단위 설정, 병렬 복제 활성화, slave_parallel_workers 값을 조정해 해결할 수 있습니다.

1

SET GLOBAL slave_parallel_workers = ${VALUE};







4. 정리

요즘 대량 데이터를 다루면서 이런 저런 고민이 참 많은데요, 정합성 은 당연하고 성능 도 고려해야 하니 쉽지 않더라고요. 동작 원리를 확실하게 알고 있지 않다면 실수하기 딱 좋아서 조금 불안하긴 한데, 점점 핵심적인 업무가 많아지며 더 재미있어 지기도 하네요.

Database
This post is licensed under CC BY 4.0 by the author.
Contents

대량 배치 작업 전, 백업의 중요성과 적절한 방법

감사 이력 관리 방법 비교