DDL 마이그레이션, 왜 이렇게 어려운가

Lock 메커니즘부터 Expand-Contract 패턴, 외래 키 전략까지 — 프로덕션 DB 스키마 변경이 서비스를 멈추는 이유와 그것을 피하는 방법을 추적한다.

ALTER TABLE orders ADD COLUMN notes VARCHAR(255); — 개발 환경에서는 1초도 안 걸린다. 프로덕션 테이블이 1억 건이면? 수십 분짜리 Lock이 서비스 전체를 마비시킨다. DDL이 Lock을 거는 원리를 이해하지 못하면, 언제 문제가 터질지 예측할 수 없다.

Lock의 두 층: MDL과 InnoDB

MySQL DDL에는 두 종류의 Lock이 겹쳐 있다.

첫 번째는 **Metadata Lock(MDL)**이다. DDL이 테이블 구조를 바꾸는 동안, 진행 중인 트랜잭션이 끝날 때까지 기다린다. 짧은 SELECT 하나가 커밋되지 않은 채 열려 있으면, 그 뒤로 들어온 ALTER TABLE은 MDL을 기다리며 큐에서 대기한다. 이 대기 상태가 길어지면 새로운 SELECT마저 그 뒤에 줄 서게 된다.

두 번째는 InnoDB Lock이다. DDL 작업 방식에 따라 세 수준으로 나뉜다.

Lock 수준	SELECT	INSERT/UPDATE/DELETE	언제
EXCLUSIVE	❌	❌	구버전 COPY 방식
SHARED	✓	❌	ALGORITHM=INPLACE
NONE	✓	✓	ALGORITHM=INSTANT

ALGORITHM=INSTANT는 물리 저장소를 건드리지 않고 메타데이터만 바꾼다. 1억 건 테이블에서 컬럼을 추가해도 밀리초가 끝이다. MySQL 8.0.29부터는 NOT NULL DEFAULT 컬럼도 INSTANT로 처리된다.

⚠ MDL 대기 함정

짧은 트랜잭션 하나가 MDL을 잡고 있으면 뒤의 DDL이 대기하고, 그 DDL 뒤로 새 DML이 모두 줄 선다. performance_schema.metadata_locks를 먼저 확인하고 DDL을 실행해야 한다.

INSTANT, INPLACE, 그리고 gh-ost

DDL 알고리즘 선택은 배포 전략을 결정한다.

ALGORITHM=INSTANT: 메타데이터만 변경한다. 기존 행의 물리 저장소는 그대로다. SELECT 시 런타임에 기본값을 자동으로 채워 반환한다. 실행 시간은 밀리초, Lock은 없다. MySQL 8.0.29 이상이라면 ADD COLUMN ... NOT NULL DEFAULT도 INSTANT다.

ALGORITHM=INPLACE: 임시 파일에 새 구조로 행을 재구성하고 교체한다. 빌드 중 읽기는 허용되고 쓰기 변경분은 Change Buffer에 누적된다. 1억 행 기준 수십 분이 걸리며, 그동안 INSERT/UPDATE/DELETE는 차단된다.

gh-ost: Binlog를 스트리밍해서 Ghost 테이블을 별도로 구성한다. 행 복사는 청크 단위로 진행되고, Lock은 최종 Cut-over 순간(수 초)에만 발생한다. 대용량 테이블이나 롤백이 필요한 상황에서 가장 안전한 선택이다. Binlog Row Format과 추가 설정이 필요하다는 단점은 있다.

gh-ost \
  --user=root --password=password \
  --host=prod-db.example.com \
  --database=ecommerce --table=orders \
  --alter="ADD INDEX idx_user_date (user_id, created_at)" \
  --chunk-size=5000 \
  --max-lag-millis=100 \
  --execute

Expand-Contract: 무중단 스키마 변경의 핵심

블루-그린 배포나 Kubernetes 롤링 업데이트 환경에서는 구버전 앱과 신버전 앱이 동시에 같은 DB를 바라본다. ALTER TABLE users RENAME COLUMN username TO user_handle을 한 번에 실행하면, 구버전 앱이 참조하는 username 컬럼이 사라지며 즉시 오류가 터진다.

Expand-Contract 패턴은 이 문제를 세 단계로 해결한다.

1. EXPAND  — 새 컬럼 추가 (NULL, INSTANT)
2. MIGRATE — 배치 백필 (동시 DML 허용)
3. CONTRACT — 구 컬럼 제거

올바른 순서는 이렇다. 먼저 앱을 배포해 양쪽 컬럼을 모두 읽고 쓰게 만든다. 그 다음 DB에 새 컬럼을 추가한다. 배치 백필로 기존 데이터를 옮긴다. 앱을 다시 배포해 새 컬럼만 사용하게 한다. 마지막으로 구 컬럼을 삭제한다.

NOT NULL 컬럼 추가도 같은 원리다. NULL로 먼저 추가하고, 배치로 값을 채우고, 마지막에 NOT NULL 제약을 붙인다. 각 단계는 모두 INSTANT이므로 Lock 시간은 밀리초다.

✎ 배치 크기 기준

한 번의 UPDATE 배치는 1~~5초 이내로 끝나야 한다. 10,000~~50,000행, 5분 간격이 실전에서 검증된 기준이다. WHERE IS NULL LIMIT N으로 진행률을 추적할 수 있다.

인덱스 추가의 숨겨진 비용

CREATE INDEX는 LOCK=NONE이다. 하지만 “Lock 없음”이 “성능 영향 없음”을 의미하지는 않는다.

인덱스 빌드는 전체 테이블을 스캔하고, 그동안 발생한 DML을 Change Buffer에 누적한다. 빌드가 완료되면 Change Buffer를 merge하는데, 이 구간에서 쓰기 성능이 20~50% 저하된다. FULLTEXT와 SPATIAL 인덱스는 더 나쁘다. 빌드 중 LOCK=SHARED가 걸려 쓰기가 완전히 차단된다.

인덱스 설계는 ESR 규칙을 따른다. Equality(등치) → Sort(정렬) → Range(범위) 순으로 컬럼을 배치한다. WHERE user_id = ? AND status = ? ORDER BY amount DESC라면 (user_id, status, amount DESC) 복합 인덱스가 최적이다. EXPLAIN으로 먼저 분석하고, 필요한 인덱스만 추가해야 한다. 불필요한 인덱스는 저장소를 낭비하고 INSERT를 느리게 만든다.

트레이드오프

✎ 도구별 트레이드오프

선택지	Lock	DML 영향	롤백	복잡도
ALGORITHM=INSTANT	밀리초	없음	불가	낮음
ALGORITHM=INPLACE	초~분	읽기만	불가	낮음
gh-ost	수 초 (cut-over)	없음	가능	중간
Expand-Contract	없음	없음	단계별	높음

외래 키도 트레이드오프가 있다. 단일 모놀리식 DB에서는 무결성을 자동으로 보장하지만, INSERT마다 참조 확인이 발생해 5배 이상 느려질 수 있다. MSA 환경에서는 서비스 간 DB 결합이 생기고 배포 순서가 복잡해진다. MSA에서는 외래 키를 제거하고 애플리케이션 계층 검증 + 배치 정리 + 이벤트 기반 동기화로 최종 일관성을 보장하는 것이 현실적이다.

정리

DDL Lock은 MDL과 InnoDB Lock 두 층이 겹친다. MDL 대기를 먼저 확인하라.
ALGORITHM=INSTANT가 가능하면 무조건 사용한다. MySQL 8.0.29부터 NOT NULL DEFAULT도 지원된다.
대용량 테이블은 gh-ost로, 컬럼 이름 변경과 구조 변경은 Expand-Contract 패턴으로 무중단 처리한다.
복합 인덱스는 ESR 규칙으로 설계하고, EXPLAIN으로 검증한 뒤 추가한다.
MSA에서 외래 키는 결합도를 높이고 배포를 복잡하게 만든다. 애플리케이션 검증으로 대체를 고려하라.

스키마 변경이 어려운 것은 DB가 나쁜 게 아니다. 구버전 앱과 신버전 앱이 공존하는 시간 동안 두 세계를 동시에 만족시켜야 하는 것이 어려운 것이다.