게시글 좋아요 수로 정렬이 필요한 경우 통계 테이블 활용하기

배경

 

https://www.youtube.com/watch?v=n-7tsMhum4g&ab_channel=%EC%A0%9C%EB%AF%B8%EB%8B%88%EC%9D%98%EA%B0%9C%EB%B0%9C%EC%8B%A4%EB%AC%B4

 

존경하는 개발자 재민님의 영상을 보던중 아래 댓글을 보았다

 

위 내용을 요약하자면

게시판 정렬시 좋아요 수로 정렬을 할 경우 테이블간의 조인과 group by로 count를 구한다.

데이터 규모가 적으면 문제 없지만 규모가 커질때는 성능 저하가 심해진다.

또한, 상품 테이블에 XxxCount 필드를 추가하는건 데이터 정합성 등 여러 가지 문제점이 발생할 수 있다.

이러한 경우 통계 테이블을 활용해서 페이징 처리를 할 수 있다.

 

 

네이버 쇼핑만 봐도 정렬 조건이 많다.

테이블이 정규화 되어있고, 모든 테이블을 조인하여 group by + count 쿼리는 비용이 크게 발생한다. 

 

 

 

통계 테이블을 활용하는 방법을 경험해 본 적이 없어 댓글을 보고 재밌겠다 싶어서 실습을 해보았다.

 

테이블 및 데이터 생성

환경: Mysql 8.0.23

-- 아이템 테이블
CREATE TABLE item (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255)
);

-- 아이템 좋아요 테이블
CREATE TABLE likes (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  item_id VARCHAR(255),
  user_id VARCHAR(255),
  INDEX idx_likes_item_id (item_id)
);
CREATE INDEX idx_like_count_desc ON item_stat (like_count DESC);

-- 통계 테이블
CREATE TABLE item_stat (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  item_id BIGINT,
  like_count INT DEFAULT 0,
  UNIQUE INDEX idx_item_stat_item_id (item_id)
);

 

item_stat이 통계용 테이블이고, item 테이블과는 1:1 관계이다.

item에 대한 좋아요 수를 계속 삽입하는게 아니라 최신 좋아요 수로 갱신하는 작업을 한다.

테스트 코드에서 JdbcTemplate을 이용해서 item 테이블 100만건, likes 테이블 1000만건, item_stat 1000만건을 넣었다.

 

1. group by + count

SELECT item.id, COUNT(likes.id) AS like_count
FROM item
LEFT JOIN likes ON likes.item_id = item.id
GROUP BY item.id
ORDER BY like_count DESC
LIMIT 10 OFFSET 0;

 

모든 item의 좋아요 수를 세어서 내림차순 정렬 후 상위 10개를 가져오는 쿼리

 

EXPLAIN 결과

id	select_type	table	type	possible_keys	key	key_len	rows	filtered	Extra
1	SIMPLE	item	index	PRIMARY	PRIMARY	8	999,128	100	Using index; Using temporary; Using filesort
1	SIMPLE	likes	index	idx_likes_item_id	idx_likes_item_id	1023	9,743,056	100	Using where; Using index; Using join buffer (hash join)

 

item 테이블

type, key, key_len, rows

type: index
key: PRIMARY
key_len:8
rows: 999,128

• 테이블 전체를 PRIMARY Key 인덱스를 통해 약 999,128건을 스캔

 

Extra (핵심)

group by를 통해 count를 구한다.

Using index; Using temporary; Using filesort

• Using index는 covering index일 때 뜨는 Extra지만, type: index는 “인덱스를 full scan 중”이라는 의미다.
  
  • item 테이블에서 id만 select하기 때문에 커버링 인덱스

※ index가 표기되는 항목과 차이점은 다음과 같다.

Extra	Using index	커버링 인덱스 (쿼리의 모든 항목이 인덱스 컬럼으로 이루어진 상태)
Extra	Using index condition	인덱스 컨디션 푸시다운 인덱스
type	index	인덱스 풀 스캔 (range 스캔이 아님)

• Using temporary: GROUP BY용 임시 테이블 생성 중
  • item.id 기준으로 그룹핑
• Using filesort: ORDER BY like_count DESC 처리를 위해 디스크 기반 정렬 발생
  • like_count는 인덱스가 없기 때문에 정렬 인덱스를 타지 못해 임시 테이블에 결과를 넣고 정렬

 

⁉️ Using temporary와 Using filesort가 뜬 이유는?

GROUP BY가 인덱스를 타면 Extra애 Using temporary와 Using filesort가 뜨지 않는다.

-- 인덱스 on (category, created_at ASC)
SELECT id, category, created_at
FROM item
WHERE category = 'BOOK'
GROUP BY category, created_at
ORDER BY category, created_at;

이 경우 WHERE, GROUP BY, ORDER BY 모두 동일한 인덱스 범위 안에서 해결 가능하여 Using INDEX만 뜬다.

 

하지만, group by + count 쿼리차아점은에서 ORDER BY like_count DESC는 집계된 결과인 COUNT(likes.id)에 대한 정렬이다.

COUNT(), SUM(), AVG()처럼 집계된 컬럼은 정렬용 인덱스가 아니므로 인덱스 기반 정렬이 불가능 하다.

따라서, 임시 테이블을 만들고 filesort로 해결한 것이다.

 

 

likes 테이블

type, key, key_len, rows

type: index
key: idx_likes_item_id
key_len:1023
rows: 9,743,056

• 테이블 전체를 idx_likes_item_id 인덱스를 통해 약 9,743,056건을 스캔

 

Extra (핵심)

Using where; Using index; Using join buffer (hash join)

 

• Using where: 조인 조건 likes.item_id = item.id 사용
  • 인덱스가 조인 조건을 커버하더라도, row-level 평가가 필요한 경우 Using where가 붙는다.
  • 즉, 인덱스로 탐색된 후보 row가 조인 조건을 만족하는지 재검증하는 비용이 있다.

💡 likes 테이블에서 일어나는 일
- 인덱스 idx_likes_item_id를 이용해 likes.item_id 순서로 접근
- 해당 인덱스는 JOIN 조건(item.id = likes.item_id)을 만족할 만한 후보만 찾아준다.
- MySQL은 각 likes row를 보고 “진짜 item.id랑 일치하는지” 조건 재검증을 진행
- 해당 작업이 row-level filtering이라서 → Using where가 붙음

• Using index: covering index로 처리
  • likes 테이블에서 id만 select하기 때문에 커버링 인덱스
• Using join buffer (hash join): item이 driving table이고, likes는 item.id 기반으로 hash join 방식으로 조인됨
  • likes가 join buffer 메모리를 촐과하면 나머지를 디스크에 저장하기 때문에 성능이 급락
  • join buffer 메모리에 간신히 충족한다 해도 느릴 수 있다.

 

⁉️ Using join buffer (hash join)을 사용한 이유는?

 

hash join이 떴다해서 Mysql 8.0.18 부터 지원하는 native hash join이 아니다

실제로는 join buffer 안에서 해시 기반 최적화를 사용하는 nested-loop-join 이다. (MySQL 5.6 부터 지원)

EXPLAIN FORMAT=JSON SELECT ... 결과 안에 "join_type": "hash_join"이 있어야 native hash join이 적용된 상황

 

순수 nested-loop join를 사용할 경우 item -> likes 1:N 조인 구조에서 likes 테이블의 데이터 규모가 크기 때문에 비효율적이다.

 

순수 nested-loop join

for each row in item:
    for each row in likes:
        if item.id = likes.item_id:
            match

 

 

join buffer + hash join (MySQL 자동 최적화)

# likes 테이블을 미리 버퍼에 올림
join_buffer = {
    item_id_1 → [like1, like2],
    item_id_2 → [like3],
    ...
}

for each item:
    join_buffer.get(item.id) → 바로 매칭

• item 테이블은 드라이빙 테이블이고, likes 테이블은 인덱스 스캔 대상 테이블이다.
• MySQL은 likes의 일부를 join buffer에 올리고 item.id 기준으로 해시 탐색해서 매칭한다.
• join 조건이 단순 등호이고 join_buffer_size에 만족했기 때문에 join buffer + hash join 방식이 적용되었다.

 

2. 통계 테이블 활용

SELECT i.id, i.name, s.like_count
FROM item i
JOIN item_stat s ON i.id = s.item_id
ORDER BY s.like_count DESC
LIMIT 10 OFFSET 0;

 

모든 item의 좋아요 수를 세어서 내림차순 정렬 후 상위 10개를 가져오는 쿼리

GROUP BY없이 미리 계산된 통계 테이블(item_stat)을 조인해서 가져오는 방식이다.

 

EXPLAIN 결과

• CREATE INDEX idx_like_count_desc ON item_stat (like_count DESC);를 만들지 않았을 경우

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	SIMPLE	s	(null)	ALL	idx_item_stat_item_id	(null)	(null)	(null)	997,632	100	Using where; Using filesort
1	SIMPLE	i	(null)	eq_ref	PRIMARY	PRIMARY	8	concert.s.item_id	1	100	(null)

 

item_stat 테이블에서 like_count로 정렬시 Using fileort가 발생하여 성능이 저하된다.

정렬된 인덱스가 존재하지 않아 테이블 전체를 스캔 한 후 정렬을 수행한다.

 

• CREATE INDEX idx_like_count_desc ON item_stat (like_count DESC);를 만들었을 경우

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	SIMPLE	s	(null)	index	idx_item_stat_item_id	idx_like_count_desc	5	(null)	10	100	Using where
1	SIMPLE	i	(null)	eq_ref	PRIMARY	PRIMARY	8	concert.s.item_id	1	100	(null)

 

정렬된 인덱스로 결과를 추출하여 성능이 대폭 향상된다. rows수 도 LIMIT 10 만큼 읽는 걸 알 수 있다.

 

복합 정렬 조건

만약, 좋아요 뿐만 아니라 평점도 함께 정렬 조건에 포함된다면 아래와 같이 쿼리 및 테이블 설계를 하면 된다.

 

쿼리

SELECT i.id, i.name, s.like_count, s.rating_avg
FROM item i
JOIN item_stat s ON i.id = s.item_id
ORDER BY s.like_count DESC, s.rating_avg DESC
LIMIT 10 OFFSET 0;

 

 

테이블 설계

CREATE TABLE item_stat (
  item_id BIGINT PRIMARY KEY,
  like_count INT NOT NULL DEFAULT 0,
  rating_avg FLOAT NOT NULL DEFAULT 0.0,
);

CREATE INDEX idx_item_stat_like_rating_desc
ON item_stat (like_count DESC, rating_avg DESC);

• 한 가지 주의할 건 MySQL은 인덱스 순서대로 정렬 조건이 일치할 때만 index sort 최적화를 적용하기 때문에 order by 쿼리 작성시 인덱스 순서 및 정렬 조건에 맞춰서 적용해야 한다.

 

통계 테이블 갱신

좋아요 등록 및 취소시 통계 테이블도 계속해서 갱신을 해줘야 한다

좋아요 등록

-- 원본 삽입
INSERT INTO likes(user_id, item_id) VALUES (101, 3001);

-- 통계 갱신
UPDATE item_stat SET like_count = like_count + 1 WHERE item_id = 3001;

 

좋아요 취소

-- 원본 삭제
DELETE FROM likes WHERE user_id = 101 AND item_id = 3001;

-- 통계 갱신
UPDATE item_stat SET like_count = like_count - 1 WHERE item_id = 3001;

 

item_stat 통계 테이블 갱신시 race condition이 발생할 수 있다.

대응 방안이 어떤게 있는지 다음 게시글에서 알아볼 예정이다.

 

참고

https://jojoldu.tistory.com/476

https://kimsj.dev/blog/mysql-nestedloopjoin-hashjoin/

 

피드백은 언제나 환영 합니다 :)