MariaDB에서 쿼리 분리를 통해 쿼리 개선하기

현재 상황

앱을 출시하기 전 “앱 출시 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);

 

 

해당 쿼리는 캘린더 월 분석 API에 사용됩니다. 또한, 월 분석 API는 자주 사용될 것으로 예상됩니다.

 

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;

 

 

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

 

실행 계획 분석

 

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

또한, eq_ref 타입을 사용하여 각 행을 Primary Key로 스캔했습니다.

 

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

 

쿼리 분리를 통한 개선

 

위의 쿼리를 인덱스를 최대한 사용하는 방향으로 쿼리를 분리하여 개선하고자합니다.

 

분리한 첫 번째 쿼리

 

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개가 나옵니다.

 

또한, 쿼리에서 필요한 것은 MEAL_PK만 반환하는 것이므로 디스크에 추가로 접근할 필요가 없어 매우 효율적입니다.

그리고, 해당 쿼리는 평균 소요 시간은 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 입니다.

 

 

 

 

서브 쿼리를 사용해보자

 

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

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

 

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'
);

 

결론

 

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

 

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

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

 

 

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