[MySQL] 간단한 1회성 마이그레이션 스크립트를 수행해야 한다면?

서버 개발을 하다보면 여러가지 상황에서 마이그레이션을 해야하는 상황이 생긴다.

예를 들면 기능의 변경 및 추가로 인해 새로운 데이터를 업데이트 해야하거나, 오염된 데이터를 정상화 시키는 작업 등이 그렇다

이럴 때 여러가지 방법으로 간단한 스크립트를 처리 할 수 있는데 나는 보통 1회성 JS 스크립트(Node)나 SQL 스크립트를 이용하곤 했다.

예를 한가지 들어보자.
DB에 어떤 JSON 컬럼이 있다. 잘못된 요청으로 인하여 이상한 데이터가 DB내에 적재 되었다.
약 1000만 건의 데이터 중에 2만건 정도의 데이터를 UPDATE 해야하는 쿼리가 있다고 가정하고 이를 정상화 해보자.

 

1. JS 1회성 스크립트

const batchSize = 500; // 한번에 처리할 양
let offset = 0;
let totalUpdated = 1;

while (totalUpdated > 0) {
  // 먼저, 대상이 될 id를 검색한다
  const ids = await db.query(`
    SELECT id FROM a
    WHERE created_at < '2020-05-05 00:00:00' AND input LIKE '%"name":""%'
    LIMIT ${batchSize} OFFSET ${offset}
  `);

  if (ids.length === 0) break;
  
  // 위에서 찾은 id를 update 처리한다
  await db.transaction(async (transaction) => {
    const [result] = await db.query(`
      UPDATE a
      SET answer_input = REPLACE(input, '"name":""', '"name":"Text"')
      WHERE id IN (${ids.join(',')})
    `, { transaction });

    totalUpdated = result.affectedRows;
  });

  offset += batchSize;
}

이렇게 JS 방식으로 업데이트를 하게 되면 어떤 문제가 있을까?

 

1. 네트워크 오버헤드 발생

각 쿼리 실행마다 클라이언트와 데이터베이스 서버 간의 네트워크 통신이 발생한다. 이는 배치가 많을수록 지연을 증가시킬 수 있다.

2. 데이터베이스 연결 유지

각 배치 단위로 트랜잭션을 처리하기 때문에 데이터베이스 연결을 지속적으로 열고 닫거나 유지해야 한다. 이는 리소스 소비를 증가시킨다.

3. 트랜잭션 관리의 복잡성

각 배치 단위로 트랜잭션을 관리하는 것은 복잡성을 증가시키며, 특히 중간에 실패가 발생했을 때 롤백 처리가 어렵다.

 

물론 장점도 있다. 버저닝이 쉽고 커스텀한 예외처리가 가능하다. 트랜잭션 관리도 복잡하지만 조금 더 명시적으로 관리가 가능할 것이다.

2. SQL 스크립트(프로시저 이용)

# 1. 임시 테이블 생성, 임시 테이블은 세션때까지만 살아있는 테이블
CREATE TEMPORARY TABLE update_targets AS
SELECT id
FROM a
WHERE created_at < '2024-05-05 00:00:00' AND input LIKE '%"name":""%';

DELIMITER //		# 종료 표식 변경, ';' 는 계속 사용해야 하므로 명령의 종료를 '//'로 바꾼다.

# 2. PROCEDURE 정의
CREATE PROCEDURE BatchUpdate()
BEGIN
    DECLARE batch_size INT DEFAULT 500; # 한번에 처리할 배치 사이즈
    DECLARE total_updated INT DEFAULT 1;
    DECLARE offset INT DEFAULT 0;

    START TRANSACTION; # 트랜잭션 오픈

    WHILE total_updated > 0 DO
        UPDATE a
        SET input = REPLACE(answer_input, '"name":""', '"name":"Text"')
        WHERE id IN (
            SELECT id FROM (
                SELECT id FROM update_targets
                LIMIT batch_size OFFSET offset
            ) AS subquery
        );

        SET total_updated = ROW_COUNT(); # 업데이트가 된 수
        SET offset = offset + batch_size; # 다음 배치 처리를 위해 offset 증가
    END WHILE;

    COMMIT;
END //

DELIMITER ;
CALL BatchUpdate();		# 3. PROCEDURE 호출

DROP TEMPORARY TABLE update_targets; # 4. 임시 테이블 삭제

이 스크립트는 아래 과정으로 수행된다.

1. TEMPORARY TABLE 생성
2. PROCEDURE 정의
3. PROCEDURE 실행
4. TEMPORARY TABLE 삭제

이렇게 프로시저를 사용한다면 똑같은 작업이라도 여러 이점을 가지고 올 수 있다.

 

1. 성능 최적화

프로시저를 사용하면 서버 측에서 직접 실행되므로 클라이언트와의 데이터 전송이 최소화되어 성능이 향상될 수 있다.

이는 특히 대량의 데이터 업데이트를 수행할 때 중요한 이점이다.

 

2. 트랜잭션 관리

프로시저를 사용하면 전체 작업을 하나의 트랜잭션으로 처리할 수 있다.

이는 작업 도중 오류가 발생했을 때 롤백하거나, 전체 작업을 원자적으로 처리하는 데 도움이 된다. 하지만 하나의 트랜잭션으로 처리하는 만큼 부하는 증가하고 Lock 시간이 길어져 다른 트랜잭션의 수행을 지연시킬 수 있다. 따라서 EXPLAIN 등의 키워드로 충분한 검증을 하고 수행해야 한다. 물론 JS스크립트와 비교할 수는 없다. 같은 작업이라도 프로시저는 DB서버 내부에서 실행되기 때문.

 

3. 네트워크 오버헤드 감소

프로시저는 데이터베이스 서버 내에서 직접 실행되므로, 클라이언트와 서버 간의 네트워크 오버헤드를 줄일 수 있습니다.

 

하지만 모든 상황에서 프로시저가 최적은 아니다. 위에서도 언급했지만 정말 큰 대규모의 처리인 경우 부하가 커질 수 있고 Lock 시간이 길어지게 된다. 이때는 JS에서 트랜잭션을 나누고 COMMIT과 처리를 반복하는 방법도 있겠지만 프로시저 내에서도 가능하다.

 

3.  SQL 스크립트(트랜잭션 분산)

# 임시 테이블 생성 가정

DELIMITER //

CREATE PROCEDURE BatchUpdate()
BEGIN
    DECLARE batch_size INT DEFAULT 500;
    DECLARE total_updated INT DEFAULT 1;
    DECLARE offset INT DEFAULT 0;

    WHILE total_updated > 0 DO
        START TRANSACTION; # 트랜잭션을 반복문 안에서 연다

        UPDATE a
        SET input = REPLACE(answer_input, '"name":""', '"name":"Text"')
        WHERE id IN (
            SELECT id FROM (
                SELECT id FROM update_targets
                LIMIT batch_size OFFSET offset
            ) AS subquery
        );

        SET total_updated = ROW_COUNT();
        SET offset = offset + batch_size;

        COMMIT; # 1회 작업이 완료되면 커밋한다.
    END WHILE;
END //

DELIMITER ;

 

상황에 따라 최적의 전략을 생각하고 수행하면 된다. 개인적으로 안전하게 트랜잭션을 나누는 방법을 추천하지만,

원자성이 중요하거나 롤백이 힘든 경우에는 DB의 사양, 처리 데이터의 양, 실행계획 등을 참고하여 2번의 방법을 쓰는것도
괜찮은 방법일 수 있다.