테마
온라인 DDL
DDL(Data Definition Language, 데이터 정의어)은 테이블·인덱스 같은 구조를 만들고 바꾸는 SQL이다. 큰 테이블에 ALTER TABLE을 돌리면 몇 시간이 걸릴 수 있는데, 그동안 테이블을 못 쓰면 서비스가 멈춘다. 온라인 DDL(online DDL)은 구조를 바꾸는 동안에도 조회와 쓰기를 계속 받게 하는 InnoDB의 기능이다. 이 문서는 변경 방식을 고르는 ALGORITHM·LOCK 옵션과, 작업마다 어떤 방식으로 얼마나 비싸게 도는지를 다룬다.
ALGORITHM — 어떻게 바꾸나
ALTER TABLE이 구조를 바꾸는 방식은 세 가지다. DML(Data Manipulation Language, 데이터 조작어)은 행을 읽고 쓰는 SQL이고, 아래 표의 "도중 DML"은 변경이 도는 동안 다른 세션(session, DB에 접속한 연결 하나)의 DML을 받아 주는지를 뜻한다.
| 방식 | 하는 일 | 도중 DML |
|---|---|---|
INSTANT | 테이블 정의만 고친다. 데이터는 안 건드린다 | 허용 |
INPLACE | 테이블을 통째로 복사하지 않고 제자리에서 바꾼다. 작업에 따라 재구성한다 | 대개 허용 |
COPY | 새 구조의 테이블을 만들어 행을 하나씩 복사한 뒤 바꿔치기한다 | 불허 |
INSTANT는 테이블 정의를 담는 데이터 딕셔너리(data dictionary)만 고치므로 테이블 크기와 상관없이 순식간에 끝난다.INPLACE는 사용자에게 보이는 복사본 테이블을 만들지 않지만, 작업에 따라 InnoDB 안에서 모든 행을 새 구조로 다시 쓰는 재구성(rebuild)을 한다. 재구성은 테이블 크기에 비례해 디스크 읽기·쓰기와 시간이 든다. 세컨더리 인덱스(secondary index) 추가는 재구성 없이 인덱스만 만든다. 칼럼 순서 변경이나 NOT NULL 지정은 재구성이라 오래 걸린다. 그래도 도중에 들어온 DML은 따로 기록해 두었다가 끝에 반영하므로 쓰기를 막지 않는다.COPY는 가장 오래된 방식이다. 복사하는 동안 조회는 되지만 쓰기는 막힌다.
ALGORITHM을 생략하면 MySQL은 그 작업이 지원하는 한 INSTANT, 안 되면 INPLACE, 그것도 안 되면 COPY를 고른다. 문제는 조용히 COPY로 떨어질 수 있다는 것이다. ALGORITHM=INPLACE처럼 명시하면 그 방식으로 할 수 없을 때 실행하지 않고 오류를 내므로, 운영 테이블에는 원하는 방식을 명시해 "생각보다 비싼 변경"을 미리 걸러 낸다.
LOCK — 도중에 무엇을 허용하나
LOCK은 DDL이 도는 동안 다른 세션에 무엇을 허용할지 정한다. 허용 범위가 넓은 것부터 NONE(조회·쓰기 모두 허용), SHARED(조회만 허용), EXCLUSIVE(둘 다 막음)이고, 생략하거나 DEFAULT면 그 작업이 허용하는 가장 넓은 수준을 쓴다. 작업이 지원하는 것보다 넓게 요구하면 문장이 오류로 실패한다.
sql
ALTER TABLE orders ADD INDEX ix_user_created (user_id, created_at),
ALGORITHM=INPLACE, LOCK=NONE;위 문장은 "복사 없이, 쓰기를 막지 않고"를 요구한다. 조건을 못 지키는 작업이면 시작하지 않는다.
작업별 비용 (MySQL 8.4)
| 작업 | 방식 | 재구성 | 도중 DML |
|---|---|---|---|
| 칼럼 추가·삭제 | INSTANT | 아니오 | 허용 |
| 칼럼 이름 변경(타입 유지) | INSTANT | 아니오 | 허용 |
| 기본값 변경 | INSTANT | 아니오 | 허용 |
| ENUM 끝에 값 추가 | INSTANT | 아니오 | 허용 |
| 세컨더리 인덱스 추가 | INPLACE | 아니오 | 허용 |
| VARCHAR 길이 늘리기 | INPLACE | 아니오 | 허용 |
| NULL·NOT NULL 변경 | INPLACE | 예 | 허용 |
| 칼럼 순서 변경 | INPLACE | 예 | 허용 |
| 기본 키 변경 | INPLACE | 예 | 허용 |
| 칼럼 데이터 타입 변경 | COPY | 예 | 불허 |
표에는 조건이 붙는다. VARCHAR는 값마다 앞에 실제 길이를 적은 길이 바이트를 붙이는데, 이 바이트 수가 같을 때만 INPLACE다. 칼럼 최대 바이트(글자 수 × 글자당 최대 바이트)가 255 이하면 1바이트, 256 이상이면 2바이트다. utf8mb4라면 VARCHAR(63)까지 1바이트, VARCHAR(64)부터 2바이트다. 늘리더라도 이 경계를 넘으면 COPY이고, 길이를 줄이는 변경은 경계와 상관없이 언제나 COPY다. ENUM은 값을 목록 순번으로 저장하므로 끝에 붙일 때만 INSTANT다. 중간에 끼우면 뒤쪽 값의 순번이 밀려 저장된 행을 모두 고쳐 써야 하므로 COPY가 된다. 기본 키 변경은 행이 기본 키 순서로 저장된 클러스터링 인덱스(clustered index)를 다시 짜야 해서 INPLACE라도 비싸다.
이어지는 내용은 온라인 DDL 2에서 다룬다. 8.4에서 즉시 끝나는 변경의 조건, 여러 변경을 묶는 법, 온라인 DDL도 피할 수 없는 메타데이터 락(metadata lock) 대기를 본다.
면접 포인트
- "INSTANT·INPLACE·COPY 차이?" → 메타데이터만 / 복사 없이 제자리(재구성할 수도) / 새 테이블로 복사·쓰기 막힘.
ALGORITHM을 명시해 COPY로 떨어지는 것을 막는다. - "
LOCK은 무엇을 정하나?" → 도중에 허용할 범위.NONE(조회·쓰기) /SHARED(조회만) /EXCLUSIVE(둘 다 막음). 지원보다 넓게 요구하면 오류. - "칼럼 타입 변경은 온라인인가?" → COPY라 도중 쓰기가 막힌다. VARCHAR 늘리기는 길이 바이트 수(1·2바이트)가 같을 때만 INPLACE.