좋아요 순 정렬은 왜 7초가 걸렸을까?

TL;DR

• 좋아요순 정렬은 ORDER BY COUNT(likes) 집계라, 매 요청마다 좋아요 297만 건을 조인·집계·정렬한다 → 7.3초.
• 인덱스로 풀스캔·해시조인은 잡아 14배 빨라졌지만(0.45초), 집계값 정렬(Using temporary; filesort)은 인덱스로 못 없앤다.
• like_count 컬럼으로 비정규화해 집계 자체를 없애고 정렬 인덱스를 붙이면 0.13ms. 대신 "어떻게 동기화·정합성을 유지하나"라는 새로운 문제가 생긴다.

상품과 상품 좋아요는 테이블 정규화로 분리되어 있다. 상품 목록에 "좋아요 순" 정렬을 붙이고, 시드 데이터를 넣어 실제로 호출해봤다. 데이터는 상품 10만, 좋아요 약 297만, 재고 10만, 브랜드 50 규모다.

일반 목록 조회는 1초쯤 나왔는데, 좋아요 순으로 정렬하니 한 번 호출에 7.3초가 걸렸다. 페이지네이션도 있고 LIMIT 20도 있는데 왜 이럴까. 실행되는 쿼리부터 봤다.

좋아요 순 정렬이 만드는 쿼리

좋아요 수는 상품 테이블이 아니라 별도의 likes 테이블에 행으로 쌓인다. 그러니 "좋아요 많은 순"으로 정렬하려면 상품마다 좋아요를 세야 한다.

SELECT p.*
FROM products p
LEFT JOIN likes l ON l.product_id = p.id AND l.deleted_at IS NULL
WHERE p.status = 'ON_SALE' AND p.deleted_at IS NULL
GROUP BY p.id
ORDER BY COUNT(l.id) DESC, p.id DESC
LIMIT 20;

EXPLAIN을 떠보면 이렇다.

테이블	type	key	rows	Extra
p (products)	ALL	NULL	99,583	Using temporary; Using filesort
l (likes)	ALL	NULL	2,954,528	Using join buffer (hash join)

정리하면 네 가지가 보인다.

1. type=ALL × 2, key=NULL — 두 테이블 다 인덱스 없이 풀스캔.
2. Using temporary — 98k 그룹을 임시 테이블에 집계.
3. Using filesort — 그 결과를 정렬. 인덱스로 정렬을 못 한다. 정렬 기준이 COUNT() 집계값이라서
4. hash join + 297만 스캔 — 매번 좋아요 전체를 메모리 해시로 빌드.

여기서 핵심은 LIMIT 20이 아무 일도 못 한다는 것이다. 정렬 기준이 COUNT(*)라 상위 20개를 알려면 일단 전부 세서 줄을 세워봐야 한다. 20개만 보고 멈출 방법이 없다. 결국 매 요청마다 297만 건을 조인하고 10만 개를 정렬하는 셈이다.

그래서 커서 페이징도 안 된다

참고로 이 정렬에는 커서(키셋) 페이징을 붙일 수 없다. 커서 페이징은 정렬 기준값이 테이블에 저장돼 있어서 인덱스로 찾아갈 수 있어야 동작한다.

WHERE (price, id) > (:lastPrice, :lastId)
ORDER BY price, id
LIMIT 20

그런데 좋아요 순의 정렬 기준은 COUNT(likes), 상품 테이블에 없는 매번 계산하는 집계값이다. SQL 실행 순서를 보면 이유가 분명하다.

1. FROM / JOIN      ← 테이블 결합
2. WHERE            ← 개별 row 필터 (집계 전!)
3. GROUP BY         ← 그룹으로 묶음
4. 집계함수 계산     ← COUNT(*) ... 여기서 값이 생김
5. HAVING           ← 집계 결과로 필터
6. SELECT
7. ORDER BY
8. LIMIT / OFFSET

COUNT(*)는 3번에서 묶인 뒤 4번에서야 값이 생긴다. 2번 WHERE 시점엔 존재하지 않으니 WHERE 좋아요수 < :직전값을 쓸 수 없고, HAVING으로 가야 한다. 그런데 HAVING은 전체를 다 묶고 다 센 다음에 거른다. 커서의 유일한 장점이 "앞부분 안 보고 마지막 위치에서 조회"하는 점프인데, 매번 전부 집계하면 그 점프가 불가능하다. 이건 비정규화로 정렬 기준값을 컬럼으로 만든 뒤에야 가능해지는 이야기라, 여기서는 짚어만 두고 넘어간다.

1차 시도: 인덱스

쿼리가 어디를 풀스캔하는지는 EXPLAIN이 알려줬다. WHERE / JOIN / GROUP BY / ORDER BY 절을 보고 인덱스를 결정했다.

likes(product_id, deleted_at) — 조인과 좋아요 카운트가 product_id로 접근하는데 단독 인덱스가 없었다. 기존 유니크 키는 (user_id, product_id) 순서라 product_id 단독 조회엔 못 쓴다. 쿼리가 WHERE product_id=? AND deleted_at IS NULL로 항상 두 컬럼을 같이 쓰니 복합으로 묶었다. 그러면 테이블을 안 보고 인덱스만으로 개수를 셀 수 있다(커버링 인덱스).

products(brand_id, status) — 브랜드 필터용. brand_id가 선택도가 높으니 선두에, status는 낮으니 뒤에 둔다. 사실 status는 ON_SALE이 90% 이상이라 단독으로는 풀스캔이 더 싸지만, 항상 같이 쓰는 컬럼이라 후행에 넣어두는 건 비용이 거의 없는 선택이다.

인덱스를 추가하고 EXPLAIN을 다시 떴다. likes 쪽이 가장 크게 바뀌었다.

likes 접근	baseline	TO-BE
type	ALL (풀스캔)	ref (인덱스 조회)
key	NULL	idx_likes_product_deleted
rows	2,954,528	29
Extra	Using join buffer (hash join)	Using index (커버링)

좋아요 297만 건 해시 조인이, 상품마다 커버링 인덱스로 29행만 들여다보는 조회로 바뀌었다. 브랜드 필터 쪽도 풀스캔(type=ALL)에서 인덱스 조회(type=ref)로 바뀌어, 전체를 훑지 않고 해당 브랜드의 상품만 바로 찾아간다.

쿼리는 5.8초에서 0.41초로, API는 6.2초에서 0.45초로 줄었다. 약 14배다.

인덱스로는 더 못 줄이는 한계

인덱스 추가만으로 14배는 분명 큰 수치다. 그런데 API 레이턴시 450ms는 실제 서비스에서 사용자 입장에선 여전히 오래 걸리는 시간이다. 실행 계획을 다시 보면 남아있는 문제가 있었다.

Extra: ... Using temporary; Using filesort

Using temporary; Using filesort가 인덱스를 깐 뒤에도 그대로 남아 있었다. 인덱스가 조인과 조회는 빠르게 했지만, ORDER BY COUNT(l.id)라 여전히 전체를 집계해서 임시 테이블에 담고 정렬해야 하기 때문이다. (Using filesort 자체가 항상 나쁜 건 아니다. 지금은 정렬 대상 row가 많아서 문제가 된다.)

시나리오	baseline	인덱스 후	남은 병목
필터 없음	15s	1s	temporary + filesort (집계 정렬)
브랜드 필터	1.4s	170ms	〃

인덱스로 잡히는 건 풀스캔과 해시 조인이고, 못 잡는 건 집계 정렬이다. 0.41초라는 한계도 결국 이 "전부 세서 정렬하기"의 비용이었다. 진짜 문제는 인덱스가 없는 게 아니라, 매 요청마다 좋아요를 처음부터 다시 센다는 것이었다.

2차 시도: 비정규화

매번 세지 말고 미리 세어두면 된다. 상품 테이블에 like_count 컬럼을 두면 ORDER BY COUNT(like.id) 없이 like_count만으로 정렬할 수 있다.

SELECT p.*
FROM products p
WHERE p.status = 'ON_SALE' AND p.deleted_at IS NULL
ORDER BY p.like_count DESC, p.id DESC
LIMIT 20;

조인도 없고 GROUP BY도 없다. 상품 컬럼 하나로 정렬한다.

비정규화 vs 반정규화

비슷한 선택지로 별도 조회용 테이블(product_like_summary)을 두는 반정규화도 있다. 읽기 성능 차이는 크지 않고, 차이는 다른 데 있다. 반정규화는 CQRS 읽기 모델이 커질 때 유리하다. 지금은 집계가 like_count 하나지만 order_count, review_count처럼 집계가 많아지면 별도 projection 테이블이 깔끔하다. 이번엔 집계가 like_count 하나뿐이라 비정규화로 갔다.

그리고 비정규화든 반정규화든, 사실 진짜 문제는 미리 집계한 like_count를 어떻게 동기화할 것인가다. 동기·이벤트·배치 중 무엇으로 갱신하느냐가 쓰기경합과 정합성을 좌우한다.

동기화 전략

SSOT는 여전히 likes 테이블이다. like_count는 거기서 파생된 캐시일 뿐이다.

동기로 처리하면 좋아요 한 번에 두 테이블을 같이 갱신해야 한다. 이벤트로 처리하면 likes만 갱신하고 카운트는 비동기로 맞춘다. 이때 멱등성을 위해 +1 증분 대신 매번 재계산해서 UPDATE 하면 주기 배치도 필요 없겠지만, 인기상품의 경우 매 이벤트마다 재계산하는 비용이 비싸다.

그래서 내가 내린 최종 결론은 이렇다.

1. 좋아요 등록/취소 → likes INSERT/soft-delete (SSOT) + 도메인 이벤트 발행
2. AFTER_COMMIT 리스너 → products.like_count +1 / -1 (O(1), 빠름)
3. 주기 스케줄러 → SSOT에서 set 기반 재계산으로 최종적 일관성 적용

단순히 증분은 빠르지만 이벤트가 유실되면 값이 어긋날 수 있으니, 스케줄러가 likes에서 다시 세어 맞추는 보정을 둔다.

mermaid
    actor User
    participant LikeService as Like 도메인
    participant Likes as likes (SSOT)
    participant Pub as ApplicationEventPublisher
    participant Listener as ProductCountListener
    participant Products as products (like_count)
    participant ReadAPI as 상품 조회 API
    participant Scheduler as 주기 스케줄러

    User->>LikeService: POST/DELETE /likes/{productId}
    alt 등록 (상태 전이 inactive→active)
        LikeService->>Likes: INSERT / soft-delete 복원
        LikeService->>Pub: publish LikeAdded(productId)
    else 취소 (active→inactive)
        LikeService->>Likes: soft-delete (deleted_at)
        LikeService->>Pub: publish LikeRemoved(productId)
    else 변화 없음
        LikeService-->>User: no-op (이벤트 미발행)
    end
    LikeService-->>User: 200 (트랜잭션 COMMIT)

    Note over Pub,Listener: 트랜잭션 COMMIT 이후
    Pub->>Listener: AFTER_COMMIT 전달
    Listener->>Products: UPDATE like_count = like_count +1 / -1

    User->>ReadAPI: GET /products?sort=LIKES_DESC
    ReadAPI->>Products: SELECT ... ORDER BY like_count DESC LIMIT 20
    Products-->>ReadAPI: 상위 20건
    ReadAPI-->>User: 200

    loop 주기 (예: 1시간)
        Scheduler->>Products: UPDATE like_count = SSOT COUNT 재계산
    end

비정규화 후 측정

비정규화하고 실행 계획을 보니, 집계와 조인이 통째로 사라졌다. likes 테이블이 쿼리에서 빠졌다.

p (products)
  type: ALL          key: NULL
  Extra: Using where; Using filesort
  (Using temporary 없음, likes 조인 없음)

남은 건 상품 9만 8천 개를 like_count로 정렬하는 filesort뿐이다. 그래서 마지막으로 정렬 인덱스 products(like_count DESC, id DESC)를 추가했다.

p (products)
  type: index        key: idx_products_like_count       rows: 20
  Extra: Using where
  (Using filesort 없음)

filesort까지 사라지고 rows: 20이 떴다. 9만 8천 개 중 인덱스 순서대로 상위 20개만 읽고 멈춘다.

단계별로 정리하면

단계	실행 계획 핵심	SQL	API
ORDER BY COUNT(likes)	해시 조인 297만 + temporary + filesort	5.8s	6.2s
인덱스만	커버링 인덱스 조회, temporary/filesort 잔존	0.41s	0.45s
비정규화 (정렬 인덱스 없음)	filesort만 (집계·조인 소멸)	33ms	72ms
비정규화 + 정렬 인덱스	filesort 제거, 상위 20개만 읽음	0.13ms	33ms

ORDER BY COUNT(like) → 인덱스 → 비정규화 → 비정규화 + 인덱스. 정리하면 이렇다.

• 인덱스는 풀스캔과 조인을 잡는다. 하지만 ORDER BY COUNT()처럼 집계값으로 정렬하는 한 "전부 세서 정렬하기"는 남는다. 인덱스로는 개선하기 힘들다.
• 비정규화는 COUNT() 집계 자체를 미리 하고, 집계를 컬럼 하나로 바꾸면 정렬 인덱스가 비로소 제 역할을 하고 LIMIT이 살아난다.

대신 비정규화는 "어떻게 정합성을 유지할 것인가"라는 또다른 문제를 가져온다.