[MySQL] 온라인 DDL

온라인 DDL이란?

DDL(Data Definition Language)은 데이터를 정의하는 언어로, 데이터베이스의 구조를 변경하는 쿼리를 의미한다. 테이블을 생성하거나 변경하는 등의 쿼리가 이에 해당한다.

온라인 DDL은 테이블의 스키마를 변경하는 작업이 실행되는 중에도 다른 커넥션에서 해당 테이블의 데이터를 조회하거나 변경하는 작업을 가능하게 한다.

MySQL 8.0 버전부터는 대부분의 스키마 변경 작업에 대해 MySQL 서버에 내장된 온라인 DDL 기능을 이용하는 것이 가능해졌다.

온라인 DDL 알고리즘

ALGORITHM과 LOCK 옵션을 이용해 어떤 알고리즘으로 스키마를 변경할 지 결정할 수 있다.

MySQL 서버는 다음 순서로 스키마 변경에 적합한 알고리즘을 찾는다.

• ALGORITHM=INSTANT
• ALGORITHM=INPLACE
• ALGORITHM=COPY

알고리즘의 우선순위가 낮을수록 더 큰 잠금, 더 많은 작업을 필요로 하므로 서버의 부하가 많이 발생한다.

이하 각각의 알고리즘에 대해 알아보자.

ALGORITHM=INSTANT

• 테이블의 데이터가 변경되지 않는다.
• 데이터 재구성(테이블 리빌드)이 발생하지 않는다. 즉, 데이터 파일에서 레코드의 저장 위치가 바뀌지 않는다.
• 스키마 변경 도중 다른 커넥션에서 테이블의 읽기/쓰기는 대기하게 되지만 스키마 변경 시간이 매우 짧으므로 다른 커넥션의 쿼리 처리에 큰 영향을 미치지 않는다.

예시

인덱스 타입 변경 (BTREE 또는 HASH 알고리즘 선택)

ALTER TABLE tbl_name DROP INDEX i1, ADD INDEX i1(key_part,...) USING BTREE, ALGORITHM=INSTANT;

칼럼 추가

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INSTANT;

테이블명 변경

ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INSTANT;

ALGORITHM=INPLACE

• 테이블의 데이터에 대한 복사본을 만들지 않는다. 임시 테이블로 데이터를 복사하지 않고 스키마 변경을 실행한다.
• 데이터 재구성(테이블 리빌드)를 실행하는 경우가 있다.
  • 데이터 재구성이 필요한 경우: 대표적으로 프라이머리 키를 추가하는 작업이 있다. 데이터 파일에서 레코드의 저장 위치가 바뀌어야하기 때문에 테이블 리빌드가 필요하다.
• 스키마 변경 중에도 다른 커넥션에서 테이블 읽기/쓰기 가능하다. 온라인 스키마 변경 실행 중에 다른 커넥션에서 발생한 쓰기 작업(INSERT, UPDATE, DELETE)는 온라인 변경 로그(Online alter log)라는 메모리 공간에 쌓아 두었다가 온라인 스키마 변경이 완료되면 로그 내용을 실제 테이블로 일괄 적용한다.
• 최초 시작 시점과 마지막 종료 시점에는 테이블 읽기/쓰기가 불가능하지만, 이 시간은 매우 짧기 때문에 다른 커넥션의 쿼리 처리에 큰 영향을 미치지 않는다.

예시

프라이머리 키 추가

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

세컨더리 인덱스 이름 변경

ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name, ALGORITHM=INPLACE, LOCK=NONE;

공간 검색 인덱스 생성 (드물게 LOCK=SHARED를 필요로 함)

CREATE TABLE geom (g GEOMETRY NOT NULL);
ALTER TABLE geom ADD SPATIAL INDEX(g), ALGORITHM=INPLACE, LOCK=SHARED;

ALGORITHM=COPY

• 변경된 스키마를 적용한 임시 테이블을 생성하고, 테이블의 레코드를 모두 임시 테이블로 복사한 후 최종적으로 임시 테이블을 RENAME하여 스키마 변경을 완료한다.
• 스키마 변경 작업 중에 다른 커넥션에서 테이블 읽기만 가능하고 DML(INSERT, UPDATE, DELETE)는 실행할 수 없다.

예시

프라이머리 키 삭제

ALTER TABLE tbl_name DROP PRIMARY KEY, ALGORITHM=COPY;

칼럼 데이터 타입 변경

ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;

온라인 처리 가능한 스키마 변경

MySQL의 모든 스키마 변경 작업이 온라인으로 가능한 것은 아니다. 어떤 스키마 변경 작업에 대해 온라인 DDL을 지원하는지 여부는 MySQL Document를 참조하면 확인할 수 있다. https://dev.mysql.com/doc/refman/8.3/en/innodb-online-ddl-operations.html

한편, 간단한 방법으로는 순서대로 옵션을 적용하여 실행해보면서 특정 스키마 변경 작업에 대해 어느정도 수준의 온라인 DDL 알고리즘을 지원하는지 알아볼 수 있다. 다음과 같이 서버 부하가 적은 순서대로 시도해보면서 지원되는지 여부를 판단할 수 있다.

1. ALGORITHM=INSTANT 옵션으로 스키마 변경 시도
2. 1번 실패시 ALGORITHM=INPLACE, LOCK=NONE 옵션으로 스키마 변경 시도
3. 2번 실패시 ALGORITHM=INPLACE, LOCK=SHARED 옵션으로 스키마 변경 시도
4. 3번 실패시 ALGORITHM=COPY, LOCK=SHARED 옵션으로 스키마 변경 시도
5. 4번 실패시 ALGORITHM=COPY, LOCK=EXCLUSIVE 옵션으로 스키마 변경 시도

프라이머리 키를 삭제하는 쿼리로 테스트를 해본다.

1번 옵션(ALGORITHM=INSTANT)으로 시도하면 에러가 발생하며 실패한다.

mysql> ALTER TABLE member DROP PRIMARY KEY, ALGORITHM=INSTANT;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY/INPLACE.

2번 옵션(ALGORITHM=INPLACE, LOCK=NONE)으로 시도해도 실패한다.

mysql> ALTER TABLE member DROP PRIMARY KEY, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY.

위에서 나온 “Try ALGORITHM=COPY.” 로그에 따라 3번 옵션(ALGORITHM=COPY, LOCK=SHARED)으로 시도하면 성공한다.

mysql> ALTER TABLE member DROP PRIMARY KEY, ALGORITHM=COPY, LOCK=SHARED;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0