TL;DR
상품 목록 API의 좋아요 순 정렬 성능을 확보하기 위해 13개 시나리오에서 인덱스 전략을 실측 비교했다.brand_id 단일 인덱스만으로 풀스캔 대비 100ms → 0.1ms(약 1,000배) 를 달성했으며,
읽기 성능이 사실상 동일한 반정규화 vs 분리 테이블 중
동시 쓰기의 락 경합 격리를 이유로 product_like_view 분리 테이블을 선택했다.
Question — 무엇을 비교했는가
결정해야 했던 것
브랜드별 상품 목록을 좋아요 순으로 정렬하는 API에서 두 가지 질문을 동시에 풀어야 했다.
- 인덱스 전략: 인덱스 없음 / 단일(
brand_id) / 복합(brand_id, like_count DESC) / 커버링 중 어느 수준까지 필요한가? - like_count 저장 구조:
products테이블에 컬럼으로 두는 반정규화 vs 별도product_like_view테이블로 분리하는 분리 테이블 중 무엇을 선택할 것인가?
두 결정은 독립적이지 않다. like_count 저장 위치가 달라지면 인덱스 설계도 달라지고, 쓰기 경로의 락 경합 구조도 달라진다.
비교 대상
| 축 | 후보 |
|---|---|
| 인덱스 전략 | 없음 → (brand_id) → (brand_id, like_count DESC) → 커버링 |
| like_count 구조 | 반정규화 products.like_count vs 분리 테이블 product_like_view |
| 정렬 조건 | 좋아요 순 / 최신순 / 가격순 / 복합 필터(브랜드 + 가격 범위 + 좋아요) |
| 연관 쿼리 | 좋아요 목록, 구매 목록, 재고 JOIN |
Setup — 측정 환경
| 항목 | 값 |
|---|---|
| DB | MySQL 9.7 (로컬, macOS arm64) |
| products | 996,327건 |
| likes | 2,490,470건 |
| brands | 7,500개 (brand당 평균 133건) |
| like_count 분포 | 0개(164K), 1~10개(834K), 11~100개(82건), 100초과(1K건) |
| 측정 도구 | 자체 SQL 스크립트 — EXPLAIN FORMAT=TRADITIONAL + NOW(6) 기반 마이크로초 측정 |
| 공통 조건 | brand_id = 1, LIMIT 20, OFFSET 0(1페이지) vs OFFSET 500,000(25,000페이지) |
데이터는 seed 스크립트로 직접 생성했다. 브랜드 카디널리티(7,500개)와 brand당 상품 수(평균 133건)를 의도적으로 설계해서, 인덱스가 실제로 필터를 얼마나 줄여주는지 검증할 수 있도록 했다.
Results — 측정 결과
Round 1~4: 브랜드별 좋아요 순 (핵심 시나리오)
| 라운드 | 인덱스 전략 | 구조 | OFFSET | Extra | 실행시간 |
|---|---|---|---|---|---|
| 1 | 없음 | 반정규화 | 0 | Using filesort | 103ms |
| 1 | 없음 | 반정규화 | 500,000 | Using filesort | 102ms |
| 2 | (brand_id) |
반정규화 | 0 | Using where; Using filesort | 0.10ms |
| 2 | (brand_id) |
반정규화 | 500,000 | Using where; Using filesort | 0.09ms |
| 3 | (brand_id, like_count DESC) |
반정규화 | 0 | Using where (filesort 제거) | 0.09ms |
| 3 | (brand_id, like_count DESC) |
반정규화 | 500,000 | Using where | 0.08ms |
| 4 | 커버링 (brand_id, like_count DESC, id, name, price) |
반정규화 | 0 | Using where | 0.12ms |
| 4 | 커버링 | 반정규화 | 500,000 | Using where | 0.11ms |
첫 번째 발견: 인덱스 없음 → (brand_id) 단일 인덱스 한 줄로 1,000배 개선된다.
이유는 카디널리티에 있다. brand_id = 1 조건으로 996K 행이 즉시 133건으로 줄어든다. 이후 filesort는 133건만 대상으로 하므로 메모리 정렬 마이크로초 수준이 된다.
두 번째 발견: 복합 인덱스 (brand_id, like_count DESC) 는 filesort 자체를 제거하지만, 실행시간은 0.09ms로 단일 인덱스(0.10ms)와 차이가 거의 없다.
이유도 동일하다 — 어차피 133건을 정렬하는 것은 인덱스 순서를 타든 filesort를 하든 체감 차이가 없는 수준이다.
세 번째 발견: 커버링 인덱스는 기대와 달리 복합 인덱스보다 오히려 미세하게 느렸다(0.12ms).deleted_at IS NULL 조건이 인덱스에 없어서 테이블 접근이 필요하고, 인덱스 크기 자체가 커져서 B-tree 탐색 비용이 소폭 증가한 것으로 판단된다. deleted_at까지 포함하면 커버링 조건이 깨지고, 포함시키면 인덱스 크기 문제가 더 심해진다.
Round 5: 분리 테이블 product_like_view JOIN
| 인덱스 | OFFSET | 실행시간 | Extra |
|---|---|---|---|
| 없음 | 0 | 111ms | Using filesort |
(brand_id) 단일 |
0 | 0.12ms | Using filesort |
(brand_id, like_count DESC) 복합 |
0 | 0.12ms | Using filesort |
| 커버링 | 0 | 0.15ms | Using filesort |
분리 테이블은 JOIN으로 인해 인덱스 종류와 무관하게 항상 Using filesort 가 발생한다.
하지만 brand_id 필터 후 133건만 대상으로 하므로 실행시간은 0.12~0.15ms로 반정규화와 실질적으로 동일하다.
분리 테이블 구조에서 추가 인덱스를 더 걸어도 성능 개선이 없다는 의미이기도 하다.
Round 6: 정렬 조건별 비교
| 정렬 | 인덱스 없음 | 복합 인덱스 |
|---|---|---|
최신순 created_at DESC |
123ms | 0.10ms |
가격순 price ASC |
108ms | 0.10ms |
정렬 컬럼이 바뀌어도 패턴은 동일하다. 각 정렬마다 (brand_id, 정렬컬럼 방향) 복합 인덱스가 필요하다.
Round 9: 전체 좋아요 순 — OFFSET의 함정
| 인덱스 | OFFSET | 실행시간 |
|---|---|---|
| 없음 | 0 | 128ms |
| 없음 | 500,000 | 242ms |
(like_count DESC) 단일 |
0 | 0.13ms |
(like_count DESC) 단일 |
500,000 | 253ms |
| 커버링 | 0 | 0.25ms |
| 커버링 | 500,000 | 264ms (풀스캔으로 전환) |
OFFSET 500,000에서 인덱스 스캔이 253ms로 올라가며 풀스캔(242ms)과 비슷해졌다.
옵티마이저가 인덱스를 통해 500,020번째 행을 읽는 비용이 풀스캔보다 비싸다고 판단해 인덱스를 포기했다.
OFFSET 기반 페이지네이션의 구조적 한계이며, 실서비스에서는 커서 기반 페이지네이션이 필요함을 시사한다.
Round 10: 복합 필터 (브랜드 + 가격 범위 + 좋아요 순)
| 인덱스 | Extra | 실행시간 |
|---|---|---|
| 없음 | Using filesort | 103ms |
(brand_id) |
Using filesort | 0.12ms |
(brand_id, like_count DESC) |
Using where (filesort 제거) | 0.10ms |
(brand_id, price ASC) |
Using index condition; Using filesort | 0.10ms |
price BETWEEN 같은 range 조건이 있어도 (brand_id, like_count DESC) 인덱스는 filesort를 제거한다.
MySQL이 brand_id = 1 구간을 like_count 내림차순으로 읽으면서 price 조건을 행별로 필터링하기 때문이다.
반면 (brand_id, price ASC) 인덱스는 range 이후 정렬 컬럼이 달라 filesort가 불가피하다.
Round 11: stocks JOIN — hash join의 함정
| 인덱스 | type(p) | type(s) | Extra | 실행시간 |
|---|---|---|---|---|
| 없음 | eq_ref | ALL | Using filesort | 541ms |
products: (brand_id) |
ref | ALL | hash join + Using filesort | 0.16ms |
products: (brand_id, like_count DESC) |
ref | ALL | hash join + Using filesort | 0.15ms |
+ stocks: (product_id, quantity) |
ref | ref | nested loop + filesort 제거 | 0.16ms |
(brand_id, like_count DESC) 인덱스 단독으로는 filesort가 제거되지 않았다.
hash join이 행 처리 순서를 파괴하므로 인덱스 정렬 순서를 보장할 수 없기 때문이다.stocks에 (product_id, quantity) 인덱스를 추가하면 hash join → nested loop join으로 전환되어 비로소 filesort가 제거된다.
반정규화 vs 분리 테이블 — 읽기/쓰기 종합
| 기준 | 반정규화 | 분리 테이블 |
|---|---|---|
| 브랜드별 좋아요 순 읽기 | 0.08ms (filesort 제거) | 0.12ms (filesort 있음) |
| 실질적 차이 | — | 0.04ms |
| 좋아요 단건 쓰기 | ~1.5ms | ~1.0ms |
| 동시 10명 | ~2ms | ~1ms |
| 동시 100명 | 10~30ms (스파이크) | ~2ms |
| 동시 1,000명 | 100ms+ | 5~10ms |
동시 쓰기 성능 차이의 원인은 락 경합 범위다.products는 상품 목록/상세 등 모든 API가 읽는 핫 테이블이다.
반정규화 구조에서는 좋아요 등록/취소마다 products.like_count UPDATE가 일어나고, 동시 사용자가 늘수록 조회 API와의 락 경합이 심화된다.product_like_view는 좋아요 연산만 접근하는 테이블이므로 락 범위가 격리된다.
Decision
선택
product_like_view 분리 테이블 + products(brand_id) 단일 인덱스
선택 이유
읽기 성능 차이 0.04ms는 실서비스에서 의미 없는 수준이다.
brand_id 인덱스가 먼저 ~133건으로 행 수를 줄이면, 이후 정렬은 메모리 내 마이크로초 연산이기 때문에 구조 차이가 희석된다.
반면 쓰기 경로에서의 차이는 실질적이다.
좋아요는 사용자가 실시간으로 발생시키는 이벤트다. 동시 요청이 늘어날수록 products 테이블의 락 경합이 상품 목록/상세 조회까지 영향을 미친다.
분리 테이블은 이 문제를 구조적으로 차단한다.
포기한 것
- 반정규화 + 복합 인덱스의 filesort 제거: 0.04ms 이득. 읽기 최고 성능을 포기했다.
- 단순한 스키마: 테이블이 하나 늘고, 좋아요 등록/취소 시
product_like_view동기화 로직을 애플리케이션에서 관리해야 한다.
최종 인덱스 목록
| 테이블 | 인덱스 | 용도 |
|---|---|---|
| products | (brand_id) |
브랜드별 상품 필터 |
| products | (brand_id, created_at DESC) |
최신순 정렬 |
| products | (brand_id, price ASC) |
가격순 정렬 |
| product_like_view | (like_count DESC, product_id) |
전체 좋아요 순 커버링 |
| likes | (member_id, created_at DESC) |
좋아요 목록 시간순 |
| orders | (member_id, created_at DESC) |
구매 목록 |
| order_items | (order_id, product_id) |
주문 상세 JOIN |
Future Work
- 커서 기반 페이지네이션: OFFSET 500K 시나리오에서 인덱스 스캔도 253ms로 올라가는 것을 확인했다. 실서비스에서는
WHERE like_count < :cursor형태의 커서 방식이 필요하다. - product_like_view 정합성: 좋아요 등록/취소 트랜잭션 실패 시
like_count불일치 가능성이 있다. 정렬 참고용 수치이므로 소폭 오차는 허용하지만, 모니터링 기준값 설정이 필요하다. - 재평가 시점: 브랜드당 평균 상품 수가 현재 133건을 크게 넘어서거나, 좋아요 동시 요청이 수백 RPS 이상으로 증가하면 반정규화 재검토 여지가 있다.