티스토리 뷰
들어가기
이번 글에서는 최근 진행했던 프로젝트(분양 웹서비스)에서 게시글 조회 API를 쿼리 튜닝을 통해 개선한 경험을 공유하려 합니다.
본 글은 아래와 같은 사항을 고려하시는 분들께 조금 더 도움이 될 것 같습니다.
- 게시판 목록 조회 시 게시글 데이터 및 관련된 추가 데이터(좋아요수, 댓글수) 등을 함께 조회하고자 하는 분
- 기존 아키텍쳐는 유지하면서 쿼리 및 ORM 코드만 변경하여 단기간 조금 더 나은 성능 개선을 원하시는 분
개선 배경
현재 펫허브 서비스는 무중단 배포로 운영되고 있으며, 일간 사용자는 3인 이하입니다..(이는 팀원수와 일치합니다 haha..)
현재와 같이 게시글이 거의 추가되지 않는 상태일 경우 게시판 조회시 체감 응답지연시간은 오래걸리지 않습니다.
하지만 미래의 사용자수가 추가로 늘어난다는 가정했을 때 가장 우선시 개선되어야 할 사항이 조회시 응답지연시간을 최소화하는 것이라 생각이 들었습니다. 그리고 이를 개선하기 위해 아키텍쳐 확장을 고려하기전 관련 API 코드와 데이터베이스를 확인하고 쿼리를 개선하는 것이 우선이라는 판단을 했습니다.
위와 같은 이유로 게시판 조회 성능 개선을 위해 쿼리 튜닝을 시도하게 되었습니다.
AS - IS
펫허브 서비스에서 게시글 목록을 조회시 REST API를 통해 응답을 하고 있으며, 해당 API는 Spring과 SpringDataJPA를 통해 개발되었습니다. 또한 게시글과 관련된 데이터들은 MySQL 5.7.4버전을 통해 저장 및 관리되고 있는 상태입니다.
QueryDslRepository
게시판 조회 API는 QueryDSL을 사용한 동적 쿼리를 통해 기본 조회 및 인기순 조회, 검색 조회를 함께 처리하고 있습니다. 게시글 목록을 위해 공통적으로 사용하는 쿼리를 JPQLQuery 인터페이스를 통해 분리했으며, 게시판 조회시 사용되는 쿼리를 별도의 메서드로 분리하여 동적으로 쿼리가 발생되도록 하였습니다.
//== 게시글 목록 공통 쿼리 ==//
private JPQLQuery<ArticleListDto> findArticleListQuery(){
JPQLQuery<Integer> likeCntByCommunity = JPAExpressions.select(articleHeart.count().intValue())
.from(articleHeart)
.where(articleHeart.community.eq(community));
JPQLQuery<Integer> commentCntByCommunity = JPAExpressions.select(comment.count().intValue())
.from(comment)
.where(comment.community.eq(community));
return query.select(Projections.fields(ArticleListDto.class,
community.articleNo,
community.title,
community.content,
member.nickname,
community.viewCount,
Expressions.as(likeCntByCommunity,"likeCnt"),
Expressions.as(commentCntByCommunity,"commentCnt"),
community.regDate,
community.thumbnail
))
.from(community)
.leftJoin(member).on(community.regId.eq(member.email))
.where(community.blindYn.eq(BlindEnum.NORMAL),
community.logicalDel.eq(LogicalDelEnum.NORMAL))
.groupBy(community.articleNo,community.title,community.content,
member.nickname,community.viewCount,community.regDate,
community.thumbnail);
}
//== 게시글 목록 조회 - 검색용 ==//
public List<ArticleListDto> findArticleList(String order, Integer pageNum, Integer option, String keyword) {
// row 시작 위치
final Integer offset = pageNum == null? 0 : (pageNum * 10) - 10; // 1 -> 0, 2 -> 10 3 -> 20
// row 개수
final Integer limit = 10;
// orderBy에서 like 서브쿼리의 alias를 읽기 위한 객체 생성
StringPath likeAlias = Expressions.stringPath("likeCnt");
BooleanExpression searchCondition = null;
if(Objects.nonNull(option)){
switch (option){
case 1:
searchCondition = titleLike(keyword);
break;
case 2:
searchCondition = contentLike(keyword);
break;
case 3:
searchCondition = titleLikeOrContentLike(keyword);
break;
default:
break;
}
}
return findArticleListQuery()
.where(searchCondition)
.orderBy(order.equals("like") ? likeAlias.desc() : community.articleNo.desc())
.offset(offset).limit(limit)
.fetch();
}
위 코드로 개발된 API에 클라이언트가 요청할 경우 아래와 같은 SQL이 JPA를 통해 발생됩니다. (JPA가 QueryDSL을 통해 SQL을 발생시키는 방식은 이번 게시글에 주요한 내용이 아님으로 생략하도록하겠습니다.)
SELECT
community.article_no AS col00,
community.title AS col10,
community.content AS col_20,
member.nickname AS col30,
community.view_cnt AS col_40,
(
SELECT CAST(COUNT(ah.heart_no) AS SIGNED)
FROM ARTICLE_HEART ah
WHERE ah.article_no = community.article_no
) AS col_50,
(
SELECT CAST(COUNT(cmt.comment_no) AS SIGNED)
FROM COMMENT cmt
WHERE cmt.article_no = community.article_no
) AS col60,
community.reg_date AS col70,
community.thumbnail AS col80
FROM
COMMUNITY community
LEFT OUTER JOIN
MEMBER member ON (community.reg_id = member.email)
WHERE
community.blind_yn = 0
AND community.logical_del = 0
GROUP BY
community.article_no,
community.title,
community.content,
member.nickname,
community.view_cnt,
community.reg_date,
community.thumbnail
ORDER BY
community.article_no DESC
LIMIT 10;위 SQL은 "WHERE절 조건에 해당되는 게시글 데이터를 조회한 뒤 내림차순(Descending) 정렬하고 그 중 10개만 반환해라."라는 의미를 가지고 있습니다. 좋아요수와 댓글수를 같이 조회하기 위해 스칼라 서브쿼리를 사용했으며 게시글 작성자의 닉네임을 포함시키기 위해 회원 테이블과 게시글 테이블을 LEFT OUTER JOIN 했습니다.
조회 성능 확인
성능 개선에 필요한 제대로된 검증을 위해 배포 환경과 유사하게 테스트 환경을 구축하고 MySQL의 프로시저 문법을 사용하여 관련 테이블(게시글, 댓글, 좋아요)에 더미데이터를 각각 20,000개씩 저장했습니다.
더미데이터를 저장 후 쿼리를 개선 하기 이전의 실행시간은 아래와 같습니다.
쿼리의 실행 시간이 76.656 sec가 소요되었으며, Postman을 사용하여 API를 요청해본 결과 76.26 sec로 매우 느리게 응답하는 것을 확인할 수 있었습니다.(쿼리의 실행시간보다 API 응답시간이 조금 더 빠른 이유는 실행시 +- 10초 정도의 오차범위가 존재하기 때문입니다.)
좋지 않은 응답 시간에 가장 큰 부분을 차지하는 쿼리 실행 시간을 개선하고자 MySQL의 'EXPLAIN' 기능을 통해 쿼리의 실행계획을 우선 확인해보았습니다.
실행 계획을 확인해본 결과는 아래와 같습니다.
- 처음 엑세스하는 테이블과 스칼라 서브쿼리로 엑세스하는 테이블이 Full Table Scan(type : ALL)되어 대량의 레코드를 읽음.
- 임시테이블(Using temporary)이 사용되었으며, 인덱스를 통해 정렬할 수 없어 FileSort(Using filesort)가 발생.
- Using where : 스토리지 엔진에서 전달한 레코드들을 MySQL 엔진 레이어에서 별도의 가공을 통해 필터링(여과) 작업을 처리한 경우에만 표시되는 코멘트입니다. 위 실행계획의 경우 스토리엔진에서 전달 받은 19813건의 레코드 중에서 1%(filtered)를 제외한 나머지는 걸러지기에 매우 비효율적이라고 판단할 수 있다.
- Using temporary : 쿼리를 처리하는 동안 메모리상 또는 디스크상에 생성된 임시 테이블이 사용된 것을 의미하는 코멘트입니다. 대표적인 예시로는 GROUP BY 컬럼과 ORDER BY 컬럼이 다를 경우 임시테이블을 사용하게 됨으로 코멘트가 표시됩니다.
- Using filesort : ORDER BY 처리가 인덱스를 사용하지 못할 때만 표시되는 코멘트입니다. 이는 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 또는 힙 소트 알고리즘을 이용해 정렬을 수행하게 된다는 것을 의미합니다.
- 'Real MySQL' 도서 참조 -
위 결과를 통해 현재의 쿼리가 대량의 디스크 I/O를 유발할 수 있으며, 조인 결과를 임시 테이블로 저장 후 정렬함으로 많은 부하가 발생될 수 있기에 성능상 매우 좋지 않다는 것을 알게 되었습니다.
T0 - BE
문제를 조금 더 세분화하여 분석하기 위해 통계값을 조회시 사용되는 스칼라 서브쿼리를 잠시 제외했습니다. 이후 가장 불필요하다고 판단되는 GROUP BY 절을 주석 처리하여 제외한 후 다시 쿼리를 실행해 보았습니다.
파란색 사각형 내부의 실행시간은 GROUP BY 절을 주석 처리하기 전의 쿼리 실행시간이며 2.481 sec가 소요되었습니다. GROUP BY 절을 주석 처리 후 실행 결과 0.014 sec가 소요되었으며 실행에도 문제가 되는 부분이 없었습니다.
(이 결과를 통해 지금까지 제대로된 검증을 하고 있지 않았다는 판단이 들었으며 차후 쿼리 작성시 단계별로 꼭 필요한 명령문만을 사용하고 불필요한 부분이 없는지 검증 하자라는 다짐을 하게 되었습니다.)
위 수정한 쿼리의 실행계획을 확인해본 결과, 옵티마이저의 예상치로 19,813건의 레코드를 읽고 체크한 이전과 달리 10개의 레코드만 읽게 되었으며, Table Full Scan에서 Index Full Scan(type: index)으로 개선되었음을 확인 할 수 있었습니다.
위 결과가 Index Full Scan으로 '효율적으로 개선된것이 아니지 않을까?'라는 의문을 가질 수 있습니다. 그러나 LIMIT 조건으로 인하여 인덱스를 역순으로 읽어 10개만 가져오기때문에 '효율적으로 개선되었다.'라는 표현이 가능해집니다.
인라인 뷰를 이용한 개선 시도
이전 세분화를 위해 임시적으로 제외한 기존 서브 쿼리를 추가하려하는데, 기존 스칼라 서브쿼리가 아닌 JOIN절에 인라인뷰(Inline view)방식으로 서브 쿼리를 사용해보았습니다. 이 시도는 기존 방식과 어떤 차이가 있는 지를 확인하기 위함이였습니다.
인라인 뷰(Inline view) 서브쿼리란?
FROM 절에 사용하는 서브쿼리를 말합니다. 사용된 서브쿼리는 하나의 테이블처럼 인식되어 SELECT 절을 통해 조회가 가능해집니다.
LEFT OUTER JOIN절에 서브쿼리를 추가하고, 개선 전 인덱스를 타지 못했던 ARTICEL_HEART 테이블에 드라이빙 테이블과 FK로 연관된 컬럼을 인덱스에 추가했습니다.
수정 한 쿼리를 실행한 결과 offset을 적용하지 않은 상태에서는 실행시간 0.053 sec가 소요되었으며, 데이터베이스에 저장된 50% 이상의 레코드 개수(10000개)를 offset으로 입력한 상태에서는 실행시간 0.105 sec가 소요되었습니다.
실행 계획을 분석한 결과 2개의 파생 테이블(Derived Table)이 생성 되었으며, 파생 테이블과 드라이빙 테이블을 조인되었다는 것을 확인할 수 있었습니다. 각각의 파생테이블은 인덱스를 탔지만(Using Index) Index Full Scan으로 많은 양의 레코드를 읽게되어 해당 시도는 효과적인 개선 방법이 아니라는 판단을 내리게 되었습니다.
최종 개선 시도 및 결과
기존 방식과 같이 스칼라 서브쿼리를 다시 도입했으며 실행한 결과는 아래와 같습니다.
최종적으로 쿼리를 실행한 결과, 인라인 뷰를 사용했을 경우보다 개선된 0.0026 sec의 실행시간을 얻을 수 있었습니다.
실행 계획 또한 만족할 수도 있지만 Using where 코멘트가 조금 아쉬웠습니다. 드라이빙테이블에서 발생하는 Using where는 여과를 위해 필요한 부분이지만 MEMBER(회원) 테이블의 Using where 코멘트는 의문점이 들었습니다. 이러한 의문점을 통해 다음과 같은 추가 개선 사항을 도출하게되었습니다.
기존 쿼리에서는 COMMUNITY(게시글) 테이블과 MEMBER(회원) 테이블을 LEFT OUTER JOIN하여 게시글 작성자의 닉네임을 조회했습니다. 하지만 조인에 사용되는 reg_id 컬럼 값이 MEMBER 테이블의 email 컬럼 값에 필수적으로 존재하기 때문에 현재의 조인방식은 적절치 못하다라는 판단이 들었습니다. 이러한 판단을 근거로 조인 방식을 INNER JOIN으로 변경한 결과 실행계획이 Using where에서 Using index condition으로 개선되었습니다.
개선된 쿼리의 실행시간을 이전과 비교해본 결과, 가시적인 개선이 이루어지지는 않았습니다. 이는 현재 입력된 데이터의 양이 적기때문이라고 판단하고 있으며, 레코드의 개수가 10만건, 100만건 혹은 그이상이 될 경우 Using index condition이 의미하고 있는 인덱스 컨디션 푸시다운(Index condition pushdown) 기능의 활성화로 인하여 몇배에서 몇십배의 성능차이를 보일 것이라 생각하고 있습니다.
인덱스 컨디션 푸시다운(Index condition pushdown) 이란?
인덱스 범위 조건을 사용할 수 없더라도 인덱스에 포함된 컬럼의 조건이 있다면 스토리지 엔진으로 조건이 전달되어 인덱스를 비교할 수 있게 하는 기능입니다. 해당 기능을 사용시 인덱스를 이용해 최대한 필터링까지 완료한 레코드에 대해서만 테이블 읽기를 수행하기 때문에 불필요한 테이블 레코드 조회를 줄일 수 있습니다.
위와 같이 최종적으로 개선된 쿼리를 사용하기 위해 QueryDSL 코드를 수정했으며, API 요청시 응답소요시간은 다음과 같습니다.
Postman을 통해 응답시간을 확인해본 결과, 개선전 76.26 sec 소요된 것에 대비하여 개선후 33 ms 소요되는 것을 확인 할 수 있었습니다. 실제 배포 환경에서는 테스트 환경과의 시스템 성능 및 구조 차이로 인하여 응답시간이 조금 더 소요될 수 있으나 사용자가 체감하기에 미미한 차이일 것이라 예상됩니다.
마무리
이번 게시글 내용에 대해 요약한 내용은 아래와 같습니다.
1. 개선 전 게시글 조회시 쿼리 실행 시간이 76.656 sec 소요, API 응답시간 76.26 sec 소요
2. 실행 계획을 통해 문제점을 분석하고 쿼리 튜닝 및 개선함.
3. 개선 후 게시글 조회시 쿼리 실행 시간 0.0026 sec, API 응답시간 33 ms로 개선됨
이번 시도를 통해 기본 조회 성능은 개선시켰으나, 조회에 대한 조건을 추가(인기순, 키워드 검색)할 경우의 성능은 개선이 되지 않은 상태입니다. 조건이 추가된 조회는 차후 반정규화 또는 통계테이블을 분리하는 방향으로 개선한 뒤 게시하도록하겠습니다.
현 게시글에 개선되어야할 사항이 있다고 판단이 되신다면 언제든 의견주세요. 빠르게 수정 또는 답변드리도록하겠습니다.
지금까지 읽어주셔서 감사합니다.
참고자료
- 'Real MySQL 1권'
- MySQL Limit 최적화 참고 - https://jeong-pro.tistory.com/m/244
- Total
- Today
- Yesterday