페이징 쿼리의 성능 개선을 시도해보았다.
페이징 해주는 메소드를 실행했다.
실행되는 쿼리는 다음과 같다. (아직 N+1 문제를 해결하지 않은 상태이다.)
Hibernate: select article0_.id as id1_0_, article0_.body as body2_0_, article0_.create_date as create_d3_0_, article0_.member_id as member_i6_0_, article0_.title as title4_0_, article0_.view_count as view_cou5_0_ from article article0_ limit ?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select count(article0_.id) as col_0_0_ from article article0_첫번째 줄인
Hibernate: select article0_.id as id1_0_, article0_.body as body2_0_, article0_.create_date as create_d3_0_, article0_.member_id as member_i6_0_, article0_.title as title4_0_, article0_.view_count as view_cou5_0_ from article article0_ limit ?는 페이징을 위해 나간 쿼리이고
마지막 줄인
Hibernate: select count(article0_.id) as col_0_0_ from article article0_도 페이징에 필요한 카운트 쿼리이다.
그 사이의 10줄은 N+1 관련 쿼리이고, 다음 포스팅에서 해결해 볼 예정이다.
쿼리 성능을 눈으로 먼저 보기 위해 1천만 건의 데이터를 넣고 페이징 쿼리를 날려보았다.
HappyScrolls에는 게시글 조회 기능이 있다. 전체를 한번에 조회하진 않고, 페이징으로 조회한다.
페이징 조회를 위해 위와 같은 쿼리를 실행했는데,
무려 2초 이상 소모됐다.
서비스에서 페이지 검색하는데에 걸리는 속도라고 생각했을 때 도저히 사용할 수 없는 속도이지 않을까 싶다.
원인
이는 오프셋의 작동 방식이 0번째 페이지던 100번째 페이지던 테이블의 처음부터 탐색하기 때문이다. 따라서 맨 페이지가 마지막에 가까워질 수록 풀스캔 방식과 비슷한 성능을 지닌다. 이로 인해 offset의 크기가 커지면 성능저하가 발생하게 된다.
이렇게 0번째 오프셋은 훨씬 좋은 성능을 지니는 것을 볼 수 있다.
문제해결
이를 해결하기 위해 제로오프셋 방식을 적용해보았다. 제로 오프셋은 말 그래도 오프셋이 0이다. 직전 페이지의 마지막 id를 기억하고, where절로 그 id보다 큰 값부터 검색하는 것이다.
따라서 where절로 변경하였더니 시간이 어마어마하게 단축된것을 알 수 있었다.
하지만 이 방법은 무한 스크롤 방식에서만 사용 가능하다. 이전 페이지의 마지막 id를 기억해야 하기 때문이다.
limit*offset으로 계산할 수 없는 이유는, 테이블에서 데이터를 삭제하면, id가 당겨지는게 아니기 때문에 현재 존재하는 데이터와 id가 맞지 않기 때문이다.
다행히도 HappyScrolls는 무한 스크롤 방식을 채택했기 때문에 zero offset 방식을 사용할 수 있다.
다시 스프링으로 돌아가서 이전에 본 메소드를 개선하기로 했다.
QueryDSL 과 네이티브 쿼리 중 무엇을 사용할 지 고민했었는데, 다른 프로젝트에서 QueryDSL를 사용중이므로 이번엔 네이티브 쿼리를 사용하기로 했다. 네이티브 쿼리와 친해지고 싶었던 이유도 있다.
구현은 위와 같이 새로운 인터페이스에 쿼리를 작성하고,
기존의 JpaRepository를 상속한 인터페이스에서 새로 작성한 인터페이스를 다중 상속했다. 자바는 인터페이스만 다중 상속이 된다.
이렇게 하면 기존에 사용하던 ArticleRepository에서 메소드를 사용할 수 있다. 굳이 ArticleDAO를 만든 이유는 JPA 쿼리 메소드와 네이티브 쿼리를 분리하는게 관리에 좋을 것 같았기 때문이다. 또, 쿼리와 명령을 분리할 계획을 갖고 있었는데, 이번 기회에 나눠보는게 좋을 것 같다고 판단했기 때문이다. 기존에 상속받던 JpaRepository도 곧 빠이빠이할 계획이다 🥲
Hibernate: select member0_.id as id1_5_, member0_.email as email2_5_, member0_.nickname as nickname3_5_, member0_.point as point4_5_, member0_.thumbnail as thumbnai5_5_ from member member0_ where member0_.email=?
Hibernate: select article0_.id as id1_0_, article0_.body as body2_0_, article0_.create_date as create_d3_0_, article0_.member_id as member_i6_0_, article0_.title as title4_0_, article0_.view_count as view_cou5_0_ from article article0_ limit ?, ?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select count(article0_.id) as col_0_0_ from article article0_기존에 페이징 처리만 한 메소드의 실행 결과이다.
쿼리는 지난 번에 봤던 것과 같다. 근데 실행시간이 2.5초에 육박하는 말도 안되는 성능인 것을 확인할 수 있다.
zero offset을 이용하는 방법을 사용하면
Hibernate: select member0_.id as id1_5_, member0_.email as email2_5_, member0_.nickname as nickname3_5_, member0_.point as point4_5_, member0_.thumbnail as thumbnai5_5_ from member member0_ where member0_.email=?
Hibernate: SELECT * FROM Article where Article.id> ? limit ?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?
Hibernate: select member0_.id as id1_5_0_, member0_.email as email2_5_0_, member0_.nickname as nickname3_5_0_, member0_.point as point4_5_0_, member0_.thumbnail as thumbnai5_5_0_ from member member0_ where member0_.id=?위와 같이 결과는 같지만 266ms로 약 1/10의 시간이 소모되는 것을 알 수 있다.
다만 N+1로 발생하는 10개의 추가 쿼리는 당연히 아직도 발생한다.
🙂 🙃 🙂 🙃