[StudyHub] 성능 관점에서 확인해본 조회 쿼리문

서론

---

RealMySQL 8.0을 읽으면서 프로젝트에서 작성한 대부분의 쿼리문들이 성능을 염두해두지 않고 작성했다는 것을 알았다.

 

지금은 데이터 수가 적기 때문에 괜찮지만 추후 데이터 수가 많아졌을 때를 대비해 성능도 올릴겸, 책도 복습할겸 해서 성능 개선 여지가 있는 쿼리문을 확인해보겠다.

 

 

 

 

LIKE

---

@Override
    public List<PostDataByInquiry> findByInquiry(final InquiryRequest inquiryRequest, final Pageable pageable, Long userId) {
        JPAQuery<PostDataByInquiry> data = jpaQueryFactory
                .select(Projections.constructor(PostDataByInquiry.class,
                        studyPostEntity.id.as("postId"),
                        studyPostEntity.major, studyPostEntity.title,
                        studyPostEntity.studyStartDate,
                        studyPostEntity.studyEndDate,
                        studyPostEntity.createdDate,
                        studyPostEntity.studyPerson,
                        studyPostEntity.filteredGender,
                        studyPostEntity.penalty,
                        studyPostEntity.penaltyWay,
                        studyPostEntity.remainingSeat,
                        studyPostEntity.close,
                        bookmarkPredicate(userId),
                        Projections.constructor(
                                UserData.class,
                                userEntity.id,
                                userEntity.major,
                                userEntity.nickname, 
                                userEntity.imageUrl
                        )
                ))
                .from(studyPostEntity)
                .leftJoin(userEntity).on(studyPostEntity.postedUserId.eq(userEntity.id))
                .where(textEq(inquiryRequest.getInquiryText())
                .or(majorEq(inquiryRequest.getInquiryText(), inquiryRequest.isTitleAndMajor())))
                .orderBy(hotPredicate(inquiryRequest), studyPostEntity.createdDate.desc())
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize() + 1);

        if (userId != null) {
            data.leftJoin(bookmarkEntity).on(studyPostEntity.id.eq(bookmarkEntity.postId)
            .and(bookmarkEntity.userId.eq(userId)));
        }

        return data.fetch();
    }

 

 

queryDsl로 작성한 전체 조회 쿼리문이다.

 

private BooleanExpression textEq(String inquiryText) {
    return studyPostEntity.title.contains(Objects.requireNonNullElse(inquiryText, ""));
}

 

 

위의 메서드를 이용해서 요청 문자열과 비슷한 제목이 있으면 반환해주는 방식이다.

 

queryDsl의 contains 메서드는 [like %inquiryText%] 문자열 앞,뒤에 와일드카드 문자를 넣어 연관된 제목을 검색하게 된다.

 

하지만 문자가 포함되는 데이터 (ex %inquiryText%), 해당 문자로 끝나는 데이터 (ex %inquiryText) 의 경우 인덱스를 이용한 검색이 불가능하다.

 

이는 Index의 자료구조가 B*Tree로 형성되어있고, 시작 값을 기준으로 데이터를 조회하기 때문이다.

 

쿼리 실행계획을 통해 인덱스를 타는 여부를 직접 확인해보겠다.

 

 

 

 

문자의 시작과 일치하는 데이터를 검색했을 경우만 인덱스 레인지 스캔으로 조회되는 것을 볼 수 있다.

 

인덱스 레인지 스캔을 이용해 조회하니 적은 데이터만 조회하기 때문에 성능이 좋아진다. 하지만 8만개의 데이터 기준으로 쿼리 실행시간이 15ms가 나왔기 때문에 아직 규모가 작은 스터디허브 프로젝트에서는 기능요구사항에 걸맞게 문자가 포함되는 데이터 (ex %inquiryText%) 까지 읽는 것이 좋을 것 같다.

 

 

 

 

 

WHERE + ORDER BY

---

 

.where(textEq(inquiryRequest.getInquiryText()).or(majorEq(inquiryRequest.getInquiryText(), inquiryRequest.isTitleAndMajor())))
.orderBy(studyPostEntity.close.asc(), hotPredicate(inquiryRequest), studyPostEntity.createdDate.desc())

 

 

쿼리문에서 where 조건과 order by 절을 함께 사용하는 구문이 있다.

 

WHERE 절과 ORDER BY 절이 같이 사용된 하나의 쿼리 문장은 다음 3가지 중 한 가지 방법으로만 인덱스를 이용한다.

 

• WHERE 절과 ORDER BY 절이 동시에 같은 인덱스를 이용 : WHERE 절의 비교 조건에서 사용하는 칼럼과 ORDER BY 절의 정렬 대상 칼럼이 모두 하나의 인덱스에 연속해서 포함돼 있을 때 이 방식으로 인덱스를 사용할 수 있다. 이 방법은 나머지 2가지 방식보다 훨씬 빠른 성능을 보이기 때문에 가능하다면 이 방식으로 처리할 수 있게 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다.
• WHERE 절만 인덱스를 이용 : ORDER BY 절은 인덱스를 이용한 정렬이 불가능하며, 인덱스를 통해 검색된 결과 레코드를 별도의 정렬 처리 과정(Using Filesort)을 거쳐 정렬을 수행한다. 주로 이 방법은 WHERE 절의 조건에 일치하는 레코드의 건수가 많지 않을 때 효율적인 방식이다.
• ORDER BY 절만 인덱스를 이용 : ORDER BY 절은 인덱스를 이용해 처리하지만 WHERE 절은 인덱스를 이용하지 못한다. 이 방식을 ORDER BY 절의 순서대로 인덱스를 읽으면서 레코드 한 건씩 WHERE 절의 조건에 일치하는지 비교하고, 일치하지 않을 때는 버리는 형태로 처리한다. 주로 아주 많은 레코드를 조회해서 정렬해야 할 때는 이런 형태로 튜닝하기도 한다.

 

https://liljay.tistory.com/17

[StudyHub] 다중 칼럼 인덱스를 이용한 조회 성능 개선

 

다중 칼럼 인덱스를 이용해 ORDER BY 성능을 올린 이전 포스트에선 WHERE 조건절이 모든 문자열을 허용하는 방식으로 진행했다.

 

하지만 WHERE 조건이 추가될 경우 문제가 생긴다.

 

예를 들어 post 데이터가 10만개가 있을 때 WHERE 조건절에 걸맞는 데이터가 10만번째 근처에 몰려있을 경우 쿼리문은 10만개의 데이터를 모두 스캔해야한다.

 

 

'화이팅' 이라는 content를 가진 데이터는 총 19개이다. 인덱스가 없을 때 아래 쿼리문을 실행하게되면 테이블 풀 스캔을 통해 모든 데이터를 조회할 것이다.

 

SELECT * FROM post WHERE post.content LIKE '화이팅%' 
	ORDER BY post.remaining_seat asc, post.created_date desc 
    	LIMIT 20;

 

 

 

인덱스를 타지않고 전체 테이블 풀 스캔을 통해 94ms가 걸렸다.

 

그렇다면 이번엔 이전 포스트에서 진행한 것 처럼 ORDER BY 절에서 사용된 칼럼들만 인덱스를 생성해보자.

 

 

인덱스를 생성한 뒤 실행했지만 인덱스 생성 전보다 성능이 5.14배 느려진 것으로 확인된다.

 

인덱스 테이블을 통해 조회하면서 순차 IO가 아닌 랜덤 IO를 이용해 조회했기 때문에 성능이 느려진 것으로 보인다.

 

이런 문제를 없애고자 WHERE 조건절에 사용되는 post.content 와 정렬 조건에 사용되는 post.remainingSeat, post.createdDate를 모두 포함하는 인덱스를 만들어 해결해보겠다.

 

 

 

인덱스 없이 조회했을 때 보다 5.87배, ORDER BY 조건만 인덱스를 걸고 조회했을 때 보다 30.25배 성능이 좋아졌다.

 

 

 

 

 

JOIN

---

 

MySQL에서 JOIN 순서와 인덱스에 대해 살펴보자.

 

• 두 칼럼 모두 각각 인덱스가 있는 경우 : 어느 테이블을 드라이빙으로 선택하든 인덱스를 이용해 드리븐 테이블의 검색 작업을 빠르게 처리할 수 있다. 이럴 때 옵티마이저가 통계 정보를 이용해 적절히 드라이빙 테이블을 선택하게된다.
• 한개의 칼럼에만 인덱스가 있는 경우 : 인덱스가 있는 칼럼을 드라이빙 테이블로 선택하고, 없는 칼럼을 드리븐 테이블로 선택한다.
• 두 칼럼 모두 인덱스가 없는 경우 : 어느 테이블을 드라이빙으로 선택하더라도 드리븐 테이블의 풀 스캔은 발생하기 때문에 옵티마이저가 적절히 드라이빙 테이블을 선택한다.

 

위에서 봤던 쿼리문은 아래와 같은 방식으로 userEntity와 studyPostEntity가 JOIN 하게된다.

.leftJoin(userEntity).on(studyPostEntity.postedUserId.eq(userEntity.id))

 

 

studyPostEntity의 postedUserId는 인덱스가 없고, userEntity의 id는 인덱스가 있기 때문에 studyPostEntity가 드라이빙 테이블 userEntity가 드리븐 테이블로 선택될 것으로 보인다.

 

userEntity의 클러스터 인덱스를 이용해 join 하고 있는 것을 확인할 수 있다.

 

나머지 쿼리문 모두 클러스터 인덱스를 이용해 join 하고 있기 때문에 JOIN시 테이블 풀 스캔으로 인한 성능저하는 없을 것 이다.

 

 

결론

---

아직 데이터가 적기 때문에 테이블 풀 스캔을 통해서 데이터를 가져오는 방식도 큰 성능저하가 생기지 않는다.

 

그렇기때문에 기능 요구사항대로 문자가 포함되는 데이터 (ex %inquiryText%)를 가져와도 좋다.

 

하지만 데이터 수가 10만개 이상이 될 때 부터 100ms 이상의 시간이 걸릴 수 있기 때문에 해당 문자로 시작되는 데이터 (ex inquiryText%)를 가져오게 변경한 후 WHERE + ORDER BY에서 사용되는 컬럼을 모두 포함하는 인덱스를 생성해 성능을 높이는 것이 좋을 것 같다.