테이블 내에서 group by의 최대값을 가진 row 검색하는 방법 + 속도체크

최근에 동일 테이블에서 그룹핑하여 최대값을 가진 row 데이터를 리스트에 출력하는 업무를 맡았다.

가장 효율적인 쿼리가 무엇일까 고민하다가 생각해낸 두가지 방법과 실제로 성능이 어떤지 테스트해보았다.

 

요구사항은 다음과 같다.(실제 업무와 비슷하게 예시로 들었다.)

특정 회원에게 가장 최근에 적용된 권한이 어떤 권한인지 검색한다. 그 권한은 시스템 관리자가 설정한 적용날짜에 따라 적용되고, 적용날짜는 하나의 테이블에 회원 일련번호와 적용날짜, 권한정보 하나의 로우로 로그데이터와 같이 insert만 해놓은 테이블(update 없음)에서 회원에게 설정된 가장 최근 적용날짜, 적용날짜가 같은 정보가 있다면 가장 최근에 등록된 데이터에서 권한정보를 가져와야한다.

비유를 들려하니 내가 봐도 뭔소리인지 모르겠고... 애초에 이렇게 설계되지 않았으면 좋았겠지만, 이미 있는 구조에서 개발들어가야하는 상황으로 극단적 상황으로 보자. 여기서 비지니스는 중요한 것이 아니기에 예시 테이블을 봐보자.

 

create table CONFIG (
    Config_Seq INT AUTO_INCREMENT PRIMARY KEY, -- pk
    User_Seq INT, -- 회원 일련정보
    Apply_Date DATE -- 적용날짜
    -- 권한정보 칼럼 등..
);

CONFIG 테이블에서 User_Seq를 그룹핑하여 Apply_Date가 가장 최근이고, 그 중에서 Config_Seq가 가장 최근에 등록된 ROW를 출력해야한다.

단건 쿼리로는 다음과 같이 될 것이다.

select *
from CONFIG
where User_Seq = 10
order by Apply_Date desc, Config_Seq desc
limit 1

 

하지만.. 얘를 리스트로 뽑아내려니 머리가 아파지기 시작한다.

방법이 안떠오르는 것은 아니였지만, 어떤게 효율이 좋을지 궁금해지기 시작했다.

 

첫번째로는 무지성으로 테이블을 여러번 조인 시키는 것이다.

select c1.*
from CONFIG c1
left join CONFIG c2
on c1.User_Seq = c2.User_Seq and c1.Apply_Date < c2.Apply_Date
left join CONFIG c3
on c1.User_Seq = c3.User_Seq and c1.Apply_Date = c3.Apply_Date and c1.Config_Seq < c3.Config_Seq
where c2.Config_Seq is null
and c3.Config_Seq is null;

조건에 따라 검색할 테이블(c1), 날짜조건 테이블(c2), 최근 등록된 데이터 조건 테이블(c3)를 두어 c2,c3에 없는 테이블을 검색한다.

 

두번째 방법은 ROW_NUMBER를 이용한다.

select *
from
(
    select
        ROW_NUMBER() over (PARTITION BY c.User_Seq ORDER BY Apply_Date desc, Config_Seq desc) as Row_Num,
        c.*
    from CONFIG c
) as config
where config.Row_Num = 1

서브쿼리로 한번 감싸줘야하지만, 테이블을 한번만 보고 뽑아낼 수 있다.

 

사실 그냥 대충만 봐도 두번째 방법이 가독성도 좋고 쿼리 속도에서도 훨씬 빠를 것으로 예상되지만, 얼마나 차이날지 테스트해보았다.

CREATE PROCEDURE dorepeat(p1 INT, apply_date DATE)
BEGIN
    SET @x =0;

    REPEAT
    SET @x = @x+1;

    INSERT INTO CONFIG (User_Seq, Apply_Date)
    VALUES (@x, apply_date);
    UNTIL @x > p1

    END REPEAT;
END;

call dorepeat(999, '2021-08-01');
call dorepeat(999, '2021-09-01');
call dorepeat(999, '2021-09-01');
call dorepeat(999, '2021-07-01');

프로시저로 간단하게 4000건의 데이터만 만들어서 테스트해보았다.

 

> select c1.*
      from CONFIG c1
      left join CONFIG c2
      on c1.User_Seq = c2.User_Seq and c1.Apply_Date < c2.Apply_Date
      left join CONFIG c3
      on c1.User_Seq = c3.User_Seq and c1.Apply_Date = c3.Apply_Date and c1.Config_Seq < c3.Config_Seq
      where c2.Config_Seq is null
      and c3.Config_Seq is null
[2021-09-05 20:08:17] 1,000 rows retrieved starting from 1 in 2 s 492 ms (execution: 2 s 334 ms, fetching: 158 ms)

> select *
      from
      (
          select
              ROW_NUMBER() over (PARTITION BY c.User_Seq ORDER BY Apply_Date desc, Config_Seq desc) as Row_Num,
              c.*
          from CONFIG c
      ) as config
      where config.Row_Num = 1
[2021-09-05 20:08:17] 1,000 rows retrieved starting from 1 in 28 ms (execution: 10 ms, fetching: 18 ms)

 

물론.. 인덱스도 없는 완전 기본형 테이블이지만, 4천건의 데이터밖에 없는데 생각보다 더 차이가 많이 난다.

무지성 테이블 조인의 경우 요구사항 조건이 늘어날 수록 조건의 개수에 따라 테이블 조인이 더 많아지고, 테이블의 데이터 개수에 따라 속도가 훨씬 영향을 많이 받는다. 

반면에 ROW_NUMBER의 경우 조건이 늘어나도 ORDER BY에 조건만 추가하면 되기에 무지성 테이블 조인은 방법이 있다는 것만 알아두는게 좋을 것 같다.

 

마지막으로 뻔한 결과를 테스트까지 한 이유는..  ORM에서 서브쿼리를 사용하는게 거부감이 들어(특히 QueryDSL) 어떻게든 서브쿼리를 사용하지 않고 해보려고 무지성 테이블과 비교까지 했는데 성능이슈가 무조건 날 것으로 보인다 ㅎ..

그럼 다음에는 QueryDSL에서 어떻게 join절에 해당 쿼리를 녹여 적용했는지 포스팅하겠다.(https://programmingnote.tistory.com/85)