운영 심화: 안전한 스키마 변경

CREATE TABLE은 빈 구조를 만들지만, 운영 중인 스키마에는 이미 데이터가 있고 그 데이터를 쓰는 프로그램들이 돌고 있습니다. 새 필수 열을 한 번에 더하면 기존 행에 넣을 값이 없거나, 데이터베이스가 의도하지 않은 기본값을 채울 수 있습니다. 그래서 스키마를 바꿀 때는 최종 모양만이 아니라, 기존 데이터가 그 모양으로 옮겨 가는 순서를 함께 설계해야 합니다.

이 글에서는 게시글을 등록하는 요청이 재시도로 두 번 들어와도 게시글이 두 개 생기지 않도록 하는 request_key 열을 더합니다. 기존 게시글에는 요청 키가 없으므로, 먼저 NULL을 허용하는 열을 더하고, 기존 행에 legacy-{id}를 채운 뒤, 빈 값과 중복이 0건임을 확인하고 나서 NOT NULL과 UNIQUE를 겁니다. 기존 행에 업무상 근거가 있는 값을 채워 넣는 작업을 백필(backfill)이라고 합니다. 처음 읽는다면 SQL을 순서대로 따라가고, 온라인 DDL의 비용은 기본 DML을 익힌 뒤 다시 읽어도 됩니다.

열만 더하고 멈추면

데이터가 있는 posts에 NULL을 허용하는 열을 더하고 거기서 작업을 끝냈다고 해 봅시다. 아래 ALTER TABLE은 version 열 뒤에 길이 64의 request_key를 NULL 허용으로 더하고, 이어지는 SELECT는 전체 행 수와 그 가운데 request_key가 NULL인 행 수를 함께 셉니다. SUM(request_key IS NULL)은 조건이 참인 행마다 1을 더하므로 NULL인 행의 수가 됩니다.

ALTER TABLE posts
  ADD COLUMN request_key VARCHAR(64) NULL AFTER version;

SELECT COUNT(*) AS total_rows, SUM(request_key IS NULL) AS null_rows
FROM posts;

total_rows | null_rows
1          | 1

이 결과는 1장의 기본 게시글 하나만 저장한 경우이며, 1장의 연습 문제까지 실행했다면 두 수가 더 크게 나옵니다. 어느 경우든 null_rows가 total_rows와 같습니다. 열은 만들어졌지만 모든 기존 행의 request_key가 NULL이고, 새로 쓰는 프로그램도 아직 값을 보내지 않을 수 있습니다. 열을 넓히는 것은 안전한 출발일 뿐, 데이터를 옮기고 최종 제약을 걸지 않으면 업무상 필수인 열이 되지 않습니다.

스키마 변경은 새 열의 선언, 기존 행의 변환, 쓰는 프로그램의 전환, 최종 제약의 네 가지 문제입니다. MySQL의 DDL은 실행 전후로 암묵적으로 커밋되므로, 트랜잭션의 DML처럼 마음대로 되돌릴 수 있다고 가정하면 안 됩니다. 그래서 단계마다 시작하기 전의 조건과 끝났음을 확인할 방법을 정해 둡니다.

넓히고, 옮기고, 좁힌다

여러 버전의 애플리케이션이 동시에 돌 수 있다면, 단계마다 예전 버전과 새 버전이 모두 정상으로 동작하는지 확인해야 합니다. 이를 위해 스키마를 먼저 넓혀(expand) 예전 버전도 그대로 쓸 수 있게 하고, 데이터와 쓰는 프로그램을 새 규칙으로 옮긴(migrate) 뒤, 모두 옮겨졌음을 확인하고 나서 제약을 좁힙니다(contract).

ALTER TABLE 자체는 표준 SQL이지만, 그것을 어떻게 실행하느냐는 MySQL의 동작입니다. MySQL 8.4는 변경의 종류에 따라 메타데이터만 바꾸는 ALGORITHM=INSTANT, 테이블 데이터를 복사하지 않고 그 자리에서 처리하되 변경에 따라 테이블을 다시 만들기도 하는 INPLACE, 새 테이블에 복사하는 COPY를 고르며, 요청한 방식을 그 변경이 지원하지 않으면 오류로 알려 줍니다(MySQL 8.4의 온라인 DDL). 작은 실습 테이블에서 순식간에 끝난 변경이 큰 운영 테이블에서도 그렇다고 기대하지 않습니다.

기존 행을 채우고 확인한 뒤 강화한다

앞 절에서 더한 NULL 허용 열에 기존 게시글의 기본 키로 만든 값을 채웁니다. 새 요청의 키에는 legacy-로 시작하는 값을 쓰지 않는다는 약속을 전제로 합니다. 다른 프로그램이 이미 키를 쓰고 있다면 그 값과 겹치지 않는지도 확인해야 합니다.

UPDATE posts
SET request_key = CONCAT('legacy-', id)
WHERE request_key IS NULL;

SELECT
  COUNT(*) AS total_rows,
  SUM(request_key IS NULL OR TRIM(request_key) = '') AS invalid_rows,
  COUNT(*) - COUNT(DISTINCT author_id, request_key) AS duplicate_rows
FROM posts;

invalid_rows가 0인지 먼저 보고, 그다음에 duplicate_rows가 0인지 봅니다. 두 열을 함께 세는 COUNT(DISTINCT author_id, request_key)는 NULL이 든 조합을 세지 않으므로, NULL이 남아 있으면 그 행도 차이에 들어가 중복처럼 보이기 때문입니다. 테이블이 비어 있다면 SUM의 결과는 NULL이므로 total_rows가 0인 것과 함께 읽습니다. 두 값이 모두 0인 것을 확인한 다음에만 제약을 강화합니다. SQL에는 확인 결과를 보고 저절로 멈추는 분기가 없으므로, 사람이나 마이그레이션 도구가 이 결과를 보고 다음 문장으로 넘어가야 합니다.

ALTER TABLE posts
  MODIFY request_key VARCHAR(64) NOT NULL,
  ADD CONSTRAINT ck_posts_request_key
    CHECK (CHAR_LENGTH(TRIM(request_key)) BETWEEN 1 AND 64),
  ADD CONSTRAINT uq_posts_author_request
    UNIQUE (author_id, request_key);

제약을 강화하고 나면 앞으로의 모든 등록 요청은 키를 보내야 하고, 같은 회원이 같은 키로 다시 등록하면 UNIQUE가 두 번째 저장을 거부합니다. 키는 회원마다 유일하면 되므로 (author_id, request_key)의 조합에 UNIQUE를 걸었습니다.

legacy-{id}는 과거의 네트워크 요청을 되살린 값이 아니라, 기존 행이 새 규칙을 지키도록 만든 전환용 값입니다. 앞으로의 키는 HTTP 요청마다 새로 생기는 ID가 아니라, 클라이언트가 같은 등록을 재시도하는 동안 그대로 유지하는 값이어야 합니다. 이 키를 쓰는 방법은 다음 글과 8장 2편에서 봅니다. 운영의 큰 테이블이라면 백필 UPDATE 하나가 많은 언두 로그와 복제 지연, 잠금을 만들 수 있으므로, 기본 키의 범위를 나누어 조금씩 채우고 진행 상황을 지켜봅니다.

더 깊이: 단계마다 InnoDB가 하는 일의 양

같은 ALTER TABLE이라도 단계마다 InnoDB가 하는 일의 양은 크게 다릅니다. NULL을 허용하는 열을 더하는 첫 문장은 MySQL 8.4에서 기본 알고리즘이 INSTANT이고 메타데이터만 바꾸므로, 행이 아무리 많아도 바로 끝납니다. 연습 문제 3의 기본값 변경도 데이터 사전의 기본값만 바꾸는 작업입니다. 반면 열을 NOT NULL로 바꾸는 MODIFY는 테이블을 그 자리에서 다시 만드는(rebuild) 작업이라 데이터를 크게 재배치하므로 비용이 큽니다. 이 작업은 엄격 모드에서만 성공하고, NULL인 행이 하나라도 있으면 실패합니다.

보조 인덱스를 더하는 작업은 테이블을 다시 만들지 않고, 인덱스를 만드는 동안에도 읽기와 쓰기를 허용합니다. 다만 그 테이블을 쓰고 있는 트랜잭션이 모두 끝나야 문장이 완료되며, UNIQUE 인덱스라면 중복 값이 없는지도 확인해야 합니다. 그래서 강화 단계는 열을 더하는 단계보다 훨씬 오래 걸린다고 보고, 운영에서는 트래픽이 적은 시간에 실행합니다.

INSTANT로 열을 더하거나 지울 때마다 테이블의 행 버전(row version)이 하나씩 늘어나며, 그 수는 INFORMATION_SCHEMA.INNODB_TABLES의 TOTAL_ROW_VERSIONS로 확인할 수 있습니다. MySQL 8.4가 허용하는 행 버전은 64개까지이고(9.1.0부터는 255개), 한도에 이르면 INSTANT 방식의 ADD COLUMN과 DROP COLUMN이 오류로 거부되어 COPY나 INPLACE로 테이블을 다시 만들어야 합니다. 테이블을 다시 만드는 ALTER TABLE이나 OPTIMIZE TABLE을 실행하면 이 값은 0으로 돌아갑니다. ROW_FORMAT=COMPRESSED를 쓰거나 FULLTEXT 인덱스가 있는 테이블에는 INSTANT로 열을 더할 수 없다는 제한도 있습니다.

스키마와 데이터를 함께 확인한다

SHOW CREATE TABLE만 보지 않고, 열의 NULL 허용과 기본값을 메타데이터에서 확인하고 실제 값의 분포도 함께 봅니다. 첫 쿼리는 information_schema.columns에서 request_key 한 열의 타입, NULL 허용, 기본값만 고르고, 둘째 쿼리는 회원별로 게시글 수와 서로 다른 키의 수를 나란히 셉니다. 두 수를 나란히 두는 이유는, 같은 회원 안에서 키가 겹치면 COUNT(DISTINCT request_key)만 작아지기 때문입니다.

SELECT column_name, column_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'board_lab' AND table_name = 'posts' AND column_name = 'request_key';

SELECT author_id, COUNT(*) AS posts, COUNT(DISTINCT request_key) AS request_keys
FROM posts
GROUP BY author_id
ORDER BY author_id;

request_key의 is_nullable은 NO, 기본값은 NULL(정해진 기본값 없음)이어야 하고, 회원마다 게시글의 수와 서로 다른 키의 수가 같아야 합니다. 스키마와 데이터 가운데 하나만 맞아서는 마이그레이션이 끝난 것이 아닙니다. 이어서 공백만 든 키를 넣어 CHECK 위반 3819를, 같은 회원의 기존 키를 다시 넣어 중복 오류 1062를 확인해 봅니다.

지우는 명령과 검사를 끄는 명령

DROP TABLE은 구조와 데이터를 함께 지우고, TRUNCATE TABLE은 구조를 남긴 채 모든 행을 빠르게 비웁니다. 둘 다 DDL로 취급되어 암묵적으로 커밋되므로, 일반 DELETE처럼 ROLLBACK으로 되돌리거나 행마다 트리거를 실행한다고 보면 안 됩니다. TRUNCATE는 다른 테이블의 외래 키가 이 테이블을 가리키고 있으면 실패하기도 합니다.

SET FOREIGN_KEY_CHECKS = 0은 지우기 어려운 행을 지우는 해결책이 아니라 무결성 검사를 건너뛰는 것입니다. 이 상태에서 넣거나 지운 행은 검사를 다시 켜도 다시 검사되지 않으므로, 부모가 없는 자식 행이 소리 없이 남을 수 있습니다. 참조의 순서와 수명 주기를 설계하고, 실습 데이터를 초기화할 때는 따로 만든 일회용 스키마에서만 합니다. 열을 지우는 DROP COLUMN도 그 열을 읽던 쿼리가 모두 바뀐 뒤, 좁히는 단계에서 합니다.

운영에서 함께 볼 것

DDL을 실행하기 전에는 테이블의 크기, 오래 열려 있는 트랜잭션, 복제본의 지연, 메타데이터 잠금을 기다리는 세션, 예상되는 알고리즘을 확인합니다. 오래 열린 트랜잭션 하나가 테이블을 쓰고 있으면 DDL이 메타데이터 잠금을 기다리고, 그 뒤로 들어온 모든 쿼리가 줄줄이 멈추는 일이 생깁니다. 마이그레이션 도구는 어느 단계에서 실패했고 다시 실행해도 되는지를 기록해야 합니다.

MySQL 8.0부터 DDL 문장 하나는 원자적으로 실행되어, 중간에 서버가 죽어도 반쯤 바뀐 상태로 남지 않습니다. 하지만 여러 문장으로 이루어진 마이그레이션 전체를 자동으로 되돌려 주지는 않으므로, 단계마다 두 번 실행해도 안전하거나, 상태를 확인한 뒤 이어서 할 수 있게 써 둡니다. 기본값을 바꾸는 것도 기존 행을 다시 쓰지 않고, 그 뒤에 열을 생략하거나 DEFAULT를 명시한 문장에만 적용된다는 점을 기억해 둡니다.

열의 이름을 바꾸는 것은 그 열을 쓰는 쿼리와 뷰, 데이터를 옮기는 작업(ETL), 대시보드까지 모두 찾아본 뒤에 합니다. 이 단계적인 변경 방식은 7장에서 인덱스를 만들 때, 10장 3편에서 기존 게시글에 카테고리 열을 더할 때, 12장에서 물리 설계를 바꿀 때 되풀이됩니다.

정리

• 데이터가 있는 테이블의 스키마는 넓히고, 옮기고, 좁히는 단계로 바꿉니다.
• NULL을 허용하는 열을 더하고, 기존 행을 채우고, 빈 값과 중복이 없음을 확인한 뒤 NOT NULL과 제약을 겁니다.
• DDL의 알고리즘과 잠금, 암묵적 커밋을 운영의 비용으로 보고, 여러 문장의 마이그레이션은 단계마다 다시 실행할 수 있게 씁니다.
• DROP, TRUNCATE, 외래 키 검사를 끄는 일을 평범한 수정 도구처럼 쓰지 않습니다.

연습 문제

1. 선택 입력인 열

posts에 선택 입력인 legacy_slug VARCHAR(100)을 더하려 합니다. 기존 행은 NULL로 두고, 값이 있을 때만 빈 문자열이 아니어야 합니다. 어떻게 더하고 확인하면 될까요?

ALTER TABLE posts
  ADD COLUMN legacy_slug VARCHAR(100) NULL AFTER request_key;

ALTER TABLE posts
  ADD CONSTRAINT ck_posts_legacy_slug
    CHECK (legacy_slug IS NULL OR CHAR_LENGTH(TRIM(legacy_slug)) > 0);

SELECT column_name, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'board_lab' AND table_name = 'posts' AND column_name = 'legacy_slug';

2. 백필과 경쟁하는 쓰기

백필 UPDATE를 마치고 확인 쿼리에서 invalid_rows가 0인 것을 본 직후, 아직 바뀌지 않은 예전 버전의 프로그램이 request_key 없이 게시글을 하나 더 넣었습니다. 그다음에 실행한 강화 ALTER TABLE은 어떻게 되나요? 이런 일을 막으려면 무엇을 확인해야 하나요?

3. 기본값을 바꾸면

posts.status의 기본값을 'DRAFT'에서 'HIDDEN'으로 바꾸는 ALTER TABLE ... ALTER COLUMN status SET DEFAULT 'HIDDEN'을 실행했습니다. 기존에 상태가 'DRAFT'로 저장된 행들은 어떻게 되나요?

다음 글 INSERT와 다중 행 등록에서는 준비한 request_key를 포함해 회원과 게시글을 넣고, 열을 생략했을 때와 NULL을 넣었을 때의 차이, 여러 행을 한 번에 넣는 방법을 다룹니다.