[test] post_likes 설계 벤치마크: PK/인덱스/ID 타입 검증

[seonghooncho]

목적

post_likes 테이블 설계 선택을 추정이 아닌 실험으로 검증한다.

핵심 선택지:

• PK 구조
  • (A) 복합키: PRIMARY KEY (post_id, member_id)
  • (C) 단일키: PRIMARY KEY (id) + 조회 최적화 보조/커버링 인덱스
• post_id 저장 타입
  • VARCHAR(24) vs BINARY(12)

검증 질문:

1. 피드 집계 쿼리에서 어떤 스키마/인덱스가 유리한가?
2. INSERT + soft delete(UPDATE) + SELECT를 합친 총 비용은 어떤 설계가 낮은가?
3. 버퍼풀 초과 시 랜덤 I/O 성격 변화가 설계별로 어떻게 달라지는가?
4. VARCHAR(24) 대비 BINARY(12)가 인덱스 크기/랜덤 I/O/성능/쓰기 비용에 유의미한 차이를 만드는가?

범위

포함

• Docker 기반 저메모리(MySQL/InnoDB) 실험 환경 구성
  • 의도적으로 작은 innodb_buffer_pool_size로 랜덤 I/O 유도
• 아래 케이스 비교
  • A) 복합키 + VARCHAR(24)
  • B) 복합키 + BINARY(12)
  • C) 단일키 + 보조/커버링 인덱스 + VARCHAR(24)
  • D) 단일키 + 보조/커버링 인덱스 + BINARY(12)
• 단일키 설계의 보조 인덱스 컬럼 순서 후보 2개 이상 평가
• 워크로드 구현
  • 대량 INSERT
  • soft delete UPDATE(deleted_at 세팅)
  • 피드 조회(IN 크기 20/50/200)
• 데이터/실험 축
  • warm vs cold 캐시
  • 데이터 스케일(버퍼풀 충분히 초과)
  • 분포(균등 vs 인기편중/롱테일)
  • post_id 생성 패턴(준순차/ObjectId 유사 vs 완전 랜덤)
• 반복 측정 및 평균/분산 산출
• 증빙 수집
  • EXPLAIN ANALYZE 또는 동등 실행계획
  • InnoDB 통계(논리/물리 read, row ops, index size)
  • 가능한 시스템 I/O 지표(IOPS/throughput/latency)
• 재현 가능한 스크립트/코드/문서/결과 리포트 추가

제외

• 운영 DB 스키마 즉시 반영
• 프로덕션 데이터 마이그레이션/배포

완료조건 (DoD)

• Docker 저메모리 실험 환경을 재현 가능하게 실행할 수 있다.
• A/B/C/D 모든 케이스와 단일키 인덱스 후보 비교 결과가 존재한다.
• INSERT/UPDATE/SELECT에 대해 warm/cold, 분포, ID 패턴, 스케일 조건별 결과표가 있다.
• 모든 결론에 최소 2개 이상 근거(실행계획/DB통계/시스템지표)가 연결된다.
• 결과 리포트에 원인 분석(B-Tree fanout/깊이/페이지 접근/인덱스 유지비)을 포함한다.
• 서비스 특성(읽기+쓰기 많음, 데이터 성장, soft delete)에 대한 최종 추천안을 제시한다.

위험요소

• 저메모리 조건에서 실행시간 급증 가능
  • 대응: 데이터 스케일 프리셋(s/m/l), 타임아웃/반복수 조절
• 환경별 절대값 편차(Docker Desktop/호스트 스토리지)
  • 대응: 환경값 명시 + 상대비교 중심 + 동일 노드 반복 측정
• cold 캐시 재현성
  • 대응: DB 재시작/캐시초기화 절차 스크립트화
• 지표 가용성 차이
  • 대응: 최소 공통 지표(실행계획 + InnoDB) 필수 수집

참고링크

• 대표 쿼리

SELECT pl.post_id,
       COUNT(*) AS like_count,
       MAX(CASE WHEN pl.member_id = ? THEN TRUE ELSE FALSE END) AS amILiking
FROM post_likes pl
WHERE pl.post_id IN (...)
  AND pl.deleted_at IS NULL
GROUP BY pl.post_id;