[DB] 쿼리 개선 일지 - MySQL 커버링 인덱스 (Using Index)

 

App Store에 심사를 올리게 되면서, "유저가 불쾌할 수 있는 게시글에 대해서 즉각적인 조치가 가능하게 하라" 라는 피드백을 받았습니다.

아무래도 소셜네트워크 서비스이다 보니까, 조금 민감한 부분이라고 생각이 됩니다.

 

[요구사항]

그래서 유저가 게시글 신고 이후에 즉각적인 조치를 하기 위해서 다음과 같은 기능 요구가 생겼습니다.

 

기능: 유저가 신고처리를 했던 게시글은, 유저에게 보이지 않는다.
성능: 신고 처리 테이블을 스캔하는 비용을 최소화

 

 

전체 코드는 접어두었습니다.

 

    @Override
    public List<PostFetchDto> findNextPageByCommmand(PostFetchCommand command) {
        Map<Long, PostFetchDto> resultMap = queryFactory.from(post)
                .join(post.user)
                .join(post.shorts)
                .where(filterByCursor(command)) // 커서 기반 페이징
                .where(getFilterExpression(command)) // 내가 업로드한 게시글
                .where(post.id.notIn(
                        JPAExpressions
                                .select(reportPost.post.id)
                                .from(reportPost)
                                .where(reportPost.reporter.id.eq(command.getUserId())
                ))) // 본인이 신고한 게시글 제거
                .where(post.shorts.shortsStateEnum.eq(ShortsStateEnum.OK)) // shorts의 인코딩이 완료된 게시글
                .limit(command.getPageSize())
                .orderBy(orderByPostSortOption(command.getSortOption())) // 커서 정렬 조건
                .orderBy(post.id.desc())
                .transform(groupBy(post.id)
                        .as(new QPostFetchDto(post.id,
                                new QPostFetchDto_PostUserInformation(post.user.id, post.user.nickname, post.user.profileImgUrl),
                                new QPostFetchDto_PostShortsInformation(post.shorts.id, post.shorts.shortsStateEnum, post.shorts.shortsUrl, post.shorts.thumbnailUrl),
                                post.view,
                                post.postCategory,
                                post.isBlind
                        )));

        return resultMap.keySet().stream()
                .map(resultMap::get)
                .collect(Collectors.toList());
    }

 

 

[기능추가]

성능 이슈 때문에 LEFT JOIN 을 사용해서 처리를 할까 하다가, 실제로 이런 경우에는 크게 성능 차이가 나지 않는다는 글을 여러개 접했습니다.

 

그래서 일단 in 절에 서브쿼리 사용하는 방식으로 구현을 했습니다.

.where(post.id.notIn(
    JPAExpressions
    .select(reportPost.post.id)
    .from(reportPost)
    .where(reportPost.reporter.id.eq(command.getUserId())
))) // 본인이 신고한 게시글 제거

 

[user_id 인덱스 추가 후 실행계획 확인]

 

먼저 신고 테이블의 원래 상황을 보자면..

 

신고 테이블은 다음과 같이 (신고번호, 포스트id, 신고자id)로 구성되어 있습니다.

그리고 한 명의 유저가 동일한 게시글을 신고할 수 없도록 (포스트 id, 신고자 id) 로 multipe unique key 제약조건을 걸어놓은 상태입니다.

 

유니크 키를 걸게 되면, 해당 제약조건의 빠른 검증을 위해 index가 함께 생성됩니다. 현재 'idx01_unique_report_post' 이라는 이름으로 인덱스가 걸려있습니다.

 

 

 

 

처음에는 일단 유저가 신고한 게시글인지 빠르게 탐색하기 위해서 `user_id` 에 인덱스를 걸었었습니다.

그런데 실제로 실행계획을 돌려보니..

제가 이번에 추가한 `idx02_report_post` 를 후보로만 올려놓고, 실제로는 기존에 걸려있던 `idx01_unique_report_post` 키를 활용하더군요.

 

[아! 커버링 인덱스..]

어! 왜 이렇지!!! 라고 샤워를 하며 30분동안 생각에 잠기게 됩니다.

 

어! 왜 이렇지!!! 라고 샤워를 하며 곰곰히 생각을 했는데, 생각해보니 기존 unique index table에 `(user_id, post_id)`가 있다는 것이 떠올라서, 샤워를 끝내고 실제로 호다닥 확인해보니 `Extra: Using index` 가 적혀있더군요 ㅎㅎ

 

왜 Extra 컬럼을 볼 생각을 안했을까

 

Extra의 Using index는 커버링 인덱스를 통해 결과를 가져왔다는 것을 의미합니다.

 

인덱스에 모든 값이 올라가있어서, 실제로는 disk I/O가 일어나지 않는 커버링 인덱스로 실행된 것 입니다.

 

 

 

[실제 내부 인덱스 테이블 구조]

실제 내부의 인덱스 테이블은 다음과 같이 생겼을 것 입니다.

MySQL 같은 경우에는 인덱스 테이블이 PK 값을 가지고 있어서, 다음과 같이 표현이 되는데

제가 위에서 실행한 쿼리에서 필요한 (post_id, user_id)는 인덱스 테이블 안에 모두 포함되어 있음을 알 수 있습니다.

 

그렇다면 굳이 느린 Disk I/O 를 하면서 테이블을 직접 읽을 필요가 없겠죠?

 

 

 

그런데 사실 다시 생각해보면, 만약 커버링 인덱스가 아니었더라도 `(user_id, post_id)` 복합키에서 user_id가 선행 컬럼이라서, 단독 index를 거는 것과 차이가 없었을 겁니다. ㅎㅎㅎ

 

왜냐. 인덱스 테이블 순서를 보시면, 선행 컬럼인 post_id 이후에 user_id가 정렬되는 방식입니다.

 

그렇다면 post_id에 따로 인덱스를 걸더라도, 동일한 데이터 순서로 탐색을 하게 됩니다.

 

감사합니다.