[JPA] JPQL FROM절 subquery를 해결하는 방법 (inline view)

문제 상황 - JPA의 FROM절에 subquery를 넣는 상황.

 문제 상황은 다음과 같다.

 

 킥보드 사용량에 대한 consumption entity가 있고, 킥보드의 이동 경로에 대한 coordinate entity가 있다. consumption : coordinate는 1 : n 관계이며, coordinate entity에 다대일 매핑만 되어 있다.

@Entity
@Getter
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "consumption")
public class Consumption {
    @Id
    @Column(name = "consumption_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    
    @Column
    private Integer cost;
}

@Entity
@Getter
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "coordinate")
public class Coordinate {
    @Id
    @Column(name = "coordinate_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private Integer sequence;

    private Double longitude;

    private Double latitude;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "consumption_id")
    private Consumption consumption;
}

 

 이 상황에서 사용자가 쓴 킥보드 사용량과 이동 경로를 한 번에 불러오고 싶다! 단, 페이징을 해야 한다! 예를 들면 킥보드 사용량 10개와, 해당하는 이동 경로를 모두 가져오고 싶다는 말이다.

 

 다대일 매핑만 사용한 이유는, 일단 일대다 매핑을 최대한 지양하기 위해서이다. 일대다 매핑을 사용하면 foreign key가 다른 table에 있기 때문에 DB의 패러다임과 달라지는 문제가 발생하며, 연관관계를 위해 추가적인 update문이 나가기 때문에 overhead가 존재하기도 한다.

 Consumption과 Coordinate 사이에 양방향 매핑을 사용하면 편리하겠지만 최대한 최적화와 학습을 생각해서 다대일 매핑만 사용한다고 치자. 

그리고 양방향을 사용하는 것 자체가 entity 사이에 너무나도 명확한 dependency가 생겨버리기 때문에 별로 좋은 방식이 아닌 것 같다는 생각이 계속 든다.

 

SELECT * FROM
	(SELECT * FROM consumption c ORDER BY c.consumption_id ASC LIMIT 1 OFFSET 0) cresult
	LEFT JOIN coordinate co ON cresult.consumption_id = co.consumption_id;

 사용자가 쓴 킥보드 사용량과 이동 경로를 한 번에 불러오고 싶다면, DB에서는 위와 같은 쿼리를 날리면 된다.

 

 그런데 JPQL에서 이를 사용할 수 없다. JPQL에서는 FROM 절에 subquery를 넣을 수 없기 때문이다.

 

 

 

문제 해결 방안

 문제를 해결할 수 있는 방법은 크게 3가지가 있을 것 같다.

1. 어떻게든 join을 써서 풀어보기
2. query를 2번 날리던가
3. native query를 사용하던가.

 

 그러나 각각의 방식에는 마음에 들지 않는 것이 하나씩 있었다.

1. join으로 풀어보기 - paging을 해야 한다. join 결과에 paging을 걸면 coordinate에 paging이 걸린다. 아무리 생각해도 join으로 풀 수가 없었다.
2. query 2번 날리기 - 1번은 paging을 써서 consumption을 가져오고, 다음 번에 첫 번째 query 결과를 사용하는 방법. 쿼리를 2번 날리는 것 자체가 overhead이다.
3. native query 사용하기 - 그러면 추가 DTO도 사용해 Object[] 결과값을 매핑도 해야 하기 때문에 읽기 힘들며 (관리의 overhead), query가 DB에 종속되는 단점이 있었다.

 

 1번은 아예 불가능한 방법. 2번과 3번을 고민하다 조금이나마 더 유지보수하기 좋을 것 같은 2번 방식을 택했다. 2번 해결책은 DB에 대한 의존성도 낮고, JPQL을 사용하므로 JPA의 최적화 + 객체지향으로 문제를 풀어나갈 수 있기 때문이었다.

 그러나 여기에도 의존성 문제는 당연히 발생하는데, CoordinateRepository가 ConsumptionRespository에 의존하게 된다는 것이다. 그래도 이 문제는 양방향으로 인해 발생하는 문제보다는 낫다고 생각한다. 양방향을 쓰다 보면 dependency가 cycle을 이룰 수도 있고, 그렇지 않더라도 dependency가 tree 형태에서 벗어나기 굉장히 쉬워진다. 이런 상황이 너무 싫어서 굳이 다대일 매핑을 선택했다.

 

 

 

해결 방안 - query 2번 날리기

public List<Consumption> findConsumptionsWithPaging(PageDto pageDto){
    String consumptionPagingQuery = "SELECT c FROM Consumption c ORDER BY c.id DESC";
    return em.createQuery(consumptionPagingQuery, Consumption.class)
                .setFirstResult((pageDto.getPageNum() - 1) * pageDto.getPageSize())
                .setMaxResults(pageDto.getPageSize())
                .getResultList();
}

 consumption을 paging을 적용한 결과로 받아온다. 받아온 결과를 consumptions라고 하자.

 

public List<Coordinate> findCoordinatesByConsumptions(List<Consumption> consumptions){
    String coordinatesByConsumptionsQuery = "SELECT co FROM Coordinate co WHERE co.consumption IN :consumptions";
    return em.createQuery(coordinatesByConsumptionsQuery, Coordinate.class)
                .setParameter("consumptions", consumptions)
                .getResultList();
}

 그 consumptions의 IN 문을 사용해서 가져온다.

 

 이렇게 짜는 것은 효율적이지는 않지만... 확장성과 성능은 어느 정도 반비례에 있기 때문에... 성능은 조금 떨어지지만 이게 맞는 것 같다.

 

 

 

후속 의문점 + 벤치마크

 지금 예시로 든 것은 작은 query지만, 만약 더 복잡한 query라면 어떻게 될까. 작은 query 여러 개를 날리는 것이 좋을까, 아니면 큰 native query 하나를 날리는 것이 좋을까?

 

lock contention

 소마 멘토님께 여쭤봤을 때는, 큰 query 하나를 처리할 때는 그것을 처리하는 동안 DB에 write lock이 걸리기 때문에 다른 client들이 DB에 접근하지 못한다고 했다. 반면 작은 query 여러 개를 쓰면 (짧은) DB 호출을 여러 번 하게 되는 것이므로 client들의 지연 시간이라는 입장에서 보았을 때는 비슷하다는 말을 해 주셨다.

 

 이 말에는 조금 의문이 생기는데, select문은 수정하지 않으니까 shared lock이 걸릴 것 같다. 그래서 여러 client가 동시에 동일한 table에 접근하는 경우에는 딱히 문제가 안 될 것 같긴 하다. 문제는 exclusive lock인 경우인데... 이 경우에는 설명하신 내용이 맞는 것 같다. 그리고 read와 write가 이어져서 발생하는 read-write 문제 (OS에서 살펴본)가 발생하는 경우도 있으므로 이 경우도 lock contention이 발생할 수 있다. 이는 DB의 구현에 달려있기 때문에 lock contention까지 신경쓴다면 직접 여러 client가 같은 DB에 접속하는 multi thread 상황을 만들고 테스트해봐야 할 것 같다.

 

monster query vs multiple small queires

 큰 쿼리 하나를 여러 개의 작은 쿼리로 나누는 것을 join decomposition이라고 한다. 이렇게 사용할 경우 일방적인 손해 같지만 몇 가지 이점도 있다고 한다. (High Performance MySQL 책) 

• caching이 효율적이게 된다.
• lock contention이 줄어든다.
• optimizer의 최적화 정도는 join보다 in이 더 높기 때문에 더 빠를 수 있다.
• join의 경우 중복된 row들을 계속 탐색하는데, 이를 줄일 수 있다.
• 중복된 row 탐색을 줄일 수 있기 때문에 memory 사용량도 줄일 수 있다.

 

 반면, DB와 application 사이에 데이터를 전송하는 overhead도 존재한다. 만약 DB가 별도의 서버로 분리되어 있는 경우에는 network overhead도 발생할 것이다.

 

 많은 글들을 찾아봤을 때, 절대적인 시간 하나로만 따져봤을 때는 큰 쿼리 하나만 사용하는 것이 훨씬 더 낫다고 한다.

 

 

벤치마크

 그래서, 벤치마크용 툴을 사용하는 것도 아니고 너무 큰 query를 사용하는 것도 아니지만, 나름대로 어떻게 코드를 짜서 벤치마크 해 봤다.

 

환경

•  하드웨어 환경 : 2018년에 구매한 LG 15G870-XA50K이고, 프로세서는 i5-7300HQ (2.5Hz) 쿼드코어이다.
• 개발 환경 : WSL이고, ubuntu를 사용했다. 이후 링크에 작성되어 있는 대로 spring boot와 docker를 사용해 MySQL을 올렸다.

 사용한 모든 소스코드 및 테스트 코드는 다음 링크에서 확인할 수 있다. 소스코드 링크,  테스트 코드 링크

 

실험

 테스트하는 코드는 바로 위에서 다룬 코드들, 작은 쿼리 2개와 큰 쿼리 1개이다. 간단하게 보는 거니까 repository에서만 DB에서 값을 받아오는 시간만 비교하면 될 것 같다.

1. 큰 query 1개가 repository에서 값을 리턴하는 데 걸리는 시간 - LargeQueryRepositoryTime이라 하자.
2. 작은 query 2개가 repository에서 값을 리턴하는 데 걸리는 시간 - SmallQueriesRepositoryTime이라 하자.

 

 SQL에 직접 데이터를 넣고 테스트하는 것이 훨씬 더 빠른 것 같아서 테스트 코드에서 데이터를 삽입하지 않고, MySQL procedure를 짰다. procedure는 아래와 같다. procedure 안에 있는 i의 최댓값만 바꾸어서 실험했다.

DELIMITER //

CREATE PROCEDURE InsertTest1000()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE j INT DEFAULT 1;

  START TRANSACTION;
  WHILE i <= 1000 DO
    INSERT INTO consumption (cost) VALUES (i);
    SET j = 1;
    
    WHILE j <= 100 DO
      INSERT INTO coordinate (latitude, longitude, sequence, consumption_id) VALUES (i, j, j, i);
      SET j = j+1;
    END WHILE;

    SET i = i + 1;
  END WHILE;

  COMMIT;
END //

DELIMITER ;

 보수적으로, 일단 consumption의 개수만 수정하려고 한다. 100개, 1000개, 10000개, 100000개 정도로 두고, 각 consumption당 100개의 coordinate가 있다고 두겠다. 3회 실험해서 평균값을 냈다.

10000개 삽입할 때는 20초 정도 걸렸는데, 100000개 넣으니까 꽤 4분 정도로 꽤 오래 걸린다. 혹시라도 같은 실험을 반복하는 사람들은 알아두면 좋을 것 같다.

 

 결과는 아래와 같다. 단위는 ms이다.

benchmark 결과

query benchmark.xlsx

0.01MB

 

 일단 차이가 꽤 나는 것을 볼 수 있다. 100개일 때는 오차가 매우 작기 때문에 비슷하게 걸리는구나... 싶은데, 1000개, 10000개, 100000개일 때는 거의 2배가량 차이가 난다. 그래도 paging을 해서인지, 0.25초 내외로 다 처리가 된다! 이정도먼 허용 범위 내이므로, 어떤 방법을 사용해도 성능상 크게 차이는 없다. 따라서 성능보다는 객체지향과 확장성을 더 신경쓰는 방향이 좋을 것이다.

 

 

 

결론

 paging 하려고 consumption에 paging subquery를 걸어놔야 하는데, JPQL은 FROM 절에 subquery가 안걸려서 query 2번 날렸다.

 

 그리고 대용량의 데이터를 다룰 경우 결과가 바뀌는 요인들이 많으니 상황에 맞게 사용해야 한다. 가능하면 벤치마킹을 한 후 더 좋은 것으로 쓰면 될 것 같다. 벤치마킹 할 때는 시간 뿐만 아니라 상황에 맞게, 메모리나 동시 접속 등도 고려해야 한다.