[DB] MYSQL 테이블 변경 주의점 ALTER TABLE

---

0. 개요 ALTER TABLE

ALTER TABLE를 실무에서 함부로 사용했다가 서비스 중단이 몇일동안 이어졌다는 사례글을 보았기 때문에 이에 관한 내용을 보고 정리하기로 하였다.

 

 

 

 

 

 

1.MySQL

1-1. Copy 방식 (MySQL 5.5 이전)

가장 전통적인 방법으로 아래의 3단계의 과정을 거쳐서 테이블 변경을 이뤄낸다.
작업 시간 동안 원본 테이블이 읽기/쓰기 잠금이 되기 때문에 실제 서비스가 해당 작업이 끝날때까지 마비가 된다.

1. 새로운 구조의 임시 테이블 생성
2. 원본 테이블의 모든 데이터를 임시 테이블로 복사 (이때 테이블은 읽기 전용 상태가 됨)
3. 원본 테이블 삭제 후, 임시 테이블의 이름을 원본으로 변경

 

 

 

 

1-2.In-Place & Online DDL 방식(MySQL 5.6 ~ 5.7)

테이블 전체를 복사하지 않고 메타데이터 변경만으로 테이블을 수정하는방식인 In-Place 방식과 작업 중에도 데이터 입력(DML)을 허용하는 Online DDL 방식이 도입된 버전이다. 

LOCK=NONE을 설정하면 테이블 변경 작업 중에도 DML이 가능해서 서비스가 중단되지않는다.

 

 

 

 

1-3. INSTANT 방식 (MySQL 8.0.12+)

데이터 파일(.ibd)을 전혀 건드리지 않고, 데이터 딕셔너리(메타데이터)만 즉시 업데이트하는 방식이다.
이전보다 안정적이고 원자적으로 처리된다.

ALGORITHM=INSTANT를 활용하면 테이블 데이터나 인덱스를 재구성하지 않아 대용량 테이블에서도 수 ms 만에 컬럼 추가 등의 작업이 끝난다.

 

 

 

 

이렇게 명시적으로 모드설정또한 가능하다.

ALTER TABLE users 
ADD COLUMN last_login_date DATETIME DEFAULT CURRENT_TIMESTAMP, 
ALGORITHM=INSTANT;

ALTER TABLE users 
ADD INDEX idx_last_login (last_login_date), 
ALGORITHM=INPLACE, 
LOCK=NONE;

ALTER TABLE users 
MODIFY COLUMN user_id BIGINT, 
ALGORITHM=COPY;