역정규화와 집계 데이터

역정규화(denormalization)는 정규화가 틀렸다는 뜻이 아니라, 측정한 읽기 병목을 줄이려고 파생된 사실을 복제하는 결정입니다. 복제하는 순간부터 원래 데이터는 무엇인지, 누가 갱신하는지, 어느 시각까지 반영했는지, 어긋나면 어떻게 복구하는지, 일치를 무엇으로 확인하는지가 필요해집니다.

예제는 프로필 카드가 회원별 공개 게시글의 총조회수를 매번 SUM으로 계산하느라 느려졌다고 가정합니다. 그래서 요약 테이블에 total_views를 저장하지만, 새 게시글의 INSERT와 요약의 UPDATE가 서로 다른 트랜잭션이라면 둘 사이에서 불일치가 생깁니다.

원본과 요약을 따로 갱신하면

게시글을 INSERT한 뒤 요약을 UPDATE하기 전에 작업자가 종료되는 상황을 만듭니다. 아래 SQL은 실제로 프로세스를 끝내지 않고, 요약의 UPDATE를 주석으로 남겨 같은 결과를 재현합니다.

DROP TABLE IF EXISTS summary_drift_demo;
DROP TABLE IF EXISTS post_summary_drift_demo;

CREATE TABLE post_summary_drift_demo (
  post_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  author_id BIGINT UNSIGNED NOT NULL,
  view_count BIGINT UNSIGNED NOT NULL,
  status VARCHAR(16) NOT NULL
);
CREATE TABLE summary_drift_demo (
  member_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  total_views BIGINT UNSIGNED NOT NULL
);

INSERT INTO post_summary_drift_demo
  (author_id, view_count, status)
VALUES (1, 50, 'PUBLISHED');
INSERT INTO summary_drift_demo VALUES (1, 50);

INSERT INTO post_summary_drift_demo
  (author_id, view_count, status)
VALUES (1, 40, 'PUBLISHED');
-- 여기서 작업자가 종료되어 아래 동기화 문장은 실행되지 않았다고 가정한다.
-- UPDATE summary_drift_demo
-- SET total_views = total_views + 40
-- WHERE member_id = 1;

SELECT
  SUM(s.view_count) AS source_views,
  summary.total_views AS cached_views,
  CAST(summary.total_views AS SIGNED) - SUM(s.view_count) AS drift_views
FROM post_summary_drift_demo AS s
JOIN summary_drift_demo AS summary ON summary.member_id = s.author_id
WHERE s.status = 'PUBLISHED'
GROUP BY summary.member_id, summary.total_views;

source_views | cached_views | drift_views
90           | 50           | -40

자동 커밋이 켜진 연결에서는 조회수 40의 INSERT가 바로 커밋되고, 요약은 이전의 50에 머뭅니다. 화면은 50을, 원래 데이터의 합계는 90을 보여 주므로 어느 숫자가 사실인지 요약만 보아서는 알 수 없습니다. 부호 없는 정수끼리의 뺄셈은 결과가 음수이면 오류 1690으로 실패하므로, 차이를 구할 때는 요약 값을 SIGNED로 바꾸어 뺍니다.

이 불일치에 대해 SQL은 아무 오류도 내지 않습니다. CHECK나 외래 키는 파생값이 원래 데이터의 SUM과 같다는 것을 강제할 수 없기 때문입니다. 그래서 같은 트랜잭션의 동기화와 이벤트 기반의 비동기 갱신, 주기적인 재계산 가운데 하나를 고르고 복구 방법까지 설계해야 합니다.

INSERT만 합계를 바꾸는 것도 아닙니다. 상태의 변경과 조회수의 수정, 삭제도 합계에 영향을 주므로, INSERT 경로에만 요약의 갱신을 이어 두면 처음에는 맞다가 운영하는 동안 불일치가 쌓입니다. 그래서 원래 데이터의 정의(공개 상태와 삭제 정책, 시간의 범위)를 SUM의 조건으로 적고, INSERT와 UPDATE, DELETE, 복구용 가져오기까지 변경 경로를 모두 나열합니다. 요약이 원래 데이터의 어느 시각까지 반영했는지 함께 저장하고, 요약을 버린 뒤 원래 데이터만으로 다시 만들 수 있는지 확인합니다.

다시 만들 수 있는 파생 모델

요약 한 행의 후보 키는 member_id이고, total_views뿐 아니라 published_count와 source_cutoff_at, rebuilt_at을 함께 둡니다. 평균만 저장하면 다시 집계할 수 없으므로 합계와 건수를 저장합니다.

동기화 방식은 요구에 따라 고릅니다. 실시간으로 강하게 일치해야 하고 변경량이 작으면 같은 트랜잭션에서, 약간의 지연을 허용하면 아웃박스(outbox)와 이벤트 작업자로, 대량의 분석이라면 배치 재생성으로 처리합니다. 아웃박스는 게시글을 바꾸는 트랜잭션 안에서 '요약을 고쳐야 한다'는 이벤트 행을 함께 기록해 두는 테이블이고, 이벤트 작업자는 커밋된 이벤트 행을 읽어 요약에 반영하는 별도의 프로세스입니다. 작업자가 도중에 멈추어도 이벤트 행이 남아 있어 다시 처리할 수 있으며, 이 방식이 재시도를 안전하게 만드는 이유는 지속 저장과 안전한 재시도에서 다룹니다. 어떤 방식을 골라도 원래 데이터와의 정기적인 일치 확인을 대신하지는 못합니다.

결정할 때는 화면이 허용하는 신선도의 지연을 수치로 정하고, 모든 게시글의 변경이 요약 행 하나에 잠금을 집중시키지 않는지 봅니다. 재처리할 때는 event_id나 버전으로 같은 변경이 두 번 적용되지 않게 하고, 분쟁과 복구에서는 정규화된 원래 데이터를 판단의 기준으로 둡니다.

더 깊이: 생성 열이 맡을 수 있는 파생값과 맡을 수 없는 파생값

같은 행 안의 값으로 계산되는 파생값이라면 MySQL의 생성 열(generated column)에 맡길 수 있습니다. VIRTUAL 생성 열은 값을 저장하지 않고 행을 읽을 때 BEFORE 트리거 바로 뒤에 계산하며, STORED 생성 열은 행을 넣거나 바꿀 때 계산해 저장합니다. 키워드를 생략하면 VIRTUAL입니다. 어느 쪽이든 데이터베이스가 정의한 식으로 값을 만들기 때문에, 원래 값과 파생값이 따로 갱신되어 어긋나는 일이 생기지 않습니다.

하지만 이 글의 회원별 총조회수는 생성 열이 될 수 없습니다. 생성 열의 식에는 리터럴과 연산자, 결정적인 내장 함수만 쓸 수 있고, 서브쿼리와 저장 함수, 변수는 쓸 수 없기 때문입니다. 다른 테이블의 여러 행을 SUM한 값은 이런 식으로 나타낼 수 없으므로, 요약 테이블과 동기화 전략이 필요해집니다. 또 생성 열은 같은 테이블에서 자기보다 앞에 정의된 생성 열만 참조할 수 있고, AUTO_INCREMENT 열을 기반으로 쓸 수 없습니다.

MySQL 8.4 매뉴얼은 VIRTUAL 열을 여러 쿼리가 똑같은 조건을 쓰게 하는 데, STORED 열을 계산 비용이 큰 조건의 캐시로 쓰는 데 활용할 수 있다고 설명합니다. 생성 열에 인덱스를 만들어 두면, 쿼리가 열 이름 대신 같은 식을 써도 옵티마이저가 그 인덱스를 쓸 수 있습니다. 같은 행의 파생값은 생성 열로, 여러 행이나 다른 테이블에 걸친 파생값은 이 글의 재생성 방식으로 나누어 맡기면 됩니다.

멱등한 요약 재생성

증분 최적화보다 정확한 재생성 쿼리를 먼저 만듭니다. 예제의 원본은 회원 두 명과 게시글 세 개이며, 요약에는 일부러 잘못된 캐시를 넣어 둡니다.

DROP TABLE IF EXISTS member_post_summary_demo;
DROP TABLE IF EXISTS summary_post_fixture;
DROP TABLE IF EXISTS summary_member_fixture;

CREATE TABLE summary_member_fixture (
  id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(80) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE summary_post_fixture (
  id BIGINT UNSIGNED NOT NULL,
  author_id BIGINT UNSIGNED NOT NULL,
  view_count BIGINT UNSIGNED NOT NULL,
  status VARCHAR(16) NOT NULL,
  published_at DATETIME(6) NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (author_id) REFERENCES summary_member_fixture (id)
);

INSERT INTO summary_member_fixture (id, name)
VALUES (1, '안동민'), (2, '김소라');

INSERT INTO summary_post_fixture
  (id, author_id, view_count, status, published_at)
VALUES
  (1, 1, 50, 'PUBLISHED', '2026-07-10 10:00:00'),
  (2, 1, 40, 'PUBLISHED', '2026-07-11 11:00:00'),
  (3, 2, 20, 'DRAFT', NULL);

CREATE TABLE member_post_summary_demo (
  member_id BIGINT UNSIGNED PRIMARY KEY,
  published_count INT UNSIGNED NOT NULL DEFAULT 0,
  total_views BIGINT UNSIGNED NOT NULL DEFAULT 0,
  source_cutoff_at DATETIME(6) NOT NULL,
  rebuilt_at DATETIME(6) NOT NULL,
  CONSTRAINT fk_summary_demo_member FOREIGN KEY (member_id)
    REFERENCES summary_member_fixture (id)
    ON DELETE CASCADE
);

-- 재구축 전에 잘못 남은 캐시를 일부러 넣는다.
INSERT INTO member_post_summary_demo
  (member_id, published_count, total_views, source_cutoff_at, rebuilt_at)
VALUES
  (1, 1, 50, '2026-07-15 00:00:00', '2026-07-14 00:00:00'),
  (2, 9, 999, '2026-07-15 00:00:00', '2026-07-14 00:00:00');

먼저 원래 데이터와 요약을 회원별로 비교해 어긋난 행을 찾습니다. MySQL에는 완전 외부 JOIN(FULL OUTER JOIN)이 없으므로, 회원을 기준으로 LEFT JOIN해 게시글이 0건인 회원과 요약 행이 아예 없는 회원까지 포함합니다.

WITH source_total AS (
  SELECT m.id AS member_id,
         COUNT(p.id) AS published_count,
         COALESCE(SUM(p.view_count), 0) AS total_views
  FROM summary_member_fixture AS m
  LEFT JOIN summary_post_fixture AS p
    ON p.author_id = m.id
   AND p.status = 'PUBLISHED'
   AND p.published_at < '2026-07-15 00:00:00'
  GROUP BY m.id
)
SELECT src.member_id,
       src.published_count AS source_count,
       COALESCE(sumry.published_count, 0) AS cached_count,
       src.total_views AS source_views,
       COALESCE(sumry.total_views, 0) AS cached_views
FROM source_total AS src
LEFT JOIN member_post_summary_demo AS sumry
  ON sumry.member_id = src.member_id
WHERE sumry.member_id IS NULL
   OR src.published_count <> COALESCE(sumry.published_count, 0)
   OR src.total_views <> COALESCE(sumry.total_views, 0)
ORDER BY src.member_id;

member_id | source_count | cached_count | source_views | cached_views
1         | 2            | 1            | 90           | 50
2         | 0            | 9            | 0            | 999

두 회원 모두 어긋났습니다. 요약 행 자체가 빠진 회원은 집계가 0이어도 sumry.member_id IS NULL 조건으로 찾으며, 이 불일치 쿼리의 결과 행 수를 배치의 지표로 씁니다.

이제 원래 데이터의 집계값으로 요약을 덮어씁니다. 기존 요약에 더하는 것이 아니라, 원래 데이터에서 계산한 값으로 바꾸는 것입니다.

INSERT ... ON DUPLICATE KEY UPDATE에서 새 행의 값을 VALUES(열)로 참조하는 문법은 MySQL 8.4에서 아직 동작하지만 폐기 예정입니다. AS new 같은 행 별칭은 VALUES와 SET 형식에만 쓰는 문법이고, GROUP BY를 쓰는 SELECT의 열도 UPDATE 절에서 바로 참조할 수 없습니다. 그래서 공식 문서가 안내하는 대로 집계 SELECT를 파생 테이블로 감싸고, 그 열 별칭을 UPDATE 절에서 참조합니다(INSERT ... ON DUPLICATE KEY UPDATE).

INSERT INTO member_post_summary_demo
  (member_id, published_count, total_views, source_cutoff_at, rebuilt_at)
SELECT * FROM (
  SELECT
    m.id AS src_member_id,
    COUNT(p.id) AS src_count,
    COALESCE(SUM(p.view_count), 0) AS src_views,
    '2026-07-15 00:00:00.000000' AS src_cutoff,
    CURRENT_TIMESTAMP(6) AS src_rebuilt_at
  FROM summary_member_fixture AS m
  LEFT JOIN summary_post_fixture AS p
    ON p.author_id = m.id
   AND p.status = 'PUBLISHED'
   AND p.published_at < '2026-07-15 00:00:00.000000'
  GROUP BY m.id
) AS src
ON DUPLICATE KEY UPDATE
  published_count = src_count,
  total_views = src_views,
  source_cutoff_at = src_cutoff,
  rebuilt_at = src_rebuilt_at;

SELECT member_id, published_count, total_views
FROM member_post_summary_demo
ORDER BY member_id;

member_id | published_count | total_views
1         | 2               | 90
2         | 0               | 0

회원 1의 공개 글 두 개는 건수 1 + 1 = 2와 조회수 50 + 40 = 90을 보탭니다. 회원 2의 DRAFT 글은 JOIN의 ON 조건에서 빠지므로 COUNT(p.id)는 0, SUM(p.view_count)는 NULL이 되고, COALESCE가 NULL을 0으로 바꿉니다. 여기서 COUNT(*)를 쓰면 LEFT JOIN이 남긴 회원 2의 행 자체를 세어 1건이 되므로 이 집계에 맞지 않습니다.

재생성을 마친 뒤 앞의 진단 쿼리를 다시 실행하면 빈 결과가 나옵니다. 같은 원본과 기준 시각으로 재생성을 다시 실행해도 건수와 조회수는 더해지지 않고, rebuilt_at만 실행한 시각으로 바뀝니다. 0건인 회원을 API가 "0"으로 보여 줄지 "행 없음"으로 보여 줄지는 따로 정하고, 탈퇴한 회원의 요약은 ON DELETE CASCADE로 함께 지웁니다.

멱등성(idempotency)은 요약을 90에 고정한다는 뜻이 아닙니다. 그 뒤 게시글 2의 조회수만 40에서 45로 고치면, 같은 기준 시각으로 재생성해도 원래 데이터의 합계는 95가 됩니다. 원래 데이터가 같을 때 재처리가 값을 두 번 더하지 않는 것과, 바뀐 원래 데이터를 다시 반영하는 것을 구분합니다. 고정한 기준 시각만으로는 나중에 고쳐질 수 있는 원래 데이터의 스냅샷이 만들어지지도 않습니다.

동기화 전략의 비교

증분 카운터는 같은 회원의 요약 행 하나에 쓰기가 몰릴 수 있습니다. 시간 버킷, 이벤트를 쌓은 뒤의 비동기 집계, 나누어 둔 카운터를 검토할 수 있지만, 읽기의 목표가 실제로 요구할 때만 그 복잡도를 더합니다. 늦게 도착한 이벤트와 수정, 삭제까지 처리하려면 조회수 증가 이벤트만으로는 부족하고, 원래 데이터의 버전이나 수정 이벤트, 영향받은 버킷의 재생성이 필요합니다.

집계에는 따로 정할 예외가 있습니다. 공개 게시글이 0건이면 SUM이 NULL이므로 건수와 COALESCE의 정책을 밝혀 두고, 사용자 시간대의 일별 통계는 그 지역 날짜의 시작과 끝을 UTC로 바꾸어 범위를 정합니다. 평균만 저장하면 다시 집계할 수 없으므로 분자와 분모를 보존하고, 원래 데이터를 물리 삭제한 뒤에도 감사용 통계를 재현할 수 있는지 보존 정책을 확인합니다.

운영 지표로는 요약의 지연과 source_cutoff_at부터 지난 시간, 불일치 행의 수, 재생성에 걸린 시간을 둡니다. 재생성은 작은 배치와 일관된 원본 읽기 범위로 실행하고, 오류가 난 회원 범위부터 다시 시작할 수 있도록 커서를 기록합니다.

역정규화 도입 기준

역정규화한 열부터 더하지 말고, 원래 쿼리와 재생성 쿼리를 먼저 완성합니다. 승인의 조건은 동기화 방법이 아니라, 정답의 정의와 복구할 수 있는지입니다.

정리

• 역정규화는 원래 데이터와 기준 시각, 갱신 방식, 복구 규칙을 함께 정해야 합니다.
• 파생값과 원래 합계의 일치는 CHECK나 외래 키로 강제할 수 없으므로 진단 쿼리로 확인합니다.
• 기존 값에 더하는 것보다, 원래 데이터에서 집계값을 다시 계산해 덮어쓰는 재생성이 안전한 기준선입니다.
• 합계와 건수를 함께 저장해 다시 집계할 수 있게 합니다.
• 불일치 쿼리와 멱등한 재생성을 운영의 실행 절차로 둡니다.

연습 문제

1. 카테고리별 일일 요약

카테고리별로 하루 동안 공개한 게시글 수와 총조회수를 요약하는 테이블을 설계해 보세요. 같은 날짜를 다시 처리해도 중복되지 않고, 글이 없는 카테고리도 표시되어야 합니다.

먼저 UTC 날짜 2026-07-14 하루와 카테고리 1, 2가 있다고 놓고 손으로 계산합니다. 카테고리 1에는 그날 공개한 조회수 10과 20의 글 두 개, 그날의 DRAFT 글 하나(조회수 999), 다음 날 00:00에 공개한 글 하나(조회수 100)가 있고, 카테고리 2에는 글이 없습니다. 공개 시각의 구간은 2026-07-14 00:00 <= published_at < 2026-07-15 00:00입니다. 두 요약 행의 건수와 합계를 쓰고, 상태와 시각의 조건을 LEFT JOIN의 ON과 WHERE 가운데 어디에 둘지 판단해 보세요.

DROP TABLE IF EXISTS category_daily_summary;
CREATE TABLE category_daily_summary (
  category_id BIGINT UNSIGNED NOT NULL,
  summary_date DATE NOT NULL,
  published_count INT UNSIGNED NOT NULL,
  total_views BIGINT UNSIGNED NOT NULL,
  source_cutoff_at DATETIME(6) NOT NULL,
  PRIMARY KEY (category_id, summary_date),
  FOREIGN KEY (category_id) REFERENCES categories (id)
);

INSERT INTO category_daily_summary
  (category_id, summary_date, published_count, total_views, source_cutoff_at)
SELECT * FROM (
  SELECT c.id AS src_category_id,
         DATE '2026-07-14' AS src_date,
         COUNT(p.id) AS src_count,
         COALESCE(SUM(p.view_count), 0) AS src_views,
         '2026-07-15 00:00:00.000000' AS src_cutoff
  FROM categories AS c
  LEFT JOIN posts AS p
    ON p.category_id = c.id
   AND p.status = 'PUBLISHED'
   AND p.published_at >= '2026-07-14 00:00:00'
   AND p.published_at < '2026-07-15 00:00:00'
  GROUP BY c.id
) AS src
ON DUPLICATE KEY UPDATE
  published_count = src_count,
  total_views = src_views,
  source_cutoff_at = src_cutoff;

2. 두 번 적용된 증분 갱신

본문의 재생성 뒤에 게시글 2의 조회수를 40에서 45로 고쳤습니다. 작업자는 요약에 UPDATE member_post_summary_demo SET total_views = total_views + 5 WHERE member_id = 1을 보냈는데, 응답을 받지 못해 같은 문장을 한 번 더 보냈고 두 문장이 모두 커밋되었습니다. 원래 데이터의 합계와 요약은 각각 얼마이고, 이런 중복 적용을 어떻게 막을 수 있을까요?

CREATE TABLE IF NOT EXISTS summary_applied_events (
  event_id VARCHAR(64) NOT NULL PRIMARY KEY,
  applied_at DATETIME(6) NOT NULL
);

START TRANSACTION;
INSERT INTO summary_applied_events (event_id, applied_at)
VALUES ('post-2-views-40-to-45', CURRENT_TIMESTAMP(6));
UPDATE member_post_summary_demo
SET total_views = total_views + 5
WHERE member_id = 1;
COMMIT;

다음 글 회원 게시판 최종 DDL에서는 지금까지 정한 원본과 타입, 인덱스, 요약의 규칙을 빈 데이터베이스에서 실행하는 통합 DDL로 묶고, 무결성과 쿼리 계획까지 확인합니다.