상속 관계에서의 DB 조회 성능 개선

먼저 테스트 데이터 총 500만개 (caregivers=간병인), 지역은 총 20개 지역으로 나눠서 동일한 비율로 (지역당 약 25만개씩) 더미데이터를 추가한 뒤 환자 -> 간병인 찾기 기능을 진행하였습니다. 

 

찾기 기능을 원활하게 하려면 적절한 인덱스 설정 + 페이지네이션 구현이 필수입니다.

현재 프로젝트는 무한스크롤 방식인 No-Offset 방식의 페이지네이션을 채택하였고, 동적 조건 검색을 원활히 하기 위한 Querydsl을 도입하였습니다.

 

하지만 구현하는 과정에서 예상치 못한 상황들을 만났고, 해결해 나간 과정을 기록하려 합니다.

 

No-Offset 적용 방식은 여기 입니다. (정렬 복합키 해결)

 

먼저 프로젝트 구조에 대해 설명드릴 내용이 있습니다.

진행중인 프로젝트는 JPA InheritanceType.JOINED 전략으로 구성되어 있습니다.

간략하게 JOINED 전략을 설명하자면, 먼저 JPA는 ORM 기술입니다. 즉, 객체와 관계형 DB 매핑을 도와주는 기술입니다. 하지만 객체와 RDB는 분명한 차이가 존재합니다. 객체는 '상속'이 존재하지만, RDB는 '상속' 개념이 없다는 것입니다. 그러므로 객체 지향적인 개발을 하는 입장에서 상속 효과를 내고 싶다면 실질적으로 DB에서 사용할 수 있는 방법은 두 가지 입니다.

 

1. 부모 테이블에 부모 컬럼 + 자식 컬럼을 몰아 넣고 관리한다.(SINGLE TABLE)

2. 테이블을 분리하되 부모 테이블에 공통된 컬럼을 넣고 자식 테이블은 자식테이블만의 컬럼을 관리한다. 이때 자식 테이블은 부모 테이블의 PK를 FK로 가지고 있음. 이때 FK는 본인 자식 테이블의 PK이기도 함)(JOINED)

 

저는 이 둘 중에 2번 JOINED 전략을 선택했는데요 이유는 단일 테이블로 진행할 시 컬럼 수가 매우 많아질 것을 우려했기 때문입니다. (지금도 컬럼이 20개가 넘는데, 더 늘어날 확률이 있기 때문에..)

 

하지만 2번 JOINED 전략은 절대 그냥 지나쳐서는 안되는 특징이 있습니다. 바로 자식 엔티티를 조회할 때, 항상 부모 엔티티와 JOIN 해서 결과를 반환한다는 것입니다. 쿼리에 명시적으로 JOIN을 추가하지 않아도 말이죠.

왜냐하면 '상속'의 특성 때문입니다. 

JAVA에서의 상속을 떠올려 보시면 이해가 빠를 것 같습니다. Java에서 자식 객체(=하위 클래스)를 생성하게 되면 부모 객체도 같이 생성이 된다는 것입니다. 상속받은 자식 클래스 생성자에 super()가 있어야 하는 이유이죠.(생략 가능)

이와 비슷하게 jpa에서도 자식 엔티티는 항상 부모 엔티티가 필요하기 때문에 항상 부모 엔티티를 조회합니다. 이를 위해 Join을 사용하죠.

실제로 extends를 통해 상속받고 있습니다.

 

다들 아시는 얘기를 왜 이리 길게 했냐면 저는 이러한

JPA Joined 전략과 Querydsl을 같이 사용할 때 쿼리가 '정확히' 어떤 방식으로 실행되는지 몰랐기에, 인덱스를 설정하는 데 삽질을 좀 했어서 입니다.

 

 

1. Querydsl 코드 실행 시 DB 실행 계획에서 using temporary를 만나다. -> 성능 저하

 

한 번 기존 코드를 실행해보겠습니다.

List<Long> memberIds = queryFactory
        .select(caregiver.id)
        .from(caregiver)
        .where(
                addressEq(condition.getAddr()),
                nameEq(condition.getName()),
                genderEq(condition.getGender()),
                ageLoe(condition.getAgeLoe()),
                caregiver.isProfilePublic,
                cursorProfilePublicTimeAndCaregiverId(lastProfilePublicTime, lastIndex)
        )
        .orderBy(caregiver.profilePublicTime.desc(), caregiver.id.asc())
        .limit(pageable.getPageSize() + 1)
        .fetch();

이 코드를 실행하고 간단하게 요청 URL에 address=서울시 강남구를 입력해봅시다. (기본 정렬: 최신순)

 

실행 결과 SQL은 

select c1_0.member_id
from caregivers c1_0 join members c1_1 on c1_0.member_id=c1_1.member_id
where c1_1.addr='서울시 강남구' and c1_1.isProfilePublic
order by c1_1.profilePublicTime desc, c1_0.member_id
limit 6;

이렇게 나옵니다. 실행 계획과 실제로 쿼리가 수행된 시간을 보시죠.

어라? 왜 이리 느릴까요?

시간이 13초..? 쿼리 한 번에 13초면 서비스 시작도 못할 것 같습니다. 

커버링 인덱스는 제대로 적용된 듯 보입니다. Extra에 Using index가 아주 잘 보이네요. 하지만 Using temporary 또한 있군요.

발생한 쿼리에서 주의 깊게 보셔야 하는 점은 order by에서 profilePublicTime은 member 테이블에서(c1_1.profilePublicTime) 정렬하고, member_id는 caregiver 테이블에서(c1_0.member_id) 정렬이 이루어진다는 점입니다.

 

여기서 좀 삽질을 했었는데요,

결론부터 말하자면 order by 절에서 member_id 정렬을 caregiver 테이블에서 진행하는 것으로 인한 Using temporary가 원인이였습니다. 

 

처음에 예상한 값은 order by c1_1.profilePublicTime desc, c1_1.member_id 였습니다. 하지만 왜 member_id는 caregiver 테이블에서 가져올까요? 이것 또한 Java 상속의 특징 때문입니다. Java에서 자식 클래스는 부모 클래스를 물려받습니다. 그래서 자식 객체(caregiver)를 생성 후에 값을 가져올 때, 자식에게 없으면 부모로 올라가서 부모의 값을 가져옵니다. 하지만 자식 안에 값이 존재한다면 바로 자식에게서 가져오죠. 즉, profilePublicTime은 caregiver 테이블에 없기 때문에 member테이블의 값을 사용하고 member_id는 caregiver 테이블에 있기 때문에 cargiver 테이블의 값을 사용합니다. 정렬 조건이 다른 테이블에서 올때 MySQL 옵티마이저는 새로운 임시 테이블을 만들어 정렬을 수행합니다.(Using temporary) 

 

• 조인 결과 생성: caregivers와 members 테이블을 조인하여 조건에 맞는 중간 결과 집합을 생성합니다.
• 임시 테이블 사용: 이 중간 결과를 정렬하기 위해 임시 테이블을 생성하고, 여기에 결과를 저장합니다.
• 정렬 수행: 임시 테이블에서 정렬을 수행합니다. 이 과정에서 메모리 내에서 정렬을 수행할 수 없는 경우, 디스크 기반의 임시 테이블을 사용하여 정렬을 완료합니다.

이제 문제 원인을 알았으니 해결을 해볼 차례입니다.

 

이 문제를 해결하기 위한 답은 생각보다 간단합니다. order by 구문이 member 테이블에서 모두 진행되면 됩니다. 임시 테이블을 사용할 필요가 없어지죠.

 

한번 제가 임의로 order by에서 c1_0.member_id를 c1_1.member_id로 변경해볼까요? (order by member_id를 member 테이블에서 진행)

select c1_0.member_id
from caregivers c1_0 join members c1_1 on c1_0.member_id=c1_1.member_id
where c1_1.addr='서울시 강남구' and c1_1.isProfilePublic
order by c1_1.profilePublicTime desc, c1_1.member_id
limit 6;

Index Key는 똑같지만 Extra에서 Using temporary가 사라진 것만으로 0.5초 내로 수행이 되는 것이 확인됩니다.

 

그렇다면 실제 Querydsl 코드에서 이를 어떻게 구현할 수 있을까요?

사실 이 부분이 좀 애먹었습니다.

지금껏 caregiver_id로 정렬을 하는 것이 문제였다면 코드에서 명시적으로 member.id를 쓰도록 하면 되는 것 아닌가? 의문이 들었습니다.

.orderBy(caregiver.profilePublicTime.desc(), member.id.asc())

이렇게 말이죠. 결과는 ?!

아아.. member.id를 해석할 수 없다고 나오네요. 생각해보면 당연합니다.

querydsl은 fetch(); 가 실행될 때 쿼리가 즉시 실행됩니다. 이때 fetch() 전,  order by(member.id.asc())를 읽을 때 member는 존재하지 않으니 에러를 발생시키는 것입니다. 추가 설명하자면 join이 포함된 쿼리가 실제로 실행된 후에야 member테이블 정보를 얻어올 수 있는데, 코드가 실제로 실행 되기 전 상태에서는 member.id를 읽을 수 없다는 것이죠.

 

사실 이러한 문제는 원래라면 명시적으로 join()을 추가하여 해결할 수 있습니다.

.join(caregiver.member, member) 이런식으로 말이죠. 이렇게 진행한다면 caregiver와 member가 join이 발생하고 order by의 member.id 또한 제대로 작동하겠죠.

하지만 진행중인 프로젝트가 JPA-JOINED 전략이라는 것이 문제입니다.

만약 코드에 join()을 작성한다면 실제 쿼리는 

select c1_0.member_id
from members m1_0 left join
    (caregivers c1_0 join members c1_1 on c1_0.member_id=c1_1.member_id)
        on m1_0.member_id=c1_0.member_id
where c1_1.addr='서울시 강남구' and c1_1.isProfilePublic
order by c1_1.profilePublicTime desc, c1_1.member_id
limit 6;

이런 식으로 join이 두 번 나가게 됩니다.

왜냐하면 JPA_JOINED 전략 특징상 from(caregiver) 진행 시, 항상 Join Member가 실행되기 때문이죠.

 

그렇다면 어떻게 member_id를 member 테이블에서 가져올 수 있을까요?

제가 생각한 방법은 두 가지였습니다.

1. member_id를 member에서 가져온다면 사실상 caregiver 테이블을 사용하는 곳이 사라집니다. (select member_id는 member 테이블에서 가져오는 것이 가능)

그렇다면 querydsl 코드에서 caregiver 객체가 아닌 member 객체를 사용해 조회하는 것이였습니다.

이렇게 진행한다면 order by를 member 테이블에서 모두 진행할 수 있게 됩니다.

 

하지만 단점 또한 있습니다. caregiver 테이블에만 속해있는 컬럼들로는 검색, 정렬을 할 수 없다는 것입니다.

대표적으로 경력(experienceYears)이 있습니다. 기존 기획에서는 경력을 통해 조건 검색이 가능해야 했습니다. (ex.3년 이상)

하지만 경력은 caregiver 테이블에만 속해 있는 컬럼입니다. 위와 같이 from(member)를 한다면 

List<Long> memberIds = queryFactory
        .select(member.id)
        .from(member)
        .where(
                addressEq(condition.getAddr()),
                nameEq(condition.getName()),
                genderEq(condition.getGender()),
                ageLoe(condition.getAgeLoe()),
                //경력을 조건에 포함시켜 검색 시, 런타임 에러 발생
                experienceYearsGoe(condition.getExperienceYearsGoe()),
                member.isProfilePublic,
                cursorViewCountsAndCaregiverId(lastViewCounts, lastIndex)
        )
        .orderBy(member.profilePublicTime.desc(), member.id.asc())
        .limit(pageable.getPageSize() + 1)
        .fetch();

private BooleanExpression experienceYearsGoe(Integer experienceYears) {
        return experienceYears == null ? null : caregiver.experienceYears.goe(experienceYears);
    }

여기서 experienceYearsGoe() 메서드에 접근할 때, caregiver를 찾을 수 없다는 문제가 동일하게 발생합니다.

 

2. from(caregiver)을 하고, caregiver.id를 사용하지 않는 것입니다.

사실 caregiver.id를 가져오는 이유는 No-Offset 방식의 페이지네이션 진행 시, 정렬 조건이 중복값일 때를 처리하기 위함입니다. profilePublicTime은 중복값이 들어갈 수 있기 때문에 이를 막기 위해 고유값인 id를 더해 고유커서를 만든 것이죠. 하지만 꼭 id로 고유값을 만들어야 할까요? caregiver 테이블에 없고, member 테이블에만 있는 고유값으로 진행하면 되지 않을까요? 코드로 구현해보자면 이렇게 될 수 있습니다. (username = unique)

List<Long> memberIds = queryFactory
        .select(caregiver.id)
        .from(caregiver)
        .where(
                addressEq(condition.getAddr()),
                nameEq(condition.getName()),
                genderEq(condition.getGender()),
                ageLoe(condition.getAgeLoe()),
                caregiver.isProfilePublic,
                cursorProfilePublicTimeAndCaregiverId(lastProfilePublicTime, lastIndex)
        )
        .orderBy(caregiver.profilePublicTime.desc(), caregiver.username.asc())
        .limit(pageable.getPageSize() + 1)
        .fetch();

 

쿼리는 이렇습니다.

select c1_0.member_id
from caregivers c1_0
         join members c1_1 on c1_0.member_id = c1_1.member_id
where c1_1.addr = '서울시 동대문구'
  and c1_1.isProfilePublic
order by c1_1.profilePublicTime desc, c1_1.username
limit 6;

username 정렬을 member 테이블(c1_1)에서 진행하는 것을 확인할 수 있습니다.

그로 인해 using temporary도 일어나지 않습니다. 하지만 속도는 id로 진행했을 때보다 약간 느린 것 같습니다. (아마 join 문 추가로 인한 성능 저하라 생각됩니다.)

이렇게 진행한다면 비로소 간병인_경력에 따른 검색이 가능해집니다.

 

이 두가지 방법 중에 저는 우선 1번 member를 통한 조회를 선택하였습니다.

이유는 

1. 지역, 이름, 성별, 나이로도 충분한 조건 검색이 가능하다.

2. 지역, 이름, 성별, 나이는 환자, 간병인 공통 검색 조건이지만 경력은 간병인 한정 검색 조건이다. 

member의 공통 필드로 검색 기능을 완료한 뒤, 이후 팀원들과의 구체적인 요구사항 회의를 진행 후, caregiver로 변경할 계획입니다.

 

 

2. order by id 오름차순으로 진행 시 직접 생성한 인덱스를 타지 않는 문제

이건 조금 다른 문제입니다. (order by를 member_id로만 진행하는 상황입니다)

만약 최신순을 복합커서키가 아닌 id로만 진행하고 order by를 member 테이블의 member_id로 진행하면 어떻게 될까요? 우선 1번에서 다룬 것 처럼 Using temporary는 안나온다는 것을 예상하실 수 있겠죠.

현재 인덱스는 addr_isProfilePublic_member_id입니다. 

실행 결과는

위 두 SQL 모두 같은 실행계획을 반환합니다.

인덱스를 타긴하는데 PRIMARY를 탑니다. 시간도 무척 오래걸리네요. filtered도 5.16에 그칩니다. 클러스터링 인덱스인 ID를 토대로 addr을 검색하니 당연히 filter 또한 1/20이 나올 수 밖에 없겠죠. (지역 총 20개)

하지만 분명 직접 생성한 addr, isProfilePublic 인덱스가 있는데도 불구하고 PRIMARY 키를 타는 모습입니다.

 

그렇다면 force index를 통해 강제로 인덱스를 적용해 봅시다.

type이 range로 바뀌고 extra에 using index(커버링 인덱스)가 추가되었네요. 또한 filter율도 100%가 나옵니다.

24초 -> 477ms 로 매우 빨라진 모습을 확인할 수 있네요. 이유가 무엇일까요? idx_member_addr_isProfilePublic 인덱스를 타는 것이 훨씬 빠른데도 불구하고 어째서 MySQL 옵티마이저는 PRIMARY 인덱스를 타는 것일까요?

 

제가 생각한 이유는, 정렬 기준 때문입니다. 현재 member 테이블의 member_id로 정렬이 이루어지고 있습니다. 그렇다면 where 절과 order by 절 모두 member 테이블에서 작업이 이루어진다는 것이죠. 이때 옵티마이저는 where addr=수원시 영통구, isProfilePublic=true인 항목들을 인덱스를 통해서 모두 찾고, 찾은 내용들을 id순으로 정렬하는 것보다, primary키인 member_id를 이용해 먼저 정렬을 진행한 뒤, 해당하는 addr과 isProfilePublic의 값들을 찾는 것이 더 효율적이라 판단할 수 있습니다. 왜냐하면 member_id는 클러스터링 인덱스로써, 매우 빠르게 값에 접근할 수 있기 때문이죠. 데이터가 많지 않다면 이것은 매우 효율적일 수 있습니다. 하지만 현재 테스트 데이터는 수원시 영통구라는 지역에 약 30만건의 데이터가 있기 때문에, PRIMARY로 인덱스를 잡는 것은 비효율적입니다. 

 

그래서 생각한 방법은 최신순 정렬을 id로 진행하지 않는 것입니다.

이때 현재 프로젝트에서의 id로 최신순 정렬하는 것에 대한 근본적인 오류를 발견했습니다.

진행중인 프로젝트에서의 최신순 의미는 매칭 리스트에 등록한 순서 입니다.

하지만 가입 시에 자동 할당되는 id값(auto_increments)으로 정렬을 진행하게 된다면, 만약 가입을 하고 한 달 뒤쯤 매칭 리스트에 등록을 하게된다면, id가 빠르기 때문에 검색 최상단에 나오지 않을 것입니다.(id desc)

즉, 최신순이란 매칭 리스트에 등록한 시간을 기준으로 해야되는 것이죠.

 

그래서 ProfilePublicTime이라는 컬럼을 추가하여 정렬 조건을 변경하였습니다. 

이제 인덱스가 PRIMARY가 아닌, 직접 생성한 인덱스를 타고 시간도 빨라진 것을 확인할 수 있습니다.

 

최신순 정렬을 profilePublicTime으로 바꿔야한다는 생각이 들기 전에는

force index를 기존 쿼리에 추가해야 한다는 생각이 들었습니다. 그래서 Querydsl에 force index를 추가하는 법을 찾아보았지만 JPA에서는 인덱스 힌트를 지원하지 않았고, Native Query를 사용하거나 쿼리 매칭 설정 파일을 따로 만들어 진행하는 방법이 있었습니다. 하지만 Native Query를 사용하게 된다면 복잡한 동적 쿼리를 컴파일 시점에 에러를 잡을 수 있는 Querydsl을 사용하지 못하게 되므로 sql 문 복잡성 증가 + 런타임 에러 발생 가능성 증가 라는 문제가 있었고, 따로 설정 파일을 만든다면 그에 해당하는 모든 쿼리문에서 동일한 매칭 함수가 적용될 수 있는 문제가 있었습니다.

 

하지만 profilePublicTime 컬럼을 추가함으로써, 인덱스를 제대로 타고, 이로 인해 성능 또한 처음에 비해 많이 빨라진 것을 볼 수 있습니다. (평균 500ms 이내)