[DB & 팀 프로젝트] 실행 계획을 통해 인덱스 걸어보기!

들어가며

Real MySQL 8.0 1권 책을 읽으며 조회 시, 인덱스의 중요성에 대해서 새삼 느끼게 되었습니다. 따라서 현재 프로젝트에서 작성한 쿼리의 실행 계획을 확인 후, 인덱스를 설정하여 조회 성능을 개선할 수 있는 부분이 있는지 확인해보고자 글을 작성하게 되었습니다!

어플리케이션 코드.

OtherMemberProfileView otherMemberProfileView = queryFactory
                .from(member)
                .leftJoin(hobby).on(hobby.id.in(member.profile.hobbyIds))
                .leftJoin(job).on(job.id.eq(member.profile.jobId))
                .leftJoin(profileImage).on(profileImage.memberId.eq(otherMemberId).and(profileImage.isPrimary.eq(true)))
                .leftJoin(match).on(getMatchJoinCondition(memberId, otherMemberId))
                .where(member.id.eq(otherMemberId))
                .transform(
                        groupBy(member.id).as(
                                new QOtherMemberProfileView(
                                        member.id,
                                        member.profile.nickname.value,
                                        profileImage.imageUrl.value,
                                        member.profile.age,
                                        member.profile.gender.stringValue(),
                                        member.profile.height,
                                        job.name,
                                        list(hobby.name),
                                        member.profile.mbti.stringValue(),
                                        member.profile.region.stringValue(),
                                        member.profile.smokingStatus.stringValue(),
                                        member.profile.drinkingStatus.stringValue(),
                                        member.profile.highestEducation.stringValue(),
                                        member.profile.religion.stringValue(),
                                        match.id,
                                        match.requesterId,
                                        match.responderId,
                                        match.requestMessage.value,
                                        match.responseMessage.value,
                                        match.status.stringValue(),
                                        member.primaryContactType.stringValue(),
                                        cases()
                                                .when(match.status.eq(MatchStatus.MATCHED).and(member.primaryContactType.eq(PrimaryContactType.PHONE_NUMBER)))
                                                .then(member.phoneNumber.value)
                                                .when(match.status.eq(MatchStatus.MATCHED).and(member.primaryContactType.eq(PrimaryContactType.KAKAO)))
                                                .then(member.kakaoId.value)
                                                .otherwise((String) null)
                                )
                        )).get(otherMemberId);

4개의 테이블과 조인하는 쿼리입니다. (총 5개의 테이블) 각 on 절에 들어가는 컬럼에 대해서 어떤 실행 계획을 갖고 있는지 확인해보겠습니다.
먼저 Spring Boot 어플리케이션 상에서 발생하는 쿼리 (queryDsl 이 변환한 SQL)을 확인한 뒤, 이에 대한 적절한 값을 넣어준 SQL의 실행 계획을 확인해보겠습니다!

    select
        m1_0.id,
        m1_0.nickname,
        pi1_0.url,
        m1_0.age,
        cast(m1_0.gender as varchar),
        m1_0.height,
        j1_0.name,
        h1_0.name,
        cast(m1_0.mbti as varchar),
        cast(m1_0.region as varchar),
        cast(m1_0.smoking_status as varchar),
        cast(m1_0.drinking_status as varchar),
        cast(m1_0.highest_education as varchar),
        cast(m1_0.religion as varchar),
        m2_0.id,
        m2_0.requester_id,
        m2_0.responder_id,
        m2_0.request_message,
        m2_0.response_message,
        cast(m2_0.status as varchar),
        cast(m1_0.primary_contact_type as varchar),
        case 
            when (m2_0.status=? 
            and m1_0.primary_contact_type=?) 
                then m1_0.phone_number 
            when (m2_0.status=? 
            and m1_0.primary_contact_type=?) 
                then m1_0.kakao_id 
            else null 
    end 
from
    members m1_0 
left join
    hobbies h1_0 
        on h1_0.id in (select
            hi1_0.hobby_id 
    from
        member_hobbies hi1_0 
    where
        m1_0.id=hi1_0.member_id) 
left join
    jobs j1_0 
        on j1_0.id=m1_0.job_id 
left join
    profile_images pi1_0 
        on pi1_0.member_id=? 
        and pi1_0.is_primary=? 
left join
    matches m2_0 
        on (m2_0.requester_id=? 
        and m2_0.responder_id=? 
        or m2_0.requester_id=? 
        and m2_0.responder_id=?) 
        and m2_0.status not in (?, ?) 
where
    m1_0.id=?

다음과 같은 쿼리로 변환해서 나오기 때문에, ? 에 해당하는 부분에 적절한 값을 대응시킨 쿼리를 분석해보겠습니다.

SQL 코드

    SELECT
    m1_0.id,
    m1_0.nickname,
    pi1_0.url,
    m1_0.age,
    CAST(m1_0.gender AS CHAR), 
    m1_0.height,
    j1_0.name,
    h1_0.name,
    CAST(m1_0.mbti AS CHAR), 
    CAST(m1_0.region AS CHAR), 
    CAST(m1_0.smoking_status AS CHAR), 
    CAST(m1_0.drinking_status AS CHAR), 
    CAST(m1_0.highest_education AS CHAR), 
    CAST(m1_0.religion AS CHAR), 
    m2_0.id,
    m2_0.requester_id,
    m2_0.responder_id,
    m2_0.request_message,
    m2_0.response_message,
    CAST(m2_0.status AS CHAR), 
    CAST(m1_0.primary_contact_type AS CHAR), 
    CASE 
        WHEN (m2_0.status = 'MATCHED' AND m1_0.primary_contact_type = 'PHONE_NUMBER') 
            THEN m1_0.phone_number 
        WHEN (m2_0.status = 'MATCHED' AND m1_0.primary_contact_type = 'KAKAO') 
            THEN m1_0.kakao_id 
        ELSE NULL 
    END AS contact_info
FROM
    members m1_0 
LEFT JOIN
    hobbies h1_0 
    ON h1_0.id IN (
        SELECT hi1_0.hobby_id 
        FROM member_hobbies hi1_0 
        WHERE m1_0.id = hi1_0.member_id
    ) 
LEFT JOIN
    jobs j1_0 
    ON j1_0.id = m1_0.job_id 
LEFT JOIN
    profile_images pi1_0 
    ON pi1_0.member_id = 1 
    AND pi1_0.is_primary = TRUE 
LEFT JOIN
    matches m2_0 
    ON (
        (m2_0.requester_id = 1 AND m2_0.responder_id = 2) 
        OR 
        (m2_0.requester_id = 2 AND m2_0.responder_id = 1)
    ) 
    AND m2_0.status NOT IN ('EXPIRED', 'REJECT_CHECKED') 
WHERE
    m1_0.id = 1;

참고 : 멤버(ID=2)가 다른 멤버(ID=1)의 프로필 정보를 조회하기 위한 쿼리입니다!

실행 계획

실행 계획 결과

m1_0 table 은 member 테이블이므로, 당연히 인덱스(기본키)를 타게 되어있다.

하지만, 두 번째 h1_0 table 은 왜 인덱스를 타지 않는 것인지 궁금하였습니다.

추측하기로는, 현재 데이터가 너무 적어서 한번에 바로 모든 데이터를 가져올 수 있는 상황이기 때문 으로 추측하였고 이를 확인하기 위해 hobbies 테이블에 1000개의 데이터를 삽입해보기로 하였습니다.

INSERT INTO hobbies (name, created_at)
SELECT CONCAT('Hobby_', n), NOW() FROM (
    WITH RECURSIVE numbers AS (
        SELECT 1 AS n
        UNION ALL
        SELECT n + 1 FROM numbers WHERE n < 1000
    )
    SELECT n FROM numbers
) AS temp;

이후 결과는 ..?

인덱스 사용

PK를 사용하여 탐색하는 것으로 결과가 변경되었습니다!

즉, 데이터가 너무 적어서 인덱스를 타서 검색하는 비용이 더 비싼 것으로 옵티마이저가 계산을 한 것이 맞았습니다.

그러면 이제 인덱스를 타지 않는 나머지 테이블(pi1_0, profile_image)에 인덱스를 걸어주도록 해보겠습니다!

profile_images pi1_0 ON pi1_0.member_id = 1 AND pi1_0.is_primary = TRUE 

처음 고려한것은 member_id 와 is_primary 를 모두 사용하는 복합 인덱스를 걸어주려고 하였으나, 기획 상 한 회원이 가질 수 있는 이미지의 개수는 최대 6개로 고정되어 있어, member_id 인덱스 하나만으로도 충분히 데이터를 빠르게 찾을 수 있다고 생각하여 member_id 하나에만 인덱스를 걸어주도록 해보겠습니다.

ALTER TABLE profile_images ADD INDEX idx_member_id (member_id);

member_id 인덱스를 타는 것으로 바뀌었습니다!

근데 왜 아래의 서브쿼리는 인덱스를 타지 않는 것일까...?

LEFT JOIN
    hobbies h1_0 
    ON h1_0.id IN (
        SELECT hi1_0.hobby_id 
        FROM member_hobbies hi1_0 
        WHERE m1_0.id = hi1_0.member_id
    ) 

• 분명 member_hobbies 테이블에는 member_id 가 보조키로 설정이 되어있는데.. 왜 인덱스를 타지 않을까?

특이한 점은 member_hobbies 테이블에 PK가 존재하지 않고 있었다는 것이었습니다. 그래서, member_id & hobby_id 의 조합으로 PK를 설정해주니, member_id 인덱스를 인식하기 시작하였습니다.

이로인한 가설은 다음과 같습니다.

인덱스가 결국 PK를 가르키는 B-Tree 로 알고 있는데, 그럼 이전에는 PK가 없어서 member_id 인덱스가 가르킬 PK가 없어서 인덱스가 작동하지 않았던 것이 아닐까?

어찌저찌 PK를 걸어주니 다행히 잘 동작하게 되었습니다...! 위의 가설을 증명할 수 있는 방법을 알게 된다면 이후 포스팅을 추가하도록 하겠습니다!

 

수정

 

인덱스가 걸리지 않았던 이유는 식별가능한 데이터가 너무 적었기 때문이다. 여기서 식별이란, '인덱스를 통해 다름을 확인할 수 있는' 을 의미하는데, 이전에는 2명의 member_id 에 대해서 hobby_id 를 1~1000까지 삽입하였기 떄문에 member_id 로는 행의 절반을 확인해야했기 때문이다. 그렇기 떄문에 인덱스를 타지 않았던 것이다.

 

근데, member_id 와 hobby_id 를 복합 인덱스로 두면 디스크에 추가적으로 접근하지 않아도 hobby_id 값을 알 수 있기 떄문에 해당 인덱스를 사용한 듯하다!

 

(member_id , hobby_id) 데이터를 (1,1) ~ (1000, 1000)으로 설정하니 결국 member_id 인덱스만 있어도 인덱스를 타는 것을 확인하였다. 

 

Real MySQL 8.0 책에 따르면 인덱스를 통해 접근하는 데이터의 수가 전체 데이터의 약 25% 이하인 경우에 인덱스를 사용한다고 했는데, 아마 이전에는 member_id 의 cardinality 가 2였기 때문에 동작하지 않았던 것 같다ㅎㅎ.. (전체 데이터는 2000개 인데...)