인덱스를 걸었는데… 더 느려졌다? 🤯

인덱스를 걸었는데… 더 느려졌다? 🤯

10만 건 이후, 인덱스의 배신과 선택의 이유

---

TL;DR

• 10만 건을 넘기면 평균보다 p95·p99 tail latency가 먼저 무너진다.
• WHERE 컬럼만 인덱싱하면 정렬을 못 받아 filesort로 샌다.
• 정렬 컬럼 + 타이브레이커(id) 를 포함한 전용 인덱스로 정렬을 인덱스로 해결해야 한다.
• 좋아요순은 인덱스만으론 부족. 드라이빙 테이블을 product_likes로 전환해야 한다.

---

1) 증상: 평균은 멀쩡한데 p95가 터진다

• 데이터 규모: 약 10만 건
• 문제 구간:
  • deep 페이지네이션(OFFSET 수백~수천)
  • 브랜드 필터 + 정렬 조합
• 현상: 평균은 괜찮아 보여도 p95·p99가 급격히 악화

원인들은 겹친다: 정렬 미지원, 카디널리티, 옵티마이저 선택, OFFSET 자체 비용

---

2) 실패한 첫 대응: WHERE만 보고 인덱스 늘리기

WHERE 조건만 보고 만든 인덱스는 정렬을 받치지 못해 Using filesort가 발생한다.

-- 🔴 WHERE만 보고 만든 인덱스 예시
CREATE INDEX idx_bad_product_brand   ON product(brand_id);
CREATE INDEX idx_bad_deleted_only    ON product(deleted_at);
CREATE INDEX idx_bad_brand_deleted   ON product(brand_id, deleted_at);
CREATE INDEX idx_bad_brand_status    ON product(brand_id, product_status);

문제점

• 정렬(ORDER BY created_at/price/like_count) 인덱스가 없어 filesort로 정렬 비용이 커진다.
• 선택도 낮은 컬럼(예: deleted_at)을 선두에 두면 범위가 넓어 이득이 미미할 수 있다.
• 조인(브랜드/좋아요)이 섞이면 드라이빙 테이블에 따라 인덱스가 더 쉽게 무력화된다.

---

3) 왜 비효율이었나: 카디널리티 & 옵티마이저

• 카디널리티 낮은 컬럼은 무조건 뒤로? → 아니다.
  WHERE deleted_at IS NULL처럼 항상 상수동치로 고정된다면, 정렬 컬럼 앞에 두어 정렬을 인덱스로 만족시키는 데 유리하다.
• 브랜드 필터가 자주 쓰이면 brand_id를 포함한 전용 인덱스가 필요.
  단, 브랜드 없는 요청에선 해당 인덱스는 정렬용으로 쓰이지 않는다(선행 키 미고정).
• 좋아요순은 product에서 시작하면 정렬 인덱스를 살리기 어렵다.
  **product_likes**에서 시작해 상위 N을 먼저 뽑고 product에 조인해야 한다.

메모: MySQL 8은 역방향 스캔을 지원한다. 같은 방향이면 굳이 DESC를 명시하지 않아도 역방향으로 스캔할 수 있다. 다만 의도 명확화를 위해 정렬 순서를 맞춰 주는 편이 좋다.

---

4) 설계 원칙 (정답 패턴)

1. 정렬 컬럼 포함: (..., created_at, id), (..., price, id) 처럼 정렬 + 타이브레이커(id) 를 함께 둔다.
2. 브랜드 전용 인덱스는 따로: brand_id 유무에 따라 서로 다른 인덱스를 탄다.
3. 좋아요순은 드라이빙 테이블 전환: product_likes→product로 조인.

---

5) 최종 인덱스 (수정판)

기본 트래픽(브랜드 필터 없음)

-- 최신순
CREATE INDEX idx_live_created ON product (deleted_at, created_at, id);

-- 가격순
CREATE INDEX idx_live_price   ON product (deleted_at, price, id);

• deleted_at IS NULL이 상수동치라면 선두에 둬도 정렬 충족에 유리.
• id는 타이브레이커 + keyset 페이징용.

브랜드 필터 + 정렬 조합

-- 최신순(브랜드 전용)
CREATE INDEX idx_live_brand_created ON product (deleted_at, brand_id, created_at, id);

-- 가격순(브랜드 전용)
CREATE INDEX idx_live_brand_price   ON product (deleted_at, brand_id, price, id);

• 브랜드가 없는 케이스에선 정렬용으로 안 탄다 → 그래서 전용 인덱스가 필요.

좋아요순(드라이빙 테이블 전환)

CREATE INDEX idx_pl_like ON product_likes (like_count, product_id);

선택지(트레이드오프): 실시간성이 조금 느슨해도 되면 product.like_count 역정규화 +
CREATE INDEX idx_live_like ON product (deleted_at, like_count, id); 로 조인 제거.

 

---

8) 근거 강화: EXPLAIN ANALYZE & 히스토그램

• EXPLAIN ANALYZE로 확인할 것:
  • Using filesort 유무
  • rows examined / actual rows
  • 어떤 인덱스를 탔는지, 조인 순서

실행 쿼리

EXPLAIN
SELECT
    p.id,
    p.name,
    b.name AS brand_name,
    p.price,
    COALESCE(pl.like_count, 0) AS like_count
FROM product AS p
         LEFT JOIN brand AS b
                   ON p.brand_id = b.id
         LEFT JOIN product_likes AS pl
                   ON pl.product_id = p.id
WHERE p.deleted_at IS NULL
ORDER BY p.created_at DESC
LIMIT 10;

나쁜 인덱스 EXPLAIN 결과

• type=ALL: 인덱스를 쓰지 않고 풀스캔
• Using filesort: 정렬 컬럼(created_at)이 인덱스에 없음

좋은 인덱스 EXPLAIN 결과

• type=ref: 범위 스캔 가능
• filesort 사라짐
• Using index condition: ICP(Index Condition Pushdown) 적용 → 불필요 row access 감소

---

10) 결과 (템플릿)

단위: ms (p95) / 동일 데이터셋, 동일 환경, vu = 10 후 60s steady

시나리오	없음 p95(ms)	나쁨 p95(ms)	최적 p95(ms)
LATEST	1032.6	2167.6	210.7
PRICE	976.3	2319.3	206.0
LIKES	7449.4	8289.6	7470.7
Shallow page	7195.8	7914.3	6207.0
Medium page	7482.1	7741.9	6836.3
Deep page	6639.6	7371.7	5806.1
Extreme page	7098.5	9040.1	7173.2

 

 

📌 배운 점

• WHERE 조건만 보고 인덱스를 늘리면 정렬을 못 받아 filesort로 성능이 악화될 수 있다.
• 정렬 컬럼 + 타이브레이커(id)를 포함한 전용 인덱스가 tail latency 개선에 필수적이다.
• 좋아요 정렬은 반드시 product_likes를 드라이빙 테이블로 전환해야 한다.
• 브랜드 필터 유무에 따라 전용 인덱스를 분리하면 옵티마이저가 안정적으로 플랜을 선택한다.

📌 측정 환경 주의

• 로컬 개발 환경(MySQL 8.x, VU=10, 60s steady)에서 측정한 값으로, 네트워크/IO 특성이 단일 노드 기준이라 절대 수치는 실제 운영 환경과 다를 수 있다.
• p95 수치는 콜드 캐시/워밍업 상태에 따라 ±10~20% 변동 가능.
• 중요한 건 절대값이 아니라, 최적 인덱스 적용 전후의 ‘상대적 개선 폭’이다.

🚀 다음편 예고

인덱스 최적화로 p95를 개선했지만, 트래픽이 더 늘어나니까 또 다른 병목이 나타났습니다. 이번엔 Redis 캐시를 도입해서 91% 성능 개선을 달성한 이야기를 들려드릴게요!

👉 인덱스를 걸었는데 또 느려졌다? → Redis 캐시로 완전체 만든 썰 Part 2

SingleFlight부터 캐시 워밍업까지, K6 테스트로 검증한 Redis 최적화 실전기