복합 식별 키와 계층 구조

복합 키 한 단계는 읽기 쉽지만, 자식이 다시 부모가 되면 키의 모든 열이 다음 외래 키로 넘어갑니다. 게시글 댓글의 답글과, 답글의 반응까지 식별 관계로 이으면, 마지막 테이블은 업무의 뜻보다 조상의 키를 복사하는 데 더 많은 열을 씁니다.

쌓여 가는 복합 키

아래 예제는 앞 글에서 INSERT한 두 댓글 모델을 부모로 쓰며, 반응의 작성자로 쓸 members의 행도 하나 이상 있어야 합니다. DDL은 문제없이 만들어지지만, 반응 하나를 찾으려면 post_id와 sequence_no, reply_no, reaction_no를 모두 알아야 합니다.

DROP TABLE IF EXISTS reply_reaction_identifying;
DROP TABLE IF EXISTS comment_reply_identifying;

CREATE TABLE comment_reply_identifying (
  post_id BIGINT UNSIGNED NOT NULL,
  sequence_no SMALLINT UNSIGNED NOT NULL,
  reply_no INT UNSIGNED NOT NULL,
  body VARCHAR(500) NOT NULL,
  PRIMARY KEY (post_id, sequence_no, reply_no),
  FOREIGN KEY (post_id, sequence_no)
    REFERENCES post_comment_identifying (post_id, sequence_no)
);
CREATE TABLE reply_reaction_identifying (
  post_id BIGINT UNSIGNED NOT NULL,
  sequence_no SMALLINT UNSIGNED NOT NULL,
  reply_no INT UNSIGNED NOT NULL,
  reaction_no INT UNSIGNED NOT NULL,
  emoji VARCHAR(16) NOT NULL,
  PRIMARY KEY (post_id, sequence_no, reply_no, reaction_no),
  FOREIGN KEY (post_id, sequence_no, reply_no)
    REFERENCES comment_reply_identifying
      (post_id, sequence_no, reply_no)
);

SET @identifying_post_id := (
  SELECT MIN(post_id) FROM post_comment_identifying
);

INSERT INTO comment_reply_identifying
  (post_id, sequence_no, reply_no, body)
VALUES (@identifying_post_id, 1, 1, '복합 키 답글');

INSERT INTO reply_reaction_identifying
  (post_id, sequence_no, reply_no, reaction_no, emoji)
VALUES (@identifying_post_id, 1, 1, 1, '좋아요');

세 단계를 지나자 반응의 대표 기본 키가 네 열이 되었습니다. 댓글의 순번을 바꾸면 그 아래의 참조에도 영향이 갑니다. 이 DDL의 외래 키는 기본 동작이므로 부모 순번의 변경을 거부하고, ON UPDATE CASCADE를 골랐다면 그 변경이 아래의 모든 기본 키와 외래 키로 번졌을 것입니다.

UPDATE post_comment_identifying
SET sequence_no = 2
WHERE post_id = @identifying_post_id
  AND sequence_no = 1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`board_lab`.`comment_reply_identifying`, CONSTRAINT `comment_reply_identifying_ibfk_1` ...)

답글이 기존의 복합 키를 참조하고 있어 거부되었습니다. 이름을 붙이지 않은 외래 키는 테이블_ibfk_번호로 불립니다. 반응을 URL이나 로그에서 가리키려면 네 값이 모두 필요하므로, 반응 하나를 가리키는 짧은 id는 이 모델에 없습니다.

관계가 종속적이라는 것과, 모든 단계의 업무 식별을 조상의 키 전체로 나타내야 한다는 결론은 다릅니다. 아래 단계의 행을 API나 감사, 검토에서 따로 참조한다면 짧고 안정적인 id가 유리합니다. 또 InnoDB의 보조 인덱스 레코드는 행을 찾기 위한 기본 키의 열을 함께 담으므로(7장 1편), 넓은 클러스터형 기본 키는 모든 보조 인덱스의 리프에 들어가 저장 공간으로도 번집니다. 그래서 열의 수만이 아니라 타입의 바이트와 보조 인덱스의 수를 함께 계산합니다.

이 DDL의 고정 길이 정수만 더해 보면 다음과 같습니다. MySQL의 정수 타입 표에 따르면 BIGINT는 8바이트, SMALLINT는 2바이트, INT는 4바이트입니다.

반응의 대표 키 값만 100만 건 늘어놓는다고 하면 (18 − 8) × 1,000,000 = 10,000,000바이트의 차이입니다. 이것은 타입을 더한 조건부의 계산일 뿐 실제 인덱스 공간을 잰 것이 아니며, 레코드와 페이지의 관리 정보, 보조 인덱스와 기본 키 열의 겹침, 대리 모델에 더할 UNIQUE, 다른 업무 속성은 넣지 않았습니다. 물리적인 비교를 하려면 그런 조건까지 같게 맞추어야 합니다.

키의 폭을 볼 때는 단계마다 기본 키의 열과 바이트 수를 표로 만들고, 보조 인덱스의 리프마다 클러스터형 기본 키가 더해지는 비용을 계산합니다. 부모의 순번이나 소유를 바꾸는 일이 아래의 식별을 바꾸는지 보고, 바깥의 링크와 로그가 어떤 키를 전달해야 하는지도 확인합니다.

식별의 뜻과 참조의 폭

comment_id와 reply_id를 대표 기본 키로 두면 아래의 외래 키가 짧아지고, 부모 범위의 순서와 reply_no는 UNIQUE로 지킬 수 있습니다. 종속된 수명은 외래 키와 삭제의 규칙이 나타내므로, 복합 기본 키만이 소유를 나타내는 것은 아닙니다.

대리 키가 모델의 뜻을 약하게 만들지는 않지만, DDL만 보면 부모 범위의 후보 키가 덜 눈에 띌 수 있습니다. 그래서 이름을 붙인 UNIQUE와 ERD의 카디널리티, 용어 사전을 함께 유지합니다. 반응을 따로 된 id로 API나 감사에서 찾는지, 순서를 바꾸는 일이 식별자의 변경이어야 하는지, 보조 인덱스의 수와 클러스터형 기본 키의 바이트를 곱한 크기가 얼마인지, 대리 키로 바꾼 뒤에도 외래 키의 RESTRICT나 CASCADE를 명시했는지를 따져 봅니다.

대표 id와 부모 범위의 키

reply_id와 reaction_id로 참조를 고정하고, (comment_id, reply_no) 같은 업무의 후보 키는 그대로 유지합니다. 아래 DDL에서 답글은 앞 글의 대리 모델 post_comment_surrogate를 comment_id 한 열로 가리키고, 반응도 답글을 reply_id 한 열로 가리킵니다. 댓글 안의 답글 순번은 UNIQUE (comment_id, reply_no)가, 같은 회원이 같은 답글에 같은 반응을 두 번 남기지 못하게 하는 규칙은 UNIQUE (reply_id, member_id, emoji)가 맡습니다. 끝의 INSERT는 첫 댓글에 답글 하나와 그 답글의 반응 하나를 넣고, 답글의 id를 @reply_id에 받아 둡니다.

DROP TABLE IF EXISTS reply_reaction;
DROP TABLE IF EXISTS comment_reply;

CREATE TABLE comment_reply (
  reply_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  comment_id BIGINT UNSIGNED NOT NULL,
  reply_no INT UNSIGNED NOT NULL,
  body VARCHAR(500) NOT NULL,
  PRIMARY KEY (reply_id),
  UNIQUE (comment_id, reply_no),
  FOREIGN KEY (comment_id)
    REFERENCES post_comment_surrogate (comment_id)
);
CREATE TABLE reply_reaction (
  reaction_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  reply_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NOT NULL,
  emoji VARCHAR(16) NOT NULL,
  PRIMARY KEY (reaction_id),
  UNIQUE (reply_id, member_id, emoji),
  FOREIGN KEY (reply_id) REFERENCES comment_reply (reply_id),
  FOREIGN KEY (member_id) REFERENCES members (id)
);

SET @surrogate_parent_comment_id := (
  SELECT MIN(comment_id) FROM post_comment_surrogate
);
SET @reaction_member_id := (SELECT MIN(id) FROM members);

INSERT INTO comment_reply (comment_id, reply_no, body)
VALUES (@surrogate_parent_comment_id, 1, '대리 키 답글');

SET @reply_id := LAST_INSERT_ID();

INSERT INTO reply_reaction (reply_id, member_id, emoji)
VALUES (@reply_id, @reaction_member_id, '좋아요');

반응은 id 하나로 참조되고, 댓글 안의 답글 순번과 같은 회원의 같은 반응이 겹치는 것은 각각의 UNIQUE가 막습니다. 아래 두 INSERT는 앞에서 넣은 답글과 반응을 같은 값으로 한 번씩 더 넣습니다. AUTO_INCREMENT는 두 행에 새 id를 줄 수 있지만 업무의 조합이 겹치므로, 두 문장 모두 거부되어야 합니다.

INSERT INTO comment_reply (comment_id, reply_no, body)
VALUES (@surrogate_parent_comment_id, 1, '중복 답글');

INSERT INTO reply_reaction (reply_id, member_id, emoji)
VALUES (@reply_id, @reaction_member_id, '좋아요');

ERROR 1062 (23000): Duplicate entry '1-1' for key 'comment_reply.comment_id'
ERROR 1062 (23000): Duplicate entry '1-1-좋아요' for key 'reply_reaction.reply_id'

이름을 붙이지 않은 두 UNIQUE는 각각 첫 열의 이름으로 불리며, 메시지에는 조합의 값이 하이픈으로 이어져 나옵니다. 댓글은 comment_id로, 답글은 reply_id로, 반응은 reaction_id로 참조합니다.

대리 키만으로는, 다른 댓글의 답글을 섞는 것 같은 복합 업무의 조건까지 저절로 확인하지 못합니다. 외래 키는 저마다 바로 위의 부모를 가리키므로, 필요한 테넌트나 소유자의 일치 규칙은 복합 UNIQUE와 외래 키나 서비스의 검증으로 보강합니다. 이 모델에서 순번은 보여 주는 순서일 뿐 정체성이 아니므로 바꾸어도 reply_id는 그대로이며, 이런 뜻이 요구와 맞지 않는다면 복합 식별의 방식을 유지합니다.

키의 폭을 잰다

키의 열 수와 인덱스의 크기는 감으로 말하지 말고 메타데이터에서 뽑아, 모델의 결정 기록에 붙입니다. 첫 쿼리는 key_column_usage에서 제약마다 열이 몇 개인지 세고, 둘째 쿼리는 이름에 reaction이 든 테이블의 보조 인덱스 크기를 읽습니다. 두 쿼리 모두 DATABASE()로 지금 연결이 고른 데이터베이스만 보므로, 실행하기 전에 board_lab을 골랐는지 확인합니다.

SELECT table_name, constraint_name, COUNT(*) AS column_count
FROM information_schema.key_column_usage
WHERE table_schema = DATABASE()
  AND table_name IN (
    'comment_reply_identifying', 'reply_reaction_identifying',
    'comment_reply', 'reply_reaction'
  )
GROUP BY table_name, constraint_name
ORDER BY table_name, constraint_name;

SELECT table_name, index_length
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name LIKE '%reaction%';

첫 쿼리는 기본 키와 외래 키뿐 아니라 UNIQUE도 셉니다. 결과를 제약의 종류별로 정리하면 다음과 같습니다.

반응에서 기본 키와 답글을 가리키는 외래 키의 열은 식별 모델이 4와 3, 대리 모델이 1과 1입니다. 다만 두 반응 모델은 키 밖의 업무 속성도 다릅니다. 식별 모델은 reaction_no를, 대리 모델은 member_id와 (reply_id, member_id, emoji)의 UNIQUE를 쓰므로, 행의 수만 맞추어서는 기본 키를 고른 비용만 비교한 것이 되지 않습니다.

index_length는 InnoDB 보조 인덱스 공간의 추정치이며, 환경에 따라 값이 달라 교재에 옮기지 않습니다. 이 값만으로 기본 키까지 포함한 전체 저장 비용이나 성능을 증명할 수는 없으므로, 비교하려면 속성과 제약, 행의 분포, 작업량이 같은 두 모델이 필요합니다. 같은 반응 데이터를 두 모델에 넣어 index_length와 삽입 시간을 비교하고, 부모의 순번을 바꿀 때 아래 외래 키의 ON UPDATE와 대리 id의 고정이 잠그는 범위를 비교하며, 고아와 중복, 다른 테넌트의 참조를 하나씩 시도해 봅니다.

더 깊이: 인덱스 키의 폭에 대한 InnoDB의 한도

복합 키가 넓어지는 데에는 InnoDB가 정한 한도도 있습니다. 여러 열로 된 인덱스에 넣을 수 있는 열은 16개까지이고, 넘으면 오류 1070(Too many key parts specified)이 납니다. 인덱스 키의 길이는 기본 행 형식인 DYNAMIC과 COMPRESSED에서 3072바이트, REDUNDANT와 COMPACT에서 767바이트까지입니다. MySQL 8.4 매뉴얼은 이 한도가 열의 앞부분만 쓰는 접두 인덱스뿐 아니라 열 전체를 쓰는 인덱스 키에도 적용된다고 적으며, 인스턴스를 8KB나 4KB 페이지로 초기화했다면 한도도 1536바이트와 768바이트로 줄어듭니다.

문자열 열의 길이는 글자 수로 정하지만, 이 한도는 바이트로 셉니다. utf8mb4는 한 글자를 1바이트에서 4바이트로 저장하므로, 한글이나 이모지가 들어갈 수 있는 열은 같은 글자 수라도 바이트로는 몇 배가 될 수 있습니다. 식별 관계로 부모의 문자열 자연 키를 자식마다 물려받으면 세대가 내려갈수록 기본 키가 이 한도에 가까워지고, 그 바이트는 7장 1편에서 본 것처럼 모든 보조 인덱스에도 복제됩니다. 이 글의 식별 모델이 문자열 대신 숫자인 게시글 id와 순번만 물려받게 한 것도 키의 폭을 작게 유지하는 선택입니다.

키 전략의 예외와 비교

넓은 자연 키가 클러스터형 기본 키이면 보조 인덱스의 리프에도 복제됩니다. 반대로 대리 기본 키를 더하면 조합의 UNIQUE라는 별도의 B+Tree가 생기므로, 대리 키가 언제나 공간을 줄이는 것은 아닙니다. 실제 열의 폭과 인덱스의 수로 계산합니다.

식별 계층에는 파티션이나 테넌트의 범위를 자연스럽게 담는 장점도 있습니다. tenant_id를 모든 키에 넣어 다른 테넌트끼리의 참조를 구조적으로 막는다면, 전파의 비용과 보안의 이득을 함께 평가합니다.

AUTO_INCREMENT의 id를 바깥에 드러낼 때, 추측하기 쉽다는 문제를 권한 검사가 대신 해결해 주지는 않습니다. UUID는 분산해서 만들 수 있는 장점과, 클러스터형 인덱스에서 값이 흩어지는 비용을 함께 비교합니다. 순번을 다시 쓸 수 있다면 지워진 reply_no와 감사 이력의 뜻을 정의하고, 이모지의 정렬 규칙이 서로 다른 표현을 같은 값으로 보는지 UNIQUE의 뜻도 시험합니다.

키를 이관할 때는 새 id를 채운 뒤 두 키의 UNIQUE와 외래 키를 함께 검증하고, 애플리케이션의 읽기와 쓰기를 차례로 바꿉니다. 기본 키를 한 번에 바꾸지 않습니다. 외래 키 열의 타입과 부호는 정확히 같아야 하며, 큰 ALTER가 온라인으로 가능한지와 복제 서버의 지연을 확인합니다.

복합 키의 뜻이 분명한 장점과 대리 키의 참조가 안정적인 장점을, 실제의 깊이와 인덱스, 변경의 작업량으로 비교합니다. "요즘 방식"이라는 이유만으로 모든 복합 키를 없애지는 않습니다.

정리

• 식별 키는 아래 관계로 갈 때마다 열과 인덱스의 폭을 넓힙니다.
• 대리 id는 참조를 짧게 만들지만, 부모 범위의 업무 UNIQUE를 대신하지는 않습니다.
• 재정렬과 바깥의 URL, 감사는 안정적인 자식 id를 둘 이유가 됩니다.
• 키의 전략은 메타데이터와 작업량으로 비용을 재어 결정합니다.

연습 문제

1. 점검 항목과 답글

점검 계획 plan_item의 항목 목록과 그 답글 checklist_reply의 두 단계를 설계해 보세요. 항목의 순서는 바뀔 수 있고, 답글은 바깥의 알림에서 참조됩니다. 키의 전략을 고르고 후보 키를 지킵니다.

DROP TABLE IF EXISTS checklist_reply;
DROP TABLE IF EXISTS concept_checklist_item;
DROP TABLE IF EXISTS plan_item;

CREATE TABLE plan_item (
  plan_item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(200) NOT NULL,
  PRIMARY KEY (plan_item_id)
) ENGINE = InnoDB;

CREATE TABLE concept_checklist_item (
  checklist_item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  plan_item_id BIGINT UNSIGNED NOT NULL,
  sequence_no INT UNSIGNED NOT NULL,
  body VARCHAR(500) NOT NULL,
  PRIMARY KEY (checklist_item_id),
  UNIQUE (plan_item_id, sequence_no),
  FOREIGN KEY (plan_item_id) REFERENCES plan_item (plan_item_id),
  CHECK (sequence_no >= 1)
) ENGINE = InnoDB;

CREATE TABLE checklist_reply (
  reply_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  checklist_item_id BIGINT UNSIGNED NOT NULL,
  author_id BIGINT UNSIGNED NOT NULL,
  body VARCHAR(500) NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (reply_id),
  FOREIGN KEY (checklist_item_id)
    REFERENCES concept_checklist_item (checklist_item_id)
) ENGINE = InnoDB;

2. 깊이가 한 단계 더 늘면

본문의 식별 계층 아래에 반응의 신고 reaction_report를 식별 관계로 하나 더 두면, 신고의 기본 키는 몇 열이 되고 정수 값 부분은 몇 바이트가 되나요? 신고 번호는 INT로 둡니다.

다음 글 공유 기본 키와 연결 테이블에서는 일대일의 공유 기본 키와 다대다의 연결 키처럼, 식별 관계가 오히려 간결한 경우를 비교합니다.