17장. 온라인 DDL — ALGORITHM과 LOCK이 보장하는 범위

{/* 삽화 자리 — 영업 중인 가게 안에서 선반을 갈아 끼우는 사람. 손님은 계속 드나들지만 문 앞에서 잠깐씩 멈칫한다 */}

ALGORITHM=INPLACE, LOCK=NONE을 걸고 200만 행에 인덱스를 만드는 7.3초 동안, 다른 세션이 7만 건을 넣고 읽었습니다. 실패는 0건이에요. 다만 처리량이 31% 줄고 p99가 0.05ms에서 0.11ms로 뛰었습니다. 그리고 안 되는 조합을 치면 0.3밀리초 만에 거부당합니다 — 데이터를 한 바이트도 안 만지고요.

면접 실전 질문: ① 온라인 DDL이 뭔가요? ② ALGORITHM=INPLACE를 왜 명시하나요? ③ LOCK=NONE이면 무중단인가요?

배경 — 4부가 밟고 있던 전제

4부가 깔고 있던 전제 — 인덱스를 만들 수 있다 — 를 여기서 걷어냅니다.

미리 말씀드리면, 이 장에서 제일 오래 걸린 ALTER는 26.7초입니다. 200만 행에서요. 운영 테이블은 그보다 크고, 그동안 놀고 있지도 않습니다.

5부는 그 이야기입니다. 이 장이 무엇이 되고 얼마나 걸리는가, 18장이 왜 그게 무중단이 아닌가, 19장이 나아졌다는 걸 어떻게 보이는가, 20장이 인덱스로 안 되는 건 무엇인가요.

그리고 16장이 남긴 질문 하나에도 여기서 답합니다 — 오래 굴려 헐거워진 인덱스를 다시 빽빽하게 만들 수 있는가.

스토리 — 두 축, 그리고 가드레일

테이블이 서비스를 계속하는 채로 스키마를 바꾸는 것 — 그게 온라인 DDL입니다. MySQL은 그걸 절 두 개로 표현해요. 작업을 어떻게 수행할지(ALGORITHM)와 그동안 동시성을 어디까지 허용할지(LOCK)입니다.

여기서 오해하기 쉬운 게 하나 있습니다. 이 절들은 “이렇게 해줘”가 아니라 “이 조건을 못 지키면 실행하지 마라”입니다.

생략하면 MySQL이 알아서 낮춰 잡습니다. 조건이 안 맞으면 아무 말 없이 COPY로 내려가요. 명시하면 그 경우 에러로 실패합니다.

성능 지시가 아니라 가드레일입니다. 그래서 무엇이 가능한지 모르겠으면 명시해서 실패로 알아내면 됩니다.

ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT;              -- ① 가장 싼 것부터
ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INPLACE, LOCK=NONE;   -- ② 실패하면 한 단계 내린다
-- ③ 그것도 실패하면 COPY 뿐 → 운영에서는 여기서 멈추고 계획을 다시 짠다

정말 그런지, 200만 행짜리 테이블에 직접 쳐봤습니다.

핵심 — 실패는 0.3밀리초에 나고, 성공은 0.005초에서 27초까지입니다

✅ 실측 (MySQL 8.0.46 / Docker / Apple M1 / macOS, 2026-08. scan_demo 200만 행 · 575MiB의 사본에 매번 새로 시도했고, 적재 직후의 더티 페이지를 가라앉힌 뒤 쟀습니다. 시간은 서버 안에서 NOW(6) 차이로 재고 성공 케이스는 3회 중앙값입니다. 실패 케이스는 프로시저 핸들러로 잡아 3회 잰 값이에요. 재현: ./ch17/ddlmatrix.sh. 전체 소스: docs/book/code/db-index/ch17/ddlmatrix.sh)

실패가 0.3밀리초입니다.

처음엔 이걸 190ms로 쟀습니다. 셸에서 docker compose exec를 세 번 호출해 바깥에서 쟀거든요 — 프로세스 기동과 접속 비용이 통째로 섞여 있었습니다. 서버 안에서 다시 재니 1,000분의 1도 안 되는 값이 나왔어요. 제가 제 결론을 과소 주장하고 있었던 셈입니다.

이게 가드레일이 진짜라는 증거입니다. 데이터를 한 바이트도 안 만지고 거부합니다. 200만 행에서 이러니 4.6GB에서도 같아요 — 실패 비용은 테이블 크기와 무관합니다.

그리고 에러 메시지가 다음 수를 알려줍니다. Try ALGORITHM=COPY/INPLACE. — 사다리의 다음 칸이 거기 적혀 있어요.

같은 연산인데 알고리즘이 157배를 가릅니다. 컬럼 추가가 INSTANT로는 127.5ms, INPLACE로는 20,057ms예요. INSTANT는 딕셔너리에 “이런 컬럼이 있다”고만 적고 끝내고, INPLACE는 테이블을 다시 씁니다.

그리고 인덱스 추가에는 선택지가 없습니다. INSTANT가 안 되거든요 — B-tree를 실제로 만들어야 하니 데이터를 읽어야 합니다. 우리가 고를 수 있는 최선이 INPLACE고, COPY보다 2.5배 빠릅니다(5,435 대 13,708).

반대로 인덱스 삭제는 4.7ms입니다. 만드는 데 5.4초 걸린 걸 지우는 데는 5밀리초예요. 지우는 건 사실상 메타데이터 작업이니까요.

📄 문서 기반 (미검증) — 버전마다 지원 범위가 바뀝니다. INSTANT만 해도 8.0.12에서 맨 뒤 컬럼 추가로 시작해 8.0.29에서 임의 위치 추가·삭제까지 넓어졌어요. 대신 8.0.29부터는 테이블당 instant 연산이 64회를 넘으면 재구축이 필요합니다. 작업 전에 해당 버전 문서를 확인하는 게 맞습니다.

(표에서 INSTANT 컬럼 이름 변경 221.6ms가 INPLACE 200.6ms보다 느립니다. 둘 다 딕셔너리만 고치는 연산이라 이 차이는 측정 잡음이에요.)

LOCK=NONE은 정말 통합니다 — 다만 공짜는 아닙니다

가장 중요한 질문이 남았습니다. LOCK=NONE을 걸면 그동안 다른 쿼리가 진짜로 통과하나요.

ALTER가 도는 동안 다른 세션에서 INSERT와 SELECT를 쉬지 않고 던져봤습니다. 그런데 여기서 함정을 하나 만났어요.

라운드를 거듭할수록 처리량이 계속 올랐습니다. 4,411 → 7,118 → 14,216건/초로요. 200만 행을 적재한 직후라 버퍼풀이 계속 데워지는 겁니다. 워밍업 라운드를 늘려도 안 잡혔어요.

그러면 ALTER 구간이 낮게 나와도 그게 ALTER 탓인지 아직 덜 데워져서인지 못 가릅니다.

그래서 ALTER를 안정된 기준선 뒤에 한 번 더 돌렸습니다. 드리프트는 뒤 라운드에 유리하니, 그런데도 낮으면 그건 ALTER 탓이에요.

✅ 실측 (같은 환경. 부하는 저장 프로시저 루프 한 세션이고, ALTER가 실제로 돈 창(7.26초) 안의 표본만 셌습니다. 기준선은 ALTER가 만든 인덱스를 지워 스키마를 맞춘 뒤 쟀어요. 실제 운영의 다중 세션 경합은 이 측정 범위 밖입니다. 재현: ./ch17/concurrent.sh)

7만 건이 통과했고 실패가 0건입니다. LOCK=NONE은 빈말이 아니에요.

(실패를 어떻게 셌냐면 — 프로시저에 에러 핸들러가 없어서, 한 건이라도 실패했다면 루프가 거기서 멈췄을 겁니다. 8초를 완주했으니 0건입니다.)

그런데 공짜도 아닙니다. 처리량이 31% 줄고 p99가 2.2배가 됩니다. 그리고 이 31%는 하한선이에요 — 드리프트가 ALTER 쪽에 유리한 상태에서 잰 값이니까요.

p50은 안 변하는 게 오히려 함정입니다. 평균만 보면 아무 일도 안 일어난 것처럼 보여요.

최대 지연은 정직하게 못 씁니다. ALTER 중 66.7ms인데 기준선에서도 106.2ms가 나왔거든요. 10만 표본 중 한 개짜리 통계라 ALTER와 무관하게 튑니다.

지연이 어디서 오는지는 세 갈래입니다.

📄 문서 기반 (미검증) — 이 표의 셋 중 어느 것이 31%의 몇 %인지는 안 가려냈습니다. 복제본이 있으면 하나 더 붙어요. DDL은 복제 적용기에서 단일 스레드로 다시 실행되니, 복제본은 ALTER 전체 소요시간만큼 통째로 지연됩니다.

세 번째 줄이 다음 장입니다.

16장 질문 2의 답 — 되돌릴 수 있습니다

16장에서 같은 폭의 인덱스가 만드는 방법에 따라 갈리는 걸 봤습니다. INSERT로 유지하면 채움률 67%, ALTER로 나중에 만들면 98%였어요. 그리고 물었습니다 — 이미 헐거워진 인덱스를 다시 빽빽하게 만들 수 있나요?

ALTER TABLE ... FORCE가 그 일을 합니다. 테이블을 제자리에서 재구성하면서 인덱스를 새로 쌓아요.

✅ 실측 (같은 환경. 50만 행, 난수 키 인덱스 — 13·14장의 그 인덱스입니다. 채움률은 14장이 페이지 실사용 바이트로 잰 엔트리 34.8바이트 기준이에요. 재현: ./ch17/rebuild.sh)

1,614장이 1,062장이 됩니다. 3분의 1이 사라져요. 14장에서 본 “분할이 남긴 공기”가 실제로 빠져나간 겁니다.

그리고 1,062장은 13장에서 단조 증가 키로 만들었던 1,063장과 한 장 차이입니다. 재구성은 난수 키가 남긴 자국을 지우고, 처음부터 순서대로 넣었을 때의 자리로 되돌립니다.

OPTIMIZE TABLE도 같은 결과를 냅니다. 세 번째 줄에서 테이블을 다시 헐겁게 만든 뒤 돌린 값이에요 — 처음엔 FORCE로 이미 압축한 테이블에 OPTIMIZE를 돌려놓고 “같은 결과”라고 썼는데, 그건 동어반복이었습니다.

같은 결과가 나오는 이유는 MySQL이 직접 알려줍니다.

mysql> OPTIMIZE TABLE rb2;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| bookdb.rb2| optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| bookdb.rb2| optimize | status   | OK                                                                |
+-----------+----------+----------+-------------------------------------------------------------------+

InnoDB에서 OPTIMIZE TABLE은 재구성으로 매핑됩니다. 그 노트 메시지가 증거예요.

다만 FORCE는 앞의 표에서 26.7초 걸린 그 연산입니다. 200만 행에서 그랬으니 📄 더 큰 테이블에서는 그만큼 더 걸리겠죠(선형인지는 재보지 않았습니다). LOCK=NONE이 되긴 하지만, 위에서 본 31% 처리량 감소를 그 시간 내내 감수해야 합니다.

그래서 실무에서는 “인덱스가 헐거우니 재구성하자”가 자동으로 좋은 선택은 아닙니다. 아끼는 건 디스크와 읽기 페이지 수인데(14장에서 2.03배), 치르는 건 그 시간 동안의 지연이니까요.

그래서 실제로 이렇게 합니다

치기 전에 두 줄은 꼭 봅니다.

-- redo 용량 (8.0.29 이하는 innodb_log_file_size × innodb_log_files_in_group)
SELECT @@innodb_redo_log_capacity/1024/1024 AS redo_mb;
-- 정렬 임시 파일이 데이터와 같은 장치를 쓰는지
SELECT @@innodb_tmpdir, @@tmpdir, @@datadir;

둘 다 빌드 구간의 지연을 미리 줄이는 항목입니다.

정리

• 테이블이 서비스를 계속하는 채로 스키마를 바꾸는 게 온라인 DDL이고, MySQL은 그걸 ALGORITHM(어떻게 수행할지)과 LOCK(동시성을 어디까지 허용할지) 두 절로 표현합니다.
• 이 절들은 요청이 아니라 가드레일입니다. 생략하면 조용히 COPY로 떨어지고, 명시하면 에러로 실패해요.
• 실패는 0.3밀리초에 납니다. 데이터를 안 만지므로 테이블 크기와 무관하고, 에러 메시지가 다음 칸을 알려줍니다(Try ALGORITHM=COPY/INPLACE.).
• 처음엔 이 값을 190ms로 쟀습니다. 셸에서 docker exec를 세 번 호출해 바깥에서 재느라 접속 비용이 섞였어요. 서버 안에서 다시 재서 고쳤습니다.
• 같은 연산인데 알고리즘이 157배를 가릅니다 — 컬럼 추가가 INSTANT 127.5ms, INPLACE 20,057ms예요.
• 인덱스 추가는 INSTANT가 안 됩니다. INPLACE가 최선이고 COPY보다 2.5배 빠릅니다(5,435 대 13,708). 반대로 인덱스 삭제는 4.7ms — 사실상 메타데이터 작업입니다.
• LOCK=NONE은 진짜로 통합니다. ALTER 7.3초 동안 7만 건이 실패 없이 통과했어요.
• 다만 공짜가 아닙니다. 처리량 31% 감소, p99 2.2배(0.05 → 0.11ms). 그리고 이 31%는 하한선입니다 — 드리프트가 ALTER 쪽에 유리한 상태에서 잰 값이니까요.
• p50은 안 변합니다. 평균 대시보드로는 이 일이 안 보여요. 최대 지연은 기준선에서도 튀어서 ALTER에 귀속하지 못했습니다.
• 16장 질문 2의 답: ALTER TABLE ... FORCE로 되돌릴 수 있습니다. 리프가 1,614장 → 1,062장, 채움률 **66% → 100%**예요. 그 1,062는 13장에서 단조 증가 키로 만든 1,063과 한 장 차이입니다.
• OPTIMIZE TABLE도 같은 결과입니다. MySQL이 Table does not support optimize, doing recreate + analyze instead라고 직접 알려줘요.
• 다만 FORCE는 26.7초 걸린 그 연산입니다. 재구성이 자동으로 좋은 선택은 아니에요.

생각해볼 질문

1. 실패가 100ms 안에 나는 건 데이터를 안 건드리기 때문입니다. 그럼 ALTER가 성공한 뒤에 문제를 발견하면 어떻게 되돌릴까요? 되돌리는 비용은 얼마일까요?
2. LOCK=NONE인데도 처리량이 31% 줄었습니다. 이 31%는 세 원인(I/O 경합·redo 압박·MDL) 중 어느 쪽일까요? 어떻게 가려낼 수 있을까요?
3. 이 장의 측정은 라운드가 지날수록 처리량이 계속 오르는 드리프트와 싸웠습니다. ALTER를 기준선 뒤에 한 번 더 돌려서 해결했는데, 이 방법이 안 통하는 경우는 언제일까요?

여기까지가 정상일 때의 이야기입니다. 빌드 구간은 LOCK=NONE이 지켜주고, 앞뒤 경계의 배타적 락은 밀리초에 끝나고요.

못 잡으면 어떻게 될까요.

메타데이터 락은 트랜잭션이 끝나야 풀립니다. 그리고 대기 중인 배타적 요청은 그 뒤에 도착한 모든 요청을 막습니다. 단순 SELECT까지요.

▶ 18장. MDL이 만드는 큐 — LOCK=NONE은 무중단이 아니다