post_likes PK 순수 비교 재실험: 100만 건 기준 저장구조/조회비용 정량화

[seonghooncho]

근본 목적

post_likes 스키마에서 복합 PK와 단일 PK 전략의 차이를, soft delete/feed covering 같은 서비스 특화 조건 없이 순수하게 분리해 다시 측정한다. 이번 실험의 목적은 Mongo ObjectId에 가까운 12B post_id 기준에서 복합 PK가 클러스터드 인덱스, PK 대체용 보조인덱스, 전체 테이블 크기, leaf density, 조회 I/O에 어떤 비용을 만드는지 정량적으로 확인하는 것이다.

비목적

이번 이슈는 실제 피드 집계 쿼리, soft delete 포함 활성 좋아요 제약, covering index 최적화 우열을 결론내리기 위한 것이 아니다. 서비스 최종 설계 비교는 순수 PK 실험 기준선을 확보한 뒤 별도 단계에서 다룬다.

배경

이전 실험은 단일 PK 케이스에 feed 전용 covering index가 함께 들어가 있었고, soft delete 조건과 조회 패턴도 동시에 섞여 있었다. 그 결과 복합 PK 자체의 비용과 추가 보조인덱스/covering 비용을 분리해서 해석하기 어려웠다.

이번 실험은 질문을 아래처럼 다시 고정한다.

1. 동일한 데이터 100만 건에서 복합 PK 자체는 얼마나 큰가?
2. 동일한 논리 조회 경로에서 복합 PK와 단일 PK + PK 대체 인덱스 중 무엇이 더 작은가?
3. 12B post_id 기준에서 클러스터드/secondary leaf density와 조회 I/O는 어떻게 달라지는가?

실험 범위

공통 원칙

• soft delete 제외
• feed 전용 인덱스 제외
• 두 케이스는 동일한 (post_id, member_id) 100만 건을 동일 순서로 적재
• post_like_id 시작값은 50,000,000
• member_id 시작값은 500,000
• post_id는 BIGINT가 아니라 Mongo ObjectId에 가까운 BINARY(12)로 고정한다

비교 케이스

Case C: 복합 PK

CREATE TABLE post_likes_case (
  post_id BINARY(12) NOT NULL,
  member_id BIGINT NOT NULL,
  created_at DATETIME(6) NOT NULL,
  PRIMARY KEY (post_id, member_id)
) ENGINE=InnoDB;

Case S: 단일 PK + PK 대체 보조인덱스

CREATE TABLE post_likes_case (
  post_like_id BIGINT NOT NULL AUTO_INCREMENT,
  post_id BINARY(12) NOT NULL,
  member_id BIGINT NOT NULL,
  created_at DATETIME(6) NOT NULL,
  PRIMARY KEY (post_like_id),
  UNIQUE KEY uk_post_member (post_id, member_id)
) ENGINE=InnoDB AUTO_INCREMENT=50000000;

데이터셋 규칙

• 총 row 수: 1,000,000
• member_id: 500000 + f(n)
• post_id: 10000000 시드를 기반으로 생성하되, 결과는 BINARY(12)로 저장
• (post_id, member_id)는 유일해야 함
• 분포는 2개를 함께 본다
  • uniform
  • skew (hot post가 존재하는 분포)

측정 단계

Phase 1. 저장 구조 측정

목적: 총 테이블 크기와 핵심 인덱스 크기를 직접 비교한다.

수집 항목:

• information_schema.tables
  • DATA_LENGTH
  • INDEX_LENGTH
  • DATA_LENGTH + INDEX_LENGTH
• mysql.innodb_index_stats
  • size
  • n_leaf_pages
  • n_diff_pfx01

필수 산출물:

• C 총 테이블 크기
• S 총 테이블 크기
• C.PRIMARY 크기
• S.PRIMARY 크기
• S.uk_post_member 크기
• C.PRIMARY vs S.uk_post_member 직접 비교
• S.PRIMARY + S.uk_post_member 합계 vs C.PRIMARY 비교

Phase 2. leaf density 분석

목적: 12B post_id 기준 PK 전략이 페이지 밀집도에 미치는 영향을 본다.

지표:

• 인덱스별 rows_per_leaf_page = row_count / n_leaf_pages
• C.PRIMARY rows/leaf
• S.PRIMARY rows/leaf
• S.uk_post_member rows/leaf

분석 포인트:

• 12B post_id 기준 C.PRIMARY와 S.uk_post_member의 밀집도 차이
• S.PRIMARY가 얼마나 더 조밀한지 확인

Phase 3. 단건 조회 경로 검증

목적: 두 케이스가 의도한 인덱스를 실제로 타는지 검증한다.

대상 쿼리:

SELECT 1
FROM post_likes_case
WHERE post_id = ?
  AND member_id = ?
LIMIT 1;

수집 항목:

• EXPLAIN
• EXPLAIN ANALYZE
• 실제 접근 인덱스
• actual time
• rows examined

기대 경로:

• C: PRIMARY
• S: uk_post_member

Phase 4. 반복 조회 workload

목적: 동일 logical work에서 읽기 비용 차이를 측정한다.

원칙:

• 동일 key set 사용
• 같은 순서/같은 횟수 재생
• key 재방문 여부를 명시적으로 통제

측정 지표:

• Innodb_buffer_pool_reads
• Innodb_data_reads
• Innodb_pages_read 계열 가능 시 추가
• query당 reads
• avg / p95 latency

성공 기준

• C.PRIMARY, S.uk_post_member, S total PK strategy cost를 같은 표에서 비교 가능해야 한다.
• BINARY(12) 기준 구조/밀집도/조회 비용 차이가 재현 가능해야 한다.
• uniform/skew 축별 차이가 재현 가능해야 한다.
• 조회 경로는 C=PRIMARY, S=uk_post_member로 고정돼야 한다.

산출물

• 실험 스크립트
• 메타데이터 파일
• 인덱스 상세 TSV
• density summary TSV
• query plan 결과 파일
• 결과 해석 보고서

TODO

• PK 순수 비교 전용 스크립트 작성
• post_id = BINARY(12) 고정 데이터셋 생성
• uniform/skew 데이터셋 생성
• 100만 건 기준 구조 측정 수행
• 단건 조회 플랜/실측 수행
• 반복 조회 workload 측정 수행
• 결과 보고서 작성