문자 집합과 콜레이션 변경을 위한 온라인 DDL 활용

온라인 DDL이란?

온라인 DDL은 테이블의 구조를 변경(스키마 변경)을 실행하는 도중에도 다른 커넥션에서 테이블의 데이터를 변경하거나 조회하는 작업을 계속 실행할 수 있도록 돕는 방법이다. 기본적으로 DDL 명령은 데이터베이스에 큰 영향을 미치지만, 온라인 DDL을 사용하면 일부 작업을 '온라인'으로 처리할 수 있어 시스템 중단을 피할 수 있다. 

문자 집합과 콜레이션 변경 작업(DDL) 중, 데이터 변경이 가능할까?

이 말은 즉, 온라인 DDL에서 ALGORITHM=INPLACE, LOCK=NONE 이 가능한지가 궁금하다는 말이다.

결론을 먼저 말하면 ‘될 수도 있고, 안 될 수도 있다’ 이다.

 

다음은 users 테이블의 예제이다. 기본 문자 집합은 utf8이고, department, email, name이라는 세 개의 문자열 컬럼이 있다.

ALTER TABLE로 문자 집합과 콜레이션 변경

ALTER TABLE users
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ALGORITHM=INPLACE, LOCK=NONE;

이 명령은 테이블 전체의 문자 집합과 콜레이션을 utf8mb4로 변경하려는 작업이다. 명령을 실행하면 다음과 같은 오류가 발생한다.

 

INPLACE 알고리즘은 지원하지 않으니, COPY 알고리즘을 사용하라고 한다.
하지만, 이 조언대로 COPY 알고리즘을 사용하면 해당 DDL 실행 시간 동안 DML은 대기하게 된다.

 

 

테이블이 아닌 컬럼 단위로 문자 집합과 콜레이션을 변경하는 작업을 시도해보았다.

-- email 컬럼 변경 (성공)
ALTER TABLE users
    MODIFY COLUMN email varchar(100)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ALGORITHM=INPLACE , LOCK=NONE;

-- department 컬럼 변경 (실패)
ALTER TABLE users
    MODIFY COLUMN department varchar(100)
        CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ALGORITHM=INPLACE , LOCK=NONE;    

1. email 컬럼은 INPLACE 알고리즘을 사용할 수 있다.
2. department 컬럼은 INPLACE 알고리즘을 사용할 수 없다.

 

이유는 무엇일까?

users 테이블의 인덱스 조회

두 컬럼의 차이를 조사한 결과, 컬럼이 인덱스에 포함되었는지 여부가 영향을 미쳤다.

 

• email 컬럼은 인덱스에 포함되지 않았기 때문에 INPLACE 알고리즘으로 처리되었다.
  • 또한, 문자 집합 변경 과정에서 utf8인 기존 데이터가 새로운 문자 집합 utf8mb4에 유효하기 때문에 데이터 변환이 필요하지 않아, INPLACE 방식으로 처리할 수 있었다. (utf8mb4 -> utf8 반대 경우는 데이터 손실이 일어날 수 있어 불가능하고, 아예 다른 문자집합일 경우 데이터 변환이 필요해 INPLACE 방식이 불가능할 수 있다.) 
• department 컬럼은 idx_department_salary라는 인덱스에 포함되어 있어 INPLACE 알고리즘을 사용할 수 없었다.
  • 문자 집합이나 콜레이션 변경 시 해당 인덱스를 재작성해야 한다. 이로 인해 INPLACE 알고리즘을 사용할 수 없었으며, COPY 알고리즘을 사용해야만 했다.

 

 

그렇다면, 만약 모든 문자열 컬럼에 인덱스가 없는 상황이라면, 처음 명령이 성공할까? 인덱스를 지우고 다시 해당 DDL을 실행시켜봤다.

ALTER TABLE users
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    ALGORITHM=INPLACE, LOCK=NONE;

 

이 경우 테이블 단위로 문자 집합과 콜레이션을 변경하는 작업이 성공했다.

 

온라인 DDL에서 INPLACE가 작동하는 조건

ALGORITHM=INPLACE가 작동하려면 다음 조건을 충족해야 한다.

컬럼에 데이터 변환이 필요하지 않을 때

기존 데이터가 새로운 문자 집합에서 유효하다면, MySQL은 데이터를 변환하지 않고 메타데이터만 업데이트한다. 예를 들어, utf8에서 utf8mb4로 변경할 때 기존 데이터가 utf8mb4에서도 유효하다면 INPLACE 알고리즘을 사용할 수 있다.

컬럼이 인덱스에 포함되지 않았을 때

문자 집합이나 콜레이션 변경은 해당 컬럼을 사용하는 모든 인덱스를 재작성해야 한다. 따라서, 컬럼이 인덱스에 포함되지 않았다면 INPLACE 알고리즘을 사용할 수 있다.

프라이머리 키나 유니크 키가 영향을 미치지 않을 때

변경 대상 컬럼이 프라이머리 키나 유니크 인덱스의 일부가 아니라면 INPLACE 알고리즘을 사용할 수 있다.

 

 

정리

온라인 DDL을 활용하여 문자 집합과 콜레이션을 변경할 때, ALGORITHM=INPLACE와 LOCK=NONE을 사용할 수 있는 조건을 충족시키는 것이 중요하다. 데이터 변환이 필요하지 않고, 변경하려는 컬럼이 인덱스에 포함되지 않으며, 프라이머리 키나 유니크 키가 영향을 미치지 않으면 성능 최적화를 위해 INPLACE 알고리즘을 사용할 수 있다.