회원 삭제 시 쿼리 19078개 문제

[Combi153]

문제

회원 삭제 API를 테스트 해보았습니다.

아래와 같이 깜짝 놀랄 결과가 나왔습니다.

총 16초 이상 걸리고, 19078 개 쿼리가 실행됩니다.

그래서 실험을 해보았습니다.

여행 개수, 감상 개수, 위치정보 개수 들을 달리하며, 회원을 삭제했습니다.

실험 결과

쿼리

회원 삭제 시 실행되는 쿼리는 다음과 같습니다.

select post_id,
       address,
       created_at,
       member_id,
       point_id,
       post_image_url,
       route_image_url,
       title,
       trip_id,
       updated_at,
       writing
from post
where member_id = ?

-- 회원 Id로 감상을 검색했을 때, 감상이 여러 개라면 감상의 개수만큼 point를 검색합니다.
select point_id,
       created_at,
       has_post,
       latitude,
       longitude,
       recorded_at,
       trip_id,
       updated_at
from point
where point_id = ?

select trip_id,
       created_at,
       image_url,
       member_id,
       name,
       route_image_url,
       status,
       updated_at
from trip
where member_id = ?

-- 회원 Id로 여행을 검색했을 때, 여행이 여러 개라면 여행의 개수만큼 point를 검색합니다.
select trip_id,
       point_id,
       created_at,
       has_post,
       latitude,
       longitude,
       recorded_at,
       updated_at
from point
where trip_id = ?

select member_id,
       created_at,
       nickname,
       oauth_id,
       oauth_type,
       updated_at
from member
where member_id = ?

-- 지워야 할 감상의 개수만큼 쿼리가 실행됩니다.
delete
from post
where post_id = ?

-- 지워야 할 위치정보의 개수만큼 쿼리가 실행됩니다.
delete
from point
where point_id = ?

-- 지워야 할 여행의 개수만큼 쿼리가 실행됩니다.
delete
from trip
where trip_id = ?

delete
from member
where member_id = ?

이처럼 위치정보, 감상, 여행의 개수에 따라 쿼리가 n 개 이상 실행되는 문제가 발생하고 있습니다.

원인

회원의 감상과 여행을 삭제할 때는 Repository의 deleteByMemberId() 메서드를 사용합니다.

이 메서드는 entity를 삭제할 때, 먼저 select 문으로 entity를 찾습니다. 그후 entity의 id를 조건으로 delete 문을 실행합니다. 따라서 회원이 여러 여행을 갖는 경우 여행의 개수만큼 select 문을 실행하거나 delete 문을 실행하는 상황이 발생하는 것입니다.

해결

감상 삭제

public interface PostRepository extends JpaRepository<Post, Long> {
    
    //(...)
    @Modifying
    @Query("DELETE FROM Post p WHERE p.member.id = :memberId")
    void deleteByMemberId(@Param(value = "memberId") Long memberId);
}

@query에 delete 문을 직접 작성합니다. 하나의 쿼리로 모든 Post가 지워질 수 있도록 합니다.

여행 삭제

여행 삭제는 다소 복잡합니다.
여행을 삭제하기 위해서는 여행에 포함된 모든 위치정보를 앞서 삭제해야 합니다.

DB 구조 상 Point table이 trip_id를 FK로 갖고 있는 형태입니다.
따라서 다음과 같은 과정을 밟습니다.

• Trip table에서 회원 Id로 삭제할 여행 Id의 목록을 조회한다.
• Point table에서 IN() 문법으로 trip_id가 삭제할 여행 Id 목록에 포함되면 삭제한다.
• Trip table에서회원 Id로 여행을 삭제한다.

@RequiredArgsConstructor
@Component
public class TripDeleteEventHandler {

    private final TripRepository tripRepository;
    private final PointRepository pointRepository;

    @EventListener
    public void deletePostByMemberId(MemberDeleteEvent event) {
        List<Long> tripIds = tripRepository.findAllTripIdsByMemberId(event.memberId());
        pointRepository.deleteByTripIds(tripIds);
        tripRepository.deleteByMemberId(event.memberId());
    }
}

public interface TripRepository extends JpaRepository<Trip, Long> {

    // (...)

    List<Trip> findAllByMemberId(Long memberId);

    @Query("SELECT t.id FROM Trip t WHERE t.member.id = :memberId")
    List<Long> findAllTripIdsByMemberId(@Param(value = "memberId") Long memberId);

    @Modifying
    @Query("DELETE FROM Trip t WHERE t.member.id = :memberId")
    void deleteByMemberId(@Param(value = "memberId")
}

findAllByMemberId 로 List을 조회할 수도 있지만 findAllTripIdsByMemberId로 TripId의 List를 조회했습니다. TripId 만 조회하는 것이 특별히 더 빠르기 때문입니다. Trip table에서 member_id 는 FK이고, trip_id 는 PK 입니다. 즉, 두 컬럼 모두 index가 설정되어 있습니다. 따라서 member_id 로 trip_id를 조회하는 것은 커버링 인덱스에 해당합니다. 커버링 인덱스는 테이블을 읽는 과정이 없기 때문에 속도가 빠릅니다.

이후 위치정보 삭제, 여행 삭제는 @query 를 활용합니다. 하나의 쿼리로 모든 Point, Post 가 지워질 수 있도록 합니다.

public interface PointRepository extends JpaRepository<Point, Long> {

    @Modifying
    @Query("DELETE FROM Point p WHERE p.trip.id IN :tripIds")
    void deleteByTripIds(@Param(value = "tripIds") List<Long> tripIds);
}