100만 row가 있는 테이블에 컬럼 추가는 어떻게 할까

테이블을 설계하고 서비스를 운영하다보면 자연스럽게 많은 데이터가 쌓이게 되는 테이블들이 존재하게 됩니다. 

또한 부득이한 경우 테이블에

컬럼 추가, 변경, 인덱스 추가 등 여러 가지 해당 테이블에 값을 변경해야 되는 상황이 생기게 되는데요.

초기에 데이터가 얼마 존재하지 않을 때는 문제가 되지 않지만 데이터가 많이 쌓이게 된 경우에는 문제가 발생할 수 도 있습니다.

 

일반적으로 컬럼을 추가한다고 할 때 사용할 수 있는 쿼리는

ALTER TABLE `테이블명` ADD `컬럼명` 자료형

ex) 'USER' 라는 테이블에 'name' 10글자 컬럼 추가

ALTER TABLE `USER` ADD `name` VARCHAR(10)

와 같이 사용할 수 있습니다.

 

 

하지만, MySQL/MariaDB 는 전통적으로 ALTER 명령어에 대해서 해당 명령어가 실행하는 도중에는 해당 테이블을 LOCK 을 잡고 있게 되기 때문에 쓰기 기능이 활발한 테이블에서는 굉장히 불편한 상황일 수 밖에 없었습니다.

 

그런데 MySQL 5.6 버전 이상부터는 Online DDL 기능이 활성화되었습니다. Online DDL은 테이블의 구조를 변경(스키마 변경)을 실행하는 도중에도, INSERT나 UPDATE와 같은 DML들을 실행할 수 있도록 해주는 기능입니다.

 

저는 MySQL 5.7 문서를 참고해서 작성하였습니다.

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

MySQL :: MySQL 5.7 Reference Manual :: 14.13.1 Online DDL Operations

 

 

Online DDL 사용 시에는 테이블 LOCK 을 최소화 하기 위해 ALGORITHM, LOCK 이라는 2가지 요소를 활용할 수 있습니다.

위에 예시를 들었던 컬럼 추가 쿼리를 2가지 요소를 추가하면 

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;

와 같이 활용할 수 있습니다.

 

물론 컬럼 추가 외에도 추가, 삭제, 이름 변경, 자료형 변경 등등 다양하게 활용할 수 있습니다.

 

다만 컬럼 추가 시에 유의해야될 점은   컬럼을 추가하는 위치를 특정하면 ALGORITHM=COPY 와 동일하게 동작한다는 점입니다.

ALTER TABLE 'table_name'
ADD COLUMN 'add_column' column_definition AFTER 'exist_column',
ALGORITHM=INPLACE, LOCK=NONE;

위와 같이 AFTER 를 활용하여 특정 컬럼 뒤에 컬럼을 추가할 경우  사실상 테이블을 COPY 하고 추가되기 때문에 ALGORITHM, LOCK 을 제대로 활용하지 못한다고 할 수 있습니다. 때문에 컬럼을 추가할 경우에는 반드시 AFTER 문법을 사용하지 않도록 하여야 합니다.