카테고리 별 상위 N 개 가져오기 - Query

환경

테스트는 PostgreSQL 11  으로 진행했는데 MySQL 이나, MariaDB 는 같은 sql 을 사용해도 테스트 할 수 있을 것이라고 생각합니다.

 

테이블 ddl

CREATE TABLE IF NOT EXISTS public.board
(
    id integer NOT NULL DEFAULT nextval('board_id_seq'::regclass),
    title text COLLATE pg_catalog."default",
    name text COLLATE pg_catalog."default",
    content text COLLATE pg_catalog."default",
    readnum numeric,
    phone text COLLATE pg_catalog."default",
    pwd text COLLATE pg_catalog."default",
    date date,
    category text COLLATE pg_catalog."default",
    CONSTRAINT board_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)

TABLESPACE pg_default;

ALTER TABLE public.board
    OWNER to postgres;

 

데이터 개수

데이터는 10만건을 넣어놓고 테스트를 진행했습니다.

 

샘플 데이터 생성기

https://www.mockaroo.com/

Mockaroo - Random Data Generator and API Mocking Tool | JSON / CSV / SQL / Excel

위 링크에서 샘플로 사용할 데이터

 

방법 1

SELECT * 
FROM ( 
   SELECT *, RANK() OVER (PARTITION BY M.CATEGORY ORDER BY M.TITLE DESC, M.ID DESC) AS RN  
   FROM BOARD AS M 
) AS RANKING
WHERE RANKING.RN <= 3

카테고리별로 그룹화 해서 title과 id로 순위를 매긴 뒤에, 상위 3 개를 가져오는 쿼리 입니다.

 

성능

약 0.77 초 걸렸네요.

 

방법 2

WITH RANKING AS (
    SELECT *, RANK() OVER (PARTITION BY M.CATEGORY ORDER BY M.TITLE DESC, M.ID DESC) AS RN  
    FROM BOARD AS M
)
SELECT * FROM RANKING AS SC WHERE SC.RN <= 3;

WITH 로 가독성만 향상시킨 방법이라고 하는데 실제로 성능은 조금 더 낮게 나왔습니다. 서브쿼리를 사용한 방법과 다르게 동작하는 모양입니다.

 

성능

0.92 초 정도 나옵니다.

 

방법 3 (방법 1 개선)

SELECT * 
FROM BOARD 
WHERE ID IN (
	SELECT ID FROM ( 
	   SELECT ID, RANK() OVER (PARTITION BY M.CATEGORY ORDER BY M.TITLE DESC, M.ID DESC) AS RN  
	   FROM BOARD AS M 
	) AS RANKING
	WHERE RANKING.RN <= 3 
)

정렬할 때, 굳이 필요 없는 데이터까지 붙일 필요 없어 보여서, ID와 TITLE, CATEGORY 만 이용해서 정렬한 뒤에, 아이디로 IN 쿼리를 날립니다.

 

성능

성능은 0.57 초 정도 나옵니다. 방법 1 보다 25% 정도의 성능은 향상 되었는데 드라마틱 하지는 않네요.

 

성능 향상 가능성이 있는 부분

에초에 정렬할 데이터를 IN 쿼리로 줄여준다면 (필요한 카테고리의 데이터만 조회), 성능이 훨 씬 올라올 수 있음

 

개선된 SQL

SELECT * 
FROM BOARD 
WHERE ID IN (
	SELECT ID FROM ( 
	   SELECT ID, RANK() OVER (PARTITION BY M.CATEGORY ORDER BY M.TITLE DESC, M.ID DESC) AS RN  
	   FROM BOARD AS M 
	   WHERE ID IN (1,2,3,4,5,6,7,8,9,10) -- 이런식으로 ROW 수를 줄임
	) AS RANKING
	WHERE RANKING.RN <= 3 
)

 

성능

 

 

주의

이 쿼리로 순위를 매길 때, 동일한 값에는 같은 순위를 매깁니다.

예를 들어서 title 이 "A" 인 게시물이 있을 때

OVER (PARTITION BY M.CATEGORY ORDER BY M.TITLE DESC)

위 와 같이 작성하면, 두 게시물이 같은 순위를 가질 수 있습니다.

이게 싫다면 꼭 id 같은 중복될 수 없는 값을 추가해주어야 합니다.