[Mybatis] 일대다 테이블 조회시 페이징과 N+1 Select 문제 해결 과정

사실이 아니라 공부한 내용과 생각을 정리한 글입니다. 언제든 가르침을 주신다면 감사하겠습니다.

 

예시를 위한 도메인은 간단한 결재 시스템입니다. 아래와 같은 1:N 관계의 테이블이 있다고 가정합니다.

예시를 위한 샘플 테이블 구조

예를 들어, 신청자가 휴가 신청을 올릴 때 팀장, 본부장, 사장 순으로 결재권자를 지정할 수 있습니다. 그렇게 지정 후 신청 버튼을 클릭하면 신청 table에 row 한건이 추가되고 승인자 table에는 그 신청건에서 지정한 승인자 3명의 정보가 추가됩니다.

apply_id는 하나의 신청 건에 대한 unique key이고 approver_id는 하나의 승인 정보에 대한 unique key이며, 신청 테이블: 승인 테이블 = 1:N 관계이고 외래 키는 apply_id입니다.

객체로 모델링하면 아래와 같습니다.

class Apply{
	String applyId;
    String applyName;
    List<Approver> approverList;
}

class Approver{
	String approverId;
    String approverName;
    Apply apply;
}

조회 api의 요구사항은 아래와 같습니다.

1. 신청 건을 조회.
   1.  클라이언트에게 List <Apply> 타입의 데이터를 최종적으로 응답해야 함
   2.  각각의 신청 건에 대한 승인자 리스트인 approverList 필드는 항상 채워서 응답해야 함
2. 신청자 이름, 승인자 이름 검색이 가능해야 한다.
3. 신청건 기준, 페이징이 가능해야 한다.
4. csv 내보내기(csv 다운로드)가 가능해야 한다.

1. 처음 구현되어 있던 형태

SELECT PAGE.RNUM, PAGE.* FROM (
    select 
        * 
    from
        apply_table as at 
    where
        at.apply_id in (
            select
                att.apply_id 
            from
                approver_table as att
            where 
                att.approver_name=='searchApproverName'
        )
     #생략.....
) PAGE WHERE PAGE.RNUM BETWEEN (1 + 1) AND (1 + 5)

메인테이블인 신청 테이블을 조회하면서 where 조건에서 서브 쿼리를 사용해 승인자 이름 기반으로 승인자 테이블을 조회합니다. 서브 쿼리에서 추출한 apply_id와 일치하는 메인테이블의 데이터만 최종적으로 뽑아냅니다.

그러고 나서 resultmap에서 아래와 같은 형태로 다시 한번 승인자 테이블을 조회하면서 신청 객체에 승인자 리스트 정보를 채워줍니다.

<resultMap id="applyApproverSelectResultMap" type="Apply">
	<!-생략...->
    <collection property="approverList" 
                column="apply_no" 
                javaType="ArrayList"
                ofType="Approver" 
                select="ApproverDao.list"/>
</resultMap>

이렇게 하면 최초 요구사항의 기능적인 부분은 모두 구현할 수 있으나 N+1 Select 문제가 발생합니다.

resultmap의 collection tag의 두 가지 동작 방식과 (Nested select, Nested results) Nested select 방식에서의 N+1 Select 문제는 링크를 참고해 주세요.(https://datajoy.tistory.com/170)

 

2. left join과 collection tag의 Nested Result 방식을 적용

위에서 N+1 select 문제가 발생하는 이유는 처음 조회 쿼리를 통해 가져온 신청자 정보를 기반으로 루프를 돌면서 승인자 테이블에 반복적으로 조회 쿼리를 날리는 데 있었습니다. 이를 해결하기 위해 처음 조회 쿼리를 날릴 때부터 left join을 통해 승인자 테이블의 정보까지 몽땅 가져온 후 nested result 방식을 통해 객체를 메핑 해 주면 될 거라고 생각했습니다.

SELECT PAGE.RNUM, PAGE.* FROM (
    select 
        * 
    from
        apply_table as at 
    left join
		approver_table as att 
	on 
		at.apply_id=att.apply_id
    where
		at.apply_name='applyNameSearch' and att.approver_name='approverNameSearch'
        #생략...
) PAGE WHERE PAGE.RNUM BETWEEN (1 + 1) AND (1 + 5)

 

<resultMap id="applyApproverSelectResultMap" type="Apply">
	<!-생략...->
    <collection property="approverList" 
                column="apply_no" 
                javaType="ArrayList"
                ofType="Approver" 
                resultMap="approverResultMap"/>
</resultMap>

<resultMap id="approverResultMap" type="Approver">
	<!-생략...->
    <id column="approver_id" 		property="approverId" />
    <id column="approver_name" 		property="approverName" />
</resultMap>

처음 이렇게 구현하고 1+N Select 쿼리가 1 Select로 줄어든 모습과 결과 데이터도 알맞게 메핑 되는 것을 보고 N+1 select 문제를 완벽하게 해결했다고 생각했습니다.

하지만 여기에는 큰 함정이 있었습니다. 

1:N 관계에서 left join을 하게 되면 최종 조회 결과는 하나의 신청건에 대해 하나의 row가 아닐 수 있습니다.(하나의 신청 건에 대해 여러 개의 승인자 row 가 있을 수 있기 때문) 그런데, 조회 sql을 보면 페이징을 left join을 한 결과물을 통해 적용하고 있습니다. 그렇게 페이징 한 데이터를 resultmap에서 메핑 하게 되면 아래와 같은 페이징 이슈가 발생할 수 있습니다.

left join 방식의 페이징 갯수 문제

위 그림을 보면 DB에서 조회한 row는 4개인데, 이 결과를 java 객체로 메핑 하게 되면 2개가 되는 것을 확인할 수 있습니다.

즉, DB에서 left join을 한 시점에서 row를 기준으로 페이징이 이루어지기 때문에  신청건 기준 페이징을 해야 한다는 요구사항을 만족하지 못하게 됩니다.

 

3. 신청자 테이블과 승인자 테이블을 별도 조회 후 애플리케이션 코드에서 수동 메핑

 

우선 아래 쿼리를 통해 신청자 데이터를 조회합니다.

SELECT PAGE.RNUM, PAGE.* FROM (
    select 
        * 
    from
        apply_table as at 
    where
        at.apply_id in (
            select
                att.apply_id 
            from
                approver_table as att
            where 
                att.approver_name=='searchApproverName'
        )
     #생략.....
) PAGE WHERE PAGE.RNUM BETWEEN (1 + 1) AND (1 + 5)

해당 쿼리를 사용하면 신청자, 승인자 기반 검색이 가능하고 신청자 기반 페이징도 가능합니다. 하지만 승인자 정보는 가져올 수 없기 때문에 승인자 테이블을 조회해야 합니다.

이때, 처음 문제가 되었던 방법은 승인자 테이블을 N번 조회한다는 것이었습니다.

이제 그렇게 하는 것이 아니라 아래 방법을 사용해 보겠습니다.

1. 신청자 정보를 조회한 결과를 JVM에 올린다.
2. 신청자 리스트 정보에서 applyId를 뽑아낸다.
3. 뽑아낸 applyId 리스트를 가지고 승인자 테이블에 한방 쿼리로 조회 요청을 한다.
4. 받아온 승인자 정보도 JVM에 올려서 [신청자 - 승인자] 정보를 코드로 메핑 해 준다.

이 방법을 통해 결론적으로 N+1 Select를 1+1 Select로 줄일 수 있고 최초 요구사항을 모두 만족할 수 있었습니다.

하지만 여기에도 함정이 있습니다?!?!

페이징의 경우 대용량 데이터에 대비해서 최대 요청 가능한 row 수가 100~200개로 한정적이라고 가정할 수 있습니다. 하지만, csv 내보내기(csv 다운로드)의 경우 특정 검색조건에 해당하는 모든 row를 페이징 없이 내보내야 합니다.

검색조건에 해당하는 row 수가 얼마나 될지 모르는 상황에서 무작정 JVM에 올리게 되면 OOM이 발생할 수 있습니다.

따라서 기존에는 Mybatis의 cursor를 사용해 문제를 해결했습니다. 

cursor에 대한 내용은 링크를 참고해 주세요: https://dev.to/composite/mybatis-cursor-t-bim

그런데, 2개의 테이블을 별도로 조회 후 JVM에 올려 직접 메핑 해 주는 작업을 하게 되면 해당 검색조건에 대한 모든 데이터를 일단 JVM에 올려야 하기 때문에 메모리 이슈가 발생할 수 있습니다.

 

cursor와 batch_size를 통한 내보내기

1.  신청자 테이블을 조회할 때는 cursor를 사용합니다. 

2.  가져온 신청자 레코드에서 batch_size 만큼 applyId를 모아서 승인자 테이블에 조회 쿼리를 날립니다.

3.  가져온 승인자 레코드와 신청자 레코드를 메핑 후 CSV 파일 스트림에 write 합니다.

4.  방금 작업한 객체의 레퍼런스를 끊어 GC 대상으로 만들어줍니다.(이건 사실 cursor가 알아서 함)

5.  2~4 작업을 반복합니다.

 

즉 신청자 테이블을 조회한 결과가 10만건이고 batch_size가 1000이라면, 실제로 JVM에는 신청자 데이터 기준 100(10만 / 1000 = 100)건 씩 올라가게 됩니다.  

이렇게 하면 1+N select 문제를 1+N/(batch_size) Select까지 줄일 수 있고 메모리 이슈도 batch_size를 통해 조절할 수 있게 됩니다.

 

 

결론

조회 api와 테이블을 설계할 때 메인테이블에 최초 조회 데이터 정보가 포함되도록 설계하는 것이 매우 중요하다는 것을 알게 되었습니다. 그랬더라면, 리스트 조회 api에서 1개의 테이블만 바라볼 수 있었을 것이고 문제가 이렇게 복잡해지지는 않았을 것입니다. [하지만 이런 상황을 만들지 않는것이 최고일 것이지만, 이러한 상황이 언제든 발생할 수 있는 게 실무라고 생각합니다.]

테이블이 분리되어 있는 상황에서 페이징 쿼리를 어떻게 만들 수 있는지, 이때 발생할 수 있는 메모리 이슈는 어떻게 해결할지 고민할 수 있었던 시간이었습니다.