N+1 문제 해결 및 인덱스를 활용한 쿼리 최적화

 

이번 포스팅에서는 EATceed를 개발하다가 발생한 N+1 문제를 Fetch Join으로 해결하고 이를 쿼리 분리를 통해 개선한 경험을 공유하고자 합니다.

EATceed는 사용자의 식사를 바탕으로 체중 관리를 해주는 서비스로 주요 Entity로는 MemberEntity, MealEntity, FoodEntity가 있습니다.

 

N+1 문제가 발생한 Entity들은 아래와 같습니다.

 

MealEntity는 식사를 의미하는 Entity로 FoodEntity와 N : N 관계입니다. EATceed에서는 JPA를 사용하므로 가운데에 연결 테이블인 MealFoodEntity를 두었습니다.

public class MealEntity { 

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = ENTITY_PREFIX + "_PK", nullable = false)
    private Long id;

    @OneToMany(mappedBy = "mealEntity")
    private List<MealFoodEntity> mealFoodEntities = new ArrayList<>();

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "MEMBER_FK")
    private MemberEntity memberEntity;
    
    ...
}

 

@Entity
public class MealFoodEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = ENTITY_PREFIX + "_PK", nullable = false)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "MEAL_FK", referencedColumnName = "MEAL_PK")
    private MealEntity mealEntity;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "FOOD_FK", referencedColumnName = "FOOD_PK")
    private FoodEntity foodEntity;

    ...
    
}

 

@Entity
public class FoodEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = ENTITY_PREFIX + "_PK", nullable = false)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "MEMBER_FK")
    private MemberEntity memberEntity;
    
    @Column(name = ENTITY_PREFIX + "_PROTEIN", nullable = false)
    private Double protein;

	  ...
}

 

MealFoodEntity와 MealFoodEntity는 양방향 ManyToOne관계, MealFoodEntity와 FoodEntity는 단방향 ManyToOne 관계

 

 

특정 사용자의 한달치 식사 내용을 분석하라 

위와 같은 요구사항이 있습니다. 위 요구사항을 해결하기 위해 MemberEntity, MealEntity, MealFoodEntity, FoodEntity를 모두 사용하여야 합니다.

N + 1 문제 발생

 

쿼리 메서드를 사용하여 아래와 같이 조회를 하였습니다. 그 결과 N + 1 문제가 발생하였습니다.

아래는 테스트 코드를 이용해 문제 상황을 재현한 것입니다.

 

 

쿼리 결과 사진)

 

1. MEAL 전체 조회 수행

 

2. MEAL_ID를 바탕으로 MEAL_FOOD 조회 -> 3번의 쿼리

MealEntity의 개수만큼 쿼리가 발생

 

3. FOOD_ID를 바탕으로 FOOD 조회 -> 6번의 쿼리

MealFoodEntity의 개수만큼 쿼리가 발생

 

N + 1 문제가 발생한 이유

MealFoodEntity와 MealEntity (양방향 ManyToOne)

 

MealEntity에서 특정 회원의 MealEntity들이 조회될 때, 해당 JPQL이 그대로 실행됩니다.

MealEntity는 List 타입의 필드를 가지고 있으므로, Proxy Collection Wrapping을 통해 Lazy Loading이 적용됩니다. 따라서 N+1 문제가 즉시 발생하지는 않습니다.

 

public class MealEntity extends BaseEntity {


    @OneToMany(mappedBy = "mealEntity", fetch = FetchType.LAZY)
    private List<MealFoodEntity> mealFoodEntities = new ArrayList<>();
		
}

 

그러나 이후에 mealFoodEntity의 컬럼을 조회할 때 N+1 문제가 발생합니다.
MealEntity 입장에서 mealFoodEntities 필드를 가지고 있지만, MEAL_TB의 정보만으로는 해당 객체를 알 수 없습니다.
따라서 JPA는 Meal_FOOD_TB에 대해 meal_id를 기준으로 추가적인 조회를 수행하게 됩니다.

 

위 상황처럼 MealEntity가 3개라면, Meal_FOOD_TB에 대해 meal_id를 조건으로 하는 조회가 3번 발생합니다.

 

 

MealFoodEntity와 FoodEntity (단방향 ManyToOne)

 

MealFoodEntity와 FoodEntity는 N:1 관계입니다.
MealFoodEntity가 조회되면, 해당 엔티티는 FoodEntity의 Proxy 객체를 가지고 있습니다. Proxy 객체에는 FOOD_ID가 포함되어 있어 이를 활용해 FOOD_TB에 쿼리를 날릴 수 있습니다.

예를 들어 MealFoodEntity가 6개라면, Food_TB에 대해 food_id를 조건으로 하는 조회가 6번 발생합니다.

public class MealFoodEntity extends BaseEntity {

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "FOOD_FK", referencedColumnName = "FOOD_PK")
    private FoodEntity foodEntity;

}

 

 

Fetch Join을 통해 N + 1 문제 해결

 

MealEntity와 MealFoodEntity는 1 : N 관계이지만, MealFoodEntity와 FoodEntity는 N : 1 관계이므로 Fetch Join을 2번 사용할 수 있습니다. 이를 통해 N + 1 문제를 해결할 수 있었습니다.

 

@Query(
        "select m from MealEntity m join fetch m.mealFoodEntity mf join fetch mf.foodEntity where m.createdDate >= :startOfMonth and m.createdDate < :endOfMonth and m.memberEntity.id = :memberId")
List<MealEntity> findMealsByMemberAndMonth(
        LocalDateTime startOfMonth, LocalDateTime endOfMonth, Long memberId);

 

 

이제 해당 쿼리가 얼마나 효율적인지 테스트해보겠습니다.

쿼리 테스트 전 준비

 

“앱 출시 6개월 후 활발하게 활동하는 회원이 100명 정도라 가정”하고 테스트 데이터를 만들었습니다.

 

회원 100명이 6개월 동안 아침, 점심, 저녁, 간식을 모두 섭취한다고 가정하면, 총 72,000개의 식사가 이루어집니다. 이는 아래와 같이 계산할 수 있습니다

100명 * 6개월 * 30일 * 4끼 = 72,000끼

그러나, 유저가 회원가입한 시기를 고려하여 프로시저를 사용해 각 테이블에 데이터를 삽입한 결과, 실제 기록된 식사 횟수(MEAL_COUNT)는 67,400개로 나타났습니다.

또한, 한 끼당 4가지 음식을 섭취한다고 가정하였습니다.

 

정확한 쿼리 테스트를 위해 쿼리 캐시 Off

 

SHOW VARIABLES LIKE 'query_cache_type';

 

아직 실제 유저가 없기 때문에 인덱스를 거는 것은 최대한 지양하고 다른 방식으로 쿼리 개선을 하려고 합니다.

 

개선할 쿼리

@Query(
        "select m from MealEntity m join fetch m.mealFoodEntity mf join fetch mf.foodEntity where m.createdDate >= :startOfMonth and m.createdDate < :endOfMonth and m.memberEntity.id = :memberId")
List<MealEntity> findMealsByMemberAndMonth(
        LocalDateTime startOfMonth, LocalDateTime endOfMonth, Long memberId);

 

해당 쿼리의 소요 시간은 약 0.764s 입니다.

select *
from MEAL_TB meal
	INNER JOIN MEAL_FOOD_TB MFT on meal.MEAL_PK = MFT.MEAL_FK
	INNER JOIN FOOD_TB food on MFT.FOOD_FK = food.FOOD_PK
where meal.CREATED_DATE >= '2024-06-01 00:00:00'
 and meal.CREATED_DATE < '2024-06-30 23:59:59'
 and meal.MEMBER_FK = 4;

 

실행 계획은 아래와 같습니다.

 

MFT 테이블에 대해 풀 테이블 스캔(Full Table Scan)을 수행하여 약 269,600개의 행을 조회했습니다. 그러나 WHERE 절 조건에 따라 거의 100%의 행이 필터링되었습니다. 또한, eq_ref 타입을 사용하여 각 행을 Primary Key로 스캔했습니다.

 

그러나 풀 테이블 스캔으로 인해 대부분의 행에 접근하였기 때문에 이는 비효율적인 쿼리입니다.

 

 

쿼리 분리를 통한 개선

 

MySQL에서는 FK에 자동으로 Index를 설정해줍니다. 이를 이용해 쿼리를 분리하여 개선하고자 합니다.

 

분리한 첫 번째 쿼리

 

explain SELECT meal.MEAL_PK
    FROM MEAL_TB meal
    WHERE meal.MEMBER_FK = 4
      AND meal.CREATED_DATE >= '2024-06-01 00:00:00'
      AND meal.CREATED_DATE < '2024-07-01 00:00:00';

 

 

동등 조건 검색 시 ref 타입을 사용하여 MEMBER_FK를 인덱스로 활용했음을 알 수 있습니다. 이 과정에서 접근한 행은 860개이며, 해당 조건을 통해 13.95%까지 필터링할 수 있었습니다.

이를 계산하면, 860 * 0.1395 = 약 120개로, 쿼리 결과는 약 120개가 나옵니다.

그리고, 해당 쿼리는 평균 소요 시간은 0.00271s 입니다.

 

 

분리한 두 번째 쿼리

 

explain SELECT *
FROM MEAL_FOOD_TB MFT
    INNER JOIN FOOD_TB food ON MFT.FOOD_FK = food.FOOD_PK
WHERE MFT.MEAL_FK IN (
    40952, 41052, 41152, 41252, 41353, 41453, 41553, 41653, 41754, 41854,
    41954, 42054, 42156, 42256, 42356, 42456, 42558, 42658, 42758, 42858,
    42959, 43059, 43159, 43259, 43361, 43461, 43561, 43661, 43763, 43863,
    43963, 44063, 44164, 44264, 44364, 44464, 44566, 44666, 44766, 44866,
    44968, 45068, 45168, 45268, 45369, 45469, 45569, 45669, 45771, 45871,
    45971, 46071, 46173, 46273, 46373, 46473, 46575, 46675, 46775, 46875,
    46977, 47077, 47177, 47277, 47379, 47479, 47579, 47679, 47780, 47880,
    47980, 48080, 48182, 48282, 48382, 48482, 48583, 48683, 48783, 48883,
    48984, 49084, 49184, 49284, 49385, 49485, 49585, 49685, 49786, 49886,
    49986, 50086, 50187, 50287, 50387, 50487, 50589, 50689, 50789, 50889,
    50990, 51090, 51190, 51290, 51392, 51492, 51592, 51692, 51794, 51894,
    51994, 52094, 52195, 52295, 52395, 52495, 52596, 52696, 52796, 52896
);

 

 

MEAL_FOOD_TB에서는 인덱스인 MEAL_FK를 사용하여 조회를 수행하고, FOOD_TB에서는 Primary Key인 FOOD_PK를 이용해 데이터를 조회합니다.

MFT 테이블에서는 MEAL_PK를 사용하여 약 480개의 행을 검색하였으며,  평균 소요 시간은 약 0.00529s 입니다.

 

 

쿼리 분리 결과

기존의 JOIN 쿼리를 인덱스를 효율적으로 사용하도록 쿼리를 분리하여 약 98.91% 개선하였습니다.

 

0.764s → 0.00271s + 0.00529s + 0.000315638 초 == 0.00832초

쿼리 소요 시간 + 애플리케이션 내 조립 및 WAS와 DB간의 통신 시간

 

 

 

https://github.com/JNU-econovation/EATceed/pull/452

 

번외 1) 서브 쿼리를 사용해보자

 

다음은 서브 쿼리를 사용하여 위 쿼리를 개선해보고자 하였습니다.

"쿼리를 분리하여 개선하기"에서 사용한 첫 번째 쿼리를 서브 쿼리로 사용하였습니다.

 

explain SELECT *
FROM MEAL_FOOD_TB MFT
    INNER JOIN FOOD_TB food ON MFT.FOOD_FK = food.FOOD_PK
WHERE MFT.MEAL_FK IN (
    SELECT meal.MEAL_PK
    FROM MEAL_TB meal
    WHERE meal.MEMBER_FK = 4
      AND meal.CREATED_DATE >= '2024-06-01 00:00:00'
      AND meal.CREATED_DATE < '2024-07-01 00:00:00'
);

 

 

그러나, 서브 쿼리의 실행 계획은 기존의 쿼리와 실행 계획이 똑같았습니다.

 

MEAL_TB의 MEMBER_FK 인덱스를 사용하라고 힌트를 준 경우도 마찬가지였습니다.

SELECT *
FROM MEAL_FOOD_TB MFT
    INNER JOIN FOOD_TB food ON MFT.FOOD_FK = food.FOOD_PK
WHERE MFT.MEAL_FK IN (
    SELECT meal.MEAL_PK
    FROM MEAL_TB meal USE INDEX (MEMBER_FK)
    WHERE meal.MEMBER_FK = 4
      AND meal.CREATED_DATE >= '2024-06-01 00:00:00'
      AND meal.CREATED_DATE < '2024-07-01 00:00:00'
);

 

왜 인덱스를 타지 못 하는 걸까?

서브 쿼리는 '쿼리 안의 쿼리'로 개발자에게 편리함을 주지만 치명적인 단점이 있습니다. 바로, 최적화를 받을 수 없다는 것입니다.
서브 쿼리는 실제 테이블을 이용하는 것이 아니라 가상의 테이블을 만들어 이용하는 것과 동일하기 때문에 메타 정보가 담겨있지 않습니다.
즉, 옵티마이저가 쿼리를 최적화할 때 필요한 인덱스와 같은 정보를 얻지 못 한다는 의미와 같습니다. 

 

 

 

부족한 글이지만, 읽어주셔서 감사합니다! 😀